随笔- 290 文章- 0 评论- 433 阅读- 334万

记录

记录由单行多列的标量构成，是一种复合数据结构。

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;