PL/SQL --> PL/SQL记录

逃离我推掉我的手 2022-05-15 03:24 142阅读 0赞

\--=======================

\-- 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

四、更多参考

有关SQL请参考

[SQL 基础--> 子查询][SQL _--_]

[SQL 基础-->多表查询][SQL _--]

[SQL基础-->分组与分组函数][SQL_--]

[SQL 基础-->常用函数][SQL _-- 1]

[SQL 基础--> ROLLUP与CUBE运算符实现数据汇总][SQL _--_ ROLLUP_CUBE]

[SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)][SQL_--_START BY ... CONNECT BY PRIOR]

有关PL/SQL请参考

[PL/SQL --> 语言基础][PL_SQL --_]

[PL/SQL --> 流程控制][PL_SQL --_ 1]

[PL/SQL --> 存储过程][PL_SQL --_ 2]

[PL/SQL --> 函数][PL_SQL --_ 3]

[PL/SQL --> 游标][PL_SQL --_ 4]

[PL/SQL -->隐式游标(SQL%FOUND)][PL_SQL --_SQL_FOUND]

[PL/SQL --> 异常处理(Exception)][PL_SQL --_ _Exception]

[PL/SQL --> PL/SQL记录][PL_SQL --_ PL_SQL]

[PL/SQL --> 包的创建与管理][PL_SQL --_ 5]

[PL/SQL --> 包重载、初始化][PL_SQL --_ 6]

[PL/SQL --> DBMS\_DDL包的使用][PL_SQL --_ DBMS_DDL]

[PL/SQL --> DML 触发器][PL_SQL --_ DML]

[PL/SQL --> INSTEAD OF 触发器][PL_SQL --_ INSTEAD OF]

[SQL _--_]: http://blog.csdn.net/robinson_0612/archive/2010/05/14/5592002.aspx
[SQL _--]: http://blog.csdn.net/robinson_0612/archive/2010/05/06/5563199.aspx
[SQL_--]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559110.aspx
[SQL _-- 1]: http://blog.csdn.net/robinson_0612/archive/2010/04/16/5495170.aspx
[SQL _--_ ROLLUP_CUBE]: http://blog.csdn.net/robinson_0612/archive/2010/06/01/5640031.aspx
[SQL_--_START BY ... CONNECT BY PRIOR]: http://blog.csdn.net/robinson_0612/archive/2010/05/22/5616877.aspx
[PL_SQL --_]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063399.aspx
[PL_SQL --_ 1]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063422.aspx
[PL_SQL --_ 2]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063434.aspx
[PL_SQL --_ 3]: http://blog.csdn.net/robinson_0612/archive/2010/12/11/6069531.aspx
[PL_SQL --_ 4]: http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx
[PL_SQL --_SQL_FOUND]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092066.aspx
[PL_SQL --_ _Exception]: http://blog.csdn.net/robinson_0612/archive/2010/12/16/6080119.aspx
[PL_SQL --_ PL_SQL]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084390.aspx
[PL_SQL --_ 5]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084475.aspx
[PL_SQL --_ 6]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092097.aspx
[PL_SQL --_ DBMS_DDL]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098248.aspx
[PL_SQL --_ DML]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098258.aspx
[PL_SQL --_ INSTEAD OF]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098263.aspx