Oracle 自定义类型TYPE（subtype, type object, type body, type table）用法

秒速五厘米 2022-04-10 05:31 1241阅读 0赞

原

# ORACLE 自定义类型 type/ object #

2017年03月28日 13:58:23 [tan1012194073][] 阅读数：5999

**一：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、自定义类型：type / create type。

**二：type / create type 区别联系**

相同：

可用用关键字create type 或者直接用type定义自定义类型,

区别：

1、create type 变量 as table of 类型

2、create type 变量 as object(

字段1 类型1,

字段2 类型2

);

\--------------------------

3、type 变量 is table of 类型

4、type 变量 is record(

字段1 类型1,

字段2 类型2

);

用 create 后面用 as , 若直接用 type 后面用 is

create 是创 object , 而 type 是创 record .

区别是 用 create 后面用 as , 若直接用 type 后面用 is

create 是创 object , 而 type 是创 record .

一般定义object的语法:

用

create type 自定义表类型A as table of 自定义Object类型A

和

create type 自定义Object类型A as object(

字段1 类型1,

字段2 类型2

);

type 自定义表类型B is table of 类型

和

type 自定义Object类型B is record(

字段1 类型1,

字段2 类型2

);

自定义类型一般分为两中,object类型和table类型.object类似于一个recored，可以表示一个表的一行数据,

object的字段就相当与表的字段.

自定义的table类型需要用的已经定义好的object类型.

**三：type record用法概述**

type 自定义Object类型B is record(

字段1 类型1,

字段2 类型2

);

**3.1：什么是记录（Record）？**  
由单行多列的标量构成的复合结构。可以看做是一种用户自定义数据类型。组成类似于多维数组。  
将一个或多个标量封装成一个对象进行操作。是一种临时复合对象类型。

记录可以直接赋值。RECORD1 :=RECORD2；  
记录不可以整体比较.  
记录不可以整体判断为空。

**3.2：%ROWTYPE和记录（Record）？**  
请区别%ROWTYPE和记录（Record）类型。%ROWTYPE可以说是Record的升级简化版。  
区别在与前者结构为表结构，后者为自定义结构。二者在使用上没有很大区别。前者方便，后者灵活。在实际中根据情况来具体决定使用。  
Record + PL/SQL表可以进行数据的多行多列存储。

**3.3：如何创建和使用记录？**  
  ①创建记录类型   语法：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>TYPE&nbsp; 记录名&nbsp;&nbsp;</code><code>IS</code>&nbsp;<code>RECORD</code> <code>　(</code> <code>&nbsp;&nbsp;</code><code>filed1 type1 [</code><code>NOT</code>&nbsp;<code>NULL</code><code>] [：=eXPr1],</code> <code>　&nbsp;&nbsp;&nbsp;&nbsp; ....... ,</code> <code>　&nbsp; filedN typen [</code><code>NOT</code>&nbsp;<code>NULL</code><code>] [：=exprn]</code> <code>）</code> </td> 
 </tr> 
 </tbody> 
</table>

记录可以整体赋值

记录不可以整体比较，只可以比较记录字段

记录不可以整体判断为空，只可以判断记录字段

使用%TYPE和%ROWTYPE动态指定记录字段。

**使用记录向表中插入数据？ **根据表结构合理安排记录字段。比如主外键。 如果用记录(RECORD)插入数据，那么只能使用记录成员； 如果用%ROWTYPE插入数据,可以直接使用%ROWTYPE。  
例子：使用记录成员向表中插入数据

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 </td> 
 <td> <code>DECLARE</code> <code>Type MyRecType&nbsp;</code><code>Is</code>&nbsp;&nbsp;<code>Record</code> <code>(</code> <code>&nbsp;&nbsp;</code><code>RENO&nbsp; EMPA.EMPNO%Type,</code> <code>&nbsp;&nbsp;</code><code>RENAME&nbsp;&nbsp; VARCHAR2(10),</code> <code>&nbsp;&nbsp;</code><code>RJOB&nbsp;&nbsp; EMPA.JOB%Type</code> <code>);</code> <code>EmpRec MyRecType;</code> <code>Begin</code> <code>Select</code>&nbsp;&nbsp;&nbsp;<code>EMPNO, ENAME, JOB&nbsp;&nbsp;</code><code>InTo</code>&nbsp;&nbsp;<code>EmpRec&nbsp;&nbsp;</code><code>From</code>&nbsp;<code>empa&nbsp;</code><code>Where</code>&nbsp;<code>empa.EMPNO =&nbsp;</code><code>'7369'</code><code>;</code> <code>DBMS_OUTPUT.PUT_LINE(EmpRec.RENO||</code><code>'&nbsp; '</code><code>||EmpRec.RENAME||</code><code>'&nbsp; '</code><code>||EmpRec.RJOB);</code> <code>EmpRec.RENO&nbsp; := 1001;</code> <code>EmpRec.RENAME :=&nbsp;</code><code>'杰克'</code><code>;</code> <code>EmpRec.RJOB&nbsp; :=&nbsp;</code><code>'办事员'</code><code>;</code> <code>Insert</code>&nbsp;<code>InTo</code>&nbsp;&nbsp;<code>empa(EMPNO,ENAME,JOB)&nbsp;&nbsp;</code><code>Values</code><code>(EmpRec.RENO, EmpRec.RENAME,EmpRec.RJOB);</code> <code>Select</code>&nbsp;&nbsp;<code>EMPNO, ENAME, JOB&nbsp;&nbsp;</code><code>InTo</code>&nbsp;&nbsp;<code>EmpRec&nbsp;&nbsp;</code><code>From</code>&nbsp;<code>empa&nbsp;</code><code>Where</code>&nbsp;<code>empa.EMPNO =&nbsp;</code><code>'1001'</code><code>;</code> <code>DBMS_OUTPUT.PUT_LINE(EmpRec.RENO||</code><code>'&nbsp; '</code><code>||EmpRec.RENAME||</code><code>'&nbsp; '</code><code>||EmpRec.RJOB);</code> <code>End</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

**3.5：使用记录更新数据？** 如果用记录(RECORD)更新数据，那么只能使用记录成员； 如果用%ROWTYPE更新数据,可以直接使用%ROWTYPE。  
例子：使用%ROWTYPE向表中插入数据

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>DECLARE</code> <code>vEmp empa%RowType;</code> <code>Begin</code> <code>Select</code>&nbsp;&nbsp;<code>*&nbsp;&nbsp;</code><code>InTo</code>&nbsp;&nbsp;<code>vEmp&nbsp;&nbsp;</code><code>From</code>&nbsp;<code>empa&nbsp;</code><code>Where</code>&nbsp;<code>empa.EMPNO =&nbsp;</code><code>'7369'</code><code>;</code> <code>UpDate</code>&nbsp;<code>empa&nbsp;</code><code>Set</code>&nbsp;<code>ROW = vEmp&nbsp;</code><code>Where</code>&nbsp;<code>EMPNO = 1001;</code> <code>End</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

**四：type  table用法**

4.1：定义

type 变量 is table of 类型

TYPE orders\_type IS TABLE OF all\_orders%ROWTYPE;

4.2:用法

1. TYPE tabletype **IS TABLE  OF** type **INDEX BY** BINARY\_INTEGER;

定义:TYPE t\_charTable **IS TABLE OF** VARCHAR2(10) **INDEX BY** BINARY\_INTEGER;

引用:tableName(**index**);

Oracle中index by binary\_integer的作用

如语句：type  numbers  is table of number index by binary\_integer;其作用是,加了”index by binary\_integer ”后，numbers类型的下标就是自增长，numbers类型在插入元素时，不需要初始化，不需要每次extend增加一个空间。

而如果没有这句话“index by binary\_integer”，那就得要显示对初始化，且每插入一个元素到numbers类型的table中时，都需要先extend.

示例：

没加“index by binary\_integer”时：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code>declare</code> <code>type numbers&nbsp;</code><code>is</code>&nbsp;<code>table</code>&nbsp;<code>of</code>&nbsp;<code>number;</code> <code>n&nbsp;&nbsp;&nbsp; numbers := numbers();</code> <code>begin</code> <code>n.extend;</code> <code>n(1) := 2;</code> <code>n.extend;</code> <code>n(2) := 3;</code> <code>for</code>&nbsp;<code>i in1 .. n.</code><code>count</code>&nbsp;<code>loop</code> <code>dbms_output.put_line(n(i));</code> <code>end</code>&nbsp;<code>loop;</code> <code>end</code><code>;</code> <code>--输出：2，3</code> </td> 
 </tr> 
 </tbody> 
</table>

而如果加了“index by binary\_integer”，代码如下写就可以达到上面的效果

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 </td> 
 <td> <code>declare</code> <code>　　type numbers&nbsp;</code><code>is</code>&nbsp;<code>table</code>&nbsp;<code>of</code>&nbsp;<code>number&nbsp;</code><code>index</code>&nbsp;<code>by</code>&nbsp;<code>binary_integer;</code> <code>　　n numbers;</code> <code>　　</code><code>begin</code> <code>　　n(1) := 2;</code> <code>　　n(2) := 3;</code> <code>　　</code><code>for</code>&nbsp;<code>i in1 .. n.</code><code>count</code>&nbsp;<code>loop</code> <code>　　dbms_output.put_line(n(i));</code> <code>　　</code><code>end</code>&nbsp;<code>loop;</code> <code>　　</code><code>end</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

五：create type 用法

5.1:定义

概念

方法:是在对象类型说明中用关键字  MEMBER   声明的子程序

方法是作为对象类型定义组成部分的一个过程或函数

方法不能与对象类型或其任何属性同名

与数据包程序相似，大多数方法有两个部分

一、抽象数据类型

1、创建类型

\--地址类型

CREATE OR REPLACE TYPE AddressType AS OBJECT

(

Country varchar2(15),

City varchar2(20),

Street varchar2(30)

);

2、类型嵌套

\--创建基于前一个类型的新的抽象数据类型：巨星类型

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>CREATE</code>&nbsp;<code>OR</code>&nbsp;<code>REPLACE</code>&nbsp;<code>TYPE SuperStarType&nbsp;</code><code>AS</code>&nbsp;<code>OBJECT</code> <code>(</code> <code>StarName varchar2(30),</code> <code>Address AddressType</code> <code>);</code> </td> 
 </tr> 
 </tbody> 
</table>

3、基于抽象类型创建关系表

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>CREATE</code>&nbsp;<code>TABLE</code>&nbsp;<code>SuperStar</code> <code>(</code> <code>StarID&nbsp;</code><code>varchar</code><code>(10),</code> <code>Star SuperStarType</code> <code>);</code> </td> 
 </tr> 
 </tbody> 
</table>

4、基于抽象类型创建对象表

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>CREATE</code>&nbsp;<code>TABLE</code>&nbsp;<code>SuperStarObj&nbsp;</code><code>of</code>&nbsp;<code>SuperStarType;</code> </td> 
 </tr> 
 </tbody> 
</table>

5、使用构造方法在表中插入记录

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>INSERT INTO SuperStar VALUES(</code><code>''</code><code>001</code><code>''</code><code>,SuperStarType(</code><code>''</code><code>Zidane</code><code>''</code><code>,AddressType(</code><code>''</code><code>France</code><code>''</code><code>,</code><code>''</code><code>Paris</code><code>''</code><code>,</code><code>''</code><code>People Street NO.</code><code>1</code><code>''</code><code>)));</code> </td> 
 </tr> 
 </tbody> 
</table>

6、查询表中记录

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 </td> 
 <td> <code>（1）SQL&gt;&nbsp;</code><code>SELECT</code>&nbsp;<code>*&nbsp;</code><code>FROM</code>&nbsp;<code>SuperStar;</code> <code>STARID</code> <code>----------</code> <code>STAR(STARNAME, ADDRESS(COUNTRY, CITY, STREET))</code> <code>--------------------------------------------------------------------------------</code> <code>001</code> <code>SUPERSTARTYPE(</code><code>''</code><code>Zidane</code><code>''</code><code>, ADDRESSTYPE(</code><code>''</code><code>France</code><code>''</code><code>,&nbsp;</code><code>''</code><code>Paris</code><code>''</code><code>,&nbsp;</code><code>''</code><code>People Street&nbsp;</code><code>NO</code><code>.1</code><code>''</code><code>))</code> <code>（2）</code> <code>SELECT</code>&nbsp;<code>s.StarID,s.Star.StarName,s.Star.Address.Country,s.Star.Address.City,s.Star.Address.Street&nbsp;</code><code>FROM</code>&nbsp;<code>SuperStar s</code> <code>STARID STAR.STARNAME STAR.ADDRESS.CO STAR.ADDRESS.CITY STAR.ADDRESS.STREET</code> <code>---------- ------------------------------ --------------- -------------------- ---------------------</code> <code>001 Zidane France Paris People Street&nbsp;</code><code>NO</code><code>.1</code> </td> 
 </tr> 
 </tbody> 
</table>

二、可变数组

1、创建带有可变数组的表

（1）创建可变数组的基类型

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>CREATE</code>&nbsp;<code>OR</code>&nbsp;<code>REPLACE</code>&nbsp;<code>TYPE MingXiType&nbsp;</code><code>AS</code>&nbsp;<code>OBJECT</code> <code>(</code> <code>GoodID varchar2(20),</code> <code>InCount&nbsp;</code><code>int</code><code>,</code> <code>ProviderID&nbsp;</code><code>varchar</code><code>(20)</code> <code>);</code> </td> 
 </tr> 
 </tbody> 
</table>

（2）创建嵌套项类型的可变数组

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>CREATE</code>&nbsp;<code>OR</code>&nbsp;<code>REPLACE</code>&nbsp;<code>TYPE arrMingXiType&nbsp;</code><code>AS</code>&nbsp;<code>VARRAY(100)&nbsp;</code><code>OF</code>&nbsp;<code>MingXiType;</code> </td> 
 </tr> 
 </tbody> 
</table>

（3）创建一个主表

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>CREATE</code>&nbsp;<code>TABLE</code>&nbsp;<code>InStockOrder</code> <code>(</code> <code>OrderID&nbsp;</code><code>varchar</code><code>(15)&nbsp;</code><code>Not</code>&nbsp;<code>Null</code>&nbsp;<code>Primary</code>&nbsp;<code>Key</code><code>,</code> <code>InDate&nbsp;</code><code>date</code><code>,</code> <code>OperatorID&nbsp;</code><code>varchar</code><code>(15),</code> <code>MingXi arrMingXiType</code> <code>);</code> </td> 
 </tr> 
 </tbody> 
</table>

2、操作可变数组

（1）插入数据

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>INSERT</code>&nbsp;<code>INTO</code>&nbsp;<code>InStockOrder</code> <code>VALUES</code><code>(</code><code>''</code><code>200710110001</code><code>''</code><code>,TO_DATE(</code><code>''</code><code>2007-10-11</code><code>''</code><code>,</code><code>''</code><code>YYYY-MM-DD</code><code>''</code><code>),</code><code>''</code><code>007</code><code>''</code><code>,</code> <code>arrMingXiType(MingXiType(</code><code>''</code><code>G001</code><code>''</code><code>,100,</code><code>''</code><code>1001</code><code>''</code><code>),</code> <code>MingXiType(</code><code>''</code><code>G002</code><code>''</code><code>,888,</code><code>''</code><code>1002</code><code>''</code><code>))</code> <code>);</code> </td> 
 </tr> 
 </tbody> 
</table>

(2)查询数据

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>SQL&gt;&nbsp;</code><code>SELECT</code>&nbsp;<code>*&nbsp;</code><code>FROM</code>&nbsp;<code>InStockOrder;</code> <code>ORDERID INDATE OPERATORID</code> <code>--------------- --------- ---------------</code> <code>MINGXI(GOODID, INCOUNT, PROVIDERID)</code> <code>----------------------------------------------------------------------</code> <code>200710110001 11-OCT-07 007</code> <code>ARRMINGXITYPE(MINGXITYPE(</code><code>''</code><code>G001</code><code>''</code><code>, 100,&nbsp;</code><code>''</code><code>1001</code><code>''</code><code>), MINGXITYPE(</code><code>''</code><code>G002</code><code>''</code><code>, 888,&nbsp;</code><code>''</code><code>1002</code><code>''</code><code>)</code> </td> 
 </tr> 
 </tbody> 
</table>

(3)使用Table()函数

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>SQL&gt;&nbsp;</code><code>SELECT</code>&nbsp;<code>*&nbsp;</code><code>FROM</code>&nbsp;<code>Table</code><code>(</code><code>SELECT</code>&nbsp;<code>t.MingXi&nbsp;</code><code>FROM</code>&nbsp;<code>InStockOrder t</code> <code>WHERE</code>&nbsp;<code>t.OrderID=</code><code>''</code><code>200710110001</code><code>''</code><code>);</code> <code>GOODID INCOUNT PROVIDERID</code> <code>-------------------- ---------- --------------------</code> <code>G001 100 1001</code> <code>G002 888 1002</code> </td> 
 </tr> 
 </tbody> 
</table>

（4）修改数据

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 </td> 
 <td> <code>UPDATE</code>&nbsp;<code>InStockOrder</code> <code>SET</code>&nbsp;<code>MingXi=arrMingXiType(MingXiType(</code><code>''</code><code>G001</code><code>''</code><code>,200,</code><code>''</code><code>1001</code><code>''</code><code>),</code> <code>MingXiType(</code><code>''</code><code>G002</code><code>''</code><code>,8888,</code><code>''</code><code>1002</code><code>''</code><code>))</code> <code>WHERE</code>&nbsp;<code>OrderID=</code><code>''</code><code>200710110001</code><code>''</code> </td> 
 </tr> 
 </tbody> 
</table>

注意：不能更新VARRAY中的单个元素，必须更新整个VARRAY

以上是部分内容，持续更新Oracle相关

[https://blog.csdn.net/tan1012194073/article/details/67635107][https_blog.csdn.net_tan1012194073_article_details_67635107]

# 【转】Oracle Object type 对象类型详解 #

2016年02月18日 10:40:43 [shcqupc][] 阅读数：1372更多

个人分类： [ORACLE][]

Oracle Object type 对象类型  
  
  
一、概述  
Oracle对象类型是Oracle面向对象程序设计的体现，它封装了数据结构和用于操纵这些数据结构的过程和函数。  
  
  
1、对象类型的组成  
对象类型由两部分组成——对象类型头和对象类型体。  
对象类型头用于定义对象的公用属性和方法；对象类型体用于实现对象类型头所定义的公用方法。  
  
  
2、对象类型属性  
定义对象类型最少要包含一个属性，最多包含1000个属性。定义时必须提供属性名和数据类型，但不能指定默认值和not null。  
数据类型不能包括long、long raw、rowid、urowid和PL/SQL特有类型(boolean\\%type\\%rowtype\\ref curdor等)  
  
  
3、对象类型的方法  
定义对象类型可以包含也可以不包含方法，可以定义构造方法、member方法、static方法、map方法和order方法。  
  
  
1）、构造方法  
用于初始化对象并返回对象实例。构造方法是与对象类型同名的函数，默认的构造方法参数是对象类型的所有属性。  
9i前只能使用系统默认的构造方法、9i后可自定义构造函数，自定义必须使用constructor function关键字  
  
  
2）、member方法  
用于访问对象实例的数据。当使用member方法时，可以使用内置参数self访问当前对象实例。  
当定义member方法时，无论是否定义self参数，它都会被作为第一个参数传递给member方法。  
但如果要定义参数self，那么其类型必须要使用当前对象类型。member方法只能由对象实例调用，而不能由对象类型调用。  
  
  
3）、static方法  
用于访问对象类型，可以在对象类型上执行全局操作，而不需要访问特定对象实例的数据，因此static方法引用self参数。  
static方法只能由对象类型调用，不能由对象实例调用(和member相反)。  
  
  
4）、map方法  
对象类型不同于标量类型可以直接比较，为了按照特定规则排序对象实例的数据，可以定义map方法，但只能有一个，与order互斥。  
map方法将对象实例映射成标量数值来比较。  
  
  
5）、order方法  
map可以在对多个对象实例之间排序，而order只能比较2个实例的大小。定义对象类型时最多只能定义一个order方法，  
而且map和order方法不能同时定义。使用原则是可不用则均不用，比较2个对象则用order，对个对象则用map。  
  
  
4、对象表  
对象表是指至少包含一个对象类型列的表。分为行对象表和列对象表。  
行对象表是指直接基于对象类型所建立的表；列对象表则是只包含一个或多个列的对象表。  
  
  
5、REF数据类型  
ref是指向行对象的逻辑指针，是Oracle的一种内置数据类型。建表时通过使用REF引用行对象，可以使不同表共享相同对象。  
例如：create table department(dno number(2),dname varchar2(10),emp ref employee\_type)  
  
  
  
  
二、对象类型的基本应用  
  
  
1、概述  
基本应用也属于最常规、最简单的应用，讲述如何建立和使用独立的并且与其他对象类型无关的对象类型。  
包括语法、建立带方法和不带方法的对象类型。  
  
  
2、语法  
create or replace type type\_name as object (  
v\_name1 datatype\[,v\_name2 datatype,...\],  
\[member|static method1 spec,member|static method2 spec,...\]);  
  
  
create or replace type body type\_name as  
member|static method1 body;  
member|static method1 body;...  
其中，type\_name是对象类型的名称，v\_name是属性名称，datatype是属性数据类型，method是方法的名称，body是PL/SQL的方法实现代码。  
如果定义对象类型头时没有定义方法，则不需要建立对象类型体。  
  
  
3、建立和使用不包含任何方法的对象类型  
\--建立对象类型  
create or replace type person\_typ1 as object(name varchar2(10),gender varchar2(2),birthdate date);  
  
  
1）、对于行对象表  
\--建立行对象表  
create table persong\_tab1 of person\_typ1;  
\--插入  
begin  
insert into person\_tab1 values('马丽','女','11-1月-76');--不用构造方法  
insert into person\_tab1 values(person\_typ1('王鸣','男','12-2月-76'));--用构造方法  
end;  
\--查询，必须使用value函数取得行数据  
declare  
person person\_typ1;  
begin  
select value(p) into person from person\_tab1 p where p.name='&name';  
dbms\_output.put\_line(person.gender||','||person.birthdate);  
end;  
\--更新  
begin  
update person\_tab1 p set b.birthdate='11-2月-76' where p.name='马丽';  
end;  
\--删除  
begin  
delete from person\_tab1 p where p.name='马丽';  
end;  
  
  
2）、对于列对象表  
\--建立列对象表  
create table employee\_tab1(eno number(6),person person\_typ1,sal number(6.2),job varchar2(10));  
\--插入，必须使用构造方法(默认构造方法)  
begin  
insert into employee\_tab1(eno,sal,job,person) values(1,2000,'高级电工',person\_typ1('王鸣','男'.'01-8月-76'));  
end;  
\--查询  
declare  
employee person\_typ1;  
salary number(6,2);  
begin  
select person,sal into employee,salary from employee\_tab1 where eno=&no;  
dbms\_output.put\_line(employee.name||','||salary);  
end;  
\--更新  
begin  
update employee\_tab1 p set p.person.birthdate='&newdate' where p.person.name='&name';  
end;  
\--删除  
begin  
delete from employee\_tab1 p where p.person.name='王鸣';  
end;  
  
  
4、建立和使用包含方法的对象类型  
  
  
\--建立对象类型头  
create or replace type person\_typ2 as object  
(  
  name      varchar2(10),  
  gender    varchar2(2),  
  birthdate date,  
  address   varchar2(100),  
  regdate   date,  
  member procedure change\_address(new\_addr varchar2),--member方法  
  member function get\_info return varchar2,--member方法  
  static function getdate return date,--static方法  
  map member function getage return varchar2,--map方法  
  order member function compare(p person\_typ2) return int,--order方法  
  constructor person typ2(name varchar2) return self as result,  
  constructor person typ2(name varchar2,gender varchar2) return self as result  
);  
\--建立对象类型体  
create or replace type body person\_typ2 is  
  member procedure change\_address(new\_addr varchar2) is  
  begin  
    address := new\_addr; --member方法体现之处，直接访问修改对象实例的数据address  
  end;  
  member function get\_info return varchar2 is  
    v\_info varchar2(100);  
  begin  
    v\_info := name || ',' || birthdate || ',' || regdate;  
    return v\_info;  
  end;  
  static function gerdate return date is  
  begin  
    return sysdate;  
  end;  
  map member function getage return varchar2 is  
  begin  
  return trunc((sysdate-birthdate)/365);--比较的依据是按照时间  
  end;  
  order member function compare(p person\_typ2) return int is  
  begin  
  case  
  when birthdate>p.birthdate then return 1;  
  when birthdate=p.birthdate then return 0;  
  when birthdate>p.birthdate then return -1;  
    end case;  
  end;  
  constructor function person\_typ2(name varchar2) return self as result is  
  begin  
  self.name:=name;  
  self.gender:='女';  
  self.birthdate:=sysdate;  
  return;  
  end;  
  constructor function person\_typ2(name varchar2,gender varchar2) return self as result is  
  begin  
  self.name:=name;  
  self.gender:=gender;  
  self.birthdate:=sysdate;  
  return;  
  end;  
end;  
  
  
\--建立列对象表  
create table employee\_tab2(eno number(6),person person\_typ2,sal number(6,2),job varchar2(10));  
\--插入  
insert into employee\_tab2(eno,sal,job,person) values(1,1500,'图书管理员',  
person\_typ2('马丽','女','11-1月-75','呼和浩特11号',person\_typ2.getdate()));--由对象类型调用的全局方法getdate(static方法)  
insert into employee\_tab2(eno,sal,job,person) values(2,2000,'高级焊工',  
person\_typ2('王鸣','男','11-5月-75','呼和浩特21号',person\_typ2.getdate()));  
insert into employee\_tab2(eno,sal,job,person) values(3,3000,'高级工程师',  
person\_typ2('李奇','男','11-5月-70','呼和浩特31号',person\_typ2.getdate()));  
insert into employee\_tab2(eno,sal,job,person) values(3,3000,'高级工程师',person\_typ2('怪兽');--自定义构造方法  
insert into employee\_tab2(eno,sal,job,person) values(3,3000,'高级工程师',person\_typ2('怪兽','男');  
\--调用  
declare  
  v\_person person\_typ2;  
  type person\_table\_type is table of person\_typ2;  
  person\_table person\_table\_type;  
begin  
  select person into v\_person from employee\_tab2 where eno = &&no;  
  v\_person.change\_address('呼和浩特12号');  
  update employee\_tab2 set person = v\_person where eno = &no;  
  dbms\_output.put\_line(v\_person.get\_info);  
  --map,取表中前2条数据来对比  
  select person bulk collect into person\_table from employee\_tab2;  
  if person\_table(1).getage()>person\_table(2).getage() then  
    dbms\_output.put\_line(person\_table(1).name||'比'||person\_table(2).name||'大');  
  else  
    dbms\_output.put\_line(person\_table(2).name||'不比'||person\_table(1).name||'大');  
  end if;  
  --compare  
  if person\_table(1).compare(person\_table(2))=1 then  
    dbms\_output.put\_line(person\_table(1).name||'比'||person\_table(2).name||'大');  
  else  
    dbms\_output.put\_line(person\_table(2).name||'不比'||person\_table(1).name||'大');  
  end if;  
end;  
  
  
  
  
三、对象类型的高级应用  
  
  
1、概述  
高级应用简述与其他对象类型具有关联关系的对象类型。包括对象类型的嵌套、参照对象类型、对象类型的继承。  
  
  
2、对象类型的嵌套  
1）、建立对象类型addr\_typ7  
create or replace type addr\_typ7 as object(  
state varchar2(20),city varchar2(20),street varchar2(50),zipcode(6),  
member function get\_addr return varchar2);  
  
  
create or replace type body addr\_typ7 as  
  member function get\_addr return varchar2 is  
  begin  
  return state||city||street;  
  end;  
end;  
  
  
2）、建立对象类型person\_typ7,嵌套addr\_typ7对象类型  
create or replace type person\_typ7 as object(  
name varchar2(10),gender varchar2(2),birthdate date,  
address addr\_typ7,member function get\_info return varchar2);  
  
  
create or replace type body person\_typ7 as  
  member function get\_info return varchar2 is  
  begin  
  return '姓名:'||name||',家庭住址'||address.get\_addr();  
  end;  
end;  
    
3）、建立列对象表employee\_tab7  
create table employee\_tab7(eno number(6),person person\_typ7,sal number(6,2),job varchar2(10));  
  
  
4）、插入  
insert into employee\_tab7(eno,sal,job,person) values  
(1,1500,'图书管理员',person\_typ7('马丽','女','01-11月-76',addr\_typ7('内蒙古自治区','呼和浩特市','呼伦北路22号','010010')));  
insert into employee\_tab7(eno,sal,job,person) values  
(2,2000,'高级钳工',person\_typ7('王鸣','男','11-12月-75',addr\_typ7('内蒙古自治区','呼和浩特市','呼伦北路50号','010010')));  
  
  
5）、更新  
declare  
  v\_person person\_typ7;  
begin  
select person into v\_person from employee\_tab7 where eno=1;  
v\_person.address.street:='北恒东街11号';  
update employee\_tab7 set person=v\_person where eno=1;  
end;  
  
  
6）、查询  
declare  
  v\_person\_typ7;  
begin  
select person into v\_person from employee\_tab7 where eno=1;  
dbms\_output.put\_line(v\_person.get\_info);  
end;  
  
  
6）、删除  
begin  
delete from employ33\_tab7 where eno=1;  
end;  
  
  
3、参照对象类型  
1）、概述  
参照对象类型是指在建立对象表时使用REF定义表列，REF实际是指向行对象表数据的指针。  
通过使用REF定义表列，可以使得一个对象表引用另一个对象表（行对象表）的数据。  
  
  
2）、建立对象类型person\_typ8  
create or replace type person\_typ8 as object(  
name varchar2(10),gender varchar2(2),birthdate date,address varchar2(100),  
member function get\_info return varchar2  
);  
  
  
create or replace type body person\_typ8 as  
  member function get\_info return varchar2 is  
  begin  
    return name||',0'||address;  
  end;  
end;  
  
  
3）、建立行对象表person\_tab8  
create table person\_tab8 of person\_typ8;  
insert into person\_tab8 values('马丽','女','11-1月-75','呼和浩特11号');  
insert into person\_tab8 values('王鸣','男','11-5月-75','呼和浩特21号');  
  
  
4）、建立列对象表employee\_tab8  
说明：employee\_tab8表直接引用person\_tab8表的数据。  
create table employee\_tab8(  
eno number(6),person ref person\_typ8,sal number(6,2),job varchar2(10));  
  
  
5）、插入  
说明：因为employee\_tab8的定义使用ref引用了person\_tab8，所以插入需要引用该表数据。，使用函数REF  
begin  
  insert into employee\_tab8 select 1,ref(a),2000,'图书管理员' from person\_tab8 a where a.name='马丽';  
  insert into employee\_tab8 select 2,ref(a),2000,'高级钳工' from person\_tab8 a where a.name='王鸣';  
end;  
  
  
6）、查询  
说明：取ref对象列数据，必须使用deref。  
declare  
  v\_person person\_typ8;  
begin  
  select deref(person) into v\_person from employee\_tab8 where eno=1;  
  dbms\_output.put\_line(v\_person.get\_into);  
end;  
  
  
7）、更新  
declare  
  v\_person person\_typ8;  
begin  
  select deref(perosn) into v\_person from employee\_tab8 where eno=1;  
  v\_person.address:='呼和浩特市神马路';  
  update person\_tab8 set address=v\_person.address where name=v\_person.name;  
end;  
  
  
8）、删除  
begin  
  delete from employee\_tab8 where eno=1;  
end;  
  
  
4、对象类型的继承  
1）、概述  
9i新增，一个对象类型继承另一个对象类型。定义需要被继承的父类时需要指定not final，否则默认final，表示对象类型不能被继承。  
  
  
2）、建立对象类型person\_typ9  
create or replace type person\_typ9 as object(  
name varchar2(10),gender varchar2(2),birthdate date,address varchar2(100),  
member function get\_info return varchar2  
) not final;  
  
  
create or replace type body person\_typ8 as  
  member function get\_info return varchar2 is  
  begin  
    return name||',0'||address;  
  end;  
end;  
  
  
3）、建立子对象类型  
create or replace type employee\_typ9 under person\_typ9(  
eno number(6),sal number(6,2),job varchar2(10),  
member function get\_other return varchar2);  
  
  
create or replace type body employee\_typ9 as  
  member function get\_other return varchar2 is  
  begin  
    return name||','||sal;  
  end;  
end;  
  
  
4）、建立行对象表  
create table employee\_tab9 of employee\_typ9;  
insert into person\_tab8 values('马丽','女','11-1月-75','呼和浩特11号',1,1500,'图书管理员');  
insert into person\_tab8 values('王鸣','男','11-5月-75','呼和浩特21号',2,2000,'高级钳工');  
  
  
5）、查询  
declare  
  v\_employee employee\_typ9;  
begin  
select value(a) into v\_employee from employee\_tab9 a where a.eno=1;  
dbms\_output.put\_line(v\_employee.get\_info||','||v\_employee.get\_other);  
end;  
  
  
四、维护对象类型  
1、显示对象类型信息  
select type\_name,attributes,final from user\_types;  
  
  
2、增删对象类型的属性  
alter type person\_typ1 add attribute address varchar2(50) cascade;  
alter type person\_typ1 drop attribute birthdate cascade;  
\--cascade级联更新依赖对象类型的对象类型和对象表。  
  
  
3、增删对象类型的方法  
alter type person\_typ1 add member function get info return varchar2 cascade;  
create or replace type body person\_typ1 as  
  member function get\_info return varchar2 is  
  begin  
 return name||','||address;  
  end;  
end;

[https://blog.csdn.net/shcqupc/article/details/50684477][https_blog.csdn.net_shcqupc_article_details_50684477]

[gisdream][]

\----用心去爱，OHYE!

*  [博客园][Link 1]
 *  [首页][gisdream]
 *  [新随笔][Link 2]
 *  [联系][Link 3]
 *  [订阅][Link 4]
 *  [管理][Link 5]

随笔-74  文章-0  评论-37

# [Oracle 自定义TYPE 的几种用法（转）][Oracle _TYPE] #

转自（

[http://space.itpub.net/?uid-12932950-action-viewspace-itemid-662514][http_space.itpub.net_uid-12932950-action-viewspace-itemid-662514]）

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）。也可以存放中间处理的数据，类似于临时表的作用，但是是存放在内存中的。

分类: [DataBase][]

好文要顶 关注我 收藏该文 ![20181227195933590][] ![20181227195933633][]

[![20181227195933742][]][20181227195933742 1]

[gisdream][20181227195933742 1]  
[关注 - 5][- 5]  
[粉丝 - 54][- 54]

\+加关注

[« ][Link 6]上一篇：[oracle嵌套表示例][Link 6]  
[» ][Link 7]下一篇：[Android使用KSOAP2调用WebService及正确导入jar包的问题][Link 7]

posted @ 2012-04-13 15:41 [gisdream][] 阅读(20748) 评论(0) [编辑][Link 8] [收藏][Link 9]

刷新评论[刷新页面][Link 9][返回顶部][Link 10]

注册用户登录后才能发表评论，请 登录 或 注册，[访问][Link 11]网站首页。

[https://www.cnblogs.com/gisdream/archive/2012/04/13/2445893.html][Oracle _TYPE]

[https://www.2cto.com/database/201212/179282.html][https_www.2cto.com_database_201212_179282.html]

[tan1012194073]: https://me.csdn.net/tan1012194073
[https_blog.csdn.net_tan1012194073_article_details_67635107]: https://blog.csdn.net/tan1012194073/article/details/67635107
[shcqupc]: https://me.csdn.net/shcqupc
[ORACLE]: https://blog.csdn.net/shcqupc/article/category/6061956
[https_blog.csdn.net_shcqupc_article_details_50684477]: https://blog.csdn.net/shcqupc/article/details/50684477
[gisdream]: https://www.cnblogs.com/gisdream/
[Link 1]: https://www.cnblogs.com/
[Link 2]: https://i.cnblogs.com/EditPosts.aspx?opt=1
[Link 3]: https://msg.cnblogs.com/send/gisdream
[Link 4]: https://www.cnblogs.com/gisdream/rss
[Link 5]: https://i.cnblogs.com/
[Oracle _TYPE]: https://www.cnblogs.com/gisdream/archive/2012/04/13/2445893.html
[http_space.itpub.net_uid-12932950-action-viewspace-itemid-662514]: http://space.itpub.net/?uid-12932950-action-viewspace-itemid-662514
[10g]: http://www.cnblogs.com/gisdream/admin/javascript:;
[DataBase]: https://www.cnblogs.com/gisdream/category/248145.html
[20181227195933590]: https://img-blog.csdnimg.cn/20181227195933590
[20181227195933633]: /images/20220402/47ff632b90b24a1a87dba0ce510e8cc7.png
[20181227195933742]: /images/20220402/962e45b6803c4e2f821ca2b48dbfd020.png
[20181227195933742 1]: http://home.cnblogs.com/u/gisdream/
[- 5]: http://home.cnblogs.com/u/gisdream/followees
[- 54]: http://home.cnblogs.com/u/gisdream/followers
[Link 6]: https://www.cnblogs.com/gisdream/archive/2012/04/13/2445291.html
[Link 7]: https://www.cnblogs.com/gisdream/archive/2012/04/29/2475842.html
[Link 8]: https://i.cnblogs.com/EditPosts.aspx?postid=2445893
[Link 9]: https://www.cnblogs.com/gisdream/archive/2012/04/13/2445893.html#
[Link 10]: https://www.cnblogs.com/gisdream/archive/2012/04/13/2445893.html#top
[Link 11]: http://www.cnblogs.com/
[https_www.2cto.com_database_201212_179282.html]: https://www.2cto.com/database/201212/179282.html