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

目录

• 阅读准备事项
• 概述
• 显式游标

1. 1. 使用显式游标步骤
   2. 显式游标属性
   3. 游标for循环
   4. 参数游标
   5. 更新或删除游标行

• 游标变量
• 使用批量提取
• 使用cursor表达式
• 练习试题

 

阅读准备事项

　　请先阅读本学习系列之“ORACLE学习系列注意事项”。

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;

•  游标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;