MySQL에서 저장 프로시저를 사용하는 방법

저자는 Write for DOnations 프로그램을 선택했습니다.

소개

일반적으로 관계형 데이터베이스로 작업할 때 애플리케이션 코드 내에서 직접 개별 DELETE를 실행합니다. 이러한 문은 기본 데이터베이스 테이블에서 직접 작동하고 조작합니다. 동일한 명령문 또는 명령문 그룹이 동일한 데이터베이스에 액세스하는 여러 애플리케이션 내에서 사용되는 경우 종종 개별 애플리케이션에서 중복됩니다.

다른 많은 관계형 데이터베이스 관리 시스템과 마찬가지로 MySQL은 저장 프로시저 사용을 지원합니다. 저장 프로시저는 공통 이름으로 재사용할 수 있도록 하나 이상의 SQL 문을 그룹화하여 데이터베이스 자체 내에서 공통 비즈니스 논리를 캡슐화하는 데 도움이 됩니다. 이러한 프로시저는 일관된 방식으로 데이터를 검색하거나 조작하기 위해 데이터베이스에 액세스하는 애플리케이션에서 호출할 수 있습니다.

저장 프로시저를 사용하면 데이터베이스 사용자가 기본 테이블에 직접 액세스하고 임의 쿼리를 실행하지 못하도록 제한하여 여러 응용 프로그램에서 공통 작업에 사용할 재사용 가능한 루틴을 생성하거나, 데이터 유효성 검사를 제공하거나, 데이터 액세스 보안의 추가 계층을 제공할 수 있습니다.

이 자습서에서는 저장 프로시저가 무엇인지, 데이터를 반환하고 입력 및 출력 매개 변수를 모두 사용하는 기본 저장 프로시저를 만드는 방법을 배웁니다.

전제 조건

이 가이드를 따르려면 SQL 기반 RDBMS(관계형 데이터베이스 관리 시스템)를 실행하는 컴퓨터가 필요합니다. 이 가이드의 지침과 예제는 다음 환경을 사용하여 검증되었습니다.

Ubuntu 20.04용 초기 서버 설정 가이드에 설명된 대로 관리 권한이 있는 루트가 아닌 사용자와 UFW로 구성된 방화벽이 있는 Ubuntu 20.04를 실행하는 서버
3단계에 설명된 대로 서버에 MySQL을 설치하고 보호합니다.
How To SELECT Rows FROM Tables in SQL 가이드에 설명된 대로 데이터베이스에서 데이터를 검색하기 위해 SELECT 쿼리를 실행하는 것과 관련된 기본 지식.

참고: 많은 RDBMS는 고유한 SQL 구현을 사용하며 저장 프로시저 구문은 공식 SQL 표준의 일부가 아닙니다. 이 자습서에 설명된 명령은 다른 RDBMS에서 작동할 수 있지만 저장 프로시저는 데이터베이스에 따라 다르므로 MySQL 이외의 시스템에서 테스트하는 경우 정확한 구문이나 출력이 다를 수 있습니다.

저장 프로시저 사용을 보여주는 테이블을 만들 수 있는 빈 데이터베이스도 필요합니다. MySQL 서버에 연결하고 이 가이드의 예제에 사용된 테스트 데이터베이스를 만드는 방법에 대한 자세한 내용은 다음 MySQL에 연결 및 샘플 데이터베이스 설정 섹션을 참조하십시오.

MySQL에 연결 및 샘플 데이터베이스 설정

이 섹션에서는 이 가이드의 예제를 따를 수 있도록 MySQL 서버에 연결하고 샘플 데이터베이스를 생성합니다.

이 가이드에서는 가상의 자동차 컬렉션을 사용합니다. 제조사, 모델, 제작 연도 및 가치와 함께 현재 소유하고 있는 자동차에 대한 세부 정보를 저장합니다.

SQL 데이터베이스 시스템이 원격 서버에서 실행되는 경우 로컬 시스템에서 서버로 SSH합니다.

ssh sammy@your_server_ip

그런 다음 MySQL 서버 프롬프트를 열고 sammy를 MySQL 사용자 계정의 이름으로 바꿉니다.

mysql -u sammy -p

procedures라는 데이터베이스를 만듭니다.

CREATE DATABASE procedures;

데이터베이스가 성공적으로 생성되면 다음과 같은 출력을 받게 됩니다.

OutputQuery OK, 1 row affected (0.01 sec)

procedures 데이터베이스를 선택하려면 다음 USE 문을 실행합니다.

USE procedures;

다음 출력을 받게 됩니다.

OutputDatabase changed

데이터베이스를 선택한 후 그 안에 샘플 테이블을 생성할 수 있습니다. 테이블 cars에는 데이터베이스의 자동차에 대한 단순화된 데이터가 포함됩니다. 다음 열을 포함합니다.

make: 이 열에는 최대 100 문자로 varchar 데이터 유형을 사용하여 표현된 각 소유 자동차의 제조사가 있습니다.< /리>
model: 이 열에는 최대 100 문자로 varchar 데이터 유형을 사용하여 표현되는 자동차 모델 이름이 있습니다.
year: 이 열은 숫자 값을 보유하기 위해 int 데이터 유형으로 자동차의 제조 연도를 저장합니다.
값: 이 열은 최대 10자리와 2십진수 데이터 유형을 사용하여 자동차의 값을 저장합니다. 소수점 이하의 숫자.

다음 명령을 사용하여 샘플 테이블을 생성합니다.

CREATE TABLE cars (
    make varchar(100),
    model varchar(100),
    year int,
    value decimal(10, 2)
);

다음 출력이 인쇄되면 테이블이 생성된 것입니다.

OutputQuery OK, 0 rows affected (0.00 sec)

그런 다음 다음 INSERT INTO 작업을 실행하여 일부 샘플 데이터가 있는 cars 테이블을 로드합니다.

INSERT INTO cars
VALUES
('Porsche', '911 GT3', 2020, 169700),
('Porsche', 'Cayman GT4', 2018, 118000),
('Porsche', 'Panamera', 2022, 113200),
('Porsche', 'Macan', 2019, 27400),
('Porsche', '718 Boxster', 2017, 48880),
('Ferrari', '488 GTB', 2015, 254750),
('Ferrari', 'F8 Tributo', 2019, 375000),
('Ferrari', 'SF90 Stradale', 2020, 627000),
('Ferrari', '812 Superfast', 2017, 335300),
('Ferrari', 'GTC4Lusso', 2016, 268000);

INSERT INTO 작업은 5개의 Porsche 모델과 5개의 Ferrari 모델이 포함된 10개의 샘플 스포츠카를 테이블에 추가합니다. 다음 출력은 5개 행이 모두 추가되었음을 나타냅니다.

OutputQuery OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

이것으로 가이드의 나머지 부분을 따르고 SQL에서 저장 프로시저를 사용할 준비가 된 것입니다.

저장 프로시저 소개

MySQL 및 다른 많은 관계형 데이터베이스 시스템의 저장 프로시저는 하나 이상의 명령을 포함하는 명명된 개체이며 호출 시 순서대로 데이터베이스에 의해 실행됩니다. 가장 기본적인 예에서 저장 프로시저는 자주 사용되는 필터를 사용하여 데이터베이스에서 데이터를 검색하는 것과 같이 재사용 가능한 루틴에서 공통 명령문을 저장할 수 있습니다. 예를 들어, 지정된 마지막 개월 수 내에 주문한 온라인 상점 고객을 검색하는 저장 프로시저를 작성할 수 있습니다. 가장 복잡한 시나리오에서 저장 프로시저는 강력한 응용 프로그램에 대한 복잡한 비즈니스 논리를 설명하는 광범위한 프로그램을 나타낼 수 있습니다.

저장 프로시저의 명령 집합에는 데이터를 반환하거나 조작하는 INSERT 쿼리와 같은 일반 SQL 문이 포함될 수 있습니다. 또한 저장 프로시저는 다음을 사용할 수 있습니다.

저장 프로시저로 전달되거나 이를 통해 반환되는 매개변수.
프로시저 코드 내에서 직접 검색된 데이터를 처리하기 위해 선언된 변수.
IF 또는 CASE 명령과 같은 특정 조건에 따라 저장 프로시저 코드의 일부 실행을 허용하는 조건문.
WHILE, LOOP 및 REPEAT와 같은 루프는 각 행에 대해 코드의 일부를 여러 번 실행할 수 있도록 합니다. 검색된 데이터 세트.
프로시저에 액세스하는 데이터베이스 사용자에게 오류 메시지를 반환하는 것과 같은 오류 처리 지침.
데이터베이스의 다른 저장 프로시저 호출.

참고: MySQL이 지원하는 광범위한 구문을 사용하면 강력한 프로그램을 작성하고 저장 프로시저로 복잡한 문제를 해결할 수 있습니다. 이 안내서는 저장 프로시저 본문, 입력 및 출력 매개변수로 묶인 SQL 문이 있는 저장 프로시저의 기본 사용에 대해서만 다룹니다. 변수, 루프 및 사용자 지정 오류 처리를 사용하여 조건부 코드를 실행하는 것은 이 가이드의 범위를 벗어납니다. 공식 MySQL 문서에서 저장 프로시저에 대해 자세히 알아볼 것을 권장합니다.

프로시저가 해당 이름으로 호출되면 데이터베이스 엔진은 정의된 대로 명령별로 프로시저를 실행합니다.

데이터베이스 사용자는 지정된 프로시저를 실행할 수 있는 적절한 권한이 있어야 합니다. 이 권한 요구 사항은 안전한 실행이 보장되는 개별 프로시저에 대한 액세스 권한을 사용자에게 제공하면서 직접적인 데이터베이스 액세스를 허용하지 않는 보안 계층을 제공합니다.

저장 프로시저는 데이터베이스 서버에서 직접 실행되어 모든 계산을 로컬에서 수행하고 완료되었을 때만 호출 사용자에게 결과를 반환합니다.

절차 동작을 변경하려는 경우 데이터베이스에서 절차를 업데이트하면 이를 사용하는 응용 프로그램이 자동으로 새 버전을 선택합니다. 모든 사용자는 응용 프로그램을 조정할 필요 없이 즉시 새 절차 코드를 사용하기 시작합니다.

다음은 저장 프로시저를 만드는 데 사용되는 SQL 코드의 일반 구조입니다.

DELIMITER //
CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)
BEGIN
    instruction_1;
    instruction_2;
    . . .
    instruction_n;
END //
DELIMITER ;

이 코드 조각의 첫 번째 및 마지막 지침은 DELIMITER // 및 DELIMITER ;입니다. 일반적으로 MySQL은 세미콜론 기호(;)를 사용하여 명령문을 구분하고 시작 및 종료 시점을 나타냅니다. MySQL 콘솔에서 세미콜론으로 구분된 여러 명령문을 실행하면 별도의 명령으로 취급되어 차례로 독립적으로 실행됩니다. 그러나 저장 프로시저는 호출될 때 순차적으로 실행될 여러 명령을 묶을 수 있습니다. 이는 MySQL에 새 프로시저를 생성하도록 지시하려고 할 때 어려움을 야기합니다. 데이터베이스 엔진은 저장 프로시저 본문에서 세미콜론 기호를 발견하고 명령문 실행을 중지해야 한다고 생각합니다. 이 상황에서 의도된 명령문은 프로시저 자체 내의 단일 명령이 아니라 전체 프로시저 생성 코드이므로 MySQL은 사용자의 의도를 잘못 해석할 수 있습니다.

이 제한을 해결하려면 DELIMITER 명령을 사용하여 구분 기호를 ;에서 //로 임시로 변경합니다. CREATE PROCEDURE 호출. 그러면 저장 프로시저 본문 내부의 모든 세미콜론이 있는 그대로 서버에 전달됩니다. 전체 절차가 끝나면 구분 기호가 마지막 DELIMITER ;와 함께 다시 ;로 변경됩니다.

새 프로시저를 생성하는 코드의 핵심은 CREATE PROCEDURE 호출 뒤에 프로시저 이름이 오는 것입니다(예: procedure_name). 프로시저 이름 뒤에는 프로시저가 수락할 선택적 매개변수 목록이 옵니다. 마지막 부분은 BEGIN 및 END 문으로 묶인 프로시저 본문입니다. 내부에는 SELECT 쿼리 또는 더 복잡한 코드와 같은 단일 SQL 문을 포함할 수 있는 프로시저 코드가 있습니다.

END 명령은 일반적인 세미콜론 대신 임시 구분 기호인 //로 끝납니다.

다음 섹션에서는 단일 쿼리를 포함하는 매개 변수가 없는 기본 저장 프로시저를 만듭니다.

매개변수 없이 저장 프로시저 만들기

이 섹션에서는 단일 SQL SELECT 문을 캡슐화하는 첫 번째 저장 프로시저를 생성하여 제조업체 및 값에 따라 내림차순으로 정렬된 소유 자동차 목록을 반환합니다.

사용할 SELECT 문을 실행하여 시작합니다.

SELECT * FROM cars ORDER BY make, value DESC;

데이터베이스는 cars 테이블에서 자동차 목록을 반환합니다. 먼저 제조업체별로 정렬한 다음 단일 제조업체 내에서 값별로 내림차순으로 정렬합니다.

Output+---------+---------------+------+-----------+
| make    | model         | year | value     |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo    | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso     | 2016 | 268000.00 |
| Ferrari | 488 GTB       | 2015 | 254750.00 |
| Porsche | 911 GT3       | 2020 | 169700.00 |
| Porsche | Cayman GT4    | 2018 | 118000.00 |
| Porsche | Panamera      | 2022 | 113200.00 |
| Porsche | 718 Boxster   | 2017 |  48880.00 |
| Porsche | Macan         | 2019 |  27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)

가장 가치 있는 페라리가 목록의 맨 위에 있고 가장 가치가 낮은 포르쉐가 맨 아래에 나타납니다.

이 쿼리가 여러 애플리케이션에서 또는 여러 사용자에 의해 자주 사용된다고 가정하고 모든 사람이 정확히 동일한 방식으로 결과를 정렬하도록 하려고 한다고 가정합니다. 이렇게 하려면 재사용 가능한 명명된 프로시저 아래에 해당 문을 저장할 저장 프로시저를 만들려고 합니다.

이 저장 프로시저를 만들려면 다음 코드 조각을 실행합니다.

DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
    SELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;

이전 섹션에서 설명한 대로 첫 번째 및 마지막 명령(DELIMITER // 및 DELIMITER ;)은 프로시저 생성.

CREATE PROCEDURE SQL 명령 뒤에는 프로시저 이름 get_all_cars가 나옵니다. 프로시저가 수행하는 작업을 가장 잘 설명하기 위해 정의할 수 있습니다. 프로시저 이름 뒤에 매개 변수를 추가할 수 있는 한 쌍의 괄호 ()가 있습니다. 이 예에서 프로시저는 매개변수를 사용하지 않으므로 괄호가 비어 있습니다. 그런 다음 프로시저 코드 블록의 시작과 끝을 정의하는 BEGIN 및 END 명령 사이에 이전에 사용된 SELECT 문을 그대로 씁니다.

참고: MySQL 사용자 권한에 따라 CREATE PROCEDURE 명령을 실행할 때 ERROR 1044 (42000): sammy@ 사용자에 대한 액세스가 거부되었습니다. 데이터베이스 프로시저에 대한 localhost. 저장 프로시저를 만들고 실행할 수 있는 권한을 사용자에게 부여하려면 루트로 MySQL에 로그인하고 다음 명령을 실행하여 필요에 따라 MySQL 사용자 이름과 호스트를 바꿉니다.

GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

사용자 권한을 업데이트한 후 루트로 로그아웃했다가 사용자로 다시 로그인하고 CREATE PROCEDURE 문을 다시 실행합니다.

저장 루틴 및 MySQL 권한 설명서에서 데이터베이스 사용자에게 저장 프로시저 관련 권한을 적용하는 방법에 대해 자세히 알아볼 수 있습니다.

데이터베이스는 성공 메시지로 응답합니다.

OutputQuery OK, 0 rows affected (0.02 sec)

get_all_cars 프로시저는 이제 데이터베이스에 저장되며 호출 시 저장된 명령문을 그대로 실행합니다.

저장된 저장 프로시저를 실행하려면 CALL SQL 명령 다음에 프로시저 이름을 사용할 수 있습니다. 새로 만든 프로시저를 다음과 같이 실행해 보십시오.

CALL get_all_cars;

프로시저 이름 get_all_cars만 있으면 프로시저를 사용할 수 있습니다. 더 이상 이전에 사용한 SELECT 문의 일부를 수동으로 입력할 필요가 없습니다. 데이터베이스는 이전에 실행한 SELECT 문의 출력과 같은 결과를 표시합니다.

Output+---------+---------------+------+-----------+
| make    | model         | year | value     |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo    | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso     | 2016 | 268000.00 |
| Ferrari | 488 GTB       | 2015 | 254750.00 |
| Porsche | 911 GT3       | 2020 | 169700.00 |
| Porsche | Cayman GT4    | 2018 | 118000.00 |
| Porsche | Panamera      | 2022 | 113200.00 |
| Porsche | 718 Boxster   | 2017 |  48880.00 |
| Porsche | Macan         | 2019 |  27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

이제 특정 방식으로 주문한 cars 테이블에서 모든 자동차를 반환하는 매개변수 없이 저장 프로시저를 성공적으로 만들었습니다. 여러 응용 프로그램에서 절차를 사용할 수 있습니다.

다음 섹션에서는 사용자 입력에 따라 프로시저 동작을 변경하기 위해 매개변수를 허용하는 프로시저를 생성합니다.

입력 매개변수를 사용하여 저장 프로시저 만들기

이 섹션에서는 프로시저를 실행하는 사용자가 프로시저에 데이터를 전달할 수 있도록 저장 프로시저 정의에 입력 매개변수를 포함합니다. 예를 들어 사용자는 쿼리 필터를 제공할 수 있습니다.

이전에 생성된 저장 프로시저 get_all_cars는 항상 cars 테이블에서 모든 자동차를 검색했습니다. 지정된 제조 연도의 자동차를 찾는 또 다른 프로시저를 만들어 보겠습니다. 이를 허용하려면 프로시저 정의에서 명명된 매개변수를 정의합니다.

다음 코드를 실행합니다.

DELIMITER //
CREATE PROCEDURE get_cars_by_year(
    IN year_filter int
)
BEGIN
    SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

이전 섹션에서 프로시저 생성 코드에 몇 가지 변경 사항이 있습니다.

먼저 이름은 get_cars_by_year로, 생산 연도를 기준으로 자동차를 검색하는 절차를 설명합니다.

이전에 비어 있던 괄호에는 이제 IN year_filter int라는 단일 매개변수 정의가 포함됩니다. IN 키워드는 매개변수가 호출 사용자에 의해 프로시저 로 전달될 것임을 데이터베이스에 알립니다. year_filter는 매개변수의 임의 이름입니다. 프로시저 코드에서 매개변수를 참조하는 데 사용합니다. 마지막으로 int는 데이터 유형입니다. 이 경우 생산 연도는 수치로 표시됩니다.

프로시저 이름 뒤에 정의된 year_filter 매개변수는 WHERE year = year_filter 절의 SELECT 문에 나타나 필터링합니다. 생산 연도에 대한 cars 테이블.

데이터베이스는 성공 메시지로 다시 한 번 응답합니다.

OutputQuery OK, 0 rows affected (0.02 sec)

이전에 수행한 것처럼 매개변수를 전달하지 않고 프로시저를 실행해 보십시오.

CALL get_cars_by_year;

MySQL 데이터베이스는 오류 메시지를 반환합니다.

Error messageERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0

이번에는 저장 프로시저가 매개변수가 제공될 것으로 예상하지만 아무 것도 제공되지 않았습니다. 매개 변수가 있는 저장 프로시저를 호출하려면 프로시저에서 예상한 것과 동일한 순서로 괄호 안에 매개 변수 값을 제공할 수 있습니다. 2017에 제조된 자동차를 검색하려면 다음을 실행합니다.

CALL get_cars_by_year(2017);

이제 호출된 프로시저가 올바르게 실행되고 해당 연도의 자동차 목록을 반환합니다.

Output+---------+---------------+------+-----------+
| make    | model         | year | value     |
+---------+---------------+------+-----------+
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Porsche | 718 Boxster   | 2017 |  48880.00 |
+---------+---------------+------+-----------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

이 예제에서는 저장 프로시저에 입력 매개 변수를 전달하고 필터링 옵션을 제공하기 위해 프로시저 내부의 쿼리에서 사용하는 방법을 배웠습니다.

다음 섹션에서는 출력 매개변수를 사용하여 단일 실행에서 여러 다른 값을 반환하는 프로시저를 만듭니다.

입력 및 출력 매개변수를 사용하여 저장 프로시저 만들기

앞의 두 예에서 생성한 저장 프로시저는 결과 집합을 얻기 위해 SELECT 문을 호출했습니다. 그러나 경우에 따라 개별 쿼리에 대한 단일 결과 집합 대신 여러 다른 값을 함께 반환하는 저장 프로시저가 필요할 수 있습니다.

컬렉션의 자동차 수량 및 해당 시장 가치(최소, 최대 및 평균)를 포함하여 지정된 연도의 자동차에 대한 요약 정보를 제공하는 프로시저를 생성한다고 가정합니다.

이렇게 하려면 새 저장 프로시저를 만들 때 OUT 매개 변수를 사용할 수 있습니다. IN 매개변수와 유사하게 OUT 매개변수에는 연관된 이름과 데이터 유형이 있습니다. 그러나 저장 프로시저에 데이터를 전달하는 대신 저장 프로시저에서 데이터를 채워 호출 사용자에게 값을 반환할 수 있습니다.

출력 매개변수를 사용하여 지정된 생산 연도의 자동차에 대한 요약 데이터를 반환하는 get_car_stats_by_year 프로시저를 만듭니다.

DELIMITER //
CREATE PROCEDURE get_car_stats_by_year(
    IN year_filter int,
    OUT cars_number int,
    OUT min_value decimal(10, 2),
    OUT avg_value decimal(10, 2),
    OUT max_value decimal(10, 2)
)
BEGIN
    SELECT COUNT(*), MIN(value), AVG(value), MAX(value)
    INTO cars_number, min_value, avg_value, max_value
    FROM cars
    WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

이번에는 생산 연도별로 자동차를 필터링하는 데 사용되는 IN 매개변수 year_filter와 함께 4개의 OUT 매개변수가 괄호 블록 내에 정의됩니다. cars_number 매개변수는 int 데이터 유형으로 표시되며 컬렉션의 자동차 수를 반환하는 데 사용됩니다. min_value, avg_value 및 max_value 매개변수 시장 가치를 나타내며 decimal(10, 2) 유형으로 정의됩니다(cars 테이블의 value 열과 유사). 이는 컬렉션에서 가장 저렴하고 가장 비싼 자동차에 대한 정보와 일치하는 모든 자동차의 평균 가격을 반환하는 데 사용됩니다.

SELECT 문은 SQL 수학 함수를 사용하여 car 테이블에서 4개의 값을 쿼리합니다. COUNT는 전체 자동차 수를 가져오고 MIN은 , AVG 및 MAX는 value 열에서 최소, 평균 및 최대 값을 가져옵니다.

메모:

해당 쿼리의 결과가 저장 프로시저의 출력 매개 변수에 저장되어야 함을 데이터베이스에 알리기 위해 새 키워드인 INTO가 도입되었습니다. INTO 키워드 뒤에 검색된 데이터에 해당하는 4개의 프로시저 매개변수 이름이 나열됩니다. 이를 통해 MySQL은 COUNT(*) 값을 cars_number 매개변수, MIN(value) 결과는 min_value 매개변수 등으로 입력됩니다.

데이터베이스는 성공적인 프로시저 생성을 확인합니다.

OutputQuery OK, 0 rows affected (0.02 sec)

이제 다음을 실행하여 새 프로시저를 실행하십시오.

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

4개의 새 매개변수는 @ 기호로 시작합니다. 데이터를 임시로 저장하는 데 사용할 수 있는 MySQL 콘솔의 로컬 변수 이름입니다. 방금 생성한 저장 프로시저에 이를 전달하면 프로시저는 해당 변수에 값을 삽입합니다.

데이터베이스는 다음과 같이 응답합니다.

OutputQuery OK, 1 row affected (0.00 sec)

이는 결과가 화면에 즉시 표시되는 이전 동작과 다릅니다. 저장 프로시저의 결과가 출력 매개 변수에 저장되고 쿼리 결과로 반환되지 않기 때문입니다. 결과에 액세스하려면 다음과 같이 MySQL 셸에서 직접 SELECT할 수 있습니다.

SELECT @number, @min, @avg, @max;

이 쿼리를 사용하면 프로시저를 다시 호출하지 않고 지역 변수에서 값을 선택합니다. 저장 프로시저는 해당 변수에 결과를 저장했으며 데이터는 셸에서 연결을 끊을 때까지 계속 사용할 수 있습니다.

메모:

출력에는 쿼리된 변수의 값이 표시됩니다.

Output+---------+----------+-----------+-----------+
| @number | @min     | @avg      | @max      |
+---------+----------+-----------+-----------+
|       2 | 48880.00 | 192090.00 | 335300.00 |
+---------+----------+-----------+-----------+
1 row in set (0.00 sec)

값은 2017에 생산된 자동차의 수와 해당 생산 연도 자동차의 최소, 평균 및 최대 시장 가치에 해당합니다.

이 예에서는 출력 매개변수를 사용하여 나중에 사용할 수 있도록 저장 프로시저 내에서 여러 다른 값을 반환하는 방법을 배웠습니다. 다음 섹션에서는 생성된 프로시저를 제거하는 방법을 배웁니다.

저장 프로시저 제거

이 섹션에서는 데이터베이스에 있는 저장 프로시저를 제거합니다.

때로는 생성한 절차가 더 이상 필요하지 않을 수도 있습니다. 다른 상황에서는 절차가 작동하는 방식을 변경하고자 할 수 있습니다. MySQL은 생성 후 프로시저 정의 변경을 허용하지 않으므로 이를 수행하는 유일한 방법은 먼저 프로시저를 제거하고 원하는 변경 사항으로 다시 생성하는 것입니다.

마지막 프로시저 get_car_stats_by_year를 제거하겠습니다. 이렇게 하려면 DROP PROCEDURE 문을 사용할 수 있습니다.

DROP PROCEDURE get_car_stats_by_year;

데이터베이스는 성공 메시지와 함께 성공적인 프로시저 삭제를 확인합니다.

OutputQuery OK, 0 rows affected (0.02 sec)

호출을 시도하여 프로시저가 삭제되었는지 확인할 수 있습니다. 실행하다:

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

이번에는 프로시저가 데이터베이스에 없다는 오류 메시지가 표시됩니다.

Error messageERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist

이 섹션에서는 데이터베이스에서 기존 저장 프로시저를 삭제하는 방법을 배웠습니다.

결론

이 가이드를 따라 저장 프로시저가 무엇인지, MySQL에서 저장 프로시저를 사용하여 재사용 가능한 명령문을 명명된 프로시저에 저장하고 나중에 실행하는 방법을 배웠습니다. 매개변수가 없는 저장 프로시저와 입력 및 출력 매개변수를 사용하여 유연성을 높이는 프로시저를 만들었습니다.

저장 프로시저를 사용하여 재사용 가능한 루틴을 생성하고 여러 애플리케이션에서 데이터에 액세스하기 위한 방법을 통합할 뿐만 아니라 개별 SQL 쿼리에서 제공하는 가능성을 초과하는 복잡한 동작을 구현할 수 있습니다. 이 자습서에서는 저장 프로시저 사용의 기본 사항만 다루었습니다. 이에 대한 자세한 내용은 저장 프로시저에 대한 MySQL 설명서를 참조하십시오.

SQL 언어와 작업에 대한 다양한 개념에 대해 자세히 알아보려면 SQL 사용 방법 시리즈의 다른 가이드를 확인하는 것이 좋습니다.