oracle rs取值,【讨论】调用存储SYS_REFCURSOR类型如何取值？-CSDN博客

本文介绍了一个使用PL/SQL编写的薪资生成过程(pro_generate_salary)，该过程根据员工编号(rybm)和类型ID(type_id)来确定薪资计算的具体方式。通过对员工的工作经历和教育背景等信息的查询，判断是否可以进行薪资计算，并根据员工的不同类型调用相应的子过程进行具体的薪资计算。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace procedure pro_generate_salary(returnResult out SYS_REFCURSOR,rybm varchar2,type_id varchar2) as

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

??: ??????????????????????

???????????????????????????

??? 2012?1?8?11:03:15

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

v_return varchar2(255);--?????????????

v_count_jl int;--????????

v_count_jyjl varchar2(64);--??????????

v_rylx varchar2(8);--???????????????

v_edu varchar2(64);--??

v_sign varchar2(8);

v_sql varchar2(2000);

v_table_gz varchar2(64);

v_table_rs varchar2(64);

v_table_work varchar2(64);--???

v_table_edu varchar2(100);

v_workdate varchar2(100);

v_lower varchar2(8);

v_out_rylx varchar2(8);

v_iscontinue varchar2(8);--??????????????

v_first varchar2(8);--??????????

v_a varchar2(4000);

begin

v_return :='';

v_lower:='no';

v_iscontinue:='no';

v_first:='1';

if type_id='201201160790' or type_id='201111170489' or type_id='201111150479' then --?????????????????(type_id ????????????????????????)

v_table_gz := 'tbl_rs_wageinfo_temp';

v_table_rs := 'tbl_info_baseinfor_temp';

v_table_work := 'TBL_INFO_WORKEXPERIENCE_temp';

v_table_edu:='tbl_info_edu_temp';

else

v_table_gz := 'tbl_rs_wageinfo';

v_table_rs := 'tbl_info_baseinfor';

v_table_work := 'TBL_INFO_WORKEXPERIENCE';

v_table_edu:='tbl_info_edu';

end if;

execute immediate 'select to_char(WORKDATE,''YYYY-MM-DD'') from '||v_table_rs||' where pid='''||rybm||''' ' into v_workdate;

--??????????????????????????????????

--??

execute immediate 'select count(1) from '||v_table_work||' t where t.pid='''||rybm||''' ' into v_count_jl;

execute immediate 'select count(1) from '||v_table_edu||' t where t.pid='''||rybm||''' and ENDDATE<=to_date('''||v_workdate||''',''YYYY-MM-DD'')' into v_count_jyjl;

/*select count(1) into v_count_jyjl from tbl_info_edu t where t.pid=rybm and ENDDATE<=v_workdate;*/

--??????????????????????

v_sql := 'select tr_syqzw_ryzx(EXECUTEWAGEORDER) from '||v_table_work||' t

where BEGINDATE = (select min(BEGINDATE) from '||v_table_work||' where pid='''||rybm||''') and t.pid='''||rybm||'''

and (sign is null or sign = ''1'')';

execute immediate v_sql into v_rylx;

--??

if v_count_jl =0 or v_count_jyjl = 0 then

if v_count_jl =0 then

v_return := '??????!';

else

v_return := '????????!';

end if;

open returnResult for

select '1' sign,v_return return_str from dual;

else

execute immediate 'select recode

from '||v_table_edu||'

where pid='''||rybm||'''

and enddate = (select max(enddate)

from '||v_table_edu||'

where pid='''||rybm||'''

and enddate <=to_date('''||v_workdate||''',''YYYY-MM-DD''))' into v_edu;

/*select recode

into v_edu

from tbl_info_edu

where pid = rybm

and enddate = (select max(enddate)

from tbl_info_edu

where pid = rybm

and enddate <= v_workdate);*/

/*select RECODE into v_edu from tbl_info_edu t where t.pid=rybm;*/

if v_edu is null then

v_return := '???????????!';

open returnResult for

select '1' sign,v_return return_str from dual;

else

while v_iscontinue='yes' or v_first=1 loop

if v_rylx = 'L01' or v_out_rylx='L01' then

v_first:='0';

pro_generate_salary_gwy(v_sign,v_lower,v_out_rylx,v_iscontinue,rybm,v_rylx,v_table_gz,v_table_rs,type_id);

while v_lower='yes' loop

pro_generate_salary_gwy(v_sign,v_lower,v_out_rylx,v_iscontinue,rybm,v_rylx,v_table_gz,v_table_rs,type_id);

end loop;

elsif v_rylx = 'L02' or v_out_rylx='L02' then

v_first:='0';

pro_generate_salary_jggr(v_sign,v_lower,v_out_rylx,v_iscontinue,rybm,v_rylx,v_table_gz,v_table_rs,type_id);

while v_lower='yes' loop

pro_generate_salary_jggr(v_sign,v_lower,v_out_rylx,v_iscontinue,rybm,v_rylx,v_table_gz,v_table_rs,type_id);

end loop;

else

v_first:='0';

pro_generate_salary_sy(v_sign,v_lower,v_out_rylx,v_iscontinue,rybm,v_rylx,v_table_gz,v_table_rs,type_id);

while v_lower='yes' loop

pro_generate_salary_sy(v_sign,v_lower,v_out_rylx,v_iscontinue,rybm,v_rylx,v_table_gz,v_table_rs,type_id);

end loop;

end if;

end loop;

if v_sign = '0' then

open returnResult for

select '0' sign,v_return return_str from dual;

else

open returnResult for

select '1' sign,v_return return_str from dual;

end if;

exception

when others then

v_return := substr(sqlerrm, 1, 200);

open returnResult for

select '1' sign,v_return return_str from dual;

insert into TBL_rs_PROC_LOG

(LOGID, LOGNAME, CONTENT, MEM, DATETIME)

values

(get_proclog_id(),

'pro_generate_salary',

v_return,

'',

sysdate);

commit;

end;