ORACLE动态SQL笔记

下面的验证是在PL/SQL developer8 上面编写和验证。窗口为SQL WINDOWS;

1,使用execute immediate处理DDL操作
在PL/SQL处理DDL语句时，execute immediate后面只需要带有ddl语句文本即可，而不需要into和using子句：
example1:
create or replace procedure pro_drop_table(p_table_name varchar2) is
  sql_statement varchar2(100);
begin
  sql_statement := 'drop table ' || p_table_name;
  execute immediate sql_statement;
end;

表test2已经存在数据库中，验证如下：
begin
  pro_drop_table('test2');
  end;

2,使用execute immediate处理DCL操作

 在PL/SQL处理DDL语句时，execute immediate后面只需要带有dcl语句文本即可，而不需要into和using子句：
example2:

create or replace procedure pro_grant_sys_priv(p_priv     varchar2,
                                               p_username varchar2) is
  sql_statement varchar2(100);
begin
  sql_statement := 'grant ' || p_priv || ' to ' || p_username;
  execute immediate sql_statement;
end;

验证如下：
begin
  pro_grant_sys_priv('create session', 'scott');
end;

3,使用execute immediate处理DML操作
当使用execute immediate 处理dml语句时，如果dml语句没有占位符，也没有returning子句，那么在execute immediate
语句之后不需要带有using和returning into子句。
(1)处理无占位符和returning子句的DML语句

declare
  sql_statement varchar2(100);
begin
  sql_statement := 'update emp set sal = sal* 1.1 where deptno=30';
  execute immediate sql_statement;
end;

(2)处理包含占位符的DML语句
declare
  sql_statement varchar2(100);
begin
  sql_statement := 'update emp set sal = sal* (1+:percent/100) ' || ' where deptno=:dno'';
  execute immediate sql_statement using &1,&2;
  end;

 (3)处理包含RETURNING子句的DML语句
declare
  salary        number(6, 2);
  sql_statement varchar2(100);
begin
  sql_statement := 'update emp set sal = sal *(1+:percent/100)' ||  ' where empno = :eno  returning sal into :salary ';
  execute immediate sql_statement
    using &1, &2
    returning into salary;
  dbms_output.put_line('新工资:' || salary);
end;

4,使用execute immediate 处理单行查询
declare
  sql_statement varchar2(100);
  emp_record    emp%rowtype;
begin
  sql_statement := 'select * from emp where empno = :eno';
  execute immediate sql_statement
    into emp_record
    using &1;
  dbms_output.put_line('雇员 ' || emp_record.ename || '的工资为' ||
                       emp_record.sal);
end;

5,动态SQL处理多行查询语句，需要使用OPEN-FOR,FETCH和CLOSE 语句。
---定义游标变量---打开游标变量---循环游标变量---关闭游标变量
(1),定义游标变量语法
declare type cursor_name is ref cursor;
cursor_variable cursor_name;
(2),打开游标变量语法
OPEN cursor_variable for dynamic_string [USING bind_argument[,bin_argument]...];
--dynamic_string是动态的select语句,bind_argument用于指定存放传递给动态select语句值的变量。
(3),循环提取数据语法
FETCH cursor_variable INTO {var1[,var2]..|  record_var};
--var是用于接收提取结果的变量；record_var是用于接收提取结果的记录变量。
(4),关闭游标
CLOSE cursor_variable；
(5),查询示例：

  declare
    type emp_cur_type is ref cursor;
    emp_cur       emp_cur_type;
    emp_record    emp%rowtype;
    sql_statement varchar2(200);
  begin
    sql_statement := 'select * from emp where deptno = :dno';
    open emp_cur for sql_statement
      using &dno;
    loop
      fetch emp_cur
        into emp_record;
      exit when emp_cur%notfound;
      dbms_output.put_line('雇员名：' || emp_record.ename || '，工资' ||
                           emp_record.sal);
    end loop;
  end; 

6,在动态SQL语句中使用BULK子句，实际是动态SQL语句将变量绑定为集合元素。
集合类型可以是PL/SQL所支持的索引表，嵌套表和VARRY；当集合元素必须使用SQL数据类型(number,char等)，
而不能使用PL/SQL数据类型(如binary_integer,boolean等)；有三种支持BULK子句：EXECUTE IMMEDIATE,FETCH,
FORALL，下面分别介绍在这三种语句中使用BULK子句的方法。

(1)使用bulk子句处理DML语句返回子句

 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_statement varchar2(200);
  begin
    sql_statement := 'update emp set sal = sal*(1+percent/100)' ||
                     ' where deptno=:dno' ||
                     ' returing ename,sal into :name,:salary';
    execute immediate sql_statement
      using &percent,&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;