[Tips]運用PL/SQL撰寫procedure/function/package

運用PL/SQL，我們可以撰寫成procedure/function/package，便可以重複呼叫利用，或是放到DBMS_JOB中去例行執行。

• procedure：沒有回傳值
• function：要有回傳值
• package：類似Java的com package概念，利用來打包一至多個procedure/function，更方便管理。

1)procedure

CREATE OR REPLACE PROCEDURE procedure_name AS

  v1 number(5);
  v2 varchar2(10);
  v3 date;

BEGIN

  --to do

END;

procedure example

CREATE OR REPLACE PROCEDURE procedure_test AS

  v1 varchar2(50);

BEGIN

  v1 := 'This is a sample procedure';

  dbms_output.put_line(v1);

END;

執行procedure example，須確認serverouptput開啟才能印出結果

SET serveroutput on;

EXEC procedure_test;

顯示結果:

This is a sample procedure

PL/SQL procedure successfully completed

2)function

CREATE OR REPLACE FUNCTION function_name( parmeter1 in varchar2,...)

RETURN varchar2

AS

  v1 number(5);

  v2 varchar2(10);

  v3 date;

BEGIN

  --to do

END;

function example

CREATE OR REPLACE FUNCTION function_test( v1 in varchar2)

RETURN varchar2

AS

  V2 varchar2(50);

BEGIN

  V2 := ' This is a sample function';

  RETURN V1 || V2;

END;

執行function example

SELECT function_test('Hello!,pl/sql!') from dual;

顯示結果:

Hello,pl/sql! This is a sample function

3)package

--Define Package

CREATE OR REPLACE PACKAGE package_name AS

  PROCEDURE procedure_name(v1 VARCHAR2) ;

  FUNCTION function_name(v2 VARCHAR2) RETURN VARCHAR2;

END;

/

--Package Body

CREATE OR REPLACE PACKAGE BODY package_name AS

  PROCEDURE proceduce_name(v1 VARCHAR2) IS

  BEGIN

 --to do   

  END ; 

  FUNCTION function_name(v2 VARCHAR2) RETURN VARCHAR2 IS

    v3 VARCHAR2(20);

  BEGIN

   --to do

  END ;

END;

package example

CREATE OR REPLACE PACKAGE package_test AS

  PROCEDURE procedure_test(v1 VARCHAR2);

  FUNCTION function_test(v2 VARCHAR2) RETURN VARCHAR2;

END;

/

CREATE OR REPLACE PACKAGE BODY package_test AS

  PROCEDURE procedure_test(v1 VARCHAR2) IS

   BEGIN

    dbms_output.put_line(v1);

  END ;

  FUNCTION function_test(v2 VARCHAR2) RETURN VARCHAR2 IS

    v3 VARCHAR2(50);

  BEGIN

    v3 := ' This is a sample function';

    RETURN V2 || V3;

  END ;

END;

執行package example -1

SET serveroutput on;

EXEC package_test.procedure_test('This is a sample procedure');

顯示結果:

This is a sample procedure

PL/SQL procedure successfully completed

執行package example -2

SET serveroutput on;

SELECT package_test.function_test('Hello,pl/sql!') from dual;

顯示結果:

Hello,pl/sql! This is a sample function

3)查看撰寫好的procedure/function/package

可以利用user_source來查看，如下列所示：

查看procedure

SELECT * FROM user_source where type like 'PROCEDURE';

查看function

SELECT * FROM user_source where type like 'FUNCTION';

查看package

SELECT * FROM user_source where type like 'PACKAGE%';

4)查看錯誤訊息

這邊提供一個好用的功能，當你在撰寫procedure/function/package的時候，如果發生無法正常運作，又找不到哪邊有問題的時候。

就可以利用show error或是SELECT * FROM user_errors;來查看。