游标cursor

最新推荐文章于 2021-08-17 12:16:00 发布

weixin_30621711

最新推荐文章于 2021-08-17 12:16:00 发布

阅读量169

点赞数

原文链接：http://www.cnblogs.com/ct-blog/p/6384446.html

版权

1 游标的相关概念及特性

1.1 定义

映射在结果集中某一行数据的具体位置，类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行，然后根据业务需求对该行进行相应特定的操作。

1.2 游标的分类

在Oracel中，游标可以分为两大类：静态游标和 REF游标（动态游标）。REF游标是一种引用类型，类似于指针。而静态游标又分为显式游标和隐式游标两种。

1.3 游标的使用过程

显式游标：声明--->打开--->读取--->关闭。
隐式游标：直接使用读取，声明、打开、关闭都是系统自动进行的。

1.4 游标的属性

公共的属性：%FOUND，%NOTFOUND，%ISOPEN，%ROWCOUNT。
- %FOUND 布尔型属性，返回TRUE如果INSERT或UPDATE DELETE语句影响了一行或多行或SELECT INTO语句返回一行或多行;否则，返回FALSE。
- %NOTFOUND 布尔型属性，与%FOUND相反。返回TRUE，如果INSERT或UPDATE DELETE语句没有影响任何行，或SELECT INTO语句没有返回任何行;否则，返回FALSE
- %ISOPEN 布尔型属性，判断游标的状态，当游标已经打开时返回TRUE，游标关闭时则返回FALSE。隐式游标总是返回FALSE，因为系统在执行后自动关闭游标。(显式cursor的数据读完后，系统会自动close)
- %ROWCOUNT 数字型属性，返回受SQL影响的行数。

注：当使用隐式游标的属性时，需要在属性前加上SQL。因为Oracle在创建隐式游标时，默认的游标名为SQL。比如：SQL%NOTFOUND;

额外的属性：%BULK_ROWCOUNT，%BULK_EXCEPTIONS。
- %BULK_ROWCOUNT被用于FORALL语句。此属性的第i个元素存储 FORALL LOOP中第 i个执行中的处理的行数UPDATE或DELETE语句。如果第 i个执行不会影响任何行%BULK_ROWCOUNT(i)返回零。
- %BULK_EXCEPTIONS被用于FORALL语句。此属性存储FORALL LOOP中第i个执行的异常，%BULK_EXCEPTIONS(i).ERROR_CODE对应错误代码，%BULK_EXCEPTIONS(i).ERROR_INDEX对应错误信息。

注：
1.不能比较两个游标变量是否相等或者不等。
2.CURSOR 类型不能够用于COLLECTION。（TYPE cvs_t IS TABLE OF SYS_REFCURSOR;）
3.基于REF CURSOR的游标变量无法在PACKAGE的级别被定义，只能够定义在过程,函数,匿名块或触发器里面。（CREATE OR REPLACE PACKAGE plch_pkg IS g_cursor SYS_REFCURSOR; END;）

1.5 cursor 的行数和列数(select)

获取cursor行数：
当游标被打开后，％ROWCOUNT归零。第一次提取之前，cursor_name％ROWCOUNT返回0。此后，它返回的fetch到的行数。完成一次fetch操作后，count+1。
获取cursor列数：

CREATE OR REPLACE FUNCTION count_sql( p_sql IN CLOB )
RETURN INTEGER
AS
        lv_cursor_id    INTEGER;
        lv_columns      DBMS_SQL.DESC_TAB;
        lv_column_count INTEGER;
BEGIN
        -- Open Cursor
        lv_cursor_id := DBMS_SQL.OPEN_CURSOR;
         
        -- Parse Cursor
        DBMS_SQL.PARSE
        ( c             => lv_cursor_id
        , statement     => p_sql
        , language_flag => DBMS_SQL.NATIVE
        );
         
        -- Describe Columns
        DBMS_SQL.DESCRIBE_COLUMNS
        ( c       => lv_cursor_id   
        , col_cnt => lv_column_count
        , desc_t  => lv_columns
        );
         
        -- Close Cursor
        DBMS_SQL.CLOSE_CURSOR(lv_cursor_id);
         
        RETURN lv_column_count;
END count_sql;
/

测试：

SELECT count_sql('SELECT dummy, dummy, dummy, ''Y'' FROM DUAL') FROM DUAL;
 
COUNT_SQL('SELECTDUMMY,DUMMY,DUMMY,''Y''FROMDUAL')
--------------------------------------------------
                                                 4

2 隐式游标

2.1 隐式游标定义

隐式游标由系统自动定义，其过程由oracle控制，完全自动化。比如当DML被使用时，Oracle为每一个不属于显式游标的DML语句都创建一个隐式游标，其声明、打开、关闭都是系统自动进行。另外，隐式游标默认的名称是SQL，不能对SQL游标显式地执行OPEN,FETCH,CLOSE语句。

2.2 隐式游标属性

类似于显式游标，隐式游标同样具有四种属性，只不过隐式游标以SQL%开头，而显示游标以Cursor_name%开头。并且，通过SQL%总是只能访问前一个DML操作或单行SELECT操作的游标属性，用于判断DML执行的状态和结果，进而控制程序的流程。

SQL%ISOPEN 游标是否打开。当执行select into、insert、update、delete这些DML操作时，Oracle会隐含地打开游标，且在该语句执行完毕或隐含地关闭游标。因为是隐式游标，故SQL%ISOPEN总是FALSE。
SQL%FOUND 判断SQL语句是否成功执行。当有作用行时则成功执行为TRUE，否则为FALSE。
SQL%NOTFOUND 判断SQL语句是否成功执行。当有作用行时否其值为FALSE，否则其值为TRUE。
SQL%ROWCOUNT 在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL。对于SELECTINTO语句，如果执行成功，SQL%ROWCOUNT的值为1；如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常NO_DATA_FOUND。

2.3 SELECT INTO

SELECT INTO用于将单行结果集放置到变量之中。SELECT INTO处理的结果包括三种情况

查询结果返回单行，SELECT INTO被成功执行；
查询结果没有返回行，PL/SQL将抛出no_data_found异常；
查询结果返回多行，PL/SQL将抛出too_many_rows 异常。

对于上述两种异常发生时，类似于普通异常处理，程序控制权转移到异常处理部分(如没有异常处理则程序中断)。对于异常被激后发，SQL游标的四个属性在此将不可使用，如下面的例子：

   DECLARE 
     v_ename emp.ename%TYPE; 
   BEGIN 
     SELECT ename INTO v_ename FROM emp WHERE empno=&no; 
     IF  SQL%ROWCOUNT=0 OR SQL%NOTFOUND THEN 
       DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!'); 
     ELSE 
       DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename ); 
     END IF; 
   EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
       DBMS_OUTPUT.PUT_LINE('No data found for '||&no); 
   END; 
-- 操作及结果：
-- Enter value for no:70 
-- No data found for 70 
        
-- Enter value for no:7788 
-- The name for record 7788 is SCOTT

从上面的演示中可以看到，当select into没有返回行时，IF SQL%ROWCOUNT=0 OR SQL%NOTFOUND THEN 语句并没有被执行。使用下面改进过的代码来执行，即可以将SQL游标属性判断放置到EXCEPTION部分。

DECLARE 
   v_ename emp.ename%TYPE; 
 BEGIN 
   SELECT ename INTO v_ename FROM emp WHERE empno=&no; 
   IF SQL%NOTFOUND THEN 
     DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!'); 
   ELSE 
     DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename ); 
  END IF; 
EXCEPTION 
  WHEN NO_DATA_FOUND THEN 
    IF SQL%NOTFOUND THEN 
      DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!'); 
      DBMS_OUTPUT.PUT_LINE('No data found for '||&no); 
    ELSE 
      DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename ); 
    END IF; 
END; 
--操作及结果：
--Enter value for no:80 
--The record 80 is not exist! 
--No data found for 80

3 显式游标

3.1 显式游标的属性

显式游标的也是4个属性（%FOUND，%NOTFOUND，%ISOPEN，%ROWCOUNT），与隐式游标的相同。

3.2 使用步骤

声明(定义)--->打开--->读取--->关闭。

声明游标
CURSOR cursor\name [(parameter[, parameter]...)]
[RETURN return_type]
IS select_statement

例子：

 DECLARE 
    CURSOR emp_cursor(department IN number2 DEFAULT 20)
　return emp%rowtype 
 IS  
    SELECT *  
    FROM emp  
    WHERE deptno=department;

打开游标
OPEN cursor_name[(VALUE[,VALUE]...)];
读取数据
FETCH cursor_name INTO { variable_list | record_variable };

注：游标使用的参数在执行中变化并不影响结果。如下例中：即使 factor一直在增加，但salary一直是乘2。

DECLARE
 my_sal employees.salary%TYPE;
  my_job employees.job_id%TYPE;
  factor INTEGER := 2;
  CURSOR c1 IS
    SELECT factor*salary FROM employees WHERE job_id = my_job;
BEGIN
   OPEN c1;  -- factor initially equals 2
   LOOP
      FETCH c1 INTO my_sal;
      EXIT WHEN c1%NOTFOUND;
      factor := factor + 1;  -- does not affect FETCH
   END LOOP;
   CLOSe c1;
END;
/

关闭游标
CLOSE cursor_name;

3.3 实例

--下面的程序将显示声明一个游标，并在循环中使用SELECT语句提取所有部门为30的员工信息。 
 DECLARE 
CURSOR emp_cursor(department IN NUMBER DEFAULT 20) IS 
    SELECT empno, ename, job, sal 
     FROM emp 
     WHERE deptno = department; 
   
  TYPE employee IS RECORD( 
    id   emp.empno%TYPE, 
    NAME emp.ename%TYPE, 
    job  emp.job%TYPE, 
    sal  emp.sal%TYPE); 
  emp_row employee; 
BEGIN 
  OPEN emp_cursor(30); 
  FETCH emp_cursor INTO emp_row; 
  WHILE emp_cursor%FOUND LOOP 
    dbms_output.put('员工编号 ' || emp_row.id); 
    dbms_output.put(' 姓名 ' || emp_row.name); 
    dbms_output.put(' 职位 ' || emp_row.job); 
    dbms_output.put(' 薪金 ' || emp_row.sal); 
    --填充下一条记录 
    FETCH emp_cursor INTO emp_row; 
  END LOOP; 
  CLOSE emp_cursor; 
END;

4 游标FOR循环

在大多数时候我们在自定义显示游标的时候都遵循下面的步骤:

打开游标
开始循环
从游标中取值
检查那一行被返回
处理
关闭循环
关闭游标

可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同，这就是FOR循环，用于FOR循环的游标按照正常的声明方式声明，它的优点在于不需要显式的打开、关闭、取数据，测试数据的存在、定义存放数据的变量等等。游标FOR 循环的语法如下:

FOR record_index in cursor_name
LOOP
{...statements...}
END LOOP;

使用例子：

  CREATE OR REPLACE Function TotalIncome
     ( name_in IN varchar2 )
     RETURN varchar2
  IS
     total_val number(6);
     cursor c1 is
       SELECT monthly_income
      FROM employees
       WHERE name = name_in;
  BEGIN
     total_val := 0;
     FOR employee_rec in c1
     LOOP
        total_val := total_val + employee_rec.monthly_income;
     END LOOP;
     RETURN total_val;
  END;

5 SELECT FOR UPDATE

SELECT FOR UPDATE 语句可以锁住游标结果集。直到下一次 COMMIT 或ROLLBACK 操作执行后，才解锁游标。

CURSOR cursor_name
IS
select_statement
FOR UPDATE [OF column_list] [NOWAIT];

cursor_name: 游标名。
select_statement: SELECT 查询语句。
column_list: 游标结果集中想要更新的字段。
NOWAIT: 选择游标是否等待资源。

例子：

CURSOR c1
IS
SELECT course_number, instructor
FROM courses_tbl
FOR UPDATE OF instructor;

注：可以使用 WHERE CURRENT OF 语句来更新或删除被 SELECT FOR UPDATE 锁住的数据。

6 WHERE CURRENT OF

用法：

UPDATE table_name SET set_clause WHERE CURRENT OF cursor_name;
DELETE FROM table_name WHERE CURRENT OF cursor_name;

注: WHERE CURRENT OF 语句只更新或删除最后被游标 FETCH 到的记录。

例子：

Update

CREATE OR REPLACE Function FindCourse
     ( name_in IN varchar2 )
     RETURN number
  IS
   cnumber number;
     CURSOR c1
     IS
       SELECT course_number,instructor
      FROM courses_tbl
       WHERE course_name = name_in
       FOR UPDATE of instructor;
  BEGIN
     OPEN c1;
     FETCH c1 INTO cnumber;
     if c1%notfound then
        cnumber := 9999;
     else
        UPDATE courses_tbl
          SET instructor = 'SMITH'
        WHERE CURRENT OF c1;
        COMMIT;
     end if;
     CLOSE c1;
  RETURN cnumber;
  END;

Delete

CREATE OR REPLACE Function FindCourse
     ( name_in IN varchar2 )
     RETURN number
  IS
     cnumber number;
     CURSOR c1
        IS
       SELECT course_number,instructor
       from courses_tbl
       where course_name = name_in
       FOR UPDATE of instructor;
  BEGIN
    open c1;
     fetch c1 into cnumber;
     if c1%notfound then
        cnumber := 9999;
    else
        DELETE FROM courses_tbl
          WHERE CURRENT OF c1;
       COMMIT;
     end if;
     close c1;
  RETURN cnumber;
  END;

转载于:https://www.cnblogs.com/ct-blog/p/6384446.html

weixin_30621711

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
游标cursor

1 游标的相关概念及特性1.1 定义映射在结果集中某一行数据的具体位置，类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行，然后根据业务需求对该行进行相应特定的操作。1.2 游标的分类在Oracel中，游标可以分为两大类：静态游标和 REF游标（动态游标）。REF游标是一种引用类型，类似于指针。而静态游标又分为显式游标和隐式游标两种。1.3 游标的使用过程显式游标：声明...
复制链接

扫一扫