记录
记录由单行多列的标量构成,是一种复合数据结构。
PL/SQL支持三种记录类型:基于表的记录、基于游标的记录以及用户自定义的记录。
一、 基于表的记录
DECLARE emp_rec emp%rowtype; BEGIN SELECT * INTO emp_rec FROM emp WHERE empno=7788;
DBMS_OUTPUT.PUT_LINE('Ename is: '||emp_rec.ename); DBMS_OUTPUT.PUT_LINE('Job is: '||emp_rec.job); DBMS_OUTPUT.PUT_LINE('Salary is: '||emp_rec.sal); END;
二、 基于游标的记录
DECLARE CURSOR emp_cur IS SELECT * FROM emp WHERE rownum <=4; emp_rec emp_cur%ROWTYPE; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Ename is: '||emp_rec.ename); DBMS_OUTPUT.PUT_LINE('Job is: '||emp_rec.job); DBMS_OUTPUT.PUT_LINE('Salary is: '||emp_rec.sal); END LOOP; END;
三、 用户自定义的记录
创建用户自定义记录的语法如下所示:
TYPE type_name IS RECORD (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;
试举一例:
DECLARE TYPE time_rec_type IS RECORD (curr_date DATE, curr_day VARCHAR2(12), curr_time VARCHAR(8) :='00:00:00' ); time_rec TIME_REC_TYPE; BEGIN SELECT sysdate INTO time_rec.curr_date FROM dual; time_rec.curr_day := TO_CHAR(time_rec.curr_date,'DAY'); time_rec.curr_time := TO_CHAR(time_rec.curr_date,'HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE('Date: '||time_rec.curr_date); DBMS_OUTPUT.PUT_LINE('Day: '||time_rec.curr_day); DBMS_OUTPUT.PUT_LINE('Time: '||time_rec.curr_time); END;
注意:
1> 当声明记录类型时,如果为某个字段指定了NOT NULL约束,则必须初始化该字段。譬如上例中,如果字段curr_date指定了NOT NULL约束,即curr_date DATE NOT NULL,则会报如下错误:PLS-00218: a variable declared NOT NULL must have an initialization assignment。可初始化为:curr_date DATE NOT NULL :=sysdate。
2> 记录不可以整体判断为空,只可以判断记录字段。
3> 记录不可以整体比较,只可以比较记录字段。
4> 可以把基于表或者游标的记录整体赋值给用户自定义的记录,只要两者具有相同的结构。但对于用户自定义的记录来说,即便两个自定义记录具有相同的结构,但类型却不同,不可整体赋值。如下所示:
DECLARE TYPE name_type1 IS RECORD (first_name VARCHAR2(15), last_name VARCHAR2(30) ); TYPE name_type2 IS RECORD (first_name VARCHAR2(15), last_name VARCHAR2(15) ); name_rec1 name_type1; name_rec2 name_type2; BEGIN name_rec1.first_name := 'John'; name_rec1.last_name := 'Smith'; name_rec2 := name_rec1; END;
运行报如下错误:
SQL> /
name_rec2 := name_rec1;
*
ERROR at line 15:
ORA-06550: line 15, column 17:
PLS-00382: expression is of wrong type
ORA-06550: line 15, column 4:
PL/SQL: Statement ignored
可见,两个记录虽然拥有相同的结构,但,每个记录的类型不同,该记录也是不兼容的,整体赋值就会报错。
上例可修改为:
DECLARE TYPE name_type1 IS RECORD (first_name VARCHAR2(15), last_name VARCHAR2(30) ); name_rec1 name_type1; name_rec2 name_type1; BEGIN name_rec1.first_name := 'John'; name_rec1.last_name := 'Smith'; name_rec2 := name_rec1; END;
上述整体赋值的限制只局限于用户自定义记录类型。可以把基于表或者游标的记录整体赋值给用户自定义的记录,只要两者具有相同的结构。
DECLARE CURSOR dept_cur IS SELECT * FROM dept; TYPE dept_type IS RECORD (deptno dept.deptno%TYPE, dname dept.dname%TYPE, loc dept.loc%TYPE ); dept_rec1 dept%ROWTYPE; -- table-based record dept_rec2 dept_cur%ROWTYPE; -- cursor-based record dept_rec3 dept_type; BEGIN -- Populate table-based record SELECT * INTO dept_rec1 FROM dept WHERE deptno=10; dept_rec2 := dept_rec1; dept_rec3 := dept_rec1; --Populate cursor-based record 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; --Populate user defined record SELECT * INTO dept_rec3 FROM dept WHERE deptno=20; dept_rec1 := dept_rec3; dept_rec2 := dept_rec3; END;
四、 嵌套记录
嵌套记录指的记录中包含其他记录和集合。
譬如:
DECLARE TYPE name_type IS RECORD (first_name VARCHAR2(15); last_name VARCHAR2(30)); TYPE person_type IS RECORD (name name_type; street VARCHAR2(50); city VARCHAR2(25); state VARCHAR2(2); zip VARCHAR2(5)); person_rec person_type;
这段代码包含两个用户自定义记录类型,其中,第二个用户自定义记录类型person_type是个嵌套的记录类型,因为其字段name是name_type类型的记录。
综合示例:
一、 输入部门号,输出该部门的员工
DECLARE TYPE ename_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; TYPE dept_info_type IS RECORD (deptno emp.deptno%TYPE, ename_tab ename_type); CURSOR name_cur (p_deptno number) IS SELECT ename FROM emp WHERE deptno = p_deptno; dept_info_rec dept_info_type; v_deptno NUMBER(2) := &v_deptno; v_counter INTEGER := 0; BEGIN dept_info_rec.deptno := v_deptno; DBMS_OUTPUT.PUT_LINE('Deptno: '||dept_info_rec.deptno||chr(10)||'Employees:'); FOR name_rec IN name_cur(v_deptno) LOOP v_counter := v_counter+1; dept_info_rec.ename_tab(v_counter) := name_rec.ename; END LOOP; FOR i IN 1..dept_info_rec.ename_tab.COUNT LOOP DBMS_OUTPUT.PUT_LINE(dept_info_rec.ename_tab(i)); END LOOP; END;
输出结果为:
Enter value for v_deptno: 10
old 14: v_deptno NUMBER(2) := &v_deptno;
new 14: v_deptno NUMBER(2) := 10;
Deptno: 10
Employees:
CLARK
KING
MILLER
PL/SQL procedure successfully completed.
二、 输出每个部门的员工总数
DECLARE CURSOR dept_cur IS SELECT dname,loc,count(*) total FROM dept,emp WHERE dept.deptno=emp.deptno GROUP BY dname,loc; TYPE dname_rec_type IS RECORD (dname dept.dname%TYPE, loc dept.loc%TYPE, total INTEGER); TYPE dept_type IS TABLE OF dname_rec_type INDEX BY BINARY_INTEGER; dept_tab dept_type; v_counter INTEGER :=0; BEGIN FOR dept_rec IN dept_cur LOOP v_counter := v_counter+1; dept_tab(v_counter).dname := dept_rec.dname; dept_tab(v_counter).loc := dept_rec.loc; dept_tab(v_counter).total := dept_rec.total; DBMS_OUTPUT.PUT_LINE(dept_tab(v_counter).dname||' '||dept_tab(v_counter).loc ||' '||dept_tab(v_counter).total); END LOOP; END;
输出结果为:
RESEARCH DALLAS 5
SALES CHICAGO 7
ACCOUNTING NEW YORK 3
PL/SQL procedure successfully completed.