116.Oracle数据库SQL开发之 PLSQL编程——游标

116.Oracle数据库SQL开发之 PLSQL编程——游标

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50086931

可以使用游标获取查询返回的记录。在通过查询将记录检索到游标中后，可以一次从游标中取出一行。使用游标时一般需要遵循以下5个步骤：

1.        声明一些变量，用于保存记录的列值

2.        声明游标，并指定查询

3.        打开游标

4.        一次从游标中获得一个记录，并将列值存储在第一步声明的变量中，然后对这些变量执行某些操作

5.        关闭游标

1. 步骤1：声明用于保存列值的变量

声明用于保存列值的变量。变量必须与列的类型兼容。

例如：

DECLARE

         V_product_idproducts.product_id%TYPE;

         V_name   products.name%TYPE;

         V_price    products.price%TYPE;

2. 步骤2：声明游标

游标的声明由游标名和希望执行的查询组成。游标声明与其他的声明一样。

声明游标的语法如下：

CURSOR cursor_name IS

         SELECT_statement;

查询在开发游标之前不会真正运行。

3. 步骤3：打开游标

打开游标时使用的是OPEN语句，这个语句必须放置在代码块的可执行段中。

例如：

OPEN v_product_cursor;

4. 步骤4：从游标中取得记录

从游标中取得每一条记录，可使用FETCH语句。FETCH语句将列的值读取到的第一步中声明的变量中：语法如下：

FETCH cursor_name

         INTOvariable[,variable … ];

         游标可能包含多条记录；因此要通过循环依次读取每一条记录。为了确定循环是否结束，可以使用布尔变量v_product_cursor%NOTFOUND。

5. 步骤5：关闭游标

使用CLOSE语句关闭游标。关闭游标可释放系统资源。

例如：

CLOSE v_product_cursor;

6. 示例

如下：

SET SERVEROUTPUT ON

 

DECLARE

  -- step 1:declare the variables

  v_product_idproducts.product_id%TYPE;

  v_name       products.name%TYPE;

  v_price      products.price%TYPE;

 

  -- step 2:declare the cursor

  CURSORv_product_cursor IS

    SELECTproduct_id, name, price

    FROMproducts

    ORDER BYproduct_id;

BEGIN

  -- step 3:open the cursor

  OPENv_product_cursor;

 

  LOOP

    -- step 4:fetch the rows from the cursor

    FETCHv_product_cursor

    INTOv_product_id, v_name, v_price;

 

    -- exit theloop when there are no more rows, as indicated by

    -- theBoolean variable v_product_cursor%NOTFOUND (= true when

    -- thereare no more rows)

    EXIT WHENv_product_cursor%NOTFOUND;

 

    -- useDBMS_OUTPUT.PUT_LINE() to display the variables

   DBMS_OUTPUT.PUT_LINE(

     'v_product_id = ' || v_product_id || ', v_name = ' || v_name ||

      ',v_price = ' || v_price

    );

  END LOOP;

 

  -- step 5:close the cursor

  CLOSEv_product_cursor;

END;

/

运行如下：

SQL>@product_cursor.sql

输出如下：

store@PDB1> @product_cursor.sql

v_product_id = 1, v_name = Modern Science,v_price = 40

v_product_id = 2, v_name = New Chemistry,v_price = 35

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: TheReturn, v_price = 14.95

v_product_id = 7, v_name = Space Force 9,v_price = 13.49

v_product_id = 8, v_name = From AnotherPlanet, 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 = Lunar Landing,v_price = 15.99

v_product_id = 14, v_name = Submarine,v_price = 15.99

v_product_id = 15, v_name = Airplane,v_price = 15.99

 

PL/SQL procedure successfully completed.

7. 游标与FOR 循环

利用FOR循环可以访问游标中的记录。当使用FOR循环时，可以不显示地打开和关闭游标——for循环会自动执行这些操作。

例如：

SET SERVEROUTPUTON

 

DECLARE

  CURSOR v_product_cursor IS

    SELECT product_id, name, price

    FROM products

    ORDER BY product_id;

BEGIN

  FOR v_product IN v_product_cursor LOOP

    DBMS_OUTPUT.PUT_LINE(

      'product_id = ' || v_product.product_id||

      ', name = ' || v_product.name ||

      ', price = ' || v_product.price

    );

  END LOOP;

END;

/

输出如下：

store@PDB1> @product_cursor2.sql

product_id = 1, name = Modern Science,price = 40

product_id = 2, name = New Chemistry, price= 35

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 = Lunar Landing,price = 15.99

product_id = 14, name = Submarine, price =15.99

product_id = 15, name = Airplane, price =15.99

 

PL/SQL procedure successfully completed.

8. OPEN-FOR语句

可以对游标使用OPEN-FOR语句，由于可以将游标分配给不同的查询，因此可以更加灵活地处理游标。如下：

SET SERVEROUTPUT ON

 

DECLARE

  -- declare aREF CURSOR type named t_product_cursor

  TYPEt_product_cursor IS

  REF CURSORRETURN products%ROWTYPE;

 

  -- declare at_product_cursor object named v_product_cursor

 v_product_cursor t_product_cursor;

 

  -- declare anobject to store columns from the products table

  -- namedv_product (of type products%ROWTYPE)

  v_productproducts%ROWTYPE;

BEGIN

  -- assign aquery to v_product_cursor and open it using OPEN-FOR

  OPENv_product_cursor FOR

  SELECT * FROMproducts WHERE product_id < 5;

 

  -- use a loopto fetch the rows from v_product_cursor into v_product

  LOOP

    FETCHv_product_cursor INTO v_product;

    EXIT WHENv_product_cursor%NOTFOUND;

   DBMS_OUTPUT.PUT_LINE(

     'product_id = ' || v_product.product_id ||

      ', name = ' || v_product.name ||

      ', price= ' || v_product.price

    );

  END LOOP;

 

  -- closev_product_cursor

  CLOSEv_product_cursor;

END;

/

在DECLARE代码块中，下面的语句声明了一个REF CURSOR类型，并命名为t_product_cursor。

REF CURSOR是一个指向游标的指针，类似于C++编程语言中的指针。

执行如下：

store@PDB1> @product_cursor3.sql

product_id = 1, name = Modern Science,price = 40

product_id = 2, name = New Chemistry, price= 35

product_id = 3, name = Supernova, price =25.99

product_id = 4, name = Tank War, price =13.95

 

PL/SQL procedure successfully completed.

9. 无约束游标

有具体的返回类型，这些游标称为约束游标。约束游标的返回类型必须与游标运行的查询中的列相匹配。无约束游标没有返回类型，因此可以运行任何查询。

如下：

SET SERVEROUTPUT ON

 

DECLARE

  TYPE t_cursorIS REF CURSOR;

 

  v_cursort_cursor;

 

  v_productproducts%ROWTYPE;

 

  v_customercustomers%ROWTYPE;

BEGIN

  OPEN v_cursorFOR

  SELECT * FROMproducts WHERE product_id < 5;

 

  LOOP

    FETCHv_cursor INTO v_product;

    EXIT WHENv_cursor%NOTFOUND;

   DBMS_OUTPUT.PUT_LINE(

     'product_id = ' || v_product.product_id ||

      ', name =' || v_product.name ||

      ', price= ' || v_product.price

    );

  END LOOP;

 

  OPEN v_cursorFOR

  SELECT * FROMcustomers WHERE customer_id < 3;

 

  LOOP

    FETCHv_cursor INTO v_customer;

    EXIT WHENv_cursor%NOTFOUND;

   DBMS_OUTPUT.PUT_LINE(

     'customer_id = ' || v_customer.customer_id ||

      ',first_name = ' || v_customer.first_name ||

      ',last_name = ' || v_customer.last_name

    );

  END LOOP;

  CLOSEv_cursor;

END;

/

执行如下：

store@PDB1> @unconstrained_cursor.sql

product_id = 1, name = Modern Science,price = 40

product_id = 2, name = New Chemistry, price= 35

product_id = 3, name = Supernova, price =25.99

product_id = 4, name = Tank War, price =13.95

customer_id = 1, first_name = John,last_name = Brown

customer_id = 2, first_name = Cynthia,last_name = Green

 

PL/SQL procedure successfully completed.