SELECT a.aac001, b.aab003,a.aac002,a.aac004,
a.aac005,a.aac006,a.aac007,a.aac008,b.aab004,
select case when t_ac01.aic001 > 0 then Trunc(t_ac01.aic001 / 12, 0) || '年' || mod(t_ac01.aic001, 12) || '月' else '0' end as aic001,
建帐时间 //gf_getjzsj(ac01_aac001,'1')
核定缴费年限 // if(t_ac01_cac387 >0, Truncate(t_ac01_cac387 /12,0)+'年'+mod(t_ac01_cac387 ,12) ,'0')+'月'
养老保险参保状态 //gf_getcbzt(ac01_aac001,'1')
医疗保险参保状态 //gf_getcbzt(ac01_aac001,'3')
FROM ac01_ac02_touch //"T_AB01",
//"T_AC01"
WHERE 个人编号 //( "T_AC01"."AAB001" = "T_AB01"."AAB001" ) and
//( "T_AC01"."AAC001" = :as_grbh )
原系统实现方式:
//----- begin: gf_getjzsj(string grbh, string xz)
string ls_jzsjselect to_char(to_date(min(aae002),'yyyymm'),'yyyy-mm')
into :ls_jzsj
from ic01
where aac001=:grbh
and aae114='1';
if isnull(ls_jzsj) or ls_jzsj = '' then
select to_char(to_date(min(aae002),'yyyymm'),'yyyy-mm')
into :ls_jzsj
from kc03
where aac001=:grbh
and aae114='1';
if isnull(ls_jzsj) or ls_jzsj = '' then
return '未参'
end if
end ifif ls_jzsj < '1994-10' then
return '1994-10'
else
return ls_jzsj
end if//----- end gf_getjzsj(string grbh, string xz) //----- begin: gf_getcbzt(string grbh, string xz)
string ls_cbsj
select aac031
into :ls_cbsj
from ac02
where aac001=:grbh
and aae140=:xz;
if sqlca.sqlcode=100 then
return '未'
end if
if ls_cbsj='1' then
return 'start'
elseif ls_cbsj='2' then
return 'stop'
elseif ls_cbsj='3' then
return 'end'
end if
return ls_cbsj//----- end gf_getcbzt(string grbh, string xz)
正确的视图创建???关键函数怎么写到SQL语句中作为字段??
非常感谢??能帮忙完成解决的100满分奉上........
a.aac005,a.aac006,a.aac007,a.aac008,b.aab004,
select case when t_ac01.aic001 > 0 then Trunc(t_ac01.aic001 / 12, 0) || '年' || mod(t_ac01.aic001, 12) || '月' else '0' end as aic001,
建帐时间 //gf_getjzsj(ac01_aac001,'1')
核定缴费年限 // if(t_ac01_cac387 >0, Truncate(t_ac01_cac387 /12,0)+'年'+mod(t_ac01_cac387 ,12) ,'0')+'月'
养老保险参保状态 //gf_getcbzt(ac01_aac001,'1')
医疗保险参保状态 //gf_getcbzt(ac01_aac001,'3')
FROM ac01_ac02_touch //"T_AB01",
//"T_AC01"
WHERE 个人编号 //( "T_AC01"."AAB001" = "T_AB01"."AAB001" ) and
//( "T_AC01"."AAC001" = :as_grbh )
原系统实现方式:
//----- begin: gf_getjzsj(string grbh, string xz)
string ls_jzsjselect to_char(to_date(min(aae002),'yyyymm'),'yyyy-mm')
into :ls_jzsj
from ic01
where aac001=:grbh
and aae114='1';
if isnull(ls_jzsj) or ls_jzsj = '' then
select to_char(to_date(min(aae002),'yyyymm'),'yyyy-mm')
into :ls_jzsj
from kc03
where aac001=:grbh
and aae114='1';
if isnull(ls_jzsj) or ls_jzsj = '' then
return '未参'
end if
end ifif ls_jzsj < '1994-10' then
return '1994-10'
else
return ls_jzsj
end if//----- end gf_getjzsj(string grbh, string xz) //----- begin: gf_getcbzt(string grbh, string xz)
string ls_cbsj
select aac031
into :ls_cbsj
from ac02
where aac001=:grbh
and aae140=:xz;
if sqlca.sqlcode=100 then
return '未'
end if
if ls_cbsj='1' then
return 'start'
elseif ls_cbsj='2' then
return 'stop'
elseif ls_cbsj='3' then
return 'end'
end if
return ls_cbsj//----- end gf_getcbzt(string grbh, string xz)
正确的视图创建???关键函数怎么写到SQL语句中作为字段??
非常感谢??能帮忙完成解决的100满分奉上........
怎么写?在ORACLE中??
SELECT a.aac001, b.aab003,a.aac002,a.aac004,
a.aac005,a.aac006,a.aac007,a.aac008,b.aab004,
select case when t_ac01.aic001 > 0 then Trunc(t_ac01.aic001 / 12, 0) || '年' || mod(t_ac01.aic001, 12) || '月' else '0' end as aic001,
建帐时间 //gf_getjzsj(ac01_aac001,'1')(t_ac01_cac387 /12,0)+'年'+mod(t_ac01_cac387 ,12) ,'0')+'月'
养老保险参保状态 //gf_getcbzt(ac01_aac001,'1')
医疗保险参保状态 //gf_getcbzt(ac01_aac001,'3') gf_getjzsj(string grbh, string xz)
string ls_jzsjselect to_char(to_date(min(aae002),'yyyymm'),'yyyy-mm')
into :ls_jzsj
from ic01
where aac001=:grbh
and aae114='1';
if isnull(ls_jzsj) or ls_jzsj = '' then
select to_char(to_date(min(aae002),'yyyymm'),'yyyy-mm')
into :ls_jzsj
from kc03
where aac001=:grbh
and aae114='1';
if isnull(ls_jzsj) or ls_jzsj = '' then
return '未参'
end if
end ifif ls_jzsj < '1994-10' then
return '1994-10'
else
return ls_jzsj
end if//----- end gf_getjzsj(string grbh, string xz) //----- begin: gf_getcbzt(string grbh, string xz)
string ls_cbsj
select aac031
into :ls_cbsj
from ac02
where aac001=:grbh
and aae140=:xz;
if sqlca.sqlcode=100 then
return '未'
end if
if ls_cbsj='1' then
return 'start'
elseif ls_cbsj='2' then
return 'stop'
elseif ls_cbsj='3' then
return 'end'
end if
return ls_cbsj FROM ac01_ac02_touch //"T_AB01",
//"T_AC01"
WHERE 个人编号 //( "T_AC01"."AAB001" = "T_AB01"."AAB001" )
is
ls_jzsj varchar2(100);
begin
select to_date(min(aae002),'yyyy-mm') into ls_jzsj from ic01 where aac001=grbh and aae114='1';
if ls_jzsj is null or ls_jzsj = '' then
select to_date(min(aae002),'yyyy-mm') into ls_jzsj from kc03 where aac001=grbh and aae114='1';
if ls_jzsj is null or ls_jzsj = '' then
return '未参';
end if;
end if;
if ls_jzsj < '1994-10' then
ls_jzsj:= '1994-10';
else
return ls_jzsj;
end if;
end gf_getjzsj;
is
ls_cbsj varchar2(100);
begin
select aac031 into ls_cbsj from ac02 where aac001=grbh and aae140=xz;
if sqlca.sqlcode=100 then //????
return '未';
end if;
if ls_cbsj='1' then
return 'start';
end if;
if ls_cbsj='2' then
return 'stop';
end;
if ls_cbsj='3' then
return 'end';
end;
end gf_getcbzt;
is
ls_cbsj varchar2(100);
i number;
begin
select count(*) into i from ac02 where aac001=grbh and aae140=xz;
if i=0 then
return '末';
end if;
select aac031 into ls_cbsj from ac02 where aac001=grbh and aae140=xz;
if ls_cbsj='1' then
return 'start';
end if;
if ls_cbsj='2' then
return 'stop';
end;
if ls_cbsj='3' then
return 'end';
end;
end gf_getcbzt;
--------------------------------
非常感谢!!
再问你一个问题,就是能把这个函数的结果作为一个视图的字段么?
比如:
create or replace view ac01_ac02_touch as
SELECT aac001,gf_getjzsj(aac001,'1')
from t_ac01
???
再次感谢??
create table t(cid varchar2(100));
/
insert into t values( '1' );
/
create or replace function getStr return varchar2
is
begin
return '123';
end;
/
create or replace view v_t as select t.*,getStr from t;
/
--执行查询
select * from v_t
/
--查询结果
1 123