PL/SQL复习十四 动态SQL

使用execute immediate处理DDL操作：

create or replace procedure drop_table(table_name in varchar2)

is

  sql_statement varchar2(100);

begin

  sql_statement := 'drop table '|| table_name;

  execute immediate sql_statement;

end;

/

调用：SQL> exec drop_table('demo'); 删除表

------------------------------------------------------------------------------------------------

DML操作：

使用无符号占位符和returing语句：

begin

  execute immediate 'update emp set sal = sal + 1000 where deptno = 30';

end;

/

有符号占位符：

begin

  execute immediate 'update emp set sal = (sal+:addsal) where deptno = :dno' using &1,&2;  

end;

/

有符号占位符，有returing子句：

declare

salary number(6,2);

begin

  execute immediate 'update emp set sal = (sal+:addsal) where '||

   'empno = :dno returning sal into :salary' using &1,&2 returning into salary;

  dbms_output.put_line('修改后的工资：' || salary);  

end;

/

处理单行查询：

declare

salary number(6,2);

begin

  execute immediate 'select sal from emp where empno = :eno' into salary using &1;

  dbms_output.put_line('雇员的工资：' || salary);  

end;

/

------------------------------------------------------------------------------------------------

处理多行查询

declare

  type emp_cursor_typ is ref cursor;--定义游标变量类型

  emp_cursor emp_cursor_typ;--声明游标变量

  emp_record emp%rowtype;--记录

  sql_stat varchar2(100);

begin

  sql_stat := 'select * from emp where deptno=:dno';

  open emp_cursor for sql_stat using &dno;--打开游标

  loop

    fetch emp_cursor into emp_record;--提取数据

    exit when emp_cursor%notfound;

    dbms_output.put_line('雇员名:'||emp_record.ename);

  end loop;

end;

/

批量提取：

declare

  type ename_table_type is table of emp.ename%type index by binary_integer;

  type sal_table_type is table of emp.sal%type index by binary_integer;

  ename_table ename_table_type;

  sal_table sal_table_type;

  sql_stat varchar2(100);

begin

  sql_stat := 'update emp set sal = sal + 500 where deptno=:dno' || 

  ' returning ename,sal into :name,:salary';

  execute immediate sql_stat using &dno returning bulk collect into 

  ename_table, sal_table;

  for i in 1..ename_table.count loop

    dbms_output.put_line('雇员'||ename_table(i)||'的新工资为：'||sal_table(i));

  end loop;

end;

/

使用fetch的批量：

declare

  type emp_cursor_type is ref cursor;

  emp_cursor emp_cursor_type;

  type ename_table_type is table of emp.ename%type index by binary_integer;

  ename_table ename_table_type;

  sql_stat varchar2(100);

begin

  sql_stat := 'select ename from emp where job=:title';

  open emp_cursor for sql_stat using '&job';

  fetch emp_cursor bulk collect into ename_table;--批量提取

  for i in 1..ename_table.count loop

    dbms_output.put_line(ename_table(i));

  end loop;

end;

/

forall中使用bulk子句：

declare

  type ename_table_type is table of emp.ename%type;

  type sal_table_type is table of emp.sal%type;

  ename_table ename_table_type;

  sal_table sal_table_type;

  sql_stat varchar2(100);

begin

  ename_table := ename_table_type('SCOTT','SMITH','CLARK');

  sql_stat := 'update emp set sal = sal + 500 where ename =:1 returning sal into :2';

  forall i in 1..ename_table.count  --forall

    execute immediate sql_stat using ename_table(i) returning bulk collect into sal_table;

    for j in 1..ename_table.count loop

      dbms_output.put_line('雇员:'||ename_table(j) ||'的新工资为：'||sal_table(j));

    end loop;

end;

/