在存储过程中做简单动态查询（二）

二、使用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包根据用户输入的表名、字段名及字段类型建表。

create or replace procedure proc_dbms_sql

（

     table_name in varchar2,        --表名

     field_name1 in varchar2,       --字段名

     datatype1 in varchar2,         --字段类型

     field_name2 in varchar2,       --字段名

     datatype2 in varchar2          --字段类型

）as

     v_cursor number;               --定义光标

     v_string varchar2（200）；       --定义字符串变量

     v_row number;                   --行数

begin

     v_cursor:=dbms_sql.open_cursor;      --为处理打开光标

     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）；    --执行语句

     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）；　　　　　　　　　　　--执行动态ＳＱＬ

     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）部分可以不要。

 

（文章来自网络，如有侵权请来信告知，本站将在第一时间删除。）