DB - WITH절(CTE), VIEW(가상테이블), Stored Program, Procedure(프로시저)

 

 

1. CTE(Common Table Expression) - WITH절

- 기존의 뷰, 파생테이블, 임시테이블 등으로 사용된 것을 대신할 수 있으며 단일 명령문의 범위 내에 존재하는 명명된 임시 결과 집합

- 해당 명령문 내에서 여러 번 참조될 수 있으며 간결한 식으로 보여지는 장점

WITH CTE_테이블이름
AS
(
   SELECT
      *
   FROM
      table1 t1  LEFT JOIN  table2 t2
      ON t1.key = t2.key
)
SELECT * FROM CTE_테이블이름;

 

 

회원등급이 판매자인 회원들의 정보를 조회하시오.

-- CTE(with절) 가상테이블
WITH memberGrade AS
(
	select
		cgc.comm_group_cd,
		cgc.comm_group_nm,
		cc.comm_cd,
		cc.comm_nm
	from
		comm_group_code cgc INNER JOIN comm_code cc
		using(comm_group_cd)
		-- ON cgc.comm_group_cd = cc.comm_group_cd
	where
		cgc.comm_group_nm = '회원등급'
),
memberOrders AS
(
	SELECT
		m.mbr_id,
		m.mbr_name,
		m.mbr_email
	from
		members m INNER JOIN memberGrade mg
		ON m.mbr_grd = mg.comm_cd
	where
		m.mbr_grd = 'mbr_grd_3'
)
SELECT * FROM memberOrders;

 

 

2. VIEW(가상테이블)

- 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블

 

2-1) 특징

- 기본테이블로부터 유도된 테이블이기에 테이블과 같은 형태의 구조이며 조작도 테이블과 거의 같음
- 가상테이블이기에 데이터의 논리적 독립성을 제공
- 뷰를 통해서만 데이터블 접근하여 뷰에 나타나지 않는 데이터 보안에 도움
- 필요한 데이터만 뷰로 정의해서 처리하기에 관리에 용이하고 명령문이 간단함

 

2-2) 단점

- 복잡한 뷰일 경우 테이블의 조인 연산등으로 실시간 데이터를 가져오므로 성능 저하가 일어남
- 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신 연산에 제약이 따름

 

2-3) MySQL VIEW 제약사항

- 뷰의 정의는 SELECT 구문이 허용하는 WHERE, GROUP BY 와 같은 것을 모두 포함 가능.
- 뷰와 함께 연결되는 트리거를 만들 수 없다.
- 뷰 정의에 있는 SELECT 구문은 다음과 같은 구성을 포함할 수 없다.
   - FROM 절 안에 서브쿼리
   - 임시테이블로 참조 (세션과 관련: 연결이 종료되면 사라짐)
   - 사용자 변수로 참조 (세션과 관련: 연결이 종료되면 사라짐)

 

2-4) MySQL VIEW 생성 및 삭제 구문

CREATE VIEW 뷰이름 AS SQL구문 ;
DROP VIEW 뷰이름 ;

 

현재 로그인하지 않은 회원의 정보를 view로 정의하고 조회

CREATE VIEW dormant_mbr
AS 
SELECT
	m.mbr_id AS memberId,
	m.mbr_name AS memberName,
	m.mbr_email AS memberEmail
FROM
	members m LEFT JOIN members_login_log mll
	ON m.mbr_id = mll.login_id
WHERE
	mll.login_no IS NULL;

alter VIEW dormant_mbr
AS 
SELECT
	m.mbr_id AS memberId,
	m.mbr_name AS memberName,
	m.mbr_email AS memberEmail,
	m.mbr_pw AS memberPw,
	CONCAT_WS(' ', m.mbr_addr, m.mbr_daddr) AS memberAddr
FROM
	members m LEFT JOIN members_login_log mll
	ON m.mbr_id = mll.login_id
WHERE
	mll.login_no IS NULL;

DROP VIEW if EXISTS dormant_mbr; -- 이름이  dormant_mbr인 view가 있다면 삭제
SELECT * FROM dormant_mbr;

 

 

3. Stored Program

- 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
- MySQL 안에서 프로그래밍 언어와 같은 기능을 제공하는 프로그램
- 자주 사용하는 복잡한 쿼리를 하나로 묶어서 이름으로 지정하여 이름을 호출하여 실행하도록 설정

 

3-1) 장점

- 긴 쿼리의 내용을 전송하지 않고 프로시저의 이름 및 매개변수 등만 전송하여 네트워크 부하를 줄일 수 있음 ( 성능 향상 )
- 직접 SQL문을 작성하지 않고 저장된 이름만 호출함으로써 일관된 작업을 함 ( 유지관리 간편 ) 
- 한 번 Stored Program을 생성해 쿼리의 수정, 삭제 등의 관리가 수월 (모듈식 프로그래밍 가능)
- 사용자 별로 테이블에 접근 권한을 주지 않고 Stored Program에 접근 권한을 줌으로써 보안 강화

 

3-2) Stored Program 종류

① 프로시저 : 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용

② 함수 : 사용자 정의 함수(내장함수가 제공하지 않아 시스템에 유용한 함수 정의)

③ 트리거 : 테이블의 DML 동작이 일어나면 실행되는 프로그램

④ 커서 : 테이블에서 여러 개의 행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하는 방식

 

 

4. Procedure(프로시저)

- 어떠한 동작을 일괄 처리하기 위한 용도
- 자주 사용되는 일반적인 쿼리를 모듈화 시켜 필요할 때만 호출

 

4-1) 장점

- 네트워크 트래픽 감소
- 데이터베이스에 비즈니스로직 중앙 집중화
- 데이터 베이스 보안 강화

 

4-2) 단점

- 디버깅 어려움
- 유지 보수 복잡성 증가 : 애플리케이션의 설치나 배포가 더 복잡해짐(각 기능을 담당하는 프로그램 코드가 자바와 MySQL 스토어드 프로그램으로 분산되어 관리하기 때문에)

 

-- 프로시저 생성구문
delimiter \\	-- 종결문자 변경
CREATE PROCEDURE 프로시저이름(IN 변수명 VARCHAR(20))
COMMENT '코멘트'
BEGIN
	-- 변수 선언
	DECLARE 변수명 VARCHAR(20);
	set 변수명 := 값;
END \\
delimiter ;

 

CREATE PROCEDURE sp_member_info(IN memberId VARCHAR(50))
COMMENT '회원아이디를 입력받아 회원정보를 조회하는 프로시저'
BEGIN
	-- sql 및 process 코드 작성
	select
		m.mbr_id,
		m.mbr_name,
		m.mbr_email
	from
		members m
	where
		m.mbr_id = memberId;
END $$
delimiter ;

-- procedure 호출
CALL sp_member_info('ksmartid1');

 

sp_member_email('ksmartid1', 'ksmartpw1') 호출시 다음과 같이 조회

delimiter \\
CREATE PROCEDURE sp_member_email(IN memberId VARCHAR(50), in memberPw VARCHAR(50))
COMMENT '회원아이디, 비밀번호를 입력받아 회원이메일을 조회하는 프로시저'
BEGIN
	select
		m.mbr_email
	from
		members m
	WHERE
		m.mbr_id = memberId and m.mbr_pw = memberPw;
END \\
delimiter ;

CALL sp_member_email('ksmartid1', 'ksmartpw1');

 

 

 

 

 

 

 

---

입력한 범위까지의 구구단을 출력하는 프로시저

delimiter \\
CREATE PROCEDURE sp_gugu(IN times int)
COMMENT '입력한 범위까지의 구구단을 출력하는 프로시저'
BEGIN
	-- 변수 선언
	DECLARE i INT DEFAULT 2;
	DECLARE j INT;
	
	-- 구구단 결과 변수
	DECLARE timeResult VARCHAR(300) DEFAULT '';
	
	if(times < 2) then
		SELECT '2미만의 값은 입력할 수 없습니다.' AS result;
	else
		DROP TABLE if EXISTS gugu;
		CREATE TABLE gugu (
			`result` TEXT COMMENT '구구단 결과'
		);
		
		-- 프로시저 호출 시 구구단 결과(테이블)를 초기화
		TRUNCATE TABLE gugu; 
		
		-- 구구단 로직
		while (i <= times) DO
			SET j := 1;
			SET timeResult := '';
			
			-- 중첩반복 (구구단)
			while (j < 10) DO
				case
				when (j = 1) then
					SET timeResult := CONCAT(i, ' X ', j, '=0', (i*j));
				when ((i*j) < 10) then
					SET timeResult := CONCAT(timeResult, '  ', i, ' X ', j, '=0', (i*j));
				else
					SET timeResult := CONCAT(timeResult, '  ', i, ' X ', j, '=', (i*j));
				END case;
            	
            -- 횟수 증감
            SET j := j + 1;
			END while;
			
			-- gugu 테이블에 결과값을 insert
			INSERT INTO gugu(result) VALUES (timeResult);
			
			-- 횟수 증감
			SET i := i + 1;    
   	END while;
   	
   	-- 구구단 결과 조회
      SELECT * FROM gugu;
   END if;
END \\
delimiter ;

CALL sp_gugu(5);