接下来比较重要，我会先贴出一个存储过程，根据这个存储过程讲解
PROCEDURE AP_CXBB_GT3_SBFGL_SBFYJSQC (OUT_RECORD OUT SYS_REFCURSOR, PI_XH PLS_INTEGER, PV_ZGSWJG VARCHAR2 --管理单位 --PI_HSND PLS_INTEGER, --汇算年度 --QR_HYDM PLS_INTEGER --行业 ) AS VI_HYXH PLS_INTEGER ; BEGIN VI_HYXH:=NVL(PI_XH ,0); DELETE FROM TMP_GT3_SBFGL_WJSTJB; if VI_HYXH >= 6 AND VI_HYXH <= 36 then insert into TMP_GT3_sbfgl_WJSTJB SELECT NSR.NSRSBH, NSR.NSRMC, NSR.SCJYDZ, case when NSRKZ.FDDBRYDDH is not null then '法人' || NSRKZ.FDDBRYDDH else '' end || case when NSRKZ.SWDLRLXDH is not null then ',税务代理人' || NSRKZ.SWDLRLXDH else '' end || case when NSRKZ.BSRYDDH is not null then ',办税人' || NSRKZ.BSRYDDH else '' end AS FDDBRGDDH, F.SWJGMC, G.SWJGMC FROM AP_CXBB_GT3_SBFAQYYIJSTJ_cs B , H_DJ_NSRXX NSR, H_DJ_NSRXX_KZ NSRKZ, DM_GY_SWJG_JH F, DM_GY_SWJG_JH G WHERE NSR.ZGSWJ_DM = F.SWJG_DM(+) AND B.SWJGDM = G.SWJG_DM(+) AND B.DJXH = NSR.DJXH(+) AND B.DJXH = NSRKZ.DJXH(+) AND ( B.SWJGDM IN (SELECT SWJG_DM FROM DM_GY_SWJG_JH V START WITH V.SWJG_DM IN (PV_ZGSWJG) CONNECT BY PRIOR V.SWJG_DM = V.SJSWJG_DM)) AND (VI_HYXH = 0 OR NSR.HY_DM IN (SELECT HY_DM FROM DM_HY_JH WHERE ZL IN (SELECT HYDM FROM TEMP_HYDM_JH_HS WHERE XH = VI_HYXH))) ;ELSE insert into TMP_GT3_sbfgl_WJSTJB SELECT NSR.NSRSBH, NSR.NSRMC, NSR.SCJYDZ, case when NSRKZ.FDDBRYDDH is not null then '法人' || NSRKZ.FDDBRYDDH else '' end || case when NSRKZ.SWDLRLXDH is not null then ',税务代理人' || NSRKZ.SWDLRLXDH else '' end || case when NSRKZ.BSRYDDH is not null then ',办税人' || NSRKZ.BSRYDDH else '' end AS FDDBRGDDH, F.SWJGMC, G.SWJGMC FROM AP_CXBB_GT3_SBFAQYYIJSTJ_cs B , H_DJ_NSRXX NSR, H_DJ_NSRXX_KZ NSRKZ, DM_GY_SWJG_JH F, DM_GY_SWJG_JH G WHERE NSR.ZGSWJ_DM = F.SWJG_DM(+) AND B.SWJGDM = G.SWJG_DM(+) AND B.DJXH = NSR.DJXH(+) AND B.DJXH = NSRKZ.DJXH(+) AND ( B.SWJGDM IN (SELECT SWJG_DM FROM DM_GY_SWJG_JH V START WITH V.SWJG_DM IN (PV_ZGSWJG) CONNECT BY PRIOR V.SWJG_DM = V.SJSWJG_DM)) AND (VI_HYXH = 0 OR NSR.HY_DM IN (SELECT HY_DM FROM DM_HY_JH WHERE DL IN (SELECT HYDM FROM TEMP_HYDM_JH_HS WHERE XH = VI_HYXH))) ; END IF ; OPEN OUT_RECORD FOR select * from TMP_GT3_sbfgl_WJSTJB A; ------------------------------------------------------------------------------------------END AP_CXBB_GT3_SBFGL_SBFYJSQC;
代码前部分
PROCEDURE AP_CXBB_GT3_SBFGL_SBFYJSQC (OUT_RECORD OUT SYS_REFCURSOR, PI_XH PLS_INTEGER, PV_ZGSWJG VARCHAR2 --管理单位 --PI_HSND PLS_INTEGER, --汇算年度 --QR_HYDM PLS_INTEGER --行业 ) AS VI_HYXH PLS_INTEGER ;
首先我们定义了 一个存储过程 AP_CXBB_GT3_SBFGL_SBFYJSQC
附注：创建 存储过程 PROCEDURE的语句为 create or replace PROCEDURE
如上：我们定义了SYS_REFCURSOR游标类型的输出变量OUT_RECORD ， PLS_INTEGER整数类型输入参数PI_XH, 和VARCHAR2字符串类型的输入参数PV_ZGSWJG
还有一个PLS_INTEGER类型变量 VI_HYXH
VI_HYXH:=NVL(PI_XH ,0);
接着我们对变量赋值，注意 orale赋值符号为 ：= 而不是 = ，其中nvl为判断参数是否空并做处理的内置函数
从begin 到end的代码块实现的业务流程我简单介绍下
1.删除临时表数据
2.将需要的数据存到临时表
3.将临时表数据放到游标输出
在写业务复杂的存储过程时，我们时常会用到临时表，游标。
下一章介绍该存储过程的 sql语句