个人对ORACLE本地动态SQL的总结

 一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用。也就是说要想在PL/SQL的begin和end之间直接使用类似于“select * from fw.math where n1=2”这样的语句是不行的,会抱错“在select语句中缺少into子句”。更加不能用DDL语句例如创建一个类型为表的对象"create table ss(s int)"。那么，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。
    在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指
在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。
     接下来用几个例子来说明本地动态SQL的用法。
     首先，我们可以动态地根据用户的输入来创建一张表，假设表里有2个字段。那么，我现在创建一个带输入参数也就是IN类型的过程：
create or replace procedure
  create_table(table_name in varchar2,column_name1 in varchar2,data_type1 in varchar2,
column_name2 in varchar2,data_type2 in varchar2)
is
sql_stmt varchar2(255);
begin
  sql_stmt := 'create table '||table_name||'('||column_name1||' '||data_type1||','||
  column_name2||' '||data_type2||')';
  execute immediate sql_stmt;
  dbms_output.put_line('名称为'||table_name||'的表已经成功创建');
  exception when others
  then dbms_output.put_line(sqlerrm);
end;
这里的sql_stmt也就是一句拼装而成的动态SQL语句

之后，我在PL/SQL里调用这个过程来动态地创建表：
declare
  table_name  varchar2(200);
  column_name1 varchar2(200);
  data_type1 varchar2(200);
  data_type2 varchar2(200);
  column_name2 varchar2(200);
begin
  table_name := '&请输入表名';
  column_name1 := '&请输入第1列的列名';
  data_type1 := '&请输入第1列的类型';
  column_name2 := '&请输入第2列的列名';
  data_type2 := '&请输入第2列的类型';
  fw.create_table(table_name,column_name1,data_type1,column_name2,data_type2);
end;

这里注意可能会产生“权限不足的异常”，我上网查了下原因是：如果在一个匿名块或者过程中通过动态SQL来执行DDL语句需要直接把CREATE TABLE之类的权限直接赋给该用户，通过角色赋的权限会失效，即可能出现“权限不足”的异常。

那么，最好执行“grant create table to fw”给予权限。


  上面的例子中，动态SQL是接受了过程的输入参数，同样的，它也可以接收绑定到某个被声明了的变量的值。
那么再看一个例子：
   我的需求是根据用户的输入动态地向表里插入一条记录
  表名是math，模式是fw，表的结构如下：
名称                                      是否为空? 类型
----------------------------------------- -------- -------------
N1                                                 NUMBER(38)
N2                                                 NUMBER(38)
MAX                                               NUMBER(38)
  
  首先还是创建一个过程：
create or replace procedure
  insert_into_math(n1_input fw.math.n1%type,n2_input fw.math.n2%type,max_input fw.math.max%type)
is
  sql_stmt varchar2(255);
begin
  sql_stmt := 'insert into fw.math values(:n1,:n2,:max)';
  execute immediate sql_stmt using n1_input,n2_input,max_input;
  commit;
  dbms_output.put_line('插入成功');
  exception when others then
  dbms_output.put_line(sqlerrm);
end;  

  在动态SQL“'insert into fw.math values(:n1,:n2,:max)”中:n1,:n2,:,max都是形式为:xxx的占位符，和Hibernate的HQL里的:xxx是一样的，XXX的实质也就是一个绑定变量，因此在引用的时候要在之前加：。而using xxx的xxx代表这些动态的参数要接收名为xxx的变量的值。其实这里也就相当于JDBC里的预编译SQL“insert into fw.math values(?,?,?)”。

  接下来，我在PL/SQL里调用上面这个过程：
declare
  n1_input fw.math.n1%type;
  n2_input fw.math.n2%type;
  max_input fw.math.max%type;
begin
  n1_input := '&请输入n1的值';
  n2_input := '&请输入n2的值';
  max_input := '&请输入max的值';
  fw.insert_into_math(n1_input,n2_input,max_input);
end;
  

     同样的，动态SQL不仅能接受输入参数，也能用“into XXX”将执行后返回的结果交给一个名为XXX的变量作为输出参数保存。
例如：
declare
    n1_input fw.math.n1%type;
    count_output number;
    sql_stmt varchar2(255);
begin
    n1_input := '&请输入n1的值';
    sql_stmt:='select count(*) from fw.math  where n1=:n1';
    execute immediate sql_stmt into count_output using n1_input;  
    dbms_output.put_line('根据n1='||n1_input||'找到的记录有'||count_output||'条');
    exception when others then
    dbms_output.put_line(sqlerrm);
end;

      

    以上就是ORACLE中本地动态SQL的基本用法，另外还有一种形式的动态SQL是通过PL/SQL的内置包DBMS_SQL的一些过程和函数通过操作游标来进行的，这里就不再多说。