db2高手进。sqlserver自定义函数转db2自定义函数

0 0

db2高手进。sqlserver自定义函数转db2自定义函数3

各位db2高手看看。因为工作需要，没办法啊。我都研究好几天了，也没弄出来，本来sqlserver技术就不咋样，然后还让我换成db2的。伤心啊。
就这个函数。sqlserver的意思我都不太懂。还要我搞成db2的。
CREATE FUNCTION FN_GetOrdByString(@classId int,@OrdString varchar(100))
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @ParentId int
DECLARE @classIdA int
DECLARE @Value VARCHAR(100)

DECLARE currCur CURSOR FOR
--查当前项目的ID及父项目ID
SELECT ParentId,classId
FROM T_MARK_CLASS
WHERE classId=@classId

OPEN currCur
FETCH NEXT FROM currCurt
INTO @ParentId,@ClassIdA

if (@@FETCH_STATUS=0)
BEGIN
if (@ParentId IS NULL) --根项目,直接将其ID做为字串返回
BEGIN
SET @Value=@OrdString+'|'+LTRIM(RTRIM(str(@ClassIdA)))
END
else
BEGIN
--非根项目,递归找上一级项目ID所组成的字串
SET @Value=FN_GetOrdByString(@ParentId,@OrdString)+'|'+LTRIM(RTRIM(str(@ClassIdA)))

END
END
CLOSE currCur
DEALLOCATE currCur
RETURN @Value

END

小弟先谢谢各位好心人了。~！

这张表的表结构：
CREATE TABLE [dbo].[T_Mark_Class](
[ClassID] [int] IDENTITY(1,1) NOT NULL,
[ClassCode] [varchar](20) NOT NULL,
[ClassName] [nvarchar](500) NULL,
[TableID] [int] NOT NULL,
[MaxMark] [int] NOT NULL,
[MinMark] [int] NULL,
[Proportion] [int] NULL,
[ParentId] [int] NULL,
[MarkType] [char](1) NULL,
[MarkClass] [char](1) NULL,
[IDSort] [int] NULL,
[ResultType] [char](1) NULL,
[PassMark] [int] NULL,
[SecondaryQuestion] [bit] NULL,
[SecondaryMark] [int] NULL,
[ClassAllowMark] [bit] NULL,
[ClassMaxMark] [int] NULL,
[ClassMinMark] [int] NULL,
[ClassBaseMark] [int] NULL,
[MainQuestion] [bit] NULL,
[LevelID] [int] NULL,
[Notes] [nvarchar](1000) NULL,
[IsTreatMainQuestion] [bit] NULL,
[TreatMainQuestionFrom] [int] NULL,
[TreatMainQuestionTo] [int] NULL,
[ModifyId] [int] NOT NULL,
[ModifyDate] [datetime] NOT NULL,
[QuestionType] [int] NULL,
CONSTRAINT [PK__T_Mark_Class__02084FDA] PRIMARY KEY CLUSTERED
(
[ClassID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

大家有类似的例子也可以发来，让小弟参考参考。小弟不胜感激。