批量插入代码存储过程-优快云博客

本文链接：https://blog.youkuaiyun.com/JavaCoder_juejue/article/details/107537628

存储过程实例：

PROCEDURE CODEBATCHINSERTDC(startcode in varchar2,
endcode in varchar2,
codepoolid in varchar2,
codetype in varchar2,
codesubtype in varchar2,
username in varchar2,
TESTROOM in varchar2,
LOCOBJID in varchar2,
RESERVELEVEL in number,
LONG_FUNCTION in number,
CHARGE in number,
CONTROL_WAY in number,
BIT_LEN in varchar2,
START_BIT_LEN in number,
COMMENTS in varchar2,
USING in number,
LIFECYCLE in number,
DESCNAME in varchar2,
ORDERNUM in varchar2) is
VM_CODECOUNT varchar2(20);
EXISTSCODE EXCEPTION;
NUMISTOLARGE EXCEPTION;
VM_CODEID VARCHAR2(20);
VM_MES VARCHAR2(200);
i number;
VM_ACCOUNTPIN varchar2(200);
type NODEID_CUR_TYPE is ref cursor return c_codepoolentity%rowtype;
NODEID_CUR NODEID_CUR_TYPE;
NODEID_RECORD c_codepoolentity%rowtype;
VM_CODEPOOLTYPE_ID number;
VM_AREA_ID number;
VM_CODETYPE number;
VM_CODELIFE_DEFAULT number;
VM_CODELIFE2 number;
VM_CODELIFE_TEMP number;
CODEPOOL_START NUMBER;
CODEPOOL_END NUMBER;
codesubtype_key varchar2(10); --purdo1.0-9443
V_COUNT NUMBER;
V_USING NUMBER;
V_RESERVELEVEL number;
V_LIFECYCLE NUMBER;
V_LONG_FUNCTION NUMBER;
v_CHARGE NUMBER;
V_CONTROL_WAY NUMBER;
V_BIT_LEN VARCHAR2(255);
V_START_BIT_LEN NUMBER;
V_COMMENTS vARCHAR2(255);
VM_NODE_ID_LIST CODE_CODEPOOLID_LIST;
V_ORDERNUM varchar2(30);
v_index number;
V_OFFICENN number;
BEGIN
if (codetype != 114004) then
if (((endcode - startcode) + 1) > 10000) then
RAISE NUMISTOLARGE;
end if;
end if;
--select count(0) INTO VM_CODECOUNT from c_code where code between to_number(startcode) and to_number(endcode) and codetype_id = codetype;
/*select checkCodeCount(startcode,endcode,codetype,codesubtype)into VM_CODECOUNT from dual;
IF (VM_CODECOUNT>0) THEN
RAISE EXISTSCODE;
END IF;*/
i := to_number(startcode);
VM_AREA_ID := LOCOBJID;

VM_CODELIFE_DEFAULT := 4965;
VM_CODELIFE2 := 4964;
VM_CODELIFE_TEMP := VM_CODELIFE_DEFAULT;

if VM_CODEPOOLTYPE_ID = 115000 then
select t.logiccodetype
into VM_CODETYPE
from ext_codepool t
where t.id = codepoolid;

if VM_CODETYPE = 4884 then
VM_CODELIFE_TEMP := VM_CODELIFE2;
else
VM_CODELIFE_TEMP := VM_CODELIFE_DEFAULT;
end if;
end if;
select to_number(substr(CPP.OFFICENUMBER_NAME, 1, 1))
into V_OFFICENN
from C_CODEPOOL CPP
where CPP.ID = codepoolid;
V_USING := to_number(USING);
V_RESERVELEVEL := RESERVELEVEL;
V_LIFECYCLE := LIFECYCLE;
V_LONG_FUNCTION := LONG_FUNCTION;
v_CHARGE := CHARGE;
V_CONTROL_WAY := CONTROL_WAY;
V_BIT_LEN := BIT_LEN;
V_START_BIT_LEN := START_BIT_LEN;
V_COMMENTS := COMMENTS;

if ((V_USING = f_get_dictionaryid('用途', '20', 'CDMA') or
V_USING = f_get_dictionaryid('用途', '21', 'CDMA2000') or
V_USING = f_get_dictionaryid('用途', '22', 'GSM') or
V_USING = f_get_dictionaryid('用途', '23', 'TD-SCDMA') or
V_USING = f_get_dictionaryid('用途', '24', 'WCDMA'))) then
v_index := 1;
else
v_index := 0;
end if;
while (i <= to_number(endcode)) loop
IF (codetype = 114003) THEN
select count(0)
INTO VM_CODECOUNT
from c_code
where code_val = i
AND codetype_id = 114003;
ELSIF (codetype = 114004) THEN
select count(0)
INTO VM_CODECOUNT
from c_code
where code_val = i
AND codetype_id = 114004;
ELSE
select count(0)
INTO VM_CODECOUNT
from c_code
where code_val = i
AND codetype_id != 114004;
END IF;
/* if(VM_CODECOUNT > 0 and v_index=0) then
select MIN(ID) INTO VM_CODEID from c_code where code_val = i;
IF(RESERVELEVEL != 4975) THEN
update c_code set codetype_id = codetype,Codepool_Id = codepoolid,RESERVELEVEL = V_RESERVELEVEL,LONG_FUNCTION = V_LONG_FUNCTION,CHARGE = V_CHARGE,CONTROL_WAY = V_CONTROL_WAY,BIT_LEN = V_BIT_LEN,START_BIT_LEN = V_START_BIT_LEN,COMMENTS = V_COMMENTS,USING = V_USING,LIFECYCLE = V_LIFECYCLE where id = VM_CODEID;
ELSE
update c_code set codetype_id = codetype,Codepool_Id = codepoolid,RESERVELEVEL = V_RESERVELEVEL,LONG_FUNCTION = V_LONG_FUNCTION,Reserver = username,CHARGE = V_CHARGE,CONTROL_WAY = V_CONTROL_WAY,BIT_LEN = V_BIT_LEN,START_BIT_LEN = V_START_BIT_LEN,COMMENTS = V_COMMENTS,USING = V_USING,LIFECYCLE = V_LIFECYCLE,RESERVETIME = sysdate where id = VM_CODEID;
END IF;
ELSE*/
select seq_c_code.nextval into VM_CODEID FROM DUAL;
IF (codetype = 114017) THEN
--LocalCode
IF (RESERVELEVEL != 4975) THEN
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE)
values
(VM_CODEID,
i,
i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE);
ELSE
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
RESERVETIME,
USING,
LIFECYCLE,
Reserver)
values
(VM_CODEID,
i,
i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
sysdate,
USING,
LIFECYCLE,
username);
END IF;
ELSIF (codetype = 114003) THEN
--LocalCode
--insert into ext_code(id,LOCALCODETYPE) values (VM_CODEID, codesubtype);
IF (RESERVELEVEL != 4975) THEN
IF (v_index = 1 AND V_OFFICENN = 0) THEN
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE)
values
(VM_CODEID,
'0' || i,
'0' || i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE);
ELSE
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE)
values
(VM_CODEID,
i,
i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE);
END IF;
ELSE
IF (v_index = 1 AND V_OFFICENN = 0) THEN
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
RESERVETIME,
USING,
Logiccodetype,
LIFECYCLE,
Reserver)
values
(VM_CODEID,
'0' || i,
'0' || i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
sysdate,
USING,
USING,
LIFECYCLE,
username);
ELSE
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
RESERVETIME,
USING,
Logiccodetype,
LIFECYCLE,
Reserver)
values
(VM_CODEID,
i,
i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
sysdate,
USING,
USING,
LIFECYCLE,
username);
END IF;
END IF;
ELSIF (codetype = 114004) THEN
--LocalCode
--insert into ext_code(id,LOCALCODETYPE) values (VM_CODEID, codesubtype);
IF (RESERVELEVEL != 4975) THEN
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE)
values
(VM_CODEID,
i,
i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE);
ELSE
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
RESERVETIME,
USING,
Logiccodetype,
LIFECYCLE,
Reserver)
values
(VM_CODEID,
i,
i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
sysdate,
USING,
USING,
LIFECYCLE,
username);
END IF;
ELSIF (codetype = 114011) THEN
--ShortCode
--insert into ext_code(id,LOCALCODETYPE) values (VM_CODEID, codesubtype);
IF (RESERVELEVEL != 4975) THEN
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE)
values
(VM_CODEID,
i,
i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE);
ELSE
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
RESERVETIME,
USING,
LIFECYCLE,
Reserver)
values
(VM_CODEID,
i,
i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
sysdate,
USING,
LIFECYCLE,
username);
open NODEID_CUR for
select coen.*
from c_codepoolentity coen
where coen.codepool_id =
(select a.id from c_codepool a where a.startcode = i);
END IF;
loop
fetch NODEID_CUR
into NODEID_RECORD;
exit when NODEID_CUR%notfound;
insert into c_codeentity
(id, codetype_id, code_id, abstractentity_id, entity_id)
values
(seq_c_codeentity.nextval,
codetype,
VM_CODEID,
102,
NODEID_RECORD.Entity_Id);
end loop;
close NODEID_CUR;
ELSIF (codetype = 114019) THEN
--LocalCode
--insert into ext_code(id,LOCALCODETYPE) values (VM_CODEID, codesubtype);
IF (RESERVELEVEL != 4975) THEN
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE)
values
(VM_CODEID,
i,
i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
USING,
LIFECYCLE);
ELSE
insert into c_code
(id,
name,
code,
code_val,
createdate,
editdate,
creator,
editor,
version,
codetype_id,
Codepool_Id,
location_id,
servicestatus,
AREA_ID,
LOCALCODETYPE,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
RESERVETIME,
USING,
LIFECYCLE,
Reserver)
values
(VM_CODEID,
i,
i,
i,
sysdate,
sysdate,
username,
username,
'1',
codetype,
codepoolid,
TESTROOM,
f_get_dictionaryid('业务状态', '0', '空闲'),
VM_AREA_ID,
codesubtype,
RESERVELEVEL,
LONG_FUNCTION,
CHARGE,
CONTROL_WAY,
BIT_LEN,
START_BIT_LEN,
COMMENTS,
sysdate,
USING,
LIFECYCLE,
username);
END IF;
END IF;
insert into ext_code (id) values (VM_CODEID); --扩展表上这个还是要的!!
insert into S_CODEOPERATELOG
(id,
WORKORDERNUMBER,
OPERATOR,
OPERATIONCONTENT,
OPERATIONDATE,
CODE_ID,
CODE_NAME)
values
(seq_s_codeoperatelog.nextval,
ORDERNUM,
username,
DESCNAME || i,
sysdate,
VM_CODEID,
i);

/* END IF;*/

i := i + 1;
end loop;
/* 号码段扩容 Purdo1.0-8707 */
SELECT CP.STARTCODE
INTO CODEPOOL_START
FROM C_CODEPOOL CP
WHERE ID = codepoolid;
SELECT CP.ENDCODE
INTO CODEPOOL_END
FROM C_CODEPOOL CP
WHERE ID = codepoolid;
IF (startcode < CODEPOOL_START) THEN
CODEPOOL_START := startcode;
UPDATE C_CODEPOOL CP
SET CP.STARTCODE = CODEPOOL_START
WHERE CP.ID = codepoolid;
END IF;
IF (endcode > CODEPOOL_END) THEN
CODEPOOL_END := endcode;
UPDATE C_CODEPOOL CP
SET CP.Endcode = CODEPOOL_END
WHERE CP.ID = codepoolid;
END IF;
/* Purdo1.0-8707 end */
PKG_CODE.CodeAnalyze(to_number(startcode),
to_number(endcode),
codetype,
username,
VM_MES);

END;