MySQL存储程序入门指南

迈不过友情╰ 2021-11-22 16:04 201阅读 0赞

1，前提 ：需要MySQL 5  
  
2，一个最简单的Hello world 程序  
  
CREATE PROCEDURE HelloWorld()  
BEGIN   
    SELECT "Hello World!";   
END;  
  
3，变量  
使用DECLARE来声明，DEFAULT赋默认值，SET赋值  
  
DECLARE counter INT DEFAULT 0;   
SET counter = counter+1;   
  
4，参数  
IN为默认类型，值必须在调用时指定，值不能返回（值传递）  
OUT值可以返回（指针传递）  
INOUT值必须在调用时指定，值可以返回  
CREATE PROCEDURE test(a INT, OUT b FLOAT, INOUT c INT)  
  
  
5，条件判断  
IF THEN、ELSEIF、ELSE、END IF  
  
CREATE PROCEDURE discunted\_price(normal\_price NUMERIC(8, 2), OUT discount\_price NUMERIC(8, 2))  
BEGIN  
    IF (normal\_price > 500) THEN  
        SET discount\_price = normal\_price \* .8;  
    ELSEIF (normal\_price > 100) THEN  
        SET discount\_price = normal\_price \* .9;  
    ELSE  
        SET discount\_price = normal\_price;  
    END IF;  
END;  
  
6，循环  
LOOP、END LOOP  
  
CREATE PROCEDURE simple\_loop(OUT counter INT)  
BEGIN  
    SET counter = 0;  
    my\_simple\_loop: LOOP  
        SET counter = counter+1;  
        IF counter = 10 THEN  
            LEAVE my\_simple\_loop;  
        END IF;  
    END LOOP my\_simple\_loop;  
END;  
  
WHILE DO、END WHILE  
  
CREATE PROCEDURE simple\_while(OUT counter INT)  
BEGIN  
    SET counter = 0;  
    WHILE counter != 10 DO  
        SET counter = counter+1;  
    END WHILE;  
END ;  
  
REPEAT、UNTILL  
  
CREATE PROCEDURE simple\_repeat(OUT counter INT)  
BEGIN  
    SET counter = 0;  
    REPEAT  
        SET counter = counter+1;  
    UNTIL counter = 10 END REPEAT;  
END;  
  
7，异常处理  
如果用cursor获取SELECT语句返回的所有结果集时应该定义NOT FOUND error handler来防止存储程序提前终结  
如果SQL语句可能返回constraint violation等错误时应该创建一个handler来防止程序终结  
  
  
8，数据库交互  
INTO用于存储单行记录的查询结果  
  
DECLARE total\_sales NUMERIC(8, 2);  
SELECT SUM(sale\_value) INTO total\_sales FROM sales WHERE customer\_id=in\_customer\_id;  
  
CURSOR用于处理多行记录的查询结果  
  
CREATE PROCEDURE cursor\_example()  
    READS SQL DATA  
BEGIN  
    DECLARE l\_employee\_id INT;  
    DECLARE l\_salary NUMERIC(8,2);  
    DECLARE l\_department\_id INT;  
    DECLARE done INT DEFAULT 0;  
    DECLARE cur1 CURSOR FOR SELECT employee\_id, salary, department\_id FROM employees;  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
  
    OPEN cur1;  
    emp\_loop: LOOP  
        FETCH cur1 INTO l\_employee\_id, l\_salary, l\_department\_id;  
        IF done=1 THEN  
            LEAVE emp\_loop;  
        END IF;  
    END LOOP emp\_loop;  
    CLOSE cur1;  
END;  
  
unbounded SELECT语句用于存储过程返回结果集  
  
CREATE PROCEDURE sp\_emps\_in\_dept(in\_employee\_id INT)  
BEGIN  
    SELECT employee\_id, surname, firstname, address1, address2, zipcode, date\_of\_birth FROM employees WHERE department\_id=in\_employee\_id;  
END;  
  
UPDATE、INSERT、DELETE、CREATE TABLE等非查询语句也可以嵌入存储过程里  
  
CREATE PROCEDURE sp\_update\_salary(in\_employee\_id INT, in\_new\_salary NUMERIC(8,2))  
BEGIN  
    IF in\_new\_salary < 5000 OR in\_new\_salary > 500000 THEN  
        SELECT "Illegal salary: salary must be between $5000 and $500, 000";  
    ELSE  
        UPDATE employees SET salary=in\_new\_salary WHERE employee\_id=in\_employee\_id;  
    END IF:  
END ;  
  
9，使用CALL调用存储程序  
  
CREATE PROCEDURE call\_example(employee\_id INT, employee\_type VARCHAR(20))  
    NO SQL  
BEGIN  
    DECLARE l\_bonus\_amount NUMERIC(8,2);  
  
    IF employee\_type='MANAGER' THEN  
        CALL calc\_manager\_bonus(employee\_id, l\_bonus\_amount);  
    ELSE  
        CALL calc\_minion\_bonus(employee\_id, l\_bonus\_amount);  
    END IF;  
    CALL grant\_bonus(employee\_id, l\_bonus\_amount);  
END;  
  
10，一个复杂的例子  
  
CREATE PROCEDURE putting\_it\_all\_together(in\_department\_id INT)  
    MODIFIES SQL DATA  
BEGIN  
    DECLARE l\_employee\_id INT;  
    DECLARE l\_salary NUMERIC(8,2);  
    DECLARE l\_department\_id INT;  
    DECLARE l\_new\_salary NUMERIC(8,2);  
    DECLARE done INT DEFAULT 0;  
  
    DECLARE cur1 CURSOR FOR  
        SELECT employee\_id, salary, department\_id  
        FROM employees  
        WHERE department\_id=in\_department\_id;  
  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
  
    CREATE TEMPORARY TABLE IF NOT EXISTS emp\_raises  
        (employee\_id INT, department\_id INT, new\_salary NUMERIC(8,2));  
  
    OPEN cur1;  
    emp\_loop: LOOP  
        FETCH cur1 INTO l\_employee\_id, l\_salary, l\_department\_id;  
        IF done=1 THEN    /\* No more rows \*/  
            LEAVE emp\_loop;  
        END IF;  
        CALL new\_salary(1\_employee\_id, l\_new\_salary); /\* Get new salary \*/  
        IF (l\_new\_salary <> l\_salary) THEN  /\* Salary changed \*/  
            UPDATE employees  
                SET salary=l\_new\_salary  
            WHERE employee\_id=l\_employee\_id;  
            /\* Keep track of changed salaries \*/  
            INSERT INTO emp\_raises(employee\_id, department\_id, new\_salary)  
                VALUES (l\_employee\_id, l\_department\_id, l\_new\_salary);  
        END IF:  
    END LOOP emp\_loop;  
    CLOSE cur1;  
    /\* Print out the changed salaries \*/  
    SELECT employee\_id, department\_id, new\_salary from emp\_raises  
        ORDER BY employee\_id;  
END;  
  
11，存储方法  
存储方法与存储过程的区别  
1，存储方法的参数列表只允许IN类型的参数，而且没必要也不允许指定IN关键字  
2，存储方法返回一个单一的值，值的类型在存储方法的头部定义  
3，存储方法可以在SQL语句内部调用  
4，存储方法不能返回结果集  
例子：  
  
CREATE FUNCTION f\_discount\_price  
    (normal\_price NUMERIC(8,2))  
    RETURNS NUMERIC(8,2)  
    DETERMINISTIC  
BEGIN  
    DECLARE discount\_price NUMERIC(8,2);  
  
    IF (normal\_price > 500) THEN  
        SET discount\_price = normal\_price \* .8;  
    ELSEIF (normal\_price >100) THEN  
        SET discount\_price = normal\_price \* .9;  
    ELSE  
        SET discount\_price = normal\_price;  
    END IF;  
  
    RETURN(discount\_price);  
END;  
  
  
12，触发器  
触发器在INSERT、UPDATE或DELETE等DML语句修改数据库表时触发  
触发器的典型应用场景是重要的业务逻辑、提高性能、监控表的修改等  
触发器可以在DML语句执行前或后触发  
  
CREATE TRIGGER sales\_trigger  
    BEFORE INSERT ON sales  
    FOR EACH ROW  
BEGIN  
    IF NEW.sale\_value > 500 THEN  
        SET NEW.free\_shipping = 'Y';  
    ELSE  
        SET NEW.free\_shipping = 'N';  
    END IF;  
  
    IF NEW.sale\_value > 1000 THEN  
        SET NEW.discount = NEW.sale\_value \* .15;  
    ELSE  
        SET NEW.discount = 0;  
    END IF;  
END;

转载于:https://www.cnblogs.com/wxb-km/archive/2012/11/18/2775780.html