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


PL/SQL-->动态SQL调用包中函数或过程
 
      动态SQL主要是用于针对不同的条件或查询任务来生成不同的SQL语句。最常用的方法是直接使用EXECUTE IMMEDIATE来执行动态SQL语句字符串或字符串变量。但是对于系统自定义的包或用户自定的包其下的函数或过程，不能等同于DDL以及DML的调用，其方式稍有差异。
 
1、动态SQL调用包中过程不正确的调用方法
[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  
  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 statement  
ORA-06512: at line 8  
  
--下面检查一下是否是sql拼接有问题  
scott@USBO> 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来执行成功  
scott@USBO> exec dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>true)  
  
PL/SQL procedure successfully completed.  
2、动态SQL调用包中过程正确的调用方法 
[sql] 
--如下面这段plsql代码，我们在原代码的基础上增加了begin ..与end部分后，该plsql块被成功执行  
--注，字符拼接的plsql块中，end; 之后不需要使用斜杠“/”  
scott@USBO> 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调用包中过程带变量的情形
[sql] 
--下面这个示例中拼接的字串中，调用了声明中的变量  
--下面给出了错误提示，是由于我们漏掉了两个单引号，即需要使用转义字符，错误如下  
scott@USBO> 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 context  
ORA-06550: line 1, column 7:  
PL/SQL: Statement ignored  
ORA-06512: at line 8  
  
--下面是增加两个单引号后的情形  
scott@USBO> 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中调用包中函数的情形
[sql] 
--下面我们来调用系统包所带的函数dbms_output.put_line  
--Author : Leshami  
--Blog   : http://blog.csdn.net/leshami  
  
scott@USBO> 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 test  
  
PL/SQL procedure successfully completed.  
  
--直接使用下面的方式可以实现  
scott@USBO> BEGIN  
  2     EXECUTE IMMEDIATE 'begin dbms_output.put_line(''This is only a test''); end;';  
  3  END;  
  4  /  
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，个人