简单说一下Native Dynamic SQL（动态sql）和包DBMS_SQL

最新推荐文章于 2021-06-10 08:42:30 发布

转载最新推荐文章于 2021-06-10 08:42:30 发布 · 233 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/gracejiang/archive/2011/01/30/5890434.html

文章标签：

#数据库 #runtime

当SQL语句超过32K字节时，EXECUTE IMMEDIATE将无法执行。本文介绍如何使用DBMS_SQL包解决此问题，适用于动态SQL语句长度无限制的情况。同时，对比Native Dynamic SQL的局限性，说明DBMS_SQL的适用场景。

问题：公司的一个小盆友问：我写了一个超长的sql,然后用EXECUTE IMMEDIATE的动态sql执行时候，会报错.

解决办法：
①首先确认EXECUTE IMMEDIATE能执行最长sql长度，32K。小盆友反映自己的没有达到32K,这个应该和数据库参数设置有关系。我不知道怎么设置的，这个理由有点牵强 O(∩_∩)O哈！
②那么能不能找到一个可以有替代性的而sql长度又没有限制的API呢？
那就是包DBMS_SQL。

看一下官方文档的解释吧

Oracle lets you to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime. This enables you to create more general-purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

Native Dynamic SQL is an alternative to DBMS_SQL that lets you place dynamic SQL statements directly into PL/SQL blocks. In most situations, Native Dynamic SQL is easier to use and performs better than DBMS_SQL. However, Native Dynamic SQL itself has certain limitations:

There is no support for so-called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs)

There is no support for SQL statements larger than 32K bytes

Also, there are some tasks that can only be performed using DBMS_SQL.

The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interface (OCI).

--------

事实上就是说二者功能一样的但是呢Native Dynamic SQL 有2个限制，当遇到这两个限制条件的时候，就只能使用包DBMS_SQL了。其中一个限制就是SQL语句的长度。而大多数情况下，出于易用性和性能表现的考虑，还是选择Native Dynamic SQL。而动态sql的雏形是Oracle Call Interface。

不仅长度有限制，事实上绑定变量的长度也是有限制的。

具体见参考：

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#BABEDAHF

讲的很详细很全面。

转载于:https://www.cnblogs.com/gracejiang/archive/2011/01/30/5890434.html