PL/SQL编程学习之使用游标

阅读准备事项

　　可以先阅读本学习系列之“PL/SQL编程之开篇”。

CREATE TABLE emp_learn AS SELECT * FROM employees;CREATE TABLE dept_learn AS SELECT * FROM departments;

概述

　　当在PL/SQL块中执行DML和单行SELECT INTO语句时，oracle会分配隐含游标。为了处理SELECT语句返回的多行数据，需要使用显式游标。从oracle database 9i 开始，也可以使用select …bulk collect into语句将多行数据存放到PL/SQL集合变量。

　　这一节主要学习在PL/SQL块中使用显示游标的方法，学习目标为：

• 学会使用显式游标、游标for循环和参数游标。
• 学会使用游标更新或删除表行。
• 学会使用游标变量。
• 学会使用批量提取和cursor表达式。

显式游标

　　PL/SQL包含隐含游标和显式游标两种游标类型，其中隐含游标用于处理单行select into和DML语句，而显示游标则用于处理select语句返回的多行数据。

使用显示游标步骤
　　　a）定义游标：用于指定游标对应的select语句。语法：

　　　　CURSOR cursor_name IS select_statement;

　　　b）打开游标：用于执行所对应的select语句，并将行数据存放到游标结果集。

　　　　OPEN cursor_name；

　　　c）提取数据：将结果集中的行数据存放到PL/SQL变量。

　　　　FETCH cursor_name INTO variable1[variable2，…];

　　　d）关闭游标：用于释放游标结果集的数据。

　　　　CLOSE cursor_name;

显式游标属性
　　　　包含%ISOPEN、%FOUND、%NOTFOUND和&ROWCOUNT四种。结合示例解释含义：

　　　　a）%ISOPEN：该属性用于检测游标是否已经打开。如果已经打开，则返回true，否则返回false。

　　　　b）%FOUND：检测游标结果集是否存在数据。如果存在，则返回true，否则返回false。

　　　　c）%NOTFOUND：检测结果集是否未提取到数据。如果不存在数据，则返回true，否则返回false。

　　　　d）%ROWCOUNT：返回已提取的实际行数。

　　　　示例：

DECLARE   CURSOR emp_learn_cursor01 IS      SELECT first_name,salary,department_id FROM emp_learn WHERE department_id=&dno;   TYPE emp_table_type IS TABLE OF emp_learn_cursor01%ROWTYPE INDEX BY Binary_Integer;   emp_table emp_table_type;   i INT;BEGIN   --OPEN emp_learn_cursor01;   IF NOT emp_learn_cursor01%ISOPEN THEN      OPEN emp_learn_cursor01;      dbms_output.put_line('检测到游标未打开，现已打开');   END IF;   LOOP      dbms_output.put_line('ROWCOUNT的值：'||emp_learn_cursor01%ROWCOUNT);      i:=emp_learn_cursor01%ROWCOUNT+1;--让集合下标从1开始      FETCH emp_learn_cursor01 INTO emp_table(i);      --EXIT WHEN emp_learn_cursor01%NOTFOUND;      IF emp_learn_cursor01%NOTFOUND THEN         dbms_output.put_line('未提取到行');         EXIT;      ELSIF emp_learn_cursor01%FOUND THEN         dbms_output.put_line('提取到'||emp_learn_cursor01%ROWCOUNT||'行');      END IF;         dbms_output.put_line('姓名：'||emp_table(i).first_name||'，部门：'||emp_table(i).department_id||'，工资：'||emp_table(i).salary);   END LOOP;   CLOSE emp_learn_cursor01;END;

游标for循环

　　　　 此功能为简化处理显式游标数据处理，当使用游标for循环时，oracle会隐含的打开游标、提取数据并关闭游标。语法：
　　　　 FOR recorde_name IN cursor_name LOOP
　　　　　　 statements;
　　　　 END LOOP;

　　　　cursor_name用于指定已定义的游标名，而recorde_name是基于游标隐含定义的记录变量。在执行循环之前，oracle会隐含的打开游标，并且每循环一次自动提取一行数据，在提取了所有数据之后自动退出循环并隐含的关闭游标。

　　示例：

DECLARE   CURSOR emp_learn_cursor01 IS      SELECT first_name,salary,department_id FROM emp_learn WHERE department_id=&dno;BEGIN   FOR emp_recorde IN emp_learn_cursor01 LOOP      dbms_output.put_line('提取到'||emp_learn_cursor01%ROWCOUNT||'行');      dbms_output.put_line('姓名：'||emp_recorde.first_name||'，部门：'||emp_recorde.department_id||'，工资：'||emp_recorde.salary);   END LOOP;END;

参数游标

　　　 参数游标是指带有参数的游标。通过使用参数游标，使用不同的参数值可以生成不同的游标结果集。定义参数游标的语法：
　　　　CURSOR cursor_name(parameter_name data_type) IS select_statement;
　　　　OPEN cursor_name(parameter_value);(当不以游标for循环方式使用时，需要以此方式打开) 　

更行或删除游标行　
　　　 通过使用显式游标，不仅可以取得游标结果集的数据，而且可以更新或删除游标结果集的当前行。需要注意的是：当使游标更新或者删除数据时，定义游标必须带有for update子句以加行共享锁，并且在更新或者删除游标行时必须带有where current of子句。语法：
　　　　CURSOR cursor_name IS select_statement FOR UPDATE [OF column_reference] [NOWAIT];
　　　　UPDATE table_name SET column=… WHERE CURRENT OF cursor_name;
　　　　DELETE FROM table_name WHERE CURRENT OF cursor_name;　

　　示例：

DECLARE   --定义游标，寻找出名字相同，且相同数为输入的特定参数的雇员的名字   CURSOR name_cursor(v_same_count NUMBER) IS      SELECT first_name FROM emp_learn GROUP BY first_name HAVING COUNT(*)=v_same_count;   --定义游标，提取特定名字的雇员信息，以便执行UD操作   CURSOR emp_info_cursor(v_name VARCHAR2) IS      SELECT employee_id,first_name,salary FROM emp_learn WHERE first_name=v_name ORDER BY employee_id DESC FOR UPDATE;   v_temp_name emp_learn.first_name%TYPE;   v_cnt INT:=&cnt;   v_num INT;BEGIN   SELECT COUNT(*) INTO v_num FROM(SELECT first_name FROM emp_learn GROUP BY first_name HAVING COUNT(*)=v_cnt);   dbms_output.put_line('操作前，名字相同，且相同数为'||v_cnt||'的分组数为'||v_num);   SAVEPOINT s1;   FOR emp_name_record IN name_cursor(v_cnt) LOOP      FOR emp_info_record IN emp_info_cursor(emp_name_record.first_name) LOOP         dbms_output.put_line('开始-雇员：'||emp_info_record.employee_id||'，姓名'||emp_info_record.first_name||'工资'||emp_info_record.salary);         CASE emp_info_cursor%ROWCOUNT            WHEN 1 THEN               UPDATE emp_learn SET salary=salary*0.9 WHERE CURRENT OF emp_info_cursor;            WHEN 2 THEN               DELETE FROM emp_learn WHERE CURRENT OF emp_info_cursor;            WHEN 3 THEN               UPDATE emp_learn SET salary=salary*1.1 WHERE CURRENT OF emp_info_cursor;            ELSE               NULL;          END CASE;      END LOOP;      FOR emp_info_record IN emp_info_cursor(emp_name_record.first_name) LOOP          dbms_output.put_line('结束-雇员：'||emp_info_record.employee_id||'，姓名'||emp_info_record.first_name||'工资'||emp_info_record.salary);      END LOOP;    END LOOP;   dbms_output.put_line('操作结束');   SELECT COUNT(*) INTO v_num FROM(SELECT first_name FROM emp_learn GROUP BY first_name HAVING COUNT(*)=v_cnt);   dbms_output.put_line('操作完成后，名字相同，且相同数为'||v_cnt||'的分组数为'||v_num);   ROLLBACK TO s1;END;

游标变量

　　游标变量是基于REF CURSOR类型所定义的变量，它实际是指向内存地址指针。使用显式游标只能定义静态游标，而通过使用游标变量可以在打开游标时指定其所对应的select语句，从而实现动态游标。注意：不能在远程子程序中使用游标变量。当指定return子句时，那么在打开游标时select子句的返回结果必须与return子句指定的记录类型匹配。使用步骤：
　　　a）定义REF CURSOR类型和游标变量
　　　　TYPE ref_type_name IS REF CURSOR [RETURN return_type];
　　　　cursor_variable ref_type_name;
　　　b）打开游标变量，指定游标变量所对应的select语句。select语句被执行，数据存放到了游标结果集
　　　　OPEN cursor_variable FOR select_statement;
　　　c）提取数据
　　　　FETCH cursor_variable INTO variable1，variable2，…;
　　　d）关闭游标变量
　　　　CLOSE cursor_variable;

　　示例：

DECLARE   TYPE ref_cursor_type IS REF CURSOR RETURN emp_learn%ROWTYPE;   emp_cursor ref_cursor_type;   emp_record emp_cursor%ROWTYPE;BEGIN   OPEN emp_cursor FOR      SELECT * FROM emp_learn WHERE department_id=&dept;   LOOP      FETCH emp_cursor INTO emp_record;      EXIT WHEN emp_cursor%NOTFOUND;      dbms_output.put_line(emp_record.first_name||'-'||emp_record.department_id);   END LOOP;   CLOSE emp_cursor;END;

使用批量提取

　　 此功能为oracle 9i的新功能，通过使用fetch … bulk collect into可以从游标结果集中一次提取所有数据。也可以使用limit子句限制每次提取的行数。

　　示例：

DECLARE   TYPE ref_cursor_type IS REF CURSOR;   emp_ref_cursor ref_cursor_type;   TYPE emp_table_type IS TABLE OF emp_learn%ROWTYPE;   emp_table emp_table_type;   TYPE emp_arrary_type IS VARRAY(10) OF emp_learn.first_name%TYPE;   emp_name_array emp_arrary_type;BEGIN   OPEN emp_ref_cursor FOR      SELECT * FROM emp_learn WHERE department_id=&dept;   FETCH emp_ref_cursor BULK COLLECT INTO emp_table;   FOR i IN 1..emp_table.count LOOP      dbms_output.put_line(emp_table(i).first_name||'-'||emp_table(i).department_id);   END LOOP;   CLOSE emp_ref_cursor;   --使用limit   OPEN emp_ref_cursor FOR      SELECT first_name FROM emp_learn;   FETCH emp_ref_cursor BULK COLLECT INTO emp_name_array LIMIT 5;   CLOSE emp_ref_cursor;   dbms_output.put_line('被限制后数组的长度为：'||emp_name_array.count);END;

使用CURSOR表达式

　　　cursor表达式用于实现嵌套游标，也是oracle 9i的新特性。通过使用cursor表达式，可以在PL/SQL块中处理基于多张表的复杂关联数据。为了可以在PL/SQL块中取得嵌套游标的数据，需要定义游标变量和嵌套循环。语法：
　　　CURSOR(subquery)

　　 示例：

DECLARE   CURSOR dept_cursor(deptno NUMBER) IS      SELECT department_name,CURSOR(SELECT first_name,salary FROM employees WHERE department_id=d.department_id)          FROM dept_learn d WHERE d.department_id=deptno;   TYPE ref_cursor_type IS REF CURSOR;   emp_cursor ref_cursor_type;   v_dname dept_learn.department_name%TYPE;   TYPE emp_record_type IS RECORD(      first_name VARCHAR2(30),      salary     NUMBER   );   emp_record emp_record_type;BEGIN   OPEN dept_cursor(&dno);   LOOP      FETCH dept_cursor INTO v_dname,emp_cursor;      EXIT WHEN dept_cursor%NOTFOUND;      LOOP         FETCH emp_cursor INTO emp_record;         EXIT WHEN emp_cursor%NOTFOUND;         dbms_output.put_line('部门名：'||v_dname||'，雇员姓名：'||emp_record.first_name||'，工资：'||emp_record.salary);      END LOOP;   END LOOP;   CLOSE dept_cursor;END;