MySQL笔记（八）存储过程procedure

这是我在学习Mysql之路上做的笔记，今天将它粘出来。这一篇主要是存储过程 procedure。有错误的欢迎大家指出。。。

数据库的创建参考另一篇博客 

储存过程

优点：

1、在创建时，经过编译存放到数据库中，以后每次执行都不需要编译，可以加快查询速度，提高数据库性能

2、对数据库进行复杂操作时，如果你在前台处理的话。可能会涉及到多次数据库连接。适合把这些语句做成储存过程，就只连接一次

3、储存过程可重复使用

4、安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

缺点：

1、可移植性差

2、对于简单SQL无优势

3、对于高并发的，不宜写过多的储存过程

存储过程三个参数in(默认)，out，inout

in读取外部参数值，只在存储过程内部有效

out不读取外部参数值，存储过程执行完毕后保留值

inout读取外部参数值，存储过程执行完毕后保留值

#delimiter修改结束符

delimiter//

SELECT NOW()//

#创建存储过程

delimiter//

CREATE PROCEDURE p1()

BEGIN

SELECT COUNT(1) FROM tb_emp;

END//

#调用存储过程

CALL p1;

#带有参数的存储过程

#in读取外部参数值，只在存储过程内部有效

delimiter//

CREATE PROCEDURE pin(IN p_inINT)

BEGIN

SELECT p_in;

SET p_in=6;

SELECT p_in;

END//

#测试in

SET @p_in=1;

CALL pin(@p_in);

SELECT @p_in;

#out不读取外部参数值，存储过程执行完毕后保留值

delimiter//

CREATE PROCEDURE pout(OUT p_outINT)

BEGIN

SELECT p_out;

SET p_out=6;

SELECT p_out;

END//

#测试out

SET @p_out=1;

CALL pout(@p_out);

SELECT @p_out;

#inout读取外部参数值，存储过程执行完毕后保留值

delimiter//

CREATE PROCEDURE pinout(INOUT p_inoutINT)

BEGIN

SELECT p_inout;

SET p_inout=6;

SELECT p_inout;

END//

#测试out

SET @p_inout=1;

CALL pinout(@p_inout);

SELECT @p_inout;

#显示数据库中的存储过程

SHOW PROCEDURE STATUS WHERE db='luo';

#查看创建存储过程的语句

SHOW CREATE PROCEDURE luo.pinout;

#删除存储过程

DROP PROCEDURE IF EXISTS luo.pinout;

#储存过程流程控制语句
#MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

-- 1、条件语句：IF-THEN-ELSEIF-ELSE-END IF
-- 2、多分支语句：CASE-WHEN-ELSE-END 
-- 3、循环语句：
-- WHILE-END WHILE
-- REPEA1T-UNTIL-END REPEAT
-- LOOP-ITERATE-LEAVE-END LOOP

-- 1、条件语句
#条件语句IF-THEN-ELSEIF-ELSE-END IF

delimiter //
DROP PROCEDURE IF EXISTS luo.p1//
CREATE PROCEDURE p1(INOUT p_inout INT)
BEGIN
	DECLARE var INT DEFAULT 0; #声明变量
	SET var = p_inout + 1 ;#注意：设置用set
	IF var = 1 THEN
		SELECT *FROM tb_dept WHERE deptno = 10 ;
	ELSEIF var = 2 THEN
		SELECT *FROM tb_dept WHERE deptno = 20 ;
	ELSE
		SELECT *FROM tb_dept WHERE deptno = 30 ;
	END IF;
END//
delimiter ;

SET @p_inout=0;
CALL p1(@p_inout);

-- 2、多分支语句：
-- CASE-WHEN-ELSE-END  语句:相当于switch-case多分支语句

delimiter //
DROP PROCEDURE IF EXISTS luo.p2//
CREATE PROCEDURE p2(INOUT p_inout INT)
BEGIN
    DECLARE var INT DEFAULT 0; #声明变量
    SET var = p_inout + 1 ;#注意：设置用set
    CASE var
        WHEN 1 THEN 
            SELECT *FROM tb_dept WHERE deptno = 10 ;
        WHEN 2 THEN
            SELECT *FROM tb_dept WHERE deptno = 20 ;
        WHEN 3 THEN
            SELECT *FROM tb_dept WHERE deptno = 30 ;
        ELSE
            SELECT *FROM tb_dept WHERE deptno = 40 ;
        END CASE;
END//
delimiter ;

SET @p_inout=1;
CALL p2(@p_inout);

查看和删除存储过程

SHOW PROCEDURE STATUS WHERE db='luo';
DROP PROCEDURE IF EXISTS luo.p2;

-- 3、循环语句：
-- WHILE-END WHILE

delimiter //
DROP PROCEDURE IF EXISTS luo.p3//
CREATE PROCEDURE p3(INOUT p_inout INT)
BEGIN
    DECLARE var INT DEFAULT 0; #声明变量
    SET var = p_inout + 1 ;#注意：设置用set
    WHILE var<6 DO
    SET var = var +1;
    SELECT var;
    END WHILE;
END//
delimiter ;

SET @p_inout=0;
CALL p3(@p_inout);

-- REPEAT-UNTIL-END REPEAT语句相当于DO-WHILE
-- UNTIL直到。。。

delimiter //
DROP PROCEDURE IF EXISTS luo.p4//
CREATE PROCEDURE p4(INOUT p_inout INT)
BEGIN
    DECLARE var INT DEFAULT 0; #声明变量
    SET var = p_inout + 1 ;#注意：设置用set
    REPEAT
    SET var = var +1;
    SELECT var;
    UNTIL var>6  #注意：没有分号
    END REPEAT;
END//
delimiter ;

SET @p_inout=0;
CALL p4(@p_inout);

-- LOOP-ITERATE-LEAVE实现循环  
-- loop 表示无条件循环，leave 类似于break 语句，跳出循环，跳出 begin end，iterate 类似于continue 

delimiter //
DROP PROCEDURE IF EXISTS p_loop//
CREATE PROCEDURE p_loop()
BEGIN
    DECLARE x INT DEFAULT 0;
    DECLARE str VARCHAR(200);
    SET x=0;
    SET str='';
    loop_label:LOOP
        IF x>10 THEN 
            LEAVE loop_label;
        END IF;
        SET x=x+1;
        IF (x MOD 2) THEN #是2的倍数，结果是0
            ITERATE loop_label;
        ELSE
            SET str=CONCAT(str,x,',');
        END IF;
    END LOOP;
    SELECT str;
END//
delimiter ;

CALL p_loop();