record类型操作

8.1.1记录类型(record)

declare
  type t_emp is record(  --定义记录类型
    v_empno  number,  --记录成员类型可以是除ref cursor以外任何数据类型
    v_ename  varchar(20),
    v_hirdedate  date,
    v_deptno  number(2)
    );
  emp_info  t_emp; --声明记录类型的变量
begin
  select * into emp_info from emp where empno= :empno;
  insert into emp_copy values emp_info;
exception  --异常处理
  when others
  then 
    null;
end;

8.1.2 定义record类型

declare
  type emp_rec is record(
    dept_row  dept%rowtype,--声明来自dept表行的嵌套记录
    empno  number,
    ename  varchar(20),
    vjob   varchar(10),
    sal    number(7,2)
    );
  emp_info  emp_rec;--申明记录类型的变量
begin
  null;
end;

declare 
  type emp_rec is record(
  empname  varchar2(12)  :='tom',
  empno   number  not null default 7800,
  hiredate  date   default sysdate,
  sal  number(7,2)
  );
  empinfo  emp_rec;
begin
  null;
end;

8.1.3 recode类型赋值

declare
  type emp_rec is record (
    empname  varchar2(20),
    empno  number not null default 7800,
    hiredate date default sysdate,
    sal number (7,2)
  );
  empinfo  emp_rec;
begin
  empinfo.empname :='tom'; --RECORD简单赋值
  empinfo.empno := 7022;
  empinfo.hiredate :=to_date('1982-01-01','YYYY-MM-DD');
  empinfo.sal := 5000;
  dbms_output.put_line('员工名称：'||empinfo.empname);
  dbms_output.put_line('员工编号：'||empinfo.empno);
  dbms_output.put_line('入职日期：'||to_char(empinfo.hiredate,'YYY-MM-DD'));
  dbms_output.put_line('员工薪资：'||empinfo.sal);
end;

declare
  type emp_rec is record(
    empno  number,
    ename  varchar2(20)
  );
  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 := 5555;
  emp_info1.ename :='tom';
  dbms_output.put_line('emp_info1的信息如下:');
  printrec(emp_info1);
  emp_info2 := emp_info1; -- 相同记录类型的变量赋值
  dbms_output.put_line('emp_info2的信息如下:');
  printrec(emp_info2);
  emp_info1 :=null; --清空记录所有成员的值
  dbms_output.put_line('emp_info1的信息如下:');
  printrec(emp_info1);
  --emp_info3　:= emp_info1; --此语句出现错误,不同记录类型的变量不能相互赋值。
end;

declare 
  type dept_rec is record(
    deptno number (10),
    dname  varchar2(30),
    loc  varchar2(30)
  );
  dept_rec_db  dept%ROWTYPE;
  dept_info  detp_rec;
begin
  select * into dept_rec_db from dept where deptno =20;
  dept_info :=dept_rec_db;--将%ROWTYPE定义的记录赋值给标准记录变量
  --select * into dept_info from dept where deptno=20；--等价于上面2行
end;

8.1.4 操纵record类型

declare
  type dept_rec is record(
    deptno  number(2),
    dname   varchar2(14),
    loc     varchar2(12)
  );
  dept_row   dept%ROWTYPE;
  dept_norow dept_rec;
begin
  dept_row.deptno := 70;
  dept_row.dname := 'tom';
  dept_row.loc := '上海';
  dept_norow.deptno :=80;
  dept_norow.dname :='harry';
  dept_norow.loc :='北京';
  insert into dept values dept_row;
  insert into dept values dept_norow;
  commit;
end;

declare
  type dept_rec is record(
    deptno  number(2),
    dname   varchar2(14),
    loc     varchar2(12)
  );
  dept_info dept_rec;
begin
  select * into dept_info from dept
    where deptno= 80;
  detp_info.name :='tom';
  update dept set row = dept_info -- set row后面不能再使用子查询
    where deptno = dept_info.deptno;
end;

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;
  dept_info.dname := 'tom';
  update dept set row = dept_info 
    where deptno = dept_info.deptno
    returning deptno,dname,loc into dept_returning;
  dept_info.deptno := 12;
  dept_info.dname :='harry';
  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;

8.1.5 record嵌套使用

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 --record 嵌套
  );
  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,ejob,mgr,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;