游標的使用

    當SELECT語句從數據庫中返回的記錄多于一條時，就可以使用游標。
游標可以理解為一次訪問一個的一組記錄，使用游標一般要遵循5個
步驟：
1、聲明用于保存列值的變量
2、聲明游標
3、打開游標
4、從游標中取得記錄
5、關閉游標
=============products表查詢的結果集==================
SQL> SELECT product_id,name,price
  2  FROM products
  3  ORDER BY product_id;


PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         1 Modern Science                      19.95
         2 Chemistry                              30
         3 Supernova                           25.99
         4 Tank War                            13.95
         5 Z Files                             49.99
         6 2412: The Return                    14.95
         7 Space Force 9                       13.49
         8 From Another Planet                 12.99
         9 Classical Music                     10.99
        10 Pop 3                               15.99
        11 Creative Yell                       14.99
        12 My Front Line                       13.49
        13 Westerrn Front                       13.5
        14 Eastern Front                        16.5


已選取 14 個資料列.


    將products表查詢出的結果集使用游標在sqlplus下運行并輸出
下面是兩個pl/sql塊，游標第一種用法分按5個步驟進行
=============游標的第一種用法=====================
SQL> --使用SET SERVEROUTPUT ON輸出
SQL> --的數據才可以在sqlplus上看到
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2   --step 1:declare the variables
  3   v_product_id products.product_id%TYPE;
  4   v_name products.name%TYPE;
  5   v_price products.price%TYPE;
  6
  7   --step 2:declare the cursor
  8  CURSOR cv_product_cursor IS
  9    SELECT product_id,name,price
 10    FROM products
 11    ORDER BY product_id;
 12  BEGIN
 13    --step 3:open the cursor
 14    OPEN cv_product_cursor;
 15   LOOP
 16    --step 4:fetch the rows from the cursor
 17    FETCH cv_product_cursor
 18    INTO v_product_id,v_name,v_price;
 19    --exit the loop when there are no more rows,as indicated
 20    --the Boolean variable cv_product_cursor%NOTFOUND (= tru
 21    --there are no more rows)
 22
 23    EXIT WHEN cv_product_cursor%NOTFOUND;
 24    --use DBMS_OUTPUT.PUT_LINE() to display the variables
 25    DBMS_OUTPUT.PUT_LINE('v_product_id = ' || v_product_id |
 26    ', v_name = ' || v_name || ', v_price = ' || v_price
 27    );
 28   END LOOP;
 29   --step 5:close the cursor
 30  CLOSE cv_product_cursor;
 31  END;
 32  /
v_product_id = 1, v_name = Modern Science, v_price = 19.95
v_product_id = 2, v_name = Chemistry, v_price = 30
v_product_id = 3, v_name = Supernova, v_price = 25.99
v_product_id = 4, v_name = Tank War, v_price = 13.95
v_product_id = 5, v_name = Z Files, v_price = 49.99
v_product_id = 6, v_name = 2412: The Return, v_price = 14.95
v_product_id = 7, v_name = Space Force 9, v_price = 13.49
v_product_id = 8, v_name = From Another Planet, v_price = 12.99
v_product_id = 9, v_name = Classical Music, v_price = 10.99
v_product_id = 10, v_name = Pop 3, v_price = 15.99
v_product_id = 11, v_name = Creative Yell, v_price = 14.99
v_product_id = 12, v_name = My Front Line, v_price = 13.49
v_product_id = 13, v_name = Westerrn Front, v_price = 13.5
v_product_id = 14, v_name = Eastern Front, v_price = 16.5


PL/SQL 程序順利完成.


利用FOR循環可以不顯式的打開和關閉游標--FOR循環會自動執行這些操作
=============游標的第二種用法=====================
SQL>  DECLARE
  2    CURSOR cv_product_cursor IS
  3     SELECT product_id,name,price
  4     FROM products
  5     ORDER BY product_id;
  6   BEGIN
  7    FOR v_product IN cv_product_cursor LOOP
  8       DBMS_OUTPUT.PUT_LINE ('product_id = ' || v_product.product_id ||
  9        ', name = ' || v_product.name || ', price = ' || v_product.price
 10       );
 11    END LOOP;
 12   END;
 13   /
product_id = 1, name = Modern Science, price = 19.95
product_id = 2, name = Chemistry, price = 30
product_id = 3, name = Supernova, price = 25.99
product_id = 4, name = Tank War, price = 13.95
product_id = 5, name = Z Files, price = 49.99
product_id = 6, name = 2412: The Return, price = 14.95
product_id = 7, name = Space Force 9, price = 13.49
product_id = 8, name = From Another Planet, price = 12.99
product_id = 9, name = Classical Music, price = 10.99
product_id = 10, name = Pop 3, price = 15.99
product_id = 11, name = Creative Yell, price = 14.99
product_id = 12, name = My Front Line, price = 13.49
product_id = 13, name = Westerrn Front, price = 13.5
product_id = 14, name = Eastern Front, price = 16.5


PL/SQL 程序順利完成.