mysql 存储过程

1.存储过程的创建

1. mysql> DELIMITER //  --定义分割符//
2. mysql> CREATE PROCEDURE proc1(OUT s int)  
3.     -> BEGIN 
4.     -> SELECT COUNT(*) INTO s FROM user;  
5.     -> END 
6.     -> //  
7. mysql> DELIMITER ;--恢复mysql默认的分割符；

注：

（1）这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。

（3）过程体的开始与结束使用BEGIN与END进行标识。

 

2. 参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数:模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程（在存储过程内部，该参数初始值为 null，无论调用者是否给存储过程参数设置值）

INOUT 输入输出参数:调用时指定，并且可被改变和返回

 

 

 

 IN参数实例

##创建存储过程
DROP PROCEDURE if exists test1;
delimiter// ##定义新的分割符
create procedure test1(in a int)
begin
    select a;##A
    set a=2;
    select a;##B
    select * from worksheet w where w.customer_id=a;##C
end//
delimiter； ##恢复默认分割符



set @a=1;  ##定义变量
call test1(@a); ##调用存储过程,传入变量@a
select @a; ##存储过程中改变变量@a的值为2，此时@a的值仍然为1

 

 

输出结果：

A.

B.

C.

 

  OUT参数实例：

DROP PROCEDURE if exists test1;
delimiter//
create procedure test1(out a int)
begin
    select a;##A
    set a=2;
    select a;##B
    select * from worksheet w where w.customer_id=a;##C
end//

set @a=1;
call test1(@a);
SELECT @a;##D：变量在存储过程中被改变，此时是改变后的值，值为2.

输出结果：

A.输出结果为null

B.输出结果为2.

C.输出customer_id为2的数据结果

D.输出结果为2

 

DROP PROCEDURE IF EXISTS test1;
DELIMITER //
CREATE PROCEDURE test1(IN input INT,OUT output INT)
BEGIN
    DECLARE var INT DEFAULT 0;
    
    SELECT COUNT(*) INTO output  FROM aaa_test;   ##将数据记录总数保存到变量output中输出。     
        
END//


SET @input=0;
CALL test1(@input,@output);
SELECT @output;

 

 

 

INOUT参数实例：

DROP PROCEDURE if exists test1;
delimiter//
create procedure test1(inout a int)
begin
    select a;## A：值为1
    set a=2;
    select a;## B：值为2
    select * from worksheet w where w.customer_id=a;## C：值为customer_id=2的数据列表
end//

set @a=1;
call test1(@a);
SELECT @a;## D ：变量@a的值在存储中改变，此时值为2.

 

 

 

4.      MySQL存储过程的调用

 

用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。

 

 

5.      MySQL存储过程的查询

我们像知道一个数据库下面有那些表，我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。

我们可以用

select name from mysql.proc where db=’数据库名’;

或者

select routine_name from information_schema.routines where routine_schema='数据库名';

或者

show procedure status where db='数据库名';

进行查询。

如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？

答案是：我们可以查看存储过程的详细，但是需要用另一种方法：

SHOW CREATE PROCEDURE 数据库.存储过程名;

就可以查看当前存储过程的详细。

 

6. 变量

变量分为用户变量和系统变量，系统变量又分为会话和全局级变量

用户变量：用户变量名一般以@开头，滥用用户变量会导致程序难以理解及管理

 

DECLARE必须出现在BEGIN  和  EDN之间，且在其它所有语句之前。

　　①申明变量　　　　

DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT '1999-12-31';
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded'; 

 

　　②变量赋值

　　　　SET 变量名 = 表达式值 [,variable_name = expression ...]。

　　　　如：set @a='hello world';

　  set @b=10;

③使用用户变量　　　
      SELECT 10 INTO @b;

或   set @b=10;

　　SELECT @b;

DROP PROCEDURE if exists test1;
delimiter //
create procedure test1(inout a int)
begin
    DECLARE vb VARCHAR(30) DEFAULT 'helloworld';
    select vb;##输出'hello world'
end
//

 

7.条件语句

DROP PROCEDURE if exists test1;
delimiter //
create procedure test1(in input int,out output varchar(30))
begin
    DECLARE var int DEFAULT 0;
    set var=input+1;
    select var;
    if var=1 then
        set output='var =1';
    elseif var =2 then
        set output='var =2';
    elseif var <=10 then
        set output=concat(var ,'<=10');
    else 
        SET output=CONCAT(var ,'>10');
    end if;
end//




SET @input=11;
CALL test1(@input,@output);
SELECT @input;
SELECT @output;

 

8.case…when

DROP PROCEDURE if exists test1;
delimiter //
create procedure test1(in input int,out output varchar(30))
begin
    DECLARE var int DEFAULT 0;
    set var=input+1;
    select var;
    case var
    when 1 then
        set output='var =1';
    when 2 then
        set output='var =2';
    else 
        SET output=concat('var=',var);
    end case;
end//

9.循环语句

创建表：

drop table if exists aaa_test;
create table aaa_test(
    id int primary key auto_increment,
    username varchar(30) not null,
    password varchar(30) not null,
    birthday date not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

①while……end while

 

 [ label: ]   WHILE  expression DO

 statements

 END   WHILE   [ label ]  ;

 

DROP PROCEDURE IF EXISTS test1;
DELIMITER //
CREATE PROCEDURE test1(IN input INT,OUT output VARCHAR(30))
BEGIN
    DECLARE var INT DEFAULT 0;
    SET var=input+1;    
    WHILE var<10 DO
        SELECT var;
        INSERT INTO aaa_test(username,PASSWORD,birthday) VALUES(var,CONCAT('pass_',var),'2016-3-24');
        SET var=var+1;
    END WHILE;
END//


SET @input=0;
CALL test1(@input,@output);

查询表可看到：

 

② repeat ……until ……end repeat

　　　　它在执行操作后检查结果，而while则是执行前进行检查。

 [ label: ]  REPEAT

 statements

 UNTIL expression

 END  REPEAT  [ label ]  ;

 

 

DROP PROCEDURE IF EXISTS test1;
DELIMITER //
CREATE PROCEDURE test1(IN input INT,OUT output VARCHAR(30))
BEGIN
    DECLARE var INT DEFAULT 0;
    SET var=input+1;        
    REPEAT
        SELECT var;
        INSERT INTO aaa_test(username,PASSWORD,birthday) VALUES(CONCAT('repeat_name_',var),CONCAT('repeat_pass_',var),CURRENT_TIMESTAMP());
        SET var=var+1;
    UNTIL var >10
    END REPEAT;    
END//


SET @input=0;
CALL test1(@input,@output);

查询表可看到：

 

③loop……end loop；

[ label: ]  LOOP

  statements

  END  LOOP  [ label ] ;

 

DROP PROCEDURE IF EXISTS test1;
DELIMITER //
CREATE PROCEDURE test1(IN input INT,OUT output VARCHAR(30))
BEGIN
    DECLARE var INT DEFAULT 0;
    SET var=input+1;        
    LOOP_LABEL:LOOP   ##用标签标记循环
        SELECT var;
        INSERT INTO aaa_test(username,PASSWORD,birthday) VALUES(CONCAT('loop_name_',var),CONCAT('loop_pass_',var),CURRENT_TIMESTAMP());
        SET var=var+1;
        IF var >10 THEN
       　　 LEAVE LOOP_LABEL;## leave 跳出标签标记的循环
    　　END IF;    
    END LOOP;    
END//


SET @input=0;
CALL test1(@input,@output);

查询表可看到：

 

④LABLES 标号：

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

如上面loop循环中使用的：

　　

 ……省略……

LOOP_LABEL:LOOP   ##用标签标记循环
        SELECT var;
        INSERT INTO aaa_test(username,PASSWORD,birthday) VALUES(CONCAT('loop_name_',var),CONCAT('loop_pass_',var),CURRENT_TIMESTAMP());
        SET var=var+1;
        IF var >10 THEN
       　　 LEAVE LOOP_LABEL;## leave 跳出标签标记的循环
    　　END IF;    
    END LOOP;  

……省略……

 

⑤ITERATE:

通过引用复合语句的标号,来从新开始复合语句。相当java 中的continue，跳出当前循环后继续执行。

DROP PROCEDURE if exists test1;
delimiter //
create procedure test1(in input int,out output varchar(30))
begin
    DECLARE var int DEFAULT 0;
    set var=input+1;        
    LOOP_LABEL:loop
        SELECT var;
        
        IF var =3 OR var =4 THEN
            SET var=var+1;
            ITERATE LOOP_LABEL;  ##当var=3或4时，跳出循环。
        END IF;    
        
        insert into aaa_test(username,password,birthday) values(concat('iterate_name_',var),concat('iterate_pass_',var),CURRENT_TIMESTAMP());
        set var=var+1;
                    
        if var >10 then
            leave LOOP_LABEL;
        end if;    
    end loop ;    
end//


SET @input=0;
CALL test1(@input,@output);

查询表可看到：