小小编
什么时候时动态SQL?
要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。
静态SQL语句？
所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。
动态SQL语句？
动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。
实现动态SQL途径？
一是通过本地动态SQL来执行，二是通过DBMS_SQL包来执行。
一、本地动态SQL
本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。
1、本地动态SQL执行DDL语句：
需求：根据用户输入的表名及字段名等参数动态建表。
| — DDL —————– declare v_sql varchar2(2000); begin v_sql := \’create table xx_test3\’ || \'(\’ || \’col2 varchar2(20),\’ || \’col3 varchar2(20)\’ || \’)\’; execute immediate v_sql; |
2、本地动态SQL执行DML语句。
需求：将用户输入的值插入到上例中建好的表中。
| — DML —————- — without parameter declare v_sql varchar2(2000); begin v_sql := \’insert into xx_test3 values(\’\’a\’\’,\’\’c\’\’)\’; execute immediate v_sql; v_sql := \’update xx_test3 set col2 = :v_col2 where col3=:v_col3\’; |
在上例中，本地动态SQL执行DML语句时使用了using子句，按顺序将输入的值绑定到变量，如果需要输出参数，可以在执行动态SQL的时候，使用RETURNING INTO 子句，如：
需要输出参数，使用INTO 子句
| — SELECT —————— declare v_ret1 varchar2(100); — accept return value v_ret2 varchar2(100); — accept return value v_sql varchar2(2000); begin v_sql := \’select col2,col3 from xx_test3 where col3=:P_col3\’; execute immediate v_sql into v_ret1,v_ret2 using \’c\’; dbms_output.put_line(v_ret1 || \’ \’ || v_ret2); end; |
二、使用DBMS_SQL包
使用DBMS_SQL包实现动态SQL的步骤如下：A、先将要执行的SQL语句或一个语句块放到一个字符串变量中。B、使用DBMS_SQL包的parse过程来分析该字符串。C、使用DBMS_SQL包的bind_variable过程来绑定变量。D、使用DBMS_SQL包的execute函数来执行语句。
1、使用DBMS_SQL包执行DDL语句
需求：使用DBMS_SQL包根据用户输入的表名、字段名及字段类型建表。
| — DDL declare v_sql varchar2(2000); v_table_name varchar2(30); v_col31_name varchar2(30); v_col32_name varchar2(30); v_cursor number; v_col31_name := \’col41\’; v_col32_name := \’col42\’; |
2、使用DBMS_SQL包执行DML语句
需求：使用DBMS_SQL包根据用户输入的值操作表中相对应的记录。
| — DML ———————— — insert declare v_sql varchar2(2000); v_cursor number; v_row number; begin — 1 commit; |
执行查询语句，使用DBMS_SQL.Execute或DBMS_SQL.Variable_Value来执行，如果要执行动态语句是查询语句，则要使用DBMS_SQL.define_column定义输出变量，然后使用DBMS_SQL.Execute, DBMS_SQL.Fetch_Rows, DBMS_SQL.Column_Value及DBMS_SQL.Variable_Value来执行查询并得到结果。
| — Select DECLARE v_sql varchar2(2000); v_cursor number; v_row number; v_ret varchar2(100); BEGIN v_sql := \’select col41 from xx_test4 where col41=\’\’row11\’\’ \’; — 1 v_cursor := dbms_sql.open_cursor(); — 2 dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); — 3 /*dbms_sql.bind_variable(v_cursor,\’:P_col41\’,\’row11\’); dbms_sql.bind_variable(v_cursor,\’:P_col42\’,\’row12\’);*/ — 4 dbms_sql.define_column(v_cursor, 1, v_ret, 100); — 5 v_row := dbms_sql.execute(v_cursor); — 6 IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN DBMS_SQL.column_value(v_cursor, 1, v_ret); — 7 dbms_sql.close_cursor(v_cursor); dbms_output.put_line(\’return:\’ || v_ret); END IF; EXCEPTION WHEN OTHERS THEN dbms_sql.close_cursor(v_cursor); END; |
总结说明：
在Oracle开发过程中，我们可以使用动态SQL来执行DDL语句、DML语句、事务控制语句及系统控制语句。但是需要注意的是，PL/SQL块中使用动态SQL执行DDL语句的时候与别的不同，在DDL中使用绑定变量是非法的（bind_variable(v_cursor,’:p_name’,name)），分析后不需要执行DBMS_SQL.Bind_Variable，直接将输入的变量加到字符串中即可。另外，DDL是在调用DBMS_SQL.PARSE时执行的，所以DBMS_SQL.EXECUTE也可以不用，即在上例中的v_row:=dbms_sql.execute(v_cursor)部分可以不要。
注意：
1,多加了分號。
v_sql的內容為SQL語句後面不可以加分號(;)。example：v_sql := \’insert into xx_test3 values(\’\’a\’\’,\’\’c\’\’)\’;
2,沒有加COMMIT。
本地動態SQL(execute immediate)，DML需要COMMIT;
3,漏掉了into。
insert 后跟 into
4,漏掉了空格
columnname datatype ,中間有空格。example：
v_col31_name || \’ \’ || v_col31_type || \’,\’ ||
5,建議格式
排好版，易讀，不會出錯。
v_sql := \’create table \’ || v_table_name ||
\'(\’ ||
v_col31_name || \’ \’ || v_col31_type || \’,\’ ||
v_col32_name || \’ \’ || v_col32_type || \’\’ ||
\’)\’;
