[MySQL] 챕터 7. SQL 고급

7.1 MySQL의 데이터 형식

7.1.1 MySQL에서 지원하는 데이터 형식의 종류

• 숫자 데이터 형식

데이터 형식	바이트 수	숫자 범위	설명
BIT(N)	N/8		1~64bit를 표현. b`0000` 형식으로 표현
TINYINT	1	-128~127	정수
SMALLINT	2	-32,768~32,767	정수
MEDIUMINT	3	-8,388,608~8,388,607	정수
INT INTEGER	4	약 -21억 ~ 약 21억	정수
BIGINT	8	약 -900경 ~ 900경	정수
FLOAT	4	-3.40E+38 ~ -1.17E-38	소수점 아래 7자리까지 표현
DOUBLE REAL	8	-1.22E-308 ~ 1.79E+308	소수점 아래 15자리까지 표현
DECIMAL(m,[d]) NUMERIC(m,[d])	5~17	-10^38+1 ~ +10^38-1	전체 자릿수(m)와 소수점 이하 자릿수(d)를 가진 숫자형 예) decimal(5,2)는 전체 자릿수를 5자리로 하되, 그 중 소수점 이하를 2자리로 하겠다는 의미

 

• 문자 데이터 형식

데이터 형식		바이트 수	설명
CHAR(n)		1~255	고정길이 문자형, n을 1부터 255까지 지정. character의 약자 그냥 CHAR만 쓰면 CHAR(1)과 동일
VARCHAR(n)		1~65535	가변길이 문자형. n을 사용하면 1부터 65535까지 지정. Variable charater의 약자
BINARY(n)		1~255	고정길이의 이진 데이터 값
VARBINARY(n)		1~255	가변길이의 이진 데이터 값
TEXT 형식	TINYTEXT	1~255	255 크기의 TEXT 데이터 값
	TEXT	1~65535	N 크기의 TEXT 데이터 값
	MEDIUMTEXT	1~16777215	16777215크기의 TEXT 데이터 값
	LONGTEXT	1~4294967295	최대 4GB 크기의 TEXT 데이터 값
BLOB 형식	TINYBLOB	1~255	255 크기의 BLOB 데이터 값
	BLOB	1~65535	N 크기이 BLOB 데이터 값
	MEDIUM BLOB	1~16777215	16777215 크기의 BLOB 데이터 값
	LONGBLOB	1~4294967295	최대 4GB 크기의 BLOB 데이터 값
ENUM(값들...)		1 또는 2	최대 65535개의 열거형 데이터 값
SET(값들...)		1, ,2, 3, 4, 8	최대 64개의 서로 다른 데이터 값

 

• 날짜와 시간 데이터 형식

데이트 형식	바이트 수	설명
DATE	3	날짜는 1001-01-01 ~ 9999-12-31까지 저장 날짜 형식만 사용. `YYYY-MM-DD` 형식으로 사용
TIME	3	-838:59:59.000000 ~ 838:59:59.000000까지 저장 HH:MM:SS` 형식으로 사용
DATETIME	8	날짜는 1001-01-01 00:00:00 ~ 9999-12-31 23:59:59까지 저장 형식은 `YYYY-MM-DD HH:MM:SS` 형식으로 사용
TIMESTAMP	4	날짜는 1001-01-01 00:00:00 ~ 9999-12-31 23:59:59까지 저장 형식은 `YYYY-MM-DD HH:MM:SS` 형식으로 사용 time_zone 시스템 변수와 관련이 있으며 UTC 시간대 변환하여 저장
YEAR	1	1901 ~ 2155까지 저장. `YYYY` 형식으로 사용

 

• 기타 데이터 형식

데이터 형식	바이트 수	설명
GEOMETRY	N/A	공간 데이터 형식으로 선, 점 및 다각형 같은 공간 데이터 개체를 저장하고 조작
JSON	8	JSON(JavaScript Object Notation) 문서를 저장

 

• LONGTEXT, LONGBLOB

MySQL에서 큰 데이터(LOB, Large Object)를 저장하기 위해 LONGTEXT와 LONGBLOB을 사용한다.

LONGTEXT는 텍스트 데이터를 저장하는 데 사용된다. 예를 들어 긴 기사, 책의 내용과 같은 긴 텍스트 데이터를 저장할 때 사용된다.

LONGBLOB은 바이너리 데이터를 저장하는 데 사용된다. 예를 들어 이미지, 동영상, 실행 파일을 저장할 때 사용된다.

 

7.1.2 변수의 사용

SQL도 다른 프로그래밍 언어처럼 변수를 선언할 수 있다.

 

• 선언

SET @myVar = 100;

 

• 사용

SELECT @myVar;

 

• LIMIT과 변수 사용

`LIMIT`을 사용할 때는 `LIMIT 3`과 같은 형식으로 사용해야한다. `LIMIT @변수` 형식으로 사용하면 오류가 발생하기 때문에 다른 방식을 사용해야 한다.

PREPARE myQuery
	FROM 'SELECT Name, height FROM usertbl ORER BY height LIMIT ?';
EXECUTE myQuery USING @myVar1;

PREPARE 쿼리문은 쿼리문을 준비만 해놓고 실행하지 않는다. EXECUTE문이 실행될 때 USING 뒤에 사용한 변수를 PREPARE문에 할당해서 사용한다.

 

7.1.3 데이터 형식과 형 변환

• CAST

SELECT CAST('2024-01-01' AS DATE);

 

• CONVERT

SELECT CONVERT('2024-01-01', DATE);

 

위의 함수들은 데이터 타입이나 형식이 일치하지 않아 발생하는 오류를 해결할 수 있다.

SELECT CAST(AVG(amount) AS SIGNED INTEGER AS '평균 구매 개수' FROM buytbl);

위와 같이 사용시 `SIGNED INTEGER`로 변환, 다시말해 정수형으로 cast될 때 반올림이 된다.

 

암시적인 형 변환

SELECT '123' = 123 AS Result;

위 쿼리는 문자열 '123'은 숫자 123으로 암시적으로 변환되어 비교된다. 결과는 `1`(true)가 된다.

 

SELECT '100' + '200';

위 커리는 `+` 연산자에 의해 숫자로 변경되어 계산된다. 결과는 300이 된다.

 

위 처럼 데이터 타입이 변환되어 계산되는 것을 형변환이라 한다. `CAST` 또는 `CONVERT` 함수를 이용해서 데이터 형식을 변환하는 것을 명시적인 변환이라 한다.

위와 같은 함수를 사용하지 않고 형변환 되는 것을 암시적인 변환이라고 한다.

 

7.1.4 MySQL 내장 함수

• IF() 함수

IF함수 사용 예시

IF() 함수는 주어진 조건이 참이면 두 번째 인자를, 거짓이면 세 번째 인자를 반환한다

 

• IFNULL() 함수

IFNULL 함수 사용 예시

IFNULL() 함수는 첫 번째 인자가 NULL이 아니면 첫 번째 인자를, NULL이면 두 번째 인자를 반환한다.

 

• NULLIF() 함수

NULLIF 함수 사용 예시

두 인자가 같으면 NULL을, 다르면 첫 번째 인자를 반환한다. 특정 값에 대해 예외를 처리할 때 유용하다.

 

• CASE 문

CASE문 사용 예시

CASE문은 여러 조건을 검사하여 각 조건에 따라 다른 값을 반환한다. `IF`문의 연쇄적 사용보다 더 간결하고 읽기 쉽다.

 

• ASCII(아스키 코드), CHAR(숫자)

ASCII() 사용 예시

문자열의 첫 번째 문자에 대한 ASCII 코드 값을 반환한다. 문자열이 비어 있으면 0을 반환한다. 위의 예시의 경우 65를 반환한다.

SELECT CHAR(72, 101, 108, 108, 111)

CHAR() 함수는 주어진 ASCII 코드들에 해당하는 문자들을 반환한다. 여러 ASCII 코드를 인자로 사용하여 여러 문자를 한 번에 반환할 수 있으며, 이 경우 각 ASCII 코드는 쉼표로 구분된다. 위의 예시는 `Hello`를 반환한다.

 

• BIT_LENGTH(), CHAR_LENGTH(), LENGTH()

BIT_LENGTH()  사용 예시

BIT_LENGTH() 함수는 문자열의 길이를 비트 단위로 반환한다. 위의 예시에서 'text' 문자열의 경우, ASCII 문자 하나는 8비트 이므로, `BIT_LENGTH('text')`는 32비트를 반환한다.

 

CHAR_LENGTH() 사용 예시

CHAR_LENGTH()는 문자의 길이를 반환한다. 영어가 아닌 다국적 언어 지원에서 유용하며, 멀티바이트 문자셋에서 문자의 실제 개수를 정확하게 측정한다.

 

LENGTH() 함수는 문자열의 길이를 바이트 단위로 반환한다. 따라서 1번 예시의 경우 13바이트이기 때문에 13이고, 2번 예시의 경우 15바이트이기 때문에 15이다.

 

• CONCAT(), CONCAT_WS()

CONCAT() 예시

CONCAT() 함수는 두 개 이상의 문자열을 하나로 결합한다. 위의 예시는 `Hello, World!`를 반환한다. 만약 인수 중 하나라도 NULL이라면, 결과는 NULL을 반환한다.

CONCAT_WS() 사용 예시

CONCAT_WS()는 첫 번째 인수로 지정된 구분자를 사용하여 이후 인수로 전달된 문자열들을 결합한다. 결합시 NULL값이 있을 경우, 이를 무시한다. 위 예시는 `Hello, world!`를 반환한다.

 

• ELT(), FIELD(), FIND_IN_SET()

ELT() 사용 예시

ELT()는 숫자 인덱스를 사용하여 입력된 문자열 목록에서 특정 위치의 문자열을 반환하는 함수다. 위 예시에서는 'Banana'가 반환된다.

 

FIELD() 사용 예시

FIELD()는 첫 번째 인수로 주어진 문자열이 후속 인수 목록에서 처음으로 나타나는 위치의 인덱스를 반환하는 함수다. 만약 첫 번째 인수가 목록에 없다면, 0을 반환한다. 위 예시에서는 2를 반환한다.

 

FIND_IN_SET() 사용 예시

FIND_IN_SET() 함수는 주어진 문자열이 콤마로 구분된 문자열 목록에서 처음으로 나타나는 위치의 인덱스를 반환한다. 첫 번째 인자는 찾을 문자열, 두 번째 인자는 콤마로 구분된 문자열 목록이다. 위 예시는 2를 반환한다.

 

INSTR() 사용 예시

INSTR() 함수는 첫 번째 인수로 주어진 문자열 내에서 두 번째 인수로 주어진 부분 문자열이 시작하는 위치의 인덱스를 반환한다. 위 예시에서는 8을 반환한다.

 

LOCATE() 사용 예시

LOCATE()함수는 INSTR()함수와 동일하지만 파라미터의 순서가 반대로 되어 있다.

 

• FORMAT()

FORMAT() 사용 예시

1,234,567.57을 반환한다. 여기서 숫자는 천 단위로 구분되고, 소수점 이하 두 자리까지 표시된다.

 

• BIN(), HEX(), OCT()

BIN() 사용 예시

BIN() 함수는 주어진 숫자를 이진수 형태의 문자열로 변환한다. 위 쿼리는 `1100`을 반환한다.

 

HEX() 사용 예시

HEX() 함수는 주어진 숫자를 16진수 형태의 문자열로 변환한다. 문자열이 주어진 경우, 그 문자열의 각 문자를 ASCII값의 16진수 형태로 변환한다. 위 쿼리는 `FF`를 반환한다. 만약 `HEX('A')`를 입력한 경우, 65의 16진수 값인 `41`을 반환한다.

 

OCT() 사용 예시

OCT() 함수는 주어진 숫자를 8진수 형태의 문자열로 변환한다. 위 쿼리는 `10`을 반환한다.

 

• REPLACE()

REPLACE() 사용 예시

REPLACE() 함수는 문자열에서 특정 패턴을 찾아 다른 문자열로 교체하는 기능을 제공한다. 위 쿼리는 `Hello World` 문자열에서 `World`를 `MySQL`로 교체하여 `Hello MySQL`을 반환한다.

 

• SPACE()

SPACE() 사용 예시

SPACE() 함수는 지정된 수의 공백 문자로 이루어진 문자열을 반환하는 함수다. CONCAT() 함수와 같이 사용할 수 있다. 위 예시는 `Hello   World`(공백 3개)를 반환한다.

 

 

• SUBSTRING()

SUBSTRING() 사용 예시

SUNSTRING() 함수는 문자열의 특정 부분을 추출하는 데 사용된다. 위 쿼리는 2번째 부터 시작하여 5개의 문자를 추출한다. 결과는 `ello `이다.

 

수학 함수

• ABS()

ABS() 사용 예시

이 쿼리는 `-123`의 절댓값인 `123`을 반환한다.

 

• CEILING()

CEILING() 사용 예시

가장 가까운 정수로 올림한다. 위 예시는 `5`를 반환한다.

 

• FLOOR()

FLOOR() 사용 예시

가장 가까운 정수로 내림한다. 위 예시는 `4`를 반환한다.

 

• ROUND()

ROUND() 사용 예시

가장 가까운 정수로 반올림한다. 선택적으로 소수점 이하 몇 번째 자리에서 반올림할지 지정할 수 있다.

 

날짜 및 시간 함수

• ADDDATE(), SUBDATE()

ADDDATE() 사용 예시

ADDDATE() 함수는 특정 날짜에 지정된 시간 간격을 더하는 데 사용된다. 이 함수를 사용하여 날짜나 시간 값을 조정할 수 있으며, 주로 날짜 연산에서 매우 유용하게 사용된다. 위 예시는 `2023-01-11`를 반환한다.

SUBDATE() 사용 예시

주어진 날짜에서 특정 시간 간격을 빼는 데 사용된다. `ADDDATE()` 함수와 반대로 작동하여, 날짜나 시간 값에서 지정된 시간 간격을 차감한다. 위 예시는 `2023-01-05`를 반환한다.

 

• ADDTIME(), SUBTIME()

ADDTIME() 사용 예시

ADDTIME() 함수는 두 개의 시간 값을 인자로 받아, 첫 번째 시간 값에 두 번째 시간 값을 더한 결과를 반환한다.

 

SUBTIME() 사용 예시

SUBTIME() 함수는 두 개의 시간 값을 인자로 받아, 첫 번째 시간 값에서 두 번째 시간 값을 뺀 결과를 반환한다. 위 쿼리의 결과는 `10:30:00`이다.

 

• CURDATE(), CURTIME(), NOW(), SYSDATE()

CURDATE() 사용 예시

현재의 날짜를 `YYYY-MM-DD` 형식의 날짜 값으로 반환한다. 시간 정보는 포함되지 않는다.

 

CURTIME() 사용 예시

현재의 시간을 `HH:MM:SS` 형식의 시간 값으로 반환한다. 날짜 정보는 포함되지 않는다.

 

NOW() 사용 예시

현재의 날짜와 시간을 `YYYY-MM-DD HH:MM:SS` 형식으로 반환한다. 날짜와 시간 정보 모두를 포함한다. 현재 날짜와 시간을 반환한다.

 

SYSDATE() 사용 예시

함수와 유사하게 현재의 날짜와 시간을 `YYYY-MM-DD HH:MM:SS` 형식으로 반환한다. 그러나 `SYSDATE()`는 쿼리가 실행될 때마다 실시간으로 시스템의 날짜와 시간을 가져오는 반면, `NOW()` 함수는 쿼리가 시작할 때의 날짜와 시간을 반환한다는 차이가 있다. 위 쿼리는 실행되는 순간의 시스템 날짜와 시간을 반환한다.

 

시스템 정보 함수

• USER()

USER() 사용 예시

현재 MySQL 세션을 생성한 사용자의 이름과 호스트를 반환한다. 예를 들어, `root@localhost` 같은 형식의 결과를 볼 수 있다.

 

• DATABASE()

DATABASE() 사용 예시

현재 선택된 데이터베이스의 이름을 반환한다. 데이터베이스가 선택되지 않았다면 NULL을 반환할 수 있다.

 

• VERSION()

VERSION() 사용 예시

현재 MySQL 서버의 버전 정보를 문자열로 반환한다. 예를 들어 `8.0.23`과 같은 값을 반환한다.

 

• SLEEP()

SLEEP() 사용 예시

지정된 시간 동안 쿼리 실행을 일시 정지한다. 위 쿼리는 5초만큼 실행을 중지한다. 실행 완료 시 1을, 실패 시 0을 반환한다.

 

그 외의 함수

이 외에도 많다. 필요할 때 찾아가며 공부하자

 

피벗의 구현

피벗( pivot )은 행 데이터를 열 형식으로 변환하는 작업을 의미한다.

피벗 예시 - 1

위 데이터를 사용하여 `item`별로 날짜에 따른 `quantity`를 열로 표시하는 피벗 테이블을 만들고 싶다고 가정하자. 즉, 각 아이템이 각기 다른 열에 표시되도록 하여 sale_date별로 각 아이템의 판매량을 쉽게 비교할 수 있도록 하려는 것이다.

피벗 예시 - 2

위 쿼리는 `IF`문을 사용하여 `item` 값에 따라 다른 열에 `quantity` 값을 배치한다. `GROUP BY` 를 사용하여 `sale_date` 별로 결과를 그룹화하고, 각 날짜에 대해 아이템 A과 B의 판매량을 분리된 열로 표시한다.

피벗 예시 - 3

피봇 테이블의 결과는 위와 같을 것이다.

 

JSON

데이터를 키-값 쌍으로 저장하는 데이터 포맷이다. 

• JSON_OBJECT(), JSON_ARRAY()

JSON 사용 예시 - 1

위 쿼리는 각 사용자에 대한 정보를 JSON 객체 형태로 반환한다. 

JSON 사용 예시 - 2

위 쿼리에 대한 결과다.

 

7.2 조인

조인은 두 개 이상의 테이블에서 열을 결합하여 데이터를 검색하는 데 사용되는 방법이다. 조인을 사용하면 관련 데이터를 여러 테이블에서 동시에 조회할 수 있다.

 

7.2.1 INNER JOIN(내부 조인)

이너 조인(INNER JOIN)은 두 테이블 간의 교집합을 검색하느 가장 일반적인 조인 유형이다. 이 조인 방식을 사용하면 두 테이블 모두에 일치하는 행만 결과로 반환된다.

이너 조인 예시

이 쿼리는 `employee` 테이블과 `departments` 테이블에서 `department_id`와 `id`가 서로 일치하는 행만 선택하여 직원의 이름과 그들이 속한 부서 이름을 함께 보여준다.

 

7.2.2 OUTER JOIN(외부 조인)

OUTER JOIN은 조인의 조건에 만족되지 않는 행까지도 포함시키는 것이다. 자주 사용되지는 않지만 가끔 사용된다. 주로 `LEFT OUTER JOIN`, `RIGHT OUTER JOIN`, `FULL OUTER JOIN` 세 가지 형태로 나뉜다.

 

• LEFT OUTER JOIN

LEFT OUTER JOIN 사용 예시

이 쿼리는 모든 `employees`에 대한 정보와 그들이 속한 `departments` 정보를 반환한다. 어떤 `employees`가 어떤 `departments`에도 속하지 않는 경우, `department_name`은 NULL로 표시된다.

 

• RIGHT OUTER JOIN

RIGHT OUTER JOIN 사용 예시

이 쿼리는 모든 `employees`에 대한 정보를 반환하고, 각 `departments`에 대한 정보가 있다면 같이 반환한다. 어떤 `departments`가 어떤 `employee`에도 연결되어 있지 않은 경우, `name`은 NULL로 표시된다.

 

• FULL OUTER JOIN

FULL OUTER JOIN 사용 예시

 `FULL OUTER JOIN`은 두 테이블의 모든 행을 반환하며, 한쪽 테이블에는 있지만 다른 쪽 테이블에는 없는 행도 포함한다.

 위 예시에서는 두 개의 테이블 `employee`와 `department`가 있고, `employee` 테이블에는 직원 정보가, `department` 테이블에는 부서 정보가 있다. `employee`의 `department_id` 필드는 `department` 테이블의 `id` 필드와 연결된다.

 첫 번째 쿼리는 `employee` 테이블에 있는 모든 직원을 `deapartment` 테이블과 `LEFT JOIN` 하여 부서 이름과 함께 반환한다. 부서에 소속되지 않은 직원은 부서 이름이 `NULL`로 나타난다.

 두 번째 쿼리는 `department` 테이블에 있는 모든 부서를 `employee` 테이블과 `RIGHT JOIN` 하여 직원 이름과 함께 반환한다. 직원이 없는 부서는 직원 이름이 `NULL`로 나타난다.

 `UNION`을 사용하여 두 결과를 결합하면, 두 테이블의 모든 데이터를 포함하는 결과 집합을 얻을 수 있다. 이를 통해 `FULL OUTER JOIN`을 구할 수 있다.

 

7.2.3 CROSS JOIN(상호 조인)

`CROSS JOIN`은 두 테이블 간의 카테시안 곱(Cartesian product)을 반환하는 SQL 조인 유형이다. 이 조인은 한 테이블의 모든 행과 다른 테이블의 모든 행을 조합하여 가능한 모든 행의 조합을 생성한다. `CROSS JOIN`은 ON 키워드를 사용하지 않으며, 조인 되는 두 테이블 간의 관계를 고려하지 않고 단순히 모든 가능한 조합을 생성한다.

CROSS JOIN 사용 예시

 위  쿼리는 `TableA`의 각 행을 `TableB`의 모든 행과 결합한다. 결과로는 `TableA`와 `TableB`의 모든 가능한 행 조합이 반환된다.

  `CROSS JOIN`은 모든 가능한 옵션 조합을 생성할 때 유용하다. 예를 들어 상품 색상과 사이즈의 모든 조합을 리스트업 할 때 사용할 수 있다.

 

7.2.4 SELF JOIN(자체 조인)

SELF JOIN 사용 예시

 셀프 조인은 테이블이 자기 자신과 조인되는 경우를 말한다. 셀프 조인은 일반적으로 테이블 내에서 계층적인 데이터나 연관된 데이터를 조회할 때 사용된다. 

 위 쿼리는 `LEFT JOIN`을 사용하여 모든 직원(`e1`)과 그들의 관리자(`e2`)를 찾는다. 만약 어떤 직원이 관리자가 없다면, 관리자 이름은 NULL로 반환된다.

 

7.2.5 UNION / UNION ALL / NOT IN / IN

• UNION

UNION 사용 예시

 `UNION`은 여러 쿼리의 결과를 결합하면서 중복된 행을 제거한다. `UNION`을 사용할 때 각 쿼리는 같은 수의 컬럼을 가져야 하며, 해당 컬럼의 데이터 타입도 호환되어야 한다. `UNION`은 내부적으로 중복을 제거하기 위해 데이터를 정렬하는 과정이 필요하므로, `UNION ALL`에 비해 성능이 느릴 수 있다.

 위 쿼리는 `employee`와 `managers` 테이블 모두에서 이름과 부서 정보를 가져오지만, 중복된 행은 한 번만 결과에 포함된다.

• UNION ALL

UNION ALL 사용 예시

 `UNION ALL`은 쿼리의 결과를 단순히 결합하기만 하며, 중복된 행을 제거하지 않는다. 따라서 `UNION`에 비해 더 빠르게 작동한다. 같은 조건(같은 수의 컬럼과 호환 가능한 데이터입)이 적용되지만, 중복 제거를 하지 않기 때문에 일반적으로 `UNION`보다 성능이 좋다.

 위 쿼리는 두 테이블에서 이름과 부서 정보를 모두 가져오지만, 중복된 행도 결과에 모두 포함된다. 예를 들어, 같은 이름과 부서가 `employee`와 `managers`에 모두 존재한다면, 이 쿼리의 결과는 해당 행이 두 번 나타난다.

 

• NOT IN

NOT IN 사용 예시

`NOT IN`은 SQL에서  하위 쿼리 또는 명시된 값의 목록에 포함되지 않는 행을 찾을 때 사용하는 연산자이다. 위 쿼리는 관리자 ID로 등록되지 않은 모든 직원을 반환한다.

 

• IN

IN 사용 예시

`IN`은 SQL에서 하위 쿼리 또는 명시된 값의 목록에 포함되는 행을 찾을 때 사용하는 연산자이다. 위 쿼리는 `products` 테이블에서 `category` 열이 `electronics`, `books`, `clothes` 중 하나에 해당하는 모든 행을 조회한다.

 

7.3 SQL 프로그래밍

• DELIMITER

SQL의 끝을 표시하는 문자를 변경하는 데 사용된다. 기본적으로는 세미콜린이다. 그러나 프로시저나 함수같은 데이터베이스 객체를 생성할 때 여러 개의 문장을 하나의 단위로 묶어야 할 필요가 있다. 이럴 때 사용한다.

delimiter 사용 예시

위의 코드에서 `DELIMITER $$`는 쿼리 구분자를 `$$`로 변경한다. 프로시저를 생성한 후 `END $$`에서 새로운 구분자를 만나 프로시저의 끝을 인식한다. 그리고 마지막에 `DELIMITER ;`로 다시 기본 구분자인 세미콜론으로 복원한다.

 

7.3.1 IF...ELSE

조건부 로직을 실행하는데 사용된다. 한 문장 이상이 처리되어야 할 때는 BEGIN ... END와 함께 묶어줘야만 한다. 습관적으로 실행할 문장이 한 문장이라도 BEGIN ... END로 묶어주는 것이 좋다.

IF...ELSE 사용 예시

`num`의 값이 0보다 크면 양수,

`num`의 값이 0이면 0,

그 외의 경우엔 음수를 출력하는 IF문 예제이다.

 

7.3.2 CASE

다양한 조건에 따라 다른 결과를 반환할 때 사용된다. 프로그래밍 언어의 SWITCH-CASE 구조와 유사하다.

CASE문 사용 예시

위 쿼리는 `부서ID`가 1인 경우 인사부, 2인 경우 개발부, 3인 경우 영업부에 해당한다.

 

7.3.3 WHILE과 ITERATE/LEAVE

• WHILE문

WHILE문 예시

`WHILE` 문은 주어진 조건이 참인 동안, 코드 블록을 반복해서 실행한다.

 

• ITERATE문

ITERATE문 예시

`ITERATE` 문은 반복문 내에서 사용되며, 해당 반복문의 시작점으로 제어를 이동시켜 다음 반복을 진행하게 된다. `ITERATE`를 사용하기 위해서는 반복문에 라벨을 지정해야 한다.

 

• LEAVE 문

LEAVE 문

`LEAVE` 문은 반복문이나 시작된 블록의 실행을 종료시키고 블록 밖으로 제어를 이동시킨다. `ITERATE` 문과 마찬가지로 사용되는 반복문이나 블록에 라벨을 지정해야 사용할 수 있다.

 

while, leave 예시

위 프로시저는 `v_num` 변수를 1부터 시작하여 증가시키면서, 숫자를 출력한다. `v_num`이 5가 되면 `LEAVE` 구문을 사용하여 `my_loop` 라벨이 지정된 `WHILE` 반복문을 종료한다.

 

7.3.4 오류 처리

MySQL에서 오류 처리는 `DECLARE [핸들러 유형] HANDLER FOR [예외 유형] [처리할 문장]`의 형식으로 구현된다. 오류 처리기는 프로시저 또는 함수 내에서 예외가 발생했을 때 실행되는 특별한 코드 블록이다.

오류 처리 예시

위 예시에서 `1146`은 MySQL에서 `테이블이 존재하지 않을 때` 발생하는 오류 코드이다. `DECLARE CONTINUE HANDLER FOR 1146` 구문은 이 오류가 발생했을 때 처리기가 어떻게 동작할지 정의한다. 이 경우에는 오류 메시지를 선택하고 실행을 계속한다. `SELECT "테이블이 존재하지 않음" AS '오류';`는 사용자에게 오류 메시지를 알리기 위한 SQL 문장이다.

 

7.3.5 동적 SQL

동적 SQL은 실행 시점에서 SQL문장을 생성하고 실행할 수 있게 하는 기법이다. MySQL에서는 동적 SQL을 사용하기 위해 `PREPARE`, `EXECUTE`, 그리고 `DEALLOCATE PREPARE` 문을 사용한다.

 

• PREPARE문

`PREPARE` 문은 문자열로 주어진 쿼리를 실행할 준비를 한다. 이 문자열은 리터럴 문자열이거나, 사용자 변수, 또는 시스템 변수일 수 있다. `PREPARE`는 문자열을 받아서 SQL 문장으로 변환하고, 실행하기 전에 문장을 서버에게 알려준다.

PREPARE문 사용 예시

 

• EXECUTE문

`EXECUTE`문은 `PREPARE`에 의해 준비된 SQL문장을 실행한다. 문장에는 매개변수가 포함될 수 있으며, 이 경우 `EXECUTE` 문을 사용할 때 매개변수 값을 전달해야 한다.

EXECUTE 사용 예시

`USING` 절은 문장에서 사용되는 매개변수에 값을 바인딩하는 데 사용된다. 각 매개변수 `@var1`, `@var2` 등은 이전에 값을 할당받은 사용자 변수여야 한다.

 

• DEALLOCATE PREPARE 문

DEALLOCATE PREPARE 사용 예시

`DEALLOCATE PREPARE` 문은 더 이상 사용하지 않는 준비된 SQL 문장을 해제한다. 위 쿼리는 준비된 문장이 차지하는 메모리를 해제하고, 문장 이름 `stmt_name`을 재사용할 수 있게 한다.

 

동적 SQL 사용 예시

위 쿼리는 다음 단계를 수행한다.

1. 사용자 변수 `@s`에 SQL 쿼리 문자열을 저장한다. `?`를 사용하여 매개변수의 위치를 나타낸다

2. `PREPARE` 문을 사용하여 문자열을 SQL 문장으로 준비한다

3. 변수 `@a`와 `@b`에 값을 할당하여 쿼리의 매개변수로 사용한다

4. `EXECUTE` 는 준비된 stmt를 실행하고 `?` 위치에 `@a`와 `@b`의 값을 바인딩한다.

5. `DEALLOCATE PREPARE` 문을 사용하여 준비된 문장을 해제한다

 

• 장점

1. DBMS는 PARSING, EXECUTE, FETCH의 단계를 거쳐 쿼리를 처리한다. 동적 SQL을 사용하면 1번 PARSING 부분을 건너뛰기 때문에 속도적인 이점이 있다.

2. SQL문을 미리 PARSING하기 때문에 대입된 값은 SQL문으로 인식하지 않는다. 따라서 SQL Injection 취약점을 예방할 수 있다.

• 단점

1. 바인드 변수는 일부 허용된 위치에서만 사용이 가능하다. 테이블 이름이느 컬럼 이름과 같은 데이터베이스 스키마의 구조적 요소는 PREPARED STATEMENTS만으로 구성할 수 없다.

2. 쿼리에 오류가 발생했을 경우 바인드 변수 부분이 `?`로 출력되기 때문에 정확히 어떤 쿼리가 실행됐는지 파악하기 힘들다. 즉, 오류 분석에 어려움이 있다

 

위의 내용을 종합하여 봤을 때, PREPARED STATEMENTS는 DML(SELECT, INSERT, UPDATE, DELETE) 구문 처리 작업에 적합하다.