Oracle开发基础-游标

IT小Chen 2021-04-13

646

---参考：《Oracle 11g SQL和 PLSQL从入门到精通》

一：隐含游标

二：显示游标

当执行SELECT、INSERT、UPDATE、以及DELETE语句时，Oracle Server会为这些SQL语句分配相应的上下文区(Context Area)，并且上下文区解析并执行相应的SQL语句。

游标是指向上下文区的指针。

游标包含隐含游标和显示游标两种类型。

一：隐含游标

1.1 SQL%ISOPEN

1.2 SQL%FOUND

1.3 SQL%NOTFOUND

1.4 SQL%ROWCOUNT

隐含游标又被称为SQL游标，专门用于处理SELECT INTO、INSERT、UPDATE、DELETE语句。

显示游标多处理多行的SELECT语句。

当在PL/SQL块中执行INSERT、UPDATE、DELETE语句时，为了取得DML语句作用的结果，需要使用SQL游标属性。

1.SQL%ISOPEN

该游标属性用于确定SQL游标是否已经打开。当PL/SQL块中执行SELECT INTO、INSERT、UPDATE、DELETE语句时，Oracle会隐含的打开游标，并且在语句执行完成之后隐含的关闭游标。

所有对于开发人员来说该属性的值永远都是FLASE，并且在开发PL/SQL应用时不需要使用该游标属性。

2.SQL%FOUND

该游标属性用于确定SQL语句执行是否成功。

SQL语句执行是否成功是根据作用行来判断的，当SQL语句有作用行时，其属性值为TRUE；

当SQL语句没有作用行时，其属性值为FALSE。

示例：

SQL> set serveroutput on
DECLARE
  v_deptno emp.deptno%TYPE := &no;
BEGIN
  UPDATE emp SET sal = sal * 1.1 WHERE deptno = v_deptno;
  IF SQL%FOUND THEN
    dbms_output.put_line('update ' || SQL%ROWCOUNT || ' line');
  ELSE
    dbms_output.put_line('There are no employees in this department');
  END IF;
END;
/
Enter value for no: 10
old   2:   v_deptno emp.deptno%TYPE := &no;
new   2:   v_deptno emp.deptno%TYPE := 10;
update 3 line
PL/SQL procedure successfully completed.

3.SQL%NOTFOUND

该游标属性用于确定SQL语句执行是否成功。

SQL语句执行是否成功是根据作用行来判断的，当SQL语句有作用行时，其属性值为FALSE；

当SQL语句没有作用行时，其属性值为TRUE。

示例：

SQL> set serveroutput on
DECLARE
  v_deptno emp.deptno%TYPE := &no;
BEGIN
  UPDATE emp SET sal = sal * 1.1 WHERE deptno = v_deptno;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('There are no employees in this department');
  ELSE
     dbms_output.put_line('update ' || SQL%ROWCOUNT || ' line');
  END IF;
END;
/
Enter value for no: 10
old   2:   v_deptno emp.deptno%TYPE := &no;
new   2:   v_deptno emp.deptno%TYPE := 10;
update 3 line
 
PL/SQL procedure successfully completed.

4 SQL%ROWCOUNT

该游标属性用于返回SQL语句所作用的总计行数。

SQL> set serveroutput on
DECLARE
  v_deptno emp.deptno%TYPE := &no;
BEGIN
  UPDATE emp set sal = sal * 1.1 where deptno = v_deptno;
  dbms_output.put_line('update '||SQL%ROWCOUNT || ' lines');
END;
 
Enter value for no: 10
old   2:   v_deptno emp.deptno%TYPE := &no;
new   2:   v_deptno emp.deptno%TYPE := 10;
update 3 lines
 
PL/SQL procedure successfully completed.

二：显示游标

2.1使用显示游标

2.2游标FOR循环

2.3参数游标

2.4更新或删除游标行

2.5游标变量

2.6使用批量提取

2.7使用CURSOR表达式

隐含游标用于处理单行SELECT INTO和DML语句。

显示游标用于处理SELECT返回的多行数据。

显示游标属性：

%ISOPEN、%FOUND、%NOTFOUND和%ROWCOUNT四个属性。

(1)%ISOPEN

该属性用于检测游标是否已经打开。如果游标已经打开，则返回TRUE，否则返回FALSE。

IF NOT emp_cursor%ISOPEN THEN
  OPEN emp_cursor;
END IF;

(2)%FOUND

该属性用于检测游标结果集是否存在数据。如果存在数据，则返回TRUE，否则返回FALSE。

LOOP
  FETCH emp_cursor INTO v_name,v_sal;
  EXIT WHEN NOT emp_cursor%FOUND;
  ...
END LOOP;

(3)%NOTFOUND

该属性用于检测游标结果集是否不存在数据。如果不存在数据，则返回TRUE，否则返回FALSE。

LOOP
  FETCH emp_cursor INTO v_name,v_sal;
  EXIT WHEN emp_cursor%NOTFOUND;
  ...
END LOOP;

(4)%ROWCOUNT

该属性用于返回已提取的实际行数。

LOOP
  FETCH emp_cursor INTO v_ename,v_deptno;
  EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%ROWCOUNT=5;
  ...
END LOOP;

2.1使用显示游标

显示游标专用于处理SELECT语句返回的多行数据，使用显示游标包括定义游标、打开游标、提前数据和关闭游标四个阶段。

定义游标：定义游标用于指定游标所对应的SELECT语句，语法如下：

CURSOR cursor_name IS select_statement;

打开游标：打开游标用于执行所对应的SELECT语句，并将行数据存放在游标结果集。语法如下：

OPEN cursor_name;

提取数据：提前数据用于将结果集中的行数据存放到PL/SQL变量。语法如下：

FETCH cursor_name INTO variable1[,variables2,...];

关闭游标：关闭游标用于释放游标结果集的数据。语法如下：

CLOSE cursor_name;

注意：当使用FETCH INTO提取数据时，每次只能提前一行数据，为了提前结果集的所有数据，需要使用循环语句。

示例一：使用标量变量接收游标数据

SQL> set serveroutput on
DECLARE
  CURSOR emp_cursor IS
    SELECT ename, job, sal FROM emp WHERE deptno = &dno;
  v_ename emp.ename%TYPE;
  v_sal   emp.sal%TYPE;
  v_job   emp.job%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor
      INTO v_ename, v_job, v_sal;
    EXIT WHEN emp_cursor%NOTFOUND;
    dbms_output.put_line('Ename is:' || v_ename || ',Job is:' || v_job ||
                         ',Sal is:' || v_sal);
  END LOOP;
  CLOSE emp_cursor;
END;
/
Enter value for dno: 10
old   3:     SELECT ename, job, sal FROM emp WHERE deptno = &dno;
new   3:     SELECT ename, job, sal FROM emp WHERE deptno = 10;
Ename is:CLARK,Job is:MANAGER,Sal is:2695
Ename is:KING,Job is:PRESIDENT,Sal is:5500
Ename is:MILLER,Job is:CLERK,Sal is:1430
 
PL/SQL procedure successfully completed.

示例二：使用PL/SQL记录变量接收游标数据

SQL> set serveroutput on
DECLARE
  CURSOR emp_cursor IS
    SELECT ename, sal from emp ORDER BY sal DESC;
  emp_record emp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor
      INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%ROWCOUNT > &N;
    dbms_output.put_line('ename is:' || emp_record.ename || ',sal is:' ||
                         emp_record.sal);
  END LOOP;
  CLOSE emp_cursor;
END;
/
Enter value for n: 3
old  10:     EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%ROWCOUNT > &N;
new  10:     EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%ROWCOUNT > 3;
ename is:KING,sal is:5500
ename is:FORD,sal is:3000
ename is:SCOTT,sal is:3000

示例三：使用PL/SQL集合变量接收游标数据

SQL> set serveroutput on
DECLARE
  CURSOR emp_cursor IS
    SELECT ename, sal from emp WHERE lower(job) = lower('&job');
  TYPE emp_table_type IS TABLE OF emp_cursor%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_table emp_table_type;
  i         INT;
BEGIN
  OPEN emp_cursor;
  LOOP
    i := emp_cursor%ROWCOUNT + 1;
    FETCH emp_cursor
      INTO emp_table(i);
    EXIT WHEN emp_cursor%NOTFOUND;
    dbms_output.put_line('ename is:' || emp_table(i).ename || ',sal is:' || emp_table(i).sal);
  END LOOP;
  CLOSE emp_cursor;
END;
/
Enter value for job: manager
old   3:     SELECT ename, sal from emp WHERE lower(job) = lower('&job');
new   3:     SELECT ename, sal from emp WHERE lower(job) = lower('manager');
ename is:JONES,sal is:2975
ename is:BLAKE,sal is:2850
ename is:CLARK,sal is:2695
 
PL/SQL procedure successfully completed.

2.2游标FOR循环

当使用游标FOR循环时，会隐含的打开游标、提前数据并关闭游标。语法如下：

FOR record_name IN cursor_name LOOP
  statement;
  ...
  END LOOP;

示例一：在游标FOR循环中引用已定义游标

SQL> set serveroutput on
DECLARE
  CURSOR emp_cursor IS
    SELECT ename, hiredate FROM emp ORDER BY hiredate DESC;
BEGIN
  FOR emp_record IN emp_cursor LOOP
    dbms_output.put_line('ename is :' || emp_record.ename ||
                         ',hiredate is:' || emp_record.hiredate);
    EXIT WHEN emp_cursor%ROWCOUNT = &N;
  END LOOP;
END;
/
Enter value for n: 3
old   8:     EXIT WHEN emp_cursor%ROWCOUNT = &N;
new   8:     EXIT WHEN emp_cursor%ROWCOUNT = 3;
ename is :ADAMS,hiredate is:23-MAY-87
ename is :SCOTT,hiredate is:19-APR-87
ename is :MILLER,hiredate is:23-JAN-82
 
PL/SQL procedure successfully completed.

示例二：在游标FOR循环中直接引用子查询

SQL> set serveroutput on
BEGIN
  FOR emp_record IN (SELECT ename, hiredate, rownum
                       FROM emp
                      ORDER BY hiredate) LOOP
    dbms_output.put_line('ename is:' || emp_record.ename ||
                         ',hiredate is:' || emp_record.hiredate);
    EXIT WHEN emp_record.rownum = &n;
  END LOOP;
END;
/
Enter value for n: 3
old   7:     EXIT WHEN emp_record.rownum = &n;
new   7:     EXIT WHEN emp_record.rownum = 3;
ename is:SMITH,hiredate is:17-DEC-80
ename is:ALLEN,hiredate is:20-FEB-81
ename is:WARD,hiredate is:22-FEB-81
 
PL/SQL procedure successfully completed.

2.3参数游标

指带有参数的游标，使用不同参数值可以生产不同的游标结果集。

示例：

SQL> set serveroutput on
DECLARE
  CURSOR emp_cursor(dno NUMBER) IS
    SELECT ename, job FROM emp WHERE deptno = dno;
BEGIN
  FOR emp_record IN emp_cursor(&dno) LOOP
    dbms_output.put_line('ename is:' || emp_record.ename || 'job is:' ||
                         emp_record.job);
  END LOOP;
END;
/
Enter value for dno: 20
old   5:   FOR emp_record IN emp_cursor(&dno) LOOP
new   5:   FOR emp_record IN emp_cursor(20) LOOP
ename is:SMITHjob is:CLERK
ename is:JONESjob is:MANAGER
ename is:SCOTTjob is:ANALYST
ename is:ADAMSjob is:CLERK
ename is:FORDjob is:ANALYST
 
PL/SQL procedure successfully completed.

2.4更新或删除游标行

定义游标必须带有FOR UPDATE子句，并且在更新或删除游标行时必须带有WHERE CURRENT OF子句。

示例一：更新游标行

DECLARE
  CURSOR emp_cursor IS
    SELECT ename, sal, deptno FROM emp FOR UPDATE;
  dno INT := &no;
BEGIN
  FOR emp_record IN emp_cursor LOOP
    IF emp_record.deptno = dno THEN
      dbms_output.put_line('ename is:' || emp_record.ename || ',sal is:' ||
                           emp_record.sal);
      UPDATE emp set sal = sal * 1.1 WHERE CURRENT OF emp_cursor;
    END IF;
  END LOOP;
END;
/
 
Enter value for no: 10
old   4:   dno INT := &no;
new   4:   dno INT := 10;
ename is:CLARK,sal is:2695
ename is:KING,sal is:5500
ename is:MILLER,sal is:1430
 
PL/SQL procedure successfully completed.

示例二：删除游标行

DECLARE
  CURSOR emp_cursor IS
    SELECT ename FROM emp FOR UPDATE;
  name VARCHAR2(10) := lower('&name');
BEGIN
  FOR emp_record IN emp_cursor LOOP
    IF lower(emp_record.ename) = name THEN
      DELETE FROM emp WHERE CURRENT OF emp_cursor;
    ELSE
      dbms_output.put_line('name is:' || emp_record.ename);
    END IF;
  END LOOP;
END;
/
Enter value for name: scott
old   4:   name VARCHAR2(10) := lower('&name');
new   4:   name VARCHAR2(10) := lower('scott');
name is:SMITH
name is:ALLEN
name is:WARD
name is:JONES
name is:MARTIN
name is:BLAKE
name is:CLARK
name is:KING
name is:TURNER
name is:ADAMS
name is:JAMES
name is:FORD
name is:MILLER
 
PL/SQL procedure successfully completed.

示例三：使用OF子句在特定表上加行共享锁

DECLARE
  CURSOR emp_cursor IS
    SELECT a.dname, b.ename
      FROM dept a
      JOIN emp b
        ON a.deptno = b.deptno
       FOR UPDATE OF b.deptno;
  name varchar2(10) := LOWER('&name');
BEGIN
  FOR emp_record IN emp_cursor LOOP
    IF LOWER(emp_record.dname) = name THEN
      dbms_output.put_line('ename is:' || emp_record.ename);
      DELETE FROM emp WHERE CURRENT OF emp_cursor;
    END IF;
  END LOOP;
END;
/
 
Enter value for name: SALES
old   8:   name varchar2(10) := LOWER('&name');
new   8:   name varchar2(10) := LOWER('SALES');
ename is:ALLEN
ename is:WARD
ename is:MARTIN
ename is:BLAKE
ename is:TURNER
ename is:JAMES
 
PL/SQL procedure successfully completed.

2.5游标变量

游标变量是基于REF CURSOR类型所定义的变量，它实际是指向内存地址的指针。

示例一：使用无返回类型的游标变量

DECLARE
  TYPE ref_cursor_type IS REF CURSOR;
  ref_cursor ref_cursor_type;
  v1  NUMBER(6);
  v2  VARCHAR2(10);
BEGIN
  OPEN ref_cursor FOR
    SELECT &col1 col1, &col2 col2 FROM &table WHERE &cond;
  LOOP
    FETCH ref_cursor
      INTO v1, v2;
    EXIT WHEN ref_cursor%NOTFOUND;
    dbms_output.put_line('col1=' || v1 || ',col2=' || v2);
  END LOOP;
  CLOSE ref_cursor;
END;
/
Enter value for col1: empno
Enter value for col2: ename
Enter value for table: emp
Enter value for cond: deptno=10
old   8:     SELECT &col1 col1, &col2 col2 FROM &table WHERE &cond;
new   8:     SELECT empno col1, ename col2 FROM emp WHERE deptno=10;
col1=7782,col2=CLARK
col1=7839,col2=KING
col1=7934,col2=MILLER
 
PL/SQL procedure successfully completed.

示例二：使用有返回类型的游标变量

DECLARE
  TYPE emp_cursor_type IS REF CURSOR RETURN emp%ROWTYPE;
  emp_cursor emp_cursor_type;
  emp_record emp%ROWTYPE;
BEGIN
  OPEN emp_cursor FOR
    SELECT * FROM emp WHERE deptno = &dno;
  LOOP
    FETCH emp_cursor
      INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    dbms_output.put_line('ename is:' || emp_record.ename || ',sal is:' ||
                         emp_record.sal);
  END LOOP;
  CLOSE emp_cursor;
END;
/
Enter value for dno: 20
old   7:     SELECT * FROM emp WHERE deptno = &dno;
new   7:     SELECT * FROM emp WHERE deptno = 20;
ename is:SMITH,sal is:800
ename is:JONES,sal is:2975
ename is:ADAMS,sal is:1100
ename is:FORD,sal is:3000
 
PL/SQL procedure successfully completed.

2.6使用批量提取

示例一：使用FETCH...BULK COLLECT提前所有数据

DECLARE
  CURSOR emp_cursor IS
    SELECT * FROM emp WHERE LOWER(job) = LOWER('&job');
  TYPE emp_table_type IS TABLE OF emp%ROWTYPE;
  emp_table emp_table_type;
BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor BULK COLLECT
    INTO emp_table;
  CLOSE emp_cursor;
  FOR i IN 1 .. emp_table.COUNT LOOP
    dbms_output.put_line('ename is:' || emp_table(i).ename || ',sal is:' || emp_table(i).sal);
  END LOOP;
END;
/
 
Enter value for job: clerk
old   3:     SELECT * FROM emp WHERE LOWER(job) = LOWER('&job');
new   3:     SELECT * FROM emp WHERE LOWER(job) = LOWER('clerk');
ename is:SMITH,sal is:800
ename is:ADAMS,sal is:1100
ename is:MILLER,sal is:1573
 
PL/SQL procedure successfully completed.

示例二：使用LIMIT子句限制提取行数

DECLARE
  CURSOR emp_cursor IS
    SELECT * FROM emp;
  TYPE emp_array_type IS VARRAY(5) OF emp%ROWTYPE;
  emp_array emp_array_type;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor BULK COLLECT
      INTO emp_array LIMIT &rows;
    FOR i IN 1 .. emp_array.COUNT LOOP
      dbms_output.put_line('ename is:' || emp_array(i).ename || ',sal is:' || emp_array(i).sal);
    END LOOP;
    EXIT WHEN emp_cursor%NOTFOUND;
  END LOOP;
  CLOSE emp_cursor;
END;
/
 
Enter value for rows: 3
old  10:       INTO emp_array LIMIT &rows;
new  10:       INTO emp_array LIMIT 3;
ename is:SMITH,sal is:800
ename is:JONES,sal is:2975
ename is:CLARK,sal is:2964.5
ename is:KING,sal is:6050
ename is:ADAMS,sal is:1100
ename is:FORD,sal is:3000
ename is:MILLER,sal is:1573
 
PL/SQL procedure successfully completed

2.7使用CURSOR表达式

CURSOR表达式用于实现嵌套游标。语法：CURSOR(subquery)

示例：

DECLARE
  CURSOR dept_cursor(no NUMBER) IS
    SELECT a.dname, CURSOR (SELECT * FROM emp WHERE deptno = a.deptno)
      FROM dept a
     WHERE a.deptno = no;
  TYPE ref_cursor_type IS REF CURSOR;
  emp_cursor ref_cursor_type;
  emp_record emp%ROWTYPE;
  v_dname    dept.dname%TYPE;
BEGIN
  OPEN dept_cursor(&dno);
  LOOP
    FETCH dept_cursor
      INTO v_dname, emp_cursor;
    EXIT WHEN dept_cursor%NOTFOUND;
    dbms_output.put_line('dname is :' || v_dname);
    LOOP
      FETCH emp_cursor
        INTO emp_record;
      EXIT WHEN emp_cursor%NOTFOUND;
      dbms_output.put_line('ename is:' || emp_record.ename ||
                           ',job is:' || emp_record.job);
    END LOOP;
  END LOOP;
  CLOSE dept_cursor;
END;
/
Enter value for dno: 10
old  11:   OPEN dept_cursor(&dno);
new  11:   OPEN dept_cursor(10);
dname is :ACCOUNTING
ename is:CLARK,job is:MANAGER
ename is:KING,job is:PRESIDENT
ename is:MILLER,job is:CLERK
 
PL/SQL procedure successfully completed.

更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：

http://blog.itpub.net/29785807/

oracle

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle开发基础-游标

评论