存储过程(一)

一、存储过程的基本语法：

1、创建存储过程
create procedure 存储过程名称(in/out/inout 参数名 参数类型(长度))
begin
     SQL语句;
end;

说明：
in：该类型参数作为输入，也就是需要调用时传入值
out：该类型参数作为输出，也就是该参数可以作为返回值
inout：既可以作为输入参数，也可以作为输出参数
参数类型长度：不指定长度时mysql会默认一个长度
如int会默认int(11)，因为int的有符号类型的最大长度就是-2147483648，是11位的。
 
2、查看指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema = 'xxx'; 

查看某个存储过程的定义sql语句
show create procedure 存储过程名字; 
 
3、删除
drop procedure [if exists] 存储过程名字;

注意：在命令行定义存储过程时 ，需要用关键字delimiter定义结束符号(默认是;) ，这里改为$$

二、变量的使用

1、系统变量：是mysql服务器提供，不是用户自定义的，属于服务器层面。分为全变量global、会话变量session。
 
查看系统变量
show [session|global] variables;  --查看所有的系统变量
show [session|global] variables like ‘...’;  --可以通过like模糊匹配方式查找变量
select @@[session/global.]系统变量名；  --查看指定变量的值，注意可选参数里面的.
 
设置系统变量
set [session|global] 系统变量名字 = 值;  --不需要@，查询是才需要@
上面这种设置全局变量方法重启后就失效，不想失效就修改参数文件, 然后重启mysqld
vi /etc/my.cnf
[mysqld]
wait_timeout=10
 
例子：show session variables like 'auto%'
     select @@global.autocommit;
     select @@session.autocommit;
     set session auto_increment = 1 ;
 
2、用户变量：是用户根据需求自己定义的变量，用户变量不用提前声明，
在使用时候直接用‘@变量名’使用就可以。其作用域为当前连接。
赋值
set @var_name = 值;
set @var_name := 值; --推荐使用这种冒号等于的方式定义
 
select @var_name := 值;
select 字段名 into @var_name from 表名;
 
查看变量
select @var_name ;    --如果直接select 一个不存在的用户变量名会返回null而不会报错
 
例子：--赋值
    set @myname := 'xiaoming';
    set @myage := 18;
    --使用
    select @myname,@myage
 
3、局部变量：是根据需要定义在局部生效的变量，访问之前，需要declare声明。
可用作存储过程内的局部变量和输入参数，局部变量的作用范围是在其内声明的begin...end模块。
 
声明：
declare 变量名 变量类型(长度) [default 默认值]
变量类型就是数据库字段类型：int、bigint、char、varchar、date、time
 
赋值：
set 变量名 = 值；
set 变量名 ：= 值；
select 字段名 into 变量名 from 表名；

ps：区分技巧：系统变量有2个@@，用户变量1个@，局部变量在存储过程或存储函数内部用declare声明。

三、其他语法




四、实践

1、创建一个存储过程，输入一个分数后返回是分数的评判等级
create procedure p1(in score int, out result varchar(10))
begin 
     if score >= 85 then
        set result := '优秀';
     elseif score >= 60 then
        set result := '及格';
     eles
        set result := '不及格';
     end if;
end;
 
call p1(85,@result); --来一个用户自定义变量来接收out返回的值
select @result;
 
2、将传入的200分制的分数，进行换算，换成百分制，然后返回分数---> inout
create procedure p2(inout score double)
begin
     set score := score * 0.5;
end;
 
set @score = 78;
call p2(@score);
select @score;
 
3、while实现1到n的累加
create procedure p3(in n int)
begin
     declare total int default 0;
     while n>0 do
           set total := total + n;
           set n := n -1;
     end while;
     select total;
end;
 
4、repeat实现计算1到n的累加，n为传入参数值
   --a.定义局部变量，记录累加之后的值；
   --b.每循环一次，就对n-1，n为0则退出循环
create procedure p4(in n int)
begin 
     declare total int default 0;
    
     repeat
           set total := total + n;
           set n := n-1;
     until n<=0
     end repeat;
     select total;
 
end; 
 
5、loop实现1到n的奇数的累加
create procedure p5(in n int)
begin 
     declare total int default 0;
    
     sum:loop
 
         if n<=0 then
            leave sum;
         end if;
         
         if n%2 = 0 then
            set n := n -1;
            iterate sum;
         end if;
 
         set total := total + n;
         set n := n -1;
 
     end loop sum;
   
     select total;
 
end;
 
6、case when使用
create procedure p6(in month int)
begin
     declare result varchar(10);
   
     case 
         when month >= 1 and month <= 3 then
              set result := '第一季度';
         when month >=4 and month <= 6 then
              set result := '第二季度';
         when month >= 7 and month <= 9 then
              set result := '第三季度';
         when month >=10 and month <= 12 then
              set result := '第四季度';
         else
              set result := '非法参数';
    end case;
    select result;
 
end;
 
call p6(6)
 
7、游标的使用, 输入一个年龄，把tb_user表中小于这个年龄的用户的信息插入到另一个表tb_user_pro中
create procedure p7(int uage int)
begin
     declare u_cursor cursor for select name,profession from tb_user where age <=uage;
     declare uname varchar(10);
     declare upro varchar(100);
 
     declare exit handler for NOT FOUND close u_cuesor; --条件处理程序，当下面while循环时游标的数据为空时就不会报错了。
 
     drop table id exists tb_user_pro;
     create table if exists tb_user_pro(
          id int primary key auto_increment,
          name varchar(10),
          profession varchar(100)
     );
 
    
     open u_cursor;
 
     while true do
          fetch u_cursor into uname,upro;
          insert into tb_user_pro values(null,uname,upro);
     end while;
     close u_cursor;
     
end;

**注意:**循环语句 LOOP执行过程中可使用 LEAVE语句或者ITEREATE来跳出循环，也可以嵌套IF等判断语句。
LEAVE 语句效果对于Java中的break，用来终止循环；
ITERATE语句效果相当于Java中的continue，用来跳过此次循环。进入下一次循环。且ITERATE之下的语句将不在进行

显示存储过程
SHOW PROCEDURE STATUS;

显示特定数据库的存储过程

SHOW PROCEDURE STATUS WHERE db = 'db名字' AND NAME = 'name名字';

显示特定模式的存储过程

SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';

显示存储过程的源码

SHOW CREATE PROCEDURE 存储过程名;

删除存储过程

DROP PROCEDURE 存储过程名;

例如：使用存储过程插入信息

CREATE PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20))
	BEGIN
	   -- 声明一个变量 用来决定这个名字是否已经存在
	   DECLARE s_count INT DEFAULT 0;
	   -- 验证这么名字是否已经存在
	   SELECT COUNT(*) INTO s_count FROM student WHERE `name` = s_student;	
	   IF s_count = 0 THEN
	        INSERT INTO student (`name`, sex) VALUES(s_student, s_sex);
		SET s_result = '数据添加成功';
	   ELSE
                SET s_result = '名字已存在，不能添加';
                SELECT s_result;
	   END IF;
	END;