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

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


说明:
in:该类型参数作为输入,也就是需要调用时传入值
out:该类型参数作为输出,也就是该参数可以作为返回值
inout:既可以作为输入参数,也可以作为输出参数
参数类型长度:不指定长度时mysql会默认一个长度,如int会默认int(11),为什么是11,因为int的有符号类型的最大长度就是-2147483648,是11位的。


2、查看存储过程
select * from information_schema.routines where routine_schema = 'xxx'; --查看指定数据库的存储过程及状态信息

show create procedure 存储过程名字 ; --查看某个存储过程的定义sql语句


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 duble)
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 = 1 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;

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐