Oracle 09)ビュー

-- 뷰 VIEW
--자바 코딩의 간결화

--상품 판매리스트 + 상품별 판매수량 평균
DROP VIEW DAYITEM_SALES_VIEW;

CREATE VIEW DAYITEM_SALES_VIEW
AS
SELECT I.ITEMCODE, I.ITEMNAME, I.PRICE, NVL(S.AMOUNT,0) AMOUNT, REGDATE, NVL(I.BIGO,' ') BIGO, S.SALEDATE
FROM ITEM I LEFT JOIN 
        (SELECT SALEDATE, ITEMCODE, SUM(AMOUNT) AMOUNT 
        FROM SALES 
        GROUP BY ITEMCODE, SALEDATE) S 
        ON (I.ITEMCODE = S.ITEMCODE)
ORDER BY I.ITEMCODE;

--웨얼문을 따로 넣어서 날짜별 판매량 조회
SELECT * FROM DAYITEM_SALES_VIEW
WHERE SALEDATE = '2021-04-29';


--DDL
DROP VIEW MEMBER_VIEW;

--맴버 뷰 (비밀번호 가리기)
CREATE VIEW MEMBER_VIEW
AS
SELECT USERID,SUBSTR(PASSWD, 1,2)|| '**' PASSWD,  BIRTHYEAR, REGDATE FROM MEMBER;

SELECT * FROM MEMBER_VIEW;

-- 문자열 자르기 / 바꾸기
SELECT SUBSTR('1515', 1,2) || '**' FROM DUAL;


--실습) 뷰 만들기
--상품 판매리스트 + 상품별 판매수량 평균
CREATE VIEW ITEM_SALES_QTY_VIEW
AS
SELECT S.*, I.ITEMNAME, IA.QTY QTYAVG
FROM SALES S INNER JOIN ITEM I ON (I.ITEMCODE = S.ITEMCODE)
INNER JOIN (SELECT ITEMCODE, AVG(QTY) QTY FROM SALES GROUP BY ITEMCODE) IA ON (I.ITEMCODE = S.ITEMCODE);


--날짜별로 볼 수 있게
SELECT * FROM ITEM_SALES_QTY_VIEW
WHERE SALEDATE = '2021-04-30';