关于[Oracle整理]动态SQL的使用方法

小小编

2 年前

什么时候时动态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 || \’\’ ||

\’)\’;