在98行UPDATE pm_role_temp SET lev = -3 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
发生死锁,望高手解决。CREATE OR REPLACE PROCEDURE AIT_ROLE_CHECKBEFOREMODIFYROLE (ip_userid pm_role_temp.Userid%TYPE,
ip_filename pm_role_temp.filename%TYPE,
ip_casesensitive varchar2
)
IS
CURSOR getdata IS
SELECT b.compid,
b.roleid,
b.NAME,
b.parentid,
b.levelid,
b.code,
a.PARENT,
a.description,
a.party,
a.companyName as tempCompName,
c.name as formatCompName
FROM pm_role_temp a,
pm_role b,
pm_company c
WHERE a.NAME = b.NAME
AND a.companyname = c.name
AND b.compid = c.compid
AND a.filename = ip_filename
AND a.userid=ip_userid
AND b.status!=2
AND c.status!=0
ORDER BY b.compid,
b.levelid,
b.roleid;
CURSOR getdata_insensi IS
SELECT b.compid,
b.roleid,
b.NAME,
b.parentid,
b.levelid,
b.code,
a.PARENT,
a.description,
a.party,
a.companyName as tempCompName,
c.name as formatCompName
FROM pm_role_temp a,
pm_role b,
pm_company c
WHERE a.NAME = b.NAME
AND lower(a.companyname) = lower(c.name)
AND b.compid = c.compid
AND a.filename = ip_filename
AND a.userid=ip_userid
AND b.status!=2
AND c.status!=0
ORDER BY b.compid,
b.levelid,
b.roleid;
v_maxcode pm_role.code%TYPE;
CURSOR getchildrole(p_mincode pm_role.code%TYPE, p_maxcode pm_role.code%TYPE) IS
SELECT compid,
roleid,
parentid,
levelid,
code
FROM pm_role
WHERE code > p_mincode
AND code <= p_maxcode
AND status!=2
ORDER BY compid,
levelid,
roleid;
v_newparentroleid pm_role.roleid%TYPE;
v_originallevid PLS_INTEGER;
v_newparentlevid PLS_INTEGER;
v_maxlevelid PLS_INTEGER;
v_hascheck PLS_INTEGER:=0;
BEGIN
IF lower(ip_casesensitive) = 'true' THEN
FOR rec IN getdata LOOP
v_hascheck:=0;
IF rec.PARENT IS NOT NULL THEN
v_maxcode := rpad(substr(rec.code, 1, (length(rec.compid) +
(rec.levelid * 3))), (length(rec.compid) + 30), '9');
BEGIN
SELECT a.roleid
INTO v_newparentroleid
FROM pm_role a,pm_company b
WHERE a.NAME = rec.PARENT AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
FOR recchildrole IN getchildrole(rec.code, v_maxcode) LOOP
IF recchildrole.roleid = v_newparentroleid THEN
dbms_output.put_line('new parent role equal to child role!');
UPDATE pm_role_temp SET lev = -2 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
v_hascheck:=1;
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('new parent is not an valid role!');
UPDATE pm_role_temp SET lev = -3 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
v_hascheck:=1;
END;
IF v_hascheck = 0 THEN
SELECT a.levelid
INTO v_newparentlevid
FROM Pm_Role a,pm_company b
WHERE a.NAME = rec.PARENT AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
SELECT a.levelid INTO v_originallevid FROM Pm_Role a,pm_company b WHERE a.NAME = rec.NAME AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
SELECT MAX(levelid)
INTO v_maxlevelid
FROM pm_role
WHERE code >= rec.code
AND code <= v_maxcode
AND status !=2;
IF v_maxlevelid + (v_newparentlevid + 1 - v_originallevid) > 10 THEN
dbms_output.put_line('new level is exceed 10!');
UPDATE pm_role_temp SET lev = 11 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
END IF;
END IF;
END IF;
END LOOP;
ELSE
FOR rec IN getdata_insensi LOOP
v_hascheck:=0;
IF rec.PARENT IS NOT NULL THEN
v_maxcode := rpad(substr(rec.code, 1, (length(rec.compid) +
(rec.levelid * 3))), (length(rec.compid) + 30), '9');
BEGIN
SELECT a.roleid
INTO v_newparentroleid
FROM pm_role a,pm_company b
WHERE a.NAME = rec.PARENT AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
FOR recchildrole IN getchildrole(rec.code, v_maxcode) LOOP
IF recchildrole.roleid = v_newparentroleid THEN
dbms_output.put_line('new parent role equal to child role!');
UPDATE pm_role_temp SET lev = -2 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
v_hascheck:=1;
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('new parent is not an valid role!');
UPDATE pm_role_temp SET lev = -3 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
v_hascheck:=1;
END;
IF v_hascheck = 0 THEN
SELECT a.levelid
INTO v_newparentlevid
FROM Pm_Role a,pm_company b
WHERE a.NAME = rec.PARENT AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
SELECT a.levelid INTO v_originallevid FROM Pm_Role a,pm_company b WHERE a.NAME = rec.NAME AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
SELECT MAX(levelid)
INTO v_maxlevelid
FROM pm_role
WHERE code >= rec.code
AND code <= v_maxcode
AND status !=2;
IF v_maxlevelid + (v_newparentlevid + 1 - v_originallevid) > 10 THEN
dbms_output.put_line('new level is exceed 10!');
UPDATE pm_role_temp SET lev = 11 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
END IF;
END IF;
END IF;
END LOOP;
END IF;
END AIT_ROLE_CHECKBEFOREMODIFYROLE;
发生死锁,望高手解决。CREATE OR REPLACE PROCEDURE AIT_ROLE_CHECKBEFOREMODIFYROLE (ip_userid pm_role_temp.Userid%TYPE,
ip_filename pm_role_temp.filename%TYPE,
ip_casesensitive varchar2
)
IS
CURSOR getdata IS
SELECT b.compid,
b.roleid,
b.NAME,
b.parentid,
b.levelid,
b.code,
a.PARENT,
a.description,
a.party,
a.companyName as tempCompName,
c.name as formatCompName
FROM pm_role_temp a,
pm_role b,
pm_company c
WHERE a.NAME = b.NAME
AND a.companyname = c.name
AND b.compid = c.compid
AND a.filename = ip_filename
AND a.userid=ip_userid
AND b.status!=2
AND c.status!=0
ORDER BY b.compid,
b.levelid,
b.roleid;
CURSOR getdata_insensi IS
SELECT b.compid,
b.roleid,
b.NAME,
b.parentid,
b.levelid,
b.code,
a.PARENT,
a.description,
a.party,
a.companyName as tempCompName,
c.name as formatCompName
FROM pm_role_temp a,
pm_role b,
pm_company c
WHERE a.NAME = b.NAME
AND lower(a.companyname) = lower(c.name)
AND b.compid = c.compid
AND a.filename = ip_filename
AND a.userid=ip_userid
AND b.status!=2
AND c.status!=0
ORDER BY b.compid,
b.levelid,
b.roleid;
v_maxcode pm_role.code%TYPE;
CURSOR getchildrole(p_mincode pm_role.code%TYPE, p_maxcode pm_role.code%TYPE) IS
SELECT compid,
roleid,
parentid,
levelid,
code
FROM pm_role
WHERE code > p_mincode
AND code <= p_maxcode
AND status!=2
ORDER BY compid,
levelid,
roleid;
v_newparentroleid pm_role.roleid%TYPE;
v_originallevid PLS_INTEGER;
v_newparentlevid PLS_INTEGER;
v_maxlevelid PLS_INTEGER;
v_hascheck PLS_INTEGER:=0;
BEGIN
IF lower(ip_casesensitive) = 'true' THEN
FOR rec IN getdata LOOP
v_hascheck:=0;
IF rec.PARENT IS NOT NULL THEN
v_maxcode := rpad(substr(rec.code, 1, (length(rec.compid) +
(rec.levelid * 3))), (length(rec.compid) + 30), '9');
BEGIN
SELECT a.roleid
INTO v_newparentroleid
FROM pm_role a,pm_company b
WHERE a.NAME = rec.PARENT AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
FOR recchildrole IN getchildrole(rec.code, v_maxcode) LOOP
IF recchildrole.roleid = v_newparentroleid THEN
dbms_output.put_line('new parent role equal to child role!');
UPDATE pm_role_temp SET lev = -2 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
v_hascheck:=1;
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('new parent is not an valid role!');
UPDATE pm_role_temp SET lev = -3 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
v_hascheck:=1;
END;
IF v_hascheck = 0 THEN
SELECT a.levelid
INTO v_newparentlevid
FROM Pm_Role a,pm_company b
WHERE a.NAME = rec.PARENT AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
SELECT a.levelid INTO v_originallevid FROM Pm_Role a,pm_company b WHERE a.NAME = rec.NAME AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
SELECT MAX(levelid)
INTO v_maxlevelid
FROM pm_role
WHERE code >= rec.code
AND code <= v_maxcode
AND status !=2;
IF v_maxlevelid + (v_newparentlevid + 1 - v_originallevid) > 10 THEN
dbms_output.put_line('new level is exceed 10!');
UPDATE pm_role_temp SET lev = 11 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
END IF;
END IF;
END IF;
END LOOP;
ELSE
FOR rec IN getdata_insensi LOOP
v_hascheck:=0;
IF rec.PARENT IS NOT NULL THEN
v_maxcode := rpad(substr(rec.code, 1, (length(rec.compid) +
(rec.levelid * 3))), (length(rec.compid) + 30), '9');
BEGIN
SELECT a.roleid
INTO v_newparentroleid
FROM pm_role a,pm_company b
WHERE a.NAME = rec.PARENT AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
FOR recchildrole IN getchildrole(rec.code, v_maxcode) LOOP
IF recchildrole.roleid = v_newparentroleid THEN
dbms_output.put_line('new parent role equal to child role!');
UPDATE pm_role_temp SET lev = -2 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
v_hascheck:=1;
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('new parent is not an valid role!');
UPDATE pm_role_temp SET lev = -3 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
v_hascheck:=1;
END;
IF v_hascheck = 0 THEN
SELECT a.levelid
INTO v_newparentlevid
FROM Pm_Role a,pm_company b
WHERE a.NAME = rec.PARENT AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
SELECT a.levelid INTO v_originallevid FROM Pm_Role a,pm_company b WHERE a.NAME = rec.NAME AND a.compid=b.compid AND b.name = rec.formatcompname AND a.status!=2 AND b.status!=0;
SELECT MAX(levelid)
INTO v_maxlevelid
FROM pm_role
WHERE code >= rec.code
AND code <= v_maxcode
AND status !=2;
IF v_maxlevelid + (v_newparentlevid + 1 - v_originallevid) > 10 THEN
dbms_output.put_line('new level is exceed 10!');
UPDATE pm_role_temp SET lev = 11 WHERE name=rec.name and companyname=rec.tempcompname and userid=ip_userid and filename=ip_filename;
END IF;
END IF;
END IF;
END LOOP;
END IF;
END AIT_ROLE_CHECKBEFOREMODIFYROLE;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货