PL/SQL游标的使用

--一.基础
--(一).SQL游标分为两种
--1.隐含游标专门处理DML,SELECT INTO操作。
--2.显示游标专门处理多行的SELECT语句。

--(二).隐含游标
--1.当使用增删改语句获取结果时,必须要使用SQL游标属性。
--2.SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT,SQL%ISOPEN
--SQL%ISOPEN用于确定SQL游标是否已打开,ORACLE会隐含打开和关闭游戏,开发时不需要使用,对于开发人员来说永远是FALSE。

--SQL%FOUND用来确定SQL执行是否成功,是否成功依据作用行来判断。
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     v_deptno emp.deptno%type:=&no;
  3  BEGIN
  4     UPDATE emp SET sal=sal*1.1
  5     WHERE deptno=v_deptno;
  6     IF SQL%FOUND THEN
  7             dbms_output.put_line('语句执行成功');
  8     ELSE
  9             dbms_output.put_line('该部门不存在雇员');
 10     END IF;
 11  END;
 12  /
输入 no 的值:  30
原值    2:      v_deptno emp.deptno%type:=&no;
新值    2:      v_deptno emp.deptno%type:=30;
语句执行成功

PL/SQL 过程已成功完成。

--SQL%NOTFOUND与SQL%FOUND相反。
SQL> DECLARE
  2     v_deptno emp.deptno%type:=&no;
  3  BEGIN
  4     UPDATE emp SET sal=sal*1.1
  5     WHERE deptno=v_deptno;
  6     IF SQL%NOTFOUND THEN
  7             dbms_output.put_line('语句执行成功');
  8     ELSE
  9             dbms_output.put_line('该部门不存在雇员');
 10     END IF;
 11  END;
 12  /
输入 no 的值:  30
原值    2:      v_deptno emp.deptno%type:=&no;
新值    2:      v_deptno emp.deptno%type:=30;
该部门不存在雇员

PL/SQL 过程已成功完成。

--SQL%ROWCOUNT用来返回SQL所作用的行数。
SQL> DECLARE
  2     v_deptno emp.deptno%type:=&no;
  3  BEGIN
  4     UPDATE emp SET sal=sal*1.1
  5     WHERE deptno=v_deptno;
  6     dbms_output.put_line('修改了'||SQL%ROWCOUNT||'');
  7  END;
  8  /
输入 no 的值:  30
原值    2:      v_deptno emp.deptno%type:=&no;
新值    2:      v_deptno emp.deptno%type:=30;
修改了6行

PL/SQL 过程已成功完成。


--(三).SAVEPOINT,ROLLBACK,COMMIT

SQL> DECLARE
  2     v_sal emp.sal%TYPE:=&salary;
  3     v_ename emp.ename%TYPE:='&name';
  4  BEGIN
  5     UPDATE emp SET sal=v_sal WHERE ename=v_ename;
  6     COMMIT;
  7  EXCEPTION
  8     WHEN OTHERS THEN
  9             ROLLBACK;
 10  END;
 11  /
输入 salary 的值:  1200
原值    2:      v_sal emp.sal%TYPE:=&salary;
新值    2:      v_sal emp.sal%TYPE:=1200;
输入 name 的值:  SCOTT
原值    3:      v_ename emp.ename%TYPE:='&name';
新值    3:      v_ename emp.ename%TYPE:='SCOTT';

PL/SQL 过程已成功完成。

--二.使用游标
--(一).使用显示游标
--a.定义游标;b.打开游标;c.提取数据;d.关闭游标
--DEMO1:使用 FETCH..INTO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     CURSOR emp_cursor IS
  3             SELECT ename,sal FROM emp WHERE deptno=10;
  4     v_ename emp.ename%TYPE;
  5     v_sal emp.sal%TYPE;
  6  BEGIN
  7     OPEN emp_cursor;
  8     LOOP
  9             FETCH emp_cursor INTO v_ename,v_sal;
 10             EXIT WHEN emp_cursor%NOTFOUND;
 11             dbms_output.put_line(v_ename||':'||v_sal);
 12     END LOOP;
 13     CLOSE emp_cursor;
 14  END;
 15  /
CLARK:2450
KING:5000
MILLER:1300

PL/SQL 过程已成功完成。

--DEMO2:使用 FETCH..BULK COLLECT INTO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     CURSOR emp_cursor IS
  3             SELECT ename FROM emp WHERE deptno=10;
  4     TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  5     ename_table ename_table_type;
  6  BEGIN
  7     OPEN emp_cursor;
  8     FETCH emp_cursor BULK COLLECT INTO ename_table;
  9     FOR i IN 1..ename_table.COUNT LOOP
 10             dbms_output.put_line(ename_table(i));
 11     END LOOP;
 12     CLOSE emp_cursor;
 13  END;
 14  /
CLARK
KING
MILLER

PL/SQL 过程已成功完成。

--DEMO3:使用 FETCH..BULK COLLECT INTO..LIMIT
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     CURSOR emp_cursor IS
  3             SELECT ename FROM emp;
  4     TYPE ename_varray_table IS VARRAY(5) OF emp.ename%TYPE;
  5     ename_varray ename_varray_table;
  6     rows INT:=5;
  7  BEGIN
  8     OPEN emp_cursor;
  9     LOOP
 10             FETCH emp_cursor BULK COLLECT INTO ename_varray
 11                     LIMIT rows;
 12             dbms_output.put('雇员名:');
 13             FOR i IN 1..ename_varray.COUNT LOOP
 14                     dbms_output.put(ename_varray(i)||',');
 15             END LOOP;
 16             dbms_output.new_line;
 17             EXIT WHEN emp_cursor%NOTFOUND;
 18     END LOOP;
 19     CLOSE emp_cursor;
 20  END;
 21  /
雇员名:SMITH,ALLEN,WARD,JONES,MARTIN,
雇员名:BLAKE,CLARK,SCOTT,KING,TURNER,
雇员名:ADAMS,JAMES,FORD,MILLER,

PL/SQL 过程已成功完成。

--DEMO4:使用基于游标类型的PL/SQL记录简化
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     CURSOR emp_cursor IS SELECT ename,sal FROM emp;
  3     emp_record emp_cursor%ROWTYPE;
  4  BEGIN
  5     OPEN emp_cursor;
  6     LOOP
  7             FETCH emp_cursor INTO emp_record;
  8             EXIT WHEN emp_cursor%NOTFOUND;
  9             dbms_output.put(emp_record.ename||','||emp_record.sal);
 10     END LOOP;
 11     dbms_output.new_line;
 12     CLOSE emp_cursor;
 13  END;
 14  /
SMITH,800ALLEN,2342.56WARD,1830.13JONES,2975MARTIN,1830.13BLAKE,4172.69CLARK,245
0SCOTT,1200KING,5000TURNER,2196.15ADAMS,1100JAMES,1390.9FORD,3000MILLER,1300

PL/SQL 过程已成功完成。

--(二).使用参数游标
SQL> --语法:CURSOR cursor_name(parameter_name datatype) IS select_statment;
SQL> --DEMO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     CURSOR emp_cursor(no NUMBER) IS
  3             SELECT ename FROM emp WHERE deptno=no;
  4     v_ename emp.ename%TYPE;
  5  BEGIN
  6     OPEN emp_cursor(10);
  7     LOOP
  8             FETCH emp_cursor INTO v_ename;
  9             EXIT WHEN emp_cursor%NOTFOUND;
 10             dbms_output.put_line(v_ename);
 11     END LOOP;
 12     CLOSE emp_cursor;
 13  END;
 14  /
CLARK
KING
MILLER

PL/SQL 过程已成功完成。

--(三).使用游标更新或删除数据
SQL> --语法:CURSOR cursor_name(parameter_name datatype)
SQL> --IS select_statment
SQL> --FOR UPDATE [OF column_reference] [NOWAIT];
SQL> --语法:UPDATE table_name SET column=.. WHERE CURRENT OF cursor_name;
SQL> --语法:DELETE table_name WHERE CURRENT OF curros_name;
SQL> --DEMO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     CURSOR emp_cursor(no NUMBER) IS
  3       SELECT ename FROM emp WHERE deptno=no
  4       FOR UPDATE OF ename
  5       NOWAIT;
  6       v_ename emp.ename%TYPE;
  7  BEGIN
  8     OPEN emp_cursor(10);
  9     LOOP
 10             FETCH emp_cursor INTO v_ename;
 11             EXIT WHEN emp_cursor%NOTFOUND;
 12             dbms_output.put_line('原始:'||v_ename);
 13             UPDATE emp SET ename=v_ename||'1' WHERE CURRENT OF emp_cursor;
 14             dbms_output.put_line('受影响的行数:'||SQL%ROWCOUNT);
 15     END LOOP;
 16     CLOSE emp_cursor;
 17     --COMMIT;
 18  END;
 19  /
原始:CLARK11
受影响的行数:1
原始:KING11
受影响的行数:1
原始:MILLER11
受影响的行数:1

PL/SQL 过程已成功完成。

--(四).使用FOR循环
SQL> --语法:FOR record_name IN cursor_name LOOP
SQL> -- END LOOP;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     CURSOR emp_cursor(no NUMBER) IS
  3       SELECT ename FROM emp WHERE deptno=no
  4       FOR UPDATE OF ename
  5       NOWAIT;
  6
  7  BEGIN
  8     FOR emp_record IN emp_cursor(10) LOOP
  9             dbms_output.put_line('原始:'||emp_record.ename);
 10             UPDATE emp SET ename=emp_record.ename||'1' WHERE CURRENT OF emp_c
 11             dbms_output.put_line('受影响的行数:'||SQL%ROWCOUNT);
 12     END LOOP;
 13     --COMMIT;
 14  END;
 15  /
原始:CLARK111
受影响的行数:1
原始:KING111
受影响的行数:1
原始:MILLER111
受影响的行数:1

PL/SQL 过程已成功完成。

--(五).使用游标变量
--定义REF CURSOR类型和游标变量
--语法:TYPE ref_cursor_name IS REF CURSOR [RETURN return_type];
--    cursor_variable ref_cursor_name;
--非RETURN返回的是记录类型

--打开游标
--语法:OPEN cursor_variable FOR select_statement;

--提取游标数据
--语法一:FETCH cursor_variable INTO variable1,variable2,...;
--语法二:FETCH cursor_variable BULK COLLECT INTO collect1,collet2,...[LIMIT rows];

--关闭游标变量
--语法:CLOSE cursor_variable;
SQL> DECLARE
  2    TYPE emp_record_type IS RECORD
  3    (
  4      name VARCHAR2(10),
  5      salary NUMBER(6,2)
  6    );
  7    TYPE emp_cursor_type IS REF CURSOR
  8      RETURN emp_record_type;
  9    emp_cursor emp_cursor_type;
 10    emp_record emp_record_type;
 11  BEGIN
 12    OPEN emp_cursor
 13      FOR SELECT ename,sal FROM emp WHERE deptno=20;
 14    LOOP
 15      FETCH emp_cursor INTO emp_record;
 16      EXIT WHEN emp_cursor%NOTFOUND;
 17      dbms_output.put_line(''||emp_cursor%ROWCOUNT||'个雇员:'||emp_record.name);
 18    END LOOP;
 19    CLOSE emp_cursor;
 20  END;
 21  /
第1个雇员:SMITH
第2个雇员:JONES
第3个雇员:SCOTT
第4个雇员:ADAMS
第5个雇员:FORD

PL/SQL 过程已成功完成。

--(六).使用游标表达式
--语法:CURSOR()
SQL> DECLARE
  2    TYPE emp_cursor_type IS REF CURSOR;
  3    CURSOR dept_cursor(no NUMBER) IS
  4      (
  5        SELECT a.dname,CURSOR(SELECT ename,sal
  6     FROM emp WHERE deptno=a.deptno)
  7        FROM dept a WHERE a.deptno=no
  8      );
  9    emp_cursor emp_cursor_type;
 10    v_ename emp.ename%TYPE;
 11    v_dname dept.dname%TYPE;
 12    v_sal emp.sal%TYPE;
 13  BEGIN
 14    OPEN dept_cursor(&no);
 15    LOOP
 16      FETCH dept_cursor INTO v_dname,emp_cursor;
 17      EXIT WHEN dept_cursor%NOTFOUND;
 18      dbms_output.put_line('部门名:'||v_dname);
 19      LOOP
 20        FETCH emp_cursor INTO v_ename,v_sal;
 21        EXIT WHEN emp_cursor%NOTFOUND;
 22        dbms_output.put_line('雇员名:'||v_ename||',工资:'||v_sal);
 23      END LOOP;
 24      CLOSE emp_cursor;
 25    END LOOP;
 26    CLOSE dept_cursor;
 27  END;
 28  /
输入 no 的值:  10
原值   14:   OPEN dept_cursor(&no);
新值   14:   OPEN dept_cursor(10);
部门名:ACCOUNTING
雇员名:CLARK1111,工资:2450
雇员名:KING1111,工资:5000
雇员名:MILLER1111,工资:1300

PL/SQL 过程已成功完成。

 

posted on 2012-08-21 18:07  wean  阅读(388)  评论(0编辑  收藏  举报

导航