pl/sql记录类型

pl/sql记录类型

   declare
     type emp_record_typ is record(name emp.ename%type,salary emp.sal%type);
     emp_record emp_record_typ;

   或者
   dept_record dept%rowtype;

 
   pl/sql记录类型的示例
         在oracle9i前，在内嵌SQL中，只能使用select into语句才可以直接引用记录变量；
         而在insert,update,delete语句中只能引用记录变量的成员;从oracle9i开始，不仅
         可以在select中直接引用记录变量，而且在insert,delete,update中也可以直接引
         用记录变量；
        
         1,在select into语句中使用pl/sql记录
        
                     示例一：在select into语句中使用记录变量
                       
                          set serveroutput on
                          declare
                            --定义记录变量类型
                           type emp_record_type is record(
                             name emp.name%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);--输出记录变量某个成员
                          end;
                          /
                     
                      示例二：在select into语句中使用记录成员
                     
                          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
                            from emp where empno=&no;
                            dbms_output.put_line(emp_record.name);
                           
                          end;
                          /   
                       
         2,在insert语句中使用pl/sql记录
                    在oracle9i之前，如果使用pl/sql记录插入数据，在VALUES子句中只能使用记录成员；
                    从ORACLE9I开始，不仅可以在VALUES子句中使用记录成员插入数据，而且可以直接
                    使用记录变量插入数据；
                   
                   
                    示例一：在VALUES子句中使用记录变量
                       declare
                         dept_record dept%rowtype;
                       begin
                         dept_record.deptno:=50;--为记录变量各个成员FU值
                         dept_record.dname:='adnub';
                         dept_record.loc:='beijing';
                         insert into dept values dept_record;--直接用记录变量插入数据
                       end;      
                      
                    示例二：在values子句中使用记录变量
                       declare
                         dept_record dept%rowtype;
                       begin
                         dept_record.deptno:=60;
                         dept_record.dname:='sales';  
                         insert into dept(deptno,dname) values(dept_record.deptno,dept_record.dname);
                       end;
                      
                      
                      
                      
                      
                      
         3,在update语句中使用pl/sql记录
              (1)在set子句中使用记录变量
                    示例如下：
                       declare
                         dept_record dept%rowtype;
                       begin
                          --为记录变量各成员FU值
                         dept_record.deptno:=30;
                         dept_record.dname:='sales';
                         dept_record.loc:='shanghai';
                          --注意upDATE语句的ROW
                         update dept set row=dept_record where deptno=30;--直接使用记录变量在set
                        END;
                        /
                       
                       
                (2)在SET子句中使用记录成员
                     declare
                        dept_record dept%rowtype;
                     begin
                        dept_record.loc:='ax';       
                        update dept set loc=dept_record.loc where deptno=10;
                      end;
                     
          4,在delete语句中使用pl/sql记录
              　示例如下：
                   declare
                      dept_record dept%rowtype;
                   begin
                     dept_record.deptno:=50;
                     delete from dept where deptno=dept_record.deptno;
                   end;             
                        
                        
                         

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-708174/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-708174/