oracle包體學習筆記

包由包規范和包體兩部分組成
包規范的創建語法如下：


CREATE [OR REPLACE] PACKAGE package_name
{IS | AS}
  package_specification
END package_name;


下面是一個創建包規范的例子：


SQL> CREATE OR REPLACE PACKAGE product_package AS
  2   TYPE t_ref_cursor IS REF CURSOR;
  3   FUNCTION get_products_ref_cursor RETURN t_ref_cursor;
  4   PROCEDURE update_product_price (
  5    p_product_id IN products.product_id%TYPE,
  6    p_factor IN NUMBER
  7  );
  8  END product_package;
  9  /


已建立套裝程式.


創建包體的語法如下：


CREATE [OR REPLACE] PACKAGE BODY package_name
{IS | AS}
   package_body
END package_name;


下面是創建包體的例子：


 1  CREATE OR REPLACE PACKAGE BODY product_package AS
 2   FUNCTION get_products_ref_cursor
 3   RETURN t_ref_cursor IS
 4    products_ref_cursor t_ref_cursor;
 5   BEGIN
 6    -- get the REF CURSOR
 7    OPEN products_ref_cursor FOR
 8    SELECT product_id,name,price
 9    FROM products;
10    -- return the REF CURSOR
11    RETURN products_ref_cursor;
12   END get_products_ref_cursor;
13   PROCEDURE update_product_price (
14     p_product_id IN products.product_id%TYPE,
15     p_factor IN NUMBER
16   ) AS
17     v_product_count INTEGER;
18   BEGIN
19    -- count the number of products with the
20    -- supplied product_id (should be 1 if the product exists)
21    SELECT COUNT(*)
22    INTO v_product_count
23    FROM products
24    WHERE product_id = p_product_id;
25   IF v_product_count = 1 THEN
26     UPDATE products
27     SET price = price * p_factor
28     WHERE product_id = p_product_id;
29     COMMIT;
30    END IF;
31   EXCEPTION
32    WHEN OTHERS THEN
33     -- perform a rollback when an exception occurrs
34     ROLLBACK;
35   END update_product_price;
36 END product_package;
37  /


SQL> SELECT product_package.get_products_ref_cursor
  2  FROM dual;


GET_PRODUCTS_REF_CUR
--------------------
CURSOR STATEMENT : 1


CURSOR STATEMENT : 1


PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
        13 Westerrn Front                       13.5
        14 Eastern Front                        16.5
         1 Modern Science                      29.93
         2 Chemistry                              30
         3 Supernova                           32.49
         4 Tank War                            13.95
         5 Z Files                             34.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                               11.19
        11 Creative Yell                       14.99
        12 My Front Line                       13.49


已選取 14 個資料列.


SQL> CALL product_package.update_product_price(3,1.25);


已完成呼叫.


SQL> SELECT price
  2  FROM products
  3  WHERE product_id = 3;


     PRICE
----------
     32.49


SQL> SELECT object_name,procedure_name
  2  FROM user_procedures
  3  WHERE object_name = 'PRODUCT_PACKAGE';


OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------
PRODUCT_PACKAGE                GET_PRODUCTS_REF_CURSOR
PRODUCT_PACKAGE                UPDATE_PRODUCT_PRICE


SQL> DROP PACKAGE product_package;


已刪除套裝程式.