-+lizzy+-
- 浏览: 19900 次
- 性别:
- 来自: 北京
-
最近访客 更多访客>>
文章分类
最新评论
-
zhaojj19870824：
公司都挺好，前一个看你在哪个部门了。我在那个不怎么好。
IBM ISSC和深圳日立环球存储 哪个好一点. -
叶智光：
IBM gdc我曾经呆过，网上很多人骂，但是我个人经历觉得是个 ...
IBM ISSC和深圳日立环球存储 哪个好一点. -
workman93：
小徐是谁阿？不会是徐志摩吧？
occasional -
-+lizzy+-：
看到一个帖子里面小徐的诗，正好在看英语，就想知道我用英语会怎么 ...
occasional -
workman93：
呵呵。看不懂。最近还很忙么？
occasional
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)、binary_float(10g)、boolean。plsql类型是不能在sql环境中使用的，比如建表时。
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）。也可以存放中间处理的数据，类似于临时表的作用，但是是存放在内存中的。
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)、binary_float(10g)、boolean。plsql类型是不能在sql环境中使用的，比如建表时。
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）。也可以存放中间处理的数据，类似于临时表的作用，但是是存放在内存中的。
分享到：
发表评论
相关推荐
Type主要有以下几种类型： - **OBJECT**：用于表示单个的对象。 - **TABLE OF**：用于表示对象数组。 - **VARRAY**：用于表示变长数组。 - **NESTED TABLE**：用于表示可排序的数组。 ### 二、创建Type的方法 ###...
除了上述两种基本的行列转换外，还有其他几种转换类型，包括多列到字符串、多行到字符串、字符串到多列以及字符串到多行的转换。这些转换通常涉及对数据进行重新组织或重组，以便于后续的处理或展示。 - **多列转换...
在Oracle数据库中，PL/SQL和JAVA是两种常见的编程语言，它们可以协同工作，实现复杂的业务逻辑。在处理Oracle中的自定义对象类型...在实际开发中，尤其是处理大量Oracle自定义类型时，JPublisher是一个非常实用的工具。
本文从应用移植的角度描述了 ORACLE 和 DB2 存在的一些差异，列举了几种移植方案，结合作者参与的项目，对基干 MTK 的移植方案：MTK 移植工具的应用和两个数据库系统中不兼容的数据库对象的移植策略做了进一步的探讨...
Oracle2: 1． 《Oracle8 优化技术》摘录 (第一章 安装) 2． 《Oracle8 优化技术》摘录 (第二章 ...50. 怎样在SQLPlus中使用 '&' 来实现自定义参数变量？ 51. 怎样在查询记录时给记录加锁 52. 自动备份Oracle数据库
其中，`init.ora`是一种较早版本的参数文件格式，在Oracle 9i及以前的版本中广泛使用。而从Oracle 9i开始，官方推荐使用`spfile`，它以二进制格式存储，更有利于提高性能。尽管如此，在某些场景下，了解`init.ora`...
Oracle提供了几种不同的方式来实现数组，包括VARARRAY、NESTED TABLE和INDEXED TABLE等。每种类型的数组都有其独特的特性和应用场景。 ### 自定义类型：VARARRAY **VARARRAY** 是一种固定大小的数组类型，它可以...
本文将详细介绍Oracle存储过程与函数的创建、使用方法以及常见操作。 #### 二、创建存储过程 存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程的优点包括提高性能、减少网络流量、...
在Oracle数据库中，我们可以使用以下几种方法来查询主表和从表： 1. **通过数据字典视图**： Oracle提供了一些内置的数据字典视图，如`USER_CONSTRAINTS`, `ALL_CONSTRAINTS`, 和 `DBA_CONSTRAINTS`，它们包含了...
其一、就业面广：全球前100强企业99家都在使用ORACLE相关技术，中国政府机构，大中型企事业单位都能有ORACLE技术的工程师岗位。 其二、技术层次深：如果期望进入IT服务或者产品公司（类似毕博、DELL、IBM等），...
根据提供的文档内容，我们可以归纳出一系列关于Oracle数据库中PL/SQL编程的重要知识点，这些知识点主要集中在以下几个方面： ### 一、基本语法 #### 1. `%type` 用法 `%type` 是一个PL/SQL特性，它允许变量继承...
Oracle Text 支持以下几种数据类型： - **VARCHAR2**: 最常见的文本数据类型，适用于短文本字段。 - **LONG**: 适用于较长的文本数据。 - **CLOB**: 用于存储大量的文本数据，例如文章、报告等。 - **NCLOB**: 与 ...
首先，Oracle分页主要有以下几种方法： 1. **ROWNUM**：这是Oracle中最基础的分页方式，通过在查询语句中结合ROWNUM伪列来限制返回的结果行数。例如： ```sql SELECT * FROM ( SELECT column1, column2, ..., ...
- **格式化查询结果**：使用`COLUMN`命令来格式化列的显示，如`COLUMN column_name FORMAT data_type`。 #### 数据库语言 1. **数据定义语言（DDL）**：用于定义数据库的结构，如创建、修改和删除表、视图、索引等...
11. 使用%TYPE和%ROWTYPE属性：这是PL/SQL中用于声明变量的数据类型与数据库表或列的数据类型一致的方法。 12. DML语句的使用：如UPDATE, DELETE语句用于更改或删除数据库表中的数据。 13. 使用ROWID伪列：可以...
对于JDBC，可以使用`OracleCallableStatement`的`setArray`方法： ```java OracleCallableStatement cs = (OracleCallableStatement) connection.prepareCall("{call proc(?)}"); ArrayDescriptor arrayDesc = ...
存储过程的参数类型主要有以下几种： 1. 无参数的存储过程：不需要传入任何参数。 2. 仅有输入参数的过程：只允许输入参数，可以有返回值。 3. 仅有输出参数的过程：调用者需要提供输出参数，存储过程会根据逻辑...
### Oracle存储过程详解 ...总之，Oracle存储过程是一种非常强大的工具，可以帮助开发者构建高性能、安全且易于维护的应用程序。通过合理设计和使用存储过程，可以显著提高数据库应用程序的整体质量。