/*查看表hr.employees表结构中,SALARY字段的定义:desc hr.employees;用ctrl+单击*/
/*分别用NUMBER、%TYPE、子类型salary定义变量v_salary_num、v_salary_type、v_salary_subtype。将hr.employees表的第一条记录中的SALARY值赋值给上述三个变量。并将上述三个变量在控制台输出。*/DECLARE v_salary_num NUMBER(8,2); v_salary_type hr.employees.salary%TYPE; SUBTYPE salary_subtype IS hr.employees.salary%TYPE; v_salary_sub salary_subtype;BEGIN SELECT salary INTO v_salary_sub FROM hr.employees WHERE ROWNUM<2; v_salary_num:=v_salary_sub; v_salary_type:=v_salary_sub; DBMS_OUTPUT.put_line('v_salary_sub='||to_char(v_salary_sub,'9999.99')); DBMS_OUTPUT.put_line('v_salary_num='||v_salary_num); DBMS_OUTPUT.put_line('v_salary_type='||v_salary_type);END;
/*接练习1,使用ROWTYPE定义一个新的变量,v_employee,并将第一条记录赋值给v_employee。分别使用IF-ELSE-END IF和CASE判断数据库中得到的salary的值,如果>2000打印出:[员工姓名]的salary>2000.如果=2000打印出:[员工姓名]的salary=2000.如果<2000打印出:[员工姓名]的salary<2000*/DECLARE v_employee hr.employees%ROWTYPE; v_salary hr.employees.salary%TYPE:=4000; BEGIN SELECT * INTO v_employee FROM hr.employees WHERE salary<v_salary AND ROWNUM<2; -- select * from hr.employees where salary<4000; IF v_employee.salary>v_salary THEN DBMS_OUTPUT.PUT_LINE('['||v_employee.first_name||v_employee.last_name||']的salary>2000,='||TO_CHAR(v_employee.salary)); ELSIF v_employee.salary=v_salary THEN DBMS_OUTPUT.PUT_LINE('['||v_employee.first_name||v_employee.last_name||']的salary=2000'); ELSE DBMS_OUTPUT.PUT_LINE('['||v_employee.first_name||v_employee.last_name||']的salary<2000'); END IF; CASE WHEN v_employee.salary>v_salary THEN DBMS_OUTPUT.PUT_LINE('['||v_employee.first_name||v_employee.last_name||']的salary>2000,='||TO_CHAR(v_employee.salary)); WHEN v_employee.salary=v_salary THEN DBMS_OUTPUT.PUT_LINE('['||v_employee.first_name||v_employee.last_name||']的salary>2000,='||TO_CHAR(v_employee.salary)); ELSE DBMS_OUTPUT.PUT_LINE('['||v_employee.first_name||v_employee.last_name||']的salary>2000,='||TO_CHAR(v_employee.salary)); END CASE;END;
/*分别使用LOOP、WHILE、FOR循环从10到1打印出一串数字。*/
--分别使用LOOP,EXITDECLARE v_loopcount number(2):=11;BEGIN LOOP v_loopcount:=v_loopcount-1; IF v_loopcount<1 THEN EXIT; END IF; DBMS_OUTPUT.PUT_LINE(v_loopcount); END LOOP;END;
--分别使用LOOP,EXIT WHENDECLARE v_loopcount number(2):=11;BEGIN LOOP v_loopcount:=v_loopcount-1; EXIT WHEN (v_loopcount<1); DBMS_OUTPUT.PUT_LINE(v_loopcount); END LOOP;END;
--分别使用WHILEDECLARE v_loopcount number(2):=10;BEGIN WHILE v_loopcount>0 LOOP DBMS_OUTPUT.PUT_LINE(v_loopcount); v_loopcount:=v_loopcount-1; END LOOP;END;
--分别使用FORDECLARE v_loopcount number(2):=10;BEGIN FOR v_loopcount IN REVERSE 1 .. 10 LOOP DBMS_OUTPUT.PUT_LINE(v_loopcount); END LOOP;END;
--分别使用FORDECLARE v_loopcount number(2):=10;BEGIN FOR v_loopcount IN 1 .. 10 LOOP DBMS_OUTPUT.PUT_LINE(11-v_loopcount); END LOOP;END;