Oracle學習筆記二十：游標的簡介和使用

一、游標概念

• 在 PL/SQL 塊執行 SELECT/INSERT/UPDATE/DELETE 語句時，Oracle 會在記憶體中為其分配上下文區，而游標是指向該區域的指針。

• 游標為應用程式提供了一種對具有多行數據查詢結果集中的每一行單獨處理的方案，是設計互動式應用程式的編程介面。

二、游標優點

• 游標允許應用程式對查詢語句返回的行結果集中的每一行進行相同或者不同的操作。

• 游標提供了對基於游標位置而對錶中數據行進行修改或者更新的能力。

• 游標把作為面向集合資料庫管理系統和麵向行的程式設計兩者聯繫起來，使兩個不同的數據處理方式能夠進行溝通。

三、游標分類

1）隱式游標

　　• 非用戶顯式聲明的游標。

　　• PL/SQL為所有的DML語句都自動聲明瞭隱式游標，如 SELECT INTO語句、INSERT語句、DELETE語句和UPDATE語句。

2）顯式游標

　　• 是由用戶聲明和操作的游標。

3）Ref游標

　　• 用於處理運行時才確定的動態查詢語句的結果。

四、游標使用

4.1、顯示游標

1）顯示游標的使用步驟：

　　• 定義游標

　　• 打開游標

　　• 使用游標

　　• 關閉游標

2）定義顯示游標的語法：

　　• 定義顯式游標在 PL/SQL 語句塊 DECLARE 中完成：

　　cursor cursor_name [(參數名 參數數據類型,……)] [return 數據類型]

　　　　is 查詢名句

3）打開游標

　　• open 游標名（輸入變數列表）

　　• 打開游標語句執行與游標關聯的查詢，綁定輸入變數，識別活動集並將游標指針置於第一行。

4）取出數據

　　• fetch 游標名 into 變數名

5）游標使用完畢之後需要關閉

　　• close 游標名

declare
    cursor c_emp is select empno,ename from emp;
    v_empno emp.empno%type;
    v_ename emp.ename%type;
begin
    if not c_emp %isopen then
        open c_emp;
    end if;
    --抓取游標中的第一條記錄
    fetch c_emp into v_empno,v_ename;
    dbms_output.put_line(v_empno || '-' || v_ename);
    --游標使用完畢後關閉游標
    close c_emp;
end;

4.2、游標屬性

用戶可以通過檢查游標屬性來確定游標的當前狀態：

　　• %FOUND

　　　　• FETCH語句成功返回時，則%FOUND的值為TRUE。

　　• %NOTFOUND

　　　　• FETCH語句未能提取行時，則%NOTFOUND的值為TRUE。

　　• %ISOPEN

　　　　• 如果游標已經打開，則返回TRUE，否則返回FALSE。

　　• %ROWCOUNT

　　　　• 返回目前為止游標提取的行數，%ROWCOUNT為數值型屬性。在第一次獲取之前，%ROWCOUNT為0。當FETCH語句返回一行時，則該數加1。

4.3、遍歷游標

迴圈取出游標中的值：

declare
    cursor c_emp is select empno,ename from emp;
    v_empno emp.empno%type;
    v_ename emp.ename%type;
begin
    if not c_emp %isopen then
        open c_emp;
    end if;
    
    loop
        fetch c_emp into v_empno,v_ename;
        exit when c_emp %notfound;
        dbms_output.put_line(v_empno || '-' || v_ename);
    end loop;
    close c_emp;
end;

4.4、游標迴圈

• 為了簡化游標的應用，Oracle提供了游標的 for 迴圈語句。

• 工作原理：

　　• 游標的for迴圈可以隱式地 open，fetch，close 游標以及迴圈處理結果集。

declare
    cursor c_emp is select empno,ename from emp;
begin
    for emp in c_emp loop
        dbms_output.put_line(emp.empno || '-' || emp.ename);
    end loop;
end;