大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说oracle+function「建议收藏」,希望您对编程的造诣更进一步.
![oracle+function[数据库教程]](/www.yht7.com/upload/image/images/imgsql/25.jpg "oracle+function「建议收藏」插图")
create or replace function fn_assetode(i_type_code in varchar2,
i_src_COLM IN varchar2,
i_date in varchar2,
i_src_cd in varchar2)
return varchar2 as
/*定义变量*/
o_tar_code varchar2(40);
begin
if i_type_code=‘1‘ then
select fi_id into o_tar_code
from t1
where
(case when i_scr_cd in (‘NFC‘) THEN NFC_ASSET_ID
WHEN i_scr_cd IN (‘GLD‘) THEN GLD_ASSET_ID)=i_src_COLM
ELSIF (i_type_code=‘2‘ OR i_type_code=‘3‘) THEN
SELECT t2.fi_id into o_tar_code
from (select distinct new_windcode ,asharecode , row_number() over(partition by new_windcode order by times desc) rn from win_asset_id_mapping)t1
inner join t2
on t1.asharecode=t2.asharecode
where t1.asharecode is not null and t1.rn=1
and t1.new_windcode=i_src_COLM
elsif i_type_code=‘4‘ then
select fi_id into o_tar_code
from t1
where to_date(i_date,‘YYYYMMDDHH24MISS‘)>=txdate
i_src_COLM IN varchar2,
i_date in varchar2,
i_src_cd in varchar2)
return varchar2 as
/*定义变量*/
o_tar_code varchar2(40);
begin
if i_type_code=‘1‘ then
select fi_id into o_tar_code
from t1
where
(case when i_scr_cd in (‘NFC‘) THEN NFC_ASSET_ID
WHEN i_scr_cd IN (‘GLD‘) THEN GLD_ASSET_ID)=i_src_COLM
ELSIF (i_type_code=‘2‘ OR i_type_code=‘3‘) THEN
SELECT t2.fi_id into o_tar_code
from (select distinct new_windcode ,asharecode , row_number() over(partition by new_windcode order by times desc) rn from win_asset_id_mapping)t1
inner join t2
on t1.asharecode=t2.asharecode
where t1.asharecode is not null and t1.rn=1
and t1.new_windcode=i_src_COLM
elsif i_type_code=‘4‘ then
select fi_id into o_tar_code
from t1
where to_date(i_date,‘YYYYMMDDHH24MISS‘)>=txdate
else
o_tar_code=‘‘;
end if;
return o_tar_code;
end fn_assetode;
o_tar_code=‘‘;
end if;
return o_tar_code;
end fn_assetode;
oracle+function
原文地址：https://www.cnblogs.com/zhanglin123/p/13409288.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/6944.html
