存储过程不在多复杂,只要熟悉基本语法,都可写出来,下面是一个例子 CREATE OR REPLACE PROCEDURE SF_INS_JMSF_P ( V_YHBM SF_JMSF_T.YHBM%TYPE, V_CNQ SF_JMSF_T.CNQ%TYPE, V_JFRQ SF_JMSF_T.JFRQ%TYPE, V_JFJE SF_JMSF_T.JFJE%TYPE, V_PZH SF_JMSF_T.PZH%TYPE, V_FPH SF_JMSF_T.FPH%TYPE, V_FPLSH SF_DWSF_T.FPLSH%TYPE, V_CZY SF_JMSF_T.CZY%TYPE, V_FYLB SF_JMSF_T.FYLB%TYPE, V_FPLB SF_JMSF_T.FPLB%TYPE, V_JFFS SF_JMSF_T.JFFS%TYPE, V_BZ SF_JMSF_T.BZ%TYPE, V_SFY SF_JMSF_T.SFY%TYPE, V_JYGS SF_JMSF_T.JYGS%TYPE, V_GZBH SF_GZ_T.BH%TYPE, V_YKBH SF_YKFP_T.BH%TYPE, V_ZRJC SF_JMSF_T.ZRJC%TYPE, V_RESULT OUT NUMBER--处理结果 ) IS V_CZDW SF_YHQX_T.DW%TYPE; V_CZBM SF_YHQX_T.BM%TYPE; V_QFZE SF_JMJS_T.JBYS%TYPE; --V_ZNJQSJE SF_JMJS_T.JBYS%TYPE; BEGIN SELECT DW,BM INTO V_CZDW,V_CZBM FROM SF_YHQX_T WHERE YHXM=V_CZY; --增加退费信息 INSERT INTO SF_JMSF_T (YHBM,CNQ,JFRQ,JFJE,FPH,PZH,CZY,FYLB,BZ,LRSJ,JFFS,FPLB,SFY,JYGS,CZDW,CZBM,FPLSH,JFDZ,ZRJC) VALUES(V_YHBM,V_CNQ,V_JFRQ,V_JFJE,V_FPH,V_PZH,V_CZY,V_FYLB,V_BZ,SYSDATE, V_JFFS,V_FPLB,V_SFY,V_JYGS,V_CZDW,V_CZBM,V_FPLSH,'公司',V_ZRJC); IF V_CNQ<=SF_DQCNQ_F THEN IF V_FYLB='基本采暖费' THEN UPDATE SF_JMJS_T SET JBQFJE=JBQFJE-V_JFJE,JBSFJE=NVL(JBSFJE,0)+V_JFJE WHERE CNQ=V_CNQ AND YHBM=V_YHBM; ELSIF V_FYLB='超高采暖费' THEN UPDATE SF_JMJS_T SET CGQFJE=CGQFJE-V_JFJE,CGSFJE=NVL(CGSFJE,0)+V_JFJE WHERE CNQ=V_CNQ AND YHBM=V_YHBM; ELSIF V_FYLB='阳台采暖费' THEN UPDATE SF_JMJS_T SET YTQFJE=YTQFJE-V_JFJE,YTSFJE=NVL(YTSFJE,0)+V_JFJE WHERE CNQ=V_CNQ AND YHBM=V_YHBM; ELSIF V_FYLB='基础热费' THEN UPDATE SF_JMJS_T SET JCQFJE=JCQFJE-V_JFJE,JCSFJE=NVL(JCSFJE,0)+V_JFJE WHERE CNQ=V_CNQ AND YHBM=V_YHBM; ELSIF V_FYLB='滞纳金' THEN UPDATE SF_JMZNJ_T SET JFZE=NVL(JFZE,0)+V_JFJE,QFJE=NVL(QFJE,0)-V_JFJE WHERE CNQ=V_CNQ AND YHBM=V_YHBM; END IF; END IF; IF (V_FYLB<>'滞纳金') AND (V_CNQ<=SF_DQCNQ_F) THEN SELECT JBQFJE+YTQFJE INTO V_QFZE FROM SF_JMJS_T WHERE CNQ=V_CNQ AND YHBM=V_YHBM; IF V_QFZE<=0 THEN UPDATE SF_JMJS_T SET QFYY=NULL WHERE CNQ=V_CNQ AND YHBM=V_YHBM; /*SELECT COUNT(1) INTO V_QFZE FROM SF_JMZNJ_T WHERE CNQ=V_CNQ AND YHBM=V_YHBM; IF V_QFZE>0 THEN SELECT NVL(JFZE,0) INTO V_QFZE FROM SF_JMZNJ_T WHERE CNQ=V_CNQ AND YHBM=V_YHBM; IF V_QFZE=0 THEN DELETE FROM SF_JMZNJ_T WHERE CNQ=V_CNQ AND YHBM=V_YHBM; END IF; END IF;*/ END IF; END IF; ---- IF V_GZBH IS NOT NULL THEN UPDATE SF_GZ_T SET XZJE=XZJE+V_JFJE,SYJE=SYJE-V_JFJE WHERE BH=V_GZBH; END IF; IF V_YKBH IS NOT NULL THEN UPDATE SF_YKFP_T SET ZT=DECODE(SIGN(SYJE-V_JFJE),1,'未到','到账'), SHJE=SHJE+V_JFJE,SYJE=SYJE-V_JFJE WHERE BH=V_YKBH; END IF; --增加日志信息 INSERT INTO SF_CZRZ_T(CZSJ,CZLB,CZR,CZNR) VALUES(SYSDATE,'居民交费',V_CZY,V_YHBM||'[于]'||TO_CHAR(V_JFRQ,'YYYY-MM-DD')||'[交]'||V_CNQ||'[年度]'||V_FYLB||TO_CHAR(V_JFJE));
COMMIT; V_RESULT:= 0; EXCEPTION WHEN OTHERS THEN ROLLBACK; V_RESULT:= 1; END SF_INS_JMSF_P;
create procedure prc_test as begin null; end; 给你个最简单的
CREATE OR REPLACE PROCEDURE SF_INS_JMSF_P
(
V_YHBM SF_JMSF_T.YHBM%TYPE,
V_CNQ SF_JMSF_T.CNQ%TYPE,
V_JFRQ SF_JMSF_T.JFRQ%TYPE,
V_JFJE SF_JMSF_T.JFJE%TYPE,
V_PZH SF_JMSF_T.PZH%TYPE,
V_FPH SF_JMSF_T.FPH%TYPE,
V_FPLSH SF_DWSF_T.FPLSH%TYPE,
V_CZY SF_JMSF_T.CZY%TYPE,
V_FYLB SF_JMSF_T.FYLB%TYPE,
V_FPLB SF_JMSF_T.FPLB%TYPE,
V_JFFS SF_JMSF_T.JFFS%TYPE,
V_BZ SF_JMSF_T.BZ%TYPE,
V_SFY SF_JMSF_T.SFY%TYPE,
V_JYGS SF_JMSF_T.JYGS%TYPE,
V_GZBH SF_GZ_T.BH%TYPE,
V_YKBH SF_YKFP_T.BH%TYPE,
V_ZRJC SF_JMSF_T.ZRJC%TYPE,
V_RESULT OUT NUMBER--处理结果
)
IS
V_CZDW SF_YHQX_T.DW%TYPE;
V_CZBM SF_YHQX_T.BM%TYPE;
V_QFZE SF_JMJS_T.JBYS%TYPE;
--V_ZNJQSJE SF_JMJS_T.JBYS%TYPE;
BEGIN
SELECT DW,BM INTO V_CZDW,V_CZBM FROM SF_YHQX_T WHERE YHXM=V_CZY;
--增加退费信息
INSERT INTO SF_JMSF_T
(YHBM,CNQ,JFRQ,JFJE,FPH,PZH,CZY,FYLB,BZ,LRSJ,JFFS,FPLB,SFY,JYGS,CZDW,CZBM,FPLSH,JFDZ,ZRJC)
VALUES(V_YHBM,V_CNQ,V_JFRQ,V_JFJE,V_FPH,V_PZH,V_CZY,V_FYLB,V_BZ,SYSDATE,
V_JFFS,V_FPLB,V_SFY,V_JYGS,V_CZDW,V_CZBM,V_FPLSH,'公司',V_ZRJC);
IF V_CNQ<=SF_DQCNQ_F THEN
IF V_FYLB='基本采暖费' THEN
UPDATE SF_JMJS_T
SET JBQFJE=JBQFJE-V_JFJE,JBSFJE=NVL(JBSFJE,0)+V_JFJE
WHERE CNQ=V_CNQ AND YHBM=V_YHBM;
ELSIF V_FYLB='超高采暖费' THEN
UPDATE SF_JMJS_T
SET CGQFJE=CGQFJE-V_JFJE,CGSFJE=NVL(CGSFJE,0)+V_JFJE
WHERE CNQ=V_CNQ AND YHBM=V_YHBM;
ELSIF V_FYLB='阳台采暖费' THEN
UPDATE SF_JMJS_T
SET YTQFJE=YTQFJE-V_JFJE,YTSFJE=NVL(YTSFJE,0)+V_JFJE
WHERE CNQ=V_CNQ AND YHBM=V_YHBM;
ELSIF V_FYLB='基础热费' THEN
UPDATE SF_JMJS_T
SET JCQFJE=JCQFJE-V_JFJE,JCSFJE=NVL(JCSFJE,0)+V_JFJE
WHERE CNQ=V_CNQ AND YHBM=V_YHBM;
ELSIF V_FYLB='滞纳金' THEN
UPDATE SF_JMZNJ_T
SET JFZE=NVL(JFZE,0)+V_JFJE,QFJE=NVL(QFJE,0)-V_JFJE
WHERE CNQ=V_CNQ AND YHBM=V_YHBM;
END IF;
END IF;
IF (V_FYLB<>'滞纳金') AND (V_CNQ<=SF_DQCNQ_F) THEN
SELECT JBQFJE+YTQFJE INTO V_QFZE FROM SF_JMJS_T
WHERE CNQ=V_CNQ AND YHBM=V_YHBM;
IF V_QFZE<=0 THEN
UPDATE SF_JMJS_T SET QFYY=NULL WHERE CNQ=V_CNQ AND YHBM=V_YHBM;
/*SELECT COUNT(1) INTO V_QFZE FROM SF_JMZNJ_T WHERE CNQ=V_CNQ AND YHBM=V_YHBM;
IF V_QFZE>0 THEN
SELECT NVL(JFZE,0) INTO V_QFZE FROM SF_JMZNJ_T WHERE CNQ=V_CNQ AND YHBM=V_YHBM;
IF V_QFZE=0 THEN
DELETE FROM SF_JMZNJ_T WHERE CNQ=V_CNQ AND YHBM=V_YHBM;
END IF;
END IF;*/
END IF;
END IF;
----
IF V_GZBH IS NOT NULL THEN
UPDATE SF_GZ_T SET XZJE=XZJE+V_JFJE,SYJE=SYJE-V_JFJE WHERE BH=V_GZBH;
END IF;
IF V_YKBH IS NOT NULL THEN
UPDATE SF_YKFP_T SET ZT=DECODE(SIGN(SYJE-V_JFJE),1,'未到','到账'),
SHJE=SHJE+V_JFJE,SYJE=SYJE-V_JFJE WHERE BH=V_YKBH;
END IF;
--增加日志信息
INSERT INTO SF_CZRZ_T(CZSJ,CZLB,CZR,CZNR)
VALUES(SYSDATE,'居民交费',V_CZY,V_YHBM||'[于]'||TO_CHAR(V_JFRQ,'YYYY-MM-DD')||'[交]'||V_CNQ||'[年度]'||V_FYLB||TO_CHAR(V_JFJE));
COMMIT;
V_RESULT:= 0;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_RESULT:= 1;
END SF_INS_JMSF_P;
as
begin
null;
end;
给你个最简单的