您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 存儲過程、函數、觸發器、游標完整語法速查與實戰範例

編輯：MySQL 綜合教學

MySQL 存儲過程、函數、觸發器、游標完整語法速查與實戰範例

系統整理 MySQL 程式化物件核心語法，含變量、流程控制、游標、觸發器實戰模板。

1. 存儲過程

DELIMITER //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
    SELECT COUNT(*) INTO param1 FROM t;
END //
DELIMITER ;

-- 調用
CALL simpleproc(@a);
SELECT @a;

2. 函數

CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ', s, '!');

-- 調用
SELECT hello('world');

3. 變量與流程控制

DECLARE var_name INT DEFAULT 10;
SET var_name = 2;

-- 迴圈
label: LOOP
    SET @x = @x + 1;
    IF @x = 100 THEN LEAVE label; END IF;
    INSERT INTO student (stuname) VALUES (CONCAT('name', @x));
END LOOP;

-- REPEAT / WHILE
REPEAT SET @x = @x + 1; UNTIL @x > 10 END REPEAT;
WHILE @y < 5 DO SET @y = @y + 1; END WHILE;

4. 游標

CREATE PROCEDURE cursor_demo()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(20);
    DECLARE cur CURSOR FOR SELECT id, name FROM student;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_id, v_name;
        IF done THEN LEAVE read_loop; END IF;
        SELECT v_id, v_name;  -- 處理邏輯
    END LOOP;
    CLOSE cur;
END;

5. 觸發器


CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(a4 INT AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0);

DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END |
DELIMITER ;

-- 測試
INSERT INTO test3 (a3) VALUES (NULL),(NULL),(NULL);
INSERT INTO test4 (a4) VALUES (0),(0),(0);
INSERT INTO test1 VALUES (1),(3);