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

2023-08-23 04:36:04 85

1存储过程

1.1什么是存储过程

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

1.2存储过程的优缺点

优点:

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

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

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

缺点:

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

1.3创建存储过程

1.3.1DELIMITER定界符

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

DELIMITER语法格式：

DELIMITER$$

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

DELIMITER;

1.3.2存储过程创建

在Mysql中，使用CREATEPROCEDURE语句来创建存储过程。

CREATEPROCEDUREp_name([proc_parameter[,...]])
routine_body

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

[IN|OUT|INOUT]parame_nametype

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语句来声明局部变量。

DECLAREvar_nametype[DEFAULTvalue]

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

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

1.3.4用户变量

用户变量一般以@开头。

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

1.3.5SET语句

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

SETvar_name=expr[,var_name2=expr]....

1.3.6SELECT....INTO语句

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

SELECTcol_name[,..]INTOvar_name[,....]table_expr

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

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

1.3.7流程控制语句

条件判断语句

if-then-else语句:

mysql>DELIMITER&&
mysql>CREATEPROCEDUREproc2(INparameterint)
->begin
->declarevarint;
->setvar=parameter+1;
->ifvar=0then
->insertintotvalues(17);
->endif;
->ifparameter=0then
->updatetsets1=s1+1;
->else
->updatetsets1=s1+2;
->endif;
->end;
->&&
mysql>DELIMITER;

case语句:

mysql>DELIMITER&&
mysql>CREATEPROCEDUREproc3(inparameterint)
->begin
->declarevarint;
->setvar=parameter+1;
->casevar
->when0then
->insertintotvalues(17);
->when1then
->insertintotvalues(18);
->else
->insertintotvalues(19);
->endcase;
->end;
->&&
mysql>DELIMITER;

循环语句
while····endwhile:

mysql>DELIMITER&&
mysql>CREATEPROCEDUREproc4()
->begin
->declarevarint;
->setvar=0;
->whilevar<6do
->insertintotvalues(var);
->setvar=var+1;
->endwhile;
->end;
->&&
mysql>DELIMITER;

repeat····endrepea：

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

mysql>DELIMITER&&
mysql>CREATEPROCEDUREproc5()
->begin
->declarevint;
->setv=0;
->repeat
->insertintotvalues(v);
->setv=v+1;
->untilv>=5
->endrepeat;
->end;
->&&
mysql>DELIMITER;

repeat
--循环体
until循环条件
endrepeat;

loop·····endloop:

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

mysql>DELIMITER&&
mysql>CREATEPROCEDUREproc6()
->begin
->declarevint;
->setv=0;
->LOOP_LABLE:loop
->insertintotvalues(v);
->setv=v+1;
->ifv>=5then
->leaveLOOP_LABLE;
->endif;
->endloop;
->end;
->&&
mysql>DELIMITER;

ITERATE迭代：

mysql>DELIMITER&&
mysql>CREATEPROCEDUREproc10()
->begin
->declarevint;
->setv=0;
->LOOP_LABLE:loop
->ifv=3then
->setv=v+1;
->ITERATELOOP_LABLE;
->endif;
->insertintotvalues(v);
->setv=v+1;
->ifv>=5then
->leaveLOOP_LABLE;
->endif;
->endloop;
->end;
->&&
mysql>DELIMITER;

1.3.8游标

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

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

1.声明游标

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

declarecursor_namecursorforselect_statement;

2.打开游标

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

opencursor_name;

3.读取游标

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

fetchcursor_nameintovar_name;

4.关闭游标

使用close语句关闭游标。

closecursor_name;

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

declarecontinuehandlerfornotfoundsettype=1;

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

delimiter$$
createPROCEDUREphoneDeal()
BEGIN
DECLAREidvarchar(64);--id
DECLAREphone1varchar(16);--phone
DECLAREpassword1varchar(32);--密码
DECLAREname1varchar(64);--id
--遍历数据结束标志
DECLAREdoneINTDEFAULTFALSE;
--游标
DECLAREcur_accountCURSORFORselectphone,password,namefromaccount_temp;
--将结束标志绑定到游标
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;

--打开游标
OPENcur_account;
--遍历
read_loop:LOOP
--取值取多个字段
FETCHNEXTfromcur_accountINTOphone1,password1,name1;
IFdoneTHEN
LEAVEread_loop;
ENDIF;

--你自己想做的操作
insertintoaccount(id,phone,password,name)value(UUID(),phone1,password1,CONCAT(name1,'的家长'));
ENDLOOP;

--关闭游标
CLOSEcur_account;
END$$

1.3.7调用存储过程

使用call语句调用存储过程

callsp_name[(传参)];

1.3.8删除存储过程

使用drop语句删除存储过程

DROPPROCEDUREsp_name

2存储函数

2.1什么是存储函数

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

2.2创建存储函数

在Mysql中使用CREATEFUNCTION语句创建：

CREATEFUNCTIONfun_name(par_nametype[,...])
RETURNStype
[characteristics]
fun_body

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

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

delimiter$$
createfunctiongetAnimalName(animalIdint)RETURNSVARCHAR(50)
DETERMINISTIC
begin
declarenameVARCHAR(50);
setname=(selectnamefromanimalwhereid=animalId);
return(name);
end$$
delimiter;

--调用
selectgetAnimalName(4)

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

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

热门推荐

随机推荐