当前位置: 代码迷 >> SQL >> PL/SQL -> 动态SQL调用包中函数或历程
  详细解决方案

PL/SQL -> 动态SQL调用包中函数或历程

热度:11   发布时间:2016-05-05 12:03:35.0
PL/SQL --> 动态SQL调用包中函数或过程

      动态SQL主要是用于针对不同的条件或查询任务来生成不同的SQL语句。最常用的方法是直接使用EXECUTE IMMEDIATE来执行动态SQL语句字符串或字符串变量。但是对于系统自定义的包或用户自定的包其下的函数或过程,不能等同于DDL以及DML的调用,其方式稍有差异。如下见本文的描述。

      有关动态SQL的描述,请参考:
           PL/SQL --> 动态SQL
           PL/SQL --> 动态SQL的常见错误

 

1、动态SQL调用包中过程不正确的调用方法

--演示环境[email protected]> select * from v$version where rownum<2;BANNER--------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production--下面的调用方法不正确,收到了ORA-00900错误消息[email protected]> set serveroutput on;[email protected]> DECLARE  2     v_sql   VARCHAR2 (300);  3     v_tab   VARCHAR2 (30) := 'DEPT';  4  BEGIN  5     v_sql := 'dbms_stats.gather_table_stats(''SCOTT'',''EMP'',cascade=>true)';  6     DBMS_OUTPUT.put_line (v_sql);  7    8     EXECUTE IMMEDIATE v_sql;  9  END; 10  /dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>true)DECLARE*ERROR at line 1:ORA-00900: invalid SQL statementORA-06512: at line 8--下面检查一下是否是sql拼接有问题[email protected]> DECLARE  2     v_sql   VARCHAR2 (300);  3     v_tab   VARCHAR2 (30) := 'DEPT';  4  BEGIN  5     v_sql := 'dbms_stats.gather_table_stats(''SCOTT'',''EMP'',cascade=>true)';  6     DBMS_OUTPUT.put_line (v_sql);  7    8  --   EXECUTE IMMEDIATE v_sql;  9  END; 10  /dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>true)PL/SQL procedure successfully completed.--对于上面的SQL拼接正常,如下,直接复制输出的sql加上exec来执行成功[email protected]> exec dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>true)PL/SQL procedure successfully completed.

2、动态SQL调用包中过程正确的调用方法 

--如下面这段plsql代码,我们在原代码的基础上增加了begin ..与end部分后,该plsql块被成功执行--注,字符拼接的plsql块中,end; 之后不需要使用斜杠“/”[email protected]> DECLARE  2     v_sql   VARCHAR2 (300);  3     v_tab   VARCHAR2 (30) := 'DEPT';  4  BEGIN  5     v_sql := 'begin dbms_stats.gather_table_stats(''SCOTT'',''EMP'',cascade=>true); end;';  6     DBMS_OUTPUT.put_line (v_sql);  7    8     EXECUTE IMMEDIATE v_sql;  9  END; 10  /begin dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>true); end;PL/SQL procedure successfully completed.

3、动态SQL调用包中过程带变量的情形

--下面这个示例中拼接的字串中,调用了声明中的变量--下面给出了错误提示,是由于我们漏掉了两个单引号,即需要使用转义字符,错误如下[email protected]> DECLARE  2     v_sql   VARCHAR2 (300);  3     v_tab   VARCHAR2 (30) := 'DEPT';  4  BEGIN  5     v_sql := 'begin dbms_stats.gather_table_stats(''SCOTT'',' || v_tab || ',cascade=>true); end;';  6     --DBMS_OUTPUT.put_line (v_sql);  7    8     EXECUTE IMMEDIATE v_sql;  9  END; 10  /DECLARE*ERROR at line 1:ORA-06550: line 1, column 45:PLS-00357: Table,View Or Sequence reference 'DEPT' not allowed in this contextORA-06550: line 1, column 7:PL/SQL: Statement ignoredORA-06512: at line 8--下面是增加两个单引号后的情形[email protected]> DECLARE  2     v_sql   VARCHAR2 (300);  3     v_tab   VARCHAR2 (30) := 'DEPT';  4  BEGIN  5     v_sql := 'begin dbms_stats.gather_table_stats(''SCOTT'',''' || v_tab || ''',cascade=>true); end;';  6     DBMS_OUTPUT.put_line (v_sql);  7    8     EXECUTE IMMEDIATE v_sql;  9  END; 10  /begin dbms_stats.gather_table_stats('SCOTT','DEPT',cascade=>true); end;PL/SQL procedure successfully completed.

4、动态SQL中调用包中函数的情形

--下面我们来调用系统包所带的函数dbms_output.put_line--Author : Leshami--Blog   : http://blog.csdn.net/leshami[email protected]> DECLARE  2     v_sql   VARCHAR2 (300);  3  BEGIN  4     v_sql := 'begin dbms_output.put_line(''This is only a test''); end;';  5    6     --DBMS_OUTPUT.put_line (v_sql);  7    8     EXECUTE IMMEDIATE v_sql;  9  END; 10  /This is only a testPL/SQL procedure successfully completed.--直接使用下面的方式可以实现[email protected]> BEGIN  2     EXECUTE IMMEDIATE 'begin dbms_output.put_line(''This is only a test''); end;';  3  END;  4  /This is only a testPL/SQL procedure successfully completed.

5、小结
a、对于动态SQL来调用函数,我们需要使用begin .. end来封装块,而不是简单的类似于DML以及DDL的调用方法
b、不能使用'exec pkg_name.proc_name'方式来拼接动态sql
c、可以拼接sql到变量,也可以直接将动态sql紧跟在EXECUTE IMMEDIATE,个人更倾向于使用前者

 

Oracle&nbsp;牛鹏社    Oracle DBsupport

更多参考

DML Error Logging 特性 

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

  相关解决方案