運用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;
--顯示Output:
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;
--顯示Output:
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 BodyCREATE 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');
--顯示Output:
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;
--顯示Output:
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;來查看。
