Ultimus解決一身兼多職的SQL

CREATE OR REPLACE PROCEDURE Get_UserOCidBy_Flag
 (
 OC_ID VARCHAR2,
 TYPE_T INT,
 SUPERVISOR_ID  OUT VARCHAR2,
 OC OUT INT
)
AS
 SUPERVISORID_V INT;
 ISPRIMAYR_V INT;
 OC_ID_V INT;
 OC_FLAG INT;
 NAME_V VARCHAR2(255);
 JOBFUNCTION_V VARCHAR2(255);
 OC_GRADE_V INT;
 JOBFUNCTION_V_N VARCHAR2(255);
 OC_GRADE_V_N INT;
 DEPT_NO VARCHAR2(255);
 V_DEPT_NO VARCHAR2(255);
 V_DEPT_NO_OLD VARCHAR2(255);
 V_RESULT VARCHAR2(255);
 DEPT_CNAME VARCHAR2(255);
 I INT;
 J INT;
 OC_GRADE_V_V INT;
 K INT;
BEGIN
 SELECT SUPERVISORID,ISPRIMARY,NAME,JOBFUNCTION INTO SUPERVISORID_V,ISPRIMAYR_V,NAME_V,JOBFUNCTION_V FROM WORKFLOW.JOBS WHERE ID =(''||OC_ID||'');
 I:=0;
 J:=TYPE_T;
 K:=10;
  IF J=8 THEN
   V_DEPT_NO:='Nothing';
  IF TRIM(ISPRIMAYR_V)=0 THEN
   SELECT TRIM(DPT_ID) INTO DEPT_NO FROM RF_EMP_POS_ADD WHERE TRIM(EMP_ID)=TRIM(NAME_V) and trim(pos_id)=trim(JOBFUNCTION_V);
  ELSE
  SELECT TRIM(DPT_ID) INTO DEPT_NO FROM RF_EMPLOYEE WHERE TRIM(EMP_NTACCNT)=TRIM(NAME_V);
  END IF;  
  WHILE (V_DEPT_NO IS NOT NULL) LOOP  
  V_DEPT_NO_OLD:=V_DEPT_NO;
  V_DEPT_NO:=DEPT_NO;
  SELECT TRIM(DPT_PDPT_ID) INTO DEPT_NO  FROM RF_DEPT  WHERE TRIM(DPT_ID)=DEPT_NO;
      IF DEPT_NO IS NULL THEN
         DEPT_NO:=V_DEPT_NO_OLD;
        V_DEPT_NO:=NULL;          
      ELSE
       V_DEPT_NO_OLD:=V_DEPT_NO;
      END IF;
  END LOOP;
  SUPERVISOR_ID:=DEPT_NO;
  OC:=1;
  RETURN;
  END IF;
  IF J=9 THEN
   V_DEPT_NO:='Nothing';
  IF TRIM(ISPRIMAYR_V)=0 THEN
   SELECT TRIM(DPT_ID) INTO DEPT_NO FROM RF_EMP_POS_ADD WHERE TRIM(EMP_ID)=TRIM(NAME_V) and trim(pos_id)=trim(JOBFUNCTION_V);
  ELSE
  SELECT TRIM(DPT_ID) INTO DEPT_NO FROM RF_EMPLOYEE WHERE TRIM(EMP_NTACCNT)=TRIM(NAME_V);
  END IF;  
  WHILE (V_DEPT_NO IS NOT NULL) LOOP
  V_DEPT_NO_OLD:=V_DEPT_NO;
  V_DEPT_NO:=DEPT_NO;
  SELECT TRIM(DPT_PDPT_ID) INTO DEPT_NO  FROM RF_DEPT  WHERE TRIM(DPT_ID)=DEPT_NO;
      IF DEPT_NO IS NULL THEN
         DEPT_NO:=V_DEPT_NO;
        V_DEPT_NO:=NULL;          
      ELSE
       V_DEPT_NO_OLD:=V_DEPT_NO;
      END IF;
  END LOOP;
  SUPERVISOR_ID:=DEPT_NO;
  OC:=0;
  RETURN;
  END IF;
  IF J<7 THEN 
   IF TRIM(SUPERVISORID_V)<>TRIM(OC_ID) THEN
        OC_ID_V:=OC_ID;  
     WHILE I      SELECT SUPERVISORID,ISPRIMARY,NAME,JOBFUNCTION INTO SUPERVISORID_V,ISPRIMAYR_V,NAME_V,JOBFUNCTION_V FROM WORKFLOW.JOBS WHERE TRIM(ID) =TRIM((''||OC_ID_V||''));
     I:=I+1;
     IF OC_ID_V=TRIM(SUPERVISORID_V) THEN
      SUPERVISOR_ID:='error';
        OC:=100;
       RETURN;
     END IF;
     OC_ID_V:=TRIM(SUPERVISORID_V);
     OC_FLAG:=TRIM(ISPRIMAYR_V);    
     END LOOP;
     IF OC_FLAG=1 THEN
      SELECT OC_GRADE INTO OC_GRADE_V FROM RF_EMPLOYEE WHERE TRIM(EMP_NTACCNT)=TRIM(''||NAME_V||'');
     ELSE
       SELECT OC_GRADE INTO OC_GRADE_V FROM RF_EMP_POS_ADD WHERE TRIM(EMP_ID)=TRIM(''||NAME_V||'') AND TRIM(POS_ID)=TRIM(''||JOBFUNCTION_V||'');
     END IF;
     SUPERVISOR_ID:=TRIM(NAME_V);
     OC:=TRIM(OC_GRADE_V);
     RETURN;
   ELSE
     SUPERVISOR_ID:='error';
     OC:=100;
     RETURN;
   END IF;   
  END IF;
  IF J>10 THEN
   IF TRIM(SUPERVISORID_V)<>TRIM(OC_ID) THEN
   OC_ID_V:=OC_ID;
   SELECT SUPERVISORID,ISPRIMARY,NAME,JOBFUNCTION INTO SUPERVISORID_V,ISPRIMAYR_V,NAME_V,JOBFUNCTION_V FROM WORKFLOW.JOBS WHERE TRIM(ID) =TRIM((''||OC_ID_V||''));
   SELECT TRIM(OC_GRADE) INTO OC_GRADE_V_V FROM RF_EMPLOYEE WHERE TRIM(EMP_NTACCNT)=TRIM(NAME_V);
   IF OC_GRADE_V>20 THEN
      SUPERVISOR_ID:='error';
       OC:=-1;
      RETURN;
   ELSE
    OC_ID_V:=OC_ID;
    WHILE I    SELECT SUPERVISORID,ISPRIMARY,NAME,JOBFUNCTION INTO SUPERVISORID_V,ISPRIMAYR_V,NAME_V,JOBFUNCTION_V FROM WORKFLOW.JOBS WHERE TRIM(ID) =TRIM((''||OC_ID_V||''));
    SELECT TRIM(OC_GRADE) INTO OC_GRADE_V FROM RF_EMPLOYEE WHERE TRIM(EMP_NTACCNT)=TRIM(NAME_V);
    IF OC_GRADE_V=J THEN
     SUPERVISOR_ID:=SUPERVISORID_V;
     OC:=OC_GRADE_V_V;
     RETURN;
    END IF;
    I:=I+1;
    IF SUPERVISORID_V=OC_ID_V THEN
       SUPERVISOR_ID:='error';
           OC:=-1;
       RETURN;
    END IF;
    OC_ID_V:=SUPERVISORID_V;
    END LOOP;
   END IF;
   SUPERVISOR_ID:=TRIM(NAME_V);
   OC:=TRIM(OC_GRADE_V);
  ELSE
     SUPERVISOR_ID:='error';
     OC:=100;
     RETURN;
   END IF; 
   
  END IF;
  EXCEPTION WHEN OTHERS THEN
  SUPERVISOR_ID:='error';
  OC:=-1;   
END Get_UserOCidBy_Flag;
/

調用的函數及參數説明，因時間關係參數説明的不夠全面，後續補充

FUNCTION Get_OCSupervisor(UserOCId,OC_FLAG)
dim sp_common,SUPERVISOR_ID_V
SUPERVISOR_ID_V=UserOCId
OC_FLAG=OC_FLAG
Set sp_common = Server.CreateObject("ADODB.Command")
 with sp_common     
  .ActiveConnection = getConnectionObject("Shat_ConnectionString") 
  .CommandText = "Get_UserOCidBy_Flag"
  .Parameters.Append .CreateParameter("@OC_ID",3, 1,20,SUPERVISOR_ID_V)
  .Parameters.Append .CreateParameter("@TYPE_T",3, 1,20,OC_FLAG)
  .Parameters.Append .CreateParameter("@SUPERVISOR_ID",200, 2,255)
  .Parameters.Append .CreateParameter("@OC",3, 2,4)
  .Execute
 end with
' response.write UserOCId
 Get_OCSupervisor=sp_common(2)&","&sp_common(3)
 Set sp_common = Nothing
 END FUNCTION
 '說明：
 'UserOCId  Ultimus組織ID
 'OC_FLAG   變量 其值有：（1、2、3、4……、7、8、9）
 '該函數有兩個主要作用
  '主要功能
  '1.通過UserOCId 及所傳的變量OC_FLAG來獲得主管工號及OC
   '例如：Get_OCSupervisor(25479289,2)   返回：jacky,20
  '2.通過UserOCId 及所傳的變量OC_FLAG來獲得該人員所在的BU、BG
   '例如：Get_OCSupervisor(25479289,8)   返回：S001,1
   '例如：Get_OCSupervisor(25479289,9)   返回：SIDC,0
 '提示：當返回的結果為：0,100  說明該人員在組織裏面出現斷層，系統找不到其主管 
 'OC_FLAG變量含義：
  '1:返回申請人本人工號及OC
  '2:返回申請人直屬主管工號及OC
  '3:返回申請人直屬主管的直屬主管工號及OC
  '4、5、6、7均是在1、2、3的基礎上遞增
  '8:返回申請人當前所選職位所在BU及1
  '9:返回申請人當前所選職位所在BG及0
 '返回的結果為一個數組
 '例如：（aaaa,bb）
 '通過第二個返回值(bb)可驗證是否是所要得到的結果
 'bb=0   申請人所在BG
 'bb=1   申請人所在BU
 'bb=100 申請人在組織裏面找不到主管，組織建制有問題
 'bb=20  申請人的直屬主管
 'bb=30 申請人的部門主管
 'bb=40  申請人的處級主管
 'bb>40 申請人更高級主管

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13538095/viewspace-217001/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13538095/viewspace-217001/