postgre 存储过程 游标含参数 oracle存储过程游标的作用

初衷:

存储过程中查询语句如何返回多行结果？
我们知道，如果存储过程中查询语句有多行结果输出，会报错：
ORA-01422: exact fetch returns more than requested number of rows
若想让存储过程中的查询语句返回多行结果不报错，则需要使用游标来实现。
本例主要也是用来熟悉存储过程中游标的简单使用方法。案例所涉及的数据表使用的是oracle自带的scott用户，不熟悉scott的也可使用下列脚本自行创建。

1. 创建表

CREATE TABLE EMP(
 EMPNO NUMBER(4) NOT NULL,  
 ENAME VARCHAR2(10),  
 JOB VARCHAR2(9),  
 MGR NUMBER(4),  
 HIREDATE DATE,  
 SAL NUMBER(7, 2),  
 COMM NUMBER(7, 2),  
 DEPTNO NUMBER(2)
);  

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, SYSDATE, 800, NULL, 20); 
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, SYSDATE, 1600, 300, 30); 
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN',7698, SYSDATE, 1250, 500, 30); 
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,SYSDATE, 2975, NULL, 20); 
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, SYSDATE, 1250, 1400, 30); 
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, SYSDATE, 2850, NULL, 30); 
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, SYSDATE, 2450, NULL, 10); 
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, SYSDATE, 3000, NULL, 20); 
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, SYSDATE, 5000, NULL, 10); 
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, SYSDATE, 1500, 0, 30); 
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, SYSDATE, 1100, NULL, 20); 
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, SYSDATE, 950, NULL, 30); 
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, SYSDATE, 3000, NULL, 20); 
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, SYSDATE, 1300, NULL, 10); 
COMMIT;

1. 使用案例

--案例1、使用游标查询部门编号为10的所有人姓名和薪水
create or replace procedure test2 is
begin
 declare
   type c is ref cursor;    
   emp_sor c;      
   cname emp.ename%type;     
   csal emp.sal%type;
begin
  open emp_sor for select ename,sal from emp where deptno=10;       
  loop        
    fetch emp_sor into cname,csal;  --取游标的值给变量。             
    dbms_output.put_line('ename:'||cname||'sal'||csal);        
    exit when emp_sor%notfound;        
   end loop;         
   close emp_sor;     
end;
end test2;

--案例2、直接定义游标
create or replace procedure test3 is
begin
 declare
   cursor emp_sor  is select ename,sal from emp where deptno=10;
   cname emp.ename%type;
   csal emp.sal%type;
begin
  open emp_sor;
  loop
    fetch emp_sor into cname,csal;  --取游标的值给变量。
    dbms_output.put_line('ename:'||cname||'sal'||csal);
    exit when emp_sor%notfound;
   end loop;
   close emp_sor;
end;
end test3;

--案例3、使用记录变量来接受游标指定的表的数据
create or replace procedure test4 is
begin
 declare
   cursor emp_sor is
     select ename, sal from emp where deptno = 10;
   --使用记录变量来接受游标指定的表的数据
   type emp_type is record(
     v_ename emp.ename%type,
     v_sal   emp.sal%type);
   --用emp_type声明一个与emp_type类似的记录变量。该记录有两列，与emp表的ename，sal同类型的列。
   emp_type1 emp_type;
 begin
   open emp_sor;
   loop
     fetch emp_sor into emp_type1; --取游标的值给变量。
     dbms_output.put_line(emp_type1.v_ename || ',' || emp_type1.v_sal);
     exit when emp_sor%notfound;
   end loop;
   close emp_sor;
 end;
end test4;

案例4、用for游标取值
create or replace procedure test5 is
begin
 declare
   cursor emp_sor is select a.ename from emp a;
   type ename_table_type is table of varchar2(20);
   ename_table ename_table_type;
 begin
  --用for游标取值
   open emp_sor;
   --通过bulk collect减少loop处理的开销，使用Bulk Collect提高Oracle查询效率
   --Oracle8i中首次引入了Bulk Collect特性，该特性可以让我们在PL/SQL中能使用批查询，批查询在某些情况下能显著提高查询效率。
   --采用bulk collect可以将查询结果一次性地加载到collections中。
   --而不是通过cursor一条一条地处理。
   --可以在select into,fetch into,returning into语句使用bulk collect。
   --注意在使用bulk collect时，所有的into变量都必须是collections
     fetch emp_sor bulk collect into ename_table;
     for i in 1 ..ename_table.count loop
       dbms_output.put_line(ename_table(i));
   end loop;
   close emp_sor;
 end;
end test5;

--案例5、用for取值，带隐式游标会自动打开和关闭
create or replace procedure test6 is
begin
 declare
   cursor emp_sor is select a.ename from emp a;
   type emp_table_type is table of varchar(20);
   begin 
   for emp_record in emp_sor
     loop
       dbms_output.put_line('第'||emp_sor%rowcount||'雇员名:'||emp_record.ename);
     end loop;
 end;
end test6;

--案例6、判断游标是否打开
create or replace procedure test7 is
begin
 declare
   cursor emp_sor is select a.ename from emp a;
   type emp_table_type is table of varchar(20);
   emp_table emp_table_type;
 begin
  --用for取值，判断游标是否打开
   if not emp_sor%isopen then
     open emp_sor;
   end if;
   fetch emp_sor bulk collect into emp_table;
   dbms_output.put_line(emp_sor%rowcount);
   close emp_sor;
 end;
end test7;

--案例7、使用游标变量取值
create or replace procedure test8 is
begin
  --使用游标变量取值
 declare
   cursor emp_sor is select a.ename,a.sal from emp a;
   emp_record emp_sor%rowtype;
 begin
  open emp_sor;
  loop
    fetch emp_sor into emp_record;
    exit when emp_sor%notfound;
     --exit when emp_sor%notfound放的位置不一样得到的结果也不一样。如果放到dbms_....后，
     --结果会多显示一行数据，即查询结果的最后一行显示了两次。
    dbms_output.put_line('序号'||emp_sor%rowcount||'名称:'||emp_record.ename||'薪水:'||emp_record.sal);
  end loop;
  close emp_sor;
 end;
end test8;

--案例8、带参数的游标，在打开游标的时候传入参数
create or replace procedure test9 is
begin
  --带参数的游标，在打开游标的时候传入参数
 declare
   cursor emp_sor(no number) is select a.ename from emp a where a.deptno=no;
   emp_record emp_sor%rowtype;
 begin
  open emp_sor(10);
  loop
    fetch emp_sor into emp_record;
    exit when emp_sor%notfound;
    dbms_output.put_line('序号'||emp_sor%rowcount||'名称:'||emp_record.ename);
  end loop;
  close emp_sor;
 end;
end test9;

--案例9、使用游标做更新操作
create or replace procedure test10 is
begin
  --使用游标做更新、删除操作，必须在定义游标的时候加上for update
  --当然也可以用for update nowait
 declare
   cursor emp_sor is select a.ename,a.sal from emp a for update;
   cname emp.ename%type;
   csal emp.sal%type;
 begin
  open emp_sor;
  loop
    fetch emp_sor into cname,csal;
    exit when emp_sor%notfound;
    dbms_output.put_line('名称:'||cname||','||'薪水:'||csal);
    if csal < 2000 then
      update emp set sal = sal+200 where current of emp_sor;
    end if;
  end loop;
  close emp_sor;
  --要查看更新后的数据，必须得重新打开游标去查询
  open emp_sor;
  loop
    fetch emp_sor into cname,csal;
     exit when emp_sor%notfound;
     dbms_output.put_line('名称:'||cname||','||'new薪水:'||csal);
  end loop;
  close emp_sor;
 end;
end test10;

--案例10、使用游标做删除操作
create or replace procedure test11 is
begin
  --使用游标做更新、删除操作，必须在定义游标的时候加上for update
 declare
   cursor emp_sor is select a.empno from emp a for update;
   pempno emp.empno%type;
 begin
  open emp_sor;
  loop
    fetch emp_sor into pempno;
    exit when emp_sor%notfound;
    dbms_output.put_line('旧的empno:'||pempno);
    if pempno = 2009 then
       delete emp where current of emp_sor;
    end if;
  end loop;
  close emp_sor;
  --要查看删除后的数据，必须得重新打开游标去查询
  open emp_sor;
  loop
    fetch emp_sor into pempno;
    exit when emp_sor%notfound;
    dbms_output.put_line('新的empno:'||pempno);
  end loop;
  close emp_sor;
 end;
end test11;

--案例11、直接使用游标而不用去定义
create or replace procedure test12 is
 begin
   for emp_record in(select empno,sal,deptno from emp)
     loop
       dbms_output.put_line('员工编号:'||emp_record.empno||',薪水:'||emp_record.sal||',部门编号'||emp_record.deptno);
     end loop;
end test12;

--案例12、带sql的统计查询
create or replace procedure test13 is
begin
 declare
   type test_cursor_type is ref cursor;
   test_cursor test_cursor_type;
   v_name user_tables.TABLE_NAME%type;
   v_count number;
   str_sql varchar2(100);
 begin
  open test_cursor for select table_name from user_tables;
  loop
    fetch test_cursor into v_name;
    if v_name is not null then
      str_sql := 'select count(*) from '|| v_name;
      execute immediate str_sql into v_count;
    end if;
    exit when test_cursor%notfound;
    dbms_output.put_line(v_name||','||v_count);
  end loop;
  close test_cursor;
 end;
end test13;

当我们写完存储过程之后，我们可以在 command window下执行，oracle默认是不显示输出的，
所以我们要 set serveroutput on 命令来显示输出结果，然后exec test1()即可输出结果。