데이터베이스 질문과 답변

프로시져 수정좀 도와주세요. 0 0 2,425

by DevAtom [PL/SQL] 프로시져 [2012.03.23 13:47:42]

기존에 전체 인원 자료를 

교원채용 합격자에 대해 인적마스터(STAF211)로 전환하는것을

전체인원이 아닌 STAF211.DEPT_CD IN (SELECT DEPT_CD FROM V_MEDI_DEPT050) 

이러한 조건을 걸어 위조건은  부서가 의학계열인 인원만 나오도록 하는..

걸어서 아래프로시져를 수정해보려고 하는데..

도무지 감이 전혀 오지 않습니다.. 어떤식으로 해야할지 도움좀 부탁드립니다! ㅠㅠ




------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE UDRIMS.SP_STAF130_TRANS_HURT
(
          IN_SUPP_NO                  IN         STAF130.SUPP_NO %TYPE,
          IN_REG_ID                   IN         VARCHAR2,
          IN_REG_IP                   IN         VARCHAR2,
          --  RETURN VALUE
          OUT_RTN                     OUT        INTEGER,
          OUT_STAFF_NO                OUT        STAF210.STAFF_NO%TYPE,
          OUT_MSG                     OUT        VARCHAR2
)
IS
/*******************************************************************************************************
 파일명              : SP_STAF130_TRANS_HURT
 버전                : <<1.1.1.0>>
 최초 작성일         : <<2008.06.30>>
 최초 작성자         : 김상봉
 내용                : <<교원채용 합격자에 대해 인적마스터(STAF211)로 전환한다. >>
 수정 작성일         : <<2008.06.30>>
 수정 작성자         : 김상봉
 수정내용            :
 INPUT               :
 OUTPUT              : OUT_RTN   - 리턴 코드   1: Success, -1: Fail, 0: No Data
                       OUT_MSG   - 리턴 메세지
*******************************************************************************************************/
  /********** 변수선언시작 ***********************/
--  V_SUPP_NO                        STAF211.SUPP_NO  %TYPE;
  V_STAFF211_CNT                     NUMBER( 5);                -- 인적마스터 존재여부
  V_STAFF210_CNT                     NUMBER( 5);                -- 인사마스터 존재여부
  V_PERS_NO                          STAF211.PERS_NO %TYPE;     -- 개인번호
  V_STAFF_NO                         STAF210.STAFF_NO%TYPE;     -- 교직원번호
  /********** 변수선언끝   ***********************/
  BEGIN
    /********** 인적마스터(STAF211) 등록 처리 ***********************/
    BEGIN
        SELECT COUNT(*)
        INTO V_STAFF211_CNT
        FROM STAF211
        WHERE RES_NO = (
                        SELECT RES_NO FROM STAF130 WHERE SUPP_NO = IN_SUPP_NO
                       )
        ;
    END;
    IF(V_STAFF211_CNT < 1 ) THEN
        BEGIN
            SELECT SF_STAF211_NEW_PERS_NO(), SF_STAF210_NEW_STAFF_NO(SF_STAF211_NEW_PERS_NO(), 'FP')
            INTO V_PERS_NO, V_STAFF_NO
            FROM DUAL;
            -- 개인기본(인적마스터)
            INSERT INTO STAF211
            (
              PERS_NO
            , KOR_NM
            , CHA_NM
            , ENG_NM
            , RES_NO
            , BIRTH_DT
            , LUSO_FG
            , GEN_FG
            , NATV_COUN_CD
            , ZIP_NO
            , ADDR
            , DETA_ADDR
            , ENG_ADDR
            , RELI_CD
            , HOB_NM
            , SKIL_NM
            , BUD_NM
            , PHT_MASTER_NO
            , HOME_TEL_NO
            , HAND_NO
            , EMAIL1
            , HGHT
            , WGT
            , SGHT_LF
            , SGHT_RT
            , CBLD_FG
            , BLDTP
            , HINDR_YN
            , MIL_UFSD_RESN
            , MIL_NO_NM
            , MILKD
            , MIL
            , MILBCH_FG
            , DEGR
            , ENST_DT
            , DCMI_DT
            , SPCLEX_FR_DT
            , SPCLEX_TO_DT
            , VA_FG
            , REG_ID
            , REG_DTTM
            , REG_IP
            )
            SELECT
              V_PERS_NO
            , KOR_NM
            , CHA_NM
            , ENG_NM
            , RES_NO
            , BIRTH_DT
            , LUSO_FG
            , GEN_FG
            , NATI_CD
            , ZIP_NO
            , ADDR
            , DETA_ADDR
            , FRN_ADDR
            , RELI_CD
            , HOB
            , SKIL
            , BUD_NM
            , PHT_MASTER_NO
            , HOME_TEL_NO
            , HAND_NO
            , EMAIL
            , HGHT
            , WGT
            , SGHT_LF
            , SGHT_RT
            , CBLD_FG
            , BLDTP
            , HIND_YN
            , MIL_UFSD_RESN
            , MIL_NO_NM
            , MILKD
            , MIL
            , MILBCH_FG
            , DEGR
            , ENST_DT
            , DCMI_DT
            , SPCLEX_FR_DT
            , SPCLEX_TO_DT
            , VA_FG
            , IN_REG_ID
            , SYSDATE
            , IN_REG_IP
            FROM STAF130
            WHERE SUPP_NO = IN_SUPP_NO
            ;
  
            /********* 에러처리 ROLLBACK *********************/
            EXCEPTION
            WHEN OTHERS THEN
            OUT_RTN     := -1;
            OUT_MSG     := TO_CHAR(SQLCODE)|| ' : ' || SQLERRM;
            RETURN;
        END ;
      ELSE --IF ( V_STAFF211_CNT = 1 ) THEN
 
        BEGIN
        --과거데이터중 개인마스터가 2건인 사람이 존재->개인번호가 최신인 자료를 기준으로 한다.
        SELECT  /*+ INDEX_DESC (STAF211 PK_STAF211) */  
             PERS_NO, SF_STAF210_NEW_STAFF_NO(PERS_NO, 'FP')
        INTO V_PERS_NO, V_STAFF_NO
        FROM STAF211
        WHERE RES_NO = (
                        SELECT RES_NO FROM STAF130 WHERE SUPP_NO = IN_SUPP_NO
                       )
          AND ROWNUM = 1
        ;
       
       --DBMS_OUTPUT.PUT_LINE(V_PERS_NO);  
       --DBMS_OUTPUT.PUT_LINE(V_STAFF_NO);  
 
        
            -- 개인기본(인적마스터) 정보를 업데이트 한다.
             UPDATE STAF211
                SET (CHA_NM 
                  , ENG_NM   
                  , GEN_FG
                  , NATV_COUN_CD
                  , ZIP_NO
                  , ADDR
                  , DETA_ADDR
                  , ENG_ADDR
                  , RELI_CD
                  , HOB_NM
                  , SKIL_NM
                  , BUD_NM
                  , PHT_MASTER_NO
                  , HOME_TEL_NO
                  , HAND_NO
                  , EMAIL1
                  , HGHT
                  , WGT
                  , SGHT_LF
                  , SGHT_RT
                  , CBLD_FG
                  , BLDTP
                  , HINDR_YN
                  , MIL_UFSD_RESN
                  , MIL_NO_NM
                  , MILKD
                  , MIL
                  , MILBCH_FG
                  , DEGR
                  , ENST_DT
                  , DCMI_DT
                  , SPCLEX_FR_DT
                  , SPCLEX_TO_DT
                  , VA_FG
                  , MOD_ID
                  , MOD_DTTM
                  , MOD_IP ) = 
           ( SELECT CHA_NM
                  , ENG_NM   
                  , GEN_FG
                  , NATI_CD
                  , ZIP_NO
                  , ADDR
                  , DETA_ADDR
                  , FRN_ADDR
                  , RELI_CD
                  , HOB
                  , SKIL
                  , BUD_NM
                  , PHT_MASTER_NO
                  , HOME_TEL_NO
                  , HAND_NO
                  , EMAIL
                  , HGHT
                  , WGT
                  , SGHT_LF
                  , SGHT_RT
                  , CBLD_FG
                  , BLDTP
                  , HIND_YN
                  , MIL_UFSD_RESN
                  , MIL_NO_NM
                  , MILKD
                  , MIL
                  , MILBCH_FG
                  , DEGR
                  , ENST_DT
                  , DCMI_DT
                  , SPCLEX_FR_DT
                  , SPCLEX_TO_DT
                  , VA_FG
                  , IN_REG_ID
                  , SYSDATE
                  , IN_REG_IP
               FROM STAF130
              WHERE SUPP_NO = IN_SUPP_NO )
              WHERE PERS_NO = V_PERS_NO 
            ;
                    
          /*기존 자료는 삭제하고 채용에 있는 자료를 최신으로 다시 인서트한다. */
          -- 학력사항
          DELETE FROM STAF220
           WHERE PERS_NO = V_PERS_NO 
          ;
  
          -- 경력사항
          DELETE FROM STAF230
           WHERE PERS_NO = V_PERS_NO 
          ;
  
          -- 자격면허
          DELETE FROM  STAF260
           WHERE PERS_NO = V_PERS_NO 
          ;
        
          -- 가족사항
          DELETE FROM  STAF270
           WHERE PERS_NO = V_PERS_NO 
          ;   
          
        END;  
      END IF;
      BEGIN
            -- 인사기본
            INSERT INTO STAF210
            (
              STAFF_NO
            , PERS_NO
            , STAFF_FG
            , CAMP_FG
            , DEPT_CD
            , EMPL_FG
            , WKROW_CD
            , BIZTP_CD
            , REG_ID
            , REG_DTTM
            , REG_IP
            )
            SELECT
              V_STAFF_NO
            , V_PERS_NO
            , 'FP'
            , ( SELECT CAMP_FG FROM STAF120 WHERE EMPL_FLD_NO = STAF130.EMPL_FLD_NO )
            , ( SELECT DEPT_CD FROM STAF120 WHERE EMPL_FLD_NO = STAF130.EMPL_FLD_NO )
            , 'A0019001' -- EMPL_FG  A0019001 공채 A0019002 특채
            , ( SELECT WKROW_CD FROM STAF120 WHERE EMPL_FLD_NO = STAF130.EMPL_FLD_NO ) -- WKROW_CD
            , ( SELECT BIZTP_CD FROM STAF120 WHERE EMPL_FLD_NO = STAF130.EMPL_FLD_NO ) -- BIZTP_CD
            , IN_REG_ID
            , SYSDATE
            , IN_REG_IP
            FROM STAF130
            WHERE SUPP_NO = IN_SUPP_NO
            ;

            -- 학력사항
            INSERT INTO STAF220
            (
              PERS_NO
            , SHCR_SEQ
            , SHCR_FG
            , ENROL_FR_DT
            , ENROL_TO_DT
            , SCH_NM
            , MJ_NM
            , DEGR_ACQ_DT
            , DEGR_NM
            , COUN_CD
            , FL_YN
            , REG_ID
            , REG_DTTM
            , REG_IP
            )
            SELECT
              V_PERS_NO
            , SHCR_SEQ
            , SHCR_FG
            , HOSH_FR_DT
            , NVL(HOSH_TO_DT, GRDT_DT)
            , SCH_NM  || ' ' || SUST_NM
            , MJ_NM
            , ACQ_DT
            , DEGR_NM
            , COUN_CD
            , FL_SHCR_YN
            , IN_REG_ID
            , SYSDATE
            , IN_REG_IP
            FROM STAF131
            WHERE SUPP_NO = IN_SUPP_NO
            ;
            -- 경력사항
            INSERT INTO STAF230
            (
              PERS_NO
            , CARR_SEQ
            , CARR_FR_DT
            , CARR_TO_DT
            , SITE_NM
            , WKPO_WKGD_NM
            , WK_MM_CNT
            , INCBT_YN
            , REG_ID
            , REG_DTTM
            , REG_IP
            )
            SELECT
              V_PERS_NO
            , CARR_SEQ
            , NVL(WK_FR_DT, TO_CHAR(SYSDATE, 'YYYYMMDD') )
            , NVL(WK_TO_DT, TO_CHAR(SYSDATE, 'YYYYMMDD') )
            , SITE_NM
            , WKPO_NM
            , trunc( MONTHS_BETWEEN( TO_DATE(WK_TO_DT, 'YYYYMMDD'), TO_DATE(WK_FR_DT, 'YYYYMMDD')))
            , INCBT_YN
            , IN_REG_ID
            , SYSDATE
            , IN_REG_IP
            FROM STAF132
            WHERE SUPP_NO = IN_SUPP_NO
            ;
            -- 자격면허
            INSERT INTO STAF260
            (
              PERS_NO
            , QUAL_SEQ
            , QUAL_FG
            , ACQ_DT
            , QUAL_LIC_NM
            , ISSU_BREU_NM
            , REG_ID
            , REG_DTTM
            , REG_IP
            )
            SELECT
              V_PERS_NO
            , QUAL_SEQ
            , NVL(QUAL_FG, 'xxxxxxxx')
            , ACQ_DT
            , QUAL_LIC_NM
            , PERF_BREU_NM
            , IN_REG_ID
            , SYSDATE
            , IN_REG_IP
            FROM STAF133
            WHERE SUPP_NO = IN_SUPP_NO
            ;
            
            -- 가족사항
            INSERT INTO STAF270
            (
              PERS_NO
            , FM_SEQ
            , RES_NO
            , FM_REL_CD
            , KOR_NM
            , JOB_WKPL_NM
            , LITO_YN
            , REG_ID
            , REG_DTTM
            , REG_IP
            )
            SELECT
              V_PERS_NO
            , FM_SEQ
            , '1234567890123'
            , FM_REL_CD
            , FM_KOR_NM
            , SITE_NM
            , LITO_YN
            , IN_REG_ID
            , SYSDATE
            , IN_REG_IP
            FROM STAF135
            WHERE SUPP_NO = IN_SUPP_NO
            ;            
             
            --이관완료 업데이트 
            UPDATE STAF130 SET TRANS_YN = '1', STAFF_NO = V_STAFF_NO
            WHERE  SUPP_NO = IN_SUPP_NO
            ;
            /********* 에러처리 ROLLBACK *********************/
            EXCEPTION
            WHEN OTHERS THEN
            OUT_RTN     := -1;
            OUT_MSG     := TO_CHAR(SQLCODE)|| ' : ' || SQLERRM;
            RETURN;
      END;
    /******** 성공처리 COMMIT *********************/
      OUT_RTN      := 1;
      OUT_MSG      := '처리에 성공하였습니다.';
      OUT_STAFF_NO := V_STAFF_NO;
      RETURN;
END SP_STAF130_TRANS_HURT;
/
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입