Packages
在練習中, 你很少創建一個獨立的存儲function或procedure. 相反,你會使用package. 什麼是package? Package是組織相關的function和procedure包含在一起, 就像創建一個圖書館, 但在PL/SQL術語中稱為package. 當你建立Procedure或Function後會保存在Oracle的Procedure與Function目錄裡, 如果我有一個Procedure功能與已存在的Procedure類似, 但又有點不一樣, Oracle又不允許在Procedure目錄下建立相同名稱的Procedure, 這時候就要靠Package來把相同名稱的Procedure分開. Package也有分類的意思, 將相關功能模組封裝在同一個Package內可提高聚合性.
PL/SQL Package有兩部分:
1. Package
specification
2. Package
body
Package Specification定義了此Package內所有的成員, 例如:Procedure與Function, 這裡定義的Procedure與Function只要擺定義即可不需要放實作的內容, 在Specification的成員都是對外公開Public.
Package Body為放置成員Spec的詳細實作區, 在建立Package時要先有Specification才可以有Body, 你可以將Spec與Body分成兩個檔案撰寫也可以撰寫在同一個檔案裡.
Package Specification 語法:
CREATE [OR REPLACE] PACKAGE <package_name> AS
-- one or more: constant, cursor, function, procedure, or variable
declarations
END <package_name>;
/
Create package spec.
以下Package spec例子, 包含了5個functions, 但只有declare並沒有運行code, code 在package body.
create or replace package date_ as
d_max constant date := to_date('99991231','YYYYMMDD');
d_min constant date := to_date('19900101','YYYYMMDD');
-- 常數關鍵字constant. 它需要一個初始值,並且不允許被改變該值.
function end_of_day( input1 in date) return date;
function get_max return date;
function get_min return date;
function random(
starting_year in
number, ending_year in number )
return date;
function start_of_day( input2 in date) return date;
end date_;
/
|
Create package body:
create or replace package body date_ as
function end_of_day(input1 in date) return date as
begin
return to_date(to_char(input1,
'YYYYMMDD')||'235959','SYYYYMMDDHH24MISS');
end end_of_day;
function get_max return date as
begin
return d_max;
end get_max;
function get_min return date as
begin
return d_min;
end get_min;
function random(starting_year in
number, ending_year in number)
return date as
d_random date;
n_day number;
n_month number;
n_year number;
begin
n_year := round(dbms_random.value(starting_year,ending_year),0);
n_month := round(dbms_random.value(1,12),0);
n_day := round(dbms_random.value(1,31),0);
d_random :=
to_date(lpad(to_char(n_year),4,'0')||
lpad(to_char(n_month),2,'0')||
lpad(to_char(n_day),2,'0'),'YYYYMMDD');
exception when others then pl(SQLERRM);
end;
return d_random;
end random;
function start_of_day(input2 in date) return date as
begin
return trunc(input2);
end start_of_day;
end date_;
/
|
執行效果:
SQL> alter session set
nls_date_format='YYYYMMDDHH24MISS';
Session altered.
SQL> select date_.end_of_day(sysdate) from dual;
DATE_.END_OF_D
--------------
20160725235959
SQL> select date_.get_max from dual;
GET_MAX
--------------
99991231000000
SQL> select date_.get_min from dual;
GET_MIN
--------------
19900101000000
SQL> select date_.random(1994,2014) from dual;
DATE_.RANDOM(1
--------------
19960809000000
SQL> select date_.start_of_day('20160101235959')
from dual;
DATE_.START_OF
--------------
20160101000000
It’s Your turn to Create a package
還記得較早前創建的to_number2 function嗎? 用它來創建你的package吧. 條款如下:
1. Package 名稱 NUMBER_
2. 用 select 來測試 package.
PL/SQL BLOCK 語法比較
ANONYMOUS
[DECLARE]
|
CREATE
FUNCTION
|
CREATE
PROCEDURE
|
CREATE
PACKAGE
|
CREATE
PACKAGE BODY
|
-
|
[parameters]
|
[parameters]
|
-
|
-
|
-
|
RETURN
|
-
|
-
|
-
|
[declaration section]
|
[declaration section]
|
[declaration section]
|
[declaration section]
|
[declaration section]
|
BEGIN
|
BEGIN
|
BEGIN
|
-
|
BEGIN
|
executable
section
|
executable
section
|
executable
section
|
-
|
executable
section
|
[EXCEPTION]
|
[EXCEPTION]
|
[EXCEPTION]
|
-
|
[EXCEPTION]
|
[exception handling]
|
[exception handling]
|
[exception handling]
|
-
|
[exception handling]
|
END;
|
END;
|
END;
|
END;
|
END;
|
/
|
/
|
/
|
/
|
/
|
學習初級Oracle PL/SQL (四)
學習初級Oracle PL/SQL (二)
沒有留言:
張貼留言