oracle开发中动态SQL的使用方法(二)
; -- 为处理打开光标
v_string: = ' create table ' || ' ' || table_name || ' ( ' || field_name1 || ' ' || datatype1 || ' , ' || field_name2 || ' ' || datatype2 || ' ) ' ;
dbms_sql.parse(v_cursor,v_string,dbms_sql.native); -- 分析语句
v_row: = dbms_sql. execute (v_cursor); -- 执行语句,动态SQL执行DDL时可以不写
dbms_sql.close_cursor(v_cursor); -- 关闭光标
exception when others then
dbms_sql.close_cursor(v_cursor); -- 关闭光标
raise;
end ;
以上过程编译通过后，执行过程创建表结构：
SQL > execute proc_dbms_sql( ' dinya_test2 ' , ' id ' , ' number(8) not null ' , ' name ' , ' varchar2(100) ' );
PL / SQL procedure successfully completed
SQL > desc dinya_test2;
Name Type Nullable Default Comments
-- -- ------------- -------- ------- --------
ID NUMBER ( 8 )
NAME VARCHAR2 ( 100 ) Y
SQL >
2、使用DBMS_SQL包执行DML语句
需求：使用DBMS_SQL包根据用户输入的值更新表中相对应的记录。
查看表中已有记录：
SQL > select * from dinya_test2;
ID NAME
1 Oracle
2 CSDN
3 ERP
SQL >
建存储过程，并编译通过：
create or replace procedure proc_dbms_sql_update
(
id number ,
name varchar2
) as
v_cursor number ; -- 定义光标
v_string varchar2 ( 200 ); -- 字符串变量
v_row number ; -- 行数
begin
v_cursor: = dbms_sql.open_cursor; -- 为处理打开光标
v_string: = ' update dinya_test2 a set a.name=:p_name where a.id=:p_id ' ;
dbms_sql.parse(v_cursor,v_string,dbms_sql.native); -- 分析语句
dbms_sql.bind_variable(v_cursor, ' :p_name ' ,name); -- 绑定变量
dbms_sql.bind_variable(v_cursor, ' :p_id ' ,id); -- 绑定变量
v_row: = dbms_sql. execute (v_cursor); -- 执行动态SQL
dbms_sql.close_cursor(v_cursor); -- 关闭光标
exception when others then
dbms_sql.close_cursor(v_cursor); -- 关闭光标
raise;
end ;
执行过程，根据用户输入的参数更新表中的数据：
SQL > execute proc_dbms_sql_update( 2 , ' csdn_dinya ' );
PL / SQL procedure successfully completed
SQL > select * from dinya_test2;
ID NAME
1 Oracle
2 csdn_dinya
3 ERP
SQL >
执行过程后将第二条的name字段的数据更新为新值csdn_dinya。这样就完成了使用dbms_sql包来执行DML语句的功能。
使用DBMS_SQL中，如果要执行的动态语句不是查询语句，使用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来执行查询并得到结果。
总结说明：
在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)部分可以不要。