PL/SQL之游标的使用-白红宇

PL/SQL之游标的使用

阅读量：4553 次

发布时间：2019-06-08

本文共 5139 字，大约阅读时间需要 17 分钟。

Oracle中的游标有两种：

显式游标

　　　　用CURSOR...IS 命令定义的游标，它可以对查询语句(SELECT)返回的多条记录进行处理。

隐式游标

　　　　是在执行插入(INSERT)、删除(DELETE)、修改(UPDATE)和返回单条记录的查询(SELECT)语句时有PL/SQL自动定义的。

1、显式游标操作

　　显式游标在块定义部分、包或子程序中声明。当声明了显式游标后，可通过下面三条命令控制显式游标操作。

　　1)打开游标

　　2)推进游标

　　3)关闭游标

--声明显式游标声明显式游标就是指定游标名和与它关联的SELECT语句，其语法如下:CURSOR cursor_name[(parameter[,parameter]...)][RETURN return_type] IS select_statement;parameter子句语法：cursor_parameter_name[IN]datatype[{:=|DEFAULT} expr]cursor_name是游标名，必须符合PL/SQL标识符的命名规范;return_type 是游标返回的查询结果集类型，它只能是一个PL/SQL记录类型或数据库表的某一列的类型。select_statement是游标中的SELECT语句，在游标中指定的SELECT语句可以带有UNION、MINUS或INTERSECT子句。select_statement不能含有INTO子句，查询结果是用FETCH语句中的INTO子句送给变量的。将PL/SQL变量绑定在游标的WHERE子句中DECLARE    v_auths auths%ROWTYPE;    v_code auths.author_code%TYPE;    CURSOR c_auths IS        SELECT * FROM auths        WHERE author_code=v_code;        因为声明游标时将PL/SQL变量绑定在WHERE子句中，所以游标的声明必须在这些变量的作用域内，同时先声明变量在声明显式游标。将游标参数绑定在游标的WHERE子句中DECLARE    CURSOR c_auths(p_code auths.author_code%TYPE;) IS        SELECT * FROM auths        WHERE author_code=p_code;游标参数的作用域只局限在游标声明所指定的查询中，并且游标可以赋缺省值。--打开显式游标处理：先查找绑定在游标中的变量，然后按照该变量的值确定查询结果集。语法：OPEN cursor_name [(parameter[,parameter]...)]cursor_name是已声明的游标的名;parameter是绑定的实参;BEGIN    --在打开游标前为绑定的变量赋值    v_code:='A0001';    --打开游标    OPEN c_auths;    如果打开游标后，在为改变量赋值，查询结果集不发生任何改变。这是因为结果集在游标打开时被确定，同时指向结果集的指针也被确定。    BEGIN    --打开游标时将参数传入    OPEN c_auths('A0001');    --推进显式游标使用FETCH语句来推进游标，返回查询结果集中的一行，没执行完一条FETCH语句后，显式游标会自动指向查询结果集的下一行。语法：    FETCH cursor_name INTO list_of_variables;    或    FETCH cursor_name INTO PL/SQL_record;    cursor_name是已声明的游标的名list_of_variables是已声明的变量列表PL/SQL_record是已定义好的PL/SQL记录INTO子句中的变量必须与游标SELECT语句中相应表列的类型兼容(一致或可以自动转换)    --关闭显式游标    当整个结果集都检索完以后，应当关闭游标，释放游标所占用的资源。    语法：CLOSE cursor_name游标处于关闭状态时，推进游标时错误的，错误信息如下：ORA-1001:Invalid CURSOR或ORA-1002:Fetch out of Sequence如果关闭游标有又执行关闭游标命令，还会提示ORA-1001错误。

2、游标的属性

%FOUND    一个返回布尔值得属性，如果FETCH语句返回了一行，则该属性返回true，否则返回false。    如果在打开游标之前或关闭游标之后使用该属性，会提示ORA-1001错误。当打开游标后没有使用FETCH语句推进，则返回NULL;%NOTFOUND    与%FOUND意思相反，如果FETCH语句返回了一行，则改属性返回false,否则返回true。其它和%FOUND相同。%ISOPEN    用来确定相关游标是否打开，如果游标已打开，则返回true,否则返回false;    %ROWCOUNT    返回游标推进的行数。如果在打开游标之前或关闭游标之后使用该属性，会提示ORA-1001错误。

3、显式游标的推进循环

DECLARE    --声明一个变量，这个变量用来接收游标返回的结果集。    v_Salary Auths.salary%TYPE;    v_Code Auths.author_code%type;    --声明游标，结果集为作家代码A0001到A0006的工资值    CURSOR c_salary IS         SELECT salary,author_code            FROM auths            WHERE author_code<='A0006'BEGIN    --打开游标，并初始化结果集    OPEN c_salary;    LOOP        --推进游标，将游标的查询结果集中的一行存到变量v_salary中。        FETCH c_salary INTO v_Salary,v_Code;        --当结果集中没有夯实退出循环。        EXIT WHEN c_salary%NOTFOUND;        IF v_Salary<=200 THEN            UPDATE auths SET salary=salary=50;                WHERE author_code=v_Code;        END IF;    END LOOP;    --关闭游标    CLOSE c_salary;    --提交所做修改    COMMIT;END;    PL/SQL还提供了一种简单类型的循环，可以自动控制游标的打开、推进和关闭。DECLARE    CURSOR c_salary IS         SELECT salary            FROM auths            WHERE author_code<='A0006'BEGIN    --开始游标FOR循环，隐含地打开c_salary游标。    FOR v_salary IN c_salary LOOP        --一个隐含的FETCH语句在这里被执行        IF v_Salary<=200 THEN            UPDATE auths SET salary=salary=50;                WHERE salary=v_salary.salary;        END IF;        --循环继续前进，一个隐含的v_salary%NOTFOUND被检测。    END LOOP;    --循环结束，c_salary游标的一个隐含的CLOSE操作被执行。    COMMIT;END;    v_salary没有在块的定义部分声明，该变量被PL/SQL编译器隐含地声明了，该变量的类型为c_salary%ROWTYPE,其作用域只在循环内部。在循环开始，游标的c_salary被自动打开。在每一次自动推进后，%FOUND属性用来检查在结果集中是否还有行，当结果集中没有行时，游标被自动关闭。    使用CURRENT OF cursor_name子句作为条件。DECLARE    --声明游标时在SELECT语句中必须加FOR UPDATE OF 子句    CURSOR c_salary IS         SELECT salary            FROM auths            WHERE author_code<='A0006' FOR UPDATE OF salary;BEGIN    FOR v_salary IN c_salary LOOP        IF v_Salary<=200 THEN            UPDATE auths SET salary=salary=50;                WHERE CURRENT OF c_salary;        END IF;    END LOOP;    COMMIT;END;

4、隐式游标处理(SQL游标)

%FOUND    当使用INSERT、DELETE或UPDATE语句处理一行或多行，或换行SELECT INTO 语句返回一行时,返回true,否则返回false;    如果执行SELECT INTO语句时返回多行，则会产生TOO_MANY_ROWS异常，并将控制权转到异常处理部分，%FOUND属性并不返回true,如果执行SELECT INTO语句时返回0行，会产生NO_DATA_FOUND异常，%FOUND属性并不返回false。    %NOTFOUND    与%FOUND属性相反，当使用INSERT、DELETE或者UPDATE语句处理的行数为0时，%NOTFOUND属性返回true,否则返回false。    %ISOPEN    因为在执行了DML语句后，Oracle会自动关闭SQL游标，所以该属性终为false。    %ROWCOUNT    该属性返回执行INSERT、DELETE或者UPDATE语句返回的行数，或返回执行SELECT INTO 语句时查询出的行数，如果INSERT、DELETE、UPDATE或者SELECT INTO语句返回的行数为0，则%ROWCOUNT属性返回0。但如果SELECT INTO语句返回的行数为多行，则产生TOO_MANY_ROWS异常，并将控制权转到异常处理部分，而不是去判断%ROWCOUNT属性。BEGIN    UPDATE auths        SET entry_date_time=SYSDATE        WHERE author_code='A0001';    IF SQL%NOTFOUND THEN        INSERT INTO auths(author_code,name,entry_date_time) VALUES('A0001','张三',SYSDATE);    END IF;END;SET SERVEROUTPUT ONDECLARE    v_birthdate DATE;BEGIN    SELECT birthdate INTO v_birthdate         FROM auths        WHERE name='张三';    IF SQL%FOUND THEN        DELETE FROM auths WHERE name='张三';    END IF;    EXCEPTION    WHEN NO_DATA_FOUND THEN        DBMS_OUTPUT.PUT_LINE('该记录不存在');    WHEN TOO_MANY_ROWS THEN        DBMS_OUTPUT.PUT_LINE('存在多条记录');END;