Oracle 自定义type的几种写法(转载--备份)

今天药忘吃喽~ 2022-08-02 18:57 224阅读 0赞

转载自 http://www.cnblogs.com/gisdream/archive/2012/04/13/2445893.html

很不错，做备份用

Oracle 自定义TYPE 的几种用法

Oracle中的类型有很多种，主要可以分为以下几类：  
1、字符串类型。如：char、nchar、varchar2、nvarchar2。  
2、数值类型。如：int、number(p,s)、integer、smallint。  
3、日期类型。如：date、interval、timestamp。  
4、PL/SQL类型。如：pls\_integer、binary\_integer、binary\_double([**10g**][10g])、binary\_float(10g)、boolean。plsql类型是不能在[**sql**][10g]环境中使用的，比如建表时。  
5、自定义类型。

下面简单的枚举下常用的几种自定义类型。  
1、子类型。  
这种类型最简单，类似类型的一个别名，主要是为了对常用的一些类型简单化，它基于原始的某个类型。如：  
有些应用会经常用到一些货币类型：number(16,2)。如果在全局范围各自定义这种类型，一旦需要修改该类型的精度，则需要一个个地修改。  
那如何实现定义的全局化呢？于是就引出了子类型：  
subtype cc\_num is number(16,2);  
这样就很方便地解决了上述的麻烦。

2、普通类型  
如：  
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进行转换。

1 row inserted

\--查看结果  
SQL> select \* from tcalendar;  
  
年       月       星期日   星期一   星期二   星期三   星期四   星期五   星期六   本月最后一日  
\-------- -------- -------- -------- -------- -------- -------- -------- -------- ------------  
2010     05       1        2        3        4        5        6        7        31

3、带成员函数的类型体（type body）  
这种类型包含了对类型中数据的内部处理，调用该类型时，可将处理后的数据返回给调用方。  
对上面的例子进行扩展。要求给当天加上特殊标识（【】）来突出显示。  
首先，在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;  
  
年       月       星期日   星期一   星期二   星期三   星期四   星期五   星期六   本月最后一日  
\-------- -------- -------- -------- -------- -------- -------- -------- -------- ------------  
2010     05       1        2        3        4        5        6        7        31  
2010     05              1        2        3        4        5        6        7 31  
2010     05             11   【12】       13       14       15       16       17 31

可以看到数据已经居中处理了，并且到了第三条已经可以突出显示当前日期了。  
在这里type 中的成员函数(member function)和静态函数(static function)的区别有必要说明一下：  
成员函数有隐含参数self，即自身类型，可以在执行的时候引用当前的数据并对数据进行操作。它的调用可以如下：object\_expression.method()  
静态函数没有该隐含参数。它的调用如下：type\_name.method();

举个例子：  
首先，创建一个带静态函数声明的类型头：  
SQL> create or replace type typ\_col as object(  
  2      col\_name varchar2(30),  
  3      tab\_name varchar2(30),  
  4      static function to\_upper return typ\_col  
  5  )  
  6  /  
  
Type created

然后创建类型体：  
SQL>  
SQL> 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  
  2  /  
  
TYP\_COL.TO\_LOWER(X).TAB\_NAME   TYP\_COL.TO\_LOWER(X).COL\_NAME  
\------------------------------ ------------------------------  
ipseg\_int\_db\_tmp               start\_ip  
ipseg\_int\_db\_tmp               end\_ip  
ipseg\_int\_db\_tmp               area\_code  
px\_city                        city\_id  
px\_city                        city\_name  
px\_city                        province\_id  
px\_citygdp                     gdp\_cycle\_id  
px\_citygdp                     city\_id  
px\_citygdp                     gdp  
px\_citygdp                     province\_id  
  
10 rows selected

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;  
  
TYP\_COL(COLUMN\_NAME,TABLE\_NAME TYP\_COL(COLUMN\_NAME,TABLE\_NAME  
\------------------------------ ------------------------------  
DEMO                           X  
IPSEG\_INT\_DB\_TMP               START\_IP  
IPSEG\_INT\_DB\_TMP               END\_IP  
IPSEG\_INT\_DB\_TMP               AREA\_CODE  
PX\_CITY                        CITY\_ID  
PX\_CITY                        CITY\_NAME  
PX\_CITY                        PROVINCE\_ID  
PX\_CITYGDP                     GDP\_CYCLE\_ID  
PX\_CITYGDP                     CITY\_ID  
PX\_CITYGDP                     GDP  
  
10 rows selected  
也就是说，静态函数主要是用于处理并返回外部数据的，而成员函数是用于处理并返回内部数据的。

然后可以在函数中使用该类型，下面是一个显示日历的函数，并调用类型的成员函数对结果做了格式化：  
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);  
/  
  
年       月       星期日   星期一   星期二   星期三   星期四   星期五   星期六   本月最后一日  
\-------- -------- -------- -------- -------- -------- -------- -------- -------- ------------  
2010     05                                                                    1 31  
2010     05              2        3        4        5        6        7        8 31  
2010     05              9       10       11   【12】       13       14       15 31  
2010     05             16       17       18       19       20       21       22 31  
2010     05             23       24       25       26       27       28       29 31  
2010     05             30       31                                              31  
  
6 rows selected

获取指定月份的日历：  
SQL> select \* from table(show\_calendar('201001'));  
  
年       月       星期日   星期一   星期二   星期三   星期四   星期五   星期六   本月最后一日  
\-------- -------- -------- -------- -------- -------- -------- -------- -------- ------------  
2010     01                                                           1        2 31  
2010     01              3        4        5        6        7        8        9 31  
2010     01             10       11       12       13       14       15       16 31  
2010     01             17       18       19       20       21       22       23 31  
2010     01             24       25       26       27       28       29       30 31  
2010     01             31                                                       31  
  
6 rows selected  
  
显示多个月的日历：  
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  /  
  
年       月       星期日   星期一   星期二   星期三   星期四   星期五   星期六   本月最后一日  
\-------- -------- -------- -------- -------- -------- -------- -------- -------- ------------  
1998     01                                                  1        2        3 31  
1998     01              4        5        6        7        8        9       10 31  
1998     01             11       12       13       14       15       16       17 31  
1998     01             18       19       20       21       22       23       24 31  
1998     01             25       26       27       28       29       30       31 31  
1998     02              1        2        3        4        5        6        7 28  
1998     02              8        9       10       11       12       13       14 28  
1998     02             15       16       17       18       19       20       21 28  
...............  
1998     09                                1        2        3        4        5 30  
1998     09              6        7        8        9       10       11       12 30  
1998     09             13       14       15       16       17       18       19 30  
1998     09             20       21       22       23       24       25       26 30  
1998     09             27       28       29       30                            30  
1998     10                                                  1        2        3 31  
1998     10              4        5        6        7        8        9       10 31  
1998     10             11       12       13       14       15       16       17 31  
1998     10             18       19       20       21       22       23       24 31  
1998     10             25       26       27       28       29       30       31 31  
  
51 rows selected  
上面是一个特殊的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  /  
  
C  
\-  
1  
2  
3  
4  
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

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'));  
  
COLUMN\_VALUE  
\--------------------------------------------------------------------------------  
1  
1  
3  
4  
5  
6  
  
6 rows selected  
  
如果要获取多字段的，则可以取上面例子：  
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')));  
  
年       月       星期日   星期一   星期二   星期三   星期四   星期五   星期六   本月最后一日  
\-------- -------- -------- -------- -------- -------- -------- -------- -------- ------------  
2008     2        3        4        5        6        7        8        9        28  
2009     12       13       4        5        6        7        8        9        31  
2010     12       13       4        5        6        7        8        9        31

以上使用的类型都基于schema级别，如果是定义在包、函数、过程等这些结构里是不能给table函数使用的。  
这种类型可以使用在管道函数中（pipelined function）。也可以存放中间处理的数据，类似于临时表的作用，但是是存放在内存中的。

[10g]: http://www.cnblogs.com/gisdream/admin/javascript:;