MySQL——存储过程和函数从零基础到入门必学教程(涵盖基础实战)
作者:mmseoamin日期:2023-12-25

目录

​前言

一、创建存储过程

二、在存储过程中使用变量

1.定义变量

2.为变量赋值

三、光标的使用

1.打开光标

2.打开光标

3.使用光标

4.关闭光标

四、流程控制的作用

1.IF语句

2.CASE语句

3.LOOP语句

4.LEAVE语句

5.ITERATE语句

6.REPEAT语句

7.WHILE语句

五、流程控制综合运用

六、查看存储过程

七、存储过程的删除

总结


 ✨✨✨大家好,我是会飞的鱼-blog,今天我来给大家介绍一下Mysql,有不足之处,请大家多多指教。感谢大家支持!!!

MySQL——存储过程和函数从零基础到入门必学教程(涵盖基础实战),第1张

前言

        存储过程和函数是在数据库中定义的一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中存储和执行的,可以减少客户器端和服务端的数据传输。 通过本次的学习,将了解存储过程的定义、作用,还可以了解创建、使用、查看、修改及删除存储过程及函数的方法。

MySQL——存储过程和函数从零基础到入门必学教程(涵盖基础实战),第2张

       


一、创建存储过程

        创建存储过程和函数是指将经常使用的一组SQL语句组合在一起,并将这些SQL语句当作一个整体存储在MySQL服务器中。存储程序可以分为存储过程和函数。在MySQL中创建存储过程使用的语句CREATE PROCEDURE。其语法形式如下:

        CREATE PROCEDURE procedure_name([proc_param[,…]])   

                 routine_body  

        在上述语句中,参数procedure_name表示所要创建的存储过程名字,参数proc_param表示存储过程的参数,参数routine_body表示存储过程的SQL语句代码,可以用BEGIN…END来标志SQL语句的开始和结束。

        提示: 在具体创建存储过程时,存储过程名不能与已经存在的存储过程名重名,实战中推荐存储过程名命名为procedure_xxx或者proc_xxx。

        proc_param中每个参数的语法形式如下:

                    [IN|OUT|INOUT] param_name type

        在上述语句中,每个参数由三部分组成,分别为输入/输出类型、参数名和参数类型。其中,输入/输出类型有三种类型,分别为IN(表示输入类型)、OUT(表示输出类型)、INOUT(表示输入/输出类型)。param_name表示参数名;type表示参数类型,可以是MySQL软件所支持的任意一个数据类型。

【示例11-1】

mysql>  use school;   #选择数据库school                                            

mysql> DELIMITER $$                                                                                

mysql> create PROCEDURE  proc_delete_student (IN sid int )                                  

        BEGIN

         declare cid  int ;   #定义变量cid                           

   Select class_id into cid from student where id = sid; #通过查询语句设置变量                                                                      

         delete from grade where id = sid;  #删除成绩表中的记录 

         delete from student where id = sid;   #删除学生表中的记录                                                    

         update class set count=count-1 where id = cid; #更新班级表中的记录  

        END;                                                                       

        $$                                                                          

         DELIMITER ;                                                                

mysql>  call proc_delete_student(2);    #调用存储过程                                                          

二、在存储过程中使用变量

        在存储过程和函数中,可以定义和使用变量。用户可以使用关键字DECLARE来定义变量,然后为变量赋值。这些变量的作用范围是在BEGIN…END程序段中。

1.定义变量

                在MySQL中,可以使用DECLARE关键字来定义变量。定义变量的基本语法如下:

                    DECLARE var_name[,…] type [DEFAULT value]  

                其中,关键字DECLARE是用来声明变量的;参数var_name是变量的名称,可以同时定义多个变量;参数type用来指定变量的类型;DEFAULT value子句将变量默认值设置为value,没有使用DEFAULT子句时,默认值为NULL。

                定义变量cid,数据类型为INT型,默认值为10,代码如下:

                  DECLARE cid INT DEFAULT 10;   

2.为变量赋值

                在MySQL中可以使用关键字SET来为变量赋值,SET语句的基本语法如下:

                    SET var_name=expr[,var_name=expr]…

                其中,关键字SET用来为变量赋值;参数var_name是变量的名称;参数expr是赋值表达式。一个SET语句可以同时为多个变量赋值,各个变量的赋值语句之间用逗号隔开。

                例如,将变量tmp_id赋值为88,代码如下:              

  SET tmp_id = 88;
                在MySQL中,还可以使用SELECT…INTO语句为变量赋值。其基本语法如下:
                     SELECT col_name[,…] INTO var_name[,…]    
                     FROM table_name WHERE condition        

            其中,参数col_name表示查询的字段名称;参数var_name是变量的名称;参数table_name指表的名称;参数condition指查询条件。

        【示例11-2】从表employee中查询id为3的记录,将该记录的id值赋给变量tmp_id,代码如下:                 

 SELECT id INTO tmp_id
                FROM grade WEHRE id=sid;  

mysql>  use school;   #选择数据库school                                            

mysql>  drop  PROCEDURE if exists query_student_class_info;                                                                             

mysql> DELIMITER $$                                                                               

mysql> create procedure  query_student_class_info (IN sid int, OUT cname varchar(128), OUT ccount  int)                                   

        BEGIN  

            declare tmp_name varchar(128);

            declare tmp_count int;

            declare tmp_cid  int;

            select class_id into tmp_cid from student where id = sid;        

            select name, count into tmp_name, tmp_count from class where id = tmp_cid;

            set cname = tmp_name, ccount = tmp_count;

         END;   

         $$                                                                          

         DELIMITER ;                                                                 

mysql>  call query_student_class_info(4, @name, @count);    #调用存储过程 

mysql>  select @name, @count;                                                        

三、光标的使用

        查询语句可能查询出多条记录,在存储过程和函数中使用光标来逐条读取查询结果集中的记录。有些书上将光标称为游标。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前,并且声明在变量和条件之后。

1.打开光标

        在MySQL中,可以使用DECLARE关键字来声明光标,其基本语法如下:

                    DECLARE cursor_name CURSOR        

                    FOR select_statement;                 

        其中,参数cursor_name表示光标的名称;参数select_statement表示SELECT语句的内容。

        【示例11-2】下面声明一个名为cur_student的光标,代码如下:

mysql>use school;#选择数据库                                                                                                                      

mysql> DELIMITER $$                                                                                

mysql> create procedure  query_student (IN sid int, OUT cname varchar(128), OUT class_id  int )                                     

        BEGIN                                                 

            DECLARE cur_student CURSOR                    

                FOR SELECT name, class_id FROM  student;   

         END;                                                 

         $$                                                                          

         DELIMITER ;                                                                                                                        

        在上面的示例中,光标的名称为cur_student;SELECT语句部分是从表student中查询出字段name和class_id的值。

2.打开光标

        在MySQL中,使用关键字OPEN来打开光标,其基本语法如下:

            OPEN cursor_name;

        其中,参数cursor_name表示光标的名称。

        下面代码打开一个名为cur_student的光标,代码如下:

            OPEN cur_student;

3.使用光标

          FETCH cursor_name

              INTO var_name[,var_name…];

        其中,参数cursor_name表示光标的名称;参数var_name表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。

use school;   #选择数据库school
DELIMITER $$ 
create procedure query_student (IN sid int, OUT cname varchar(128), OUT cid int)                                                                                   
BEGIN
        declare tmp_name varchar(128);
        declare tmp_cid int;
        declare done int default 0;
        declare cur_student CURSOR FOR SELECT name,class_Id from student where id=sid;
        declare continue handler for not found set done = 1; #将结束标志绑定到游标上
        open  cur_student;
         select done;
        fetch cur_student into tmp_name, tmp_cid;
        select done;
         select tmp_name, tmp_cid;
         close cur_student; 
          set cname = tmp_name, cid = tmp_cid;
        END;
        $$
        DELIMITER ;

4.关闭光标

        在MySQL中,使用关键字CLOSE来关闭光标,其基本语法如下:

            CLOSE cursor_name;

        其中,参数cursor_name表示光标的名称。

        例如: 关闭一个名为cur_student的光标,代码如下:

           

CLOSE cur_student;

        在上面的示例中,关闭了这个名称为cur_student的光标。关闭了之后就不能使用FETCH来使用光标了。提示

        如果存储过程或函数中执行了SELECT语句,并且SELECT语句会查询出多条记录,这种情况最好使用光标来逐条读取记录,光标必须在处理程序之前且在变量和条件之后声明,而且光标使用完毕后一定要关闭。

四、流程控制的作用

1.IF语句

        IF语句用来进行条件判断。根据条件执行不同的语句。其语法的基本形式如下:

IF search_condition THEN statement_list      

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list]                          

END  IF                                      

        参数search_condition表示条件判断语句;参数statement_list表示不同条件的执行语句。

---【示例11-4】下面是一个IF语句的示例,代码如下:

 IF age>20 THEN SET @count1=@count1+1;  
    ELSEIF age=20 THEN @count2=@count2+1;
    ELSE @count3=@count3+1;               
  END IF;  

                                    

        该示例根据age与20的大小关系来执行不同的SET语句。如果age值大于20,将count1的值加1;如果age值等于20,就将count2的值加1;其他情况将count3的值加1。IF语句都需要使用END IF来结束。

#课堂范例

mysql>  use school;   #选择数据库school                                                                                                                         

mysql> DELIMITER $$                                                                               

mysql> create procedure proc_test_if (IN input int, OUT output int)

        begin

            if input>20 then set input=input+1;

            elseif input=20 then  set input=input+2;

            else  set input = input+3;

            end if;

            set output = input;

        end;

mysql>  $$                                                                                 

mysql>  DELIMITER ;                                                                                                                        

2.CASE语句

        CASE语句可实现比IF语句更复杂的条件判断,其语法的基本形式如下:

CASE case_value                                 

WHEN when_value THEN statement_list           

[ WHEN when_value THEN statement_list ]         

[ELSE statement_list]                              

END CASE                                        

        其中,参数case_value表示条件判断的变量;参数when_value表示变量的取值;参数statement_list表示不同when_value值的执行语句。

---【示例11-5】下面是一个CASE语句的示例。代码如下:

CASE level                                   
      WHEN 20 THEN SET attack = attack + 5; 
      WHEN 30 THEN SET attack = attack + 10;
      WHEN 40 THEN SET attack = attack + 15;
      ELSE SET attack = attack + 1;
END CASE         

  

        当级别level值为20时,attack值加5;当级别level值为30时,attack值加10;当级别level值为40时,attack值加15;否则,attack + 1。CASE语句使用END CASE结束。

3.LOOP语句

        LOOP语句可以使某些特定的语句重复执行,实现一个简单的循环。LOOP语句本身没有停止循环,只有遇到LEAVE语句等才能停止循环。LOOP语句的语句形式如下:

   [begin_label:] LOOP            

   statement_list                 

   END LOOP [end_label]         

        其中,参数begin_label和参数end_label分别表示循环开始和结束的标志,这两个标志必须相同,而且都可以省略;参数statement_list表示需要循坏执行的语句。

---【示例11-6】下面是一个LOOP语句的示例,代码如下:

add_num:LOOP                
     SET @count = @count + 1;
END LOOP add_num;           

        该示例循环执行count加1的操作。因为没有跳出循环的语句,这个循环成了一个死循环。LOOP循环都以END LOOP结束。

4.LEAVE语句

        LEAVE语句主要用于跳出循环控制,其语法形式如下:

LEAVE label                 

        其中,参数label表示循环的标志。

---【示例11-7】下面是一个LEAVE语句的示例。代码如下:

add_num: LOOP             
SET @count=@count + 1;
Select @count;
IF @count = 100 THEN
    LEAVE add_num;    
END IF;
END LOOP add_num;     

        该示例循环执行count值加1的操作。当count的值等于100时,LEAVE语句跳出循环。

5.ITERATE语句

        ITERATE语句也是用来跳出循环的语句,但是ITERATE语句是跳出本次循环,然后直接进入下一次循环,ITERATE语句的语法形式如下:

  ITERATE label 

        其中,参数label表示循环的标志。

---【示例11-8】下面是一个ITERATE语句的示例。代码如下:

add_num1:LOOP             
    Set @count = @count +1
    IF @count=100 THEN    
        LEAVE add_num1      
    ELSE IF MOD(@count, 3) = 0 then
        ITERATE add_num1;  
     Select * from student;   
END LOOP add_num1;      

        该示例循环执行count加1的操作,count的值为100时结束循环。如果count的值能够整除3,就跳出本次循环,不再执行下面的SELECT语句。

        注意: LEAVE语句和ITERATE语句都用来跳出循环语句,但是两者的功能是不一样的。LEAVE语句是跳出整个循环,然后执行循环后面的程序,和C++ break 相似。ITERATE语句是跳出本次循环,然后进入下一次循环,和C++ continue 相似。使用这两个语句时一定要区分清楚。

6.REPEAT语句

        REPEAT语句是有条件控制的循环语句。当满足特定条件时,就会跳出循环语句。REPEAT语句的基本语法形式如下:

[begin_label:] REPEAT       

        statement_list;      

     UNTIL search_condition

END REPEAT [end_label]     

        其中,参数statement_list表示循环的执行语句;参数search_condition表示结束循环的条件,满足该条件时循环结束。

---【示例11-9】下面是一个REPEAT语句的示例。代码如下:

REPEAT                       
     SET @count=@count+1;  
     UNTIL @count=100       
END REPEAT;           

       

        该示例循环执行count加1的操作,count值为100时结束循环。REPEAT循环都用END REPEAT结束。

7.WHILE语句

        WHILE语句也是有条件控制的循环语句,但WHILE语句和REPEAT语句是不一样的。WHILE语句是当满足条件时执行循环内的语句。WHILE语句的基本语法形式如下:

[begin_label:] WHILE search_condition DO  

        Statement_list                     

END WHILE [end_label]                    

        其中,参数statement_condition表示循环执行的条件,满足该条件时循环执行;参数statement_list表示循环的执行语句。

---【示例11-10】下面是一个WHILE语句的示例。代码如下:

WHILE @count<100 DO      
    SET @count = @count + 1;
END WHILE;          

        

五、流程控制综合运用

mysql>  use school;   #选择数据库school
mysql> DELIMITER $$                                                                               
mysql> create procedure query_all_students (IN sid int, OUT cname varchar(128), OUT cid int)
BEGIN
        declare tmp_name varchar(128);    #必须定义在声明光标之前
        declare tmp_cid  int;
         declare  done int default 0;
        declare cur_student CURSOR FOR SELECT name, class_id FROM  student ;
        declare continue handler for not found set done = 1; #将结束标志绑定到游标上
        open  cur_student;
        read_loop:LOOP      #循环读取
        fetch cur_student into tmp_name, tmp_cid;
                IF done=1 then
                        Leave read_loop;
                END IF;
                select tmp_name, tmp_cid;  
        END LOOP read_loop;
        close cur_student;
        set cname = tmp_name, cid = tmp_cid;
END; 
mysql>  $$
mysql>  DELIMITER ;  
【示例11-12】在学生表中插入一条记录,并返回记录的自增长id
mysql>  use school;   #选择数据库school 
mysql> DELIMITER $$  
mysql> create procedure fetch_insert_student_id (IN p_name varchar(128), in p_class_id int, IN p_sex char(1), OUT rid int)
                BEGIN
                        Insert into student (name, class_id, sex) values(p_name, p_class_id, p_sex);
                        select last_insert_id() as rid; 
                END;
mysql>  $$
mysql>  DELIMITER ; 

六、查看存储过程

        存储过程创建以后,用户可以通过SHOW STATUS语句来查看存储过程的状态,也可以通过SHOW CREATE语句来查看存储过程的定义。用户也可以通过查询information_schema数据库下的Routines表来查看存储过程的信息。本节将详细讲解查看存储过程的状态与定义的方法。

1.SHOW STATUS语句查看存储过程

        在MySQL中,可以通过SHOW STATUS语句。其基本语法形式如下:

     SHOW PROCEDURE STATUS  [ like ‘pattern’ ] ; 

        其中,参数PROCEDURE表示查询存储过程;参数LIKE 'pattern'用来匹配存储过程的名称。

图11-13的执行结果显示了存储过程的创建时间、修改时间和字符集等信息。

MySQL——存储过程和函数从零基础到入门必学教程(涵盖基础实战),第3张

2.使用SHOW CREATE语句查看存储过程的定义

        在MySQL中,可以通过SHOW CREATE语句查看存储过程的状态,语法形式如下:

    SHOW CREATE PROCEDURE proc_name     

        其中,参数PROCEDURE表示查询存储过程;参数proc_name表示存储过程的名称。

---【示例11-14】查询名为proc_delete_student的存储过程的状态,代码如下,执行结果如下图所示。

   

 SHOW CREATE PROCEDURE proc_delete_student \G

MySQL——存储过程和函数从零基础到入门必学教程(涵盖基础实战),第4张

3.从information_schema.Routine表中查看存储过程的信息

        存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下:

        SELECT * FROM information_schema.Routines

         Where ROUTINE_NAME = ‘proc_name’;

        其中,字段ROUTINE_NAME是Routines 存储存储过程和函数的列名称;参数proc_name表示存储过程或函数的名称。

---【示例11-15】下面从Routines表中查询名为proc_delete_student的存储过程信息,具体SQL代码如下,执行结果如下图所示。

select routine_definition from information_schema.Routines where         routine_name='proc_delete_student';

MySQL——存储过程和函数从零基础到入门必学教程(涵盖基础实战),第5张

七、存储过程的删除

        在MySQL中删除存储过程通过SQL语句DROP完成:

    DROP PROCEDURE proc_name;            

        在上述语句中,关键字DROP PROCEDURE用来表示实现删除存储过程,参数proc_name表示所要删除的存储过程名称。

---【示例11-16】执行SQL语句DROP PROCEDURE,删除存储过程对象proc_delete_student,具体步骤如下:

DROP PROCEDURE proc_delete_student;

总结

✨✨✨以上就是我学习的总结,希望大家一起探讨、探讨,一起加油,迈向更好的明天!!!