使用 SQL Server 的输出参数调用 Oracle 存储过程
我在 SQL Server 2008 R2 中有一个 Oracle 链接服务器.我需要执行 Oracle 存储过程(第一个是输出参数，第二个过程是输入参数):
I have an Oracle linked server in SQL Server 2008 R2. I need to execute Oracle stored procedures (with output parameter in first, and input parameter in second procedure):
CREATE OR REPLACE PROCEDURE my1.spGetDate(CurrentDate OUT VARCHAR2)
IS
BEGIN
-- set output parameter, no select statements
END;
CREATE OR REPLACE PROCEDURE my1.spDeleteOldRecords(CurrentDate IN VARCHAR2)
IS
BEGIN
-- conditional delete from oracle table, no select statements
END;
我没有找到关于这个问题的完整文档，只有带有无参数选择/非选择过程的简单示例，想知道如何调用这些过程、内部带有选择的过程、具有基本参数类型的多参数过程.
I didn't found any complete documentation on this question, only simple examples with parameterless select/nonselect procedures, and want to know, how to call these procedures, procedures with select inside, multiparameter procedures with basic parameter types.
推荐答案
它应该是这样工作的:
DECLARE @dateval DATETIME
EXECUTE ('begin my1.spGetDate(?); end;', @dateval OUTPUT) AT ORA_DBLINK_NAME;
EXECUTE ('begin my1.spDeleteOldRecords(?); end;', @dateval) AT ORA_DBLINK_NAME;
如果您有多个参数，它可能如下所示:
If you have several parameters, it could look like this:
EXECUTE ('begin my1.spProc(?,?,?,?); end;', @param_in_1, @param_in_2, @param_out_3 OUTPUT, @param_out_4 OUTPUT) AT DBLINK_NAME;
相关文章