您的位置：首页 > 博客中心 > 数据库 >

[推荐]ORACLE PL/SQL编程之四：把游标说透(不怕做不到，只怕想不到)

时间：2022-03-14 03:34

原文:

[推荐]ORACLE PL/SQL编程之四：

把游标说透(不怕做不到，只怕想不到)

继上两篇：

得到了大家的强力支持，感谢。接下来再下猛药，介绍下一篇，大家一定要支持与推荐呀~！我也才有动力写后面的。

本篇主要内容如下：

4.1 游标概念

4.1.1 处理显式游标

4.1.2 处理隐式游标

4.1.3 关于 NO_DATA_FOUND 和 %NOTFOUND的区别

4.1.4 使用游标更新和删除数据

4.2 游标变量

4.2.1 声明游标变量

4.2.2 游标变量操作

SQL语句

游标

非查询语句

隐式的

结果是单行的查询语句

隐式的或显示的

结果是多行的查询语句

显示的

属性

值

SELECT

INSERT

UPDATE

DELETE

SQL%ISOPEN

FALSE

FALSE

FALSE

FALSE

SQL%FOUND

TRUE

有结果

成功

成功

SQL%FOUND

FALSE

没结果

失败

失败

SQL%NOTFUOND

TRUE

没结果

失败

失败

SQL%NOTFOUND

FALSE

有结果

成功

失败

SQL%ROWCOUNT

返回行数，只为1

插入的行数

修改的行数

删除的行数

例11: 删除EMPLOYEES表中某部门的所有员工，如果该部门中已没有员工，则在DEPARTMENT表中删除该部门。

DECLARE
    V_deptno department_id%TYPE :=&p_deptno;
BEGIN
    DELETE FROM employees WHERE department_id=v_deptno;
    IF SQL%NOTFOUND THEN
        DELETE FROM departments WHERE department_id=v_deptno;
    END IF;
END;

例12: 通过隐式游标SQL的%ROWCOUNT属性来了解修改了多少行。
DECLARE
   v_rows NUMBER;
BEGIN
--更新数据
   UPDATE employees SET salary = 30000
   WHERE department_id = 90 AND job_id = ‘AD_VP‘;
--获取默认游标的属性值
   v_rows := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE(‘更新了‘||v_rows||‘个雇员的工资‘);
--回退更新，以便使数据库的数据保持原样
   ROLLBACK;
END;

4.1.3 关于 NO_DATA_FOUND 和 %NOTFOUND的区别

SELECT … INTO 语句触发 NO_DATA_FOUND；

当一个显式游标的WHERE子句未找到时触发%NOTFOUND；

当UPDATE或DELETE 语句的WHERE 子句未找到时触发 SQL%NOTFOUND；在提取循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND.4.1.4 使用游标更新和删除数据

游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。

为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使ORACLE锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。

语法：

SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]

如果另一个会话已对活动集中的行加了锁，那么SELECT FOR UPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：

ORA-0054 ：resource busy and acquire with nowait specified.

如果使用 FOR UPDATE 声明游标，则可在DELETE和UPDATE 语句中使用

WHERE CURRENT OF cursor_name子句，修改或删除游标结果集合当前行对应的数据库表中的数据行。

例13：从EMPLOYEES表中查询某部门的员工情况，将其工资最低定为 1500；

DECLARE
    V_deptno employees.department_id%TYPE :=&p_deptno;
    CURSOR emp_cursor
  IS
  SELECT employees.employee_id, employees.salary
    FROM employees WHERE employees.department_id=v_deptno
  FOR UPDATE NOWAIT;
BEGIN
    FOR emp_record IN emp_cursor LOOP
    IF emp_record.salary < 1500 THEN
        UPDATE employees SET salary=1500
    WHERE CURRENT OF emp_cursor;
    END IF;
    END LOOP;
--    COMMIT;
END;

例14：将EMPLOYEES表中部门编码为90、岗位为AD_VP的雇员的工资都更新为2000元；

DECLARE
   v_emp_record employees%ROWTYPE;
   CURSOR c1
   IS
      SELECT * FROM employees FOR UPDATE;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO v_emp_record;
      EXIT WHEN c1%NOTFOUND;
      IF v_emp_record.department_id = 90 AND
         v_emp_record.job_id = ‘AD_VP‘
      THEN
         UPDATE employees SET salary = 20000
         WHERE CURRENT OF c1;  --更新当前游标行对应的数据行
      END IF;
   END LOOP;
   COMMIT;   --提交已经修改的数据
   CLOSE c1;
END;

4.2 游标变量

与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。

4.2.1 声明游标变量

游标变量为一个指针，它属于参照类型，所以在声明游标变量类型之前必须先定义游标变量类型。在PL/SQL中，可以在块、子程序和包的声明区域内定义游标变量类型。

语法格式为：

TYPE ref_type_name IS REF CURSOR
[ RETURN return_type];

其中：ref_type_name为新定义的游标变量类型名称；

return_type 为游标变量的返回值类型，它必须为记录变量。

在定义游标变量类型时，可以采用强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型。

声明一个游标变量的两个步骤：

步骤一：定义一个REF CURSOU数据类型，如：

TYPE ref_cursor_type IS REF CURSOR;

步骤二：声明一个该数据类型的游标变量，如：

cv_ref REF_CURSOR_TYPE;

例：创建两个强类型定义游标变量和一个弱类型游标变量：

DECLARE
    TYPE deptrecord IS RECORD(
        Deptno departments.department_id%TYPE,
        Dname departments.department_name%TYPE,
        Loc departments.location_id%TYPE
    );
    TYPE deptcurtype IS REF CURSOR RETURN departments%ROWTYPE;
    TYPE deptcurtyp1 IS REF CURSOR RETURN deptrecord;
    TYPE curtype IS REF CURSOR;
    Dept_c1 deptcurtype;
    Dept_c2 deptcurtyp1;
    Cv curtype;

4.2.2 游标变量操作

与游标一样，游标变量操作也包括打开、提取和关闭三个步骤。

1．打开游标变量

打开游标变量时使用的是OPEN…FOR 语句。格式为：

OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;

其中：cursor_variable_name为游标变量，host_cursor_variable_name为PL/SQL主机环境（如OCI: ORACLE Call Interface，Pro*c 程序等）中声明的游标变量。

OPEN…FOR 语句可以在关闭当前的游标变量之前重新打开游标变量，而不会导致CURSOR_ALREAD_OPEN异常错误。新打开游标变量时，前一个查询的内存处理区将被释放。

2．提取游标变量数据

使用FETCH语句提取游标变量结果集合中的数据。格式为：

FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]…| record_variable};

其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称；variable和record_variable分别为普通变量和记录变量名称。

3．关闭游标变量

CLOSE语句关闭游标变量，格式为：

CLOSE {cursor_variable_name | :host_cursor_variable_name}

其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称，如果应用程序试图关闭一个未打开的游标变量，则将导致INVALID_CURSOR异常错误。

例15：强类型参照游标变量类型

DECLARE
    TYPE emp_job_rec IS RECORD(
        Employee_id employees.employee_id%TYPE,
        Employee_name employees.first_name%TYPE,
        Job_title employees.job_id%TYPE
    );
    TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec;
    Emp_refcur emp_job_refcur_type ;
    Emp_job emp_job_rec;
BEGIN
    OPEN emp_refcur FOR
    SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id
  FROM employees
  ORDER BY employees.department_id;

    FETCH emp_refcur INTO emp_job;
    WHILE emp_refcur%FOUND LOOP
       DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||‘: ‘||emp_job.employee_name||‘ is a ‘||emp_job.job_title);
    FETCH emp_refcur INTO emp_job;
    END LOOP;
END;

例16：弱类型参照游标变量类型

PROMPT
PROMPT ‘What table would you like to see?‘
ACCEPT tab PROMPT ‘(D)epartment, or (E)mployees:‘

DECLARE
    Type refcur_t IS REF CURSOR;
    Refcur refcur_t;
    TYPE sample_rec_type IS RECORD (
        Id number,
        Description VARCHAR2 (30)
    );
    sample sample_rec_type;
    selection varchar2(1) := UPPER (SUBSTR (‘&tab‘, 1, 1));
BEGIN
    IF selection=‘D‘ THEN
        OPEN refcur FOR
    SELECT departments.department_id, departments.department_name FROM departments;
        DBMS_OUTPUT.PUT_LINE(‘Department data‘);
    ELSIF selection=‘E‘ THEN
        OPEN refcur FOR
    SELECT employees.employee_id, employees.first_name||‘ is a ‘||employees.job_id FROM employees;
        DBMS_OUTPUT.PUT_LINE(‘Employee data‘);
    ELSE
        DBMS_OUTPUT.PUT_LINE(‘Please enter ‘‘D‘‘ or ‘‘E‘‘‘);
        RETURN;
    END IF;
    DBMS_OUTPUT.PUT_LINE(‘----------------------‘);
    FETCH refcur INTO sample;
    WHILE refcur%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(sample.id||‘: ‘||sample.description);
        FETCH refcur INTO sample;
    END LOOP;
    CLOSE refcur;
END;

例17：使用游标变量（没有RETURN子句）

DECLARE
--定义一个游标数据类型
   TYPE emp_cursor_type IS REF CURSOR;
--声明一个游标变量
   c1 EMP_CURSOR_TYPE;
--声明两个记录变量
   v_emp_record employees%ROWTYPE;
   v_reg_record regions%ROWTYPE;

BEGIN
   OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
   LOOP
      FETCH c1 INTO v_emp_record;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||‘的雇佣日期是‘
                            ||v_emp_record.hire_date);
   END LOOP;
--将同一个游标变量对应到另一个SELECT语句
   OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;
   LOOP
      FETCH c1 INTO v_reg_record;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||‘表示‘
                            ||v_reg_record.region_name);
   END LOOP;
   CLOSE c1;
END;

例18：使用游标变量（有RETURN子句）

DECLARE
--定义一个与employees表中的这几个列相同的记录数据类型
   TYPE emp_record_type IS RECORD(
        f_name   employees.first_name%TYPE,
        h_date   employees.hire_date%TYPE,
        j_id     employees.job_id%TYPE);
--声明一个该记录数据类型的记录变量
   v_emp_record EMP_RECORD_TYPE;
--定义一个游标数据类型
   TYPE emp_cursor_type IS REF CURSOR
        RETURN EMP_RECORD_TYPE;
--声明一个游标变量
   c1 EMP_CURSOR_TYPE;
BEGIN
   OPEN c1 FOR SELECT first_name, hire_date, job_id
               FROM employees WHERE department_id = 20;
   LOOP
      FETCH c1 INTO v_emp_record;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(‘雇员名称：‘||v_emp_record.f_name
                ||‘  雇佣日期：‘||v_emp_record.h_date
                ||‘  岗位：‘||v_emp_record.j_id);
   END LOOP;
   CLOSE c1;
END;

原创作品，转贴请注明作者和出处，留此信息。

------------------------------------------------

cnBlobs:CSDN：

作者：EricHu（DB、C\S、B\S、WebService、WCF、PM等）
出处：

Q Q：80368704 E-Mail: 80368704@qq.com
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看