오라클 컬럼추가/삭제/수정 & 날짜컬럼 변형 및 조작 & 오라클함수생성 방법

(1) 기존테이블에 컬럼 추가

alter table 테이블명 

  add ( 컬럼명1 DATE   Default Sysdate NOT NULL

      , 컬럼명2 DATE   Default Sysdate NOT NULL  )

alter table 테이블명

  add (컬럼명 VARCHAR2 (3))     

(2) 기존 테이블 컬럼의 속성 변경 예

--컬럼이 비어져있어야 한다는 전제조건 있음

alter table 테이블명

modify ( 컬럼명 VARCHAR2(20)) 

(3) 기존 테이블 컬럼 삭제

alter table 테이블명

drop column 컬럼명

(4) 기준 테이블 복수개 컬럼 삭제

alter table 테이블명

 drop (컬럼명1, 컬럼명2)

------------------------------------------------------------------------

참고

(1) 특정 값을 select 해와서 컬럼 값을 수정하고 싶을 때

Update 테이블명1 a    -- a : alias (별명지정)  

   set 수정컬럼명 = (       -- SubQuery      

       Select 컬럼

         From 테이블명2 b 

         Where b.profno  = a.profno 

          And rownum    = 1 

       )

     , Audit_Date  = Sysdate

(2) 두 날짜 사이의 기간 계산

Select  MONTHS_BETWEEN(Sysdate, hiredate),   -- 주어진 두 날짜 사이의 기간 계산  

       ADD_MONTHS(hiredate, 6) 입사_6개월후,         -- 주어진 날짜에서 몇 개월을 더한 날짜를 추출

  From Professor 

 Where MONTHS_BETWEEN(Sysdate, hiredate) <= 120

(3) 날짜관련 

Select profno, hiredate,   -- hiredate : date 

       hiredate + 30   입사_30일후, 

       hiredate + 60   입사_60일후, 

       hiredate - 90   입사_90일전, 

       TO_CHAR(hiredate,'YYYY-MM-DD HH24:MI:SS') 입사일자,    -- MI : 분의 의미, MM : 월의 의미

       TO_CHAR(Sysdate,'YYYY-MM-DD HH24:MI:SS') 현재일자,     -- 12시간 포맷 : HH12:MI:SS 이렇게 나타내는데 12는 생략가능하다.

       TO_CHAR(Sysdate,'Q') 일자_Querter,                     

       TO_CHAR(Sysdate,'WW') 연초_몇주,

       TO_CHAR(Sysdate,'W') 월초_몇주,

       TO_CHAR(Sysdate,'DDD') 연초_몇일,

       TO_CHAR(Sysdate,'D') 주초_무슨요일,  -- 1 : 일, 2 : 월, 3: 화, 4 : 수, 5 : 목, 6 : 금, 7 토, 1: 일요일부터 출발한다. 

       TO_CHAR(LAST_DAY(Sysdate),'YYYY-MM-DD')  금월_마지막일,

       TO_CHAR(NEXT_DAY(Sysdate,'일'),'YYYY-MM-DD') 돌아오는_일요일,

       TO_CHAR(NEXT_DAY(Sysdate,7),'YYYY-MM-DD') 돌아오는_토요일2,    -- 1: 일요일부터 출발한다. 

       TO_CHAR(Sysdate,'YYYY-MM-DD HH:MI:SS AM') 현재일자_5,

       TO_CHAR(Sysdate,'YYYY-MM-DD HH:MI:SS PM') 현재일자_6, 

       TO_CHAR(ROUND(Sysdate),'YYYY-MM-DD HH:MI:SS AM') 현재일자_반올림,

       TO_CHAR(TRUNC(Sysdate),'YYYY-MM-DD HH:MI:SS AM') 현재일자_절삭 

  From 테이블명

(4) 사용자 정의 함수생성 및 테스트(1)

CREATE OR REPLACE FUNCTION 함수명-- 사용자 정의 함수 생성

   RETURN VARCHAR2                          -- 리턴값 정의

IS 

   v_SYSDATE VARCHAR2(20);

BEGIN

   SELECT to_char(SYSDATE, 'yyyymmddhh24miss')

     INTO v_SYSDATE

     FROM DUAL

   ;

   RETURN(v_SYSDATE);

   

EXCEPTION

   WHEN OTHERS THEN

       RETURN(NULL);

END;

/

Select 함수명-- 사용자 함수 테스트 

  From Dual ; 

(5) 사용자 정의 함수생성 및 테스트(2)

함수에 NUM타입의 데이터를 받아 WHERE 조건절에 파라미터로 쿼리수행

결과값을 T_AGE에 셋팅하여 RETURN하는 함수

CREATE OR REPLACE FUNCTION 함수명2 (P_IDNUM NUMBER)

 RETURN CHAR

 IS 

    I_AGE INT;

    T_AGE CHAR(7);

 BEGIN

    SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - TO_NUMBER(EXTRACT(YEAR FROM BIRTHDATE)) + 1

           INTO I_AGE

      FROM STUDENT

     WHERE IDNUM = P_IDNUM

       AND ROWNUM = 1;

        

    IF I_AGE < 20 THEN 

        T_AGE :='10 대';

    ELSIF I_AGE < 30 THEN 

        T_AGE :='20 대';

    ELSIF I_AGE < 40 THEN 

        T_AGE := '30 대';

    ELSIF I_AGE < 50 THEN 

        T_AGE := '40 대';

    ELSIF I_AGE < 60 THEN 

        T_AGE := '50 대';

    ELSE 

        T_AGE := '기타';

    END IF;     

        

    RETURN (T_AGE);

EXCEPTION

    WHEN OTHERS THEN

    RETURN (NULL);

END;

/