--=======================
-- PL/SQL --> PL/SQL 记录
--=======================
PL / SQL 记录有着类似于表的数据结构，是一个或多个字段且拥有数据类型的集合体。定义了 PL / SQL 记录类型之后，可以定义 PL / SQL 记录变
量。声明一个 PL / SQL 记录变量相当于定义了多个标量变量，简化了变量的声明，从而大大节省了内存资源。多用于简化单行多列的数据处理。
一、定义 PL / SQL 记录
1. 直接定义 PL / SQL 记录 ( 首先定义记录类型，类型中包含的记录成员，其次是记录类型变量 )
TYPE type_name IS RECORD --type_name 用于指定自定义记录类型的名称
( field_name1 datatype1 [NOT NULL] [ := DEFAULT EXPRESSION] , -- 定义记录的成员、数据类型及缺省值
field_name2 datatype2 [NOT NULL] [ := DEFAULT EXPRESSION] ,
...
field_nameN datatypeN [NOT NULL] [ := DEFAULT EXPRESSION] );
record_name TYPE_NAME ; -- 定义记录变量名 record_name
2. 使用 % rowtype 定义 PL / SQL 记录
使用 % rowtype 时，记录成员名称和类型与所依赖对象 ( 表，视图，游标 ) 名称和类型完全相同
对于表和视图，游标所有列定义时，使用 % rowtype 定义将大大节省内存空间
record_name table_name % rowtype -- 基于不同的对象定义 PL/SQL 记录，此处为表
record_name view_name % rowtype
reocrd_name cursor_name % rowtype
二、 PL / SQL 记录的使用
1. SELECT INTO 语句中使用
-- 使用 PL/SQL 记录变量
undefine no
DECLARE
TYPE emp_record_type IS RECORD
(
name emp . ename % TYPE ,
salary emp . sal % TYPE ,
dno emp . deptno % TYPE
);
emp_record emp_record_type ;
BEGIN -- 下面的查询到的数据插入到记录变量，注意列之间的顺序与声明顺序保持一致
SELECT ename , sal , deptno INTO emp_record
FROM emp WHERE empno = & no ;
dbms_output . put_line ( emp_record . name ); -- 输出时仅仅输出记录变量的一个成员 emp_record.name
END ;
Enter value for no : 7788
SCOTT
-- 使用 PL/SQL 记录成员
undefine no
DECLARE
TYPE emp_record_type IS RECORD
(
name emp . ename % TYPE ,
salary emp . sal % TYPE ,
dno emp . deptno % TYPE
);
emp_record emp_record_type ;
BEGIN
SELECT ename , sal INTO emp_record . name , emp_record . salary -- 此处仅仅使用到了 name 和 salary 成员
FROM emp WHERE empno = & no ;
dbms_output . put_line ( emp_record . name ); -- 输出时仅仅输出记录变量的一个成员 emp_record.name
END ;
/
2. INSERT 语句中使用记录变量及成员
-- 使用 PL/SQL 记录变量
DECLARE
dept_record dept % ROWTYPE ;
BEGIN
dept_record . deptno := 50 ;
dept_record . dname := 'ADMINISTRATOR' ;
dept_record . loc := 'BEIJING' ;
INSERT INTO dept VALUES dept_record ;
END ;
/
scott@ORCL > select * from dept where deptno = 50 ;
DEPTNO DNAME LOC
---------- -------------- -------------
50 ADMINISTRATOR BEIJING
-- 使用 PL/SQL 记录成员
scott@ORCL > DECLARE
2 dept_record dept % ROWTYPE ;
3 BEGIN
4 dept_record . deptno := 60 ;
5 dept_record . dname := 'SALES' ;
6 INSERT INTO dept ( deptno , dname ) VALUES ( dept_record . deptno , dept_record . dname );
7 END ;
8 /
PL / SQL procedure successfully completed .
scott@ORCL > select * from dept where deptno in ( 50 , 60 );
DEPTNO DNAME LOC
---------- -------------- -------------
50 ADMINISTRATOR BEIJING
60 SALES
3. UPDATE 语句中使用记录变量及成员
-- 使用 PL/SQL 记录变量 ( 使用 ROW 来更新整行 )
DECLARE
dept_record dept % ROWTYPE ;
BEGIN
dept_record . deptno := 50 ;
dept_record . dname := 'SERVICE' ;
dept_record . loc := 'GuangZhou' ;
UPDATE dept SET ROW = dept_record WHERE deptno = 50 ; -- 注意 update 时，使用 ROW 来表示整行
END ;
/
-- 使用 PL/SQL 记录成员 ( 使用成员记录更新单列或多列 )
scott@ORCL > DECLARE
2 dept_record dept % ROWTYPE ;
3 BEGIN
4 dept_record . loc := 'ShangHai' ;
5 UPDATE dept SET loc = dept_record . loc WHERE deptno = 60 ;
6 END ;
7 /
4. DELETE 语句中使用记录变量及成员
DECLARE
dept_record dept % ROWTYPE ;
BEGIN
dept_record . deptno := 60 ;
DELETE FROM dept WHERE deptno = dept_record . deptno ;
END ;
/
三、 PL / SQL 记录使用时的几个问题
1. 记录成员非空值的问题 ( 非空值应当在初始化时赋值，而不是在使用时赋值 )
scott@ORCL > DECLARE
2 TYPE ex_type IS RECORD
3 ( col1 NUMBER ( 3 ),
4 col2 VARCHAR2 ( 5 ) NOT NULL);
5 ex_record ex_type ;
6 BEGIN
7 ex_record . col1 := 15 ;
ex_record . col1 := TO_CHAR ( ex_record . col1 );
8 ex_record . col2 := 'John' ;
9 DBMS_OUTPUT . PUT_LINE ( 'ex_record.col1 is ' || ex_record . col1 );
10 DBMS_OUTPUT . PUT_LINE ( 'ex_record.col2 is ' || ex_record . col2 );
11 END ;
12 /
col2 VARCHAR2 ( 5 ) NOT NULL);
*
ERROR at line 4 :
ORA - 06550 : line 4 , column 6 :
PLS - 00218 : a variable declared NOT NULL must have an initialization assignment -- 错误消息指出非空值应当在初始化时指定
DECLARE
TYPE ex_type IS RECORD (
col1 NUMBER ( 3 ),
col2 VARCHAR2 ( 5 ) NOT NULL := 'John' ); -- 注意对于非空值应当在初始化时赋值，而不是在使用时赋值
ex_record ex_type ;
BEGIN
ex_record . col1 := 15 ;
ex_record . col1 := TO_CHAR ( ex_record . col1 );
--ex_record.col2:='John';
DBMS_OUTPUT . PUT_LINE ( 'ex_record.col1 is ' || ex_record . col1 );
DBMS_OUTPUT . PUT_LINE ( 'ex_record.col2 is ' || ex_record . col2 );
END ;
ex_record . col1 is 15
ex_record . col2 is John
2. 记录变量之间的赋值问题
下面的例子中两个 PL / SQL 变量 name_rec1 与 name_rec2 尽管具有表面上相同的定义，但两者之间不能相互赋值
DECLARE
TYPE ex_type1 IS RECORD (
first_name VARCHAR2 ( 15 ),
last_name VARCHAR2 ( 30 ));
TYPE ex_type2 IS RECORD (
first_name VARCHAR2 ( 15 ),
last_name VARCHAR2 ( 30 ));
ex_rec1 ex_type1 ;
ex_rec2 ex_type2 ;
BEGIN
ex_rec1 . first_name := 'Robinson' ;
ex_rec1 . last_name := 'Cheng' ;
ex_rec2 := ex_rec1 ; -- 不合理的赋值方式
END ;
ex_rec2 := ex_rec1 ;
*
ERROR at line 13 :
ORA - 06550 : line 13 , column 25 :
PLS - 00382 : expression is of wrong type
ORA - 06550 : line 13 , column 3 :
PL / SQL : Statement ignored
具有相同的记录类型时，不同记录变量之间可以相互赋值
DECLARE
TYPE ex_type1 IS RECORD (
first_name VARCHAR2 ( 15 ),
last_name VARCHAR2 ( 30 ));
ex_rec1 ex_type1 ;
ex_rec2 ex_type1 ;
BEGIN
ex_rec1 . first_name := 'Robinson' ;
ex_rec1 . last_name := 'Cheng' ;
ex_rec2 := ex_rec1 ;
DBMS_OUTPUT . PUT_LINE ( 'ex_rec1 is ' || ex_rec1 . first_name|| ' ' || ex_rec1 . last_name );
DBMS_OUTPUT . PUT_LINE ( 'ex_rec2 is ' || ex_rec2 . first_name|| ' ' || ex_rec2 . last_name );
END ;
ex_rec1 is Robinson Cheng
ex_rec2 is Robinson Cheng
3. 基于表，基于游标，基于自定义 PL / SQL 记录的综合使用
DECLARE
CURSOR dept_cur IS -- 声明游标
SELECT * FROM dept WHERE deptno = 30 ;
TYPE dept_type IS RECORD ( -- 声明一个自定义的 PL/SQL 记录类型
deptno NUMBER ( 2 ),
dname VARCHAR2 ( 14 ),
loc VARCHAR2 ( 13 ));
dept_rec1 dept % ROWTYPE ; -- 声明基于表 dept 的记录变量
dept_rec2 dept_cur % ROWTYPE ; -- 声明基于游标 dept_cur 的记录变量
dept_rec3 dept_type ; -- 声明基于自定义 dept_type 的记录变量
BEGIN
SELECT * INTO dept_rec1 FROM dept WHERE deptno = 30 ; -- 将查询的结果插入到基于表 dept 的记录变量中
OPEN dept_cur ;
LOOP
FETCH dept_cur
INTO dept_rec2 ; -- 将游标的内容插入到游标记录变量中
EXIT WHEN dept_cur % NOTFOUND ;
END LOOP ;
dept_rec1 := dept_rec2 ;
dept_rec3 := dept_rec2 ;
DBMS_OUTPUT . PUT_LINE ( dept_rec1 . deptno || ' ' || dept_rec1 . dname );
DBMS_OUTPUT . PUT_LINE ( dept_rec2 . deptno || ' ' || dept_rec2 . dname );
DBMS_OUTPUT . PUT_LINE ( dept_rec3 . deptno || ' ' || dept_rec3 . dname );
END ;
30 SALES
30 SALES
30 SALES
四、更多参考
有关 SQL 请参考
SQL 基础--> ROLLUP 与CUBE 运算符实现数据汇总
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
有关 PL/SQL 请参考
