postgre 存储过程 sql查询 动态参数

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

oracle视频教程请关注:http://down.51cto.com/4202939/up

转载于:https://blog.51cto.com/19880614/1316555