从SQL Server链接服务器调用带输入参数的Oracle函数实现方法

通用调用语法（针对调用包XYZ下函数ABC、传入参数K的需求） #

已配置好链接服务器的场景下，优先用EXECUTE (...) AT 链接服务器名的方式调用，语法如下：

-- 1. 先定义SQL Server侧的变量
DECLARE @FuncResult 对应Oracle函数返回值的SQL Server类型; -- 用来接收ABC函数的返回值
DECLARE @K 对应参数K的SQL Server类型 = '你的参数K的实际值'; -- 要传入的参数K

-- 2. 调用Oracle侧的包函数
EXECUTE ('
DECLARE
  v_oracle_ret 对应Oracle侧的返回值类型;
BEGIN
  -- 调用XYZ包的ABC函数，? 是外部参数的占位符，按顺序对应后面传的@K
  v_oracle_ret := XYZ.ABC(?);
  -- 把返回值赋值给输出占位符，对应后面的@FuncResult OUTPUT
  ? := v_oracle_ret;
  -- 如果有事务需求可以加COMMIT;
END;', @K, @FuncResult OUTPUT) AT 你的Oracle链接服务器名称;

参数传递规则 #

• 传入参数直接按顺序写在EXECUTE的参数列表里，PL/SQL块中用?按顺序匹配即可
• 如果是字符串类型的参数，PL/SQL块里写死的字符串要加两个单引号转义，不需要转义外部传入的参数值
• 输出参数要在变量后加OUTPUT标识，和PL/SQL块中用来返回值的?占位符顺序对应

现有存储过程的修正 #

你提供的代码存在2个语法问题：PL/SQL块中不识别SQL Server的变量名@Adress，要用?占位，且赋值语句末尾缺分号，修正后的完整代码如下：

ALTER PROCEDURE [dbo].[CreateProject] (@ProjectBudgetId INT)
AS
BEGIN
 SET NOCOUNT ON

DECLARE @Value varchar(1000)
DECLARE @Adress varchar(1000) = 'https://dev.local/front/form/39449d5c-705a-487d-a115-db50910fe200/1'


EXECUTE 
('
DECLARE 
v_numer VARCHAR2(200);
v_id NUMBER;
p_link_EOD VARCHAR2(2000);

BEGIN
v_numer := TETA_ADMIN.API_NAX_PROJEKTY_KLN.wyznacz_nr_projektu(''Projekt_test'', SYSDATE);

-- 外部传入的@Adress用?占位，末尾加分号
p_link_EOD := ?;

v_id :=  TETA_ADMIN.API_NAX_PROJEKTY_KLN.dodaj_projekt (
''Projekt_test'',
v_numer,
''Oppo'',
''Numr zewnetrzny, np. POWER/64548763 itd'',
To_Date(''01012021'',''ddmmyyyy''), 
To_Date(''31122021'',''ddmmyyyy''),
''PLN'',
10254000,
NULL,
''Testowy typ projektu'',
''Testowy rodzaj projektu'',
''Opis słowno-muzyczny, czyli co w projekcie będzie robione'',
NULL,
p_link_EOD);

COMMIT;
? := v_id;

END;', @Adress, @Value OUTPUT) AT TETAATH


UPDATE [bud].[ProjectBudget]
SET [BudgetTETAId] = @Value
WHERE Id = @ProjectBudgetId

END

内容的提问来源于stack exchange，提问作者mrub