Oracle—遊标簡介（轉）

遊标的概念:

遊标是SQL的一個記憶體工作區，由系統或使用者以變量的形式定義。遊标的作用就是用于臨時存儲從資料庫中提取的資料塊。在某些情況下，需要把資料從存放在磁盤的表中調到計算機記憶體中進行處理，最後将處理結果顯示出來或最終寫回資料庫。這樣資料處理的速度才會提高，否則頻繁的磁盤資料交換會降低效率。

遊标有兩種類型：顯式遊标和隐式遊标。在前述程式中用到的SELECT...INTO...查詢語句，一次隻能從資料庫中提取一行資料，對于這種形式的查詢和DML操作，系統都會使用一個隐式遊标。但是如果要提取多行資料，就要由程式員定義一個顯式遊标，并通過與遊标有關的語句進行處理。顯式遊标對應一個傳回結果為多行多列的SELECT語句。

遊标一旦打開，資料就從資料庫中傳送到遊标變量中，然後應用程式再從遊标變量中分解出需要的資料，并進行處理。

隐式遊标:

如前所述， DML操作和單行SELECT語句會使用隐式遊标，它們是：

* 插入操作：INSERT。

* 更新操作：UPDATE。

* 删除操作：DELETE。

* 單行查詢操作：SELECT ... INTO ...

當系統使用一個隐式遊标時，可以通過隐式遊标的屬性來了解操作的狀态和結果，進而控制程式的流程。隐式遊标可以使用名字SQL來通路，但要注意，通過SQL遊标名總是隻能通路前一個DML操作或單行SELECT操作的遊标屬性。是以通常在剛剛執行完操作之後，立即使用SQL遊标名來通路屬性。遊标的屬性有四種，如下所示。

隐式遊标的屬性   傳回值類型     意    義  
    SQL%ROWCOUNT        整型       代表DML語句成功執行的資料行數  
    SQL%FOUND          布爾型      值為TRUE代表插入、删除、更新或單行查詢操作成功  
    SQL%NOTFOUND       布爾型      與SQL%FOUND屬性傳回值相反  
    SQL%ISOPEN         布爾型      DML執行過程中為真，結束後為假

【訓練1】　使用隐式遊标的屬性，判斷對雇員工資的修改是否成功。

步驟1：輸入和運作以下程式：

SET SERVEROUTPUT ON   --隻能在SQL PLUS裡面使用，意思是在視窗裡顯示伺服器輸出資訊。如果在plsql中，注釋掉就可以了。
        BEGIN  
        UPDATE emp SET sal=sal+100 WHERE empno=1234;  
         IF SQL%FOUND THEN   
        DBMS_OUTPUT.PUT_LINE('成功修改雇員工資！');  
        COMMIT;   
        ELSE  
        DBMS_OUTPUT.PUT_LINE('修改雇員工資失敗！');  
         END IF;   
        END;

運作結果：

修改雇員工資失敗！  
        PL/SQL 過程已成功完成。

步驟2：将雇員編号1234改為7788，重新執行以上程式：

運作結果為：

成功修改雇員工資！  
        PL/SQL 過程已成功完成。

說明：本例中，通過SQL%FOUND屬性判斷修改是否成功，并給出相應資訊。

注意：在“指令視窗”中可以使用“SET SERVEROUTPUT ON”：在視窗裡顯示伺服器輸出資訊。

Oracle—遊标簡介（轉）

顯式遊标:

遊标的定義和操作

遊标的使用分成以下4個步驟。（顯式遊标需要被打開才能使用，不需要打開的是隐式遊标）

1．聲明遊标

在DECLEAR部分按以下格式聲明遊标：

CURSOR 遊标名[(參數1 資料類型[，參數2 資料類型...])]

IS SELECT語句;

參數是可選部分，所定義的參數可以出現在SELECT語句的WHERE子句中。如果定義了參數，則必須在打開遊标時傳遞相應的實際參數。

SELECT語句是對表或視圖的查詢語句，甚至也可以是聯合查詢。可以帶WHERE條件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT語句中可以使用在定義遊标之前定義的變量。

2．打開遊标

在可執行部分，按以下格式打開遊标：

OPEN 遊标名[(實際參數1[，實際參數2...])];

打開遊标時，SELECT語句的查詢結果就被傳送到了遊标工作區。

3．提取資料

在可執行部分，按以下格式将遊标工作區中的資料取到變量中。提取操作必須在打開遊标之後進行。

FETCH 遊标名 INTO 變量名1[，變量名2...];

或

FETCH 遊标名 INTO 記錄變量;

遊标打開後有一個指針指向資料區，FETCH語句一次傳回指針所指的一行資料，要傳回多行需重複執行，可以使用循環語句來實作。控制循環可以通過判斷遊标的屬性來進行。

下面對這兩種格式進行說明：

第一種格式中的變量名是用來從遊标中接收資料的變量，需要事先定義。變量的個數和類型應與SELECT語句中的字段變量的個數和類型一緻。

第二種格式一次将一行資料取到記錄變量中，需要使用%ROWTYPE事先定義記錄變量，這種形式使用起來比較友善，不必分别定義和使用多個變量。

定義記錄變量的方法如下：

變量名表名|遊标名%ROWTYPE；

其中的表必須存在，遊标名也必須先定義。

4．關閉遊标

CLOSE 遊标名;

顯式遊标打開後，必須顯式地關閉。遊标一旦關閉，遊标占用的資源就被釋放，遊标變成無效，必須重新打開才能使用。

以下是使用顯式遊标的一個簡單練習。

【訓練1】用遊标提取emp表中7788雇員的名稱和職務。

SET SERVEROUTPUT ON  
            DECLARE   
             v_ename VARCHAR2(10);  
             v_job VARCHAR2(10);  
             CURSOR emp_cursor IS   
             SELECT ename,job FROM emp WHERE empno=7788;  
             BEGIN  
         OPEN emp_cursor;  
        FETCH emp_cursor INTO v_ename,v_job;  
            DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);  
            CLOSE emp_cursor;  
            END;

執行結果：

SCOTT,ANALYST  
        PL/SQL 過程已成功完成。

說明：該程式通過定義遊标emp_cursor，提取并顯示雇員7788的名稱和職務。

作為對以上例子的改進，在以下訓練中采用了記錄變量。

【訓練2】用遊标提取emp表中7788雇員的姓名、職務和工資。

SET SERVEROUTPUT ON  
            DECLARE  
             CURSOR emp_cursor IS  SELECT ename,job,sal FROM emp WHERE empno=7788;  
             emp_record emp_cursor%ROWTYPE;  
            BEGIN  
    OPEN emp_cursor;      
            FETCH emp_cursor INTO emp_record;  
               DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal);  
             CLOSE emp_cursor;  
            END;

執行結果：

SCOTT,ANALYST,3000  
        PL/SQL 過程已成功完成。

說明：執行個體中使用記錄變量來接收資料，記錄變量由遊标變量定義，需要出現在遊标定義之後。

注意：可通過以下形式獲得記錄變量的内容：

記錄變量名.字段名。

【訓練3】顯示工資最高的前3名雇員的名稱和工資。

SET SERVEROUTPUT ON  
        DECLARE  
        V_ename VARCHAR2(10);  
        V_sal NUMBER(5);  
        CURSOR emp_cursor IS  SELECT ename,sal FROM emp ORDER BY sal DESC;  
        BEGIN  
         OPEN emp_cursor;  
         FOR I IN 1..3 LOOP  --這裡的I就是隐式遊标
           FETCH emp_cursor INTO v_ename,v_sal;  
         DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);  
          END LOOP;  
         CLOSE emp_cursor;  
         END;

執行結果：

KING,5000  
     SCOTT,3000  
     FORD,3000  
     PL/SQL 過程已成功完成。

說明：該程式在遊标定義中使用了ORDER BY子句進行排序，并使用循環語句來提取多行資料。

遊标循環

【訓練1】使用特殊的FOR循環形式顯示全部雇員的編号和名稱。

SET SERVEROUTPUT ON  
DECLARE  
  CURSOR emp_cursor IS   
  SELECT empno, ename FROM emp;  
BEGIN  
FOR Emp_record IN emp_cursor LOOP     
    DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);  
    END LOOP;  
    END;

執行結果為：

7369SMITH  
7499ALLEN  
7521WARD  
7566JONES  
         PL/SQL 過程已成功完成。

說明：可以看到該循環形式非常簡單，隐含了記錄變量的定義、遊标的打開、提取和關閉過程。 Emp_record為隐含定義的記錄變量，循環的執行次數與遊标取得的資料的行數相一緻。

【訓練2】另一種形式的遊标循環。

SET SERVEROUTPUT ON   
BEGIN  
 FOR re IN (SELECT ename FROM EMP)  LOOP  --re應該是隐式遊标
  DBMS_OUTPUT.PUT_LINE(re.ename)  
 END LOOP;  
END;

執行結果為：

SMITH  
    ALLEN  
    WARD  
    JONES

說明：該種形式更為簡單，省略了遊标的定義，遊标的SELECT查詢語句在循環中直接出現。

顯式遊标屬性

雖然可以使用前面的形式獲得遊标資料，但是在遊标定義以後使用它的一些屬性來進行結構控制是一種更為靈活的方法。顯式遊标的屬性如下所示。

遊标的屬性   傳回值類型   意    義  
    %ROWCOUNT        整型     獲得FETCH語句傳回的資料行數  
    %FOUND          布爾型    最近的FETCH語句傳回一行資料則為真，否則為假  
    %NOTFOUND       布爾型    與%FOUND屬性傳回值相反  
    %ISOPEN         布爾型    遊标已經打開時值為真，否則為假

可按照以下形式取得遊标的屬性：

遊标名%屬性

要判斷遊标emp_cursor是否處于打開狀态，可以使用屬性emp_cursor%ISOPEN。如果遊标已經打開，則傳回值為“真”，否則為“假”。具體可參照以下的訓練。

【訓練1】使用遊标的屬性練習。

SET SERVEROUTPUT ON  
    DECLARE  
      V_ename VARCHAR2(10);  
      CURSOR emp_cursor IS   
      SELECT ename FROM emp;  
    BEGIN  
     OPEN emp_cursor;  
     IF emp_cursor%ISOPEN THEN  
    LOOP  
       FETCH emp_cursor INTO v_ename;  
       EXIT WHEN emp_cursor%NOTFOUND;  
       DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);  
      END LOOP;  
     ELSE  
      DBMS_OUTPUT.PUT_LINE('使用者資訊：遊标沒有打開！');  
     END IF;  
     CLOSE  emp_cursor;  
    END;

執行結果：

1-SMITH  
2-ALLEN  
3-WARD  
 PL/SQL 過程已成功完成。

說明：本例使用emp_cursor%ISOPEN判斷遊标是否打開；使用emp_cursor%ROWCOUNT獲得到目前為止FETCH語句傳回的資料行數并輸出；使用循環來擷取資料，在循環體中使用FETCH語句；使用emp_cursor%NOTFOUND判斷FETCH語句是否成功執行，當FETCH語句失敗時說明資料已經取完，退出循環。

遊标參數的傳遞

【訓練1】帶參數的遊标。

SET SERVEROUTPUT ON  
            DECLARE  
                V_empno NUMBER(5);  
                V_ename VARCHAR2(10);  
                CURSOR  emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS  
                SELECT  empno, ename FROM emp  
                WHERE   deptno = p_deptno AND job = p_job;  
    BEGIN  
         OPEN emp_cursor(10, 'CLERK');  
        LOOP  
         FETCH emp_cursor INTO v_empno,v_ename;  
         EXIT WHEN emp_cursor%NOTFOUND;  
         DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);  
          END LOOP;  
        END;

執行結果：

7934,MILLER  
        PL/SQL 過程已成功完成。

說明：遊标emp_cursor定義了兩個參數：p_deptno代表部門編号，p_job代表職務。語句OPEN emp_cursor(10, 'CLERK')傳遞了兩個參數值給遊标，即部門為10、職務為CLERK，是以遊标查詢的内容是部門10的職務為CLERK的雇員。循環部分用于顯示查詢的内容。

也可以通過變量向遊标傳遞參數，但變量需要先于遊标定義，并在遊标打開之前指派。對以上例子重新改動如下：

【訓練2】通過變量傳遞參數給遊标。

SET SERVEROUTPUT ON  
        DECLARE  
        v_empno  NUMBER(5);  
        v_ename  VARCHAR2(10);  
        v_deptno NUMBER(5);  
        v_job    VARCHAR2(10);  
        CURSOR emp_cursor IS  
            SELECT empno, ename FROM emp  
            WHERE   deptno = v_deptno AND job = v_job;  
        BEGIN  
         v_deptno:=10;  
         v_job:='CLERK';  
         OPEN emp_cursor;  
        LOOP  
         FETCH emp_cursor INTO v_empno,v_ename;  
         EXIT WHEN emp_cursor%NOTFOUND;  
         DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);  
         END LOOP;  
        END;

執行結果：

7934,MILLER  
        PL/SQL 過程已成功完成。

說明：該程式與前一程式實作相同的功能。

動态SELECT語句和動态遊标的用法

Oracle支援動态SELECT語句和動态遊标，動态的方法大大擴充了程式設計的能力。

對于查詢結果為一行的SELECT語句，可以用動态生成查詢語句字元串的方法，在程式執行階段臨時地生成并執行，文法是：

execute immediate 查詢語句字元串 into 變量1[,變量2...];

以下是一個動态生成SELECT語句的例子。

【訓練1】動态SELECT查詢。

SET SERVEROUTPUT ON   
        DECLARE   
        str varchar2(100);  
        v_ename varchar2(10);  
        begin  
        str:='select ename from scott.emp where empno=7788';  
        execute immediate str into v_ename;   
        dbms_output.put_line(v_ename);  
        END;

執行結果：

SCOTT  
        PL/SQL 過程已成功完成。

說明：SELECT...INTO...語句存放在STR字元串中，通過EXECUTE語句執行。

在變量聲明部分定義的遊标是靜态的，不能在程式運作過程中修改。雖然可以通過參數傳遞來取得不同的資料，但還是有很大的局限性。通過采用動态遊标，可以在程式運作階段随時生成一個查詢語句作為遊标。要使用動态遊标需要先定義一個遊标類型，然後聲明一個遊标變量，遊标對應的查詢語句可以在程式的執行過程中動态地說明。

定義遊标類型的語句如下：

TYPE 遊标類型名 REF CURSOR;

聲明遊标變量的語句如下：

遊标變量名遊标類型名;

在可執行部分可以如下形式打開一個動态遊标：

OPEN 遊标變量名 FOR 查詢語句字元串;

【訓練2】按名字中包含的字母順序分組顯示雇員資訊。

輸入并運作以下程式：

declare   
     type cur_type is ref cursor;  
     cur cur_type;  
     rec scott.emp%rowtype;  
     str varchar2(50);  
     letter char:= 'A';  
    begin  
            loop          
             str:= 'select ename from emp where ename like ''%'||letter||'%''';  
             open cur for str;  
             dbms_output.put_line('包含字母'||letter||'的名字：');  
                loop  
                  fetch cur into rec.ename;  
                  exit when cur%notfound;  
                  dbms_output.put_line(rec.ename);  
                end loop;  
              exit when letter='Z';  
               letter:=chr(ascii(letter)+1);  
            end loop;  
    end;

執行結果：

包含字母A的名字：  
    ALLEN  
    WARD  
    MARTIN  
    BLAKE  
    CLARK  
    ADAMS  
    JAMES  
    包含字母B的名字：  
    BLAKE  
    包含字母C的名字：  
    CLARK  
    SCOTT

說明：使用了二重循環，在外循環體中，動态生成遊标的SELECT語句，然後打開。通過語句letter:=chr(ascii(letter)+1)可獲得字母表中的下一個字母。

摘自：http://heisetoufa.iteye.com/blog/366483/

Oracle—遊标簡介（轉）

繼續閱讀

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List