动态sql例子

很简单的一个例子

declare
  TYPE cur_typ IS REF CURSOR;
  v_inv_cursor cur_typ;
  v_tabName    varchar2(20);
  v_sql        varchar2(2000);
  v_count      number(8, 2);
  cursor c is
    select table_name from user_tables;
begin
  open c;
  loop
    fetch c
      into v_tabName;
    exit when c%notfound;
    v_sql := 'select count(*)  num from ' || v_tabName;
  
    open v_inv_cursor for v_sql;
    loop
      fetch v_inv_cursor
        into v_count;
      exit when v_inv_cursor%notfound;
      dbms_output.put_line(v_count);
    end loop;
    close v_inv_cursor;
  end loop;
  close c;
end;

------

select * from dept;

select * from emp;
declare
  sql_stmt varchar2(200);
  type id_table is table of integer;
  type name_tables is table of varchar2(8);
  t_empno id_table:=id_table(9001,9002,9003,9004,9005);
  t_empname name_tables:=name_tables('张三','李四','王五','赵六','何七');
  
  v_deptno number(2):=30;
  v_department_name varchar2(20):='南京';
  
  emp_rec emp%rowtype;
begin
  emp_rec.employee_id:=9001;
  emp_rec.first_name:='simens';
  emp_rec.last_name:='bai';
  emp_rec.email:='baixyu';
  emp_rec.phone_number:='123.123.1232';
  
  emp_rec.hire_date:=trunc(sysdate);
  emp_rec.job_id:='AD_VP';
  emp_rec.salary:=90000;
  emp_rec.commission_pct:=null;
  emp_rec.manager_id:=100;
  emp_rec.department_id:=90;
  
  sql_stmt:='update dept set department_name=:1 where department_id=:2';
  execute immediate sql_stmt using v_department_name,v_deptno;
  
  sql_stmt:='create table emp_name_tab(empno number,empname varchar2(20))';
  execute immediate sql_stmt;
  sql_stmt:='insert into emp_name_tab values(:1,:2)';
  for i in t_empno.first .. t_empno.last loop
    execute immediate sql_stmt using t_empno(i),t_empname(i);
    end loop;
    
   -- sql_stmt:='insert into emp values :1';
    --execute immediate sql_stmt using emp_rec;
    end;
    select * from dept;

    select * from emp_name_tab;

动态sql---查询多行--没有使用批量，只能循环，下面有个使用批量的效率高

declare
  type emp_cur_type is ref cursor;
  emp_cur  emp_cur_type;
  v_deptno number(4) := '&deptno';
  v_empno  number(4);
  v_ename  varchar2(25);
begin
  open emp_cur for 'select employee_id,first_name from emp where department_id=:1'
    using v_deptno;
  loop
    fetch emp_cur
      into v_empno, v_ename;
    exit when emp_cur%notfound;
    dbms_output.put_line(v_empno);
    dbms_output.put_line(v_ename);
  end loop;
  close emp_cur;
  exception 
  when others then
    if emp_cur%found then
      close emp_cur;
      end if;
      dbms_output.put_line('error:'||substr(sqlerrm,1,200));
end;

--dml中使用bulk
declare
type ename_table_type is table of varchar2(20) index by binary_integer;
type sal_table_type is table of number(10,2) index by binary_integer;
ename_tab ename_table_type;
sal_tab sal_table_type;
v_deptno number(4):=20;
v_percent number(4,2):=0.12;
sql_stmt varchar2(500);
begin
  sql_stmt:='update hr.employees set salary=salary*(1+:percent) where department_id=:deptno returning first_name,salary into :ename,:salary';
  execute immediate sql_stmt using v_percent,v_deptno returning bulk collect into ename_tab,sal_tab;
  for i in 1 .. ename_tab.count loop
    dbms_output.put_line(ename_tab(i)||':'||sal_tab(i));
    end loop;
    end;
    
    --select中使用bulk
    declare
    type ename_table_type is table of varchar2(20) index by binary_integer;
    ename_tab ename_table_type;
    v_deptno number(4):='&deptno';
    sql_stmt varchar2(500);
    begin
      sql_stmt:='select first_name from hr.employees where department_id=:1';
      execute immediate sql_stmt bulk collect into ename_tab using v_deptno;
      for i in 1 .. ename_tab.count loop
        dbms_output.put_line(ename_tab(i));
        end loop;
        end;
        
        
        --游标fetch中使用bulk
    declare
      type ename_table_type is table of varchar2(20) index by binary_integer;
      type emp_cur_type is ref cursor;
      emp_cur emp_cur_type;
      ename_tab ename_table_type;
      v_deptno  number(4) := '&deptno';
      sql_stmt  varchar2(500);
    begin
     open emp_cur for 'select first_name from hr.employees where department_id=:1' using v_deptno;
     fetch emp_cur bulk collect into ename_tab;
     close emp_cur;
      for i in 1 .. ename_tab.count loop
        dbms_output.put_line(ename_tab(i));
      end loop;
    end;
        --dml使用forall
        create or replace procedure tt
        authid current_user --定义调用者权限
        as
          type ename_table_type is table of varchar2(20) index by binary_integer;
          type sal_table_type is table of number(10, 2) index by binary_integer;
          type empno_table_type is table of number(4);
          ename_tab ename_table_type;
          sal_tab   sal_table_type;
          empno_tab empno_table_type;
          v_percent number(4, 2) := 0.12;
          sql_stmt  varchar2(500);
        begin
          empno_tab := empno_table_type(2134, 2214, 143);
          sql_stmt  := 'update hr.employees set salary=salary*(1+:percent) where employee_id=:empno returning first_name,salary into :ename,:salary';
          forall i in 1 .. empno_tab.count
           execute immediate sql_stmt using
                           v_percent, empno_tab(i) returning bulk collect into ename_tab, sal_tab;
        exception
          when others
            then
              dbms_output.put_line(dbms_utility.format_call_stack);
              dbms_output.put_line(sql_stmt);
              raise;
          for i in 1 .. ename_tab.count loop
            dbms_output.put_line(ename_tab(i) || ':' || sal_tab(i));
          end loop;
        end;

动态sql的绑定变量及返回值

declare

  v_empno   number(3) := 363;

  v_percent number(4, 2) := 0.12;

  v_salary  number(10, 2);

  sql_stmt  varchar2(500);

begin

  sql_stmt := 'update emp set salary=salary*(1+:percent)where employee_id=:empno returning salary into :salary';

  executeimmediate sql_stmt using v_percent, v_empno returninginto v_salary;

  dbms_output.put_line(v_salary);

end;

 

多行动态sql语句

declare

  type emp_cur_type isrefcursor;

  emp_cur  emp_cur_type;

  v_deptno number(4) := '&deptno';

  v_empno  number(4);

  v_ename  varchar2(25);

begin

  open emp_cur for'select empno,ename from emp wheredeptno=:1'

    using v_deptno;

  loop

    fetch emp_cur

      into v_empno, v_ename;

    exitwhen emp_cur%notfound;

    dbms_output.put_line(v_empno);

    dbms_output.put_line(v_ename);

  endloop;

  close emp_cur;

exception

  whenothersthen

    if emp_cur%foundthen

      close emp_cur;

    endif;

    dbms_output.put_line(substr(sqlerrm, 1, 200));

end;

 

使用批量多行dml语句

 

declare

  type ename_table_type istableofvarchar2(25) indexbybinary_integer;

  type sal_table_type istableofnumber(10, 2) indexbybinary_integer;

  ename_tab ename_table_type;

  sal_tab   sal_table_type;

  v_deptno  number(4) := 20;

  v_percent number(4, 2) := 0.12;

  sql_stmt  varchar2(500);

begin

  sql_stmt := 'update emp setsal=sal*(1+:percent) where deptno=:deptno returning ename,sal into:ename,:salary';

  executeimmediate sql_stmt

    using v_percent, v_deptno

    returningbulkcollect

    into ename_tab, sal_tab;

  for i in1 .. ename_tab.count loop

   dbms_output.put_line(ename_tab(i) || ',' || sal_tab(i));

  endloop;

end;

 

使用批量fetch语句来获取多行查询结果

 

declare

  type ename_table_type istableofvarchar2(20) indexbybinary_integer;

  type empno_table_type istableofnumber(24) indexbybinary_integer;

  type emp_cur_type isrefcursor;

  ename_tab ename_table_type;

  empno_tab empno_table_type;

  emp_cur   emp_cur_type;

  v_deptno  number(4) := '&deptno';

begin

  open emp_cur for'select empno,ename from emp wheredeptno=:1'

    using v_deptno;

  fetch emp_cur bulkcollect

    into empno_tab, ename_tab;

  close emp_cur;

  for i in1 .. ename_tab.count loop

   dbms_output.put_line(empno_tab(i) || ename_tab(i));

  endloop;

end;