create or replace procedure PRO_AJ_GB_JBXX
(p_tablename in varchar2,p_zhsjc in date,P_SJW IN VARCHAR2)
is
v_ajbh aj_gb_jbxx.ajbh%type;
v_zab aj_gb_jbxx.zab%type;
v_fab1 aj_gb_jbxx.fab1%type;
v_fab2 aj_gb_jbxx.fab2%type;
v_fasjsx aj_gb_jbxx.fasjsx%type;
v_fasjxx aj_gb_jbxx.fasjxx%type;
v_fadqh aj_gb_jbxx.fadqh%type;
v_fadxz aj_gb_jbxx.fadxz%type;
v_fadzbh aj_gb_jbxx.fadzbh%type;
v_fxsj aj_gb_jbxx.fxsj%type;
v_ladw aj_gb_jbxx.ladw%type;
v_larq aj_gb_jbxx.larq%type;
v_cbdw aj_gb_jbxx.cbdw%type;
v_aqms aj_gb_jbxx.aqms%type;
v_ywgl aj_gb_jbxx.ywgl%type;
v_zhsjc aj_gb_jbxx.zhsjc%type;
V_SFWK NUMBER;
v_sfzx varchar2(5);
TYPE CURSOR_XX IS REF CURSOR;
CC CURSOR_XX;
begin
OPEN CC FOR 'select ajbh, zab, fab1, fab2, fasjsx, fasjxx, fadqh, fadxz, fadzbh, fxsj, ladw, larq, cbdw, aqms, ywgl, zhsjc from
'||p_tablename||' where zhsjc >= :1 and zhsjc <= ( select max(zhsjc) from '||p_tablename||')' using p_zhsjc;
LOOP
FETCH CC INTO v_ajbh, v_zab, v_fab1, v_fab2, v_fasjsx, v_fasjxx, v_fadqh, v_fadxz, v_fadzbh, v_fxsj, v_ladw, v_larq, v_cbdw, v_aqms, v_ywgl, v_zhsjc;
EXIT WHEN CC%NOTFOUND;
SELECT COUNT(*) INTO V_SFWK FROM AJ_GB_JBXX WHERE ajbh = V_ajbh;
if v_sfzx = '0' or v_sfzx is null then
IF V_SFWK <= 0 THEN
insert into aj_gb_jbxx (ajbh, zab, fab1, fab2, fasjsx, fasjxx, fadqh, fadxz, fadzbh, fxsj, ladw, larq, cbdw, aqms, ywgl, zhsjc)
values (v_ajbh, v_zab, v_fab1, v_fab2, v_fasjsx, v_fasjxx, v_fadqh, v_fadxz, v_fadzbh, v_fxsj, v_ladw, v_larq, v_cbdw, v_aqms, v_ywgl, v_zhsjc);
ELSE
IF P_SJW <> '1' THEN
UPDATE aj_gb_jbxx SET YWGL= '31' WHERE ajbh = V_ajbh;
ELSE
UPDATE aj_gb_jbxx SET ajbh=v_ajbh, zab=v_zab, fab1=v_fab1, fab2=v_fab2, fasjsx=v_fasjsx,
fasjxx=v_fasjxx, fadqh=v_fadqh, fadxz=v_fadxz, fadzbh=v_fadzbh, fxsj=v_fxsj, ladw=v_ladw,
larq=v_larq, cbdw=v_cbdw, aqms=v_aqms,ywgl= '31', zhsjc=v_zhsjc WHERE ajbh = V_ajbh;
END IF;
END IF;
else
IF V_SFWK <= 0 THEN
insert into aj_gb_jbxx (ajbh, zab, fab1, fab2, fasjsx, fasjxx, fadqh, fadxz, fadzbh, fxsj, ladw, larq, cbdw, aqms, ywgl, zhsjc)
values (v_ajbh, v_zab, v_fab1, v_fab2, v_fasjsx, v_fasjxx, v_fadqh, v_fadxz, v_fadzbh, v_fxsj, v_ladw, v_larq, v_cbdw, v_aqms, v_ywgl, v_zhsjc);
ELSE
IF P_SJW <> '1' THEN
UPDATE aj_gb_jbxx SET YWGL= '31' WHERE ajbh = V_ajbh;
ELSE
UPDATE aj_gb_jbxx SET ajbh=v_ajbh, zab=v_zab, fab1=v_fab1, fab2=v_fab2, fasjsx=v_fasjsx,
fasjxx=v_fasjxx, fadqh=v_fadqh, fadxz=v_fadxz, fadzbh=v_fadzbh, fxsj=v_fxsj, ladw=v_ladw,
larq=v_larq, cbdw=v_cbdw, aqms=v_aqms,ywgl= '31', zhsjc=v_zhsjc WHERE ajbh = V_ajbh;
END IF;
END IF;
end if;
END LOOP;
CLOSE CC;
end PRO_AJ_GB_JBXX;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8345941/viewspace-953448/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8345941/viewspace-953448/