ITPub博客

首页 > 数据库 > Oracle > PL/SQL --> 动态SQL调用包中函数或过程

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

Oracle 作者:winder2010 时间:2013-12-30 19:40:03 0 删除 编辑

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

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


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

--如下面这段plsql代 ,我们在原代 的基础上增 了begin ..与end部分后,该plsql块被成功执行
--注,字符拼接的plsql块中,end; 之后不需要使用斜 “/”
scott@USBO> DECLARE
     v_sql   VARCHAR2 (300);
     v_tab   VARCHAR2 (30) := 'DEPT';
  BEGIN
     v_sql := 'begin dbms_stats.gather_table_stats(''SCOTT'',''EMP'',cascade=>true); end;';
     DBMS_OUTPUT.put_line (v_sql);
 
     EXECUTE IMMEDIATE v_sql;
  END;
 10  /
begin dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>true); end;
PL/SQL procedure successfully completed.
3、动态SQL调用包中过程带变量的情形


--下面这个示例中拼接的字串中,调用了声明中的变量
--下面给出了错误提示,是由于我们漏掉了两个单引号,即需要使用转义字符,错误如下
scott@USBO> DECLARE
     v_sql   VARCHAR2 (300);
     v_tab   VARCHAR2 (30) := 'DEPT';
  BEGIN
     v_sql := 'begin dbms_stats.gather_table_stats(''SCOTT'',' || v_tab || ',cascade=>true); end;';
     --DBMS_OUTPUT.put_line (v_sql);
 
     EXECUTE IMMEDIATE v_sql;
  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 context
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at line 8
--下面是增 两个单引号后的情形
scott@USBO> DECLARE
     v_sql   VARCHAR2 (300);
     v_tab   VARCHAR2 (30) := 'DEPT';
  BEGIN
     v_sql := 'begin dbms_stats.gather_table_stats(''SCOTT'',''' || v_tab || ''',cascade=>true); end;';
     DBMS_OUTPUT.put_line (v_sql);
 
     EXECUTE IMMEDIATE v_sql;
  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://dong2008hong.blog.163.com
scott@USBO> DECLARE
     v_sql   VARCHAR2 (300);
  BEGIN
     v_sql := 'begin dbms_output.put_line(''This is only a test''); end;';
 
     --DBMS_OUTPUT.put_line (v_sql);
 
     EXECUTE IMMEDIATE v_sql;
  END;
 10  /
This is only a test
PL/SQL procedure successfully completed.
--直接使用下面的方式可以实现
scott@USBO> BEGIN
     EXECUTE IMMEDIATE 'begin dbms_output.put_line(''This is only a test''); end;';
  END;
  /
This is only a test
PL/SQL procedure successfully completed.
5、小结
a、对于动态SQL来调用函数,我们需要使用begin .. end来封装块,而不是简单的类似于DML以及DDL的调用方法
b、不能使用'exec pkg_name.proc_name'方式来拼接动态sql
c、可以拼接sql到变量,也可以直接将动态sql紧跟在EXECUTE IMMEDIATE,个人更倾向于使用前者

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23728664/viewspace-1116210/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-04-15