PL/SQL_游标使用

最新推荐文章于 2023-03-15 16:07:40 发布

菖草

最新推荐文章于 2023-03-15 16:07:40 发布

阅读量1.1k

点赞数 1

分类专栏： PL/SQL 文章标签： PLSQL 游标动态游标 ref cursor 参数游标

本文链接：https://blog.csdn.net/com185272358/article/details/21739705

版权

PL/SQL 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

使用游标

当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，Oracle会为其分配上下文区（Context Area），游标是指向上下文区的指针。对于数据操纵语句和单行SELECT INTO语句来说，Oracle会为它们分配隐含游标。
1、使用显式游标
为了处理SELECT语句返回的多行数据，开发人员可以使用显式游标，使用显式游标包括定义游标、打开游标、提取数据和关闭游标四个阶段。
（1）定义游标
语法如下：

CURSOR cursor_name IS select_statement;

（2）打开游标
语法如下：

OPEN cursor_name;

（3）提取数据

语法如下：

语法一：FETCH cursor_name INTO variable1,variable2,...;
语法二：FETCH cursor_NAME BULK COLLECT INTO collect1,collect2,...[LIMIT rows];

（4）关闭游标

CLOSE cursor_name;

示例一：在显式游标中使用FETCH...INTO语句
在Oracle9i之前，使用FETCH...INTO语句每次只能处理一行数据。为了处理结果集中的多行数据，必须要使用循环语句进行处理。

DECLARE
  CURSOR cur_emp IS
  SELECT ename,sal FROM emp WHERE deptno = 20;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  --打开游标
  OPEN cur_emp;
  
  --循环提取
  LOOP
    FETCH cur_emp INTO v_ename,v_sal;
    EXIT WHEN cur_emp%NOTFOUND;
    dbms_output.put_line('员工姓名：' || v_ename ||'，工资：' || v_sal);
  END LOOP;
  
  --关闭游标
  CLOSE cur_emp;
END;

示例二：在显式游标中，使用FETCH...BULK COLLECT INTO语句提取所有数据
从Oracle9i开始，通过使用FETCH...BULK COLLECT INTO语句，一次就可以提取结果集的所有数据。

DECLARE
  CURSOR cur_emp IS
  SELECT ename FROM emp;
  TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  ename_table ename_table_type;
BEGIN
  --打开游标
  OPEN cur_emp;
  FETCH cur_emp BULK COLLECT INTO ename_table;
  FOR i IN 1..ename_table.COUNT LOOP
    dbms_output.put_line('员工姓名：' || ename_table(i));
  END LOOP;
  --关闭游标
  CLOSE cur_emp;
END;

示例三：在显式游标中使用FETCH...BULK COLLECT INTO...LIMIT语句提取部分数据
当使用FETCH...BULK COLLECT INTO语句提取数据时，默认情况下会提取结果集的所有数据。如果结果集含有大量数据，并且使用VARRAY集合变量接收数据，那么可能需要限制每次提取的行数。

DECLARE
  TYPE name_array_type IS VARRAY(5) OF VARCHAR2(10);
  name_array name_array_type;
  CURSOR cur_emp IS
  SELECT ename FROM emp;
  ROWS INTEGER := 5;
  v_count INTEGER := 0;
BEGIN
  --打开游标
  OPEN cur_emp;
  LOOP
    FETCH cur_emp BULK COLLECT INTO name_array
    LIMIT ROWS;
    dbms_output.put('员工姓名：');
    FOR i IN 1..(cur_emp%ROWCOUNT-v_count) LOOP
      dbms_output.put(name_array(i) || ' ');
    END LOOP;
    dbms_output.new_line;
    v_count := cur_emp%ROWCOUNT;
    EXIT WHEN cur_emp%NOTFOUND;
  END LOOP;
  --关闭游标
  CLOSE cur_emp;
END;

示例四：使用游标属性

DECLARE
  CURSOR cur_emp IS
  SELECT ename FROM emp;
  TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  ename_table ename_table_type;
BEGIN
  IF NOT cur_emp%ISOPEN THEN
    --打开游标
    OPEN cur_emp;
  END IF;
  FETCH cur_emp BULK COLLECT INTO ename_table;
  dbms_output.put_line('提取的总计行数：' || cur_emp%ROWCOUNT);
  --关闭游标
  CLOSE cur_emp;
END;

示例五：基于游标定义记录变量

DECLARE
  CURSOR cur_emp IS
  SELECT ename,sal FROM emp;
  TYPE emp_record_type IS RECORD(
    ename emp.ename%TYPE,
    sal emp.sal%TYPE
  );
  emp_record emp_record_type;
BEGIN
  --打开游标
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO emp_record;
    EXIT WHEN cur_emp%NOTFOUND;
    dbms_output.put_line('员工姓名：' || emp_record.ename || '，工资：' || emp_record.sal);
  END LOOP;
  --关闭游标
  CLOSE cur_emp;
END;

参数游标

参数游标是指带有参数的游标。在定义了参数游标之后，当使用不同参数值多次打开游标时，可以生成不同的结果集。
参数游标语法如下：

CURSOR cursor_name(parameter_name datatype) IS select_statement;

示例如下：

DECLARE
  CURSOR cur_emp(dno number) IS
  SELECT ename FROM emp WHERE deptno = dno;
  v_ename emp.ename%TYPE;
BEGIN
  --打开游标
  OPEN cur_emp(20);
  LOOP
    FETCH cur_emp INTO v_ename;
    EXIT WHEN cur_emp%NOTFOUND;
    dbms_output.put_line('员工姓名：' || v_ename);
  END LOOP;
  --关闭游标
  CLOSE cur_emp;
END;

注意，定义参数游标时，游标参数只能指定数据类型，而不能指定长度。另外，定义参数游标时，一定要在游标子查询的WHERE子句中引用该参数，否则失去了定义参数游标的意义。

使用游标更新或删除数据
通过使用显式游标，不仅可以一行一行地处理SELECT语句的结果，而且也可以更新或删除当前游标行的数据。注意，如果要通过游标更新或删除数据，在定义游标时必须要带有FOR UPDATE子句，语法如下：

CURSOR cursor_name(parameter_name datatype)
IS select_statement
FOR UPDATE [OF column_reference] [NOWAIT];

如上所示，FOR UPDATE子句用于在游标结果集数据上加行共享锁，以防止其他用户在相应行上执行DML操作；当SELECT语句引用到多张表时，使用OF子句可以确定哪些表要加锁，如果没有OF子句，则会在SELECT语句所引用的全部表上加锁；NOWAIT子句用于指定不等待锁。在提取了游标数据之后，为了更新或删除当前游标行数据，必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句。语法如下：

UPDATE table_name SET column=... WHERE CURRENT OF cursor_name;
DELETE table_name WHERE CURRENT OF cursor_name;

1、使用游标更新数据

DECLARE
  CURSOR cur_emp IS
  SELECT sal FROM emp FOR UPDATE;
  v_oldsal emp.sal%TYPE;
BEGIN
  --打开游标
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO v_oldsal;
    EXIT WHEN cur_emp%NOTFOUND;
    IF v_oldsal < 2000 THEN
      UPDATE emp SET sal = sal + 100 WHERE CURRENT OF cur_emp;
    END IF;
  END LOOP;
  --关闭游标
  CLOSE cur_emp;
END;

2、使用游标删除数据

DECLARE
  CURSOR cur_emp IS
  SELECT deptno FROM emp FOR UPDATE;
  v_deptno emp.deptno%TYPE;
BEGIN
  --打开游标
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO v_deptno;
    EXIT WHEN cur_emp%NOTFOUND;
    IF v_deptno = 30 THEN
      DELETE FROM emp WHERE CURRENT OF cur_emp;
    END IF;
  END LOOP;
  --关闭游标
  CLOSE cur_emp;
END;

3、使用OF子句在特定表上加行共享锁
如果游标子查询涉及到多张表，那么在默认情况下会在所有修改表行上加行共享锁。为了只在特定表上加行共享锁，需要在FOR UPDATE子句后带有OF子句。

DECLARE
  CURSOR cur_emp IS
  SELECT ename,sal,dname,emp.deptno FROM emp,dept
  WHERE emp.deptno = dept.deptno
  FOR UPDATE OF emp.deptno;
  TYPE emp_record_type IS RECORD(
    ename emp.ename%TYPE,
    sal emp.sal%TYPE,
    dname dept.dname%TYPE,
    deptno emp.deptno%TYPE
  );
  emp_record emp_record_type;
BEGIN
  --打开游标
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO emp_record;
    EXIT WHEN cur_emp%NOTFOUND;
    IF emp_record.deptno = 20 THEN
      UPDATE emp SET sal = sal + 100 WHERE CURRENT OF cur_emp;
    END IF;
  END LOOP;
  --关闭游标
  CLOSE cur_emp;
END;

4、使用NOWAIT子句
使用FOR UPDATE语句对被作用行加锁，如果其他会话已经在被作用行上加锁，那么在默认情况下当前会话要一直等待对象释放锁。通过在FOR UPDATE子句中指定NOWAIT语句，可以避免等待锁。

DECLARE
  CURSOR cur_emp IS
  SELECT ename,sal FROM emp FOR UPDATE;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  --打开游标
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO v_ename,v_sal;
    EXIT WHEN cur_emp%NOTFOUND;
    IF v_sal < 2000 THEN
      UPDATE emp SET sal = sal + 100 WHERE CURRENT OF cur_emp;
    END IF;
  END LOOP;
  --关闭游标
  CLOSE cur_emp;
END;

游标FOR循环

游标FOR循环是在PL/SQL块中使用游标最简单的方式，简化了对游标的处理。当使用游标FOR循环时，Oracle会隐含地打开游标、提取游标数据并关闭游标。使用游标FOR循环的语法如下：

FOR record_name IN cursor_name LOOP
  statement1;
  statement2;
END LOOP;

1、使用游标FOR循环
当使用游标开发PL/SQL应用程序时，为了简化程序代码，建议大家使用游标FOR循环。

DECLARE
  CURSOR cur_emp IS
  SELECT ename,sal FROM emp;
BEGIN
  FOR emp_record IN cur_emp LOOP
    dbms_output.put_line('第' || cur_emp%ROWCOUNT || '个雇员：' || emp_record.ename);
  END LOOP;
END;

2、在游标FOR循环中直接使用子查询
当使用游标FOR循环时，习惯作法是首先在定义部分定义游标，然后在游标FOR循环中引用该游标。如果在使用游标FOR循环时不需要使用任何游标属性，那么可以直接在游标FOR循环中使用子查询。

BEGIN
  FOR emp_record IN (
    SELECT ename,sal FROM emp
  ) LOOP
    dbms_output.put_line(emp_record.ename);
  END LOOP;
END;

使用动态游标

在PL/SQL块中使用游标变量包含定义游标变量、打开游标、提取游标数据、关闭游标等四个阶段。具体步骤如下：
（1）定义REF CURSOR类型和游标变量
语法如下：

TYPE ref_type_name IS REF CURSOR [RETURN return_type];
cursor_variable ref_type_name;

（2）打开游标
语法如下：

OPEN cursor_variable FOR select_statement;

（3）提取游标数据
语法如下：

语法一：FETCH cursor_variable INTO variable1,variable2,...;
语法二：FETCH cursor_variable BULK COLLECT INTO collect1,collect2,...[LIMIT rows];

（4）关闭游标变量

CLOSE cursor_variable;

示例一：在定义REF CURSOR类型时不指定RETURN子句
如果在定义REF CURSOR类型时不指定RETURN子句，那么在打开游标时可以指定任何的SELECT语句。

DECLARE
  TYPE refcur_emp_type IS REF CURSOR;
  cur_emp refcur_emp_type;
  emp_record emp%ROWTYPE;
BEGIN
  --打开游标
  OPEN cur_emp FOR SELECT * FROM emp;
  LOOP
    FETCH cur_emp INTO emp_record;
    EXIT WHEN cur_emp%NOTFOUND;
    dbms_output.put_line('第' || cur_emp%ROWCOUNT || '个雇员：' || emp_record.ename);
  END LOOP;
  --关闭游标
  CLOSE cur_emp;
END;

示例二：在定义REF CURSOR类型时指定RETURN子句

DECLARE
  TYPE emp_record_type IS RECORD(
    ename emp.ename%TYPE,
    sal emp.sal%TYPE
  );
  TYPE refcur_emp_type IS REF CURSOR;
  cur_emp refcur_emp_type;
  emp_record emp_record_type;
BEGIN
  --打开游标
  OPEN cur_emp FOR
  SELECT ename,sal FROM emp;
  LOOP
    FETCH cur_emp INTO emp_record;
    EXIT WHEN cur_emp%NOTFOUND;
    dbms_output.put_line('第' || cur_emp%ROWCOUNT || '个雇员：' || emp_record.ename);
  END LOOP;
  --关闭游标
  CLOSE cur_emp;
END;

使用CURSOR表达式

CURSOR表达式是Oracle9i新增加的特征，用于返回嵌套游标。在Oracle9i之前，使用显式游标时，结果集只能包含普通数据；从Oracle9i开始，结果集不仅可以包含普通数据，而且允许包含嵌套游标的数据。使用CURSOR表达式的语法如下：
CURSOR(subquery)
示例如下：

DECLARE
  TYPE refcursor IS REF CURSOR;
  CURSOR cur_dept(dno NUMBER) IS
  SELECT d.dname,CURSOR(
    SELECT ename,sal FROM emp WHERE deptno = d.deptno
  )
  FROM dept d WHERE d.deptno = dno;
  cur_emp refcursor;
  v_dname dept.dname%TYPE;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  --打开游标
  OPEN cur_dept(20);
  LOOP
    FETCH cur_dept INTO v_dname,cur_emp;
    EXIT WHEN cur_dept%NOTFOUND;
    dbms_output.put_line('部门名：' || v_dname);
    LOOP
      FETCH cur_emp INTO v_ename,v_sal;
      EXIT WHEN cur_emp%NOTFOUND;
      dbms_output.put_line('雇员名：' || v_ename || '，工资：' || v_sal);
    END LOOP;
  END LOOP;
  --关闭游标
  CLOSE cur_dept;
END;