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 = '';
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 = '';
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 = '';
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 = '';
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 = '';
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 (调用对象方法)

上一篇:为什么Button点击了没反应,反而其他事件反应了


下一篇:解决Idea、WebStorm下使用Vue cli脚手架项目无法使用Webpack别名的问题