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;