PL/SQL oracle IF문 연습

PL/SQL 문연습

 

오라클 if문, REPLACE 함수 사용

 

휴대폰번호로 deptid를 찾아 뎁트아이디 첫번째 글자로

표기함
 

DECLARE USERID VARCHAR2(10):=NULL;
 BEGIN 
 SELECT a.DEPT_ID INTO USERID FROM ORGDBADM.TCN_USERINFORMATION_BASIC a, ORGDBADM.TCN_USERINFORMATION_ADDR b WHERE a.USER_UID = b.USER_ID 
        AND REPLACE (REPLACE (REPLACE (REPLACE (REPLACE(REPLACE(b.MOBILE,' ',NULL),'~',NULL),'-',NULL),'.',NULL),'(',NULL),')', NULL) = '01090999813';
 
     IF  substr(USERID,1,1)='H' 
     THEN  USERID := 'AA_GWCN';
     ELSIF substr(USERID,1,1)='6' 
     THEN USERID := 'HL_GWCN'; 
     ELSIF substr(USERID,1,1)='3' 
     THEN USERID := 'KN_GWCN'; 
     ELSIF substr(USERID,1,1)='4' 
     THEN USERID := 'CC_GWCN'; 
     ELSIF substr(USERID,1,1)='2' 
     THEN USERID := 'HG_GWCN'; 
     ELSIF substr(USERID,1,1)='5' 
     THEN USERID := 'KD_GWCN'; 
     ELSIF substr(USERID,1,1)='A' 
     THEN USERID := 'AA_GWCN'; 
     ELSIF substr(USERID,1,1)='8' 
     THEN USERID := 'AA_GWCN'; 
     ELSIF substr(USERID,1,1)='9' 
     THEN USERID := 'DT_GWCN'; 
     ELSE 
     USERID := 'DPTNMERR';
     END IF;
  
 dbms_output.PUT_LINE(USERID);
 
 
    END;