游标

游标的概念

--为了处理 SQL 语句，ORACLE 必须分配一片叫上下文( context area )的区域来处理所必需的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)。
--游标是一个指向上下文的句柄( handle)或指针。通过游标，PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。
对于不同的SQL语句，游标的使用情况不同：作者：olaH
链接：https://www.jianshu.com/p/ba7c9d97d7c0
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

在PL/SQL中，游标的使用分为两种，一种是显式游标，一种是隐式游标

显式游标

显示游标的使用需要事先使用declare来进行声明，其过程包括：

声明游标-->打开游标-->从游标提取数据-->关闭游标

显示游标cursor 游标名称 is 查询语句 for update;

declare
     cursor emp_cursor is select * from scott.emp for update;
begin
    for var_emp in emp_cursor loop
        if var_emp.sal <= 1000 then
             update scott.emp set sal = var_emp.sal+300 where current of emp_cursor;//游标的当前行
        elsif var_emp.sal<=2000 then
             update scott.emp set sal = var_emp.sal+200 where current of emp_cursor;
        else
             update scott.emp set sal = var_emp.sal+100 where current of emp_cursor;
        end if; 
    end loop;
    commit;
end;

隐式游标

该方式多用于处理select语句返回的多行数据的情形。而隐式游标则由则由系统自动定义，当DML被使用时，Oracle为每一个不属于显示游标的DML语句都创建一个隐式游标，其声明、打开、关闭都是系统自动进行。多用于配合DML返回单行数据的处理。

一、隐式游标的定义及其属性
    定义
        隐式游标则由则由系统自动定义，非显示定义游标的DML语句即被赋予隐式游标属性。其过程由oracle控制，完全自动化。隐式游标的名称是SQL，不能对SQL游标显式地执行OPEN,FETCH,CLOSE语句。
    属性
        类似于显示游标，隐式游标同样具有四种属性，只不过隐式游标以SQL%开头，而显示游标以Cursor_name%开头通过SQL%总是只能访问前一个DML操作或单行SELECT操作的游标属性，用于判断DML执行的状态和结果，进而控制程序的流程
        SQL%ISOPEN
            游标是否打开。当执行select into ,insert update,delete时，Oracle会隐含地打开游标，且在该语句执行完毕或隐含地关闭游标，因为是隐式游标，故SQL%ISOPEN总是false

        SQL%FOUND
            判断SQL语句是否成功执行。当有作用行时则成功执行为true，否则为false。
        SQL%NOTFOUND
            判断SQL语句是否成功执行。当有作用行时否其值为false，否则其值为true。

        SQL%ROWCOUNT
            在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为true,如果没有成功，SQL%ROWCOUNT的值为，同时产生一个异常NO_DATA_FOUND。

二、演示

1.SQL%FOUND的使用

DECLARE
    v_empno emp.empno%TYPE:=&no;
BEGIN
    UPDATE emp SET sal=sal+200     --根据给定的empno，更新一条记录
    WHERE empno=v_empno;
    IF SQL%FOUND THEN              --使用SQL游标属性SQL%FOUND作为判断条件
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('SQL code is executed successful');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The Employee is not exist');
        ROLLBACK;
        END IF;
END;

        Enter value for no: 7788
        old   2:   v_empno emp.empno%TYPE:=&no;
        new   2:   v_empno emp.empno%TYPE:=7788;
        SQL code is executed successful

        PL/SQL procedure successfully completed

2.SQL游标的综合应用(根据SQL游标的不同属性返回不同的结果)

DECLARE
     v_dept emp.deptno%TYPE := &no;
BEGIN
    IF SQL%ROWCOUNT >= 0 THEN  --判断更新前SQL%ROWCOUNT的属性
        DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT value is ' ||SQL%ROWCOUNT || 'before updated');
    ELSE
        DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT value is NULL before updated');
    END IF;

    UPDATE emp SET sal = sal + 200 WHERE deptno = v_dept;
    IF SQL%FOUND THEN    --判断SQL%FOUND的属性
        DBMS_OUTPUT.PUT_LINE('SQL code is executed successful');
        DBMS_OUTPUT.PUT_LINE('SQL%Found is TRUE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No such department');
        DBMS_OUTPUT.PUT_LINE('SQL%Found is FALSE');
    END IF;

    IF SQL%NOTFOUND THEN    --判断SQL%NOTFOUND的属性
        DBMS_OUTPUT.PUT_LINE('SQL%NotFound is TRUE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('SQL%NotFound is FALSE');
    END IF;

    IF SQL%ISOPEN THEN    --判断SQL%ISOPEN的属性
        DBMS_OUTPUT.PUT_LINE('SQL%ISOPEN is TRUE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('SQL%ISOPEN is FALSE');
    END IF;
    DBMS_OUTPUT.PUT_LINE('The rows updated is :' || QL%ROWCOUNT || ' rows by SQL Cursor'); --判断SQL%ROWCOUNT的属性

END;

       Enter value for no: 10   --下面是成功更新后的结果

        SQL%ROWCOUNT value is NULL before updated

        SQL code is executed successful

        SQL%Found is TRUE

        SQL%NotFound is FALSE

        SQL%ISOPEN is FALSE

        The rows updated is :3 rows by SQL Cursor


        Enter value for no: 80   --下面是未成功更新后的结果

        SQL%ROWCOUNT value is NULL before updated

        No such department

        SQL%Found is FALSE

        SQL%NotFound is TRUE

        SQL%ISOPEN is FALSE

        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游标的四个属性在此将不可使用，如下面的例子：

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

posted @ 2019-10-22 14:42 一了然阅读(176) 评论(0) 收藏举报

刷新页面返回顶部

一了然

游标

游标的概念

公告