PL/SQL之游标的使用

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 ON
DECLARE
    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;

 

5、游标变量

　　这部分内容以后补上。