PL/SQL --> PL/SQL记录
--=======================
-- 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 基础--> 子查询
SQL 基础-->多表查询
SQL基础-->分组与分组函数
SQL 基础-->常用函数
SQL 基础--> ROLLUP与CUBE运算符实现数据汇总
SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)