首页 > 代码库 > Oracle自定义数据类型 1

Oracle自定义数据类型 1

原文 oracle 自定义类型 type / create 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、自定义类型:type / create type。

二 type / create type 区别联系

相同:

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

区别:

create type 变量 as table of 类型

--

create type 变量 as object(

字段1 类型1,

字段2 类型2

);

--------------------------

与 type 变量 is table of 类型

--

type 变量 is record(

字段1 类型1,

字段2 类型2

);

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

create 是创 object , 而 type 是创 record .

另 type用在语句块中,而create 是的独立的.

 

 

一般定义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:如何创建和使用记录?

 ①创建记录类型

 

 语法:

 

TYPE  记录名  IS RECORD
(
  filed1 type1 [NOT NULL] [:=eXPr1],
      ....... ,
  filedN typen [NOT NULL] [:=exprn]

其中,filed1是标量的名字。

 ②声明记录类型变量:
  记录类型变量名 记录类型

 ③填充记录。

 ④访问记录成员
  记录类型变量名.filed1
  .........
  记录类型变量名.filedN

 

注意:

表字段类型修改后,还需要修改记录字段类型,有时候可能会忘记,从而出现错误。

对于记录内每个字段(filed1.。。。),可以指定也可以使用%TYPE和%ROWTYPE动态指定记录字段类型。

好处是表字段发生变化,记录字段自动改变。但是,由于每次执行前,遇到%TYPR或%ROWTYPE,

数据库系统都会去查看对应表字段类型,会造成一定的数据库开销,如果系统中大量使用记录类型,则对性能会有一定影响。

另外如果删除了某一字段,而自定义记录中使用了该字段,也会有可能忘记删除该字段。

对数据库负荷偏低的系统,性能问题一般可以不重点关注,但是对于高负荷数据库服务器,

各个环节都要考虑性能问题,每处节省一点出来,性能整体就有很大提高。

 

语法:

 

TYPE  记录名  IS RECORD
(
 filed1 table.Filed%Type [NOT NULL] [:=eXPr1] ,
 filed2 table.Filed%Type [NOT NULL] [:=eXPr1] ,
     ....... ,
  filedn table.Filed%Type [NOT NULL] [:=exprn]
);

 

例子:记录可以整体赋值

 

/*conn scott/tiger
 Create Table  empa  As  Select * From emp;
 */
 Declare
     Type EmpType is Record(
        EMPNO number(4),
        ENAME  varchar2(10),
        JOB varchar2(15),
        SAL number(7,2),
        DEPTNO number(2)
     );
   EmpRec1  EmpType;
   EmpRec2  EmpType;
 Begin
   EmpRec1.Empno:=7369;
   EmpRec1.Ename:=SMITH;
   EmpRec1.Job:=CLERK;
   EmpRec1.Sal:=800;
   EmpRec1.Deptno:=10;
   EmpRec2 := EmpRec1;
   DBMS_output.put_line(EmpRec2.empno);
 End;

 

例子:记录不可以整体比较,只可以比较记录字段

 

Declare
     Type EmpType is Record(
        EMPNO number(4),
        ENAME  varchar2(10),
        JOB varchar2(15),
        SAL number(7,2),
        DEPTNO number(2)
     );
   EmpRec1  EmpType;
   EmpRec2  EmpType;
 Begin
   EmpRec1.Empno:=7369;
   EmpRec1.Ename:=SMITH;
   EmpRec1.Job:=CLERK;
   EmpRec1.Sal:=800;
   EmpRec1.Deptno:=10;
   if EmpRec1.sal < EmpRec2.sal then
     DBMS_output.put_line(Xiao Xiao Xiao);
   end if;
 End;

 

例子:记录不可以整体判断为空,只可以判断记录字段。

 

Declare
     Type EmpType is Record(
        EMPNO number(4),
        ENAME  varchar2(10),
        JOB varchar2(15),
        SAL number(7,2),
        DEPTNO number(2)
     );
   EmpRec  EmpType;
 Begin
   if EmpRec.ename is null then
     DBMS_output.put_line(Kong Kong Kong);
   end if;
 End;

 

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

/*conn scott/tiger
Create Table  empa  As  Select * From emp;
*/
DECLARE
 Type MyRecType Is  Record
 (
  RENO  EMPA.EMPNO%Type,
  RENAME   EMPA.ENAME%Type,
  RJOB   EMPA.JOB%Type
 );
 EmpRec  MyRecType;
Begin
 Select   EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = 7369;
 If  EmpRec.RJOB  = CLERK  Then
  DBMS_OUTPUT.PUT_LINE(Name: ||EmpRec.RENAME);
 End If;
End;

例子:数据集中的记录和记录类型中的数据关系。

 

DECLARE
  Type MyRecType Is  Record
  (
   RENO  EMPA.EMPNO%Type,
   RENAME   EMPA.ENAME%Type,
   RJOB   EMPA.JOB%Type
  );
  EmpRec  MyRecType;
  vJob  EMPA.JOB%Type;
 Begin
  Select   EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = 7369;
  DBMS_OUTPUT.PUT_LINE(MyRecType.RJOB: ||EmpRec.RJOB);
  EmpRec.RJOB  := 修改值后  ;
  DBMS_OUTPUT.PUT_LINE(MyRecType.RJOB: ||EmpRec.RJOB);
  Select JOB  InTo vJob  from empa  Where empa.EMPNO = EmpRec.RENO;
  DBMS_OUTPUT.PUT_LINE(EMPA.JOB: ||vJob);
 End;
 /

 

3.4:使用记录向表中插入数据?
根据表结构合理安排记录字段。比如主外键。
如果用记录(RECORD)插入数据,那么只能使用记录成员;
如果用%ROWTYPE插入数据,可以直接使用%ROWTYPE。

例子:使用记录成员向表中插入数据

 

DECLARE
 Type MyRecType Is  Record
 (
  RENO  EMPA.EMPNO%Type,
  RENAME   VARCHAR2(10),
  RJOB   EMPA.JOB%Type
 );
 EmpRec MyRecType;
Begin
 Select   EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = 7369;
 DBMS_OUTPUT.PUT_LINE(EmpRec.RENO||  ||EmpRec.RENAME||  ||EmpRec.RJOB);
 EmpRec.RENO  := 1001;
 EmpRec.RENAME := 杰克;
 EmpRec.RJOB  := 办事员;
 Insert InTo  empa(EMPNO,ENAME,JOB)  Values(EmpRec.RENO, EmpRec.RENAME,EmpRec.RJOB);
 Select  EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = 1001;
 DBMS_OUTPUT.PUT_LINE(EmpRec.RENO||  ||EmpRec.RENAME||  ||EmpRec.RJOB);
End;

 

3.5:使用记录更新数据?
如果用记录(RECORD)更新数据,那么只能使用记录成员;
如果用%ROWTYPE更新数据,可以直接使用%ROWTYPE。

例子:使用%ROWTYPE向表中插入数据

DECLARE
 vEmp empa%RowType;
Begin
 Select  *  InTo  vEmp  From empa Where empa.EMPNO = 7369;
 UpDate empa Set ROW = vEmp Where EMPNO = 1001;
End;

3.6:使用记录删除数据?
删除记录时,只能在delete语句的where子句中使用记录成员。

 

四 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); 

例子:

declare
   type t_table is table of varchar2(10) indexby BINARY_integer;
   MyTab   t_table;
begin
   MyTab(1) :=  A;
   MyTab(2) :=  B;
   MyTab(3) :=  C;
   DBMS_OUTPUT.PUT_LINE(First index:||   || mytab(1) ||   );
end;
--
DECLARE
     TYPE t_StudentTable IS TABLE OF students%ROWTYPE INDEXBY BINARY_INTEGER;
     v_Students t_StudentTable;
BEGIN
SELECT * INTO v_Students(1100)
FROM students
WHERE id=1100;
     DBMS_OUTPUT.PUT_LINE( v_Students(1100).OUUSRNM);
END;

--record table综合使用

/*conn scott/tiger
 Create table empa as select * from emp;
*/
--例子:
Declare
 Type RecType Is Record
 (
  rno  empa.empno%type,
  rname empa.ename%type,
  rsal  empa.sal%type
 );
 Type TabType Is Table Of  RecType  Index By Binary_Integer;
 MyTab TabType;
 vN Number;
Begin
 --填充
 vN := 1;
 For varR In (Select  *  From empa Order By empno ASC)
 Loop
  MyTab(vN).rno  := varR.empno;
  MyTab(vN).rname := varR.ename;
  MyTab(vN).rsal := varR.sal;
  vN := vN + 1;
 End Loop;
 --访问
 vN := MyTab.First;
 For varR In vN..MyTab.count
 Loop
  DBMS_OUTPUT.PUT_LINE(vN ||‘   ‘||MyTab(vN).rno||‘   ‘||MyTab(vN).rname||‘   ‘||MyTab(vN).rsal);
  vN := MyTab.Next(vN);
 End Loop;
End;

 

注意:

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”时:

declare
type numbers is table of number;
n    numbers := numbers();
begin
n.extend;
n(1) := 2;
n.extend;
n(2) := 3;
for i in1 .. n.count loop
dbms_output.put_line(n(i));
end loop;
end;
--输出:2,3

而如果加了“index by binary_integer”,代码如下写就可以达到上面的效果

declare
  type numbers is table of number index by binary_integer;
  n numbers;
  begin
  n(1) := 2;
  n(2) := 3;
  for i in1 .. n.count loop
  dbms_output.put_line(n(i));
  end loop;
  end;

 

五 create type 用法

5.1 定义

概念  

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

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

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

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

 

CREATE   [OR   REPLACE]   TYPE   <typename>   AS   OBJECT
(attribute1   datatype,
    :
 attributeN   datatype
 MEMBER   PROCEDURE   <methodname>   (parameter,   mode,   datatype),
 MEMBER   FUNCTION   <methodname>   (parameter,   mode,   datatype)
 RETURN   datatype,PRAGMA   RESTRICT_REFERENCES
 (<methodname>,WNDS/RNDS/WNPS/RNPS)
);

 

 说明:PRAGMA   RESTRICT_REFERENCES指定MEMBER方法按以下模式之一   操作:

  –WNDS   (不能写入数据库状态)   不能修改数据库    

 –RNDS   (不能读出数据库状态)   不能执行查询    

 –WNPS   (不能写入数据包状态)   不能更改数据包变量的值    

 –RNPS   (不能读出数据包状态)   不能引用数据包变量的值    

 例:

create   or   replace   type   FLIGHT_SCH_TYPE   as   object
(
FLIGHTNO   VARCHAR2(4)   ,   AIRBUSNO   VARCHAR2(5)   ,
ROUTE_CODE   VARCHAR2(7)   ,   DEPRT_TIME   VARCHAR2(10)   ,
JOURNEY_HURS   VARCHAR2(10)   ,   FLIGHT_DAY1   NUMBER(1)   ,
FLIGHT_DAY2   NUMBER(1)   ,
Member   function   DAYS_FN(FLIGHT_DAY1   in   number)   return   varchar2   ,
Pragma       restrict_references(DAYS_FN   ,   WNDS)
);

 

 创建对象类型方法主体

CREATE   [OR   REPLACE]   TYPE   BODY   <typename>   AS MEMBER
   FUNCTION   <methodname>   (parameter   dataype)   RETURN
  <datatype>   IS<PL/SQL_block>;MEMBER   PROCEDURE   <methodname>
(parameter   datatype);
END;

例:

create   or   replace   type   body   FLIGHT_SCH_TYPE   as
       member   function   DAYS_FN(FLIGHT_DAY1   in   number)   return   varchar2
       is
      disp_day   varchar2(20)   ;
      begin   
      if   flight_day1   =   1   then
           disp_day   :=   Sunday   ;      
     elsif   flight_day1   =   2   then
           disp_day   :=   Monday   ;       
     elsif   flight_day1   =   3   then   
          disp_day   :=   Tuesday   ;    
     elsif   flight_day1   =   4   then   
         disp_day   :=   Wednesday   ;    
     elsif   flight_day1   =   5   then     
         disp_day   :=   Thursday   ;        
    elsif   flight_day1   =   6   then       
         disp_day   :=   Friday      ;  
       elsif   flight_day1   =   7   then
         disp_day   :=   Saturday   ; 
    end   if   ;        
     return   disp_day   ;
  end   ;
end   ;

 

后续内容请参考本博客文章 Oracle自定义数据类型 2 (调用对象方法)

 

Oracle自定义数据类型 1