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];
升级会员 