oracle 动态sql

    /* oracle 动态sql */
    
    /*
     静态sql;在建立过程是就已经完成了编译的sql（直接嵌入在pl/sql 快中的语句），静态sql 用于完成特定或固定的工作
     动态sql:在运行是建立的sql（在运行pl/sql时动态输入的sql语句）: 用于执行 ddl,dcl 或更加灵活的语句
     
      <一> 处理非查询语句
      三种不同的动态处理方法；
          1.execute immediate: 用于，dcl,ddl,dml(insert,delete,update),以及单行select 动态语句；
          2.open-for ,fetch , close 语句：用于处理动态的多行查询语句 ；
          3.使用批量动态sql,;加快sql 语句处理，提高性能；
          
          eg1;ddl 语句
           create or replace proc_drop_tab(tab_name varchar2) is
              drop_sql varchar2(100);
           begin
              drop_sql := 'drop table  '||tab_name ;
              execute immediate drop_sql;
           end;
     
     
          eg2;dcl 语句
           create or replace proc_drop_tab(priv varchar2,username varchar2) is
              grant_sql varchar2(100);
           begin
              drop_sql := 'grant '|| priv || 'to  '||username ;
              execute immediate grant_sql;
           end;
           
           
           
           eg3; 处理包含占位符的dml 语句（要使用 using 子句）
              declare
                 dml_sql varchar2(100);
               begin
                  dml_sql := 'update emp set sal=sal+:v_sal where deptno=:v_dno';
                  execute immediate dml_sql using &1,&2;
               end;
           
           
           eg4;处理包含returning 子句的dml(要使用returning into)
              declare
                 dml_sql varchar2(100);
               begin
                  dml_sql := 'update emp set sal=sal+:v_sal where deptno=:v_dno returning sal into :v_sal ';
                  execute immediate dml_sql using &1,&2 returning into v_sal;
                  dbms_output_putline(v_sal);
               end;
           
           
           eg5;处理单行查询
             declare
                 select_sql varchar2(100);
                 r_record emp%rowtype;
               begin
                  select_sql := 'select * from emp  where deptno=:v_dno ';
                  execute immediate select_sql  into r_record using &1;
                  dbms_output_putline(r_record.name);
               end;
            
           
           --==========================================================================================================
           
           <二> 处理多行查询
             
          1.   定义游标变量-》打开游标变量-》循环提取数据-》关闭游标变量
               eg;提取多行数据
              declare
               type c_cursor is cursor;
               v_cursor c_cursor;
               r_record emp%rowtype;
               v_sql varchar(100);
             begin
                  v_sql := 'select * from emp where deptno=:v_dno ';
                  open v_cursor for v_sql using &1;
                   loop
                     fetch v_cursor into r_record;
                     exit when v_cursor%notfound;
                      dbms_output.put_line(r_record.name);
                   end loop;
                   
                  close v_cursor;
              end;

           
        2。使用bulk collect into 子句 可以提高性能（实际上动态sql 语句将变量绑定为集合元素）  
           eg1; execute immediate 中 使用bulk collect into ...
           
             declare
              type emp_tab is table of emp.name%type index by binary_integer;
               v_emp_tab emp_tab;
              v_bulksql varchar2(100);
              begin
                v_bulksql := 'select * from emp deptno :v_dno';
                execute immediate v_bulksql bulk collect into v_emp_tab using &1;
                 for v in i..v_emp_tab.count loop
                    dbms_output.put_line(v_emp_tab(i));
                end loop;
              end;
              
         eg2; fetch 语句中使用 bulk collect into ....(类同上)
            fetch c_cursor bulk collect into v_emp_tab ;
            
            
         eg3; forall 语句中使用bulk collect into ..
             forall i in 1..v_emp_tab.count
                         execute immediate v_sql using v_emp_tab(i) returning bulk collect into sal_tab;
           
    */