網頁

2017/9/3

Oracle 如何建立Package

以下介紹如何建立Oracle Stored Procedure(SP)的Package。

Package是SP的一種,分為兩部分,一是spec,另一是body。

spec是Package的介面,在spec中宣告Package名稱,子程式如Function及Procedure的名稱及參數,公開變數,Cursor,Exception等等。而body是Package的實作。

從外部只能看到Package的spec,在應用程式要呼叫Package的子程式,只要知道Package的spec中定義的程序名稱及參數型別等。

下面範例建立一個空的Package spec及body。my_package為Package的名稱

-- spec
CREATE OR REPLACE PACKAGE my_package AS
END my_package;
-- body
CREATE OR REPLACE PACKAGE BODY my_package AS
END my_package;

在Oracle SQL Developer中執行以上指令後,在資料庫的套裝程式目錄下可看到新建的Package(記得重新整理)。


下面範例建立一個Package,內含一個Function及一個Procedure。

-- spec
CREATE OR REPLACE PACKAGE my_package AS
  FUNCTION fn_print(pi_name IN VARCHAR2) RETURN VARCHAR2;
  PROCEDURE pc_get_score(pi_id IN VARCHAR2, po_score OUT NUMBER);
END my_package;
-- body
CREATE OR REPLACE PACKAGE BODY my_package AS

  var1 VARCHAR2(30) := 'hello world';
  
  -- Function
  FUNCTION fn_print(pi_name IN VARCHAR2) RETURN VARCHAR2 IS 
    BEGIN
      DBMS_OUTPUT.PUT_LINE(pi_name);
      RETURN var1;
    END fn_print;
  
  -- Procedure
  PROCEDURE pc_get_score(pi_id IN VARCHAR2, po_score OUT NUMBER) IS
    BEGIN
      SELECT SCORE INTO po_score FROM MY_SCORE WHERE USER_ID = pi_id;
    END pc_get_score;

END my_package;

var1定義在body中,為Package的local變數。

my_package.pc_get_score查詢的資料表MY_SCORE資料如下。若此資料表不存在,則在編譯上面的Package時會出現錯誤。

CREATE TABLE MY_SCORE 
(
  USER_ID VARCHAR2(10),
  SCORE NUMBER
);

INSERT INTO MY_SCORE (USER_ID,SCORE) VALUES ('1',98);
INSERT INTO MY_SCORE (USER_ID,SCORE) VALUES ('2',87);
INSERT INTO MY_SCORE (USER_ID,SCORE) VALUES ('3',56);
  USER_ID   SCORE  
 --------- ------- 
        1      98  
        2      87  
        3      56  

接下來測試上面的Function及Procedure,由於會印出結果,所以先執行下面指令開啟SERVEROUTPUT

SET SERVEROUTPUT ON

在SQL Deverloper中直接呼叫上面的Function

DECLARE
  result VARCHAR2(30);
BEGIN
  result := my_package.fn_print('ABCDE');
  DBMS_OUTPUT.PUT_LINE(result);
END;

印出結果如下

ABCDE
hello world

在SQL Deverloper中直接呼叫上面的Procedure

DECLARE
  user_score NUMBER(3);
BEGIN
  my_package.pc_get_score('1', user_score);
  DBMS_OUTPUT.PUT_LINE(user_score);
END;

印出結果如下

98

如果覺得文章有幫助的話還幫忙點個Google廣告,感恩。


參考:

沒有留言:

張貼留言