Stored procedure if--else if--END if use

CREATE OR REPLACE PROCEDURE bjpjyxk_hf_sd (
Sqid_p in VARCHAR2,--application form ID
Xkbh_p in VARCHAR2,--newly generated license number
Flag out varchar2--Identification bit
)
As
YWLX VARCHAR2 (50);--Business type (0: New Office, 1: Change)
V_raise exception;--Custom exception
Zsid_p VARCHAR2 (50);--License ID
Yxkzh_p VARCHAR2 (50);--Original license number
Xkzlx_p VARCHAR2 (50);--License type
V_xkzh VARCHAR2 (50); --Beijing medicine to Beijing food Medicine temporary variable
BEGIN
--Routine body goes here, e.g.
Dbms_output. Put_Line (' ============== license issued ================ ');
--Get License type
SELECT ywlx into YWLX from xzxk_bjpjyws_hf_sq WHERE sqid=sqid_p;
--License Type
SELECT xkzlx into xkzlx_p from Gg_xkz_bjpscws WHERE sqid=sqid_p;
--Get the license information record in the receiving stage
SELECT Zsid,xmzh into zsid_p,yxkzh_p from Gg_xkz_bjpscws WHERE sqid=sqid_p;

--Update license information
IF ywlx = ' 0 ' Then
IF xkzlx_p = ' 1 ' then--operating a new license number
--update Gg_xkz_bjpscws set czzt= ' I ', datexc= ' one ', instnum=f_getslbs (), lastdate=sysdate where czzt= ' V ' and xmzh=xkbh_p;
UPDATE Gg_xkz_bjpscwsset
Xmzh = Xkbh_p,
FZRQ = To_char (sysdate, ' YYYYMMDD '),
YXQQSRQ = To_char (sysdate, ' YYYYMMDD '),
YXQJZRQ = To_char (Add_months (sysdate, 1), ' YYYYMMDD '),
Xkzzt = ' 1 ',
Dybs = ' 000 ',
GSBs = ' N ',
Czzt = ' V ',
Instnum = f_getslbs (),
Lastdate = Sysdate,
Datexc = ' 11 '
WHERE zsid = zsid_p;
--Update the Application form license number
UPDATE xzxk_bjpjyws_hf_sq SET bjbs= ' Y ', datexc= ' one ', instnum=f_getslbs (), Lastdate=sysdate WHERE sqid=sqid_p;
--Insert the application form into all information sheets
INSERT into XZXK_BJPSCWS_XX_QB
(Xkzid,xkzh,qyid,qymc,qyxz,qyzcdz,qyzcdzszqx,qyzcdzszjd,qyzcdzyzbm,jydz,jydzszqx,jydzszjd,jydzyzbm,bgdz, BGDZSZQX,BGDZSZJD,
BGDZYZBM,FDDBR,ZGRS,YTJRS,GDZC,SYMJ,TJCLLX,BGCS,XKFW,QTCL,DATEXC,XKZLX,INSTNUM,LASTDATE,LXR,LXDH)
SELECT ZSID_P,XKBH_P,QYID,QYMC,QYXZ,QYZCDZ,QYZCDZSZQX,QYZCDZSZJD,QYZCDZYZBM,JYDZ,JYDZSZQX,JYDZSZJD,JYDZYZBM, BGDZ,BGDZSZQX,BGDZSZJD,
BGDZYZBM,FDDBR,ZGRS,YTJRS,GDZC,SYMJ, ', ', XKXM,QTCL, ' One ', xkzlx,f_getslbs (), SYSDATE,LXR,LXDH
From xzxk_bjpjyws_hf_sq WHERE sqid=sqid_p;

--Update Full table information (based on license information)
UPDATE xzxk_bjpscws_xx_qb SET (XKZZT,FZRQ,SCFZRQ,YXQQSRQ,YXQJZRQ,ZZJGDM,FZJGDM,FZJGMC,DYBS,GSBS,XKZLX,QYMC,FDDBR, QYZCDZ,BGDZ,JYDZ,JYDZSZQX,JYDZSZJD,XKFW) =
(SELECT xkzzt,fzrq,fzrq,yxqqsrq,yxqjzrq,zzjgdm,fzjgdm,fzjgmc,dybs,gsbs,xkzlx,qymc,fddbr,qydz,bgdz,jydz,jydzszqx , JYDZSZJD,XKFW
From GG_XKZ_BJPSCWS where sqid=sqid_p) where xkzid=zsid_p;

--Insert Personnel information table
INSERT into Xzxk_bjpscws_xx_ryxx (Zj,xkzh,qyid,xm,zjlx,zjh,xl,sxzy,zc,instnum,lastdate,datexc,xkzid)
SELECT Sys_guid (), xkbh_p,qyid,fddbr,zjlx,zjh,xl,sxzy,zc,f_getslbs (), Sysdate, ' one ', zsid_p
From xzxk_bjpjyws_hf_sq WHERE sqid=sqid_p;

UPDATE xzxk_bjpscws_xx_ryxx SET (XM) =
(SELECT fddbr
from Gg_xkz_bjpscws where sqid=sqid_p) where xkzid=zsid_p;
Elsif xkzlx_p = ' 3 ' then--production use license number
--update production license is invalid
Update GG_XKZ_BJPSCWS set czzt= ' I ', datexc= ' one ', instnum=f_ getslbs (), lastdate=sysdate where czzt= ' V ' and xmzh=yxkzh_p;
--change Beijing medicine to Beijing food
V_xkzh: = REPLACE (yxkzh_p, ' Beijing Medicine ', ' Beijing Food ');
-new license is valid
UPDATE gg_xkz_bjpscwsset
Xmzh = V_xkz H,
Fzrq = To_char (sysdate, ' YYYYMMDD '),
Yxqqsrq = To_char (sysdate, ' YYYYMMDD '),
Yxqjzrq = To_char (Add_months ( Sysdate-1, ' YYYYMMDD '),
Xkzzt = ' 1 ',
Dybs = ' $ ',
GSBs = ' N ',
Czzt = ' V ',
Instnum = f_getslbs () ,
Lastdate = sysdate,
Datexc = ' one '
WHERE zsid = zsid_p;

--Update Request form license Number
Update xzxk_bjpjyws_hf_sq SET bjbs= ' Y ', datexc= ' one ', instnum=f_getslbs (), Lastdate=sysdate WHERE sqid=sqid_p;
--Data exchange Delete using
/* INSERT into Pub_table_data_delexc (ID, tablename,tablepkname,tablepk,delflag,instnum,lastdate, DATEXC)
Select Sys_guid (), ' xzxk_bjpscws_xx_qb ', ' Xkzid ', Xkzid as TABLEPK, ' N ', f_getslbs (), Sysdate, ' one '
from XZXK_BJPSCWS_XX_QB where xkzh=yxkzh_p;
*/
--Insert the application form into the entire information table in
Update XZXK_BJPSCWS_XX_QB set
(XKZH,QYID,QYMC,QYXZ,QYZCDZ,QYZCDZSZQX,QYZCDZSZJD, QYZCDZYZBM,JYDZ,JYDZSZQX,JYDZSZJD,JYDZYZBM,BGDZ,BGDZSZQX,BGDZSZJD,
BGDZYZBM,FDDBR,ZGRS,YTJRS,GDZC,SYMJ, TJCLLX,BGCS,XKFW,QTCL,DATEXC,XKZLX,INSTNUM,LASTDATE,LXR,LXDH)
= (SELECT V_xkzh,qyid,qymc,qyxz,qyzcdz, QYZCDZSZQX,QYZCDZSZJD,QYZCDZYZBM,JYDZ,JYDZSZQX,JYDZSZJD,JYDZYZBM,BGDZ,BGDZSZQX,BGDZSZJD,
BGDZYZBM,FDDBR, ZGRS,YTJRS,GDZC,SYMJ, ', ', ' xkxm,qtcl, ' one ', xkzlx,f_getslbs (), Sysdate,lxr,lxdh
from xzxk_bjpjyws_hf_sq WHERE sqid=sqid_p) where xkzh=yxkzh_p;

--Update Full table information (based on license information)
UPDATE xzxk_bjpscws_xx_qb SET (XKZZT,FZRQ,SCFZRQ,YXQQSRQ,YXQJZRQ,ZZJGDM,FZJGDM,FZJGMC,DYBS,GSBS,XKZLX,QYMC,FDDBR, QYZCDZ,BGDZ,SCDZ,SCDZSZQX,SCDZSZJD,XKFW) =
(SELECT xkzzt,fzrq,fzrq,yxqqsrq,yxqjzrq,zzjgdm,fzjgdm,fzjgmc,dybs,gsbs,xkzlx,qymc,fddbr,qydz,bgdz,scdz,scdzszqx , SCDZSZJD,XKFW
From GG_XKZ_BJPSCWS where sqid=sqid_p) where Xkzh=v_xkzh;

--Data exchange Delete user table
INSERT into Pub_table_data_delexc (ID, TABLENAME,TABLEPKNAME,TABLEPK,DELFLAG,INSTNUM,LASTDATE,DATEXC)
Select Sys_guid (), ' xzxk_bjpscws_xx_ryxx ', ' ZJ ', zj as TABLEPK, ' N ', f_getslbs (), Sysdate, ' one ' from xzxk_bjpscws_xx_ryxx where xkzh=yxkzh_p;

Delete Xzxk_bjpscws_xx_ryxx where xkzh=yxkzh_p;
--Insert Personnel information table
INSERT into Xzxk_bjpscws_xx_ryxx (Zj,xkzh,qyid,xm,zjlx,zjh,xl,sxzy,zc,instnum,lastdate,datexc,xkzid)
SELECT Sys_guid (), v_xkzh,qyid,fddbr,zjlx,zjh,xl,sxzy,zc,f_getslbs (), Sysdate, ' one ', zsid_p
From xzxk_bjpjyws_hf_sq WHERE sqid=sqid_p;

UPDATE xzxk_bjpscws_xx_ryxx SET (XM) =
(SELECT FDDBR
From GG_XKZ_BJPSCWS where sqid=sqid_p) where xkzid=zsid_p;

ELSE
Raise v_raise;
END IF;

elsif ywlx = ' 1 ' Then
--Change the original valid license to invalid
Update GG_XKZ_BJPSCWS set czzt= ' I ', datexc= ' one ', instnum=f_getslbs (), lastdate=sysdate where czzt= ' V ' and xmzh=yxkzh_p;
--to deal with the pre-launch license number to change the Beijing medicine into Beijing Food medicine
If InStr (Yxkzh_p, ' Beijing Medicine ') >0 then
V_xkzh:=replace (yxkzh_p, ' Beijing Medicine ', ' Beijing Food Medicine ');
Else
V_xkzh:=yxkzh_p;
End If;
--Update license
UPDATE GG_XKZ_BJPSCWS
SET Fzrq = To_char (sysdate, ' YYYYMMDD '),
YXQQSRQ = To_char (sysdate, ' YYYYMMDD '),
Xmzh = V_xkzh,
Xkzzt = ' 1 ',
Dybs = ' 000 ',
GSBs = ' N ',
Czzt = ' V ',
Instnum = f_getslbs (),
Lastdate = Sysdate,
Datexc = ' 11 '
WHERE zsid = zsid_p;

--Update the Application form license number
UPDATE xzxk_bjpjyws_hf_sq SET bjbs= ' Y ', datexc= ' one ', instnum=f_getslbs (), Lastdate=sysdate WHERE sqid=sqid_p;

--Data exchange Delete use
--insert into Pub_table_data_delexc (ID, TABLENAME,TABLEPKNAME,TABLEPK,DELFLAG,INSTNUM,LASTDATE,DATEXC)
--select sys_guid (), ' xzxk_bjpscws_xx_qb ', ' Xkzid ', Xkzid as TABLEPK, ' N ', f_getslbs (), sysdate, ' 11 '
--from XZXK_BJPSCWS_XX_QB where xkzh=yxkzh_p;
--Update Full table information
UPDATE XZXK_BJPSCWS_XX_QB
SET (XKZH,QYID,QYMC,QYXZ,QYZCDZ,QYZCDZSZQX,QYZCDZSZJD,QYZCDZYZBM,JYDZ,JYDZSZQX,JYDZSZJD,JYDZYZBM,BGDZ,BGDZSZQX, BGDZSZJD,
BGDZYZBM,FDDBR,ZGRS,YTJRS,GDZC,SYMJ,TJCLLX,BGCS,XKFW,QTCL,DATEXC,XKZLX,INSTNUM,LASTDATE,LXR,LXDH) =
(SELECT V_xkzh,qyid,qymc,qyxz,qyzcdz,qyzcdzszqx,qyzcdzszjd,qyzcdzyzbm,jydz,jydzszqx,jydzszjd,jydzyzbm,bgdz, BGDZSZQX,BGDZSZJD,
BGDZYZBM,FDDBR,ZGRS,YTJRS,GDZC,SYMJ, ', ', XKXM,QTCL, ' One ', xkzlx,f_getslbs (), SYSDATE,LXR,LXDH
From XZXK_BJPJYWS_HF_SQ WHERE sqid=sqid_p)
WHERE xkzh=yxkzh_p;

--Update Full table information (based on license information)
UPDATE xzxk_bjpscws_xx_qb SET (XKZZT,FZRQ,SCFZRQ,YXQQSRQ,YXQJZRQ,ZZJGDM,FZJGDM,FZJGMC,DYBS,GSBS,XKZLX,QYMC,FDDBR, QYZCDZ,BGDZ,SCDZ,SCDZSZQX,SCDZSZJD,XKFW) =
(SELECT xkzzt,fzrq,fzrq,yxqqsrq,yxqjzrq,zzjgdm,fzjgdm,fzjgmc,dybs,gsbs,xkzlx,qymc,fddbr,qydz,bgdz,scdz,scdzszqx , SCDZSZJD,XKFW
From GG_XKZ_BJPSCWS where sqid=sqid_p) where xkzid=zsid_p;

--Data exchange Delete user table
INSERT into Pub_table_data_delexc (ID, TABLENAME,TABLEPKNAME,TABLEPK,DELFLAG,INSTNUM,LASTDATE,DATEXC)
Select Sys_guid (), ' xzxk_bjpscws_xx_ryxx ', ' ZJ ', zj as TABLEPK, ' N ', f_getslbs (), sysdate, ' 11 '
from Xzxk_bjpscws_xx_ryxx where xkzh=yxkzh_p;
Delete Xzxk_bjpscws_xx_ryxx where xkzh=yxkzh_p;

--Insert People table
INSERT INTO Xzxk_bjpscws_xx_ryxx
(Zj,xkzh,qyid,xm,zjlx,zjh,xl,sxzy,zc,instnum,lastdate,datexc,xkzid)
Select Sys_guid (), v_xkzh,qyid,fddbr,zjlx,zjh,xl,sxzy,zc,f_getslbs (), Sysdate, ' one ', zsid_p
from XZXK_BJPJYWS_HF_SQ where sqid=sqid_p;

UPDATE xzxk_bjpscws_xx_ryxx SET (XM) =
(SELECT FDDBR
From GG_XKZ_BJPSCWS where sqid=sqid_p) where xkzid=zsid_p;

Dbms_output. Put_Line (' ============== license issued to end ================ ');
ELSE
Raise v_raise;
END IF;

Commit
Exception
When V_raise Then
Flag: = ' N ';
Dbms_output. Put_Line (' ============== did not find the specified business Type ================ ');
Rollback
When No_data_found Then
Flag: = ' N ';
Rollback
When others then
Flag: = ' N ';
Rollback
END BJPJYXK_HF_SD;

Stored procedure if--else if--END if use