oracle如何自定义类型,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();

举个例子：

首先，创建一个带静态函数声明的类型头：

原文：http://www.cnblogs.com/shcqupc/p/5099035.html