oracle数据库中动态SQL使用小结

oracle数据库中编译PL/SQL程序块分为两种,一种是前期编译,即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型;另一种是后期编译,即SQL语句只有在程序运行阶段才能确定,例如当查询条件为用户输入时,那么oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。通常,静态SQL采用前期编译,动态SQL采用后期编译。

本文简单的总结下oracle数据库中动态SQL的用法:

1、动态SQL语法

(1)语法

①EXECUTE IMMEDIATE 动态SQL语句 USING 绑定参数列表 RETURNING INTO 输出参数列表;

②使用OPEN-FOR、FETCH和CLOSE语句处理多行查询语句。

(2)说明

①动态SQL是指DDL和不确定的DML(即带参数的DML)。

②“绑定参数列表”为输入参数列表,其类型为in类型,在运行时刻与动态SQL语句中的参数进行绑定。

③“输出参数列表”为动态SQL语句执行后返回的参数列表。

④由于动态SQL是在运行时进行确定,所以相对于静态SQL而言,会损失一些系统性能来换取其灵活性。

⑤EXECUTE IMMEDIATE只能处理单条查询结果语句的SQL。

2、执行动态SQL

(1)使用EXECUTE IMMEDIATE

EXECUTE IMMEDIATE代替了以前oracle 8i版本中的DBMS_SQL包,它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块,动态创建和执行SQL语句的性能很好,EXECUTE IMMEDIATE的目标在于减小企业成本并获得较高的性能,相比以前,编码也很简单。

EXECUTE IMMEDIATE的使用有如下技巧:

①EXECUTE IMMEDIATE不会提交一个DML事务执行,应该显示提交,如果通过EXECUTE IMMEDIATE来处理DML命令,那么在完成以前需要显示提交或者作为EXECUTE IMMEDIATE自己的一部分。如果通过EXECUTE IMMEDIATE来处理DDL命令,它会提交所有以前改变的数据。

②不支持返回多行的查询,这种交互将用临时表来存储记录或者用REF CURSOR

③当执行SQL时,不要用分号,当执行PL/SQL时,在其尾部加上分号。

④对于Forms开发者,应该在PL/SQL 8.0.6.3及以后版本中使用,Forms 6i不能使用此功能(我还没用Form测试,都是在PL/SQL中写,如果说法有误,以后修改)。

⑤当使用EXECUTE IMMEDIATE处理DML语句时,如果DML语句没有“绑定参数列表”,也没有RETURNING子句,那么在EXECUTE IMMEDIATE语句之后不需要带有USING和RETURNING INTO子句。

(2)使用OPEN-FOR、FETCH和CLOSE语句

使用步骤:定义游标变量 -> 打开游标变量 -> 循环游标变量 -> 关闭游标变量

EXECUTE IMMEDIATE只能处理单行查询语句,处理多行查询语句需要使用OPEN-FOR、FETCH和CLOSE语句。

3、EXECUTE IMMEDIATE用法示例

(1)在PL/SQL中运行DDL语句

BEGIN
  EXECUTE IMMEDIATE 'create table t_yed01(c_id number, c_name varchar2(100), c_age number, c_sex varchar2(2))';
END;

(2)使用USING子句给动态语句传值

BEGIN
  -- 固定插入一条记录
  EXECUTE IMMEDIATE 'insert into t_yed01(c_id, c_name, c_age, c_sex) values (:1, :2, :3, :4)'
    USING 1, 'yedward', 22, 'M';
  -- 在PL/SQL Developer中是可以通过用&来让手工输入
  EXECUTE IMMEDIATE 'insert into t_yed01(c_id, c_name, c_age, c_sex) values (:1, :2, :3, :4)'
    USING &id, &NAME, &age, &sex;
END;

注意,USING后面的上面这段PL/SQL在PL/SQL Developer中的运行效果如下图1所示:

在PL/SQL中的运行效果

图1:在PL/SQL Developer中的运行效果

从图1中可以看出,由于没有在代码中写commit,所以左上角有个提交或者回滚的按钮。我在运行的时候发现了一个小问题,挺有意思,我发现当使用&来输入参数的时候,运行这段代码,首先执行的并不是&上面的那段插入,而是直接弹出一个变量输入对话框,输入完点击确定以后,才开始从头开始运行代码,也就是说,其实PL/SQL Developer是先让输入参数,然后再执行代码的,并不是先执行,然后运行到了有&的地方再让输入参数,这个地方可以注意下。

(3)动态SQL实现into操作

DECLARE
  l_tmp NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'select 99 from dual'
    INTO l_tmp;
  dbms_output.put_line(l_tmp);
END;

(4)动态SQL执行程序包

BEGIN
  EXECUTE IMMEDIATE 'begin ' || g_import_package || '.validate_data(:0,:1);end;'
    USING IN p_account_id, OUT x_message;
END;

好奇的朋友,也可以试试把g_import_package这个包名放在分号里面(字符串里面),看看可不可以。动态SQL的简单用法总结大概如上,有些地方我还没有来得及在oracle中测试,以后慢慢再补充。

本文标题:oracle数据库中动态SQL使用小结

本文链接:http://yedward.net/?id=181

本文版权归作者所有,欢迎转载,转载请以文字链接的形式注明文章出处。

相关文章