PL/SQL里 execute immediate的用法 谁给解释下

execute immediate什么用处~

在ORACLE的PL/SQL里：
EXECUTE IMMEDIATE 代替了以前Oracle8i中DBMS_SQL package包.
它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码.尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。


-- 使用技巧


1. EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交
如果通过EXECUTE IMMEDIATE处理DML命令，
那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分.
如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据


2. 不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors.


3. 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号.


4. 在Oracle手册中，未详细覆盖这些功能。
下面的例子展示了所有用到Execute immediate的可能方面.希望能给你带来方便.


5. 对于Forms开发者,当在PL/SQL 8.0.6.3.版本中，Forms 6i不能使用此功能.


EXECUTE IMMEDIATE -- 用法例子


1. 在PL/SQL运行DDL语句


begin
execute immediate 'set role all';
end;


2. 给动态语句传值(USING 子句)


declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;


3. 从动态语句检索值(INTO子句)


declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;


4. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.
黓认为IN类型,其它类型必须显式指定


declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;

if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;


5. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量


declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;


6. 传递并检索值.INTO子句用在USING子句前


declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;


7. 多行查询选项.对此选项用insert语句填充临时表，
用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.

declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' ||
' select empno, ename from emp ' ||
' where sal > :1'
using l_sal;
commit;
end;


对于处理动态语句,EXECUTE IMMEDIATE 比以前可能用到的更容易并且更高效.
当意图执行动态语句时，适当地处理异常更加重要.应该关注于捕获所有可能的异常.

在PL/SQL中在执行SQL语句时可以直接写SQL或者可以把一个SQL语句拼成一个字符串，如下：
select * from dual;
v_sql:='select * from dual'; EXECUTE IMMEDIATE v_sql;

一般来说直接写SQL的性能是高于拼字符串的，因为如果执行拼字符串的需要内部自动调动oracle机制，先解析字符串映射成SQL语句然后再执行。

但是拼SQL的方式有好处。即SQL语句是一个字符串可以动态拼接，根据不同的条件来改变SQL语句，这是直接写SQL所不能达到的。
拼SQL还有个好处就是 v_sql:='select * from tables t where t.c_date=:1 and t.name=:2'; EXECUTE IMMEDIATE v_sql USING '20130304','xiaoming';
可以动态的对参数传递值，这是最大的优势。

在PL/SQL中定义一个变量 v_date varchar2,如果直接写SQL可以 直接写变量 v_date
如果是拼SQL则是 v_sql:='select * from tables t where t.c_date='''||v_date||''''; 这样在执行的时候打印出来的SQL就是 select * from tables t where t.c_date=’20130304‘'
如果是v_sql:='select * from tables t where t.c_date='||v_date||''; 这样在执行的时候打印出来的SQL就是 select * from tables t where t.c_date=20130304; 这里c_date如果是varchar2类型，则这里oracle要隐式的进行数字到字符串的转换 ，影响性能

execute 为SQL命令，意为执行存储过程，immediate 为执行的存储过程名称。
以下摘自MsSql帮助，别怪我复制哦，实在是帮助里描述的才最全面。

EXECUTE
执行标量值的用户定义函数、系统过程、用户定义存储过程或扩展存储过程。同时支持 Transact-SQL 批处理内的字符串的执行

若要唤醒调用函数，请使用 EXECUTE stored_procedure 中描述的语法。

语法
执行存储过程：

[ [ EXEC [ UTE ] ]
{
[ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var
}
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]
[ ,...n ]
[ WITH RECOMPILE ]

执行字符串：

EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )

参数
@return_status

是一个可选的整型变量，保存存储过程的返回状态。这个变量在用于 EXECUTE 语句前，必须在批处理、存储过程或函数中声明过。

在用于唤醒调用标量值用户定义函数时，@return_status 变量可以是任何标量数据类型。

procedure_name

是拟调用的存储过程的完全合法或者不完全合法的名称。过程名称必须符合标识符规则。有关更多信息，请参见使用标识符。无论服务器的代码页或排序方式如何，扩展存储过程的名称总是区分大小写。

用户可以执行在另一数据库中创建的过程，只要该用户拥有此过程或有在该数据库中执行它的适当的权限。用户可以在另一台运行 Microsoft® SQL Server™ 的服务器上执行过程，只要该用户有适当的权限使用该服务器（远程访问），并能在数据库中执行该过程。如果指定了服务器名称但没有指定数据库名称，SQL Server 会在用户默认的数据库中寻找该过程。

;number

是可选的整数，用于将相同名称的过程进行组合，使得它们可以用一句 DROP PROCEDURE 语句除去。该参数不能用于扩展存储过程。

在同一应用程序中使用的过程一般都以该方式组合。例如，在订购应用程序中使用的过程可以 orderproc;1、orderproc;2 等来命名。DROP PROCEDURE orderproc 语句将除去整个组。在对过程分组后，不能除去组中的单个过程。例如，DROP PROCEDURE orderproc;2 是不允许的。有关过程组的更多信息，请参见 CREATE PROCEDURE。

@procedure_name_var

是局部定义变量名，代表存储过程名称。

@parameter

是过程参数，在 CREATE PROCEDURE 语句中定义。参数名称前必须加上符号 (@)。在以 @parameter_name = value 格式使用时，参数名称和常量不一定按照 CREATE PROCEDURE 语句中定义的顺序出现。但是，如果有一个参数使用 @parameter_name = value 格式，则其它所有参数都必须使用这种格式。

默认情况下，参数可为空。如果传递 NULL 参数值，且该参数用于 CREATE 或 ALTER TABLE 语句中不允许为 NULL 的列（例如，插入至不允许为 NULL 的列），SQL Server 就会报错。为避免将 NULL 参数值传递给不允许为 NULL 的列，可以在过程中添加程序设计逻辑或采用默认值（使用 CREATE 或 ALTER TABLE 语句中的 DEFAULT 关键字）。

value

是过程中参数的值。如果参数名称没有指定，参数值必须以 CREATE PROCEDURE 语句中定义的顺序给出。

如果参数值是一个对象名称、字符串或通过数据库名称或所有者名称进行限制，则整个名称必须用单引号括起来。如果参数值是一个关键字，则该关键字必须用双引号括起来。

如果在 CREATE PROCEDURE 语句中定义了默认值，用户执行该过程时可以不必指定参数。如果该过程使用了带 LIKE 关键字的参数名称，则默认值必须是常量，并且可以包含 %、_、[ ] 及 [^] 通配符。

默认值也可以为 NULL。通常，过程定义会指定当参数值为 NULL 时应该执行的操作。

@variable

是用来保存参数或者返回参数的变量。

OUTPUT

指定存储过程必须返回一个参数。该存储过程的匹配参数也必须由关键字 OUTPUT 创建。使用游标变量作参数时使用该关键字。

如果使用 OUTPUT 参数，目的是在调用批处理或过程的其它语句中使用其返回值，则参数值必须作为变量传递（即 @parameter = @variable）。如果一个参数在 CREATE PROCEDURE 语句中不是定义为 OUTPUT 参数，则对该参数指定 OUTPUT 的过程不能执行。不能使用 OUTPUT 将常量传递给存储过程；返回参数需要变量名称。在执行过程之前，必须声明变量的数据类型并赋值。返回参数可以是 text 或 image 数据类型以外的任意数据类型。

DEFAULT

根据过程的定义，提供参数的默认值。当过程需要的参数值没有事先定义好的默认值，或缺少参数，或指定了 DEFAULT 关键字，就会出错。

n

是占位符，表示在它前面的项目可以多次重复执行。例如，EXECUTE 语句可以指定一个或者多个 @parameter、value 或 @variable。

WITH RECOMPILE

强制编译新的计划。如果所提供的参数为非典型参数或者数据有很大的改变，使用该选项。在以后的程序执行中使用更改过的计划。该选项不能用于扩展存储过程。建议尽量少使用该选项，因为它消耗较多系统资源。

@string_variable

是局部变量的名称。@string_variable 可以是 char、varchar、nchar 或 nvarchar 数据类型，最大值为服务器的可用内存。如果字符串长度超过 4,000 个字符，则把多个局部变量串联起来用于 EXECUTE 字符串。有关系统提供的 SQL Server 数据类型更多的信息，请参见数据类型。

[N]'tsql_string'

是一个常量，tsql_string 可以是 nvarchar 或 varchar 数据类型。如果包含 N，则该字符串将解释为 nvarchar 数据类型，最大值为服务器的可用内存。如果字符串长度超过 4,000 个字符，则把多个局部变量串联起来用于 EXECUTE 字符串。

注释
如果过程名称的前三个字符为 sp_，SQL Server 会在 Master 数据库中寻找该过程。如果没能找到合法的过程名称，SQL Server 会寻找所有者名称为 dbo 的过程。若要将存储过程名称解析为与系统存储过程同名的用户定义存储过程，请提供一个完全合法的过程名称。

参数可以通过利用 value 或 @parameter_name = value 来提供。参数不是事务的一个部分；因而如果事务中的参数值更改，且该事务在以后回滚，该参数值不会退回到以前的值。返回给调用方的值总是过程返回时的值。

当一个存储过程调用另一个存储过程时，会产生嵌套。当调用的过程开始执行时，嵌套级会增加，当调用过程执行结束时，嵌套级则会减少。嵌套级最高为32级，超过32级时，会导致整个调用过程链失败。当前的嵌套级存储在 @@NESTLEVEL 函数中。

SQL Server 目前使用返回值 0 到 -14 来表示存储过程的执行状态。值 –15 到 -99 留作后用。有关保留的返回状态值的列表的更多信息，请参见 RETURN。

因为远程存储过程和扩展存储过程不在事务的作用域中（除非在 BEGIN DISTRIBUTED TRANSACTION 语句中发出或者是和不同的配置选项一起使用），所以通过调用执行的命令不能回滚。有关更多信息，请参见系统存储过程和 BEGIN DISTRIBUTED TRANSACTION。

当使用游标变量时，如果执行的过程传递一个分配有游标的游标变量，就会出错。

在执行存储过程时，如果语句是批处理中的第一个语句，则不一定要指定 EXECUTE 关键字。

使用带字符串的 EXECUTE 命令
使用字符串串联运算符 (+) 为动态执行创建长字符串。每个字符串表达式可以是 Unicode 与 non-Unicode 数据类型的混合。

尽管每个 [N] 'tsql_string' 或 @string_variable 不得超过 8,000 个字节，SQL Server 语法分析器中对这种串联只进行逻辑处理而不占用物理内存。例如，该语句决不会生成长 16,000 个串联起来的字符串：

EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')

在 EXECUTE 语句执行前，不会编译 EXECUTE 语句内的语句。

数据库环境的更改只在 EXECUTE 语句结束前有效。例如，在这个例子的 EXEC 后，数据库环境是 master：

USE master EXEC ("USE pubs") SELECT * FROM authors

权限
存储过程的 EXECUTE 权限默认给该存储过程的所有者，该所有者可以将此权限转让给其他用户。当遇到 EXECUTE 语句时，即使 EXECUTE 语句是在存储过程中，也会检查在 EXECUTE 字符串内使用该语句的权限。当运行一个执行字符串的存储过程时，系统会在执行该过程的用户环境中，而不是在创建该过程的用户环境中检查权限。但是，如果某用户拥有两个存储过程，并且第一个过程调用第二个过程，则在第二个过程中不进行 EXECUTE 权限检查。

示例
A. 使用 EXECUTE 传递单个参数
showind 存储过程需要参数 (@tabname)，它是一个表的名称。下面这个例子执行 showind 存储过程，以 titles 为参数值。

说明 showind 存储过程只是用来作为一个例子，pubs 数据库并没有此过程。

EXEC showind titles

在执行过程中变量可以显式命名：

EXEC showind @tabname = titles

如果这是 isql 脚本或批处理中第一个语句，则 EXEC 语句可以省略：

showind titles

-或-

showind @tabname = titles

B. 使用多个参数与一个输出参数
这个例子执行 roy_check 存储过程，传递三个参数。第三个参数 @pc 是输出参数。过程执行完后，返回变量可以从变量 @percent 得到。

说明 roy_check 存储过程只是用作举例，pubs 数据库中并没有此过程。

DECLARE @percent int
EXECUTE roy_check 'BU1032', 1050, @pc = @percent OUTPUT
SET Percent = @percent

C.使用带一个变量的 EXECUTE 'tsql_string' 语句
这个例子显示 EXECUTE 语句如何处理动态生成的、含有变量的字符串。这个例子创建 tables_cursor 游标来保存所有用户定义表 (type = U) 的列表。

说明 此例子只用作举例。

DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
/* A @@FETCH_STATUS of -2 means that the row has been deleted.
There is no need to test for this because this loop drops all
user-defined tables. */.
EXEC ('DROP TABLE ' + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
PRINT 'All user-defined tables have been dropped from the database.'
DEALLOCATE tables_cursor

D.使用带远程存储过程的 EXECUTE 语句
这个例子在远程服务器 SQLSERVER1 上执行 checkcontract 存储过程，在 @retstat 中保存返回状态，说明运行成功或失败。

DECLARE @retstat int
EXECUTE @retstat = SQLSERVER1.pubs.dbo.checkcontract '409-56-4008'

E. 使用带扩展存储过程的 EXECUTE 语句
下例使用 xp_cmdshell 扩展存储过程列出文件扩展名为 .exe 的所有文件的目录。

USE master
EXECUTE xp_cmdshell 'dir *.exe'

F. 使用带一个存储过程变量的 EXECUTE 语句
这个例子创建一个代表存储过程名称的变量。

DECLARE @proc_name varchar(30)
SET @proc_name = 'sp_who'
EXEC @proc_name

G. 使用带 DEFAULT 的 EXECUTE 语句
这个例子创建了一个存储过程，过程中第一个和第三个参数为默认值。当运行该过程时，如果调用时没有传递值或者指定了默认值，这些默认值就会赋给第一个和第三个参数。注意 DEFAULT 关键字有多种使用方法。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc_calculate_taxes' AND type = 'P')
DROP PROCEDURE proc_calculate_taxes
GO
-- Create the stored procedure.
CREATE PROCEDURE proc_calculate_taxes (@p1 smallint = 42, @p2 char(1),
@p3 varchar(8) = 'CAR')
AS
SELECT *
FROM mytable

proc_calculate_taxes 存储过程可以以多种组合方式执行：

EXECUTE proc_calculate_taxes @p2 = 'A'
EXECUTE proc_calculate_taxes 69, 'B'
EXECUTE proc_calculate_taxes 69, 'C', 'House'
EXECUTE proc_calculate_taxes @p1 = DEFAULT, @p2 = 'D'
EXECUTE proc_calculate_taxes DEFAULT, @p3 = 'Local', @p2 = 'E'
EXECUTE proc_calculate_taxes 69, 'F', @p3 = DEFAULT
EXECUTE proc_calculate_taxes 95, 'G', DEFAULT
EXECUTE proc_calculate_taxes DEFAULT, 'H', DEFAULT
EXECUTE proc_calculate_taxes DEFAULT, 'I', @p3 = DEFAULT

在ORACLE的PL/SQL里：
EXECUTE IMMEDIATE 代替了以前Oracle8i中DBMS_SQL package包.
它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码.尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。

-- 使用技巧

1. EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交
如果通过EXECUTE IMMEDIATE处理DML命令，
那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分.
如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据

2. 不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors.

3. 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号.

4. 在Oracle手册中，未详细覆盖这些功能。
下面的例子展示了所有用到Execute immediate的可能方面.希望能给你带来方便.

5. 对于Forms开发者,当在PL/SQL 8.0.6.3.版本中，Forms 6i不能使用此功能.

EXECUTE IMMEDIATE -- 用法例子

1. 在PL/SQL运行DDL语句

begin
execute immediate 'set role all';
end;

2. 给动态语句传值(USING 子句)

declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;

3. 从动态语句检索值(INTO子句)

declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;

4. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.
黓认为IN类型,其它类型必须显式指定

declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;

if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;

5. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量

declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;

6. 传递并检索值.INTO子句用在USING子句前

declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;

7. 多行查询选项.对此选项用insert语句填充临时表，
用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.

declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' ||
' select empno, ename from emp ' ||
' where sal > :1'
using l_sal;
commit;
end;

对于处理动态语句,EXECUTE IMMEDIATE 比以前可能用到的更容易并且更高效.
当意图执行动态语句时，适当地处理异常更加重要.应该关注于捕获所有可能的异常.

比如：
begin
execute immediate 'create table test(id int)';
end;

动态sql的执行方法

---

十堰市17244609835： pl/sql exec的用法 - ？
召骨酚麻： 如果是函数的话,直接调用就好了啊,写在一个包里

十堰市17244609835： 怎样通过PL/SQL将EXEC表L导入到Oracle数据库 - ？
召骨酚麻： 用pl/sql ODBC Importer data from ODBC 选择excel file作为源点击Connect选择excel文件然后在Data to Oracle选择oracle的用户和表最后做数据字段的映射就可也来.

十堰市17244609835： plsql 中想创建个简单的存储过程,为什么执行exec的时候报错啊! - ？
召骨酚麻： PL/SQL SQL window窗口下不可以执行 存储过程的,你可以在command window 下执行,也可以找到该存储过程,右击 --> test,执行 存储过程

十堰市17244609835： PL/SQL里 execute immediate的用法 谁给解释下 - ？
召骨酚麻： 在ORACLE的PL/SQL里: EXECUTE IMMEDIATE 代替了以前Oracle8i中DBMS_SQL package包. 它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前,EXECUTE IMMEDIATE的目标在于减小企业...

十堰市17244609835： SQL中的EXEC是什么意思 - ？
召骨酚麻： 你去查一下sp_addextendedproperty这个存储过程的定义嘛.exec就是执行存储过程.

十堰市17244609835： sqlserver中如何在用户定义的函数里使用exec? - ？
召骨酚麻： 这里我举个例子,假如有一个函数dbo.NumAdd用来计算加法,那它可能是这样的:DROP FUNCTION dbo.NumAdd GO CREATE FUNCTION dbo.NumAdd( @A SMALLINT, @B SMALLINT) RETURNS INT AS BEGIN DECLARE @Ret INT SET ...

十堰市17244609835： sqlserver中exec和execute的区别 - ？
召骨酚麻： --一样的 exec 是 execute的简写 ,没什么区别,就像admin是administrator的简写一样

十堰市17244609835： sql server 中EXEC的用法 - ？
召骨酚麻： create procedure first_test_sp(@filter_name nvarchar(1000)) as exec ('declare @temp nvarchar(30) select @temp=left_code from item_code where '+ @filter_name) print @temp go --这样试试!

十堰市17244609835： SQL中exec的用法 - ？
召骨酚麻： 此句话应该这样:exec sp_getDjLsh 'DJH',@djlsh OUTPUTexec @djlsh=sp_GetDjLsh 'DJH',@djlsh 这有点多此一举了'DJH'表示输入参数

十堰市17244609835： PL/SQL中直接写SQL语句和用EXECUTE IMMEDIATE方法的区别 - ？
召骨酚麻： 在PL/SQL中在执行SQL语句时可以直接写SQL或者可以把一个SQL语句拼成一个字符串,如下:select * from dual; v_sql:='select * from dual'; EXECUTE IMMEDIATE v_sql; 一般来说直接写SQL的性能是高于拼字符串的,因为如果执行拼字符串...