项目11 创建与管理图书借阅网站项目游标

11.3 任务2——静态游标

--- 11.3.1 隐式游标 ---

-- 例11.1 --
SET ServerOutput ON;
DECLARE
var_Name char(10);
    BEGIN
        SELECT Name INTO var_Name
        FROM USERS
        WHERE ID = 2;
        DBMS_OUTPUT.PUT_LINE(var_NAME);
    END;
/
-- 使用SELECT语句声明隐式游标，从USERS表中读取NAME字段的值到变量var_Name --

--- 11.3.2 显式游标 ---

-- 例11.6 --
SET ServerOutput ON;
DECLARE
    varID NUMBER;
    varName CHAR(10);
    CURSOR USERCUR(varNo NUMBER)IS
    SELECT Id,Name FROM USERS
    WHERE Id = varNo;
BEGIN
    OPEN USERCUR(3);
    FETCH USERCUR INTO varId,varName;
    CLOSE USERCUR;
    DBMS_OUTPUT.PUT_LINE('用户编号：'||varId||',用户名:'||varName);
END;
/
-- 完整的游标应用实例 --

11.4 任务3——游标属性

--- 11.4.1 %ISOPEN属性 ---

-- 例11.7 --
SET ServerOutput ON;
DECLARE
    varName VARCHAR2(50);
    varISBN CHAR(13);
    CURSOR BOOKCUR(varType NUMBER)IS
        SELECT ISBN,BOOKNAME FROM BOOK
        WHERE BOOKTYPEID = varType;
BEGIN
    FETCH BOOKCUR INTO varISBN,varName;
    CLOSE BOOKCUR;
    dbms_output.put_line('ISBN:'||varISBN||',书名:'||varName);
END;
/
-- 声明游标BOOKCUR，查询指定类型的图书的ISBN和书名 --
-- 结果出错，因为没有打开游标，直接使用 --

-- 例11.8 --
SET ServerOutput ON;
DECLARE
    varName VARCHAR2(50);
    varISBN CHAR(13);
    CURSOR BOOKCUR(varType NUMBER)IS
    SELECT ISBN,BOOKNAME FROM BOOK
    WHERE BOOKTYPEID = varType;
BEGIN
    IF BOOKCUR % ISOPEN = FALSE Then
        OPEN BOOKCUR(2);
    END IF;
    FETCH BOOKCUR INTO varISBN,varName;
    CLOSE BOOKCUR;
    dbms_output.put_line('ISBN:'||varISBN||',书名:'||varName);
END;
/
-- 在使用游标之前，调用%ISOPEN属性判断游标是否打开 --

--- 11.4.2 %FOUND属性和%NOTFOUND属性 ---

-- 例11.9 --
SET ServerOutput ON;
DECLARE
    varName VARCHAR2(50);
    varISBN CHAR(13);
    CURSOR BOOKCUR(varType NUMBER)IS
    SELECT ISBN,BOOKNAME FROM BOOK
    WHERE BOOKTYPEID = varType;
BEGIN
    IF BOOKCUR % ISOPEN = FALSE Then
        OPEN BOOKCUR(2);
    END IF;
    FETCH BOOKCUR INTO varISBN,varName;
    WHILE BOOKCUR % FOUND
        LOOP
        dbms_output.put_line('ISBN:'||varISBN||',书名:'||varName);
        FETCH BOOKCUR INTO varISBN,varName;
        END LOOP;
        CLOSE BOOKCUR;
END;
/
-- %FOUND属性可以循环执行游标读取数据 --

--- 11.4.3 %ROWCOUNT属性 ---

-- 例11.10 --
SET ServerOutput ON;
DECLARE
    varName VARCHAR2(50);
    varISBN CHAR(13);
    CURSOR BOOKCUR(varType NUMBER)IS
    SELECT ISBN,BOOKNAME FROM BOOK
    WHERE BOOKTYPEID = varType;
BEGIN
    IF BOOKCUR % ISOPEN = FALSE Then
        OPEN BOOKCUR(2);
    END IF;
    FETCH BOOKCUR INTO varISBN,varName;
    WHILE BOOKCUR % FOUND
        LOOP
        dbms_output.put_line('ISBN:'||varISBN||',书名:'||varName);
        IF BOOKCUR % ROWCOUNT = 2 THEN
            EXIT;
        END IF;
        FETCH BOOKCUR INTO varISBN,varName;
        END LOOP;
            CLOSE BOOKCUR;
END;
/
-- 只读取前两行数据 --

11.5 任务4——REF游标

-- 例11.11 --
SET ServerOutput ON;
DECLARE
    TYPE BOOK_CURSOR_TYPE IS REF CURSOR RETURN BOOK % ROWTYPE;
    v_BOOK BOOK_CURSOR_TYPE;
    v_BOOKINFO BOOK % ROWTYPE;
BEGIN
    OPEN v_BOOK FOR SELECT * BOOK;
    LOOP
        FETCH v_BOOK INTO v_BOOKINFO;
        EXIT WHEN v_BOOK % NOTFOUND;
        dbms_output.put_line('ISBN:'||v_BOOKINFO.ISBN||',书名:'||v_BOOKINFO.BOOKNAME);
        END LOOP;
END;
/
-- 一个完整的REF游标应用实例 --

11.6 任务5——游标FOR循环

--- 11.6.1 典型FOR循环 ---

-- 例11.12 --
SET ServerOutput ON;
DECLARE
    CURSOR BOOKCur(varType NUMBER)IS
    SELECT ISBN,BOOKNAME FROM BOOK
    WHERE BOOKTYPEID = varType;
BEGIN
    FOR var_BookRecord IN BOOKCur(2)LOOP
        dbms_output.put_line('ISBN:'||var_BookRecord.ISBN||',书名:'||var_BookRecord.BOOKName);
        END LOOP;
END;
/
-- 利用典型游标FOR循环查询图书类型编号为2的图书的ISBN和书名 --

--- 11.6.2 带子查询的游标FOR循环 ---

-- 例11.13 --
SET ServerOutput ON;
DECLARE
    BEGIN
    FOR var_BookRecord in (select ISBN,BOOKName from BOOK where BOOKTypeID = 2)LOOP
    dbms_output.put_line('ISBN:'||var_BookRecord.ISBN||',书名:'||var_BookRecord.BOOKName);
    END LOOP;
    END;
/
-- 修改上例，在游标FOR循环中直接使用SELECT子查询代替游标名 --