初衷:
存储过程中查询语句如何返回多行结果?
我们知道,如果存储过程中查询语句有多行结果输出,会报错:
ORA-01422: exact fetch returns more than requested number of rows
若想让存储过程中的查询语句返回多行结果不报错,则需要使用游标来实现。
本例主要也是用来熟悉存储过程中游标的简单使用方法。案例所涉及的数据表使用的是oracle自带的scott用户,不熟悉scott的也可使用下列脚本自行创建。
创建表
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、使用游标查询部门编号为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()即可输出结果。
转载自@AlanMathisonTuring