MySQL 함수와 사용자 정의 로직

소개

이 랩에서는 MySQL 에서 사용자 정의 함수를 생성하고 사용하는 방법을 배웁니다. 이 랩은 사각형의 넓이를 계산하는 함수를 생성하여 데이터베이스 내에서 재사용 가능한 로직을 캡슐화하는 데 중점을 둡니다.

단계는 MySQL 서버에 연결하고, 데이터베이스를 생성하고 (필요한 경우), 길이와 너비를 위한 입력 매개변수로 calculate_rectangle_area 함수를 정의하고, 반환 데이터 유형을 지정하는 것을 포함합니다. 함수 본문은 넓이를 계산하고 결과를 반환합니다. 후속 단계에서는 SELECT 쿼리에서 이 함수를 사용하고, 샘플 데이터로 출력을 테스트하고, 마지막으로 함수를 삭제하는 방법을 다룹니다.

MySQL 에 연결하고 데이터베이스 생성하기

이 단계에서는 MySQL 서버에 연결하고 사용자 정의 함수를 생성하고 사용할 데이터베이스를 생성합니다.

먼저 터미널을 열고 root 사용자로 MySQL 서버에 연결합니다. 랩 시작 시 한 번 다음 명령을 실행합니다.

sudo mysql -u root

이 명령은 sudo를 사용하여 mysql 클라이언트를 root 사용자로 실행합니다. 이 환경의 root 사용자는 비밀번호가 설정되어 있지 않으므로 MySQL 셸에 직접 연결됩니다. mysql> 프롬프트가 표시됩니다.

이후 모든 단계에서 MySQL 셸에 계속 머물러 있어야 합니다. 랩이 끝날 때까지 종료하지 마십시오.

이제 my_functions_db라는 데이터베이스를 생성합니다. 이 데이터베이스는 테이블과 함수를 저장하는 데 사용됩니다. mysql> 프롬프트에서 다음 SQL 명령을 실행합니다.

CREATE DATABASE IF NOT EXISTS my_functions_db;

데이터베이스를 생성한 후에는 다음 작업을 위해 기본 데이터베이스로 선택합니다.

USE my_functions_db;

데이터베이스가 변경되었음을 나타내는 메시지가 표시되어야 합니다.

이제 my_functions_db 데이터베이스에 연결되었으며 다음 단계에서 사용자 정의 함수를 생성할 준비가 되었습니다.

사각형 넓이 계산 함수 생성

이 단계에서는 사각형의 넓이를 계산하는 사용자 정의 함수를 MySQL 에 생성합니다. 이 함수는 길이와 너비를 입력으로 받아 계산된 넓이를 반환합니다.

이전 단계에서 설명한 대로 MySQL 셸에 계속 접속해 있고 my_functions_db 데이터베이스를 선택했는지 확인하십시오.

이제 calculate_rectangle_area라는 함수를 생성합니다. 이 함수는 length와 width라는 두 개의 정수 매개변수를 받아 넓이를 나타내는 정수를 반환합니다.

MySQL 셸에서 다음 SQL 명령을 실행합니다.

DELIMITER //

CREATE FUNCTION calculate_rectangle_area (length INT, width INT)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE area INT;
  SET area = length * width;
  RETURN area;
END //

DELIMITER ;

이 명령을 자세히 살펴보겠습니다.

• DELIMITER //: 기본 세미콜론 (;) 에서 문 구분 기호를 //로 변경합니다. 함수 정의 본문 내에 세미콜론이 포함되어 있기 때문에 이 작업이 필요합니다.
• CREATE FUNCTION calculate_rectangle_area (length INT, width INT): 함수 이름 (calculate_rectangle_area) 과 입력 매개변수 (length 및 width, 둘 다 INT 타입) 를 정의합니다.
• RETURNS INT: 함수가 정수 값을 반환함을 지정합니다.
• DETERMINISTIC: 이 키워드는 동일한 입력 값에 대해 함수가 항상 동일한 출력을 생성함을 나타냅니다. 이는 MySQL 이 함수를 사용하는 쿼리를 최적화하는 데 도움이 됩니다.
• BEGIN ... END: 이 블록은 함수의 로직을 포함합니다.
• DECLARE area INT;: 함수 내에서 INT 타입의 area라는 지역 변수를 선언합니다.
• SET area = length * width;: length와 width 매개변수를 곱하여 넓이를 계산하고 결과를 area 변수에 할당합니다.
• RETURN area;: area 변수에 저장된 값을 함수의 출력으로 반환합니다.
• END //: 임시 구분 기호 //를 사용하여 함수 정의의 끝을 표시합니다.
• DELIMITER ;: 구분 기호를 기본 세미콜론 (;) 으로 되돌립니다.

이 명령을 실행하면 my_functions_db 데이터베이스에 calculate_rectangle_area 함수가 생성됩니다.

함수가 성공적으로 생성되었는지 확인하려면 현재 데이터베이스의 함수 목록을 볼 수 있습니다.

SHOW FUNCTION STATUS LIKE 'calculate_rectangle_area';

이 명령은 함수에 대한 정보를 표시하여 존재 여부를 확인합니다.

SELECT 쿼리에서 함수 사용하기

calculate_rectangle_area 함수를 생성했으므로 이제 테이블에 저장된 데이터를 기반으로 사각형의 넓이를 계산하기 위해 SELECT 쿼리에서 이 함수를 사용할 수 있습니다.

동일한 MySQL 셸 세션에서 계속 작업하고 my_functions_db 데이터베이스를 사용하고 있는지 확인하십시오.

먼저 다양한 사각형의 치수를 저장할 rectangles라는 테이블을 생성합니다. 다음 SQL 명령을 실행합니다.

CREATE TABLE IF NOT EXISTS rectangles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  length INT,
  width INT
);

이 명령은 자동 증가하는 id를 기본 키로 하고 length 및 width 열을 가진 테이블을 생성합니다.

다음으로 rectangles 테이블에 샘플 데이터를 삽입합니다.

INSERT INTO rectangles (length, width) VALUES (5, 10);
INSERT INTO rectangles (length, width) VALUES (7, 8);
INSERT INTO rectangles (length, width) VALUES (12, 3);

이 명령은 서로 다른 길이 및 너비 값을 가진 세 개의 행을 rectangles 테이블에 추가합니다.

이제 SELECT 쿼리에서 calculate_rectangle_area 함수를 사용하여 사각형 치수와 계산된 넓이를 검색할 수 있습니다. 다음 SQL 명령을 실행합니다.

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

이 쿼리에서:

• SELECT id, length, width: rectangles 테이블에서 id, length, width 열을 선택합니다.
• calculate_rectangle_area(length, width): calculate_rectangle_area 함수를 호출하고 각 행의 length 및 width 열의 값을 인수로 전달합니다.
• AS area: 함수에서 반환된 결과에 area라는 별칭을 할당하여 출력 열을 더 읽기 쉽게 만듭니다.
• FROM rectangles: 데이터를 rectangles 테이블에서 검색해야 함을 지정합니다.

이 쿼리의 출력은 테이블의 각 사각형에 대한 id, length, width 및 계산된 area를 보여줍니다. 다음과 유사한 출력이 표시되어야 합니다.

+----+--------+-------+------+
| id | length | width | area |
+----+--------+-------+------+
|  1 |      5 |    10 |   50 |
|  2 |      7 |     8 |   56 |
|  3 |     12 |     3 |   36 |
+----+--------+-------+------+

이를 통해 사용자 정의 함수를 표준 SQL 쿼리 내에서 효과적으로 사용하여 데이터에 대한 계산을 수행하는 방법을 보여줍니다.

샘플 데이터로 함수 출력 테스트

이 단계에서는 rectangles 테이블에 더 많은 샘플 데이터를 삽입하고 계산된 넓이를 확인하여 calculate_rectangle_area 함수를 추가로 테스트합니다. 이를 통해 함수가 다양한 입력 값으로 올바르게 작동하는지 확인할 수 있습니다.

동일한 MySQL 셸 세션에서 계속 작업하고 my_functions_db 데이터베이스를 사용하고 있는지 확인하십시오.

이제 다른 길이 및 너비 값을 가진 행을 rectangles 테이블에 추가합니다.

INSERT INTO rectangles (length, width) VALUES (10, 10);
INSERT INTO rectangles (length, width) VALUES (2, 5);
INSERT INTO rectangles (length, width) VALUES (15, 4);

이 명령은 rectangles 테이블에 세 개의 행을 더 추가합니다.

이제 새로 추가된 행을 포함하여 모든 사각형의 계산된 넓이를 보기 위해 SELECT 쿼리를 다시 실행합니다.

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

출력에는 이제 새 사각형의 넓이가 포함됩니다.

+----+--------+-------+------+
| id | length | width | area |
+----+--------+-------+------+
|  1 |      5 |    10 |   50 |
|  2 |      7 |     8 |   56 |
|  3 |     12 |     3 |   36 |
|  4 |     10 |    10 |  100 |
|  5 |      2 |     5 |   10 |
|  6 |     15 |     4 |   60 |
+----+--------+-------+------+

출력을 검토하고 새 행에 대한 계산된 넓이가 올바른지 확인합니다.

• 길이 10, 너비 10 의 경우 넓이는 100 (10 * 10) 입니다.
• 길이 2, 너비 5 의 경우 넓이는 10 (2 * 5) 입니다.
• 길이 15, 너비 4 의 경우 넓이는 60 (15 * 4) 입니다.

이 단계는 다양한 데이터로 함수를 사용하는 방법에 대한 이해를 강화하고 정확성을 확인합니다.

함수 및 테이블 삭제

마지막 단계에서는 calculate_rectangle_area 함수와 rectangles 테이블을 삭제하여 데이터베이스를 정리합니다. 더 이상 필요하지 않은 객체를 제거하는 것은 좋은 습관입니다.

동일한 MySQL 셸 세션에서 계속 작업하고 my_functions_db 데이터베이스를 사용하고 있는지 확인하십시오.

먼저 calculate_rectangle_area 함수를 삭제합니다. 다음 SQL 명령을 실행합니다.

DROP FUNCTION IF EXISTS calculate_rectangle_area;

• DROP FUNCTION: 함수를 제거하는 데 사용되는 명령입니다.
• IF EXISTS: 함수가 존재하지 않는 경우 오류를 방지하는 절입니다.

다음으로 rectangles 테이블을 삭제합니다. 다음 SQL 명령을 실행합니다.

DROP TABLE IF EXISTS rectangles;

• DROP TABLE: 테이블을 제거하는 데 사용되는 명령입니다.
• IF EXISTS: 테이블이 존재하지 않는 경우 오류를 방지하는 절입니다.

이 명령을 실행한 후 함수와 테이블 모두 my_functions_db 데이터베이스에서 제거됩니다.

함수가 삭제되었는지 확인하려면 함수를 다시 나열해 볼 수 있습니다.

SHOW FUNCTION STATUS LIKE 'calculate_rectangle_area';

이 명령은 이제 빈 결과 집합을 반환해야 하며, 이는 함수가 제거되었음을 나타냅니다.

테이블이 삭제되었는지 확인하려면 테이블에서 선택해 볼 수 있습니다.

SELECT * FROM rectangles;

이 명령은 ERROR 1146 (42S02): Table 'my_functions_db.rectangles' doesn't exist와 유사한 오류를 반환해야 하며, 이는 테이블이 제거되었음을 나타냅니다.

이제 exit를 입력하고 Enter 키를 눌러 MySQL 셸을 종료할 수 있습니다.

MySQL 에서 사용자 정의 함수와 테이블을 성공적으로 생성, 사용, 테스트 및 삭제했습니다.

요약

이 실습에서는 재사용 가능한 로직을 캡슐화하기 위해 MySQL 에서 함수를 생성하는 방법을 배웠습니다. 이 과정에는 MySQL 서버에 연결하고, 데이터베이스를 생성 (필요한 경우) 한 다음, 입력 매개변수, 반환 데이터 유형 및 DETERMINISTIC 키워드를 사용하여 함수를 정의하는 작업이 포함되었습니다.

구체적으로, 사각형의 길이와 너비를 입력으로 받아 넓이를 반환하는 calculate_rectangle_area 함수를 생성했습니다. 함수 본문에는 계산된 넓이를 저장할 로컬 변수를 선언하고 RETURN 문을 사용하여 결과를 반환하는 내용이 포함되었습니다.

그런 다음 테이블에 저장된 데이터에 대한 계산을 수행하기 위해 SELECT 쿼리에서 이 함수를 사용하는 방법을 배웠습니다. 또한 추가 샘플 데이터를 사용하여 함수의 정확성을 테스트했습니다. 마지막으로 데이터베이스 환경을 정리하기 위해 함수와 테이블을 삭제하는 방법을 배웠습니다.

이 실습은 MySQL 에서 사용자 정의 함수를 생성하고 사용하는 실용적인 소개를 제공했으며, 이는 로직을 캡슐화하고 데이터베이스 작업의 효율성을 향상시키는 데 유용한 기술입니다.