萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 存儲過程完整實戰:建立、刪除、動態 SQL、參數模式、自定義函數與呼叫範例

存儲過程基礎結構

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- 宣告區
    DECLARE var_name datatype DEFAULT value;
    -- 邏輯區
    IF condition THEN
        -- statements
    ELSE
        -- statements
    END IF;
    -- SQL 陳述式
    INSERT INTO ...;
END

參數模式

範例一:動態條件查詢使用者

CREATE PROCEDURE get_users_dynamic(
    IN where_cond VARCHAR(500),
    IN order_by VARCHAR(100)
)
BEGIN
    DECLARE stmt VARCHAR(2000);
    SET @sql = CONCAT('SELECT id, name, password, age, getdate(adddate) AS adddate 
                       FROM users WHERE ', where_cond);
    IF LENGTH(order_by) > 0 THEN
        SET @sql = CONCAT(@sql, ' ORDER BY ', order_by);
    END IF;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

呼叫:

CALL get_users_dynamic('age <= 30', 'age DESC');

範例二:動態插入(處理單引號跳脫)

CREATE PROCEDURE insert_user(
    IN name2 VARCHAR(50), IN pwd2 VARCHAR(32), 
    IN age2 INT, IN adddate2 DATETIME
)
BEGIN
    DECLARE stmt VARCHAR(2000);
    SET @sql = CONCAT('INSERT INTO users(name,password,age,adddate) VALUES(',
        QUOTE(name2), ',', QUOTE(pwd2), ',', age2, ',', QUOTE(adddate2), ')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

QUOTE() 自動處理單引號跳脫,比手動串四個單引號安全。

自定義函數

CREATE FUNCTION getdate(dt DATETIME) RETURNS VARCHAR(50)
RETURN DATE_FORMAT(dt, '%y-%m-%d');

建立、查看、刪除管理

-- 查看所有存儲過程
SHOW PROCEDURE STATUS;

-- 查看特定過程定義
SHOW CREATE PROCEDURE get_users_dynamic;

-- 刪除
DROP PROCEDURE IF EXISTS get_users_dynamic;

註釋與除錯技巧

CREATE PROCEDURE p()
BEGIN
    /* 這是多行註釋 */
    DECLARE v INT DEFAULT 0; -- 單行註釋
    -- 除錯:寫入日誌表或 SELECT 變數觀察
    SELECT v;
END

權限與安全

  • 建立者需 CREATE ROUTINE 權限。
  • 執行者需 EXECUTE 權限。
  • SQL SECURITY DEFINER(預設)以建立者權限執行;INVOKER 以呼叫者權限執行。
copyright © 萬盛學電腦網 all rights reserved
模式方向說明
IN輸入呼叫者傳入,過程內修改不回傳(預設)
OUT輸出過程內賦值,返回給呼叫者
INOUT雙向呼叫者傳入,過程修改後返回