MYSQL 저장 프로시저 및 저장 함수를 사용하는 방법

1. 저장 프로시저와 저장 함수란 무엇인가요?

• 저장 프로시저(Stored Procedure)는 데이터베이스에 저장되는 SQL 문 집합입니다. 저장 프로시저는 비즈니스 논리를 캡슐화함으로써 데이터베이스 실행 효율성과 데이터 액세스 보안을 향상시킬 수 있습니다.

• 저장 함수는 실행된 SQL 문 집합을 데이터베이스에 저장하는 것을 말합니다. 저장 프로시저와 차이점은 저장 함수에 반환 값이 있다는 점입니다.

2. 저장 프로시저 만들기

CREATE PROCEDURE procedure_name([IN/OUT] parameter_name data_type)
BEGIN
    SQL Statement;
END;

이미 employee라는 직원 테이블이 있고 이제 해당 테이블을 기반으로 직원의 이름과 급여를 쿼리할 수 있는 저장 프로시저를 만들어야 한다고 가정합니다. 직원의 직업 번호 ：employee的员工表，现在需要创建一个存储过程，可以根据员工的工号查询员工的姓名和工资：

DELIMITER //
CREATE PROCEDURE get_employee_info_by_id(IN emp_id INT)
BEGIN
    SELECT name, salary FROM employee WHERE id = emp_id;
END //
DELIMITER ;

3. 创建存储函数

CREATE FUNCTION function_name([IN/OUT] parameter_name data_type) RETURNS data_type
BEGIN
    DECLARE variable_name data_type;
    SQL Statement;
    RETURN variable_name;
END;

假设我们已经有一张名为product的商品表，现在需要创建一个存储函数，可以根据商品的编号查询商品的单价：

DELIMITER //
CREATE FUNCTION get_product_price_by_id(IN product_id INT) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE price DECIMAL(10,2);
    SELECT unit_price INTO price FROM product WHERE id = product_id;
    RETURN price;
END //
DELIMITER ;

4. 存储过程和存储函数的使用

• 调用存储过程：

CALL procedure_name([parameter_name]);

• 调用存储函数：

SELECT function_name([parameter_name]);

使用上面创建的get_employee_info_by_id存储过程可以这样调用：

CALL get_employee_info_by_id(1);

使用上面创建的get_product_price_by_id存储函数可以这样调用：

SELECT get_product_price_by_id(1001);

以下是一些常见的存储过程和存储函数的示例：

5. 带有if语句的存储过程

假设我们已经有一张名为employee的员工表，现在需要创建一个存储过程，查询员工的姓名和工资，如果工资大于5000，则在结果中添加一个备注：“高收入”。

DELIMITER //
CREATE PROCEDURE get_employee_info_with_note()
BEGIN
    SELECT name, salary, IF(salary > 5000, '高收入', '') AS note FROM employee;
END //
DELIMITER ;

6. 带有循环语句的存储过程

假设我们已经有一张名为product的商品表，现在需要创建一个存储过程，把商品的单价全部乘以1.1。

DELIMITER //
CREATE PROCEDURE update_all_product_price()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE pid INT;
    DECLARE price DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT id, unit_price FROM product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO pid, price;
        IF done THEN
            LEAVE read_loop;
        END IF;
        UPDATE product SET unit_price = price * 1.1 WHERE id = pid;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

7. 带有事务的存储过程

假设我们已经有一张名为order的订单表和一张名为order_item的订单详情表，现在需要创建一个存储过程，向这两张表中插入一条记录。

DELIMITER //
CREATE PROCEDURE insert_order(IN order_id INT, IN item_name VARCHAR(50), IN item_price DECIMAL(10,2), IN item_quantity INT)
BEGIN
    START TRANSACTION;
    INSERT INTO `order`(id) VALUES(order_id);
    SET @last_order_id = LAST_INSERT_ID();
    INSERT INTO order_item(order_id, item_name, item_price, item_quantity) VALUES(@last_order_id, item_name, item_price, item_quantity);
    COMMIT;
END //
DELIMITER ;

8. 带有游标的存储函数

假设我们已经有一张名为product

DELIMITER //
CREATE FUNCTION get_max_product_price() RETURNS DECIMAL(10,2)
BEGIN
    DECLARE max_price DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT unit_price FROM product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET max_price = 0;
    OPEN cur;
    FETCH cur INTO max_price;
    read_loop: LOOP
        FETCH cur INTO max_price;
        IF max_price IS NULL THEN
            LEAVE read_loop;
        END IF;
        IF max_price > @max_price THEN 
            SET @max_price = max_price;
        END IF;
    END LOOP;
    CLOSE cur;
    RETURN max_price;
END //
DELIMITER ;

3. 저장 함수 만들기

rrreee

이미 product라는 제품 테이블이 있고 이제 단가를 쿼리할 수 있는 저장 함수를 만들어야 한다고 가정합니다. 제품 번호 기준 제품:

rrreee
• 4. 저장 프로시저 및 저장 함수 사용

• 저장 프로시저 호출:

rrreee

• 저장 함수 호출:

🎜rrreee🎜 get_employee_info_by_ 사용 이드 위에서 생성된 저장 프로시저는 다음과 같습니다. 호출: 🎜rrreee🎜 위에서 생성된 get_product_price_by_id 저장 함수를 사용하면 다음과 같이 호출할 수 있습니다. 🎜rrreee🎜다음은 일반적인 저장 프로시저와 저장 프로시저의 몇 가지 예입니다. 함수: 🎜🎜5. if 문이 포함된 저장 프로시저 🎜🎜이미 employee라는 직원 테이블이 있다고 가정해 보겠습니다. 이제 직원의 이름과 급여를 쿼리하는 저장 프로시저를 만들어야 합니다. 5000보다 크면 결과에 "고소득"이라는 메모를 추가하세요. 🎜rrreee🎜6. 루프 문이 포함된 저장 프로시저🎜🎜 product라는 제품 테이블이 이미 있고 이제 제품의 단가에 1.1을 곱하는 저장 프로시저를 만들어야 한다고 가정해 보겠습니다. 🎜rrreee🎜7. 트랜잭션이 포함된 저장 프로시저🎜🎜 이미 order라는 주문 테이블과 order_item이라는 주문 세부정보 테이블이 있다고 가정해 보겠습니다. 이 두 테이블에 레코드를 삽입합니다. 🎜rrreee🎜8. 커서를 사용한 저장 기능🎜🎜 이미 product라는 제품 테이블이 있고 이제 제품 테이블에서 최대 단가를 쿼리하는 저장 함수를 만들어야 한다고 가정해 보겠습니다. 🎜rrreee🎜9. 저장 프로시저 및 저장 함수의 장점🎜🎜🎜🎜코드를 재사용하여 SQL 문을 반복적으로 작성하지 않아도 됩니다.🎜🎜🎜🎜흐름 제어 문을 저장 프로시저 및 저장 함수에서 사용하여 복잡한 논리를 처리할 수 있습니다.🎜🎜 🎜🎜데이터베이스 작업은 저장 프로시저와 저장 함수를 통해 캡슐화되어 효율성과 보안을 향상시킬 수 있습니다. 🎜🎜🎜

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