PL/SQL -- PL/SQL记录

谁践踏了优雅 2022-03-19 14:51 191阅读 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 <][SQL]

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！[https://blog.csdn.net/jiangjunshow][https_blog.csdn.net_jiangjunshow]

[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]: http://blog.csdn.net/robinson_0612/archive/2010/06/01/5640031.aspx
[https_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485