[SQL] 04-3 SQL 프로그래밍

시작 전 알아야 할 개념

1. SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만들어야 함.
   1) 스토어드 프로시저는 MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터 베이스 개체임.
   2) 스토어드 프로시저 구조
   DELIMITER $$
   CREATE PROCEDURE
   BEGIN
   // 이 부분에 SQL 프로그래밍 코딩
   END $ $
   DELIMITER ;
   CALL

IF 문

IF 문의 기본 형식

1. IF 문은 조건식이 참이라면, 'SQL 문장들'을 실행하고, 그렇지 않으면 그냥 넘어감.

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

3. 'SQL 문장들'이 한 문장이라면 그 문장만 써도 되지만, 두 문장 이상이 처리되어야 할 때는 BEGIN ~ END로 묶어줘야 함.
   
   1) 만약, 기존에 ifProc1()을 만든 적이 있다면 삭제함.
   2) 세미콜론으로는 SQL의 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어서 $$를 사용함.
   3) 스토어드 프로시저의 이름을 ifProc1()로 지정함.
   4) 조건식으로 100과 100이 같은지 비교함. 참이므로 다음 행이 실행됨.
   5) CALL로 호출하면 ifProc1()이 실행됨.

IF ~ ELSE 문

1. IF ~ ELSE 문은 조건에 따라 다른 부분을 수행함.
   1) 조건식이 참이라면, 'SQL 문장들 1'을 실행하고, 그렇지 않으면 'SQL 문장들 2'를 실행함.

2. 예제
   
   1) DECLARE 예약어를 사용하여 myNum 변수를 선언함. 제일 뒤에는 변수의 데이터 형식을 INT로 지정함.
   2) SET 예약어로 myNum 변수에 200을 대입함.
   3) myNum이 100인지 아닌지를 구분함.

IF 문의 활용

1. 아이디가 APN인 회원의 데뷔 일자가 5년이 넘었는지 확인해보고, 넘었을 시 축하 메시지를 출력하는 코드
   
   1) 변수를 3개 준비함. 데뷔 일자는 debutDate에, 오늘 날짜는 curDate에, 데뷔 일자부터 오늘까지 몇일이 지났는지는 days에 저장할 예정임.
   2) APN의 데뷔 일자를 추출하는 SELECT문이 있음. 이때, 그냥 SELECT와 달리 INTO 변수가 붙음. 이 경우, 결과를 변수에 저장하며 결국 에이핑크의 데뷔 일자가 debutDate에 저장됨.
   3) CURRENT_DATE() 함수로 현재 날짜를 curDate에 저장함.
   4) DATEDIFF() 함수로 데뷔 일자부터 현재 날짜까지 일수를 days에 저장함.
   5) 일자가 저장된 days를 365로 나눠서 연으로 변환한 후 5년이 넘는 것과 그렇지 않은 경우에 메시지를 다르게 출력함.

2. 날짜 관련 함수
   1) CURRENT_DATE(): 오늘 날짜를 알려줌.
   2) CURRENT_TIMESTAMP(): 오늘 날짜 및 시간을 함께 알려줌.
   3) DATEDIFF(날짜1, 날짜2): 날짜2부터 날짜1까지 일수로 몇일인지 알려줌.
   

CASE 문

CASE 문의 기본 형식

1. IF 문은 참 아니면 거짓 두 가지만 있기 때문에 2중 분기라는 용어를 사용하는 반면, CASE 문은 2가지 이상의 여러 가지 경우일 때 처리가 가능하므로 다중 분기라고 부름.

2. CASE 문의 형식
   CASE
   WHEN 조건1 THEN
   SQL 문장들1
   WHEN 조건2 THEN
   SQL 문장들2
   WHEN 조건3 THEN
   SQL 문장들3
   ELSE
   SQL 문장들4
   END CASE;

3. 시험 점수와 학점 예제
   
   1) 시험 점수 point 변수에 88을 입력했고, 학점을 저장한 credit 변수를 준비함.
   2) point에 따라서 credit을 A부터 F까지 설정함.
   3) 앞의 모든 조건에 해당하지 않으면 F학점으로 처리함.

CASE 문의 활용

회원들의 총 구매액을 계산하여 회원의 등급을 4단계로 나누는 예제

• 총 구매액 1500 이상 -> 최우수 고객
• 총 구매액 1000 ~ 1499 -> 우수 고객
• 총 구매액 1~ 999 -> 일반 고객
• 0 이하 (구매한 적 없음) -> 유령 고객

1. 구매 테이블에서 회원별로 총 구매액 구하기
   

2. ORDER BY를 사용해서 총 구매액이 많은 순서로 정렬하기
   

3. 회원의 이름도 출력하기
   1) 회원의 이름은 회원 테이블에 있으므로 구매 테이블과 조인하기
   

4. 구매하지 않은 회원의 아이디와 이름도 출력하기
   1) 내부 조인 대신 외부 조인을 사용하면 됨.
   2) 구매한 적이 없어도 회원 테이블에 있는 회원은 모두 출력해야 하므로 INNER JOIN을 RIGHT OUTER JOIN으로 변경함.
   

5. 계획한대로 총 구매액에 따라 회원 등급 구분하기
   

WHILE 문

WHILE 문의 기본 형식

1. WHILE 문의 형식
   WHILE <조건식> DO
   SQL 문장들
   END WHILE;

2. 1부터 100까지의 값을 모두 더하는 WHILE 문
   
   1) 1, 2, 3 ... 으로 증가할 변수 i와 합계를 누적할 변수 hap을 준비하기
   2) i가 100 이하인 동안에 계속 반복함.
   3) i를 계속 hap에 누적시키고, i는 1씩 증가시킴.

WHILE 문의 응용

1. ITERATE 문과 LEAVE 문
   1) ITERATE [레이블]
   : 지정한 레이블로 가서 계속 진행함.
   2) LEAVE [레이블]
   : 제정한 레이블을 빠져나감. 즉 WHILE 문이 종료됨.

2. 예제
   1) 1부터 100까지 합계를 구하는 예제
   2) 4의 배수는 제외하기
   3) 숫자를 더하는 중간에 합계가 1000이 넘어가면, 더하는 것을 그만두고 1000이 넘는 순간의 숫자를 출력한 뒤 프로그램 종료하기
   
   과정
   1) WHILE 문을 myWhile 이라는 레이블로 지정함.
   2) i가 4의 배수라면 i를 1 증가시키고, ITERATE를 만나서 1단계로 올라감. 즉, WHILE 문을 계속 진행함.
   3) i가 4의 배수가 아니면 hap에 누적시킴.
   4) hap이 1000을 초과하면 LEAVE를 만나서 myWhile 레이블을 빠져나감.

동적 SQL

SQL 문은 내용이 고정되어 있는 경우가 대부분임. 그러나, 상황에 따라 내용 변경이 필요할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용할 수 있음.

PREPARE과 EXECUTE

1. PREPARE는 SQL 문을 실행하지는 않고 미리 준비만 해놓고, EXECUTE는 준비한 SQL 문을 실행함.

2. 실행 후에는 DEALLOCATE PREPARE로 문장을 해제해 주어야 함.

3. 예제
   
   1) PREPARE 문에서는 SELECT * FROM member WHERE mem_id = "BLK"를 바로 실행하지 않고 myQuery에 입력해놓음.
   2) 실행이 필요한 시점에서 EXECUTE myQuery 문으로 실행함.

4. PREPARE 문에서는 ?로 향후에 입력될 값을 비워 놓고, EXECUTE에서 USING으로 ?에 값을 전달할 수 있음. 그러면, 실시간으로 필요한 값들을 전달하여 동적으로 SQL이 실행됨.
   
   1) 출입용 테이블을 간단히 만듦. 아이디는 자동으로 증가되도록 하고, 출입하는 시간을 DATETIME 형으로 준비함.
   2) 현재 날짜와 시간을 @curDate 변수에 넣음.
   3) ?를 사용해서 entry_time에 입력할 값을 비워놓음.
   4) USING 문으로 앞에서 준비한 @curDate 변수를 넣은 후에 실행됨. 결국 이 SQL을 실행한 시점의 날짜와 시간이 입력됨.