记录类型有些类似于C语言的结构或者,使用记录可以一次性处理多个类型的值。
记录类型简介
记录类型给了程序员自定义程序结构的能力,这种程序结构是指变量类型的集合,这些变量被组织在一起同一进行管理,是的记录类型有些类似于表的一行。可以把记录类型想象成表的一行记录。
在未使用记录类型之前,我们看个例子,定义多个变量来保存emp表中字段的值:
DECLARE
--定义保存字段值的变量
v_empno NUMBER;
v_ename VARCHAR2 (20);
v_job VARCHAR2 (9);
v_mgr NUMBER (4);
v_hiredate DATE;
v_sal NUMBER (7, 2);
v_comm NUMBER (7, 2);
v_deptno NUMBER (2);
BEGIN
--从emp表中取出字段值
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
FROM emp
WHERE empno = :empno;
--向emp_copy表中插入变量的值
INSERT INTO emp_copy
(empno, ename, job, mgr, hiredate, sal, comm,
deptno
)
VALUES (v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm,
v_deptno
);
EXCEPTION --异常处理块
WHEN OTHERS
THEN
NULL;
END;
这样的写法需要定义多个变量,如果同时对两个以上的表进行操作,会使得变量的定义变得混乱。
如果把所有这些变量考虑为一个单元进行处理,可以将其声明为一个记录类型。使用记录类型修改后:
DECLARE
--定义记录类型
TYPE t_emp IS RECORD
(
v_empno NUMBER,
v_ename VARCHAR2 (20),
v_job VARCHAR2 (9),
v_mgr NUMBER (4),
v_hiredate DATE,
v_sal NUMBER (7, 2),
v_comm NUMBER (7, 2),
v_deptno NUMBER (2)
);
--声明记录类型的变量
emp_info t_emp;
BEGIN
--从emp表中取出字段值赋给记录类型
SELECT *
INTO emp_info
FROM emp
WHERE empno = :empno;
--向emp_copy表中插入记录类型的值
INSERT INTO emp_copy VALUES emp_info;
EXCEPTION --异常处理块
WHEN OTHERS
THEN
NULL;
END;
定义记录类型
如:
DECLARE
--声明记录类型
TYPE emp_rec IS RECORD (
dept_row dept%ROWTYPE, --声明来自dept表行的嵌套记录
empno NUMBER, --员工编号
ename VARCHAR (20), --员工名称
job VARCHAR (10), --职位
sal NUMBER (7, 2) --薪资
);
--声明记录类型的变量
emp_info emp_rec;
BEGIN
NULL;
END;
可以在声明的时候对记录类型中的成员进行初始化:
DECLARE
TYPE emp_rec IS RECORD (
empname VARCHAR (12) := '李斯特', --员工名称,初始值李斯特
empno NUMBER NOT NULL DEFAULT 7369, --员工编号,默认值7369
hiredate DATE DEFAULT SYSDATE, --雇佣日期,默认值当前日期
sal NUMBER (7, 2) --员工薪资
);
--声明emp_rec类型的变量
empinfo emp_rec;
BEGIN
NULL;
END;
记录类型赋值
简单赋值
语法如下:
record_name.field_name := expression;
如:
DECLARE
TYPE emp_rec IS RECORD (
empname VARCHAR (12) := '李斯特', --员工名称,初始值李斯特
empno NUMBER NOT NULL DEFAULT 7369, --员工编号,默认值7369
hiredate DATE DEFAULT SYSDATE, --雇佣日期,默认值当前日期
sal NUMBER (7, 2) --员工薪资
);
--声明emp_rec类型的变量
empinfo emp_rec;
BEGIN
--下面的语句为empinfo记录赋值。
empinfo.empname:='施密斯';
empinfo.empno:=7010;
empinfo.hiredate:=TO_DATE('1982-01-01','YYYY-MM-DD');
empinfo.sal:=5000;
--下面的语句输出empinfo记录的值
DBMS_OUTPUT.PUT_LINE('员工名称:'||empinfo.empname);
DBMS_OUTPUT.PUT_LINE('员工编号:'||empinfo.empno);
DBMS_OUTPUT.PUT_LINE('雇佣日期:'||TO_CHAR(empinfo.hiredate,'YYYY-MM-DD'));
DBMS_OUTPUT.PUT_LINE('员工薪资:'||empinfo.sal);
END;
记录类型赋值
除了为单个成员逐个赋值外,还可以为整个记录一次性赋值,最常见的方式是将一个记录类型赋予另一个记录类型的值,如:
DECLARE
--定义记录类型
TYPE emp_rec IS RECORD (
empno NUMBER,
ename VARCHAR2 (20)
);
--定义与emp_rec具有相同成员的记录类型
TYPE emp_rec_dept IS RECORD (
empno NUMBER,
ename VARCHAR2 (20)
);
--声明记录类型的变量
emp_info1 emp_rec;
emp_info2 emp_rec;
emp_info3 emp_rec_dept;
--定义一个内嵌过程用来输出记录信息
PROCEDURE printrec (empinfo emp_rec)
AS
BEGIN
DBMS_OUTPUT.put_line ('员工编号:' || empinfo.empno);
DBMS_OUTPUT.put_line ('员工名称:' || empinfo.ename);
END;
BEGIN
emp_info1.empno := 7890; --为emp_info1记录赋值
emp_info1.ename := '张大千';
DBMS_OUTPUT.put_line ('emp_info1的信息如下:');
printrec (emp_info1); --打印赋值后的emp_info1记录
emp_info2 := emp_info1; --将emp_info1记录变量直接赋给emp_info2
DBMS_OUTPUT.put_line ('emp_info2的信息如下:');
printrec (emp_info2); --打印赋值后的emp_info2的记录
emp_info3:=emp_info1; --此语句出现错误,不同记录类型的变量不能相互赋值
END;
要注意,如果一个记录类型的变量赋给另一个记录类型,两个记录类型必须完全一致,不是光成员一致就行。
但是,使用%ROWTYPE可以根据数据表的行来定义一个记录类型的变量,记录的所有成员是表中的字段列表,可以讲一个以%ROWTYPE定义的记录类型的变量赋给一个与该记录具有完全相同的记录成员的记录变量,如:
DECLARE
--定义一个与dept表具有相同列的记录
TYPE dept_rec IS RECORD (
deptno NUMBER (10),
dname VARCHAR2 (30),
loc VARCHAR2 (30)
);
--定义基于dept表的记录类型
dept_rec_db dept%ROWTYPE;
dept_info dept_rec;
BEGIN
--使用SELECT语句为记录类型赋值
SELECT *
INTO dept_rec_db
FROM dept
WHERE deptno = 20;
--将%ROWTYPE定义的记录赋给标准记录变量
dept_info := dept_rec_db;
END;
可以看到,尽管dept_info
和dept_rec_db
并不是相同的dept_rec
类型,但是因为%ROWTYPE的运行机制及dept_rec
中的记录成员与dept表相同,因此赋值是成功的。
如果要清空一个记录类型的 变量,可以简单地为该变量赋一个空的或未初始化的记录类型,即可清空所有的记录成员值。
使用SELECT或FETCH语句赋值
这里先介绍使用SELECT语句赋值。要使用SELECT语句赋值,SELECT语句的选择列表必须要与记录类型的成员个数及类型相匹配,否则Oracle将抛出异常。
如:
DECLARE
TYPE emp_rec IS RECORD (
empno NUMBER (10),
ename VARCHAR2 (30),
job VARCHAR2 (30)
);
--声明记录类型的变量
emp_info emp_rec;
BEGIN
--为记录类型赋值
SELECT empno,
ename,
job
INTO emp_info
FROM emp
WHERE empno = 7369;
--输出记录类型的值
DBMS_OUTPUT.put_line ( '员工编号:'
|| emp_info.empno
|| CHR (13)
|| '员工姓名:'
|| emp_info.ename
|| CHR (13)
|| '员工职别:'
|| emp_info.job
);
END;
操纵记录类型
在INSERT语句中使用记录类型
如:
DECLARE
TYPE dept_rec IS RECORD (
deptno NUMBER (2),
dname VARCHAR2 (14),
loc VARCHAR2 (13)
);
--定义2个记录类型的变量
dept_row dept%ROWTYPE;
dept_norow dept_rec;
BEGIN
--为记录类型赋值
dept_row.deptno := 70;
dept_row.dname := '工程部';
dept_row.loc := '上海';
dept_norow.deptno := 80;
dept_norow.dname := '电脑部';
dept_norow.loc := '北京';
--插入%ROWTYPE定义的记录变量到表中
INSERT INTO dept
VALUES dept_row;
--插入普通记录变量的值到表中
INSERT INTO dept
VALUES dept_norow;
--向数据库提交对表的更改
COMMIT;
END;
在UPDATE语句中使用记录类型
如:
DECLARE
TYPE dept_rec IS RECORD ( --定义记录类型
deptno NUMBER (2),
dname VARCHAR2 (14),
loc VARCHAR2 (13)
);
dept_info dept_rec; --定义记录类型的变量
BEGIN
SELECT *
INTO dept_info
FROM dept
WHERE deptno = 80; --使用SELECT语句初始化记录类型
dept_info.dname := '信息管理部'; --更新记录类型的值
UPDATE dept
SET ROW = dept_info
WHERE deptno = dept_info.deptno;--在UPDATE中使用记录变量更新表
END;
要注意,SET ROW右边是不能使用子查询的。
在RETURNING子句中使用记录
可以在DML语句中包含一个RETURNING子句,用来返回被UPDATE、DELETE或INSERT操作所影响到的行,通过RETURNING子句,可以将受影响的行保存到一个记录或者是一个记录集合。如:
DECLARE
TYPE dept_rec IS RECORD ( --定义记录类型
deptno NUMBER (2),
dname VARCHAR2 (14),
loc VARCHAR2 (13)
);
dept_info dept_rec; --定义记录类型的变量
dept_returning dept%ROWTYPE; --定义用于返回结果的记录类型
BEGIN
SELECT *
INTO dept_info
FROM dept
WHERE deptno = 80; --使用SELECT语句初始化记录类型
dept_info.dname := '信息管理部'; --更新记录类型的值
UPDATE dept
SET ROW = dept_info
WHERE deptno = dept_info.deptno --在UPDATE中使用记录变量更新表,返回受影响的行到记录
RETURNING deptno,
dname,
loc
INTO dept_returning;
dept_info.deptno := 12;
dept_info.dname := '维修部';
INSERT INTO dept --插入新的部门编号记录,返回受影响的行的记录
VALUES dept_info
RETURNING deptno,
dname,
loc
INTO dept_returning;
DELETE FROM dept --删除现有的部门,返回受影响的行的记录
WHERE deptno = dept_info.deptno
RETURNING deptno,
dname,
loc
INTO dept_returning;
END;
要注意,记录变量是不允许出现在SELECT列表、WHERE子句、GROUP BY子句或ORDER BY子句中的。
使用嵌套记录
如:
DECLARE
TYPE dept_rec IS RECORD ( --定义部门记录类型
deptno NUMBER (2),
dname VARCHAR2 (14),
loc VARCHAR2 (13)
);
TYPE emp_rec IS RECORD ( --定义员工记录类型
v_empno NUMBER,
v_ename VARCHAR2 (20),
v_job VARCHAR2 (9),
v_mgr NUMBER (4),
v_hiredate DATE,
v_sal NUMBER (7, 2),
v_comm NUMBER (7, 2),
v_dept_rec dept_rec --定义嵌套的员工记录
);
emp_info emp_rec; --员工记录
dept_info dept_rec; --临时部门记录
BEGIN
SELECT * --从数据库中取出员工部门的记录
INTO dept_info
FROM dept
WHERE deptno = (SELECT deptno
FROM emp
WHERE empno = 7369);
emp_info.v_dept_rec:=dept_info; --将部门信息记录赋给嵌套的部门记录
SELECT empno, ename, job, mgr, --为emp表赋值
hiredate, sal, comm
INTO emp_info.v_empno, emp_info.v_ename, emp_info.v_job, emp_info.v_mgr,
emp_info.v_hiredate, emp_info.v_sal, emp_info.v_comm
FROM emp
WHERE empno = 7369;
--输出嵌套记录的员工所在部门信息
DBMS_OUTPUT.PUT_LINE('员工所属部门为:'||emp_info.v_dept_rec.dname);
END;