从Microsoft SQL Server到Oracle动态语句执行转换

EXECUTE sp_executesql语句使用执行动态SQL语句与参数在Microsoft SQL Server。EXECUTE sp_executesql的第二字符串参数描述动态SQL语句参数和它们类型。

EXECUTE IMMEDIATE语句使用执动态SQL语句与参数在Oracle。输入参数标记为 :N，N 是参数的数在USING子句。

SQLWays转换Microsoft SQL Server EXECUTE sp_executesql语句到Oracle EXECUTE IMMEDIATE。SQLWays变化输入参数在MSQL动态语句到造当的Oracle语法。SQLWays替换参数的名称在Microsoft SQL Server动态字符串大盘参数的数，当转换到Oracle。

表格 42。从Microsoft SQL Server到Oracle动态语句执行转换
Microsoft SQL Server	Oracle
create procedure sql_sp_executesql as declare @param int execute sp_executesql N'select * from tab1 where col1 = @param', N'@param int', @param = 35	CREATE OR REPLACE PROCEDURE sql_sp_executesql AS v_param NUMBER(10,0); BEGIN EXECUTE IMMEDIATE 'select * from tab1 where col1 = :1' USING 35; end;
create procedure sql_sp_executesql2 as declare @InsOrderID int declare @InsertString varchar(50) SET @InsertString = N'INSERT INTO tab1' + ' VALUES (@InsOrderID)' EXEC sp_executesql @InsertString, N'@InsOrderID INT', @InsOrderID	CREATE OR REPLACE PROCEDURE sql_sp_executesql2 AS v_InsOrderID NUMBER(10,0); v_InsertString VARCHAR2(50); BEGIN v_InsertString := 'INSERT INTO tab1' \|\| ' VALUES (:1)'; EXECUTE IMMEDIATE v_InsertString USING v_InsOrderID; END;
create procedure sql_sp_executesql3 as declare @val int declare @InsertString varchar(50) SET @InsertString = N'DELETE FROM tab1 WHERE col1 = @par1 and col2=@par2' EXEC sp_executesql @InsertString, N'@par1 INT, @par2 INT', @par1=1, @par2=@val	CREATE OR REPLACE PROCEDURE sql_sp_executesql3 AS v_val NUMBER(10,0); v_InsertString VARCHAR2(50); BEGIN v_InsertString := 'DELETE FROM tab1 WHERE col1 = :1 and col2=:2' ; EXECUTE IMMEDIATE v_InsertString USING 1,v_val; end;

表格 42。从Microsoft SQL Server到Oracle动态语句执行转换

Microsoft SQL Server

Oracle

create procedure sql_sp_executesql as
declare @param int
execute sp_executesql           N'select * from tab1 where 
col1 = @param',
N'@param int',
@param = 35

CREATE OR REPLACE PROCEDURE sql_sp_executesql AS
v_param NUMBER(10,0);
BEGIN
EXECUTE IMMEDIATE 'select * from tab1 where col1 = :1' 
USING 35;
end;

create procedure sql_sp_executesql2 as
declare @InsOrderID int
declare @InsertString varchar(50)
SET @InsertString = N'INSERT INTO tab1' +
' VALUES (@InsOrderID)'
EXEC sp_executesql @InsertString,
N'@InsOrderID INT',   @InsOrderID

CREATE OR REPLACE PROCEDURE sql_sp_executesql2 AS
v_InsOrderID NUMBER(10,0);
v_InsertString VARCHAR2(50);
BEGIN
v_InsertString := 'INSERT INTO tab1' ||
' VALUES (:1)';
EXECUTE IMMEDIATE v_InsertString USING v_InsOrderID;
END;

create procedure sql_sp_executesql3 as
declare @val int
declare @InsertString varchar(50) 
SET @InsertString = N'DELETE FROM tab1 WHERE 
col1 = @par1 and col2=@par2'
EXEC sp_executesql @InsertString,
N'@par1 INT, @par2 INT',   @par1=1, @par2=@val

CREATE OR REPLACE PROCEDURE sql_sp_executesql3 AS
v_val NUMBER(10,0);
v_InsertString VARCHAR2(50);
BEGIN
v_InsertString := 'DELETE FROM tab1 WHERE col1 = :1 and 
col2=:2' ;
EXECUTE IMMEDIATE v_InsertString
USING 1,v_val;
end;

Ispirer SQLWays数据库迁移软件

从Microsoft SQL Server到Oracle动态语句执行转换