动态SQL调用包中函数过程_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
2
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 553 | 回复: 1   主题: 动态SQL调用包中函数过程        上一篇   下一篇 
wayne
注册用户
等级:中校
经验:1690
发帖:221
精华:0
注册:2011-7-21
状态:离线
发送短消息息给wayne 加好友    发送短消息息给wayne 发消息
发表于: IP:您无权察看 2015-7-20 16:12:38 | [全部帖] [楼主帖] 楼主

动态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
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,ViewOrSequence 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


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 isonly 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 isonly 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)    操作        顶端 
联动大白
注册用户
等级:新兵
经验:79
发帖:0
精华:0
注册:2015-5-27
状态:离线
发送短消息息给联动大白 加好友    发送短消息息给联动大白 发消息
发表于: IP:您无权察看 2019-5-28 0:30:00 | [全部帖] [楼主帖] 2  楼

为了方便大家阅读,我对文章中错误号来解释一下吧!

Error Id: ORA-06512

Title: at stringline string

Description:

at stringline string

Action:

Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA.

Cause:

Backtrace message as the stack is unwound by unhandled exceptions.


Error Id: ORA-06550

Title: line string, column string: string

Description:

line string, column string: string

Action:

none

Cause:

Usually a PL/SQL compilation error.


Error Id: PLS-00357

Title: Table,View Or Sequence reference ’string’ not allowed in this context

Description:

Table,View Or Sequence reference ’string’ not allowed in this context

Action:

Remove or relocate the illegal reference.

Cause:

A reference to database table, view, or sequence was found in an inappropriate context. Such references can appear only in SQL statements or (excluding sequences) in %TYPE and %ROWTYPE declarations. Some valid examples follow: SELECT ename, emp.deptno, dname INTO my_ename, my_deptno, my_dept .FROM emp, dept WHERE emp.deptno = dept.deptno; DECLARE last_name emp.ename%TYPE; dept_rec dept%ROWTYPE;


也许你已明白,但对一个人有用也是我存在的理由!^_^ By:持之以恒的大白

-- 来自: 北京联动北方科技有限公司



赞(0)    操作        顶端 
总帖数
2
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论