mysql 저장 프로시저 커서-MySQL 튜토리얼-php.cn

MySQL 저장 프로시저 및 커서에 대한 자세한 설명

MySQL 데이터베이스에서 저장 프로시저는 미리 컴파일된 SQL 문 집합으로, 데이터베이스의 서브루틴으로 간주될 수 있습니다. 저장 프로시저는 일반적으로 복잡한 비즈니스 논리를 처리하고 애플리케이션과 데이터베이스 간의 상호 작용을 단순화하며 데이터 처리 효율성과 보안을 향상시키는 데 사용됩니다. 커서는 저장 프로시저에서 쿼리 결과 집합을 처리하는 데 사용되는 메커니즘입니다.

이 글에서는 MySQL 저장 프로시저와 커서의 사용법과 특징을 자세히 소개하겠습니다.

저장 프로시저

1.1 저장 프로시저의 구문 구조

다음은 MySQL 저장 프로시저의 기본 구문 구조입니다.

CREATE PROCEDURE procedure_name [ (parameter_list) ] BEGIN
    -- 存储过程的逻辑实现
END;

로그인 후 복사

그 중

procedure_name: 저장 프로시저의 이름
parameter_list: 저장 프로시저의 매개변수 목록은 0개 이상의 매개변수를 포함할 수 있으며, 각 매개변수는 매개변수 이름과 매개변수 유형으로 구성됩니다.
BEGIN과 END 사이는 저장 프로시저의 논리적 구현 부분입니다.

예를 들어, 학생 테이블(student)의 모든 레코드를 쿼리하고 반환하는 간단한 저장 프로시저를 정의하고 다음을 반환합니다.

CREATE PROCEDURE get_all_students()
BEGIN
    SELECT * FROM student;
END;

로그인 후 복사

1.2 저장 프로시저의 매개변수 전달

저장 프로시저는 매개변수를 미리 정의할 수 있으며 호출 시 이러한 매개변수를 호출할 수 있습니다. 프로시저를 저장할 때 통과되었습니다. MySQL 저장 프로시저는 IN, OUT 및 INOUT의 세 가지 매개변수 전달 방법을 지원합니다.

IN: 입력 매개변수로 저장 프로시저 호출 시 매개변수 값이 전달됩니다.
OUT: 출력 매개변수로 결과가 계산되어 저장 프로시저 내에서 반환됩니다.
INOUT: 입력 매개변수이자 출력 매개변수입니다. 저장 프로시저 호출 시 매개변수 값을 전달하고 저장 프로시저의 계산된 결과를 받습니다.

다음은 매개변수 전달을 사용하는 저장 프로시저의 예입니다.

CREATE PROCEDURE add_two_numbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
    SET result = num1 + num2;
END;

로그인 후 복사

위의 예에서 add_two_numbers 저장 프로시저는 두 개의 입력 매개변수 num1과 num2를 수신하고 그 추가 결과를 출력 매개변수 결과에 할당합니다.

다음 문을 사용하여 이 저장 프로시저를 호출할 수 있습니다.

CALL add_two_numbers(2, 3, @result);
SELECT @result; -- 输出 5

로그인 후 복사

1.3 저장 프로시저의 흐름 제어

MySQL 저장 프로시저는 IF, CASE, WHILE, LOOP 및 LEAVE 등과 같은 일반적인 흐름 제어 문을 지원합니다. 복잡한 논리 제어 및 비즈니스 처리를 구현하려면 이러한 명령문을 사용하십시오.

다음은 IF 및 WHILE 문을 사용한 저장 프로시저의 예입니다.

CREATE PROCEDURE calc_factorial(IN num INT, OUT result BIGINT)
BEGIN
    SET result = 1;
    WHILE num > 1 DO
        SET result = result * num;
        SET num = num - 1;
    END WHILE;
END;

로그인 후 복사

위 예에서 입력 매개 변수 num이 1보다 큰 경우 WHILE 루프를 사용하여 num의 계승 값과 결과를 계산합니다. 출력 매개변수 결과에 저장됩니다.

다음 문을 사용하여 이 저장 프로시저를 호출할 수 있습니다.

CALL calc_factorial(6, @result);
SELECT @result; -- 输出 720

로그인 후 복사

Cursor

2.1 커서의 기본 개념

MySQL 저장 프로시저에서 커서는 저장 프로시저에 설정된 쿼리 결과를 탐색하는 데 사용되는 메커니즘입니다. . 커서는 쿼리 결과 집합의 현재 행을 추적하고 해당 행의 데이터를 읽는 데 필요한 만큼 행별로 이동합니다.

커서를 사용하여 커서를 선언하고, 열고, 닫고, 작동하려면 다음 4가지 명령을 사용해야 합니다.

DECLARE: 커서 선언, 커서 이름, 쿼리 문 및 커서 유형 정의 등.
OPEN: 커서를 열고 커서가 가리키는 버퍼에 설정된 쿼리 결과를 저장합니다.
FETCH: 커서가 가리키는 현재 행을 가져와서 현재 행의 값을 해당 변수에 저장합니다.
CLOSE: 커서를 닫고 커서가 차지한 메모리를 해제합니다.

다음은 커서를 이용한 저장 프로시저의 예입니다.

CREATE PROCEDURE get_all_students()
BEGIN
    DECLARE done INT DEFAULT FALSE; -- 定义游标是否结束的标志
    DECLARE s_id INT; -- 存储查询结果中的学生编号
    DECLARE s_name VARCHAR(255); -- 存储查询结果中的学生姓名
    DECLARE cursor_students CURSOR FOR SELECT id, name FROM student; -- 声明游标，查询表 student 中的所有数据
    -- 打开游标
    OPEN cursor_students;
    -- 遍历游标指向的结果集
    read_loop: LOOP
        -- 获取游标指向的当前行
        FETCH cursor_students INTO s_id, s_name;
        -- 如果游标到达结果集的末尾，则跳出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 输出当前行的值
        SELECT s_id, s_name;
    END LOOP;
    -- 关闭游标
    CLOSE cursor_students;
END;

로그인 후 복사

위의 예에서는 Student 테이블의 모든 데이터를 쿼리하기 위해 Cursor_students라는 커서를 선언했습니다. 커서를 연 후 read_loop 루프 본문을 사용하여 쿼리 결과 집합을 순회하고 FETCH 명령을 사용하여 현재 행의 값을 가져와 변수 s_id 및 s_name에 저장하고 콘솔에 출력합니다. 커서가 결과 집합의 끝에 도달하면 루프를 중단합니다. 커서 사용이 끝나면 CLOSE 명령을 사용하여 커서를 닫아야 합니다.

2.2 커서의 특성 및 응용 시나리오

커서는 개발자에게 결과 집합을 쿼리하는 편리한 방법을 제공하지만 추가 메모리와 리소스가 필요하기 때문에 커서를 사용할 때는 다음 문제에 특별한 주의가 필요합니다.

커서 특히 대규모 데이터 세트로 작업할 때 성능에 영향을 미칠 수 있습니다. 대규모 또는 동시성이 높은 환경에서는 커서를 사용하지 않는 것이 좋으며 하위 쿼리, JOIN 작업과 같은 다른 방법을 우선적으로 사용해야 합니다.
커서는 저장 프로시저에서만 사용할 수 있으며 SQL 문에서는 직접 사용할 수 없습니다.
커서는 주의해서 사용해야 합니다. 커서가 올바르게 닫히지 않으면 MySQL 데이터베이스가 많은 메모리 리소스를 차지하거나 충돌이 발생할 수 있기 때문입니다.

일반적으로 커서는 다음 시나리오에 적합합니다.

저장 프로시저에서 복잡한 쿼리 논리를 구현해야 하는 상황.
대규모 데이터 세트를 일괄 처리해야 하는 상황.
쿼리 결과 집합을 행 단위로 처리해야 하는 상황.

요약

이 글에서는 주로 MySQL 데이터베이스의 저장 프로시저와 커서의 사용법과 특징을 소개합니다. 저장 프로시저는 애플리케이션과 데이터베이스 간 상호 작용의 효율성과 보안을 향상시킬 수 있으며 커서는 쿼리 결과 집합을 쉽게 탐색할 수 있습니다. 그러나 메모리 누수 및 성능 문제를 방지하려면 커서를 사용할 때 주의해야 합니다.

참조: