【转】Oracle TYPE OBJECT(2)
一、子类型
这种类型最简单，类似类型的一个别名，主要是为了对常用的一些类型简单化，它基于原始的某个类型。如：
有些应用会经常用到一些货币类型：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 typ_calendar as object(
年 varchar2(8),
月 varchar2(8),
星期日 varchar2(8),
星期一 varchar2(8),
星期二 varchar2(8),
星期三 varchar2(8),
星期四 varchar2(8),
星期五 varchar2(8),
星期六 varchar2(8),
本月最后一日 varchar2(2)
);
create table tcalendar of typ_calendar;
SQL> insert into tcalendar
2 select typ_calendar('2010','05','1','2','3','4','5','6','7','31') from dual
3 /
三、带成员函数的类型体
这种类型包含了对类型中数据的内部处理，调用该类型时，可将处理后的数据返回给调用方。
对上面的例子进行扩展。要求给当天加上特殊标识（【】）来突出显示。
首先，在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();
举个例子：
首先，创建一个带静态函数声明的类型头：
> create or replace type body typ_col as
2 static function to_upper
3 return typ_col as
4 begin
5 return typ_col(upper(col_name), upper(tab_name));
6 end to_upper;
7 end;
8 /
Warning: Type body created with compilation errors
SQL> show errors
Errors for TYPE BODY LYON.TYP_COL:
LINE/COL ERROR
-------- ---------------------------------------------------
5/30 PLS-00588: 非限定实例属性引用只允许在成员方法中使用
5/9 PL/SQL: Statement ignored
错误信息表明，实例属性只能在成员方法中使用。这里隐去了self的调用，其实：
typ_col(upper(col_name), upper(tab_name));
等价于：
typ_col(upper(self.col_name), upper(self.tab_name));
而这种方式的使用根据前面的定义，只能在成员函数中实现：
SQL> create or replace type typ_col as object(
2 col_name varchar2(30),
3 tab_name varchar2(30),
4 member function to_upper return typ_col
5 )
6 /
Type created
SQL>
SQL> create or replace type body typ_col as
2 member function to_upper
3 return typ_col as
4 begin
5 return typ_col(upper(self.col_name), upper(self.tab_name));
6 end to_upper;
7 end;
8 /
Type body created
那两者调用上又有什么差别呢？
按照前面的定义，静态函数的用法是type_name.method()，所以有：
SQL> select typ_col.to_lower(x).tab_name, typ_col.to_lower(x).col_name from tcol
SQL> select typ_col(column_name, table_name).to_upper().tab_name,
2 typ_col(column_name, table_name).to_upper().col_name
3 from user_tab_columns t
4 where rownum <= 10;
也就是说，静态函数主要是用于处理并返回外部数据的，而成员函数是用于处理并返回内部数据的。
然后可以在函数中使用该类型，下面是一个显示日历的函数，并调用类型的成员函数对结果做了格式化：
create or replace function show_calendar(
v_yermonth varchar2 := to_char(sysdate, 'yyyymm'))
return tbl_calendar as
v_cal tbl_calendar;
v_seg pls_integer := 6;
v_len pls_integer := 8;
v_yer varchar2(4) := substr(v_yermonth, 1, 4);
v_mon varchar2(2) := lpad(substr(v_yermonth, 5, 2), 2, '0');
v_ini date := to_date(v_yermonth || '01', 'yyyymmdd');
begin
select typ_calendar(v_yer, v_mon,
case when rn >= wkn - 1 and rn - wkn + 2 <= mxdays
then rn - wkn + 2 end,
case when rn >= wkn - 2 and rn - wkn + 3 <= mxdays
then rn - wkn + 3 end,
case when rn >= wkn - 3 and rn - wkn + 4 <= mxdays
then rn - wkn + 4 end,
case when rn >= wkn - 4 and rn - wkn + 5 <= mxdays
then rn - wkn + 5 end,
case when rn >= wkn - 5 and rn - wkn + 6 <= mxdays
then rn - wkn + 6 end,
case when rn >= wkn - 6 and rn - wkn + 7 <= mxdays
then rn - wkn + 7 end,
case when rn >= wkn - 7 and rn - wkn + 8 <= mxdays
then rn - wkn + v_len end,
mxdays).format()
bulk collect into v_cal
from (select (rownum - 1)*7 rn,
to_number(to_char(trunc(v_ini, 'mm'), 'd')) wkn,
to_number(to_char(last_day(v_ini), 'dd')) mxdays
from dual
connect by rownum <= v_seg) b
where rn - wkn + 2 <= mxdays; --过滤空行
return v_cal;
end show_calendar;
获得当前月的日历：
SQL> select * from table(show_calendar);
获取指定月份的日历：
SQL> select * from table(show_calendar('201001'));
显示多个月的日历：
SQL> select b.*
2 from (select to_char(add_months(date'1998-01-01', rownum-1), 'yyyymm') c from dual connect by rownum <= 10) a,
3 table(show_calendar(to_char(a.c))) b
4
上面是一个特殊的table函数使用方法。
即将a表中构造的月份，作为参数传入到table函数中的show_calendar函数中，然后show_calendar函数根据指定的月份返回
该月的日历。实现了获取多个月日历的要求。
自定义type的一个限制是不能使用rowid类型：
SQL> create or replace type typ_rowid as object(rid urowid);
2 /
Warning: Type created with compilation errors
SQL> show errors;
Errors for TYPE CUSTOMER21.TYP_ROWID:
LINE/COL ERROR
-------- ---------------------------------------------------
1/30 PLS-00530: 为此对象类型属性使用了非法类型: UROWID。
其他的特殊使用还有自定义聚集函数，典型的例子就是字符串相加的问题。
我们知道，对数字列的相加很简单，直接求sum即可。但是如何对字符列进行相加呢？
如：
SQL> with tmp as (
2 select '1' c from dual union all
3 select '2' c from dual union all
4 select '3' c from dual union all
5 select '4' c from dual)
6 select * from tmp
7 /
1，2，3，4要合并为1->2->3->4，该如何实现？
一个办法是用层级查询来实现（用sys_connect_by_path即可）。
另外，10g下，还可以用wm_sys.wm_concat函数来实现。
还有就是自定义聚集函数了。自定义聚集函数首先要定义一个类型，在类型中调用了Oracle内部实现的几个接口函数:
CREATE OR REPLACE TYPE "TYP_STRCAT" as object
(
strsum varchar2(4000),
strcnt number,
strdelimit varchar2(10),
static function ODCIAggregateInitialize(
actx in out typ_strcat)
return number,
member function ODCIAggregateIterate(
self in out typ_strcat,
val in varchar2)
return number,
member function ODCIAggregateTerminate(
self in typ_strcat,
returnvalue out varchar2,
flags in number)
return number,
member function ODCIAggregateMerge(
self in out typ_strcat,
ctx2 typ_strcat)
return number
)
CREATE OR REPLACE TYPE BODY "TYP_STRCAT" as
static function ODCIAggregateInitialize(actx in out typ_strcat)
return number as
begin
actx := typ_strcat(null, 1, ',');
return ODCICONST.Success;
end;
member function ODCIAggregateIterate(self in out typ_strcat,
val in varchar2) return number as
begin
self.strsum := self.strsum || strdelimit || val;
self.strcnt := self.strcnt + 1;
return ODCICONST.Success;
end;
member function ODCIAggregateTerminate(self in typ_strcat,
returnvalue out varchar2,
flags in number) return number as
begin
returnvalue := ltrim(self.strsum, strdelimit);
return Odciconst.Success;
end;
member function ODCIAggregateMerge(self in out typ_strcat,
ctx2 in typ_strcat) return number as
begin
self.strsum := ctx2.strsum || self.strsum;
return Odciconst.Success;
end;
end;
然后创建函数：
CREATE OR REPLACE FUNCTION "SSUM" (p_str varchar2)
return varchar2
/*parallel_enable*/ aggregate using typ_strcat;
然后，就可以使用字符串相加的功能了：
SQL> with tmp as (
2 select '1' c from dual union all
3 select '2' c from dual union all
4 select '3' c from dual union all
5 select '4' c from dual)
6 select replace(ssum(c), ',', '->') from tmp
7 /
REPLACE(SSUM(C),',','->')
--------------------------------------------------------------------------------
1->2->3->4
表类型
这种类型类似于一个数组类型，可以申明一维或多维。
比如说，创建一个元素长度为4000的字符串数组，则有：
create or replace type tbl_varchar2 as table of varchar2(4000)
SQL> select * from table(tbl_varchar2('1','1','3','4','5','6'));
如果要获取多字段的，则可以取上面例子：
SQL> select *
2 from table(tbl_calendar(
3 typ_calendar('2008','2','3','4','5','6','7','8','9','28'),
4 typ_calendar('2009','12','13','4','5','6','7','8','9','31'),
5 typ_calendar('2010','12','13','4','5','6','7','8','9','31')));
以上使用的类型都基于schema级别，如果是定义在包、函数、过程等这些结构里是不能给table函数使用的。
这种类型可以使用在管道函数中（pipelined function）。也可以存放中间处理的数据，类似于临时表的作用，但是是存放在内存中的。
