如何将 Oracle 包转换为 SQL Server？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18107927/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to translate Oracle package to SQL Server?
提问by Jakob
In Oracle I have lots of stored procedures using a package which basically stores (encapsulates) and initializes all variables used by these procedures. There is one function in the package as well which takes care of initializing all it's package variables.
在 Oracle 中，我有很多使用包的存储过程，该包基本上存储（封装）并初始化这些过程使用的所有变量。包中还有一个函数负责初始化所有包变量。
My question is: how to port this to SQL Server?
我的问题是：如何将其移植到 SQL Server？
My first attempt is to declare all package variables and use them as OUTPUT parameters for a procedure to initialize them, but then I need to declare these variables over and over again in each procedure using them (and there are a lots of them in the package). Is there any better (and DRY) way to do this on SQL Server?
我的第一次尝试是声明所有包变量并将它们用作初始化它们的过程的 OUTPUT 参数，但随后我需要在每个使用它们的过程中一遍又一遍地声明这些变量（并且包中有很多变量） ）。在 SQL Server 上有没有更好（和 DRY）的方法来做到这一点？
Some code to explain it:
一些代码来解释它：
ORACLE:
甲骨文：
The package:
包裹：
create or replace
PACKAGE MYPARAMS AS
/**
param container
*/
type rc_params is record
(
var1 varchar2(30),
var2 integer
);
/**
init param container
use: v_params rc_pkp_plan_params := MYPARAMS.f_get_params(initvar)
*/
function f_get_params(initvar number) return rc_params;
END MYPARAMS;
/
The package body:
包体：
CREATE OR REPLACE
PACKAGE BODY MYPARAMS AS
function f_get_params(initvar number) return rc_params AS
retval rc_params;
BEGIN
retval.var1 := 'MY_VAR1';
retval.var2 := initvar;
return retval;
END f_get_params;
END MYPARAMS;
/
Some usage example:
一些使用示例：
declare
initvar integer := 22;
v_params MYPARAMS.rc_params := MYPARAMS.f_get_params(initvar);
begin
dbms_output.put_line(v_params.var1 || ' initialized by ' || v_params.var2);
end;
SQL Server:
SQL 服务器：
if exists (select * from sysobjects where id = object_id('f_get_params'))
drop procedure f_get_params
go
create procedure f_get_params(
@initvalue integer,
@var1 varchar(30) OUTPUT,
@var2 integer OUTPUT
)
as
set @var1 = 'MYVAR1'
set @var2 = @initvalue
go
-- this block i would like to avoid:
declare
@initvalue integer = 33,
@var1 varchar(30),
@var2 integer
exec f_get_params @initvalue, @var1 OUTPUT, @var2 OUTPUT
print @var1 + ' initialized by ' + convert(varchar(2), @var2)
Hope the description is clear enough...
希望描述足够清楚...
采纳答案by vr8ce
Unfortunately, T-SQL doesn't have anything like Oracle's packages, package variables, or structures of variables. Oh that it did.
不幸的是，T-SQL 没有像 Oracle 的包、包变量或变量结构那样的东西。哦，它做到了。
What you've done is probably the easiest way to accomplish it in T-SQL, even if it does require duplicating the variables.
您所做的可能是在 T-SQL 中完成它的最简单方法，即使它确实需要复制变量。
You can use a # table, i.e. create a #params table in f_get_parms that contains all of the variables, then use that # table in all of the other procs to retrieve them. The downside is that you still either have to declare the variables in the calling procedures, or use DML to access the columns in the # table, which is a lot more cumbersome than having them as variables.
您可以使用 # 表，即在 f_get_parms 中创建一个包含所有变量的 #params 表，然后在所有其他过程中使用该 # 表来检索它们。缺点是您仍然必须在调用过程中声明变量，或者使用 DML 访问 # 表中的列，这比将它们作为变量要麻烦得多。
Another way I've used before is to use XML to pass multiple variables around but treat them as a single one. It's still more cumbersome to access the values than in variables, but it has the advantage of allowing you to use a function instead of a procedure to get the values.
我以前使用的另一种方法是使用 XML 来传递多个变量，但将它们视为一个变量。访问值仍然比访问变量更麻烦，但它的优点是允许您使用函数而不是过程来获取值。
CREATE FUNCTION dbo.uf_get_params (
@initvar int
)
RETURNS xml
AS
BEGIN
DECLARE @xml xml,
@var1 varchar(30) = 'MYVAR' -- setting value on DECLARE requires SQL2008+
SELECT @xml = (SELECT @var1 AS var1,
@initvar AS initvar
FOR XML RAW('params'))
RETURN @xml
END
go
In your calling procedure, you would have
在你的调用过程中，你会有
DECLARE @params xml = (SELECT dbo.uf_get_parms(12))
to get the parameters, then use standard XML/XQUERY functions to retrieve the individual variables (attributes) from the @params XML variable.
获取参数，然后使用标准 XML/XQUERY 函数从 @params XML 变量中检索各个变量（属性）。
