[혼공] 4-3장 SQL 프로그래밍

스토어드 프로시저

- MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체 

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
	이 부분에 SQL 프로그래밍 코딩
END $$ -- 스토어드 프로시저 종료 
DELIMITER; -- 종료 문자를 다시 세미콜론으로 변경
CALL 스토어드_프로시저_이름(); -- 스토어드 프로시저 실행

 

1. IF 문

 

- 기본 형식

IF <조건식> THEN
	SQL 문장들
END IF;

- SQL 문장이 두 문장 이상이 처리되어야 할 때는 BEGIN ~ END로 묶어줘야 함 

 

ex)

DROP PROCEDURE IF EXISTS ifProc1; -- 기존에 ifPro1을 만든적이 있다면 삭제 
    DELIMITER //
    CREATE PROCEDURE ifProc1() -- 스토어드 프로시저 이름을 ifProc1()로 설정
    BEGIN
		IF 100 = 100 THEN
			SELECT '100은 100과 같습니다.';
		END IF;
    END //
    DELIMITER ;
    CALL ifProc1();

 

IF ~ ELSE문

 

USE market_db;
DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
	DECLARE myNum INT;-- myNum 변수 선언
    SET myNum = 200;
    IF myNum = 100 THEN
		SELECT '100입니다.';
	else
		SELECT '100이 아닙니다.';
	END IF;
END $$
DELIMITER ;
CALL ifProc2();

 

1) DECLARE 예약어를 사용해서 myNum 변수 선언

DECLARE myNum INT;

 

2) SET 예약어로 myNum 변수에 200 대입

SET myNum = 200;

 

IF문의 활용

- APN (에이핑크)인 회원의 데뷔 일자가 5년이 넘었는지 확인해보고 5년이 넘었으면 축하메시지 출력

USE market_db;
DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER //
CREATE PROCEDURE ifProc3()
BEGIN 
    DECLARE debutDate DATE; -- 데뷔일자 
    DECLARE curDate DATE; -- 오늘
    DECLARE days INT; -- 활동한 일수 
    SELECT debut_date INTO debutDate
		FROM market_db.member
        WHERE mem_id = 'APN';
        
	SET curDate = CURRENT_DATE(); -- 현재 날짜
    SET days = DATEDIFF(curDate, debutDate);
    
    IF(days/365) >=5 TEHN -- 5년이 지났다면
		SELECT '데뷔한 지' + days + '일이나 지났습니다.';
	ELSE
		SELECT '데뷔한 지' + days + '일밖에 안되었네요.';
	END IF;
END //
DELIMITER ;
CALL ifProc3();

 

1) SELECT ~ INTO 

- 결과를 변수에 저장 

- 에이핑크의 데뷔 일자가 debutDate에 저장 

 SELECT debut_date INTO debutDate
    FROM market_db.member
    WHERE mem_id = 'APN';

 

날짜 관련 함수 

- CURRENT_DATE() : 오늘 날짜를 알려줌

- CURRNET_TIMESTAMP() : 오늘 날짜 및 시간을 함께 알려줌

- DATEDIFF(날짜1, 날짜2) : 날짜2부터 날짜1까지 일수로 몇일인지 알려줌

 

 

2. CASE문

 

-기본 형식

CASE
   WHEN 조건 1 THEN
   	SQL 문장1
   WHEN 조건 2 THEN
    SQL 문장2
   WHEN 조건 3 THEN
    SQL 문장3
   ELSE
    SQL 문장4
END CASE;

 

- 활용 

 회원들의 총 구매액을 계산해서 회원의 등급을 4단계로 나눔 

 

1) 구매 테이블에서 회원의 아이디(mem_id)별로 가격과 수량을 곱해서 총 구매액의 합계 구하기

SELECT mem_id, SUM(price*amount) "총구매액"
    FROM buy
    GROUP BY mem_id;

 

2) 모든 회원의 아이디와 이름 출력 (외부 조인 이용) 

SELECT M.mem_id, M.mem_name,
   SUM(price*amount) "총구매액"
 FROM buy B
    RIGHT OUTER JOIN member M
    ON B.mem_id = M.mem_id
GROUP BY M.mem_id
ORDER BY SUM(price*amount) DESC;

3) 총 구매엑에 따라 회원 등급 구분

CASE
   WHEN (총구매액 >= 1500) THEN '최우수 고객'
   WHEN (총구매액 >= 1000) THEN '우수 고객'
   WHEN (총구매액 >= 1) THEN '일반 고객'
   ELSE '유령 고객'
END

 

4) CASE문을 새로운 열로 추가 

 -> 새로운 '회원등급'열이 추가되고 총 구매액에 따라서 회원이 분류됨

SELECT M.mem_id, M.mem_name, SUM(price*amount) "총구매액",
   CASE
     WHEN (SUM(price*amount) >= 1500) THEN '최우수 고객'
     WHEN (SUM(price*amount) >= 1000) THEN '우수 고객'
     WHEN (SUM(price*amount) >= 1) THEN '일반 고객'
     ELSE '유령 고객'
   END "회원등급"
 FROM buy B
    RIGHT OUTER JOIN member M
    ON B.mem_id = M.mem_id
GROUP BY M.mem_id
ORDER BY SUM(price*amount) DESC;

 

 

3. WHILE 문

 

- 기본 형식 

WHILE <조건식> DO
   SQL 문장들
END WHILE;

 

- ITERATE [레이블]

 - 지정한 레이블로 가서 계속 진행

 - continue 역할

 

-LEAVE [레이블]

 - 지정한 레이블을 빠져나감.

 - WHILE문이 종료됨

 - break 역할

 

- 활용

1에서 100까지 합계에서 4의 배수를 제외 

 

USE market_db;
DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수 
    DECLARE hap INT; -- 더한 값을 누적할 변수 
    SET i=1;
    SET hap = 0 ;
    
    myWhile: -- WHILE문을 myWhile이라는 레이블로 지정
    WHILE (i <= 100) DO
		IF (i%4 = 0) THEN
			SET i=i+1;
            ITERATE myWhile; -- 지정한 레이블문으로 가서 계속 진행
		END IF;
        SET hap = hap + i; -- 4의 배수가 아니면 hap에 누적
        IF(hap > 1000) THEN 
			LEAVE myWhile; -- 지정한 레이블문을 떠남 
		END IF;
        SET i=i+1;
	END WHILE;
    
    SELECT '1부터 100까지의 합 (4의 배수 제외), 1000 넘으면 종료 =>', hap;
END $$
DELIMITER ;
CALL whileProc();

 

4. 동적 SQL 

 

- 상황에 따라 내용 변경이 필요할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용 가능

 

PREPARE

- SQL 문을 실행하지 않고 미리 준비 해둠

 

EXECUTE

- 준비한 SQL문을 실행 

 

DEALLOCATE PREPARE

 - 실행 후에는 문장을 해제 

 

- PREPARE문에서는 ?로 향후에 입력될 값을 비워놓고, EXECUTE에서 USING으로 ?값에 전달 가능

 

ex)

보안이 중요한 출입문에서는 출입할 내역을 테이블에 기록

출입증을 태그한느 순간의 날짜와 시간이 INSERT문으로 만들어져서 입력되도록

 

DROP TABLE IF EXISTS gate_table;
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME); -- 출입용 테이블 작성

SET @curDate = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL,?)'; -- ?를 사용해서 entry_time에 입력할 값을 비워놓음
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;