MySQL 저장 프로시저 기본

소개

이 실습에서는 MySQL 저장 프로시저의 기본 사항을 배우게 됩니다. 목표는 MySQL 데이터베이스 내에서 데이터를 관리하기 위해 저장 프로시저를 생성, 호출 및 수정하는 방법을 이해하는 것입니다.

먼저 employees라는 데이터베이스와 테이블을 생성합니다. 그런 다음 employees 테이블에 데이터를 삽입하기 위한 insert_employee라는 저장 프로시저를 작성합니다. CALL 문을 사용하여 이 프로시저를 호출하는 방법과 프로시저에 입력 매개변수를 추가하는 방법을 배우게 됩니다. 마지막으로 DROP PROCEDURE 문을 사용하여 프로시저를 삭제하는 방법을 배우게 됩니다.

참고: 이 실습에서는 처음에 MySQL 셸에 한 번만 접속하고 맨 마지막에 종료하면 됩니다. 다음 단계의 모든 SQL 명령은 동일한 MySQL 세션 내에서 실행해야 합니다. 단계 사이에 MySQL 에 반복적으로 연결하거나 연결을 해제할 필요가 없습니다.

데이터 삽입을 위한 프로시저 생성

이 단계에서는 테이블에 데이터를 삽입하는 MySQL 저장 프로시저를 만드는 방법을 배우게 됩니다. 저장 프로시저는 데이터베이스 내에 저장된 미리 컴파일된 SQL 문입니다. 이름으로 실행할 수 있으며, 이는 성능과 보안을 향상시킬 수 있습니다.

먼저 터미널을 열고 다음 명령을 사용하여 MySQL 서버에 연결합니다.

sudo mysql -u root

이 명령은 root 사용자로 MySQL 서버에 연결합니다. 이후 모든 단계에서 이 MySQL 세션을 열어 두십시오.

연결되면 MySQL 셸에 있게 됩니다. 이제 설정 중에 생성된 testdb 데이터베이스로 전환합니다.

USE testdb;

이제 올바른 데이터베이스에 있으므로 employees 테이블에 데이터를 삽입하는 저장 프로시저를 만들어 보겠습니다. 저장 프로시저는 CREATE PROCEDURE 문을 사용하여 생성됩니다. 새 직원 레코드를 삽입하는 insert_employee라는 프로시저를 정의할 것입니다.

다음은 저장 프로시저의 SQL 코드입니다.

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255))
BEGIN
    INSERT INTO employees (name, department) VALUES (employee_name, employee_department);
END //
DELIMITER ;

이 코드를 자세히 살펴보겠습니다.

• DELIMITER //: 이 구문은 문 구분 기호를 ;에서 //로 변경합니다. 프로시저 자체에 세미콜론이 포함되어 있으므로 전체 프로시저 정의를 단일 문으로 처리하도록 MySQL 에 알려야 하기 때문에 필요합니다.
• CREATE PROCEDURE insert_employee: 이 구문은 insert_employee라는 저장 프로시저의 생성을 선언합니다.
• (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255)): 이 구문은 프로시저의 입력 매개변수를 정의합니다. employee_name 및 employee_department는 매개변수의 이름이며 VARCHAR(255)는 해당 데이터 유형입니다. IN 키워드는 이러한 입력 매개변수임을 나타냅니다.
• BEGIN ... END: 이 블록에는 프로시저가 호출될 때 실행될 SQL 문이 포함됩니다.
• INSERT INTO employees (name, department) VALUES (employee_name, employee_department);: 이 SQL 문은 입력 매개변수로 전달된 값을 사용하여 employees 테이블에 새 행을 삽입합니다.
• DELIMITER ;: 이 구문은 문 구분 기호를 ;로 다시 재설정합니다.

이 코드를 실행하려면 MySQL 셸에 직접 복사하여 붙여넣으십시오.

코드를 실행한 후 MySQL 셸에서 다음 명령을 실행하여 프로시저가 생성되었는지 확인할 수 있습니다.

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

이 명령은 이름, 데이터베이스 및 생성 날짜를 포함하여 insert_employee 프로시저에 대한 정보를 표시합니다.

이제 employees 테이블에 데이터를 삽입하는 저장 프로시저를 성공적으로 만들었습니다. 다음 단계에서는 이 프로시저를 호출하는 방법을 배우게 됩니다.

CALL 문을 사용하여 프로시저 호출

이전 단계에서는 insert_employee라는 저장 프로시저를 만들었습니다. 이 단계에서는 CALL 문을 사용하여 이 프로시저를 호출하는 방법을 배우게 됩니다.

알림: 여전히 MySQL 셸에 있어야 하며 testdb 데이터베이스를 사용하고 있어야 합니다. 그렇지 않은 경우 다음 명령으로 전환하십시오.

USE testdb;

CALL 문은 저장 프로시저를 실행하는 데 사용됩니다. 구문은 다음과 같습니다.

CALL procedure_name(argument1, argument2, ...);

이 경우 프로시저 이름은 insert_employee이며 직원 이름과 직원 부서라는 두 개의 인수를 받습니다.

"Engineering" 부서의 "Alice Smith"라는 새 직원을 삽입하기 위해 insert_employee 프로시저를 호출해 보겠습니다.

CALL insert_employee('Alice Smith', 'Engineering');

이 문은 지정된 인수로 insert_employee 프로시저를 실행합니다.

데이터가 올바르게 삽입되었는지 확인하려면 MySQL 셸에서 employees 테이블을 쿼리할 수 있습니다.

SELECT * FROM employees;

테이블에 "Alice Smith"라는 이름과 "Engineering"이라는 부서가 있는 새 행이 표시되어야 합니다. id는 자동으로 할당됩니다.

"Marketing" 부서의 "Bob Johnson"이라는 다른 직원을 삽입해 보겠습니다.

CALL insert_employee('Bob Johnson', 'Marketing');

다시 employees 테이블을 쿼리하여 삽입을 확인합니다.

SELECT * FROM employees;

이제 테이블에 "Alice Smith"에 대한 행 하나와 "Bob Johnson"에 대한 행 하나, 총 두 개의 행이 표시되어야 합니다.

이제 CALL 문을 사용하여 저장 프로시저 insert_employee를 성공적으로 호출하고 데이터가 올바르게 삽입되었는지 확인했습니다. 이는 저장 프로시저를 사용하여 SQL 논리를 캡슐화하고 재사용하는 방법을 보여줍니다.

프로시저에 입력 매개변수 추가

이전 단계에서는 insert_employee라는 저장 프로시저를 만들고 호출했으며, 이 프로시저는 employee_name과 employee_department라는 두 개의 입력 매개변수를 받습니다. 이 단계에서는 프로시저에 다른 입력 매개변수를 추가하는 방법을 배우게 됩니다.

insert_employee 프로시저에 employee_salary 매개변수를 추가해 보겠습니다. 이렇게 하면 새 레코드를 삽입할 때 직원의 급여를 지정할 수 있습니다.

먼저 기존 프로시저를 삭제해야 합니다. 삭제하지 않으면 동일한 이름의 프로시저를 만들려고 할 때 오류가 발생합니다. MySQL 셸에서 다음을 실행합니다.

DROP PROCEDURE IF EXISTS insert_employee;

이제 새 입력 매개변수가 있는 수정된 저장 프로시저를 만들어 보겠습니다.

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255), IN employee_salary DECIMAL(10, 2))
BEGIN
    INSERT INTO employees (name, department, salary) VALUES (employee_name, employee_department, employee_salary);
END //
DELIMITER ;

변경 사항을 자세히 살펴보겠습니다.

• 프로시저 정의에 IN employee_salary DECIMAL(10, 2)라는 새 입력 매개변수를 추가했습니다. DECIMAL(10, 2)는 급여의 데이터 유형으로, 최대 10 자리 숫자와 소수점 이하 2 자리를 허용합니다.
• INSERT 문을 수정하여 salary 열과 employee_salary 매개변수를 포함했습니다.

이제 수정된 insert_employee 프로시저를 호출하여 "Finance" 부서의 "Charlie Brown"이라는 새 직원을 급여 60000.00 으로 삽입해 보겠습니다.

CALL insert_employee('Charlie Brown', 'Finance', 60000.00);

데이터가 올바르게 삽입되었는지 확인하려면 MySQL 셸에서 employees 테이블을 쿼리할 수 있습니다.

SELECT * FROM employees;

테이블에 "Charlie Brown"이라는 이름, "Finance"라는 부서, 60000.00 이라는 급여가 있는 새 행이 표시되어야 합니다.

이제 저장 프로시저 insert_employee에 입력 매개변수를 성공적으로 추가하고 데이터가 올바르게 삽입되었는지 확인했습니다. 이는 저장 프로시저를 수정하여 새 요구 사항을 수용하는 방법을 보여줍니다.

프로시저 삭제

이 마지막 단계에서는 데이터베이스에서 저장 프로시저를 삭제 (제거) 하는 방법을 배우게 됩니다. 프로시저를 삭제하면 데이터베이스에서 제거되어 더 이상 실행할 수 없게 됩니다.

알림: 여전히 MySQL 셸에 있어야 하며 testdb 데이터베이스를 사용하고 있어야 합니다.

DROP PROCEDURE 문은 저장 프로시저를 제거하는 데 사용됩니다. 구문은 다음과 같습니다.

DROP PROCEDURE [IF EXISTS] procedure_name;

IF EXISTS 절은 선택 사항이지만 권장됩니다. 프로시저가 존재하지 않는 경우 오류가 발생하는 것을 방지합니다.

이 경우 프로시저 이름은 insert_employee입니다. 프로시저를 삭제해 보겠습니다.

DROP PROCEDURE IF EXISTS insert_employee;

이 문은 testdb 데이터베이스에서 insert_employee 프로시저를 제거합니다.

프로시저가 삭제되었는지 확인하려면 MySQL 셸에서 프로시저 상태를 다시 표시해 볼 수 있습니다.

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

이 명령은 프로시저가 더 이상 존재하지 않음을 나타내는 빈 결과 집합을 반환해야 합니다.

또는 프로시저를 호출하려고 하면 오류가 발생합니다.

CALL insert_employee('Test', 'Test', 1000);

그러면 ERROR 1305 (42000): PROCEDURE testdb.insert_employee does not exist와 유사한 오류 메시지가 표시됩니다.

이제 저장 프로시저 insert_employee를 성공적으로 삭제했습니다.

이제 다음을 입력하여 MySQL 셸을 종료할 수 있습니다.

exit

이것으로 MySQL 에서 저장 프로시저를 만들고, 호출하고, 수정하고, 삭제하는 것에 대한 실습이 완료되었습니다.

요약

이 실습에서는 employees라는 데이터베이스와 테이블을 만드는 것부터 시작하여 MySQL 저장 프로시저의 기본 사항을 배웠습니다. 그런 다음 CREATE PROCEDURE 문을 사용하여 employees 테이블에 데이터를 삽입하는 insert_employee라는 저장 프로시저를 정의했습니다. DELIMITER 명령은 프로시저 정의 내의 세미콜론을 처리하는 데 사용되었습니다.

이 실습에서는 저장 프로시저의 입력 매개변수를 정의하고 이름과 데이터 유형을 지정하는 방법도 다루었습니다. 이를 통해 프로시저를 호출할 때 값을 전달할 수 있어 더 유연하고 재사용 가능하게 만들 수 있습니다. CALL 문을 사용하여 저장 프로시저를 호출하는 연습을 하고 데이터 삽입을 확인했습니다. 마지막으로 DROP PROCEDURE 문을 사용하여 저장 프로시저를 삭제하는 방법을 배웠습니다.