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;