说明：本内容是工作用到的知识点整理，来自工作中和网络。
代码于Oracle9上测试。
什么时候时动态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;
end; |
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;
commit;
end;
-- with parameter
declare
v_sql varchar2(2000);
begin
v_sql := 'insert into xx_test3 values(:value1,:value2)';
execute immediate v_sql using 'test_p1', 'test_p2';
commit;
end;
-- Update
declare
v_sql varchar2(2000);
begin
v_sql :='update xx_test3 set col2=''updatecol1'' where col3=''c'' ';
execute immediate v_sql;
commit;
v_sql := 'update xx_test3 set col2 = :v_col2 where col3=:v_col3';
execute immediate v_sql using 'update test_p1','test_p2';
commit;
end;
-- Delete
declare
v_sql varchar2(2000);
begin
v_sql := 'delete from xx_test3 where col2 = :v_col2';
execute immediate v_sql using 'c';
commit;
end; |
在上例中，本地动态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_col31_type varchar2(30);
v_col32_name varchar2(30);
v_col32_type varchar2(30);
v_cursor number;
v_row number;
begin
v_table_name := 'xx_test4';
v_col31_name := 'col41';
v_col31_type :='varchar2(100)';
v_col32_name := 'col42';
v_col32_type :='varchar2(100)';
-- step1
v_sql := 'create table ' || v_table_name ||
'(' ||
v_col31_name || ' ' || v_col31_type || ',' ||
v_col32_name || ' ' || v_col32_type || '' ||
')';
dbms_output.put_line('v_sql ' || v_sql);
-- step2
v_cursor := dbms_sql.open_cursor; -- open cursor for analyse SQL
--dbms_output.put_line('v_cursor ' || to_char(v_cursor));
dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
-- step3
v_row := dbms_sql.execute(v_cursor); -- execute sql statemet
--dbms_output.put_line('v_row ' || to_char(v_row));
dbms_sql.close_cursor(v_cursor); -- close cursor
exception
when others then
dbms_sql.close_cursor(v_cursor);
end; |
2、使用DBMS_SQL包执行DML语句
需求：使用DBMS_SQL包根据用户输入的值操作表中相对应的记录。
| -- DML ------------------------
-- insert
declare
v_sql varchar2(2000);
v_cursor number;
v_row number;
begin
v_sql :='insert into xx_test4 values(:P_col41,:P_col42)';
-- 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
v_row := dbms_sql.execute(v_cursor);
-- 5
dbms_sql.close_cursor(v_cursor);
commit;
exception
when others then
dbms_sql.close_cursor(v_cursor);
end; |
执行查询语句，使用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 || '' ||
')';
