oracle复合变量 record.docx

oracle复合变量 record.docx

《oracle复合变量 record.docx》由会员分享，可在线阅读，更多相关《oracle复合变量 record.docx（18页珍藏版）》请在冰豆网上搜索。

oracle复合变量record

PL/SQL有两种复合数据结构：

记录和集合。

记录由不同的域组成，集合由不同的元素组成。

记录是PL/SQL的一种复合数据结构，scalar数据类型和其他数据类型只是简单的在包一级进行预定义，但复合数据类型在使用前必须被定义，记录之所以被称为复合数据类型是因为他由域这种由数据元素的逻辑组所组成。

域可以是scalar数据类型或其他记录类型，它与c语言中的结构相似，记录也可以看成表中的数据行，域则相当于表中的列，在表和虚拟表（视图或查询）中非常容易定义和使用，行或记录中的每一列或域都可以被引用或单独赋值，也可以通过一个单独的语句引用记录所有的域。

在存储过程或函数中记录也可能有参数。

记录就是相关的数据项集中存储在一个单元中，每项都有它自己的名字和数据类型。

假定我们有关于雇员的各种数据信息，如名字、薪水和雇佣日期，这些项在逻辑上是相关联的，但类型不相似。

记录可以把它所拥有的每一项当作一个逻辑单元，这样就便于组织和表现信息。

%ROWTYPE属性能让我们声明代表数据表中一行记录的类型。

但是我们不能利用它指定或声明自己的数据类型。

不过没关系，RECORD关键字可以满足我们定义自己的记录的要求。

创建记录例子所用的表:

createtabledept

（

deptnovarchar（10）primarykey,

dnamevarchar（10）,

locvarchar（10）

）;

createtableemp

（

empnovarchar（10）primarykey,

enamevarchar（10）,

"JOB"varchar（10）,

mgrvarchar（10）,

salvarchar（10）,

deptnovarchar（10）referencesdept（deptno）

）;

1.自定义PL/SQL记录

declare

typeemp_record_typeisrecord（

nameemp.ename%type,

salaryemp.sal%type,

dnoemp.deptno%type

）;

emp_recordemp_record_type;

...

DECLARE

TYPEstockitemISRECORD（

item_noINTEGER（3）,

descriptionVARCHAR2（50）,

quantityINTEGER,

priceREAL（7,2）

）;

item_infostockitem;

...

作为函数或过程的形式参数来使用

DECLARE

TYPEemprecISRECORD（

emp_idemp.empno%TYPE,

last_nameVARCHAR2（10）,

job_titleVARCHAR2（9）,

salaryNUMBER（7,2）

）;

...

PROCEDUREraise_salary（emp_infoemprec）;

函数的返回类型指定为RECORD类型

DECLARE

TYPEemprecISRECORD（

emp_idNUMBER（4）,

last_nameVARCHAR2（10）,

dept_numNUMBER

（2）,

job_titleVARCHAR2（9）,

salaryNUMBER（7,2）

）;

...

FUNCTIONnth_highest_salary（nINTEGER）

RETURNemprecIS...

2.使用%rowype属性定义记录变量

declare

dept_recorddept%rowtype;

emp_recordemp%rowtype;

初始化记录

1.在定义记录的时候，同时进行初始化操作

DECLARE

TYPEtimerecISRECORD（

secsSMALLINT:

=0,

minsSMALLINT:

=0,

hrsSMALLINT:

=0

）;

BEGIN

...

END;

2.为记录添加NOTNULL约束，对于有NOTNULL约束的字段，声明时必须进行初始化

DECLARE

TYPEstockitemISRECORD（

item_noINTEGER（3）NOTNULL:

=999,

descriptionVARCHAR2（50）,

quantityINTEGER,

priceREAL（7,2）

）;

BEGIN

...

END;

记录的引用

1.对函数nth_highest_sal的调用就引用到记录类型emp_info的salary域

DECLARE

TYPEemprecISRECORD（

emp_idNUMBER（4）,

job_titleVARCHAR2（9）,

salaryNUMBER（7,2）

）;

middle_salNUMBER（7,2）;

FUNCTIONnth_highest_sal（nINTEGER）

RETURNemprecIS

emp_infoemprec;

BEGIN

...

RETURNemp_info;--returnrecord

END;

BEGIN

middle_sal:

=nth_highest_sal（10）.salary;--callfunction

...

END;

对于返回类型是一个包含嵌套域的记录的函数

DECLARE

TYPEtimerecISRECORD（

minutesSMALLINT,

hoursSMALLINT

）;

TYPEagendaitemISRECORD（

priorityINTEGER,

subjectVARCHAR2（100）,

DURATIONtimerec

）;

FUNCTIONitem（nINTEGER）

RETURNagendaitemIS

item_infoagendaitem;

BEGIN

...

RETURNitem_info;--returnrecord

END;

BEGIN

NULL;

IFitem（3）.duration.minutes>30THEN...--callfunction

END;

对于包含在记录中的对象的引用方法

DECLARE

TYPEflightrecISRECORD（

flight_noINTEGER,

plane_idVARCHAR2（10）,

captainemployee,--declareobject

passengerspassengerlist,--declarevarray

depart_timetimerec,--declarenestedrecord

airport_codeVARCHAR2（10）

）;

flightflightrec;

BEGIN

...

IFflight.captain.name='HRawlins'THEN...

END;

把记录中的所有字段都设置成空值，只需用一个未初始化的同类型记录为它赋值

DECLARE

TYPEemprecISRECORD（

emp_idemp.empno%TYPE,

job_titleVARCHAR2（9）,

salaryNUMBER（7,2）

）;

emp_infoemprec;

emp_nullemprec;

BEGIN

emp_info.emp_id:

=7788;

emp_info.job_title:

='ANALYST';

emp_info.salary:

=3500;

emp_info:

=emp_null;--nullsallfieldsinemp_info

...

END;

一次性为整个记录进行赋值

第一个方法:

把同类型的一个记录赋值给另外一个记录

DECLARE

TYPEdeptrecISRECORD（

dept_numNUMBER

（2）,

dept_nameVARCHAR2（14）

）;

TYPEdeptitemISRECORD（

dept_numNUMBER

（2）,

dept_nameVARCHAR2（14）

）;

dept1_infodeptrec;

dept2_infodeptitem;

BEGIN

...

dept1_info:

=dept2_info;--illegal;differentdatatypes

END;

使用%ROWTYPE获取的记录

DECLARE

TYPEdeptrecISRECORD（

dept_numNUMBER

（2）,

dept_nameVARCHAR2（14）,

LOCATIONVARCHAR2（13）

）;

dept1_infodeptrec;

dept2_infodept%ROWTYPE;

BEGIN

SELECT*

INTOdept2_info

FROMdept

WHEREdeptno=10;

dept1_info:

=dept2_info;

...

END;

第二个方法:

使用SELECT或FETCH语句把对应的字段值放入记录中去

1.使用selectinto

使用SELECTINTO给记录赋值要将记录或域放在INTO子串中，INTO子串中的变量与SELECT中列的位置相对应

DECLARE

TYPEdeptrecISRECORD（

dept_numNUMBER

（2）,

dept_nameVARCHAR2（14）,

LOCATIONVARCHAR2（13）

）;

dept_infodeptrec;

BEGIN

SELECT*

INTOdept_info

FROMdept

WHEREdeptno=20;

...

END;

2.使用fetch

如果SQL语句返回多行数据或者希望使用带参数的游标，那么就要使用游标，这种情况下使用FETCH语句代替INSTEADINTO是一个更简单、更有效率的方法：

DECLARE

CURSORstock_cur（symbol_inVARCHAR2）IS

SELECTsymbol,exchange,begin_date

FROMstock

WHEREsymbol=UPPER（symbol_in）;

stock_infostock_cur%ROWTYPE

BEGIN

OPENstock_cur（’ORCL’）;

FETCHstock_curINTOstock_info;

把一个嵌套记录赋给另一个，这里要保证的是被嵌套的记录类型是相同的

DECLARE

TYPEtimerecISRECORD（

minsSMALLINT,

hrsSMALLINT

）;

TYPEmeetingrecISRECORD（

"DAY"DATE,

time_oftimerec,--nestedrecord

room_noINTEGER

）;

TYPEpartyrecISRECORD（

"DAY"DATE,

time_oftimerec,--nestedrecord

placeVARCHAR2（25）

）;

seminarmeetingrec;

partypartyrec;

BEGIN

...

party.time_of:

=seminar.time_of;

END;

但不能基于两个不同的TYPE语句来获得相同的结构

DECLARE

TYPEstock_quote_recISRECORD

（deptnodept.deptno%TYPE

bidNUMBER（10,4）

asknumber（10,4）

volumeNUMBER

）;

TYPEstock_quote_tooISRECORD

（deptnodept.deptno%TYPE

bidNUMBER（10,4）

asknumber（10,4）

volumeNUMBER

）;

--这两个记录看上去是一样的，但实际上是不一样的

stock_onestock_quote_rec;

stock_twostock_quote_rec;

--这两个域有相同的数据类型和大小

stock_alsostock_quote_too;--与stock_quote_rec是不同的数据类型

BEGIN

stock_one.deptno:

='orcl';

stock_one.volume:

=1234500;

stock_two:

=stock_one;--正确

stock_also:

=stock_one;--错误，数据类型错误（XUGU这儿有问题）

stock_also.deptno:

=stock_one.deptno;

stock_also.volume:

=stock_one.volume;

end;

使用PL/SQL记录

1.在selectinto语句中使用PL/SQL记录

declare

typeemp_record_typeisrecord（

nameemp.ename%type,

salaryemp.sal%type,

titleemp.job%type）;

emp_recordemp_record_type;

begin

selectename,sal,jobintoemp_record

fromempwhereempno=&no;

dbms_output.put_line（'姓名'||emp_record.name）;

dbms_output.put_line（'岗位'||emp_record.title）;

dbms_output.put_line（'工资'||emp_record.salary）;

end;

2.在INSERT语句中使用PL/SQL记录

PL/SQL对INSERT语句的唯一的扩展就是能让我们使用一个独立RECORD类型或是%ROWTYPE类型变量，来代替域列表来插入一条数据。

这样才可以让我们的代码更具可读性，更容易维护。

记录中域的个数必须和INTO子句后面列出的字段个数相等，对应的域和字段的类型必须兼容。

这样可以保证记录与数据表兼容。

2.1在values子句中使用记录变量

declare

dept_recorddept%rowtype;

begin

dept_record.deptno:

=50;

dept_record.dname:

='ADMINISTRATOR';

dept_record.loc:

='BEIJING';

insertintodeptvaluesdept_record;

end;---这个虚谷存在问题

2.2在values子句中使用记录成员

declare

dept_recorddept%rowtype;

begin

dept_record.deptno:

=60;

dept_record.dname:

='SALES';

insertintodept（deptno,dname）values

（dept_record.deptno,dept_record.dname）;

end;

3.在UPDATE语句中使用PL/SQL记录

PL/SQL对UPDATE语句的唯一的扩展就是能让我们使用一个独立RECORD类型或是%ROWTYPE类型变量，来代替域列表更新一条数据。

记录中域的个数必须和SET子句后面列出的字段个数相等，对应的域和字段的类型也必须兼容。

3.1在SET子句中使用记录变量

declare

dept_recorddept%rowtype;

begin

dept_record.deptno:

=30;

dept_record.dname:

='SALES';

dept_record.loc:

='SHANGHAI';

updatedeptsetrow=dept_recordwheredeptno=30;---虚谷报错'字段ROW不存在'

end;

（注:

关键字ROW只允许出现在SET子句的左边,不能在子查询中使用SETROW）

3.2在SET子句中使用记录成员

declare

dept_recorddept%rowtype;

begin

dept_record.loc:

='GUANGZHOU';

updatedeptsetloc=dept_record.locwheredeptno=10;

end;

4.在DELETE语句中使用PL/SQL记录

declare

dept_recorddept%rowtype;

begin

dept_record.deptno:

=50;

deletefromdeptwheredeptno=dept_record.deptno;

end;

在INSERT，UPDATE和DELETE语句中可以使用returning子句

INSERT，UPDATE和DELETE语句都可以包含RETURNING子句，返回的字段值来自于被影响到的行，它们被放到PL/SQL记录变量中。

这就可以省掉在插入、更新操作之后或删除操作之前执行SELECT查找被影响到的数据。

我们只能在对一行数据进行操作时使用这个子句。

DECLARE

TYPEemprecISRECORD（

emp_nameVARCHAR2（10）,

job_titleVARCHAR2（9）,

salaryNUMBER（7,2）

）;

emp_infoemprec;

emp_idNUMBER（4）;

BEGIN

emp_id:

=7782;

UPDATEemp

SETsal=sal*1.1

WHEREempno=emp_id

RETURNINGename,

job,

sal

INTOemp_info;

END;

记录类型插入/更新操作的约束

1.记录类型变量只在下面几种情况下才允许使用：

1.1在UPDATE语句中SET子句的右边

1.2在INSERT语句中VALUES子句的后面

1.3在RETURNING语句中INTO子句的后面

记录变量是不允许出现在SELECT列表、WHERE子句、GROUPBY子句或ORDERBY子句中的。

2.关键字ROW只允许在SET子句的左面出现，并且不能和子查询连用。

3.UPDATE语句中，如果使用了ROW关键字，那么SET就只能使用一次。

4.如果一个INSERT语句的VALUES子句中包含了记录变量，那么就不允许出现其他变量或值。

5.如果RETURNING语句的INTO子句中包含了记录变量，那么就不允许出现其他变量或值。

6.下面三种情况是不能使用记录的：

6.1含有记录嵌套。

6.2函数返回记录类型。

6.3记录的插入/更新是用EXECUTEIMMEDIATE语句完成的。

包含对象类型的记录是可以使用的：

CREATETYPEworkerASOBJECT（

NAMEVARCHAR2（25）,

deptVARCHAR2（15）

）;

CREATETABLEteams（team_noNUMBER,team_memberworker）;

DECLARE

team_recteams%ROWTYPE;

BEGIN

team_rec.team_no:

=5;

team_rec.team_member:

=worker（'PaulOcker','Accounting'）;

UPDATEteams

SETROW=team_rec;

END;

记录可以包含集合：

CREATETYPEworkerASOBJECT（

NAMEVARCHAR2（25）,

deptVARCHAR2（15）

）;

CREATETYPErosterASTABLEOFworker;

CREATETABLEteams（team_noNUMBER,membersroster）

NESTEDTABLEmembersSTOREASteams_store;

INSERTINTOteams

VALUES（1,

roster（worker（'PaulOcker','Accounting'）,

worker（'GailChan','Sales'）,

worker（'MarieBello','Operations'）,

worker（'AlanConwright','Research'）））;

DECLARE

team_recteams%ROWTYPE;

BEGIN

team_rec.team_no:

=3;

team_rec.members:

=roster（worker（'WilliamBliss','Sales'）,

worker（'AnaLopez','Sales'）,

worker（'BridgetTowner','Operations'）,

worker（'AjaySingh','Accounting'））;

UPDATEteams

SETROW=team_rec;

END;

用查询结果为记录类型的集合赋值

PL/SQL的绑定操作可以分为三类：

定义：

使用SELECT或FETCH语句为PL/SQL变量或主变量赋值。

内绑定：

用INSERT语句插入的或UPDATE语句更新的数据库值。

外绑定：

用INSERT、UPDATE或DELETE语句的RETURNING子句把值返回到PL/SQL变量或主变量中。

PL/SQL支持使用DML语句对记录类型的集合进行批量绑定。

一个"定义"或"外绑定"变量可以是记录类型的集合，"内绑定"值可以保存到记录类型的集合中的。

语法如下：

SELECTselect_itemsBULKCOLLECT

INTOrecord_variable_name

FROMrest_of_select_stmt

FETCH{cursor_name

|cursor_variable_name

|:

host_cursor_variable_name}

BULKCOLLECTINTOrecord_variable_name

[LIMITnumeric_expression];