oracle 过程实例 函数 以及table的封装

过程实例已备后期回顾

CREATE OR REPLACE PROCEDURE NRMS.P_UPDATE_FIBERPATH(IN_OSID NUMBER,IN_NSID NUMBER,IN_MOVESID NUMBER) IS

V_NUM NUMBER(5);
V_NEWSID NUMBER(14);
V_CITYCODE VARCHAR2(5);
V_GROUPID  NUMBER(14);

CURSOR CR_ATERMINAL IS SELECT * FROM S_FIBERPATH WHERE A_TERMINAL IN(
    SELECT CSID FROM R_CONTAIN WHERE C_CODE IN('TERMINAL','PORT') START WITH PSID=IN_MOVESID CONNECT BY PSID = PRIOR CSID
    UNION SELECT CSID FROM R_CONTAIN WHERE CSID=IN_MOVESID AND C_CODE IN('TERMINAL','PORT'));

CURSOR CR_ZTERMINAL IS SELECT * FROM S_FIBERPATH WHERE Z_TERMINAL IN(
    SELECT CSID FROM R_CONTAIN WHERE C_CODE IN('TERMINAL','PORT') START WITH PSID=IN_MOVESID CONNECT BY PSID = PRIOR CSID
    UNION SELECT CSID FROM R_CONTAIN WHERE CSID=IN_MOVESID AND C_CODE IN('TERMINAL','PORT'));

BEGIN
    SELECT F_GETTOP_EXCEPTSTATION(IN_NSID) INTO V_NEWSID FROM DUAL;
    SELECT CITY_CODE INTO V_CITYCODE FROM R_CONTAIN WHERE CSID=IN_MOVESID;
    V_NUM:=0;
    FOR C1 IN CR_ATERMINAL LOOP
         
        
        BEGIN 
            SELECT SID INTO V_GROUPID FROM S_FIBERPATH_GROUP WHERE A_SECTOP=V_NEWSID AND Z_SECTOP=C1.Z_SECTOP 
            OR  A_SECTOP=C1.Z_SECTOP AND Z_SECTOP=V_NEWSID;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                SELECT SEQ_FIBERPATH_GROUP.NEXTVAL INTO V_GROUPID FROM DUAL;
                INSERT INTO S_FIBERPATH_GROUP (SID,FIBERPATH_NUM,A_SECTOP,Z_SECTOP,CITY_CODE) 
                VALUES(V_GROUPID,0,V_NEWSID,C1.Z_SECTOP,V_CITYCODE);        
        END;

        --+1   
        UPDATE S_FIBERPATH_GROUP SET FIBERPATH_NUM=FIBERPATH_NUM+1 WHERE SID=V_GROUPID;

        --  -1
        SELECT FIBERPATH_NUM INTO V_NUM FROM S_FIBERPATH_GROUP WHERE SID=C1.GROUP_ID;
        
        IF V_NUM -1 <=0 THEN
            DELETE S_FIBERPATH_GROUP WHERE SID=C1.GROUP_ID;
        ELSE
            UPDATE S_FIBERPATH_GROUP SET FIBERPATH_NUM=FIBERPATH_NUM-1 WHERE SID=C1.GROUP_ID;
        END IF;
        
        --按 sid 改 fiberpath
        UPDATE S_FIBERPATH SET A_SECTOP=V_NEWSID,GROUP_ID=V_GROUPID WHERE SID=C1.SID;
    END LOOP;

    V_NUM:=0;
    FOR C2 IN CR_ZTERMINAL LOOP
        
        BEGIN
            SELECT SID INTO V_GROUPID FROM S_FIBERPATH_GROUP WHERE A_SECTOP=C2.A_SECTOP AND Z_SECTOP=V_NEWSID 
            OR A_SECTOP=V_NEWSID AND Z_SECTOP=C2.A_SECTOP;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                SELECT SEQ_FIBERPATH_GROUP.NEXTVAL INTO V_GROUPID FROM DUAL;
                INSERT INTO S_FIBERPATH_GROUP (SID,FIBERPATH_NUM,A_SECTOP,Z_SECTOP,CITY_CODE) 
                VALUES(V_GROUPID,0,C2.A_SECTOP,V_NEWSID,V_CITYCODE); 
        END; 
        
        -- +1   
        UPDATE S_FIBERPATH_GROUP SET FIBERPATH_NUM=FIBERPATH_NUM+1 WHERE SID=V_GROUPID;
        
        --  -1
        SELECT FIBERPATH_NUM INTO V_NUM FROM S_FIBERPATH_GROUP WHERE SID=C2.GROUP_ID;
        
        IF V_NUM -1 <=0 THEN
            DELETE S_FIBERPATH_GROUP WHERE SID=C2.GROUP_ID;
        ELSE
            UPDATE S_FIBERPATH_GROUP SET FIBERPATH_NUM=FIBERPATH_NUM-1 WHERE SID=C2.GROUP_ID;
        END IF;
        
         --按 sid 改 fiberpath
        UPDATE S_FIBERPATH SET Z_SECTOP=V_NEWSID,GROUP_ID=V_GROUPID WHERE SID=C2.SID;
    
    END LOOP;


   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END P_UPDATE_FIBERPATH;

函数

CREATE OR REPLACE FUNCTION NRMS.f_cpid2name(in_cpid NUMBER )
RETURN VARCHAR2
AS
    v_name VARCHAR2(200);
    cursor cr is  select sname  from s_checkpoint where sid=in_cpid;
BEGIN
    v_name:='';
  /**传入 正的sid 根据流程id 找到其对应的名称**/
   for c1 in cr loop
   v_name:= c1.sname;
   exit;
 end loop; 
 RETURN v_name;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END f_cpid2name;

类型 table分装

• 封装类型
• 封装table

DROP TYPE IPRAN.TYPE_REC;

CREATE OR REPLACE TYPE IPRAN.TYPE_REC is object (ID VARCHAR2(30), 
    APPLY_ID varchar2(30), 
    FLOWID VARCHAR2(30),
    FLOWNAME VARCHAR2(100),
    TASKNAME VARCHAR2(100),
    STARTDATE VARCHAR2(50),
    ENDDATE VARCHAR2(50),
    YQWCDATE VARCHAR2(50),
    STS VARCHAR2(30),
    TASKOWNER VARCHAR2(20),
    STATE VARCHAR2(20)
);

DROP TYPE IPRAN.TYPE_TB;

CREATE OR REPLACE TYPE IPRAN.TYPE_TB is table of TYPE_REC;