动态游标，动态SQL

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;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8345941/viewspace-953448/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8345941/viewspace-953448/