为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:windows
【CPU】:Intel
【问题描述】*:SQLSERVER标量值函数怎么转达梦函数
USE [WCQCG_New]
GO
/****** Object: UserDefinedFunction [dbo].[GetMoneySource] Script Date: 11/08/2023 17:35:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--drop function GetMoneySource;
ALTER function [dbo].[GetMoneySource](@RequestID varchar(50))
returns varchar(100)
as
begin
declare @Explain varchar(100)
DECLARE @temp TABLE
(
ID varchar(50),
BudgetIn decimal(18,2),
BudgetOut decimal(18,2),
RaiseIndependently decimal(18,2)
);
-- 将源表中的数据插入到表变量中
INSERT INTO @temp(ID,BudgetIn, BudgetOut, RaiseIndependently)
SELECT ID,BudgetIn,BudgetOut,RaiseIndependently FROM CG_RequestItem where FormID=@RequestID;
-- 声明变量
DECLARE
@ID varchar(50),
@BudgetIn decimal(18,2),
@BudgetOut decimal(18,2),
@RaiseIndependently decimal(18,2);
set @Explain=''
WHILE EXISTS(SELECT ID FROM @temp)
BEGIN
SELECT top 1 @ID=ID, @BudgetIn=BudgetIn,@BudgetOut=BudgetOut,@RaiseIndependently=RaiseIndependently FROM @temp;
DELETE FROM @temp WHERE ID=@ID;
if @BudgetIn>0 and @Explain not like '%预算内%'
begin
set @Explain= @Explain+' 预算内'
end
if @BudgetOut>0 and @Explain not like '%预算外%'
begin
set @Explain=@Explain+' 预算外'
end
if @RaiseIndependently>0 and @Explain not like '%单位自筹%'
begin
set @Explain=@Explain+' 单位自筹'
end
END
return @Explain
END
CREATE OR REPLACE FUNCTION GetMoneySource(RequestID IN VARCHAR2) RETURN VARCHAR2 IS Explain VARCHAR2(100); CURSOR c_temp IS SELECT ID, BudgetIn, BudgetOut, RaiseIndependently FROM CG_RequestItem WHERE FormID = RequestID; temp_row c_temp%ROWTYPE; BEGIN Explain := ''; OPEN c_temp; LOOP FETCH c_temp INTO temp_row; EXIT WHEN c_temp%NOTFOUND; IF temp_row.BudgetIn > 0 AND INSTR(Explain, '预算内') = 0 THEN Explain := Explain || '预算内'; END IF; IF temp_row.BudgetOut > 0 AND INSTR(Explain, '预算外') = 0 THEN Explain := Explain || '预算外'; END IF; IF temp_row.RaiseIndependently > 0 AND INSTR(Explain, '单位自筹') = 0 THEN Explain := Explain || '单位自筹'; END IF; END LOOP; CLOSE c_temp; RETURN Explain; END GetMoneySource; /