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

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

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

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
  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调用包中过程正确的调用方法

--如下面这段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调用包中过程带变量的情形

--下面这个示例中拼接的字串中,调用了声明中的变量
--下面给出了错误提示,是由于我们漏掉了两个单引号,即需要使用转义字符,错误如下
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中调用包中函数的情形

--下面我们来调用系统包所带的函数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,个人更倾向于使用前者

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏AhDung

【SQL】分配函数一枚[AllotToTable]

像这种分配法我估计在多种场景都有在用,它应该有个名堂的,只是我不知道,还请知道的猿友告知,谢谢。

13310
来自专栏张俊红

数据分析系列——SQL数据库

数据分析系列——SQL数据库 总第49篇 ▼ 本文知识只是用作于常用的数据分析中,并未涉及专业数据库搭建等知识。全篇分为四个部分:初识数据库、数据库的操作、数据...

39880
来自专栏芋道源码1024

数据库中间件 Sharding-JDBC 源码分析 —— SQL 解析(三)之查询SQL

1. 概述 2. SelectStatement 2.1 AbstractSQLStatement 2.2 SQLToken 3. #query() 3.1 ...

41080
来自专栏battcn

MySQL - RANGE优化篇

对于单列索引,索引值区间可以方便地用WHERE语句中的相应范围条件表示。优化器在常量传播阶段,会将一些非常量值转换为常量。

21630
来自专栏熊二哥

那些年我们写过的T-SQL(中篇)

中篇的重点在于,在复杂情况下使用表表达式的查询,尤其是公用表表达式(CTE),也就是非常方便的WITH AS XXX的应用,在SQL代码,这种方式至少可以提高一...

19370
来自专栏me的随笔

T-SQL基础(一)之简单查询

SQL: Structured Query Language,结构化查询语言,是一种在关系型数据库中用于管理数据的标准语言。SQL是一种声明式编程语言,即只需表...

21920
来自专栏Jackson0714

【T-SQL基础】01.单表查询-几道sql查询题

35590
来自专栏程序员的SOD蜜

使用CTE解决复杂查询的问题

最近,同事需要从数个表中查询用户的业务和报告数据,写了一个SQL语句,查询比较慢: Select S.Name, S.AccountantCode, ( Se...

24960
来自专栏大前端_Web

SQL笔记

版权声明:本文为吴孔云博客原创文章,转载请注明出处并带上链接,谢谢。 https://blog.csdn.net/wkyseo/articl...

13520
来自专栏乐沙弥的世界

MongoDB 部分索引(Partial Indexes)

a、部分索引就是带有过滤条件的索引,即索引只存在与某些文档之上 b、满足过滤条件的文档在查询时,其执行计划将使用该列上的索引,否则不会被使用 c、稀疏索...

14100

扫码关注云+社区

领取腾讯云代金券