MYSQL高级第一天(3)

存储过程和函数：

一、定义：

　　存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效果是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有

函数：是一个有返回值的过程；

过程：是一个没有返回值的函数；

 

二、语法：

1、创建：

delimiter $;
create procedure pro_test1()
begin
    select 'Hello';
end$

2、查看：

--方法一
select name from mysql.proc where db='db_name';

--方法二
show procedure status\G;

--方法三
show create procedure pro_test1\G;

3、调用：

call pro_test1();

4、删除：

drop procedure pro_test1;

 

二、语法结构：

1、变量：

声明：declare

delimiter $;
create procedure pro_test1()
begin
    declare num int default 0;
    select concat('num的值为：',num);
end$

赋值(set 或者 select into)：

delimiter $;
create procedure pro_test2()
begin
    declare num int default 0;
    set num=num+10;
    select concat('num的值为：',num);
end$

　　

delimiter $;
create procedure pro_test3()
begin
    declare num int default 0;
    select count(*) into num from city;
    select concat('city表中的记录数为：',num);
end$

call pro_test3();

 

三、流程控制：

1、if条件判断：

delimiter $;
create procedure pro_test4()
begin
    declare height int default 175;
    declare description varchar(50) default '';
    if height>=180 then
        set description="高挑";
    elseif heihgt>=170 then
        set description="标准";
    else
        set description="一般";
    select concat('身高为：',height,'对应的身材类型为：',description);
    end if;
end$

@会话变量，@@系统变量

set @name='itcast';

select @name;

传参：

mysql>delimiter $;
->create procedure pro_test6(in height int,out description varchar(10))
->begin
->   if height>=180 then
->       set description='高';
->    elseif height>=170 then
->        set description='中';
->    elseif height>=160 then
->        set description='低';
->   end if

mysql>call pro_test6(175,@description)$

mysql>select @descriptioin$

　　

2、case语法：

delimiter $;
create procedure pro_test7(mon int)
begin
    declare result varchar(10) default '';
    case
        when mon>=1 and mon<=3 then
            set result='第一季度';
        when mon>=4 and mon<=6 then
            set result='第二季度';
        when mon>=7 and mon<=9 then
            set result='第三季度';
        when mon>=10 and mon<=12 then
            set result='第四季度';
    end case;
    select concat('传递的月份为：',mon,‘，属于’,result);
end$

 

3、while循环：

delimiter $;
create procedure pro_test8(n int)
begin
    declare total int default 0;
    declare num int default 1;
    while num<=n do
        set total=total+num;
        set num=num+1;
    end while;
    select total;
end$

 

4、repeat循环

delimiter $;
create procedure pro_test9(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循环

delimiter $;
create procedure pro_test10(n int)
begin
    declare total int default 0;
    c:loop
        set total=total+n;
        set n=n-1;
        if n<0 then
            leave c;
        end if;
     end loop c;
    select total;
end$

 

四、游标、光标：

语法：

delimiter $;
create procedure pro_test11()
begin
    declare e_id int default 0;
    declare e_name varchar(50) default '';
    declare e_age int default 0;
    declare e_salary int default 0;
    declare has_data int default 1;
    
    declare emp_result cursor as select * from emp;
    open emp_result;    

    fetch emp_result int e_id,e_name,e_age,e_salary;
    select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary);
    select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary);
    close emp_result;
end$

　　

循环读取游标：

delimiter $;
create procedure pro_test11()
begin
    declare e_id int default 0;
    declare e_name varchar(50) default '';
    declare e_age int default 0;
    declare e_salary int default 0;
    declare has_data int default 1;
    
    declare emp_result cursor as select * from emp;
    declare exit handler for not found set has_data=0;
    open emp_result;
    
    repeat
    fetch emp_result int e_id,e_name,e_age,e_salary;
    select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary=',e_salary);
    until has_data=0
    end repeat;
    close emp_result;
end$

　　

 

五、存储函数：

语法：

--创建
delimiter $;
create function fun1(countryId int)
returns int
begin
    declare cnum int;
    select count(*) into cnum from city where country_id=countryId;
     return cnum;
end$

select fun1(1)$

　　

--删除
drop function fun1$