【PL/SQL】 使用游标

一、游标分类

1.隐式游标

①对于SELECT …INTO…语句，一次只能从数据库中获取到一条数据，对于这种类型的DML SQL语句，就是隐式CURSOR

②Select/Update/Insert/Delete操作

2.显式游标：由程序员定义和管理

①对于从数据库中提取多行数据，就需要使用显式CURSOR

②定义游标——CURSOR [CURSOR NAME] IS

③打开游标——OPEN [CURSOR NAME]

④操作数据——FETCH [CURSOR NAME]

⑤关闭游标——CLOSE [CURSOR NAME]

                                   

二、显式游标

·专门用于处理SELECT语句返回的多行数据

·定义游标

①使用显式游标前必须先定义游标

②执行相应的SELECT语句

③语法：

CURSOR cursor_name IS select_statement;

 

·打开游标

①打开显式游标时会执行相应的SQL语句

②将执行结果存放在结果集中

③语法：

OPEN cursor_name;

 

·提取数据

①使用FETCH子句一次提取一条或多条记录

②variable用于接收游标数据的变量

③语法：

一次提取一条记录：

FETCH cursor_name

INTO variable1,variable2,variable3…;

 

一次提取多条记录：

FETCH cursor_name BULK COLLECT

INTO collect1,collect2,collect3...;

 

·关闭游标

①在提取并处理完所有的数据后，必须关闭游标释放结果集

②语法：

CLOSE  cursor_name;

 

·显式游标的属性

①%ISOPEN

确定游标是否已经打开（True or False）

②%FOUND

返回是否从结果集提取到了数据（True or False）

③%NOTFOUND

返回是否从结果集没有提取到数据（True or False）

④%ROWCOUNT

返回到当前为止已经提取到的实际行数

 

实例：

通过显式游标SELECT读取多行数据：

SQL> DECLARE

  2    CURSOR cur_emp IS

  3      SELECT * FROM emp WHERE deptno=&NO;

  4      emp_rec  emp%ROWTYPE;

  5    BEGIN

  6      IF NOT cur_emp%ISOPEN THEN

  7          OPEN cur_emp;

  8      END IF;

  9 

 10      LOOP

 11        FETCH cur_emp INTO emp_rec;

 12        EXIT WHEN cur_emp%NOTFOUND;

 13        dbms_output.put_line(emp_rec.ename ||','||emp_rec.sal||','||emp_rec.deptno);

 14      END LOOP;

 15      CLOSE cur_emp;

 16     END;

 17  /

SMITH,4600,20

JONES,2975,20

ADAMS,1100,20

FORD,3000,20

PL/SQL procedure successfully completed

 

通过FOR循环读取游标数据：

SQL> DECLARE

  2    CURSOR cur_emp IS

  3    SELECT * FROM emp WHERE deptno=&NO;

  4  BEGIN

  5    FOR emp_rec IN cur_emp LOOP

  6      dbms_output.put_line(emp_rec.ename ||','||emp_rec.sal||','||emp_rec.deptno);

  7    END LOOP;

  8  END;

  9  /

SMITH,4600,20

JONES,2975,20

ADAMS,1100,20

FORD,3000,20

PL/SQL procedure successfully completed

 

三、参数游标

·参数游标是指带有参数的游标

·在定义了参数游标之后，当使用不同参数值多次打开游标时，可以生成不同的结果集

·注意，当定义参数游标时，游标参数只能指定数据类型，而不能指定长度

·当定义参数游标时，一定要在游标子查询的where子句中引用该参数，否则就失去了定义参数游标的意义

·语法：

CURSOR  cursor_name

   [(parameter_name  datatype,…]

IS

   Select_statement;

 

OPEN  cursor_name(parameter_value,……);

 

·实例：

SQL> DECLARE

  2    CURSOR  emp_cur(v_deptno NUMBER) IS

  3      SELECT * FROM emp WHERE deptno=v_deptno;

  4    emp_rec   emp%ROWTYPE;

  5  BEGIN

  6    IF NOT emp_cur%ISOPEN THEN

  7      OPEN emp_cur(30);

  8    END IF;

  9    LOOP

 10      FETCH emp_cur INTO emp_rec;

 11      EXIT WHEN emp_cur%NOTFOUND;

 12      dbms_output.put_line(emp_rec.ename ||','||emp_rec.sal ||','||emp_rec.deptno);

 13    END LOOP;

 14  END;

 15  /

ALLEN,1600,30

WARD,1250,30

MARTIN,1250,30

BLAKE,2850,30

TURNER,1500,30

JAMES,950,30

PL/SQL procedure successfully completed

 

 

四、使用游标处理数据

1.使用游标更新和删除数据

①FOR UPDATE 子句

SELECT …

  FROM…

   FOR UPDATE [ OF column_reference ] [ NOWAIT | WAIT n ];

②WHERE CURRENT OF 子句

UPDATE employees

    SET  salary = ...

    WHERE CURRENT OF c_emp_cursor;

 实例：

使用游标更新数据：

drop table t purge;

create table t(id number(4),num varchar2(15),state number(1));

insert into t(id,num,state) select rownum,abs(dbms_random.random),mod(rownum,2)+1 from dual connect by rownum<=1000;

commit;

 

SQL> DECLARE

  2    CURSOR cur_dt IS

  3       SELECT ID,NUM,state FROM t FOR UPDATE OF NUM;

  4    v_id   t.id%TYPE;

  5    v_num  t.num%TYPE;

  6    v_state  t.state%TYPE;

  7    BEGIN

  8      OPEN cur_dt;

  9      LOOP

 10        FETCH cur_dt

 11          INTO v_id,v_num,v_state;

 12        EXIT WHEN cur_dt%NOTFOUND;

 13        UPDATE t

 14            SET NUM = v_state || LPAD (v_num,14,'0')

 15          WHERE CURRENT OF cur_dt;

 16       END LOOP;

 17       COMMIT;

 18       CLOSE cur_dt;

 19  END;

 20  /

PL/SQL procedure successfully completed

 

实例：

使用游标删除数据：

SQL> DECLARE

  2    CURSOR cur_dt IS

  3      SELECT ID,NUM FROM t FOR UPDATE;

  4    v_id   t.id%TYPE;

  5    v_num t.num%TYPE;

  6    v_type NUMBER;

  7  BEGIN

  8    OPEN cur_dt;

  9    LOOP

 10      FETCH cur_dt

 11        INTO v_id,v_num;

 12        EXIT WHEN cur_dt%NOTFOUND;

 13        v_type :=to_number(SUBSTR(v_num,-1));

 14        IF MOD(v_type,2)=1 THEN

 15          DELETE t WHERE CURRENT OF cur_dt;

 16        END IF;

 17     END LOOP;

 18     COMMIT;

 19     CLOSE cur_dt;

 20  END;

 21  /

PL/SQL procedure successfully completed

 

五、游标FOR循环

①使用FOR循环可以简化游标的使用

②oracle内部会隐含打开、提取和关闭游标

③游标不需要DECLARE

④语法：

FOR record_name IN cursor_name | select_statement   LOOP

Statement1;

Statement2;

……

END LOOP;

 

实例：

SQL> BEGIN

  2    FOR emp_rec IN(SELECT * FROM emp WHERE deptno=&NO) LOOP

  3      dbms_output.put_line(emp_rec.ename ||','||emp_rec.sal ||','||emp_rec.deptno);

  4    END LOOP;

  5  END;

  6  /

SMITH,4600,20

JONES,2975,20

ADAMS,1100,20

FORD,3000,20

PL/SQL procedure successfully completed

六、CURSOR 表达式

①CURSOR 表达式是9i新增的特性

②它用于返回嵌套游标

③使用它可以处理更加复杂的基于多张表的关联数据

1.游标变量

①在PL/SQL中用于存放指向内存地址的指针

②在定义时指定其对应的SELECT语句

③可以使用在OCI、Pro*C/C++、FORMS和REPORTS中

2.游标变量的使用过程

①定义游标

语法：

TYPE ref_type_name IS REF CURSOR [RETURN return_type];

cursor_variable ref_type_name;

解析：

·ref_type_name:用于指定自定义类型名

·RETURN:用于指定返回结果的数据类型

·cursor_variable:用于指定游标变量名

②打开游标

语法：

OPEN cursor_variable FOR select_statement;

·select_statement: 用于指定游标所对应的SELECT语句

③提取游标数据

语法：

FETCH cursor_variable INTO variable1,variable2,...;

FETCH cursor_variable BULK COLLECT

INTO collect1,collect2,...[LIMIT ROWS];

·variable：用于指定接收游标数据的变量

·collect：用于指定接收游标结果的集合变量

④关闭游标

CLOSE cursor_variable;

实例：

SQL> DECLARE

  2    TYPE emp_cursor IS REF CURSOR;

  3    my_cursor  emp_cursor;

  4    v_ename  emp.ename%TYPE;

  5    v_sal  emp.sal%TYPE;

  6    v_deptno  dept.deptno%TYPE :=&deptno;

  7    v_dname  dept.dname%TYPE;

  8  BEGIN

  9    SELECT dname INTO v_dname FROM dept WHERE deptno=v_deptno;

 10    dbms_output.put_line('Department:' || v_dname);

 11    OPEN my_cursor FOR

 12      SELECT ename,sal FROM emp WHERE deptno=v_deptno;

 13    LOOP

 14      FETCH my_cursor

 15        INTO v_ename,v_sal;

 16        EXIT WHEN my_cursor%NOTFOUND;

 17        dbms_output.put_line('--name: ' || v_ename || 'salary:' || v_sal);

 18    END LOOP;

 19    CLOSE my_cursor;

 20  END;

 21  /

Department:RESEARCH

--name: SMITHsalary:4600

--name: JONESsalary:2975

--name: ADAMSsalary:1100

--name: FORDsalary:3000

PL/SQL procedure successfully completed