Oracle-游标和存储过程

1.带return和参数传递的游标

declare type emp_record_type is record (f_name scott.emp.ename%type, h_date scott.emp.hiredate%TYPE); v_1 emp_record_type; cursor c3(v_deptno number ,v_job VARCHAR2) return emp_record_type is select ename, hiredate from scott.emp where deptno=v_deptno AND job =v_job; begin open c3(v_job=>'MANAGER', v_deptno=>10); loop fetch c3 into v_1; if c3%found then DBMS_OUTPUT.PUT_LINE(v_1.f_name||' 的雇佣日期是 ' ||v_1.h_date); else DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); exit; end if; end loop; close c3; end;

2.利用FOR循环检索游标-利用FOR循环统计并输出各个部门的平均工资。

declare cursor c_1 is select deptno,avg(sal) avgsal from scott.emp group by deptno; v_dept c_1%rowtype; begin for v_dept in c_1 loop DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.avgsal); end loop; end;

3.利用FOR循环统计并输出各个部门的平均工资。

declare begin for v_dept in (select deptno,avg(sal) avgsal from scott.emp group by deptno) loop DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.avgsal); END LOOP; end;

4.带update 的游标-Scott.emp表，利用游标，给工资低于1200 的员工增加工资50。并输出“编码为’员工编码号’的工资已经更新”

declare v_empno scott.emp.empno%TYPE; v_sal scott.emp.sal%TYPE; CURSOR c_cursor IS SELECT empno,sal FROM scott.emp; begin open c_cursor; loop fetch c_cursor into v_empno, v_sal; exit when c_cursor%notfound; if v_sal<=1200 then UPDATE scott.emp SET Sal=Sal+50 WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!'); END IF; DBMS_OUTPUT.PUT_LINE('记录数:'|| c_cursor %ROWCOUNT); END LOOP; CLOSE c_cursor; END; DECLARE v_empno scott.emp.empno%TYPE; v_sal scott.emp.sal%TYPE; CURSOR c_cursor IS SELECT empno,sal FROM scott.emp where sal<=1200 for update; begin open c_cursor; LOOP FETCH c_cursor INTO v_empno, v_sal; EXIT WHEN c_cursor%NOTFOUND; UPDATE scott.emp SET Sal=Sal+50 WHERE current of c_cursor; DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!'); DBMS_OUTPUT.PUT_LINE('记录数:'|| c_cursor%ROWCOUNT); END LOOP; CLOSE c_cursor; END;

5.

修改scott.emp表员工的工资， 如果员工的部门号为10，工资提高100； 部门号为20，工资提高150； 部门号为30，工资提高200； 否则工资提高250。

DECLARE CURSOR c_emp IS SELECT * FROM scott.emp FOR UPDATE; v_zl NUMBER; v_emp c_emp%rowtype; BEGIN FOR v_emp IN c_emp LOOP CASE v_emp.deptno WHEN 10 THEN v_zl:=100; WHEN 20 THEN v_zl:=150; WHEN 30 THEN v_zl:=200; ELSE v_zl:=250; END CASE; UPDATE scott.emp SET sal=sal+v_zl WHERE CURRENT OF c_emp; END LOOP; END;

6.

修改emp表的工资， 工资不足1000的，调整为1500， 工资高于1000的，调整为原来工资的1.5倍， 调整后，若工资〉10000，则设其为10000

declare cursor c_1 is select empno,sal from scott.emp for update of sal nowait; v_sal scott.emp.sal%type; begin for cursor_1 in c_1 loop if cursor_1.sal<=1000 then v_sal:=1500;--工资不足1000的，调整为1500 else v_sal:=cursor_1.sal*1.5;-----工资高于1000的，调整为原来工资的1.5倍 if v_sal>10000 then----调整后，若工资〉10000，则设其为10000 v_sal:=10000; end if; end if; update scott.emp set sal=v_sal where current of c_1;-----按照以上要求一条一条修改记录 end loop; end;

7.带case的select 语句的嵌套

create table t01 as select xh,xm,zym, (case when zxf>50 then 'gao' when zxf>=40 then 'zhong' else '学分不够，需继续' end) as 获得学分情况 from xs;

8.不带参数的存储过程-创建一存储过程update_emp,该过程用于将emp表中empno为7876的员工姓名修改为candy

create or replace procedure update_emp is begin update scott.emp set ename='candy' where deptno=7876; DBMS_OUTPUT.PUT_LINE('更新成功！'); end update_emp; 调用： declare begin update_emp; end;

9.存储过程的调用

两种调用方式： 在SQL*PLUS中调用 EXEC procedure_name(parameter_list) （了解） 在PL/SQL块中调用 Declare 实参声明 BEGIN procedure_name(parameter_list); END； /*掌握*/

10.计算指定系总学分大于40的人数

create or replace procedure count_grade( v_zym in xs.zym%type,person_num out number ) is begin select count(zxf) into person_num from xs where zym=v_zym and zxf>=40; end count_grade; 调用：--对于in 类型的参数可以直接赋初值，对类型参数需要在程序块中定义接收变量。 declare person_n number(3); begin count_grade('计算机',person_n); dbms_output.put_line(person_n); end;

11.编写一存储过程，用于计算指定系学生的总学分

create or replace procedure totalcredit(z_zym in xs.zym%type,total out number) is begin select sum(zxf) into total from xs where zym=z_zym; end totalcredit; 调用： declare total number(3); begin totalcredit('计算机',total); dbms_output.put_line(total); end;

12.游标变量

declare type t_dept is ref cursor return scott.emp%rowtype; c_1 t_dept; v_row scott.emp%rowtype; begin open c_1 for select * from scott.emp where deptno=10; fetch c_1 into v_row; dbms_output.put_line(v_row.empno||' ' ||v_row.job); close c_1; open c_1 for select * from scott.emp where sal>=2000; fetch c_1 into v_row; dbms_output.put_line(v_row.deptno||' ' ||v_row.job); close c_1; end;