SQL 프로그래밍(스토어드 프로시저, IF문, CASE문, WHILE문, 동적SQL)

《스토어드 프로시저(Stored Procedure)란?》

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

 

SQL문법의 함수(Function)와  유사하지만  SQL함수와는 차이가 있다.

- 함수(Function) : 클라이언트에서 처리, 리턴값 필수, 리턴값 하나만 반환가능

- 프로시저(Procedure) : 서버로 보내서 처리, 리턴값 선택, 리턴값 여러개 반환가능

 

프로시저 같은 경우 실행, 처리를 할 때 주로 사용되고, 함수는 간단한 계산이나 수치 결과를 나타낼 때 주로 사용

 

👀 스토어드 프로시저의 장점정리!

1. 하나의 요청으로 여러 SQL문을 실행하는 것이 가능하다.

2. 쿼리를 처리할 때 네트워크의 부하를 줄여 실행시간을 줄일 수 있다.

 

- 스토어드 프로시저 프레임

DELIMITER $$
CREATE PROCEDURE < 스토어드 프로시저 이름() >
BEGIN
< SQL프로그래밍 코드 부분 >
END $$
DELIMITER ;
CALL < 스토어드 프로시저 이름() > ;

 

---

 

《IF문과 WHILE문》

 

IF와 WHILE 모두 조건식의 참 거짓을 판별하고 그 결과에 따라 출력한다.

 

IF와 WHILE의 차이점

- IF는 조건식을 한 번 수행하면 종료된다.

- WHILE은 BREAK에 걸릴 때 까지 무한히 반복된다.

 

- IF문 프레임

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

 

만약 수행할 문장이 하나라면 ----로 감싸져 있는 부분 외의 문장은 생략해도 좋다.

 

 

- WHILE 프레임

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

 

WHILE도 마찬가지로 만약 수행할 문장이 하나라면 ----로 감싸져 있는 부분 외의 문장은 생략해도 좋다.

 

---

 

- if문의 자세한 내용은 예제를 통해서 알아보자 

 

예제에서 사용할 member 테이블이다.

 

                                                        < member >

 

열이름에 대한 설명

 

 

 

< 상황가정! >

이번에 에이핑크 소속사의 주식을 왕창 사서 대주주가 되었다. 기념으로 에이핑크에게 격려문자를 보내려고 한다.

데뷔한지 5년 이상이 되었다면 ' 데뷔한지 ~일이나 지났습니다. 축하합니다! ', 아직 5년 미만이라면 ' 데뷔한지 ~일밖에 안되었네요. 화이팅~ ' 이라고 출력되는 코드를 작성해보자.

 

# 는 주석으로 코드를 이해하는 데 필요한 내용을 적어두었다.

else는 if문의 조건에서 False값을 반환하는 데이터들에 적용된다.

 

 

결과값은 아래와 같다.

데뷔한지 4442일이나 지났습니다. 축하합니다!

---

- while문의 자세한 내용은 예제를 통해서 알아보자 

 

1) 간단히 정수 1~100까지 더한 합을 구하는 프로그램을 작성해보자.

 

 

 

 

2) 1~100까지의 정수중 4의 배수를 제외한 값들의 합을 구하고, 합이 1000을 넘으면 종료하는 프로그램을 작성해보자.

 

 

iterate를 만나면 다시 반복문으로 돌아간다.
leave를 만나면 반복문을 빠져나간다.

myWhile같이 label을 지정할 때는 꼭 콜론(:) 을 사용해야한다. 세미콜론(;)과 혼용하지 말자.

 

결과값은 아래와 같다.

1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==>

1014

 

 

---

《CASE문》

 

CASE 문은 조건이 2가지 이상일 경우 사용하는 명령어이다.

IF 문은 참 또는 거짓 두 가지만 존재하기 때문에 2중 분기라는 용어를 사용한다. 하지만  CASE 문은조건이 2가지 이상일 때 사용하므로 다중 분기라고 부른다.

 

CASE 프레임

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

 

 

- CASE문의 자세한 내용은 예제를 통해서 알아보자 

 

1) 시험성적이 88점인 학생이 있다. 시험점수에 대한 등급은 90이상이면 A, 80이상이면 B, 70점 이상이면 C, 60점 이하면 D, 그 이하는 F를 주려고 한다. 이 학생의 등급을 매길 프로그램을 작성해보자.

 

 

결과값은 아래와 같다.

취득점수==>88

학점==>B

 

 

 

 

2)  각각의 mem_id와 mem_name에 해당하는 고객의 총구매액을 구하고, 이에 따른 고객 등급을 매겨보자.

 

예제에서 사용할 member 테이블과 buy 테이블이다.

 

                                                         < member >

 

                                                                    < buy >

num	mem_id	prod_name	group_name	price	amount
1	BLK	지갑	NULL	30	2
2	BLK	맥북프로	디지털	1000	1
3	APN	아이폰	디지털	200	1
4	MMU	아이폰	디지털	200	5
5	BLK	청바지	패션	50	3
6	MMU	에어팟	디지털	80	10
7	GRL	혼공SQL	서적	15	5
8	APN	혼공SQL	서적	15	2
9	APN	청바지	패션	50	1
10	MMU	지갑	NULL	30	1
11	APN	혼공SQL	서적	15	1
12	MMU	지갑	NULL	30	4

 

<열이름에 대한 설명>

num	구매번호
prod_name	구매한 제품명
price	제품가격
amount	구매제품 수량

나머지 열이름에 대한 정보는 member테이블과 동일하다.

 

 

 

 

 

결과값은 아래와 같다.

 

mem_id	mem_name	총구매액	회원등급
MMU	마마무	1950	최우수고객
BLK	블랙핑크	1210	우수고객
APN	에이핑크	295	일반고객
GRL	소녀시대	75	일반고객
ITZ	잇지	NULL	유령고객
OMY	오마이걸	NULL	유령고객
RED	레드벨벳	NULL	유령고객
SPC	우주소녀	NULL	유령고객
TWC	트와이스	NULL	유령고객
WMN	여자친구	NULL	유령고객

 

---

《동적 SQL》

 

SQL을 고정시키지 않고 동적으로 사용한다는 것이다.

이게 무슨 말인지 나도 꽤나 생각해봤다..🙄

SQL은 내용이 고정되어 있는 경우가 대부분이지만 상황에 따라 내용 변경이 필요할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용할 수 있다. 이런면에서 동적 SQL이라고 부르는 것 같다.

 

< 동적 SQL에서는! >

PREPARE는 SQL 문을 실행하지는 않고 미리 준비만 해놓는 역할을 한다.

EXECUTE는 준비한 SQL 문을 실행한다.

모든 것을 실행 후에는 DEALLOCATE PREPARE로 문장을 해제해주는 것이 바람직하다.

 

 

- CASE문의 자세한 내용은 예제를 통해서 알아보자 

 

 

EXECUTE로 @CurDate에 해당하는 내용을 INSERT INTO gate_table VALUES(NULL,?)의 ?자리에 넣어준다.

id자리를 NULL로 둔 것은 저 자리가 AUTO_INCREMENT이기 때문에 설정하지 않아도 실행시킬 때마다 1씩 추가된 값으로 생성되기 때문이다.

 

id	entry_time
1	2023-04-10 03:48:10

하나를 선언해두면 실시간으로 기록이 가능하다는 장점이 있어서 출입로그로도 많이 사용하고 있다는 것을 알아두자.

 

- set @변수명 : 변수의 값 : 변수의 선언 및 값 대입

- prepare ~ from : SQL 구문을 선언 하는 부분

- execute ~ using : 미리 선언된 구문을 실행 하는 부분

 

혹시 이 내용들이 기억나지 않는다면 아래 링크를 참고하자

MySQL의 데이터 형식과 형태 변환 (tistory.com)

MySQL의 데이터 형식과 형태 변환