[혼자 공부하는 SQL] chapter 4. SQL 고급 문법(4-3)

https://www.hanbit.co.kr/store/books/look.php?p_code=B6846155853

혼자 공부하는 SQL

위 내용은 <혼자 공부하는 SQL>을 기반으로 공부하여 작성 내용입니다.

4-3 SQL 프로그래밍

• 스토어드 프로시저 : MySQL에서 프로그래밍 기능이 필요할 때 상요하는 데이터베이스 개체

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

CALL 스토어드_프로시저_이름(); --스토어드 프로시저 실행

-- 일반적으로 구분 문자(DELIMITER)는 $$ 많이 사용
-- /, &, @ 등을 사용해도 상관 X

 

IF 문

-- 기본 형식
IF <조건식> THEN
    SQL 문장들
END IF;

• 'SQL 문장들'이 한 문장이라면 그 문장만 써도 됨
• 두 문장 이상 처리되어야 할 때는 BEGIN~END로 묶어줘야 함

-- 기존에 ifProc1()을 만들었다면 삭제함
DROP PROCEDURE IF EXISTS ifProc1;

-- 세미콜론으로 SQL의 끝인지 스토어드 프로시저의 끝인지 구별할 수 없어서 $$ 사용
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
    -- 조건식으로 100과 100이 같은지 비교, 참이므로 다음 행 실행
    IF 100 = 100 THEN 
        SELECT '100은 100과 같습니다.';
    END IF;
END $$
DELIMITER ;

CALL ifProc1();

 

IF ~ ELSE 문

-- ifProc2()가 존재하면 삭제함
DROP PROCEDURE IF EXISTS ifProc2;

DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
    -- DECLARE 예약어를 사용해서 myNum 변수 선언, 데이터 형식 INT로 지정
    DECLARE myNum INT;
    
    -- SET 예약어로 변수에 200 대입
    SET myNum = 200;
    
    -- myNum이 100인지 아닌지 구분
    IF myNum = 100 THEN
        SELECT '100입니다.';
    ELSE
        SELECT '100이 아닙니다.';
    END IF;
END $$
DELIMITER  ;

CALL ifProc2();

 

IF문의 활용

-- 아이디가 APN인 회원의 데뷔 일자가 5년이 넘었는지 확인해보고 넘었으면 축하 메시지 출력
DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
    -- 변수 3개 준비
    DECLARE debutDate DATE; -- 데뷔 일자
    DECLARE curDate DATE; -- 오늘
    DECLARE days INT; -- 활동한 일수
    
    -- APN의 데뷔 일자 추출하는 SELECT문
    -- INTO 변수 : 결과를 변수에 저장함
    SELECT debut_date INTO debutDate
        FROM market_db.member
        WHERE mem_id = 'APN';
          
    -- CURRENT_DATE() : 현재 날짜를 변수에 저장
    SET curDATE = CURRENT_DATE(); 
    
    -- 날짜의 차이(일 단위)를 변수에 저장
    SET days = DATEDIFF(curDATE, debutDate); 
    
    IF(days/365) >= 5 THEN -- 5년이 지났다면
        SELECT CONCAT('데뷔한 지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!');
    ELSE
        SELECT '데뷔한 지' + days + '일밖에 안되었네요. 핑순이들 화이팅~';
    END IF;
END $$
DELIMITER ;

CALL ifProc3();

 

CASE 문

• 2가지 이상의 여러 가지 경우일 때 처리가 가능하므로 '다중 분기'라고 부름
• WHEN 다음에 조건이 나오는데 조건이 여러 개라면 여러 번 반복함
• 모든 조건에 해당하지 않으면 마지막 ELSE 부분 수행

-- 기본 형식
CASE
    WHEN 조건 1 THEN
    	SQL 문장들 1
    WHEN 조건 2 THEN
    	SQL 문장들 2
    WHEN 조건 3 THEN
    	SQL 문장들 3
    ELSE
    	SQL 문장들 4
END CASE;

-- 성적 처리
DROP PROCEDURE IF EXISTS caseProc;
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
    
    DECLARE point INT;
    DECLARE credit CHAR(1);
    SET point = 88;
    
    CASE -- point에 따라서 credit을 A부터 F까지 설정
        WHEN point >= 90 THEN
            SET credit = 'A';
        WHEN point >= 80 THEN
            SET credit = 'B';
        WHEN point >= 70 THEN
            SET credit = 'C';
        WHEN point >= 60 THEN
            SET credit = 'D';
        ELSE -- 앞의 모든 조건에 해당하지 않으면 F학점으로 처리
            SET credit = 'F';
    END CASE;
    -- 결과 출력
    SELECT CONCAT('취득점수 ==> ', point), CONCAT('학점 ==> ', credit);
END $$
DELIMITER ;
CALL caseProc()

 

CASE 문의 활용

-- buy 테이블에서 회원별로 총구매액 구함
SELECT  mem_id, SUM(price*amount) "총구매액"
    FROM buy
    GROUP BY mem_id;

-- 총 구매액이 많은 순서로 정렬
SELECT  mem_id, SUM(price*amount) "총구매액"
    FROM buy
    GROUP BY mem_id
    ORDER BY SUM(price*amount) DESC;

-- 회원 이름 출력하기 위해 회원테이블과 구매테이블 조인
SELECT  B.mem_id, M.mem_name, SUM(price*amount) "총구매액"
    FROM buy B
        INNER JOIN member M
        ON B.mem_id = M.mem_id
    GROUP BY B.mem_id
    ORDER BY SUM(price*amount) DESC;

-- 구매X 회원 아이디와 이름 출력
-- 내부 조인 대신 외부 조인
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;

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;

 

WHILE 문

-- 기본 형식
WHILE <조건식> DO
    SQL 문장들
END WHILE;

DROP PROCEDURE IF EXISTS whileProc;

DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수
    DECLARE hap INT; -- 더한 값을 누적할 변수
    SET i = 1;
    SET hap = 0;
    
    WHILE(i<=100) DO
        -- hap의 원래 값에 i를 더해서 다시 hap에 넣으라는 의미
        SET hap = hap + i;
        
        -- i의 원래 값에 1을 더해서 다시 i에 넣으라는 의미
        SET i = i + 1;
    END WHILE;
    SELECT '1부터 100까지의 합 ==> ', hap;
END $$
DELIMITER ;

CALL whileProc()

 

WHILE 문의 응용

• ITERATE[레이블] : 지정한 레이블로 가서 계속 진행
• LEAVE[레이블] :  지정한 레이블을 빠져나감 => WHILE문 종료

DROP PROCEDURE IF EXISTS whileProc2;

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

CALL whileProc2();

 

동적 SQL

PREPARE와 EXECUTE

• PREPARE : SQL 문을 실행하지는 않고 미리 준비만 함
• EXECUTE : 준비한 SQL 문을 실행함
• DEALLOCATE PREPARE : 실행 후 문장 해제

use market_db;

-- myQuery에 SELECT * FROM member WHERE mem_id = "BLK" 입력만 시켜놓음
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';

-- 필요한 시점에 myQuery 실행
EXECUTE myQuery;

DEALLOCATE PREPARE myQuery;

 

동적 SQL의 활용

• PREPARE 문에서 ?로 향후에 입력될 값을 비워 놓고, EXECUTE에서 USING으로 ?에 값을 전달할 수 있음
• 그러면 실시간으로 필요한 값들을 전달해서 동적으로 SQL이 실행됨

DROP TABLE IF EXISTS gate_table;
-- 출입용 테이블 생성
-- 아이디는 자동으로 증가, 출입하는 시간을 DATETIME형으로 준비
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

-- 현재 날짜와 시간을 @curDate변수에 저장
SET @curDate = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간

-- ?를 사용하여 entry_time에 입력할 값을 비워 놓음
PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';

-- USING 문으로 앞에서 준비한 @curDate 변수를 넣은 후에 실행
-- 이 SQL을 실행한 시점의 날짜와 시간이 입력됨
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;