mysql 存储过程
1.存储过程的创建
- mysql> DELIMITER // --定义分割符//
- mysql> CREATE PROCEDURE proc1(OUT s int)
- -> BEGIN
- -> SELECT COUNT(*) INTO s FROM user;
- -> END
- -> //
- 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);
查询表可看到: