Oracle 自訂類型

標籤：

一、子類型 
這種類型最簡單，類似類型的一個別名，主要是為了對常用的一些類型簡單化，它基於原始的某個類型。如： 
有些應用會經常用到一些貨幣類型：number(16,2)。如果在全域範圍各自訂這種類型，一旦需要修改該類型的精度，則需要一個個地修改。 
那如何?定義的全域化呢？於是就引出了子類型： 
subtype cc_num is number(16,2); 
這樣就很方便地解決了上述的麻煩。

 

我們可以在任何PL/SQL塊、子程式或包中定義自己的子類型

SUBTYPE  subtype_name IS  base_type[(constraint)] [NOT  NULL ]; 

subtype_name就是聲明的子類型的名稱，base_type可以是任何標量類型或使用者定義型別，約束只是用於限定基底類型的精度和數值範圍，或是最大長度。下面舉幾個例子：

DECLARE 
  SUBTYPE  birthdate IS  DATE  NOT  NULL ;   -- based on DATE type

  SUBTYPE  counter IS  NATURAL ;   -- based on NATURAL subtype

  TYPE  namelist IS  TABLE  OF  VARCHAR2 (10);

  SUBTYPE  dutyroster IS  namelist;   -- based on TABLE type

  TYPE  timerec IS  RECORD (
    minutes   INTEGER ,
    hours     INTEGER 
  );

  SUBTYPE  finishtime IS  timerec;   -- based on RECORD type

  SUBTYPE  id_num IS  emp.empno%TYPE ;   -- based on column type 

我們可以使用%TYPE或%ROWTYPE來指定基底類型。當%TYPE提供資料庫欄位中的資料類型時，子類型繼承欄位的大小約束(如果有的話)。但是，子類型並不能繼承其他約束，如NOT NULL。

2、使用子類型

一旦我們定義了子類型，我們就可以聲明該類型的變數、常量等。下例中，我們聲明了Counter類型變數，子類型的名稱代表了變數的使用目的：

DECLARE 
  SUBTYPE  counter IS  NATURAL ;

  ROWS   counter; 

下面的例子示範了如何約束使用者自訂子類型：

DECLARE 
  SUBTYPE  accumulator IS  NUMBER ;

  total   accumulator(7, 2); 

子類型還可以檢查數值是否越界來提高可靠性。下例中我們把子類型Numeral的範圍限制在-9到9之間。如果程式把這個範圍之外的數值賦給Numeral類型變數，那麼PL/SQL就會拋出一個異常。

DECLARE 
  SUBTYPE  numeral IS  NUMBER (1, 0);

  x_axis   numeral;   -- magnitude range is -9 .. 9 
  y_axis   numeral;
BEGIN 
  x_axis    := 10;   -- raises VALUE_ERROR 
  ...
END ; 

 

二、普通類型

create or replace type SH_type1 as object (
col1 number,
col2 varchar2(50),
col3 date,
);
create or replace type SH_TABLE_TYPE1 IS TABLE OF SH_type1;
create table t1_type of SH_type1;
insert into t1_type(COL1,COL2,COL3) values(SH_sqe_ASC.NEXTVAL,‘hello‘,sysdate-10);
SELECT * FROM T1_TYPE;

 

三、帶成員函數的類型體

這種類型包含了對類型中資料的內部處理，調用該類型時，可將處理後的資料返回給調用方。 
對上面的例子進行擴充。要求給當天加上特殊標識（【】）來反白。 
首先，在typ_calendar中增加一個成員函式宣告： 

create or replace type typ_calendar as object( 

年 varchar2(8),
月 varchar2(8),
星期日 varchar2(8),
星期一 varchar2(8),
星期二 varchar2(8),
星期三 varchar2(8),
星期四 varchar2(8),
星期五 varchar2(8),
星期六 varchar2(8),
本月最後一日 varchar2(2),

member function format(
curday date := sysdate,
fmtlen pls_integer := 8
)return typ_calendar
)

然後，建立一個type body，在type body中實現該成員函數： 

create or replace type body typ_calendar as
member function format(
curday date := sysdate,
fmtlen pls_integer := 8
) return typ_calendar as
v_return typ_calendar := typ_calendar(‘‘,‘‘,‘‘,‘‘,‘‘,‘‘,‘‘,‘‘,‘‘,‘‘);
v_dd varchar2(2) := to_char(curday, ‘dd‘);

function fmt(
fmtstr varchar2
)return varchar2 as
begin
return lpad(fmtstr, fmtlen, ‘ ‘);
end fmt;
begin
v_return.年 := 年;
v_return.月 := 月;
v_return.星期日 := fmt(星期日);
v_return.星期一 := fmt(星期一);
v_return.星期二 := fmt(星期二);
v_return.星期三 := fmt(星期三);
v_return.星期四 := fmt(星期四);
v_return.星期五 := fmt(星期五);
v_return.星期六 := fmt(星期六);
v_return.本月最後一日 := 本月最後一日;
if (年 || lpad(月, 2, ‘0‘) = to_char(curday, ‘yyyymm‘)) then
case v_dd
when 星期日 then
v_return.星期日 := fmt(‘【‘ || 星期日 || ‘】‘);
when 星期一 then
v_return.星期一 := fmt(‘【‘ || 星期一 || ‘】‘);
when 星期二 then
v_return.星期二 := fmt(‘【‘ || 星期二 || ‘】‘);
when 星期三 then
v_return.星期三 := fmt(‘【‘ || 星期三 || ‘】‘);
when 星期四 then
v_return.星期四 := fmt(‘【‘ || 星期四 || ‘】‘);
when 星期五 then
v_return.星期五 := fmt(‘【‘ || 星期五 || ‘】‘);
when 星期六 then
v_return.星期六 := fmt(‘【‘ || 星期六 || ‘】‘);
else null;
end case;
end if;

return v_return;
end format;
end;

插入測試資料： 

SQL> insert into tcalendar
2 select typ_calendar(‘2010‘,‘05‘,‘1‘,‘2‘,‘3‘,‘4‘,‘5‘,‘6‘,‘7‘,‘31‘) from dual
3 /
1 row inserted
SQL> insert into tcalendar
2 select typ_calendar(‘2010‘,‘05‘,‘1‘,‘2‘,‘3‘,‘4‘,‘5‘,‘6‘,‘7‘,‘31‘).format() from dual
3 /
1 row inserted
SQL> insert into tcalendar
2 select typ_calendar(‘2010‘,‘05‘,‘11‘,‘12‘,‘13‘,‘14‘,‘15‘,‘16‘,‘17‘,‘31‘).format() from dual
3 /
1 row inserted
SQL> select * from tcalendar;

 

可以看到資料已經置中處理了，並且到了第三條已經可以反白當前日期了。 
在這裡type 中的成員函數(member function)和靜態函數(static function)的區別有必要說明一下： 
成員函數有隱含參數self，即自身類型，可以在執行的時候引用當前的資料並對資料進行操作。它的調用可以如下：object_expression.method() 
靜態函數沒有該隱含參數。它的調用如下：type_name.method();

舉個例子： 
首先，建立一個帶靜態函式宣告的類型頭： 

 

Oracle 自訂類型