mysql存储过程和函数(一)——简单的存储过程编写

    存储过程

---

存储过程简介：
    能完成比较复杂的判断和运算
    可编程性强，灵活
    sql编程的代码可以重复使用
    执行速度相对会快一些
    减少网络之间的数据传输，节省开销

---

准备工作：
dilimiter  可以修改sql语句的结尾结束符。修改分号为其他符号，
因为存储过程和函数中有多个分号，多以需要用dilimiter修改结束符

---

本博客使用测试数据库为mysql官方测试数据库sakila,mysql官网可下载

---

mysql简单存储过程

1，最简单的存储过程

需求：查询actor表中id为偶数的actor信息

delimiter $$
create procedure sel_even_actor()
begin
    select * from actor where actor_id%2 = 0;
end;
$$

delimiter;
call sel_even_actor();

总结:
    创建语法：create procedure procedure_Name;
    包含一个以上的代码块，代码块要用begin,end之间包含；
    在命令行中创建的情况下，需要使用定义分隔符delimiter $$，
    创建结束并且要修改回来

2，存储过程中定义变量

需求：取出actor_id = 28的演员名

delimiter $$
create procedure sel_actorname_28()
begin 
//定义变量，用来保存需要查询的名字
declare actorname_28 varchar(64) default "";
//set可以给变量赋值
set actorname_28 = 'NiuShao';
//存储过程中也可以用into赋值
select concat(first_name,'-',last_name) as name into actorname_28 from actor where actor_id=28;
//显示查出的值
select actorname_28;
end;
$$

delimiter;
call sel_even_actor();

总结:
    变量的声明使用declare语句，一个declare只能声明一个变量，变量必须先声明后使用
    变量具有数据类型和长度，和myuql的sql数据类型保持一致，因此也可以指定默认值，字符集和排序规则
    变量可以用set进行赋值，也可以用select into 的方式赋值
    变量如果需要返回，可以使用select语句，例如：select 变量名


需求：取出actor,film表的行数以及actor表名字用A开头的演员和film中用B开头的电影

delimiter $$
create procedure sel_actor_film()
begin
    begin 
        declare a_sum int default 0;
        declare f_sum int default 0;
        select count(*) into a_sum from actor;
        select count(*) into f_sum from film;
        select a_sum,f_sum;
    end;
    begin 
        declare a_a varchar(64) default "";
        declare f_a varchar(64) default "";
        select concat(first_name,'-',last_name) into a_a from actor where first_name like "A%";
        select title into f_a from film where title like 'A%';
        select a_a,f_a;
    end;
end;
$$

delimiter ;
call sel_actor_film();//这里第二个代码块因为超过了一行，调用的时候无法显示。所以变量只能保存一个数据来输出

注意:变量是有作用域的，作用范围在begin和end块之间,end结束变量的作用范围即结束
    需要多个块之间传递值，可以使用全局变量，即放在所有代码块之前
    传参变量是全局的，可以在多个代码块之间起作用

3，存储过程的传入参数IN

需求:传入参数actor_id,取出该id下的演员名字

delimiter $$
create procedure sel_actorname_in(actorid int)
begin
    declare actorname varchar(64) default "";
    select concat(first_name,'-',last_name) as name into actorname from actor where actor_id = actorid;     
    select actorname;
end;
$$
delimiter ;

call sel_actorname_in(28);

总结:传入参数，类型为IN，表示该参数的值必须在调用存储过程的时候指定，如果不是指定为IN，默认就是IN类型
    IN类型参数一般只用于传入，在调用的过程中一般不做修改和返回
    如果调用存储过程中需要修改和返回值，可以使用out类型参数

4，存储过程的传入参数OUT

需求:传入film_id,输出电影title

delimiter $$
create procedure sel_film_out(in id int,out name varchar(64))
begin
    select title into name from film where film_id=id;
    select name;
end;
$$
delimiter ;

set @name="";   //mysql中命令行定义变量
call sel_film_out(1,@name); //mysql中命令行调用变量

归纳:
    传出参数，在调用存储过程中，可以改变值，并且可以返回
    out是传出参数，不能用于传入参数的值
    调用存储过程时，out参数也需要指定，但必须是变量，不能是常量
    如果既需要传入参数，同时有需要传出参数，可以使用INOUT类型参数

5，存储过程的可变参数INOUT

需求：传入file_id,同时传出title,file_id

delimiter $$
    create procedure sel_film_inout(inout id int,inout name varchar(64))
    begin
        select film_id,title into id,name from film where film_id=id;
        select id,name;
    end;
$$
delimiter ;

set @name="";
set @id="28";
call sel_film_inout(@id,@name);

归纳：
    可变变量inout,调用额时候可传入值，在调用过程中可以修改它的值，同时也能返回值
    inout 参数集合了in和out类型的参数功能
    inout调用的时候传入的是变量，而不是常量

6，存储过程中的条件语句

需求:编写存储过程,如果id为偶数，给我title。如果为奇数，给出id

delimiter $$
    create procedure sel_film_if(IN id int)
    begin
    declare name varchar(32) default "";
        if(id%2=0)
        then
            select title into name from film where film_id = id;
            select name;
        else
            select id;
        end if; 
    end;
$$
delimiter ;

call sel_film_if(29);
call sel_film_if(28);

归纳:
    条件语句最基本的结构 if() then ……else……end if;
    引申为 if() 
            then
                 ……
            elseif() 
            then
                ……
            elseif() 
            then
                ……
            end if;
    if判断返回逻辑的真假，表达式可以是任意返回真假的表达式

7,while循环语句

需求：
    创建只有id的表，并且插入一万条数据

create table test_while( 
`id` int not null
);

delimiter $$
    create procedure insert_test()
    begin
        declare i int default 0;
        while(i<=10000) do
        begin
            select i;
            set i = i+1;
            insert into test_while(id)values(i);
        end; 
        end while;
    end;
$$
delimiter ;

call test_while();

归纳:
    while语句最基本的结构
    while()do
    begin
    end;
    end while;

    while判断返回逻辑的真假，表达式可以是任意返回真假的表达式

8，repeat循环语句

需求:同while的测试表，降序插入10000条语句

delimiter $$
    create procedure insert_repeat()
    begin
        declare i int default 10001;
            repeat
            begin
            select i;
            set i = i-1;
            insert into test_while(id)values(i);
            end;
            until i<0
            end repeat;
    end;
$$
delimiter ;

归纳:
    repeat语句最基本的结构，repeat
                            begin
                                ……
                            end;
                            untile……
                            end repeat;
    until判断返回逻辑的真或者假，表达式可以是任意返回真或者i假的表达式，只有当until语句为真的时候，循环结束。

9，游标

简介:mysql中的游标，就是保存查询结果的临时内存区域

需求：编写存储过程,使用游标把film_id为偶数的记录加后缀

delimiter $$
create procedure film_cursor()
begin
    //定义停止循环的变量
    declare stopwhile int default 0;
    //定义变量获取当前游标的值
    declare name varchar(64);
    //定义游标变量，保存当前查询结果
    declare name_cursor cursor for select title from film where film_id%2=0;
    //continue handler 是游标句柄，如果游标结束，则改变循环变量的值
    declare continue handler for not found set stopwhile = 1;
    //打开游标，也就是当前定义的游标变量
    open name_cursor;
    //获取当前游标变量，赋值给name
    fetch name_cursor into name;
    //循环修改
    while (stopwhile=0) do
        begin
        update film set title = concat(name,'_NiuShao') where title = name;
        fetch name_cursor into name;
        end;
    end while;
    //关闭游标
    close name_cursor;
end;
$$
delimiter ;

call film_cursor()

归纳:
    定义游标变量，变量名后加 cursor
    游标变量是一个select 语句赋值，fetch一次，查一次，直到查询结束，也就是游标句柄找不到。赋值用 for
    使用游标需要开启游标和关闭游标