细谈Mysql的存储过程和存储函数-6ren

细谈Mysql的存储过程和存储函数

转载作者：qq735679552 更新时间：2022-09-27 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章细谈Mysql的存储过程和存储函数由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

1 存储过程。

1.1 什么是存储过程。

存储过程是一组为了完成某项特定功能的sql语句集，其实质上就是一段存储在数据库中的代码，他可以由声明式的sql语句（如CREATE,UPDATE,SELECT等语句）和过程式sql语句（如IF...THEN...ELSE控制结构语句）组成。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用.

1.2 存储过程的优缺点。

优点

1.可增强sql语言的功能和灵活性存储过程可以用流程控制语言编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算.

2.良好的封装性存储过程被创建后，可以在程序中被多次调用，而不必担心重写编写该存储过程的sql语句.

3.高性能存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中，以后的操作中只需要从高速缓冲器中调用已编译好的二进制代码执行即可，从而提高了系统性能.

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程.

1.3 创建存储过程。

1.3.1 DELIMITER定界符。

在sql中服务器处理sql语句默认是以分号作为语句的结束标志，然而在创建存储过程时，存储过程体中可能包含多条sql语句，这些sql语句如果仍以分号作为语句结束符，那么服务器在处理时会以第一条sql语句处的分号作为整个程序的结束符，而不再去处理后面的sql。为解决这个问题，通常使用DELIMITER命令，将sql语句的结束符临时修改为其他符号.

DELIMITER语法格式:

 
    ? 
   
         DELIMITER $$

$$是用户定义的结束符，通常这个符号可以是一些特殊的符号。另外应避免使用反斜杠,因为他是转义字符。若希望换回默认的分号作为结束标记，只需再在命令行输入下面的sql语句即可.

 
    ? 
   
         DELIMITER ;

1.3.2 存储过程创建。

在Mysql中，使用CREATE PROCEDURE语句来创建存储过程.

 
    ? 
   
         CREATE 
         PROCEDURE 
         p_name([proc_parameter[,...]]) 
        
         routine_body

其中，语法项“proc_parameter”的语法格式是:

 
    ? 
   
         [ 
         IN 
         | 
         OUT 
         |INOUT]parame_name type<br>

1."p_name"用于指定存储过程的名称.

2."proc_parameter"用于指定存储过程中的参数列表。其中，语法项"parame_name"为参数名，"type"为参数的类型（类型可以是Mysql中任意的有效数据类型）。Mysql的存储过程支持三种类型的参数，即输入参数IN，输出参数OUT,输入输出参数INOUT。输入参数是使数据可以传递给一个存储过程；输出参数是用于存储过程需要返回的一个操作结果；输入输出参数既可以充当输入参数也可以充当输出结果。参数的取名不要和表中的列名相同，否则尽管不会返回出错信息，但储存过程中的sql语句会将参数名当做列名，从而引发不可预知的错误.

3.语法项"rountine_body"表示存储过程的主体部分，也成为存储过程体，其包含了需要执行的sql。过程体以关键字BEGIN开始，以关键字END结束。若只有一条sql可以忽略BEGIN....END标志.

1.3.3 局部变量。

在存储过程体中可以声明局部变量，用来存储过程体中的临时结果。在Mysql中使用DECLARE语句来声明局部变量.

 
    ? 
   
         DECLARE 
         var_name type [ 
         DEFAULT 
         value]

"var_name"用于指定局部变量的名称；"type"用来声明变量的类型；"DEFAULT"用来指定默认值，如果没有指定则为NULL.

注意：局部变量只能在存储过程体的BEGIN...END语句块中;局部变量必须在存储过程体的开头处声明;局部变量的作用范围仅限于声明它的BEGIN...END语句块，其他语句块中的语句不可以使用它.

1.3.4 用户变量。

用户变量一般以@开头.

注意：滥用用户变量会导致程序难以理解及管理.

1.3.5 SET语句。

在Mysql中通过SET语句对局部变量赋值，其格式是:

 
    ? 
   
         SET 
         var_name = expr[,var_name2 = expr]....

1.3.6 SELECT....INTO语句。

在Mysql中，可以使用SELECT...INTO语句把选定的列的值存储到局部变量中。格式是:

 
    ? 
   
         SELECT 
         col_name[,..]  
         INTO 
         var_name[,....] table_expr

其中"col_name"用于指定列名；"var_name"用于指定要赋值的变量名；"table_expr"表示SELECT语句中FROM后面的部分.

注意：SELECT...INTO语句返回的结果集只能有一行数据.

1.3.7 流程控制语句。

条件判断语句。

if-then-else 语句

 
    ? 
   
         mysql > DELIMITER &&  
        
         mysql >  
         CREATE 
         PROCEDURE 
         proc2( 
         IN 
         parameter  
         int 
         )  
        
         ->  
         begin 
        
         ->  
         declare 
         var  
         int 
         ;  
        
         ->  
         set 
         var=parameter+1;  
        
         -> if var=0  
         then 
        
         ->  
         insert 
         into 
         t  
         values 
         (17);  
        
         ->  
         end 
         if;  
        
         -> if parameter=0  
         then 
        
         ->  
         update 
         t  
         set 
         s1=s1+1;  
        
         ->  
         else 
        
         ->  
         update 
         t  
         set 
         s1=s1+2;  
        
         ->  
         end 
         if;  
        
         ->  
         end 
         ;  
        
         -> &&  
        
         mysql > DELIMITER ;

case语句

 
    ? 
   
         mysql > DELIMITER &&  
        
         mysql >  
         CREATE 
         PROCEDURE 
         proc3 ( 
         in 
         parameter  
         int 
         )  
        
         ->  
         begin 
        
         ->  
         declare 
         var  
         int 
         ;  
        
         ->  
         set 
         var=parameter+1;  
        
         ->  
         case 
         var  
        
         ->  
         when 
         0  
         then 
        
         ->  
         insert 
         into 
         t  
         values 
         (17);  
        
         ->  
         when 
         1  
         then 
        
         ->  
         insert 
         into 
         t  
         values 
         (18);  
        
         ->  
         else 
        
         ->  
         insert 
         into 
         t  
         values 
         (19);  
        
         ->  
         end 
         case 
         ;  
        
         ->  
         end 
         ;  
        
         -> &&  
        
         mysql > DELIMITER ;

循环语句 while ···· end while

 
    ? 
   
         mysql > DELIMITER &&  
        
         mysql >  
         CREATE 
         PROCEDURE 
         proc4()  
        
         ->  
         begin 
        
         ->  
         declare 
         var  
         int 
         ;  
        
         ->  
         set 
         var=0;  
        
         -> while var<6 do  
        
         ->  
         insert 
         into 
         t  
         values 
         (var);  
        
         ->  
         set 
         var=var+1;  
        
         ->  
         end 
         while;  
        
         ->  
         end 
         ;  
        
         -> &&  
        
         mysql > DELIMITER ;

repeat···· end repea:

它在执行操作后检查结果，而 while 则是执行前进行检查.

 
    ? 
   
         mysql > DELIMITER &&  
        
         mysql >  
         CREATE 
         PROCEDURE 
         proc5 ()  
        
         ->  
         begin 
        
         ->  
         declare 
         v  
         int 
         ;  
        
         ->  
         set 
         v=0;  
        
         -> repeat  
        
         ->  
         insert 
         into 
         t  
         values 
         (v);  
        
         ->  
         set 
         v=v+1;  
        
         -> until v>=5  
        
         ->  
         end 
         repeat;  
        
         ->  
         end 
         ;  
        
         -> &&  
        
         mysql > DELIMITER ;

 
    ? 
   
         repeat 
        
         --循环体 
        
         until 循环条件  
        
         end 
         repeat;

loop ·····endloop

loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环.

 
    ? 
   
         mysql > DELIMITER &&  
        
         mysql >  
         CREATE 
         PROCEDURE 
         proc6 ()  
        
         ->  
         begin 
        
         ->  
         declare 
         v  
         int 
         ;  
        
         ->  
         set 
         v=0;  
        
         -> LOOP_LABLE:loop  
        
         ->  
         insert 
         into 
         t  
         values 
         (v);  
        
         ->  
         set 
         v=v+1;  
        
         -> if v >=5  
         then 
        
         -> leave LOOP_LABLE;  
        
         ->  
         end 
         if;  
        
         ->  
         end 
         loop;  
        
         ->  
         end 
         ;  
        
         -> &&  
        
         mysql > DELIMITER ;

ITERATE迭代:

 
    ? 
   
         mysql > DELIMITER &&  
        
         mysql >  
         CREATE 
         PROCEDURE 
         proc10 ()  
        
         ->  
         begin 
        
         ->  
         declare 
         v  
         int 
         ;  
        
         ->  
         set 
         v=0;  
        
         -> LOOP_LABLE:loop  
        
         -> if v=3  
         then 
        
         ->  
         set 
         v=v+1;  
        
         -> ITERATE LOOP_LABLE;  
        
         ->  
         end 
         if;  
        
         ->  
         insert 
         into 
         t  
         values 
         (v);  
        
         ->  
         set 
         v=v+1;  
        
         -> if v>=5  
         then 
        
         -> leave LOOP_LABLE;  
        
         ->  
         end 
         if;  
        
         ->  
         end 
         loop;  
        
         ->  
         end 
         ;  
        
         -> &&  
        
         mysql > DELIMITER ;

1.3.8 游标。

MySQL中的游标可以理解成一个可迭代对象(类比Python中的列表、字典等可迭代对象)，它可以用来存储select 语句查询到的结果集，这个结果集可以包含多行数据，从而使我们可以使用迭代的方法从游标中依次取出每行数据.

MySQL游标的特点： 1.只读：无法通过光标更新基础表中的数据。 2.不可滚动：只能按照select语句确定的顺序获取行。不能以相反的顺序获取行。此外，不能跳过行或跳转到结果集中的特定行。 3.敏感：有两种游标：敏感游标和不敏感游标。敏感游标指向实际数据，不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快，因为它不需要临时拷贝数据。MySQL游标是敏感的.

1.声明游标。

游标声明必须在变量声明之后。如果在变量声明之前声明游标，MySQL将会发出一个错误。游标必须始终与select语句相关联.

 
    ? 
   
         declare 
         cursor_name  
         cursor 
         for 
         select_statement;

2.打开游标。

使用open语句打开游标，只有先打开游标才能读取数据.

 
    ? 
   
         open 
         cursor_name;

3.读取游标。

使用fetch语句来检索游标指向的一行数据，并将游标移动到结果集中的下一行.

 
    ? 
   
         fetch 
         cursor_name  
         into 
         var_name;

4.关闭游标。

使用close语句关闭游标.

 
    ? 
   
         close 
         cursor_name;

当游标不再使用时，应该关闭它。当使用MySQL游标时，还必须声明一个notfound处理程序来处理当游标找不到任何行时的情况。因为每次调用fetch语句时，游标会尝试依次读取结果集中的每一行数据。当游标到达结果集的末尾时，它将无法获得数据，并且会产生一个条件。处理程序用于处理这种情况.

 
    ? 
   
         declare 
         continue 
         handler  
         for 
         not 
         found  
         set 
         type = 1;

type是一个变量，示游标到达结果集的结尾.

 
    ? 
   
         delimiter $$ 
        
         create 
         PROCEDURE 
         phoneDeal() 
        
         BEGIN 
        
         DECLARE 
         id  
         varchar 
         (64);  
         -- id 
        
         DECLARE 
         phone1  
         varchar 
         (16);  
         -- phone 
        
         DECLARE 
         password1  
         varchar 
         (32);  
         -- 密码 
        
         DECLARE 
         name1  
         varchar 
         (64);  
         -- id 
        
         -- 遍历数据结束标志 
        
         DECLARE 
         done  
         INT 
         DEFAULT 
         FALSE 
         ; 
        
         -- 游标 
        
         DECLARE 
         cur_account  
         CURSOR 
         FOR 
         select 
         phone, 
         password 
         , 
         name 
         from 
         account_temp; 
        
         -- 将结束标志绑定到游标 
        
         DECLARE 
         CONTINUE 
         HANDLER  
         FOR 
         NOT 
         FOUND  
         SET 
         done =  
         TRUE 
         ; 
        
         -- 打开游标 
        
         OPEN 
         cur_account;  
        
         -- 遍历 
        
         read_loop: LOOP 
        
         -- 取值 取多个字段 
        
         FETCH 
         NEXT 
         from 
         cur_account  
         INTO 
         phone1,password1,name1; 
        
         IF done  
         THEN 
        
         LEAVE read_loop; 
        
         END 
         IF; 
        
         -- 你自己想做的操作 
        
         insert 
         into 
         account(id,phone, 
         password 
         , 
         name 
         ) value(UUID(),phone1,password1,CONCAT(name1, 
         '的家长' 
         )); 
        
         END 
         LOOP; 
        
         -- 关闭游标 
        
         CLOSE 
         cur_account; 
        
         END 
         $$

1.3.7 调用存储过程。

使用call语句调用存储过程。

 
    ? 
   
         call sp_name[(传参)];

1.3.8 删除存储过程。

使用drop语句删除存储过程。

 
    ? 
   
         DROP 
         PROCEDURE 
         sp_name

2 存储函数。

2.1 什么是存储函数。

存储函数和存储过程一样，都是sql和语句组成的代码块。存储函数不能有输入参数，并且可以直接调用，不需要call语句，且必须有一条包含RETURN语句.

2.2 创建存储函数。

在Mysql中使用CREATE FUNCTION语句创建:

 
    ? 
   
         CREATE 
         FUNCTION 
         fun_name (par_name type[,...]) 
        
         RETURNS 
         type 
        
         [characteristics]  
        
         fun_body

其中，fun_name为函数名，并且名字唯一，不能与存储过程重名。par_name是指定的参数，type为参数类型；RETURNS字句用来声明返回值和返回值类型。fun_body是函数体，所有存储过程中的sql在存储函数中同样可以使用。但是存储函数体中必须包含一个RETURN 语句。 characteristics指定存储过程的特性，有以下取值:

LANGUAGE SQL:说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
[NOT] DETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的，每次执行存储过程时，相同的输入会得到相同的输出，NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出，如果没有指定任意一个值，默认为NOT DETERMINISTIC。
[CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA]：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但不包含读写数据语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表名子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY[DEFINER|INVOKER]：指明谁有权限来执行。DEFINER表示只有定义着才能执行。INVOKER表示用友权限的调用者可以执行。默认情况下，系统指定为DEFINER。
COMMENT 'string'：注释信息，用来描述存储过程或函数。

 
    ? 
   
         delimiter $$ 
        
         create 
         function 
         getAnimalName(animalId  
         int 
         )  
         RETURNS 
         VARCHAR 
         (50) 
        
         DETERMINISTIC 
        
         begin 
        
         declare 
         name 
         VARCHAR 
         (50); 
        
         set 
         name 
         =( 
         select 
         name 
         from 
         animal  
         where 
         id=animalId); 
        
         return 
         ( 
         name 
         ); 
        
         end 
         $$ 
        
         delimiter;

 
    ? 
   
         -- 调用 
        
         select 
         getAnimalName(4)

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我.

原文链接：https://juejin.im/post/5cdc20ede51d453ce71f6113 。

最后此篇关于细谈Mysql的存储过程和存储函数的文章就讲到这里了,如果你想了解更多关于细谈Mysql的存储过程和存储函数的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐： mysql 8.0.15 winx64解压版图文安装教程

文章推荐： shared_ptr线程安全性全面分析

文章推荐：浅析Boost智能指针:scoped_ptr shared_ptr weak_ptr

文章推荐： PHP实现通过正则表达式替换回调的内容标签

详解C语言sscanf()函数、vsscanf()函数、vscanf()函数
C语言sscanf()函数：从字符串中读取指定格式的数据头文件： ?
php - 如何解释at()函数； substr()函数;伪代码中的exist()函数
最近，我有一个关于工作预评估的问题，即使查询了每个功能的工作原理，我也不知道如何解决。这是一个伪代码。下面是一个名为foo()的函数，该函数将被传递一个值并返回一个值。如果将以下值传递给foo函数，
VBS教程：函数-CStr 函数
CStr 函数返回表达式，该表达式已被转换为 String 子类型的 Variant。 CStr(expression) expression 参数是任意有效的表达式。说明通常，可以
VBS教程：函数-CSng 函数
CSng 函数返回表达式，该表达式已被转换为 Single 子类型的 Variant。 CSng(expression) expression 参数是任意有效的表达式。说明通常，可
VBS教程：函数-CreateObject 函数
CreateObject 函数创建并返回对 Automation 对象的引用。 CreateObject(servername.typename [, location]) 参数 serv
VBS教程：函数-Cos 函数
Cos 函数返回某个角的余弦值。 Cos(number) number 参数可以是任何将某个角表示为弧度的有效数值表达式。说明 Cos 函数取某个角并返回直角三角形两边的比值。此比值是
VBS教程：函数-CLng 函数
CLng 函数返回表达式，此表达式已被转换为 Long 子类型的 Variant。 CLng(expression) expression 参数是任意有效的表达式。说明通常，您可以使
VBS教程：函数-CInt 函数
CInt 函数返回表达式，此表达式已被转换为 Integer 子类型的 Variant。 CInt(expression) expression 参数是任意有效的表达式。说明通常，可
VBS教程：函数-Chr 函数
Chr 函数返回与指定的 ANSI 字符代码相对应的字符。 Chr(charcode) charcode 参数是可以标识字符的数字。说明从 0 到 31 的数字表示标准的不可打印的
VBS教程：函数-CDbl 函数
CDbl 函数返回表达式，此表达式已被转换为 Double 子类型的 Variant。 CDbl(expression) expression 参数是任意有效的表达式。说明通常，您可
VBS教程：函数-CDate 函数
CDate 函数返回表达式，此表达式已被转换为 Date 子类型的 Variant。 CDate(date) date 参数是任意有效的日期表达式。说明 IsDate 函数用于判断 d
VBS教程：函数-CCur 函数
CCur 函数返回表达式，此表达式已被转换为 Currency 子类型的 Variant。 CCur(expression) expression 参数是任意有效的表达式。说明通常，
VBS教程：函数-CByte 函数
CByte 函数返回表达式，此表达式已被转换为 Byte 子类型的 Variant。 CByte(expression) expression 参数是任意有效的表达式。说明通常，可以
VBS教程：函数-CBool 函数
CBool 函数返回表达式，此表达式已转换为 Boolean 子类型的 Variant。 CBool(expression) expression 是任意有效的表达式。说明如果 ex
VBS教程：函数-Atn 函数
Atn 函数返回数值的反正切值。 Atn(number) number 参数可以是任意有效的数值表达式。说明 Atn 函数计算直角三角形两个边的比值 (number) 并返回对应角的弧
VBS教程：函数-Asc 函数
Asc 函数返回与字符串的第一个字母对应的 ANSI 字符代码。 Asc(string) string 参数是任意有效的字符串表达式。如果 string 参数未包含字符，则将发生运行时错误。
VBS教程：函数-Array 函数
Array 函数返回包含数组的 Variant。 Array(arglist) arglist 参数是赋给包含在 Variant 中的数组元素的值的列表（用逗号分隔）。如果没有指定此参数，则
VBS教程：函数-Abs 函数
Abs 函数返回数字的绝对值。 Abs(number) number 参数可以是任意有效的数值表达式。如果 number 包含 Null，则返回 Null；如果是未初始化变量，则返回 0。
VBS教程：函数-FormatPercent 函数
FormatPercent 函数返回表达式，此表达式已被格式化为尾随有 % 符号的百分比（乘以 100 ）。 FormatPercent(expression[,NumDigitsAfterD
VBS教程：函数-FormatNumber 函数
FormatNumber 函数返回表达式，此表达式已被格式化为数值。 FormatNumber( expression [,NumDigitsAfterDecimal [,Inc

qq735679552

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

细谈Mysql的存储过程和存储函数