02_06 내장 함수와 제어문 실습

하위 태스크 1

함수 예제 실행

숫자/문자/날짜/NULL 처리 함수 예제 스크립트 실행 및 결과 분석

함수	입력	출력
CONCAT	`MARY, ', ', SMITH`	`MARY, SMITH`
	`NULL, MARY, SMITH`	NULL
CONCAT_WS	`', ', MARY, SMITH, MARY.SMITH@sakilacustomer.org`	`MARY, SMITH, MARY.SMITH@sakilacustomer.org`
	`', ', MARY, NULL, SMITH`	`MARY, SMITH`
NOW		`2026-01-12 13:40:48`
	`3`	`2026-01-12 14:14:17.141`
IFNULL	`NULL, ''`	빈 문자열
	`NULL, 'col_3'`	`col_3`
COALESCE	`NULL, NULL, NULL, 'col_5'`	`col_5`
	`NULL, NULL, NULL, NULL`	NULL
LOWER	`'Do it! SQL'`	`do it! sql`
	`'MARY.SMITH\@sakilacustomer.org'`	`mary.smith@sakilacustomer.org`
UPPER	`'Do it! SQL'`	`DO IT! SQL`
	`'MARY.SMITH\@sakilacustomer.org'`	`MARY.SMITH@SAKILACUSTOMER.ORG`
LTRIM	`' Do it! MySQL'`	`Do it! MySQL`
RTRIM	`'Do it! MySQL '`	`Do it! MySQL`
TRIM	`' Do it! MySQL '`	`Do it! MySQL`
	`BOTH '#' FROM '# Do it! MySQL #'`	`Do it! MySQL`
LENGTH	`'Do it! MySQL'`	`12`
	`'두잇 마이에스큐엘'`	`25`
	’`A'`	`1`
	`'강'`	`3`
	`'漢'`	`3`
	`'◁'`	`3`
	`' '`	`1`
CHAR_LENGTH	`'Do it! MySQL'`	`12`
	`'두잇 마이에스큐엘'`	`9`
POSITION	`'!' IN 'Do it!! MySQL'`	`6`
	`'#' IN 'Do it!! MySQL'`	`0`
LEFT	`'Do it! MySQL', 2`	`Do`
RIGHT	`'Do it! MySQL', 2`	`QL`
SUBSTRING	`'Do it! MySQL', 4, 2`	`it`
	`'MARY', 2, 3`	`ARY`
	`'abc@email.com', 1, POSITION('@' IN 'abc@email.com') -1`	`abc`
REPLACE	`'ANGELA', 'A', 'C'`	`CNGELC`
REPEAT	`'0', 10`	`0000000000`
SPACE	`10`
REVERSE	`'Do it! MySQL'`	`LQSyM !ti oD`
STRCMP	`'Do it! MySQL', 'Do it! MySQL'`	`0`
CURRENT_DATE		`2026-01-12`
CURRENT_TIME		`14:13:20`
	`3`	`14:14:17.141`
CURRENT_TIMESTAMP		`2026-01-12 14:13:20`
	`3`	`2026-01-12 14:14:17.141`
UTC_DATE		`2026-01-12`
UTC_TIME	`3`	`05:15:06.451`
UTC_TIMESTAMP	`3`	`2026-01-12 05:15:06.451`
DATE_ADD	`NOW(), INTERVAL 1 YEAR`	`2027-01-12 14:15:45`
	`NOW(), INTERVAL -1 YEAR`	`2025-01-12 14:16:03`
DATE_SUB	`NOW(), INTERVAL 1 YEAR`	`2025-01-12 14:16:37`
	`NOW(), INTERVAL -1 YEAR`	`2027-01-12 14:16:37`
DATEDIFF	`'2023-12-31 23:59:59.9999999', '2023-01-01 00:00:00.0000000'`	`365`
TIMESTAMPDIFF	`MONTH, '2023-12-31 23:59:59.9999999', '2023-01-01 00:00:00.0000000'`	`-12`
DAYNAME	`'2023-08-20'`	`Sunday`
YEAR	`'2023-08-20'`	`2023`
MONTH	`'2023-08-20'`	`8`
WEEK	`'2023-08-20'`	`34`
DAY	`'2023-08-20'`	`20`
DATE_FORMAT	`'2023-08-20 20:23:01', '%m/%d/%Y'`	`08/20/2023`
	`NOW(), GET_FORMAT(DATE, 'USA')`	`01.12.2026`
GET_FORMAT	`DATE, 'USA'`	`%m.%d.%Y`
COUNT	`*`	전체 행의 수
	`address2`	`address2` 열에서 NULL을 제외한 행의 수
SUM	`amount`	`amount` 열의 합산
AVG	`amount`	`amount` 열의 평균
MIN	`amount`	`amount` 열의 최솟값
MAX	`amount`	`amount` 열의 최댓값
STDDEV	`amount`	`amount` 열의 표준 편차
STDDEV_SAMP	`amount`	`amount` 열의 표본 표준 편차

하위 태스크 2

형 변환/오버플로 실험

CAST/CONVERT 와 큰 수 연산 예제를 실행해 오버플로 처리 이해

함수	입력	출력	비고
CAST	`NOW() AS SIGNED`	`20260112142726`
	`20230819 AS DATE`	`2023-08-19`
	`20230819 AS CHAR`	`20230819`
	`NOW(), SIGNED`	`20260112142810`
	`20230819, DATE`	`2023-08-19`
	`20230819, CHAR(5)`	`20230`
	`9223372036854775807 AS UNSIGNED`	`9223372036854775807`	오버플로우 방지
CONVERT	`9223372036854775807, UNSIGNED`	`9223372036854775807`	오버플로우 방지

하위 태스크 3

파생 컬럼 만들기

CONCAT, DATE_FORMAT, CASE 등을 사용한 리포트용 컬럼 생성

고객 이름 + 이메일을 하나의 문자열로 결합:

SELECT
	CONCAT(first_name, ' ', last_name, ': ', email)
FROM
	customer;

결제 일시에서 연/월/일/요일 추출:

SELECT
	YEAR(payment_date),
	MONTH(payment_date),
	DAY(payment_date),
	DAYNAME(payment_date)
FROM
	payment;

결제 금액 구간(예: 0~10, 10~20 등)을 CASE로 구분한 등급 컬럼:

SELECT
	CASE
		WHEN amount <= 10 THEN 'C'
		WHEN amount <= 20 THEN 'B'
		ELSE 'A'
	END
FROM
	payment;

하위 태스크 4

IF/CASE 예제 분석

IF/CASE 기반 SELECT/프로시저 예제를 읽고 구조 파악

doit_proc 프로시저:

지역 변수: customer_cnt(INT 형), add_number(INT 형)

DELIMITER $$
CREATE PROCEDURE doit_proc()
BEGIN
	DECLARE customer_cnt INT; -- 지역 변수 선언
	DECLARE add_number INT; -- 지역 변수 선언
	
	SET customer_cnt = 0;
	SET add_number = 100;
	SET customer_cnt = (SELECT COUNT(*) FROM customer);
	
	SELECT customer_cnt + add_number;
END $$
DELIMITER ;

doit_if 프로시저:

파라미터: customer_id_input(INT 형)
지역 변수: store_id_i(INT 형), s_id_one(INT 형), s_id_two(INT 형)

DELIMITER $$
CREATE PROCEDURE doit_if (customer_id_input INT) -- 파라미터 선언
BEGIN
	DECLARE store_id_i INT; -- 지역 변수 선언
	DECLARE s_id_one INT; -- 지역 변수 선언
	DECLARE s_id_two INT; -- 지역 변수 선언
	SET store_id_i = (SELECT store_id FROM customer WHERE customer_id = customer_id_input);
	
	IF store_id_i = 1 THEN SET s_id_one = 1; -- IF 문 시작
		ELSE SET s_id_two = 2;
	END IF; -- IF 문 종료
	
	SELECT store_id_i, s_id_one, s_id_two;
END $$
DELIMITER ;

doit_case 프로시저:

매개변수: customer_id_input(INT 형)
지역 변수: customer_level(VARCHAR 형), amount_sum(FLOAT 형)

DELIMITER $$
CREATE PROCEDURE doit_case (customer_id_input INT) -- 매개변수 선언
BEGIN
	DECLARE customer_level VARCHAR(10); -- 지역 변수 선언
	DECLARE amount_sum float; -- 지역 변수 선언
 
	SET amount_sum = (SELECT SUM(amount) FROM payment WHERE customer_id = customer_id_input GROUP BY customer_id);
	
	CASE -- CASE 문 시작
		WHEN amount_sum >= 150 THEN SET customer_level = 'VVIP';
		WHEN amount_sum >= 120 THEN SET customer_level = 'VIP';
		WHEN amount_sum >= 100 THEN SET customer_level = 'GOLD';
		WHEN amount_sum >= 80 THEN SET customer_level = 'SILVER';
		ELSE SET customer_level = 'BRONZE';
	END CASE; -- CASE 문 종료
	
	SELECT customer_id_input as customer_id, amount_sum, customer_level;
END $$
DELIMITER ;

doit_while 프로시저:

매개변수: param_1(INT 형), param_2(INT 형)
지역 변수: i(INT 형), while_sum(INT 형)

DELIMITER $$
CREATE PROCEDURE doit_while (param_1 INT, param_2 INT) -- 매개변수 선언
BEGIN
	DECLARE i INT; -- 지역 변수 선언
	DECLARE while_sum INT; -- 지역 변수 선언
	
	SET i = 1;
	SET while_sum = 0;
	
	WHILE (i <= param_1) DO -- WHILE 문 시작
		SET while_sum = while_sum + param_2;
		SET i = i + 1;
	END WHILE; -- WHILE 문 종료
	
	SELECT while_sum;
END $$
DELIMITER ;

doit_dynamic 프로시저:

매개변수: t_name(VARCHAR 형), c_name(VARCHAR 형), customer_id(INT 형)
사용자 정의 변수: @t_name(동적), @c_name(동적), @customer_id(동적), @sql(동적)

DELIMITER $$
CREATE PROCEDURE doit_dynamic (t_name VARCHAR(50), c_name VARCHAR(50), customer_id INT) -- 매개변수 선언
BEGIN
	SET @t_name = t_name; -- 사용자 정의 변수 할당
	SET @c_name = c_name; -- 사용자 정의 변수 할당
	SET @customer_id = customer_id; -- 사용자 정의 변수 할당
	SET @sql = CONCAT('SELECT ', @c_name, ' FROM ', @t_name, ' WHERE customer_id =', @customer_id); -- 사용자 정의 변수 할당
	
	SELECT @sql;
	
	PREPARE dynamic_query FROM @sql;
	
	EXECUTE dynamic_query;
	
	DEALLOCATE PREPARE dynamic_query;
END $$
DELIMITER ;

하위 태스크 5

WHILE/반복 로직 실습

WHILE 을 이용한 반복 합계 등 간단 로직을 프로시저로 구현

다음 코드 블록의 loop_sum 프로시저는 1부터 100까지의 합을 조회한다.

DELIMITER $$
CREATE PROCEDURE loop_sum ()
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE total_result INT DEFAULT 0;
 
	WHILE (i <= 100) DO
		SET total_result = total_result + i;
		SET i = i + 1;
	END WHILE;
	
	SELECT total_result;
END
DELIMITER ;
 
CALL loop_sum();

하위 태스크 6

나만의 프로시저 설계/구현

입력 파라미터를 받아 비즈니스 로직을 수행하는 프로시저 작성

다음은 max_id보다 customer_id가 작은 고객 중 활성 고객의 수를 구하는 프로시저다.

DELIMITER $$
CREATE PROCEDURE get_active_customer_nums(max_id INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE active_nums INT DEFAULT 0;
	DECLARE curr_active INT;
	
	WHILE (i <= max_id) DO
		SELECT active INTO curr_active
		FROM customer
		WHERE customer_id = i;
	
		IF curr_active = 1 THEN
			SET active_nums = active_nums + 1;
		END IF;
		
		SET i = i + 1;
	END WHILE;
		
	SELECT active_nums;
END
DELIMITER ;
 
CALL get_active_customer_nums(100);

하위 태스크 7

프로시저 관리 연습

SHOW CREATE/DROP/재생성 등 프로시저 관리 작업 수행

다음은 고객 식별자가 cust_id인 고객의 총 지불액과 고객 등급을 조회하는 get_sum_amount_and_grade 프로시저다.

DELIMITER $$
CREATE PROCEDURE get_sum_amount_and_grade(cust_id INT)
BEGIN
	SELECT 
	    customer_id, 
	    amount_sum,
	    CASE 
	        WHEN amount_sum >= 100 THEN 'VVIP'
	        WHEN amount_sum >= 80 THEN 'VIP'
	        ELSE 'NORMAL'
	    END AS customer_grade
	FROM (
	    SELECT customer_id, SUM(amount) AS amount_sum
	    FROM payment
	    GROUP BY customer_id
	) AS sub
	WHERE customer_id = cust_id;
END
DELIMITER ;

get_sum_amount_and_grade 프로시저의 정의를 조회한다.

SHOW CREATE PROCEDURE get_sum_amount_and_grade;

get_sum_amount_and_grade 프로시저를 호출한다.

CALL get_sum_amount_and_grade(1);

get_sum_amount_and_grade 프로시저를 삭제한다.

DROP PROCEDURE get_sum_amount_and_grade;

RADIANT GARDEN

탐색기

최근 게시글

자산관리테크 서비스의 이해

03_04 반응형 웹 실습

테크니컬 라이터가 알려주는 무조건 읽히는 실전 글쓰기

02_06 내장 함수와 제어문 실습

하위 태스크 1

하위 태스크 2

하위 태스크 3

하위 태스크 4

하위 태스크 5

하위 태스크 6

하위 태스크 7

그래프 뷰

목차