存储过程

#存储过程：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();

　　例子，通过存储过程向数据表中添加指定数量信息。

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();