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

일반적으로 관계형 데이터베이스로 작업할 때 개별 SQL(구조적 쿼리 언어) 쿼리를 실행하여 SELECT, INSERT, UPDATE 또는 DELETE와 같은 데이터를 검색하거나 조작합니다. 애플리케이션 코드 내에서 직접. 그 진술은 효과가 있습니다 기본 데이터베이스 테이블을 직접 조작합니다. 같은 경우 명령문 또는 명령문 그룹이 여러 애플리케이션 내에서 사용됩니다. 동일한 데이터베이스에 액세스하면 개별적으로 중복되는 경우가 많습니다.

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

저장 프로시저를 사용하면 재사용 가능한 공통 루틴을 생성할 수 있습니다. 여러 애플리케이션에서 사용할 작업, 데이터 유효성 검사 제공, 또는 제한하여 추가 데이터 액세스 보안 계층을 제공합니다. 데이터베이스 사용자가 기본 테이블에 직접 액세스하여 발행하는 것을 방지 임의의 쿼리입니다.

이 튜토리얼에서는 저장 프로시저가 무엇인지, 어떻게 저장 프로시저를 사용하는지 배우게 됩니다. 데이터를 반환하고 입력과 입력을 모두 사용하는 기본 저장 프로시저를 만듭니다. 출력 매개변수.

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

이 섹션에서는 MySQL 서버에 연결하고 이 가이드의 예를 따라갈 수 있는 샘플 데이터베이스입니다.

이 가이드에서는 가상의 자동차 컬렉션을 사용합니다. 당신은 저장합니다 현재 소유하고 있는 자동차에 대한 세부정보(제조업체, 모델, 제작 연도, 및 값.

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

ssh sammy@your_server_ip

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

mysql -u sammy-p

procedures라는 이름의 데이터베이스를 생성합니다:

CREATE DATABASEprocedures;

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

OutputQuery OK, 1 row affected (0.01 sec)

선택하려면 프로시저 데이터베이스에서 다음 USE 문을 실행합니다.

USEprocedures;

다음 출력을 받게 됩니다.

OutputDatabase changed

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

• make: 이 열에는 최대 100자의 varchar 데이터 유형을 사용하여 표현된 각 소유 자동차의 제조업체가 포함됩니다.
• model: 이 열은 열에는 최대 100자의 varchar 데이터 유형을 사용하여 표현된 자동차 모델 이름이 포함됩니다.
• 연도: 이 열은 자동차 빌드를 저장합니다. int 데이터 형식의 연도를 사용하여 숫자 값을 저장합니다.
• value: 이 열은 최대 10자리, 소수점 이하 2자리의 10진수 데이터 형식을 사용하여 자동차의 값을 저장합니다.

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

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 carsVALUES('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 작업을 수행하면 테이블에 10대의 샘플 스포츠카가 추가됩니다. 포르쉐와 페라리 5개 모델. 다음 출력은 모든 5개의 행이 추가되었습니다.

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

이제 가이드의 나머지 부분을 따르고 SQL에서 저장 프로시저를 사용할 준비가 되었습니다.

저장 프로시저 소개

MySQL 및 기타 여러 관계형 데이터베이스의 저장 프로시저 시스템은 하나 이상의 명령이 포함된 명명된 개체입니다. 그런 다음 호출될 때 데이터베이스에 의해 순서대로 실행됩니다. 에서 가장 기본적인 예를 들면, 저장 프로시저는 공통 명령문을 다음과 같이 저장할 수 있습니다. 데이터베이스에서 데이터를 검색하는 것과 같은 재사용 가능한 루틴 자주 사용되는 필터. 예를 들어 저장 프로시저를 생성하여 다음을 수행할 수 있습니다. 지난 시간 내에 주문한 온라인 상점 고객을 검색합니다. 개월 수. 가장 복잡한 시나리오에서 저장 프로시저는 다음을 수행할 수 있습니다. 복잡한 비즈니스 로직을 설명하는 광범위한 프로그램을 나타냅니다. 강력한 애플리케이션.

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

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

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

데이터베이스 사용자에게는 다음 작업을 수행할 수 있는 적절한 권한이 있어야 합니다. 실행하다 주어진 절차. 이 권한 요구 사항은 다음 계층을 제공합니다. 보안, 사용자에게 액세스 권한을 부여하는 동안 직접 데이터베이스 액세스를 허용하지 않음 안전한 실행이 보장되는 개별 프로시저에 적용됩니다.

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

절차 동작을 변경하려면 데이터베이스의 프로시저와 이를 사용하는 애플리케이션은 자동으로 새 버전을 선택합니다. 모든 사용자가 즉시 시작됩니다. 조정할 필요 없이 새로운 절차 코드를 사용합니다.

저장 프로시저를 생성하는 데 사용되는 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 호출 뒤에 프로시저 이름이 오는 것입니다: 프로시저_이름 예에서. 프로시저 이름 뒤에는 선택적 목록이 옵니다. 프로시저가 허용하는 매개변수입니다. 마지막 부분은 절차입니다. BEGIN 및 END 문으로 묶인 본문입니다. 내부에는 SELECT 쿼리 또는 더 복잡한 코드와 같은 단일 SQL 문을 포함할 수 있는 프로시저 코드가 있습니다.

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

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

없이 저장 프로시저 생성 매개변수

이 섹션에서는 단일 SQL SELECT 문을 캡슐화하여 제조업체와 가격을 기준으로 내림차순으로 정렬된 소유 자동차 목록을 반환하는 첫 번째 저장 프로시저를 만듭니다.

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

SELECT * FROM cars ORDER BY make, value DESC;

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

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)

가장 가치 있는 Ferrari가 목록 맨 위에 있고 가장 가치가 낮은 Porsche가 나타납니다.

이 쿼리가 여러 애플리케이션에서 자주 사용되거나 여러 사용자가 사용하고 모든 사람이 결과를 정렬하는 방법도 똑같습니다. 그렇게 하려면 해당 명령문을 재사용 가능한 이름으로 저장하는 저장 프로시저 프로시저.

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

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

이전 섹션에서 설명한 대로 첫 번째 및 마지막 명령(DELIMITER / / 및 DELIMITER ;) MySQL에게 절차 기간 동안 세미콜론 문자를 명령문 구분 기호로 처리하는 것을 중지하도록 지시합니다.

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

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

OutputQuery OK, 0 rows affected (0.02 sec)

get_all_cars프로시저는 이제 데이터베이스에 저장되며, 호출되면 저장된 문을 다음과 같이 실행합니다.

저장된 저장 프로시저를 실행하려면 CALL SQL 명령 뒤에 프로시저 이름을 입력하면 됩니다. 다음과 같이 새로 생성된 프로시저를 실행해 보세요.

CALLget_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 테이블에서 모든 자동차를 반환하는 매개 변수 없이 저장 프로시저를 성공적으로 만들었습니다. . 여러 애플리케이션에서 이 프로시저를 사용할 수 있습니다.

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

위 내용은 MySQL에서 저장 프로시저를 사용하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!