[오라클] 뷰테이블 조회 VIEW_NEWSAWON

구글 이미지

 

 

-- SYS에서

-- GRANT CREATE VIEW TO NEWSCOTT;

 

CREATE OR REPLACE VIEW VIEW_NEWSAWON

 -- 뷰테이블을 만드는데 없으면 만들고 있으면 이것으로 대체 해라

 

AS

SELECT T.SANO AS "사원번호",

            T.NAME AS "사원명",

            T.JUBUN AS "주민번호",

            T.GENDER AS "성별",

            T.AGE AS "현재나이",

            TRUNC(T.AGE, -1) AS "연령대",

            T.YEARPAY AS "연봉",

            T.HIREDATE AS "입사일자",

 

            TO_CHAR(

            ADD_MONTHS(SYSDATE, (60-T.AGE) * 12)

                          ,'YYYY') ||

            TO_CHAR(T.HIREDATE, '-MM-DD')

            AS "정년퇴직일",

 

     TRUNC(

     ( T.YEARPAY / 12 ) *

     TRUNC( MONTHS_BETWEEN( TO_DATE( TO_CHAR(

            ADD_MONTHS(SYSDATE, (60-T.AGE) * 12)

                          ,'YYYY') ||

            TO_CHAR(T.HIREDATE, '-MM-DD'), 'YYYY-MM-DD'),

                 T.HIREDATE) / 12 )

      ) AS "퇴직금",

 

     TRUNC( SYSDATE - T.HIREDATE )  AS "근무일수",

 

     TRUNC( TO_DATE(TO_CHAR(

            ADD_MONTHS(SYSDATE, (60-T.AGE) * 12)

                          ,'YYYY') ||

            TO_CHAR(T.HIREDATE, '-MM-DD'),'YYYY-MM-DD')

               - SYSDATE ) AS "남은일수"

 

FROM

(

SELECT SANO, NAME, JUBUN,

 

         CASE

         WHEN SUBSTR(JUBUN, 7, 1) IN ('1','3') THEN '남'

         ELSE '여' END  AS "GENDER",

 

         EXTRACT(YEAR FROM SYSDATE)

          - (CASE

          WHEN SUBSTR(JUBUN, 7, 1) IN ('1','2')

          THEN TO_NUMBER( SUBSTR(JUBUN, 1, 2) ) + 1900

          ELSE TO_NUMBER( SUBSTR(JUBUN, 1, 2) ) + 2000

          END )

          + 1 AS "AGE",

 

          NVL2(COMM, SAL*12 + COMM, SAL*12) AS "YEARPAY",

 

          HIREDATE

 

FROM TBL_NEWSAWON

) T; --여기까지 테이블 뷰

 

 

<테이블 뷰 확인>

SELECT *

FROM VIEW_NEWSAWON;

 

 

SELECT 사원번호, 사원명, 주민번호

FROM VIEW_NEWSAWON

WHERE 성별 = '여' AND 퇴직금 >= 100000;