为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:8
【操作系统】:win 10
【CPU】:英特尔
【问题描述】*:SqlServer存储过程中标量值函数的写法
原sql:
CREATE PROCEDURE [futian_user].[PROC_FP_GetFPINFO]
@SJH VARCHAR(15),
@TJBH VARCHAR(10),
@TJCS INT,
@FPHM_I VARCHAR(20),
@DYLX VARCHAR(1)
AS
BEGIN
declare @FPMC varchar(100)
SELECT @FPMC= isnull(MC,'') from MZ_XTCS where zdm ='BS_ZCMC_LYL'
declare @JKR varchar(20)
SELECT @JKR= XM from TJ_TJDJB where TJBH=@TJBH AND TJCS=@TJCS
declare @KPRQ datetime;
select @KPRQ = GETDATE();
declare @QTXX varchar(500)
declare @SSJE decimal(18,4)
declare @SKR varchar(20)
declare @SSJE_DX varchar(200)
select @SSJE=CONVERT(decimal(18,2),A.JE),@QTXX=A.FPQTXX,@SKR=ISNULL(B.XM,A.CZY) from TJ_SJH_FP A
LEFT JOIN GZRY B ON a.CZY = b.GKHM
where A.FPXH=@FPHM_I
create table #FPINTO (
FPMC varchar(100),
PJDM varchar(100),
PJHM varchar(100),
SHXYDM varchar(100),
JYM varchar(100),
JKR varchar(100),
KPRQ datetime,
JEHJ_DX varchar(100),
JEHJ_XX decimal(18,4),
QTXX varchar(100),
SKDW varchar(100),
FHR varchar(100),
SKR varchar(100)
)
select @SSJE_DX = [futian_user].f_num_chn
insert into #FPINTO values (@FPMC,'',@FPHM_I,'','',@JKR,@KPRQ,@SSJE_DX,@SSJE,@QTXX,@FPMC,@SKR,@SKR)
select *from #FPINTO
drop table #FPINTO
END
转换后的sql:
CREATE PROCEDURE "futian_user"."PROC_FP_GETFPINFO"
/DMDTS CONVERT
CREATE PROCEDURE [futian_user].[PROC_FP_GetFPINFO]/
(
DM_SJH VARCHAR (15),
DM_TJBH VARCHAR (10),
DM_TJCS INT ,
DM_FPHM_I VARCHAR (20),
DM_DYLX VARCHAR (1)
)
AS
DM_FPMC varchar (100);
DM_JKR varchar (20);
DM_KPRQ TIMESTAMP ;
DM_QTXX varchar (500);
DM_SSJE decimal (18,4);
DM_SKR varchar (20);
DM_SSJE_DX varchar (200);
BEGIN
/DMDTS CONVERT
DM_FPMC varchar (100)/
SELECT isnull(MC,'') INTO DM_FPMC from MZ_XTCS where ZDM ='BS_ZCMC_LYL';
/DMDTS CONVERT
DM_JKR varchar (20)/
SELECT XM INTO DM_JKR from TJ_TJDJB where TJBH=DM_TJBH AND TJCS=DM_TJCS;
/*DMDTS CONVERT
DM_KPRQ TIMESTAMP */
select GETDATE() INTO DM_KPRQ ;
/DMDTS CONVERT
DM_QTXX varchar (500)/
/DMDTS CONVERT
DM_SSJE decimal (18,4)/
/DMDTS CONVERT
DM_SKR varchar (20)/
/DMDTS CONVERT
DM_SSJE_DX varchar (200)/
select CONVERT( decimal(18,2),A.JE),A.FPQTXX,ISNULL(B.XM,A.CZY) INTO DM_SSJE,DM_QTXX,DM_SKR from TJ_SJH_FP A
LEFT JOIN GZRY B ON A.CZY = B.GKHM
where A.FPXH=DM_FPHM_I; EXECUTE IMMEDIATE'
create table #FPINTO (
FPMC varchar (100),
PJDM varchar (100),
PJHM varchar (100),
SHXYDM varchar (100),
JYM varchar (100),
JKR varchar (100),
KPRQ TIMESTAMP ,
JEHJ_DX varchar (100),
JEHJ_XX decimal (18,4),
QTXX varchar (100),
SKDW varchar (100),
FHR varchar (100),
SKR varchar (100)
)';
select ""."F_NUM_CHN"(DM_SSJE) INTO DM_SSJE_DX ;
insert into #FPINTO values (DM_FPMC,'',DM_FPHM_I,'','',DM_JKR,DM_KPRQ,DM_SSJE_DX,DM_SSJE,DM_QTXX,DM_FPMC,DM_SKR,DM_SKR);
select *from #FPINTO; EXECUTE IMMEDIATE'
drop table #FPINTO';
END;
这一行应该如何转换“select ""."F_NUM_CHN"(DM_SSJE) INTO DM_SSJE_DX ; ”
F_NUM_CHN 是一个标量值函数
求解
CREATE OR REPLACE FUNCTION "F_NUM_CHN"(num NUMBER)
RETURN VARCHAR
AS
BEGIN
-- 自定义逻辑(示例:简单数字转中文)
RETURN CASE num
WHEN 1 THEN '壹'
WHEN 2 THEN '贰'
ELSE '其他'
END;
END;
DECLARE
DM_SSJE_DX VARCHAR(100);
BEGIN
DM_SSJE_DX := "F_NUM_CHN"(2);
PRINT DM_SSJE_DX; -- 输出 '贰'
END;