Table 数据类型能否作为存储过程参数? 数据库移植又一问题

[mahongxi]

大家好,在大家的帮助下小弟的数据库移植工作在三月份进展顺利,已经完成了2个工具的移植(Oracle->SQLSERVER).
现在又遇到了一点问题,是关于在客户端调用服务器的过程时传递一个数组（或叫Table总之是一些普通数据类型的集合）参数
在ORACLE中有一类参数是这样定义的:
type TTable is table of number index by binary_integer;

使用时
procedure TTT(ReceiverID in TTable) is
begin
if ReceiverID.count > 0 then
for I in ReceiverID.FIRST .. ReceiverID.LAST loop
if ReceiverID(I) = 4 then
//具体业务.。。。。。。
end if;
end loop;
end if;
end;

在移植到SQLSERVER过程中,工具自动帮我把参数ReceiverID 转成了 Table(A INT) 这个数据类型,但编译却通过不了,我知道可以在函数返回值中用TABLE类型,但是不是SQLSERVER中不允许TABLE类型作为输入输出参数呢?
如果可以,请问在客户端用ADO如何使用?
如果不可以,有没有什么其它方式实现类似功能?我想到一招就是传一个逗号分开的字符串"12,34,45,67,78.."
然后在数据库中提供一些操作这些字串的函数,比如一个把这种字串转成TABLE的函数啊,然后再使用这个TABLE,但这又有一个问题,可能多次用到这个TABLE,如果保存这个函数返回的TABLE结果(好像不能用别一个@TABLE=FUNCTION...吧,也许临时表可以)。
各位费心了.
另外,小弟在近期工作中对ORACLE程序(DOA数据访问)……>SQLSERVER移植积累了一点心得,愿与各位讨论.

[zjcxc]

"xml的方法也不错,不过字符串最大就8000,XML本身需要的就占了大部分.."

这个观点是错误的,你可以定义存储过程的参数为text或者ntext类型,这样你可以传递最大为2G的xml数据

[real_name]

学习

[talantlee]

mark +up

[xluzhong]

学习!

[子陌红尘]

在存储过程中应该是无法使用表变量作为输出参数的，因为一旦存储过程结束，表变量也随之消失。

[zjcxc]

存储过程参数无法使用表变量

[zjcxc]

证据如下下(摘自sql联机帮助):


@parameter

过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值，或者该值设置为等于另一个参数）。存储过程最多可以有 2.100 个参数。

使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。

data_type

参数的数据类型。除 table 之外的其他所有数据类型均可以用作存储过程的参数。但是，cursor 数据类型只能用于 OUTPUT 参数。如果指定 cursor 数据类型，则还必须指定 VARYING 和 OUTPUT 关键字。

[zjcxc]

可以考虑把数据用字符串传递"12,34,45,67,78.."

然后将字符串分拆为表变量(直接在存储过程中进行吧),这样就可以多次使用了.

如果要用函数实现字符串分拆,也是可以的,如果分拆结果要多次使用,则将函数分拆结果保存为表变量
@TABLE=FUNCTION不可以,但 isnert @TABLE select * from FUNCTION是可以的

如果要分拆的数据长的话,用临时表比表变量好,临时表可以加索引之类,可以提高处理效率.

[zjcxc]

--字符串分拆参考:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

/*--字符串分函数

分拆指定分隔符的的字符串,返回结果表

--邹建 2004.04(引用请保留此信息)--*/

/*--调用示例

SELECT * FROM f_splitSTR('aa ba b','a ')
--*/
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO

[mahongxi]

zjcxc(邹建) 老大:
我真是太爱你了! 你是网民的大福星
痛哭流涕中......

[Yang_]

我想“传一个逗号分开的字符串"12,34,45,67,78.."”是可以的，在存储过程里的处理可以有以下思路：
1、函数，可以输出表变量但是，正如你自己所说，不能多次使用，多次使用要么多次调用函数、要么倒入表变量或者临时表，都不是很方便，而且函数效率也很成问题。
2、直接处理到临时表或者表变量，也许这是效率最高的方法。
3、动态sql语句，比如传入数据是'12,34,45'，可以用动态sql语句实现
select * from tablename where code in (12,34,56)
这个方法很不通用，也可能效率很低

[brothersun0116]

学习啊……

[yonghengdizhen]

用全局临时表应该是一个不错的选择,在外部创建表,传参时把临时表名传过去就OK了.不过由于是全局临时表,外部过程要想办法保证名称的唯一性

[yonghengdizhen]

我不知道局部临时表是否能在存储过程中引用

[7271747]

你试试
declare @tablename sysname
这个参数应该可以

[mahongxi]

To ：Yang_(扬帆破浪):
嗯,也许临时表是不错的方案
To 7271747
谢谢.我会试下的.

[mahongxi]

"这个观点是错误的,你可以定义存储过程的参数为text或者ntext类型,这样你可以传递最大为2G的xml数据"
谢谢zjcxc(邹建) 大哥帮助,正为传入大字符串的事儿发愁呢. 那在ADO中要用
ftMemo类型吧,赋值时是不是和ftString一样呢?
另外,在效率上XML是会差一些吧?我先用逗点分隔的办法,以后有需要再改为XML

[zjcxc]

赋值是一样的.

如果数据不多,就没有必要用xml了,毕竟还要在程序中去组织生成xml结构.

[mahongxi]

因为写的是通用组件，不能对值的多少限制，所以今天采用了ntext传入xml的方式。
ntext处理字符串分隔没搞定 :-(

[zjcxc]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitText]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitText]
GO

/*--分拆ntext数据

将ntext数据按逗号(,)分拆为表

--邹建 2005.04(引用请保留此信息)--*/

/*--调用示例

select * from f_splitText(N'TCL328800,TCL328801,TCL328802')
--*/
create function f_splitText(@text ntext)
returns @re table(col nvarchar(1000))
as
begin
declare @i int,@j int
select @i=1,
@j=datalength(@text)/2,
@j=@j-patindex('%,%',reverse(substring(@text,@j-4000,4000)))
while @i>0 and @i<@j
begin
insert @re select substring(@text,@i,patindex('%,%',substring(@text,@i,4000))-1)
select @i=@i+patindex('%,%',substring(@text,@i,4000))
end
insert @re select substring(@text,@i,4000)
return
end
go