#存储过程:mysql5.0以上。将sql以函数的形式存储起来共调用,极大地提高了数据库的处理速度,同时提高了数据库编程的灵活性。
#定界符,防止被mysql当成sql语句处理
DELIMITER //
create procedure demo()#函数名
begin #过程体的开始与结束使用begin与end进行标识
select * from 表名
end
// DELIMITER ;
#用户变量:::凡是带有@的都属于用户变量,使用用户变量要先set @user = '';,否则null;查询用户变量select @user;注意,滥用用户变量易导致程序难以理解或管理。
#注释:::--单行;/****/多行注释;
#过程函数参数:::IN/OUT/INOUT 参数名 类型(为mysql字段类型)
DELIMITER //
create procedure demo()
BEGIN
select * from sp_user;
END
//
DELIMITER;
#调用
CALL demo();
#获取数据的全部存储过程
select * from information_schema.ROUTINES;
select * from information_schema.ROUTINES where specific_name = 'in_param';
#in
DELIMITER //
CREATE PROCEDURE in_param(IN p_in int)
BEGIN
SELECT p_in;
SET p_in=2;
SELECT p_in;
END;
//
DELIMITER ;
drop procedure in_param;
#调用
SET @p_in=1;
CALL in_param(@p_in);
SELECT @p_in;
#out
DELIMITER //
CREATE PROCEDURE out_param(OUT p_out int)
BEGIN
SELECT p_out;
set p_out=2;
select p_out;
END
// DELIMITER;
#调用
SET @p_out=1;
CALL out_param(@p_out);
SELECT @p_out;
#inout
DELIMITER //
CREATE PROCEDURE inout_param(INOUT p_inout int)
BEGIN
SELECT p_inout;
SET p_inout=2;
SELECT p_inout;
END
//
DELIMITER;
drop PROCEDURE inout_param;
#调用
SET @p_inout=1;
CALL inout_param(@p_inout) ;
SELECT @p_inout;
SELECT 'Hello World' into @x;
SELECT @x;
SET @y='Goodbye Cruel World';
SELECT @y;
SET @z=1+2+3;
SELECT @z;
#IN/OUT/INPUT参数
#试例1,简单调用(OUT)。实现调用存储过程时,传入 user_id 返回改用户的user_name
drop PROCEDURE out_demo;
DELIMITER //
create procedure out_demo(IN my_user_id varchar(32),OUT user_name varchar(32))
BEGIN
select name into user_name from Watcher where id = my_user_id;
select user_name;
END
//
DELIMITER;
#试例1调用方法:
CALL shoufuyou_statistics.out_demo('5',@user_name);
#试例2,简单调用(IN)。实现调用存储过程时,传入 user_id 返回改用户的user_name
drop PROCEDURE in_demo;
DELIMITER //
create procedure in_demo(IN my_user_id varchar(32))
BEGIN
declare user_name varchar(32) default '';#因参数中不含有此变量,故须要声明declare
select name into user_name from Watcher where id = my_user_id;
select user_name;
END
//
DELIMITER;
#试例2调用方法:
CALL shoufuyou_statistics.in_demo('5');
#试例3,简单调用(INOUT)。实现调用存储过程时,传入 user_id 返回改用户的user_name
drop PROCEDURE inout_demo;
DELIMITER //
create procedure inout_demo(INOUT my_user_id varchar(32),INOUT user_name varchar(32))
BEGIN
set my_user_id = '5';
set user_name = '易君强';
select id,name into my_user_id,user_name from Watcher where id = my_user_id;
select my_user_id,user_name;
END
//
DELIMITER;
#试例3调用方法:
call inout_demo(@my_user_id,@user_name);#正确
call inout_demo('6',@user_name);#错误
###变量作用域 由上自下
DELIMITER //
CREATE PROCEDURE default_demo()
begin
declare x1 varchar(5) default 'outer';
begin
declare x1 varchar(5) default 'inner';
select x1;
end;
select x1;
end;
//
DELIMITER ;
call default_demo();
###条件语句
#1.if-then-else
delimiter $$
create procedure if_demo(IN parameter int)
begin
declare var int;
set var = parameter * 1;
if var = 2 then
insert into demo value(3,17);#向表demo中添加信息,注意表的字段数量
end if;
if parameter = 0 then
update demo set user_email = user_email + 1;#修改表demo的字段user_email值为自身+1
else
update demo set user_email = user_email + 2;#修改表demo的字段user_email值为自身+2
end if;
end;
$$
delimiter;
call if_demo(2);#调用
#2.case语句
DELIMITER //
CREATE PROCEDURE case_demo (in parameter int)
begin
declare var int;
set var = parameter + 1;
case var
when 0 then
insert into demo (user_email)values(17);
when 1 then
insert into demo (user_email)values(18);
else
insert into demo (user_email)values(19);
end case;
end;
//
DELIMITER ;
call case_demo(-1);
###循环语句
#1.while...end while 特点是执行操作前检查结果
DELIMITER //
CREATE PROCEDURE while_demo()
begin
declare var int;
set var = 0;
while var < 6 do
insert into demo (user_email)values(var);
set var = var + 1;
end while;
end;
//
DELIMITER ;
call while_demo();
#2.repeat···· end repeat 特点是执行操作后检查结果,与do..while类似
DELIMITER //
CREATE PROCEDURE repeat_demo()
begin
declare v int;
set v = 0;
repeat
insert into demo (user_email)values(v);
set v = v + 1;
until v >= 5
end repeat;
end;
//
DELIMITER ;
call repeat_demo();
#3.LOOP...END LOOP
DELIMITER //
drop function if exists loop_demo;
CREATE PROCEDURE loop_demo()
begin
declare v int;
set v = 0;
LOOP_LABLE:LOOP
insert into demo (user_email)VALUES(v);
set v = v + 1;
if v >= 5 then
LEAVE LOOP_LABLE;#类似于break,满足条件就跳出
end if;
END LOOP;
end;
//
DELIMITER ;
call loop_demo();
#3.LOOP...END LOOP--demo---在MySQL中用函数实现在字符串一后面循环拼接n个字符串二
delimiter $$
drop function if exists fun_addStr;
create function fun_addStr(str1 varchar(100),str2 varchar(10),num int) returns varchar(200)
begin
declare i int default 1;
declare result varchar(200) default '';
set result = str1;
myloop:loop
set i = i + 1;
set result = concat(result,str2);
if i > num then
leave myloop;
end if;
end loop myloop;
return result;
end $$
delimiter;
select fun_addStr('字符串一','字符串二',3);
#迭代
#ITERATE 通过引用复合语句的标号,来从新开始复合语句
DELIMITER //
CREATE PROCEDURE iterate_demo()
begin
declare v int;
set v = 0;
LOOP_LABLE:LOOP
if v = 3 then
set v = v + 1;
ITERATE LOOP_LABLE;
end if;
insert into demo (user_email)VALUES(v);
set v = v + 1;
if v >= 5 then
LEAVE LOOP_LABLE;
end if;
END LOOP;
end;
//
DELIMITER ;
call iterate_demo();