create or replace procedure L_P_ONU_VER isT_SQL VARCHAR2(4000); CUR1 PKG_TYPE_DEFINE.TYPE_REF_CURSOR; TYPE V_ONU_VIEW IS TABLE OF ONU_VER_VIEW%ROWTYPE; T_ONU_VIEW V_ONU_VIEW; T_ONU_VER ONU_VER_TEST%ROWTYPE; BEGIN
T_SQL :='SELECT * FROM ONU_VER_VIEW WHERE 1=1'; --给游标用到的数据
OPEN CUR1 FOR T_SQL; LOOP FETCH CUR1 BULK COLLECT INTO T_ONU_VIEW LIMIT 1000; EXIT WHEN T_ONU_VIEW.COUNT=0; FOR I IN 1 .. T_ONU_VIEW.COUNT LOOP BEGIN T_ONU_VER:=NULL;
-- T_ONU_VER.SN BEGIN SELECT O.LOGIC_ID, CASE WHEN A.SN=T_ONU_VER.LOGIC_ID THEN 'Y' ELSE 'N' END SN INTO T_ONU_VER.LOGIC_ID, T_ONU_VER.SN FROM ONU_VER_VIEW O LEFT JOIN SERVICE.INTF_DEVICE_ALL A ON A.SN=O.LOGIC_ID WHERE O.LOGIC_ID=T_ONU_VER.LOGIC_ID; EXCEPTION WHEN OTHERS THEN NULL; END; -- T_ONU_VER.KD_ID,YY_ID,GQ_ID IF T_ONU_VER.SN='Y'THEN BEGIN SELECT IO1.ACCESSNO KD_ID, IO2.ACCESSNO YY_ID, IO3.ACCESSNO GQ_ID INTO T_ONU_VER.KD_ID, T_ONU_VER.YY_ID, T_ONU_VER.GQ_ID FROM T_RES_DEVICE D LEFT JOIN SERVICE.INTF_ORDERINFO IO1 ON D.DEV_NAME=IO1.ACCESSNO AND IO1.PRODUCTID NOT IN ('1','3','13','3816','1004','500000165','500000166','90022') LEFT JOIN SERVICE.INTF_ORDERINFO IO2 ON D.DEV_NAME=IO2.ACCESSNO AND IO2.PRODUCTID IN ('3816','1004','500000165','500000166','90022') LEFT JOIN SERVICE.INTF_ORDERINFO IO3 ON D.DEV_NAME=IO3.ACCESSNO AND IO3.PRODUCTID IN ('1','3','13','100109') WHERE D.DEV_TYPE_ID IN (7,8) AND D.IS_VALID='Y' AND D.RESERVE20=T_ONU_VER.LOGIC_ID AND ROWNUM<2; EXCEPTION WHEN OTHERS THEN NULL; END; END IF; --PON_LOID BEGIN SELECT O.LOGIC_ID, CASE WHEN A.LOGIC_ID=T_ONU_VER.LOGIC_ID THEN 'Y' ELSE 'N' END PON_LOID INTO T_ONU_VER.LOGIC_ID, T_ONU_VER.PON_LOID FROM ONU_VER_VIEW O LEFT JOIN T_RES_SYNC_ONU A ON A.LOGIC_ID=O.LOGIC_ID WHERE O.LOGIC_ID=T_ONU_VER.LOGIC_ID; EXCEPTION WHEN OTHERS THEN NULL; END; IF T_ONU_VER.PON_LOID='Y' THEN --OLT_IP,OLT_PON,ONU_ID,ONU_NAME BEGIN SELECT A.OLT_ID OLT_IP, A.PON_ID OLT_PON, A.ONU_NO ONU_ID, A.ONU_NAME ONU_NAME INTO T_ONU_VER.OLT_IP, T_ONU_VER.OLT_PON, T_ONU_VER.ONU_ID, T_ONU_VER.ONU_NAME FROM T_RES_SYNC_ONU A WHERE A.LOGIC_ID=T_ONU_VER.LOGIC_ID; EXCEPTION WHEN OTHERS THEN NULL; END;
--OLT_NAME,ONU_STATUS BEGIN SELECT D.DEV_NAME, A.IF_OPER_STATUS ONU_STATUS INTO T_ONU_VER.OLT_NAME, T_ONU_VER.ONU_STATUS FROM T_RES_DEVICE D LEFT JOIN T_RES_PORT A ON A.DEV_ID=D.DEV_ID AND A.IF_POSITION=T_ONU_VER.OLT_PON WHERE D.DEV_TYPE_ID IN (6,9) AND D.IS_VALID='Y' AND D.IP_ADDR=T_ONU_VER.OLT_IP; EXCEPTION WHEN OTHERS THEN NULL; END; END IF; select sysdate into t_ONu_ver.Last_Up_Time from dual; INSERT INTO ONU_VER_TEST VALUES T_ONU_VER;
COMMIT; DBMS_OUTPUT.put_line(t_ONu_ver.Last_Up_Time); EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; END LOOP; CLOSE CUR1;END L_P_ONU_VER;
create or replace procedure L_P_ONU_VER isT_SQL VARCHAR2(4000);
CUR1 PKG_TYPE_DEFINE.TYPE_REF_CURSOR;
TYPE V_ONU_VIEW IS TABLE OF ONU_VER_VIEW%ROWTYPE;
T_ONU_VIEW V_ONU_VIEW;
T_ONU_VER ONU_VER_TEST%ROWTYPE;
BEGIN
T_SQL :='SELECT * FROM ONU_VER_VIEW WHERE 1=1'; --给游标用到的数据
OPEN CUR1 FOR T_SQL;
LOOP
FETCH CUR1 BULK COLLECT
INTO T_ONU_VIEW LIMIT 1000;
EXIT WHEN T_ONU_VIEW.COUNT=0;
FOR I IN 1 .. T_ONU_VIEW.COUNT LOOP
BEGIN
T_ONU_VER:=NULL;
T_ONU_VER.VER_ID := T_ONU_VIEW(I).VER_ID;
T_ONU_VER.VENDOR := T_ONU_VIEW(I).VENDOR;
T_ONU_VER.MODEL := T_ONU_VIEW(I).MODEL;
T_ONU_VER.IP := T_ONU_VIEW(I).IP;
T_ONU_VER.AREA_ID := T_ONU_VIEW(I).AREA_ID;
T_ONU_VER.CON_TIME := T_ONU_VIEW(I).CON_TIME;
T_ONU_VER.REG_STATUS := T_ONU_VIEW(I).REG_STATUS;
T_ONU_VER.LOGIC_ID := T_ONU_VIEW(I).LOGIC_ID;
T_ONU_VER.HD_VERSION := T_ONU_VIEW(I).HD_VERSION;
T_ONU_VER.SW_VERSION := T_ONU_VIEW(I).SW_VERSION;
-- T_ONU_VER.SN
BEGIN
SELECT O.LOGIC_ID,
CASE WHEN A.SN=T_ONU_VER.LOGIC_ID THEN 'Y' ELSE 'N' END SN
INTO T_ONU_VER.LOGIC_ID,
T_ONU_VER.SN
FROM ONU_VER_VIEW O
LEFT JOIN SERVICE.INTF_DEVICE_ALL A ON A.SN=O.LOGIC_ID
WHERE O.LOGIC_ID=T_ONU_VER.LOGIC_ID;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- T_ONU_VER.KD_ID,YY_ID,GQ_ID
IF T_ONU_VER.SN='Y'THEN
BEGIN
SELECT IO1.ACCESSNO KD_ID,
IO2.ACCESSNO YY_ID,
IO3.ACCESSNO GQ_ID
INTO T_ONU_VER.KD_ID,
T_ONU_VER.YY_ID,
T_ONU_VER.GQ_ID
FROM T_RES_DEVICE D
LEFT JOIN SERVICE.INTF_ORDERINFO IO1
ON D.DEV_NAME=IO1.ACCESSNO
AND IO1.PRODUCTID NOT IN ('1','3','13','3816','1004','500000165','500000166','90022')
LEFT JOIN SERVICE.INTF_ORDERINFO IO2
ON D.DEV_NAME=IO2.ACCESSNO
AND IO2.PRODUCTID IN ('3816','1004','500000165','500000166','90022')
LEFT JOIN SERVICE.INTF_ORDERINFO IO3
ON D.DEV_NAME=IO3.ACCESSNO
AND IO3.PRODUCTID IN ('1','3','13','100109')
WHERE D.DEV_TYPE_ID IN (7,8)
AND D.IS_VALID='Y'
AND D.RESERVE20=T_ONU_VER.LOGIC_ID
AND ROWNUM<2;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
--PON_LOID
BEGIN
SELECT O.LOGIC_ID,
CASE WHEN A.LOGIC_ID=T_ONU_VER.LOGIC_ID THEN 'Y' ELSE 'N' END PON_LOID
INTO T_ONU_VER.LOGIC_ID,
T_ONU_VER.PON_LOID
FROM ONU_VER_VIEW O
LEFT JOIN T_RES_SYNC_ONU A ON A.LOGIC_ID=O.LOGIC_ID
WHERE O.LOGIC_ID=T_ONU_VER.LOGIC_ID;
EXCEPTION
WHEN OTHERS THEN
NULL;
END; IF T_ONU_VER.PON_LOID='Y' THEN
--OLT_IP,OLT_PON,ONU_ID,ONU_NAME
BEGIN
SELECT A.OLT_ID OLT_IP,
A.PON_ID OLT_PON,
A.ONU_NO ONU_ID,
A.ONU_NAME ONU_NAME
INTO T_ONU_VER.OLT_IP,
T_ONU_VER.OLT_PON,
T_ONU_VER.ONU_ID,
T_ONU_VER.ONU_NAME
FROM T_RES_SYNC_ONU A
WHERE A.LOGIC_ID=T_ONU_VER.LOGIC_ID;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--OLT_NAME,ONU_STATUS
BEGIN
SELECT D.DEV_NAME,
A.IF_OPER_STATUS ONU_STATUS
INTO T_ONU_VER.OLT_NAME,
T_ONU_VER.ONU_STATUS
FROM T_RES_DEVICE D
LEFT JOIN T_RES_PORT A
ON A.DEV_ID=D.DEV_ID
AND A.IF_POSITION=T_ONU_VER.OLT_PON
WHERE D.DEV_TYPE_ID IN (6,9)
AND D.IS_VALID='Y'
AND D.IP_ADDR=T_ONU_VER.OLT_IP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
select sysdate into t_ONu_ver.Last_Up_Time from dual;
INSERT INTO ONU_VER_TEST VALUES T_ONU_VER;
COMMIT;
DBMS_OUTPUT.put_line(t_ONu_ver.Last_Up_Time);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END LOOP;
CLOSE CUR1;END L_P_ONU_VER;
如果是用的plsql developer工具, 在test界面, 点击create profiler report, 然后执行过程;完成后查看profiler选项卡就行了