初学mysql(十)-数据库之存储过程、函数与游标-自定义函数和流程控制（下）

最新推荐文章于 2022-02-28 15:30:12 发布

不贰过先生

最新推荐文章于 2022-02-28 15:30:12 发布

阅读量898

点赞数

分类专栏：数据库文章标签： mysql自定义流程控制mysql自定义

本文链接：https://blog.csdn.net/u013132035/article/details/53148622

版权

数据库专栏收录该内容

17 篇文章 2 订阅

订阅专栏

这篇博客详细介绍了MySQL中的流程控制和自定义函数，包括IF语句、CASE语句、LOOP、LEAVE、ITERATE、REPEAT和WHILE等的使用。通过实例展示了如何在数据库中进行条件判断、循环操作，以及存储过程和函数的调用、查看、修改和删除。

摘要由CSDN通过智能技术生成

上一篇博客讲了存储过程、函数、以及游标，这一篇博客接着上一篇博客来说。首先说说mysql数据库中的流程控制及自定义函数的使用。

自定义函数：

根据所需要的功能，使用流程控制来完成所需要的功能，完成功能的代码就称为自定义函数。要想完成自定义函数就必须学会流程控制的使用。存储过程和函数可以使用流程控制来控制语句的执行。MYSQL中可以使用IF语句、case语句、loop语句、leave语句、iterate语句、while语句来实现流程的控制。和Java、c、c++的使用有些类似，不过还是有些区别的。

表t_user，表t_user1，字段1 id, 字段2 userName, 字段3 job, 字段4 jobtypeid

create table t_user(
      id int primary key auto_increment,
      userName varchar(20) ,
      job  varchar(20),
      jobTypeId int,
      constraint `fk` foreign key(jobTypeId) references t_jobs(id)
);

create table t_user1(
     id int primary key auto_increment,
     userName varchar(20) ,
     job  varchar(20),
     jobTypeId int
);

表t_jobs,字段1 id, 字段2 jobtypeName, 字段3 jobNum

create table t_jobs(
    id int primary key auto_increment,
    jobTypeName varchar(20),
    jobNum int
);

1）：IF语句的使用。

IF语句格式: if search_condition(判断条件) then statement_list(执行语句，可以为一条或者多条)
         [elseif  search_condition then statement_list]...
         [else statements_list]
end if

例子：输入id判断是否有这个id记录如果有修改其工作职位和工作类型id,没有的话插入其记录。

delimiter &&
    create procedure pro_user(in uid int)
    begin
         select count(*) into @nums from t_user where id = uid;
          if @nums > 0 then update t_user set job='飞行员',jobtypeid=6 where id = uid;
          elseif @nums = 0 then insert into t_user values(null, '杨利伟', '飞行员', 6);
           else insert into t_user values(null, '杨利伟', '飞行员', 6);
           end if;
     end
&&

调用存储过程:

call pro_user(1);

2）：case语句的使用。

    case语句格式1：case  case_value(条件判断表达式)
                  when when_value(可能取的值) then statement_list(执行语句)
                  [when when_value(可能取的值) then statement_list(执行语句)]...
                  [else statement_list(执行语句)]
                  end case

 case语句格式2：case
                when search_condition(条件判断语句) then statement_list(执行语句)
                [when search_condition(条件判断语句) then statement_list(执行语句)]...
                [else statement_list(执行语句)]
                end case

例子：输入指定Id查看是否有这个人，有的话修改其userName,job,jobTypeID否则插入此记录。

形式1:

delimiter &&
     create procedure pro_user1(in uid int)
      begin
           select count(*) into @nums from t_user where id = uid;
            case @nums
            when 1 then update t_user set job='平民', jobTypeId=0 where id = uid;
            when 0 then insert into t_user values(null, '希拉里', '平民', 0) ;
             else insert into t_user values(null, '希拉里', '平民', 0);
          end case;
     end
&&

形式2:

delimiter &&
      create procedure pro_user1(in uid int)
      begin
          select count(*) into @nums from t_user where id = uid;
          case
          when @nums = 0 then insert into t_user values(null, '希拉里', '平民', 0);
          when @nums = 1 then update t_user set job='平民', jobTypeId=0 where id = uid;
           else insert into t_user values(null, '希拉里', '平民', 0);
       end case;
   end
&&

调用：call pro_user1(2);

3）：loop、leave语句；loop可以使某些特定的语句重复执行，实现一个简单的循环，但是loop本身没有停止循环的语句，必须是遇到leave语句才能停止循环。

loop语法格式：

[begin label:]loop
statement_list
end loop[end_label]

leave语法格式：

leave label

例子：把t_user表中的n条数据全部导入t_user1表中做备份。

delimiter &&
      create procedure pro_user2(in n int)
      begin
            declare a,b varchar(20);
            declare c int;
            abc:loop
                if n =0 then leave abc;
                else
                   select userName, job, jobTypeId into a,b,c from t_user where id = n;
                    insert into t_user1 values(null, a, b, c);
                    set n = n - 1;
                 end if;
               end loop abc; 
        end
&&

调用：call pro_user2(10);

4）：iterate语句。iterate用来跳出循环，但是它只是跳出本次循环执行下一次循环类似与java的continue语句。

iterate语法：iterate label;

例子：例子把t_user表中的数据备份到t_user1表中，是id为4的倍数的不备份。

delimiter &&
    create procedure pro_user3(in n int)
    begin
         declare a,b varchar(20);
         declare c int;
         abc:loop
               set n = n - 1;
               if n = 0 then leave abc;
               elseif (n%3)=0 then iterate abc;
               else
                     select userName, job,jobTypeId into a,b,c from t_user where id = n;
                     insert into t_user1 values(null, a,b,c);
                end if;
                end loop abc;
    end
&&

调用:call pro_user3(10);

5）：repeat语句。repeat语句有条件的循环控制语句。当满足特定条件时，就会跳出循环语句。

repeat语法：[begin_label: ]repeat

statement_list

until search_condition

end repeat[end_label]

例子：插入10条重复的记录到t_user1表中（这个例子比较简单，读者可以仿照上面写把t_user表中的n条记录备份到t_user1表中）。

delimiter &&
    create procedure pro_user4(in n int)
    begin
            repeat  set n = n - 1;
            insert into t_user1 values(null, '希拉里', '平民', 0);
           until n = 0
          end repeat;
    end
&&

备份记录：

调用：call pro_user4(10);

6）while语句。

while语法：[begin_label:] while search_condition do

statement_list

end while[end_label]

例子：插入10条重复的记录到t_user1表中（这个例子比较简单，读者可以仿照上面写把t_user表中的n条记录备份到t_user1表中）

delimiter &&
    create procedure pro_user5(in n int)
    begin
          while n > 0 do
                 insert into t_user1 values(null, ‘川普’,'总统', 1);
                set n = n - 1;
        end while;
    end
&&

调用：

call pro_user5(10);

A：调用存储过程和函数：

调用存储过程：语法：call 存储过程名字sp_name(参数1,参数2....); 如：call pro_user(10)

调用函数：语法：fun_name(参数1,参数2,.....); 如：call func_user(10);

B:查看存储过程或函数的3种方式

1查看状态：

show {procedure|function}  status [like '存储过程或函数的名字']

如：

show procedure status like 'pro_user';

2查看存储过程或函数的定义：

show create {procedure|function} 存储过程或函数的名字

如：

show create procedure pro_user;

3从information——schema.routines表中查看存储过程或函数的信息。

C:修改存储过程或函数；一般不建议这样做，因为这样容易产生不可发现的不正确信息，一般建议删除重新编写。

语法：

alter {procedure|function} 名字[characteristic...]
 characteristic:{contains sql} no sql | read sql data | modifies sql data}
 | sql security {definer | invoker}
 | comment 'string'

D：删除存储过程或函数：

语法：

drop  {procedure | function} 名字;

例子：

drop procedure pro_user ; drop function func_user;

上面思考题答案：用repeat和while把t_user表中指定的记录条数的数据备份到t_user1表中。

delimiter  &&
   create procedure pro_user(in counts int)
   begin
            declare a,b varchar(20);
            declare c int;
            repeat set counts = counts-1;
                 select userName,job,jobTypeId into a,b,c from t_user where id = counts;
                 insert into t_user1 values(null, a,b, c);
           until n = 0
          end repeat;
   end
&&