Mysql-存储过程

存储过程

什么是存储过程

存储过程是为以后的使用而保存的一条或条SQL语句的集合，类似于批处理

 

为什么用存储过程

1）简单，把多条SQL语句封装在类似函数的单元中，方便调用和修改

2）安全，通过存储过程限制基础数据的访问，减少数据讹误的机会；

　　防止了错误，需要执行的步骤越多，就越可能出错，保持了数据的一致性。

3）高性能，存储过程比单独的SQL语句要快

 

怎么使用存储过程

首先创建一个表

CREATE TABLE students(
    stu_id INT(10) not NULL AUTO_INCREMENT PRIMARY KEY,
        stu_name VARCHAR(50) not NULL,
        age INT(3) not NULL
)ENGINE="INNODB", CHARSET="UTF8";
INSERT INTO students VALUES(NULL, "lily", 21);
INSERT INTO students VALUES(NULL, "tom", 18);
INSERT INTO students VALUES(NULL, "marton", 16);

 

创建存储过程

栗子1：创建无参数的存储过程

DELIMITER //

CREATE PROCEDURE studentage()
BEGIN
    SELECT AVG(age) AS studentavgage
    FROM students;
END //

DELIMITER ;

 调用

CALL studentage();

 

栗子2：创建有参数的存储过程

DELIMITER //

CREATE PROCEDURE studentage(
    OUT agel INT,
    OUT ageh INT,
    OUT agea INT
)
BEGIN
    SELECT MIN(age) 
    INTO agel
    FROM students;
    SELECT MAX(age)
    INTO ageh
    FROM students;
    SELECT AVG(age)
    INTO agea
    FROM students;
END //

DELIMITER ;

调用

CALL studentage(@agemin, @agemax, @ageavg);
SELECT @agemin, @agemax, @ageavg;

 

栗子3：创建一个有输入输出的存储过程

DELIMITER //

CREATE PROCEDURE studentage(
    IN agelimit INT,
    OUT scount INT
)
BEGIN
    SELECT COUNT(*) 
    INTO scount
    FROM students
    WHERE age > agelimit;
END //

DELIMITER ;

调用

CALL studentage(18, @scount);
SELECT @scount;

 

栗子4：创建智能存储过程

需求

三个同学一起上超市购物，超市现在有一个大一新生的优惠活动，旨在促进年轻的大一新生前来购物

是大一新生，购物总价打八折（当然这个需求是我胡诌的。。。-_-）

 

准备数据

CREATE TABLE orderitems(
ord_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
stu_id INT,
total_price DECIMAL(8, 2)
);


INSERT INTO orderitems VALUES(NULL, 1, 200.00);
INSERT INTO orderitems VALUES(NULL, 2, 300.00);
INSERT INTO orderitems VALUES(NULL, 3, 400.00);

SELECT * FROM orderitems;

 

创建

DELIMITER //

CREATE PROCEDURE ordertotal(
    IN sid INT,
    IN isfreshman BOOLEAN,
    OUT ototal DECIMAL(8, 2)
)COMMENT 'Obtain ordertotal after discount, optionally adding discount'

BEGIN
    -- Declare variable for total
    DECLARE total DECIMAL(8, 2);
    -- Declare variable discount percentage
    DECLARE discountperc INT DEFAULT 8;

    -- Get the order total
    SELECT total_price
    FROM orderitems
    WHERE stu_id = sid
    INTO total;

    -- the student is freshman?
    IF isfreshman THEN
        SELECT total*discountperc/10 INTO total;
    END IF;

    -- Finally, save to out variables
    SELECT total INTO ototal;

END //
        
DELIMITER ;

 

调用

CALL ordertotal(1, 1, @ototal);
SELECT @ototal;

 

说明

1）OUT表示从存储过程中传出

2）IN表示传递给存储过程

3）INOUT表示对存储过程传入和传出

4）INTO指定一个检索的值保存到OUT对应的变量中

5）存储过程的代码位于BEGIN和END语句内

6）所有的MYSQL变量都以@为首

7）BOOLEAN值指定1为真，0为假

8）DECLARE定义两个局部变量，要求指定变量名和数据类型

 

显示存储创建过程语句

SHOW CREATE PROCEDURE studentage;

 

显示何时、由谁创建等信息

SHOW PROCEDURE STATUS LIKE 'studentage';

 

删除存储过程

DROP PROCEDURE studentage;

 

参考资料：《Mysql必知必会》