程序博客网 > 梦红楼歌词陈知

Oralce游标使用

来源：互联网 发布：梦红楼歌词陈知 编辑：程序博客网 时间：2024/06/07 11:07

一、游标的相关概念及特性

1. 定义

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

2. 游标的分类

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

                                                    |--隐式游标：系统自动定义的游标，用于处理select into 和DML语句。
                           |---静态游标---|
Oracle游标-----|                              |--显式游标：即用户自定义游标，专门用于处理select语句返回的多行数据。
                           |---REF游标(本文不作详细介绍)

3. 游标使用的一般过程：

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

4. 游标的4个共同属性：%FOUND，%NOTFOUND，%ISOPEN，%ROWCOUNT。

    %FOUND 布尔型属性，如果SQL语句至少影响到一行数据，则该属性为TRUE，否则为FALSE。
    %NOTFOUND 布尔型属性，与%FOUND相反。
    %ISOPEN 布尔型属性，判断游标的状态，当游标已经打开时返回TRUE，游标关闭时则返回FALSE。
    %ROWCOUNT 数字型属性，返回受SQL影响的行数。
    注：当使用隐式游标的属性时，需要在属性前加上SQL。因为Oracle在创建隐式游标时，默认的游标名为SQL。比如：..... IF SQL%NOTFOUND THEN....


二、隐式游标

1. 隐式游标定义

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

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。


3. 隐式游标使用示例
    (1) 使用隐式游标SQL%NOTFOUND属性

[sql] view plaincopy
  1. SET serveroutput ON  
  2. BEGIN  
  3.   UPDATE emp SET sal=1200 WHERE empno='7369';  
  4.   IF SQL%NOTFOUND THEN  
  5.     dbms_output.put_line('未更新任何记录');  
  6.   ELSE  
  7.     dbms_output.put_line('更新'||SQL%ROWCOUNT||'条记录');  
  8.   END IF  
  9. END;  

    (2) 隐式游标的综合应用（根据SQL游标的不同属性返回不同的结果）
[sql] view plaincopy
  1. DECLARE  
  2.   v_dept emp.deptno%TYPE := &no;  
  3. BEGIN  
  4.           
  5.   IF SQL%ROWCOUNT >= 0 THEN  --判断更新前SQL%ROWCOUNT的属性  
  6.     DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT value is ' || SQL%ROWCOUNT ||'before updated');  
  7.   ELSE  
  8.     DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT value is NULL before updated');  
  9.   END IF;  
  10.           
  11.   UPDATE emp SET sal = sal + 200 WHERE deptno = v_dept;  
  12.           
  13.   IF SQL%FOUND THEN    --判断SQL%FOUND的属性  
  14.     DBMS_OUTPUT.PUT_LINE('SQL code is executed successful');  
  15.     DBMS_OUTPUT.PUT_LINE('SQL%Found is TRUE');  
  16.   ELSE  
  17.     DBMS_OUTPUT.PUT_LINE('No such department');  
  18.     DBMS_OUTPUT.PUT_LINE('SQL%Found is FALSE');  
  19.   END IF;  
  20.   IF SQL%NOTFOUND THEN    --判断SQL%NOTFOUND的属性  
  21.     DBMS_OUTPUT.PUT_LINE('SQL%NotFound is TRUE');  
  22.   ELSE  
  23.     DBMS_OUTPUT.PUT_LINE('SQL%NotFound is FALSE');  
  24.   END IF;  
  25.   
  26.   IF SQL%ISOPEN THEN    --判断SQL%ISOPEN的属性  
  27.     DBMS_OUTPUT.PUT_LINE('SQL%ISOPEN is TRUE');  
  28.   ELSE  
  29.     DBMS_OUTPUT.PUT_LINE('SQL%ISOPEN is FALSE');  
  30.   END IF;  
  31.   DBMS_OUTPUT.PUT_LINE('The rows updated is :' || SQL%ROWCOUNT ||' rows by SQL Cursor'); --判断SQL%ROWCOUNT的属性  
  32. END;  
       操作一及结果：
[sql] view plaincopy
  1. Enter value for no: 10   --下面是成功更新后的结果  
  2. SQL%ROWCOUNT value is NULL before updated  
  3. SQL code is executed successful  
  4. SQL%Found is TRUE  
  5. SQL%NotFound is FALSE  
  6. SQL%ISOPEN is FALSE  
  7. The rows updated is :3 rows by SQL Cursor  
       操作二及结果：
[sql] view plaincopy
  1. Enter value for no: 80   --下面是未成功更新后的结果  
  2. SQL%ROWCOUNT value is NULL before updated  
  3. No such department  
  4. SQL%Found is FALSE  
  5. SQL%NotFound is TRUE  
  6. SQL%ISOPEN is FALSE  
  7. The rows updated is :0 rows by SQL Cursor  

    (3) SELECT INTO时使用隐式游标
          SELECT INTO用于将单行结果集放置到变量之中。SELECT INTO处理的结果包括三种情况
           查询结果返回单行，SELECT INTO被成功执行；
            查询结果没有返回行，PL/SQL将抛出no_data_found异常；
            查询结果返回多行，PL/SQL将抛出too_many_rows 异常。
          对于上述两种异常发生时，类似于普通异常处理，程序控制权转移到异常处理部分(如没有异常处理则程序中断)。对于异常被激后发，SQL游标的四个属性在此将不可使用，如下面的例子：
[sql] view plaincopy
  1. DECLARE  
  2.   v_ename emp.ename%TYPE;  
  3. BEGIN  
  4.   SELECT ename INTO v_ename FROM emp WHERE empno=&no;  
  5.   IF  SQL%ROWCOUNT=0 OR SQL%NOTFOUND THEN  
  6.     DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!');  
  7.   ELSE  
  8.     DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename );  
  9.   END IF;  
  10. EXCEPTION  
  11.   WHEN NO_DATA_FOUND THEN  
  12.     DBMS_OUTPUT.PUT_LINE('No data found for '||&no);  
  13. END;  
         操作及结果：
[sql] view plaincopy
  1. Enter value for no:70  
  2. No data found for 70  
  3.       
  4. Enter value for no:7788  
  5. The name for record 7788 is SCOTT  
        从上面的演示中可以看到，当select into没有返回行时，IF  SQL%ROWCOUNT=0OR SQL%NOTFOUND THEN 语句并没有被执行。
        使用下面改进过的代码来执行，即可以将SQL游标属性判断放置到EXCEPTION部分。
[sql] view plaincopy
  1. DECLARE  
  2.   v_ename emp.ename%TYPE;  
  3. BEGIN  
  4.   SELECT ename INTO v_ename FROM emp WHERE empno=&no;  
  5.   IF SQL%NOTFOUND THEN  
  6.     DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!');  
  7.   ELSE  
  8.     DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename );  
  9.   END IF;  
  10. EXCEPTION  
  11.   WHEN NO_DATA_FOUND THEN  
  12.     IF SQL%NOTFOUND THEN  
  13.       DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!');  
  14.       DBMS_OUTPUT.PUT_LINE('No data found for '||&no);  
  15.     ELSE  
  16.       DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename );  
  17.     END IF;  
  18. END;  
        操作及结果：
[sql] view plaincopy
  1. Enter value for no:80  
  2. The record 80 is not exist!  
  3. No data found for 80  


三、显式游标

1. 显式游标的属性

    显式游标的也是4个属性（%FOUND，%NOTFOUND，%ISOPEN，%ROWCOUNT），与隐式游标的相同，在文章头部已作介绍。
2. 显式游标的使用步骤：声明(定义)--->打开--->读取--->关闭。

     (1) 声明游标
          格式如下：

[sql] view plaincopy
  1. CURSOR cursor_name [(parameter[, parameter]...)]  
  2. [RETURN return_type]  
  3. IS select_statement  
          例子：
[sql] view plaincopy
  1. DECLARE  
  2.    CURSOR emp_cursor(department IN number2 DEFAULT 20)  
  3. IS   
  4.    SELECT empno,ename,job,sal   
  5.    FROM emp   
  6.    WHERE deptno=department;  
     (2) 打开游标
           格式：OPEN cursor_name[(VALUE[,VALUE]...)];
           例子：OPEN emp_cursor;
     (3) 读取游标数据
           格式：FETCH cursor_name INTO { variable_list | record_variable };
           例子：FETCH emp_cursor INTO emp_row;
     (4) 关闭游标
           格式：CLOSE cursor_name;
           例子：CLOSE emp_cursor;
3. 显式游标完整的使用示例：
[sql] view plaincopy
  1.         --下面的程序将显示声明一个游标，并在循环中使用SELECT语句提取所有部门为30的员工信息。  
  2.         DECLARE  
  3.           CURSOR emp_cursor(department IN NUMBER DEFAULT 20) IS  
  4.             SELECT empno, ename, job, sal  
  5.               FROM emp  
  6.              WHERE deptno = department;  
  7.           
  8.           TYPE employee IS RECORD(  
  9.             id   emp.empno%TYPE,  
  10.             NAME emp.ename%TYPE,  
  11.             job  emp.job%TYPE,  
  12.             sal  emp.sal%TYPE);  
  13.           emp_row employee;  
  14.         BEGIN  
  15.           OPEN emp_cursor(30);  
  16.           FETCH emp_cursor INTO emp_row;  
  17.           WHILE emp_cursor%FOUND LOOP  
  18.             dbms_output.put('员工编号 ' || emp_row.id);  
  19.             dbms_output.put(' 姓名 ' || emp_row.name);  
  20.             dbms_output.put(' 职位 ' || emp_row.job);  
  21.             dbms_output.put(' 薪金 ' || emp_row.sal);  
  22.             --填充下一条记录  
  23.             FETCH emp_cursor INTO emp_row;  
  24.           END LOOP;  
  25.           CLOSE emp_cursor;  
  26.         END;  

        操作及结果：

[sql] view plaincopy
  1. --  


四、游标FOR循环

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

1、打开游标
2、开始循环
3、从游标中取值
4、检查那一行被返回
5、处理
6、关闭循环
7、关闭游标

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

[sql] view plaincopy
  1.       FOR record_name IN  
  2.         corsor_name[(parameter[,parameter]...)]  
  3.         | (query_difinition)  
  4.       LOOP  
  5.         statements  
  6.       END LOOP;  
       使用例子：

[sql] view plaincopy
  1. SET serveroutput ON;  
  2. DECLARE  
  3.   CURSOR mycur(vartype NUMBER) IS  
  4.     SELECT emp_no, emp_zc FROM cus_emp_basic WHERE com_no = vartype;  
  5. BEGIN  
  6.   FOR person IN mycur(000627)   
  7.   LOOP  
  8.     dbms_output.put_line('雇员编号:' || person.emp_no || ',地址:' ||  
  9.                          person.emp_zc);  
  10.   END LOOP;  
  11. END;  

      注：在使用游标FOR循环时，一定不要使用OPEN语句、FETCH语句和CLOSE语句，否则将产生错误。

0 0
梦红楼歌词陈知 梦红楼歌词陈知
原创粉丝点击
热门IT博客
js下一个兄弟节点js下一个兄弟节点
sqlserver清空数据库sqlserver清空数据库
宝马编程设码 教程
文笔好的玄幻小说知乎
mac连接ftp上传文件
新概念 知乎
软交换网络各节点功能
淘宝客宣传语
淘宝详情页模板哪里找
mac苹果铃声制作
考试管理系统数据库
sql存储过程怎么写
淘宝感谢信的范文
flothermal软件下载
数值策划 知乎
数字电视是网络电视吗
三菱q系列编程手册 pdf
艾拉物联 知乎
动力学仿真软件
为什么卸载不了软件
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 嘌呤含量高的食物 香菇含嘌呤高吗 无嘌呤的食物有哪些 生蚝嘌呤 红豆嘌呤高吗 那些食物嘌呤含量高 高嘌呤水果 嘌呤食物表 花生米嘌呤高吗 食物嘌呤表 高嘌呤的食物 不含嘌呤的食物有哪些 嘌呤高吃什么 鸡汤嘌呤高吗 别嘌呤片 哪些食物含嘌呤 核桃嘌呤高吗 嘌呤的危害 痛风嘌呤 咖啡嘌呤高吗 木耳嘌呤高吗 蘑菇嘌呤高吗 蘑菇含嘌呤高吗 什么食物嘌呤高 啤酒嘌呤高吗 黄豆含嘌呤高吗 含嘌呤的食物有哪些 河蟹嘌呤 银耳嘌呤高吗 红酒嘌呤高吗 富含嘌呤的食物有哪些 豆腐 嘌呤 深海鱼油含嘌呤吗 含嘌呤的食物 鲫鱼嘌呤高吗 什么叫嘌呤 嘌呤高不能吃什么 猪血嘌呤 玉米嘌呤高吗 黄酒嘌呤高吗 嘌呤高的食物有那些

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里