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)