函数传入代码值输出代码说明

最新推荐文章于 2022-10-30 22:09:59 发布

ThisSin

最新推荐文章于 2022-10-30 22:09:59 发布

阅读量172

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/qq_28198893/article/details/89849620

版权

sql 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

CREATE OR REPLACE FUNCTION FUN_IS_CKC055_CHANGE(PRM_CKC055 IN VARCHAR2) RETURN VARCHAR2 IS
V_ckc055_explain VARCHAR2(200) := ‘0’; --ckc055字段说明
v_element varchar2(200);
v_tmp varchar2(200);
v_aaa103 varchar2(200);

BEGIN
v_tmp := PRM_CKC055;
IF v_tmp IS NULL OR v_tmp =’’ THEN
return V_ckc055_explain;
ELSE
while instr(v_tmp, ‘,’) > 0 LOOP
v_element := substr(v_tmp, 1, instr(v_tmp, ‘,’) - 1);
v_tmp := substr(v_tmp,
instr(v_tmp, ‘,’) + length(’,’),
length(v_tmp));
begin
select aaa103 into v_aaa103 from aa10 where aaa100 = ‘CKC055’ AND AAA102 = v_element;
exception
WHEN OTHERS THEN
v_aaa103 := ‘0’;
end;

if v_aaa103 != ‘0’ then
if V_ckc055_explain = ‘0’ then
V_ckc055_explain := v_aaa103;
else
V_ckc055_explain := V_ckc055_explain||’,’||v_aaa103;
end if;

end if;
end loop;

—最后一个没有逗号分割
if v_tmp is not null then
select aaa103 into v_aaa103 from aa10 where aaa100 = ‘CKC055’ AND AAA102 = v_tmp;
if V_ckc055_explain = ‘0’ then
V_ckc055_explain := v_aaa103;
else
V_ckc055_explain := V_ckc055_explain||’,’||v_aaa103;
end if;
end if;
END IF;

RETURN V_ckc055_explain;
END FUN_IS_CKC055_CHANGE;