멋쟁이 사자처럼 23회 ( 04 / 01 )

[학습 목표]

1. VIEW를 생성하고 수정하여 복잡한 SELECT 문을 재사용 가능한가상 테이블 형태로 활용할 수 있다. 2. VIEW에서 WITH CHECK OPTION, SQL SECURITY,ALOGORITHM 등의 옵션을 이해하고 적절하게 사용할 수 있다. 3. 스토어드 프로시저와 사용자 정의 함수의 차이점과 사용 목적을 이해하고 설명할 수 있다. 4. 프로시저와 함수를 생성(Create), 호출 (Call) , 수정(Alter) , 삭제(Drop) 하는 문법을 활용할 수 있다. 5. 변수 선언 , 조건문(If) , 반복문(LOOP , WHILE, FOR), 커서 (CURSOR)를 활용하여 복잡한 로직을 구현할 수 있다.

---

VIEW 

 

1) VIEW 생성하기 및 조회하기

 

[형태]

CREATE
    [OR REPLACE]   기본뷰가 있으면 삭제하고 새로 만들겠다.  
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE_참조여러번 사용할 때 }] 뷰데이터 처리 방법 
    [DEFINER = user] DEFINER=  admin@localhost 사용자 정의
    [SQL SECURITY { DEFINER | INVOKER _ 뷰를 호출하는 사람에게 권한 }] 
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED_ 중첩허용하겠다.   | LOCAL_ 중첩 뷰는 허용하지 않는다. ] CHECK OPTION]

 

a. WITH CASCADE CHECK OPTION 은 뷰 조건을 만족하지 않는 데이터의 INSERT/UPDATE 를 차단한다. 

b. JOIN VIEW는 기본적으로 수정(INSERT/UPDATE/DELETE)이 불가하며, CHECK OPTION을 통해 예외적으로 제한된 수정만 허용할 수 있다

c. 뷰는 데이터를 복사하지 않고 원본 테이블의 데이터를 기반으로 가상으로 보여주는 객체이다.

 

ex) 

CREATE OR REPLACE  
     ALGORITHM = MERGE 
     DEFINER = 'root'@'localhost' 
     SQL SECURITY  INVOKER 
     VIEW  my_view(empno,ename, DEPTNO)
     AS 
	 SELECT EMPNO , ENAME , DEPTNO  
     FROM EMP
     WHERE DEPTNO =  10
     WITH CASCADED CHECK OPTION ; 
     #---> EMP 에서 데이터를 가져와서 뷰의 결과로 제공하고 , 삽입, 삭제, 업데이트할때 VIEW도 검사하겠다.

 

위에서 만든 VIEW를 출력해보기 

-- 생성된 뷰를 조회해 보자.  출력을 해보자  
SELECT   *
FROM MY_VIEW;

---

 

2) CHECK_OPTION 과 IS_UPDATABLE 

WITH  CHECK OPTION  : 현재 뷰에서 WHERE 절 조건을 강제로 위임한다.

WITH CASCADED | LOCAL   CHECK OPTION  :   현재 뷰와 연관된 뷰의 WHERE 절 조건강제 위임한다.

 

CHECK_OPTION : WITH CHECK OPTION 뷰 생성 시 추가 옵션 , CASCADE를 지정하게 되면 
뷰와 관련된 모든 중첩된 뷰의 조건까지 체크하도록 한다. 

 

IS_UPDATABLE : 수정 가능 유무를 리턴한다. YES 는 해당 뷰를 통해 원본을 수정할 수 있다.

 

여기서 CASCADE , YES 이면 {조건에 만족하는} INSERT , UPDATE 허용이 가능하다.

 

ex 1) 

CREATE  VIEW JOIN_EMP02 
AS
SELECT ENAME, DEPTNO, DNAME
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE DEPTNO  > 10  
 WITH CASCADED CHECK OPTION ; -- 조건에 맞는 업데이트와 추가가 이루어 져야 한다.

 

 

ex2)  업데이트 / 조건에 따라 수정하기 

UPDATE JOIN_EMP02
 SET ENAME ='길동'
 WHERE ENAME ='KING';
 
  SELECT  * FROM  JOIN_EMP02;
  SELECT  * FROM EMP;

 

위 코드를 보면 view에서 수정을 할 경우 원본도 같이 수정되는 것을 볼 수 있다. 

 

하지만 insert로 할 경우에는 안됨을 주의한다. 

안되는 경우)

 -- INSERT는 안됨  
INSERT INTO JOIN_EMP02 (ENAME, DEPTNO, DNAME) VALUES ('정길', 10, '부산');
 SELECT CHECK_OPTION, IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS   
WHERE TABLE_NAME = 'JOIN_EMP02';

 

---

 

3) 뷰와 조인

INSERT	조인이 불가능 -> 원본을 모두 업데이트 해야 한다.
UPDATE	조인 가능 -> 업데이트 대상 컬럼이 업데이트 가능 테이블에서 올 경우
DELETE	조인 불가능 -> 병합된 뷰는 가능하지만 조인 뷰는 불가하다

 

왜 조인 뷰에 INSERT가 불가한가?

MYSQL이 어떤 테이블에 삽입할지 모호 하기 때문이다. 

 

예제를 통해 살펴보자.

 

ex) 

 a. T1,T2 테이블 만들기

CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);

 

b. VIEW 만들기

-- CASE 1: 집계함수 포함된 뷰 ( 업데이트 X) 
CREATE VIEW vmat 
AS SELECT SUM(x) AS s 
FROM t1;

-- CASE 2 : 단순 뷰 생성 _업데이트 가능 
CREATE VIEW vup 
AS SELECT * 
FROM t2;

-- CASE 3 : 조인 뷰 
CREATE VIEW vjoin 
AS SELECT *
FROM vmat JOIN vup 
ON vmat.s=vup.c;

 

c. INSERT 해보기

-- CASE 1: 원본으로 들어오는 데이터의 합을 가상의 VIEW의 컬럼에서 계산한 결과 확인 
SELECT * FROM T1;
INSERT INTO T1 VALUES(10);
SELECT * FROM VMAT;
INSERT INTO T1 VALUES(20);
SELECT * FROM VMAT;
INSERT INTO T1 VALUES(10);
SELECT * FROM VMAT;
SELECT * FROM T1;
# 위 결과는 뷰에 값을 넣음에 따라 원본도 같이 값이 넣어진다. 

-- CASE 2 & 3
INSERT INTO VJOIN (C) VALUES(1); -- 업데이트가 안됨 
INSERT INTO VUP (C) VALUES(1); -- 단일 뷰는 INSERT 가능

 

D. UPDATE 확인하기

UPDATE VJOIN SET C = C+1; -- T2 테이블의 C VUP의 컬럼으로 업데이트 가능
UPDATE VJOIN SET X = X+1; -- VMAT의 SUM(X) 집계 포함 업데이트 불가능

 

 

그 외..

-- 허용되는 다중테이블의 UPDATE 구문 -> VUP 로 업데이트 대상으로 조인 
UPDATE VUP JOIN (SELECT SUM(X) AS S FROM T1) AS DT ON ...
SET C =C+1;

-- 허용되는 다중 테이블의 UPDATE 구문 -> 파생테이블 집계수정 불가
UPDATE VUP JOIN (SELECT SUM(X_ AS S FROM T1 ) AS DT ON ...
SET  S = S+1; -- 파생 테이블의 DT는 집계 수정 불가

 

 

---

프로시저 함수 

저번에 프로시저 함수 생성 및 실행법은 다뤘으니 이번에는 이를 가지고 활용을 해보겠다. 

 

ex) A와 B를 받아 더하기 ,뺴기 , 곱하기 , 나누기를 하는 프로시저 함수를 생성해보겠다

https://dev.mysql.com/doc/refman/8.4/en/if.html

-- 프로시저
CREATE DEFINER=`hippo`@`%` PROCEDURE `MY_CALC`(IN A INT ,IN B INT,OUT HAP INT ,OUT SUB INT , OUT MUL INT ,OUT R_DIV DECIMAL(10,2))
BEGIN
	SET HAP = A + B;
	SET SUB= A - B;
	SET MUL = A * B;

    IF B != 0 THEN 
		SET R_DIV = A/B;
    ELSE 
		SET R_DIV = NULL;
    END IF;
    
END

-- 호출하기
SET @A = 10;
SET @B = 0;
SET @HAP = 0;
SET @SUB = 0;
SET @MUL = 0;
SET @R_DIV = 0;

-- 2-1 프로시저 호출 
CALL MY_CALC(@A, @B,@HAP, @SUB,@MUL,@R_DIV);

-- 2-2 결과
SELECT @HAP AS '+', @SUB AS '-', @MUL AS '*' ,@R_DIV AS '/';

 

---

ex) LOOP 프로시저 함수 생성 및 호출하기

https://dev.mysql.com/doc/refman/8.4/en/loop.html

-- 프로시저
CREATE DEFINER=`hippo`@`%` PROCEDURE `doiterate`(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1; -- 조건이 TRUE 이면 LOOP 처음으로 돌아가자
    END IF;
    LEAVE label1;  -- 조건이 FALSE 이면 LOOP 를 종료하자.
  END LOOP label1;
  SET @x = p1;
END

-- 호출하기
call my_emp.doiterate(1000);
SELECT @X;

 

 

나머지는 아래를 참고하면 된다.

https://dev.mysql.com/doc/refman/8.4/en/flow-control-statements.html