mysql 使用預存程序

MySQL 是目前最普及的一種關聯式資料庫管理系統，其使用預存程序是為了更好地維護和管理 SQL 語句和程序的執行。預存程序是一種預先編譯的 SQL 程式碼區塊，可以將常用的 SQL 語句封裝起來，在多次執行時提高效率，降低執行時間的資源佔用。

MySQL 中預存程序的使用

1、建立預存程序

從根本上來說，建立一個預存程序就是寫一個SQL 語句區塊，以及在系統資料庫中保存這個代碼塊。以下是一個具體的範例：

CREATE PROCEDURE `procedure_name` (`parameter_list`)
BEGIN
    -- SQL 语句块
END

其中，procedure_name 是預存程序的名稱，paramter_list 是預存程序的參數列表，可以包含入參、出參，也可以不包含參數。

例如，我們建立一個取得使用者資訊的預存程序：

CREATE PROCEDURE `get_user_info`(user_id INT)
BEGIN
    SELECT * FROM `user` WHERE `user_id` = user_id;
END

在預存程序的SQL 語句區塊中，我們可以使用MySQL 支援的所有SQL 語句，如SELECT、INSERT、UPDATE 、DELETE 等，也可以使用控制流語句，例如IF、WHILE、LOOP 等，從而實現更複雜的資料邏輯處理。

2、呼叫預存程序

建立預存程序之後，我們可以透過CALL 語句來呼叫預存程序，並向其傳遞參數：

CALL procedure_name(param1, param2, ...)

例如，我們可以按如下方式呼叫上面定義的get_user_info 預存程序：

CALL `get_user_info`(1);

3、刪除預存程序

如果隨著時間的推移，一個預存程序已經不再需要，可以使用DROP PROCEDURE 語句刪除它：

DROP PROCEDURE `procedure_name`;

例如：

DROP PROCEDURE `get_user_info`;

4、預存程序實例

我們來看一個完整的預存程序實例。假設我們有一個 score 表，儲存了學生的考試成績，而我們需要計算出每個學生的總分、平均分，並按照總分從高到低排列。這時可以使用預存程序來實現：

CREATE PROCEDURE `calc_stu_score`()
BEGIN
     DECLARE `total_score` INT;
     DECLARE `avg_score` FLOAT;
     DECLARE `stu_id` INT DEFAULT 0;
     DECLARE `stu_name` VARCHAR(255);
     DECLARE `total` INT DEFAULT 0;
    DECLARE `cursor_stu_id` CURSOR FOR SELECT `stu_id` FROM `score` GROUP BY `stu_id`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

    DROP TEMPORARY TABLE IF EXISTS `tmp_score`;
    CREATE TEMPORARY TABLE `tmp_score` (
        `stu_id` INT NOT NULL,
        `stu_name` VARCHAR(255) NOT NULL,
        `total_score` INT NOT NULL,
        `avg_score` FLOAT NOT NULL,
        PRIMARY KEY (`stu_id`)
    );

    OPEN cursor_stu_id;

    stu_loop: LOOP
        FETCH cursor_stu_id INTO stu_id;
        IF finished = TRUE THEN 
            LEAVE stu_loop;
        END IF;

        SELECT `name` INTO stu_name FROM `student` WHERE `stu_id` = stu_id;

        SELECT SUM(`sorce`), COUNT(*) INTO total_score, total FROM `score` WHERE `stu_id` = stu_id;

        SET avg_score = total_score / NULLIF(total, 0);

        INSERT INTO `tmp_score` (`stu_id`, `stu_name`, `total_score`, `avg_score`) VALUES (stu_id, stu_name, total_score, avg_score);

    END LOOP;

    CLOSE cursor_stu_id;

    SELECT * FROM `tmp_score` ORDER BY `total_score` DESC;

    DROP TEMPORARY TABLE `tmp_score`;

END

在這個預存程序中，我們先定義了一些需要使用的變量，包括total_score 表示某個學生的總分，avg_score 表示某個學生的平均分數，stu_id 表示某個學生的編號，stu_name 表示某個學生的姓名，還有一個臨時表tmp_score 。

然後我們使用了 DECLARE CURSOR 語句宣告了一個遊標變數 cursor_stu_id，用於查詢學生表中的學生編號。在循環中，我們根據這個學生編號查詢成績表，計算出該學生的總分和平均分，並將其保存到臨時表中。最後，我們使用 SELECT 語句查詢臨時表，按照總分從高到低排序，並在最後使用 DROP TABLE 語句刪除臨時表。

最後，我們可以使用CALL 語句來呼叫這個預存程序：

CALL `calc_stu_score`();

總結

預存程序是MySQL 中一個非常強大的工具，它可以大大簡化我們的資料庫操作流程，並提高資料庫的效能和安全性。在實際應用中，我們可以使用預存程序來完成許多複雜的資料處理操作，以便更好地滿足業務需求，提高效率，降低成本。

以上是mysql 使用預存程序的詳細內容。更多資訊請關注PHP中文網其他相關文章！