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)

有关PL/SQL请参考

PL/SQL --> 语言基础

PL/SQL --> 流程控制

PL/SQL --> 存储过程

PL/SQL --> 函数

PL/SQL --> 游标

PL/SQL -->隐式游标(SQL%FOUND)

PL/SQL --> 异常处理(Exception)

PL/SQL --> PL/SQL记录

PL/SQL --> 包的创建与管理

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器