mysql储存过程的优缺点和创建使用详细教程

mysql储存过程的优缺点和创建使用详细教程mysql储存过程的优缺点和创建使用详细教程?1234567891011121314151617
18192021222324252627DROPprocedureIFEXISTS`getGameName`;#删除储存过
程?#注意参数名不能与字段名相同DELIMITER$$CREATEPROCEDUREgetGameName(?INgame
idINT,#入参?OUTg_nameVARCHAR(45),#出参?OUTpin_yinVARCHAR(45))
#出参BEGIN?SELECTgamename?INTOg_name?FROMcy_game?WHEREid=game
id;??SELECTpinyin?INTOpin_yin?FROMcy_game?WHEREid=gameid;EN
D$$DELIMITER;?callgetGameName(4,@g_name,@pin_yin);#调用储存过程SELECT
@g_name,@pin_yin;#返回值?SHOWPROCEDURESTATUS;#显示有哪些储存过程?SHOWCRE
ATEPROCEDUREgetGameInfo;#显示指定储存过程的信息，包括代码一.定义存储过程是存储在数据库目录中的一段
声明性SQL语句。触发器，其他存储过程以及Java，Python，PHP等应用程序可以调用存储过程。自身的存储过程称为递归存储过程
。大多数数据库管理系统支持递归存储过程。但是，MySQL不支持它。在MySQL中实现递归存储过程之前，您应该检查MySQL数据
库的版本二.优缺点MySQL是最受欢迎的开源RDBMS，被社区和企业广泛使用。然而，在它发布的第一个十年期间，它不支持存储过程
，存储函数，触发器和事件。自从MySQL5.0版本以来，这些功能被添加到MySQL数据库引擎，使其更加灵活和强大。MySQL存储
过程的优点通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。但是，MySQL实现的存储过程略有
不同。MySQL存储过程按需编译。在编译存储过程之后，MySQL将其放入缓存中。MySQL为每个连接维护自己的存储过程高速缓
存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。存储过程有助于减少应用程序和数据
库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。存储的程序对任何应用程序都是可重用的
和透明的。存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。存储的程序是安全的。数据库管理员
可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。除了这些优点之外，存储过程有其自身的缺点，在数
据库中使用它们之前，您应该注意这些缺点。MySQL存储过程的缺点如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将
会大大增加。此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不当于逻辑运算。存储过程
的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是，MyS
QL不提供调试存储过程的功能。开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这
可能会导致应用程序开发和维护阶段的问题。内存，CPU开销大，复杂逻辑困难，不便调试，开发和维护不易MySQL存储过程有自己的优点和
缺点。开发应用程序时，您应该决定是否应该或不应该根据业务需求使用存储过程。三.创建储存过程，调用储存过程?123456#创建储存
过程DELIMITER//?CREATEPROCEDUREGetAllProducts()?BEGIN?SELECT?
FROMproducts;?END//DELIMITER;?1#调用储存过程CALLSTORED_PROCEDURE_NA
ME();navicat客户端创建：1.打开数据库，左侧目录-》函数-》右击新建函数2.设置参数，入参，出参，出入参3.编
写sql语句，保存，创建完毕四.变量声明变量要在存储过程中声明一个变量，可以使用DECLARE语句，如下所示：?1DECLARE
variable_namedatatype(size)DEFAULTdefault_value;SQL下面来更详细地解释上
面的语句：首先，在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。其次，指定变量的数据类型及其大
小。变量可以有任何MySQL数据类型，如INT，VARCHAR，DATETIME等。第三，当声明一个变量时，它的初始值为NULL。
但是可以使用DEFAULT关键字为变量分配默认值。例如，可以声明一个名为total_sale的变量，数据类型为INT，默认值为0，
如下所示：?1DECLAREtotal_saleINTDEFAULT0;SQLMySQL允许您使用单个DECLARE语句声
明共享相同数据类型的两个或多个变量，如下所示：?1DECLAREx,yINTDEFAULT0;SQL我们声明了两个整数变
量x和y，并将其默认值设置为0。分配变量值当声明了一个变量后，就可以开始使用它了。要为变量分配一个值，可以使用SET语句，例如：?
12DECLAREtotal_countINTDEFAULT0;SETtotal_count=10;SQL上面语句中
，分配total_count变量的值为10。除了SET语句之外，还可以使用SELECTINTO语句将查询的结果分配给一个变量。
请参阅以下示例：?1234DECLAREtotal_productsINTDEFAULT0?SELECTCOUNT()
INTOtotal_productsFROMproductsSQL在上面的例子中：首先，声明一个名为total_produc
ts的变量，并将其值初始化为0。然后，使用SELECTINTO语句来分配值给total_products变量，从示例数据库(yi
ibaidb)中的products表中选择的产品数量。变量范围(作用域)一个变量有自己的范围(作用域)，它用来定义它的生命周期。
如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问。如果您在BEGINEN
D块内声明一个变量，那么如果达到END，它将超出范围。可以在不同的作用域中声明具有相同名称的两个或多个变量，因为变量仅在自己的作
用域中有效。但是，在不同范围内声明具有相同名称的变量不是很好的编程习惯。以@符号开头的变量是会话变量。直到会话结束前它可用和可访
问。在本教程中，我们向您展示了如何在存储过程中声明变量，并讨论了变量的范围(作用域)。五.存储过程参数MySQL存储过程参数示例
1.IN参数示例以下示例说明如何使用GetOfficeByCountry存储过程中的IN参数来查询选择位于特定国家/地区的办公室。
?12345678910111213USE`yiibaidb`;DROPprocedureIFEXISTS`GetOff
iceByCountry`;?DELIMITER$$USE`yiibaidb`$$CREATEPROCEDUREGetOf
ficeByCountry(INcountryNameVARCHAR(255))?BEGIN?SELECT?FROMo
ffices?WHEREcountry=countryName;?END$$?DELIMITER;SQLcountryNa
me是存储过程的IN参数。在存储过程中，我们查询位于countryName参数指定的国家/地区的所有办公室。假设我们想要查询在美国
(USA)的所有办事处，我们只需要将一个值(USA)传递给存储过程，如下所示：?1CALLGetOfficeByCountry(
''USA'');SQL执行上面查询语句，得到以下结果-要在法国获得所有办事处，我们将France字符串传递给GetOfficeBy
Country存储过程，如下所示：?1CALLGetOfficeByCountry(''France'')SQL2.OUT参数示例以
下存储过程通过订单状态返回订单数量。它有两个参数：orderStatus：IN参数，它是要对订单计数的订单状态。total：存储指
定订单状态的订单数量的OUT参数。以下是CountOrderByStatus存储过程的源代码。?12345678910111213
14USE`yiibaidb`;DROPprocedureIFEXISTS`CountOrderByStatus`;?D
ELIMITER$$CREATEPROCEDURECountOrderByStatus(?INorderStatusVA
RCHAR(25),?OUTtotalINT)BEGIN?SELECTcount(orderNumber)?INTOtot
al?FROMorders?WHEREstatus=orderStatus;END$$DELIMITER;SQL要获取发
货订单的数量，我们调用CountOrderByStatus存储过程，并将订单状态传递为已发货，并传递参数(@total)以获取返回
值。?12CALLCountOrderByStatus(''Shipped'',@total);SELECT@total;SQL执
行上面查询语句后，得到以下结果-?123456+--------+|@total|+--------+|???303|+
--------+1rowinsetSQL要获取正在处理的订单数量，调用CountOrderByStatus存储过程，如下所
示：执行上面查询语句后，得到以下结果-?123456+------------------+|total_in_process
|+------------------+|???????????????7|+------------------+1r
owinsetSQLINOUT参数示例以下示例演示如何在存储过程中使用INOUT参数。如下查询语句-?123456DELIM
ITER$$CREATEPROCEDUREset_counter(INOUTcountINT(4),INincINT
(4))BEGIN?SETcount=count+inc;END$$DELIMITER;SQL上面查询语句是如何运行的
？set_counter存储过程接受一个INOUT参数(count)和一个IN参数(inc)。在存储过程中，通过inc参数的值增加
计数器(count)。下面来看看如何调用set_counter存储过程：?12345SET@counter=1;CALLs
et_counter(@counter,1);--2CALLset_counter(@counter,1);--3CAL
Lset_counter(@counter,5);--8SELECT@counter;--8六.返回多个值的存储过程
示例我们来看看示例数据库(yiibaidb)中的orders表。?12345678910111213mysql>descord
ers;+----------------+-------------+------+-----+---------+------
-+|Field?????????|Type???????|Null|Key|Default|Extra|
+----------------+-------------+------+-----+---------+-------+|
orderNumber???|int(11)????|NO|PRI|NULL|??????||orderDa
te?????|date|NO|????|NULL|??????||requiredDate??|date
|NO|????|NULL|??????||shippedDate???|date|YES?|????
|NULL|??????||status????????|varchar(15)|NO|????|NULL
|??????||comments??????|text???????|YES?|????|NULL|????
??||customerNumber|int(11)????|NO|MUL|NULL|??????|+--
--------------+-------------+------+-----+---------+-------+7row
sinsetSQL以下存储过程接受客户编号，并返回发货(shipped)，取消(canceled)，解决(resolved)和
争议(disputed)的订单总数。?1234567891011121314151617181920212223242526272
8293031323334353637383940414243444546DELIMITER$$?CREATEPROCEDUR
Eget_order_by_cust(?INcust_noINT,?OUTshippedINT,?OUTcancele
dINT,?OUTresolvedINT,?OUTdisputedINT)BEGIN?--shipped?SELECT
?count()INTOshipped?FROM?orders?WHERE?customerNumber=cust_no
?ANDstatus=''Shipped'';??--canceled?SELECT?count()INTOcancel
ed?FROM?orders?WHERE?customerNumber=cust_no?ANDstatus=''Cance
led'';??--resolved?SELECT?count()INTOresolved?FROM?orders?WHER
E?customerNumber=cust_no?ANDstatus=''Resolved'';??--disputed?
SELECT?count()INTOdisputed?FROM?orders?WHERE?customerNumber=
cust_no?ANDstatus=''Disputed'';?ENDSQL除IN参数之外，存储过程还需要4个额外的OUT参数：
shipped,canceled,resolved和disputed。在存储过程中，使用带有COUNT函数的SELECT语句根据
订单状态获取相应的订单总数，并将其分配给相应的参数。要使用get_order_by_cust存储过程，可以传递客户编号和四个用户定
义的变量来获取输出值。执行存储过程后，使用SELECT语句输出变量值。?123456+----------+-----------
+-----------+-----------+|@shipped|@canceled|@resolved|@di
sputed|+----------+-----------+-----------+-----------+|??????2
2|????????0|????????1|????????1|+----------+-----------+--
---------+-----------+1rowinsetSQL从PHP调用返回多个值的存储过程以下代码片段显示如何从P
HP程序中调用返回多个值的存储过程。?1234567891011121314151617181920212223242526272
8293031323334prepare($sql);??$stmt->bindParam('':no'',$custo
merNumber,PDO::PARAM_INT);?$stmt->execute();?$stmt->closeCursor(
);??//executethesecondquerytogetvaluesfromOUTparameter?
$r=$pdo->query("SELECT@shipped,@canceled,@resolved,@disputed")
?->fetch(PDO::FETCH_ASSOC);?if($r){?printf(''Shipped:%d,Cancel
ed:%d,Resolved:%d,Disputed:%d'',?$r[''@shipped''],?$r[''@cancele
d''],?$r[''@resolved''],?$r[''@disputed'']);?}?}catch(PDOException$
pe){?die("Erroroccurred:".$pe->getMessage());?}}?call_sp(141)
;PHP在@符号之前的用户定义的变量与数据库连接相关联，因此它们可用于在调用之间进行访问。七.循环语句MySQL提供循环语句，允许
您根据条件重复执行一个SQL代码块。MySQL中有三个循环语句：WHILE，REPEAT和LOOP。我们将在以下部分中更详细地检
查每个循环语句。WHILE循环WHILE语句的语法如下：?123WHILEexpressionDO?statementsEND
WHILESQLWHILE循环在每次迭代开始时检查表达式。如果expressionevaluates为TRUE，MySQL将执
行WHILE和ENDWHILE之间的语句，直到expressionevaluates为FALSE。WHILE循环称为预先测试条件
循环，因为它总是在执行前检查语句的表达式。下面的流程图说明了WHILE循环语句：以下是在存储过程中使用WHILE循环语句的示例：
?123456789101112131415161718DELIMITER$$?DROPPROCEDUREIFEXISTS
test_mysql_while_loop$$?CREATEPROCEDUREtest_mysql_while_loop()
?BEGIN?DECLAREx?INT;?DECLAREstr?VARCHAR(255);??SETx=1;?SET
str=?'''';??WHILEx?<=5DO?SETstr=CONCAT(str,x,'','');?SETx
=x+1;?ENDWHILE;??SELECTstr;?END$$DELIMITER;SQL在上面的test_mys
ql_while_loop存储过程中：首先，重复构建str字符串，直到x变量的值大于5。然后，使用SELECT语句显示最终的字符串
。要注意，如果不初始化x变量的值，那么它默认值为NULL。因此，WHILE循环语句中的条件始终为TRUE，并且您将有一个不确定的
循环，这是不可预料的。下面来测试test_mysql_while_loopstored调用存储过程：?1CALLtest_mys
ql_while_loop();SQL执行上面查询语句，得到以下结果-?123456789mysql>CALLtest_my
sql_while_loop();+------------+|str???????|+------------+|1,2,
3,4,5,|+------------+1rowinset?QueryOK,0rowsaffectedSQLRE
PEAT循环REPEAT循环语句的语法如下：?1234REPEAT?statements;UNTILexpressionEND
REPEATSQL首先，MySQL执行语句，然后评估求值表达式(expression)。如果表达式(expression)的计算结
果为FALSE，则MySQL将重复执行该语句，直到该表达式计算结果为TRUE。因为REPEAT循环语句在执行语句后检查表达式(ex
pression)，因此REPEAT循环语句也称为测试后循环。下面的流程图说明了REPEAT循环语句的执行过程：我们可以使用RE
PEAT循环语句重写test_mysql_while_loop存储过程，使用WHILE循环语句：?1234567891011121
3141516171819DELIMITER$$?DROPPROCEDUREIFEXISTSmysql_test_rep
eat_loop$$?CREATEPROCEDUREmysql_test_repeat_loop()?BEGIN?DECLAR
ExINT;?DECLAREstrVARCHAR(255);??SETx=1;?SETstr=?'''';??RE
PEAT?SETstr=CONCAT(str,x,'','');?SETx=x+1;?UNTILx?>5?EN
DREPEAT;??SELECTstr;?END$$DELIMITER;SQL要注意的是UNTIL表达式中没有分号(;)。执
行上面查询语句，得到以下结果-?123456789mysql>CALLmysql_test_repeat_loop();+-
-----------+|str???????|+------------+|1,2,3,4,5,|+----------
--+1rowinset?QueryOK,0rowsaffectedSQLLOOP，LEAVE和ITERATE语句有
两个语句允许您用于控制循环：LEAVE语句用于立即退出循环，而无需等待检查条件。LEAVE语句的工作原理就类似PHP，C/C++，
Java等其他语言的break语句一样。ITERATE语句允许您跳过剩下的整个代码并开始新的迭代。ITERATE语句类似于PHP，
C/C++，Java等中的continue语句。MySQL还有一个LOOP语句，它可以反复执行一个代码块，另外还有一个使用循环标签
的灵活性。以下是使用LOOP循环语句的示例。?12345678910111213141516171819202122CREATE
PROCEDUREtest_mysql_loop()?BEGIN?DECLAREx?INT;?DECLAREstr?VA
RCHAR(255);??SETx=1;?SETstr=?'''';??loop_label:?LOOP?IF?x>
10THEN?LEAVE?loop_label;?ENDIF;??SETx=x+1;?IF(xmod2)
THEN?ITERATE?loop_label;?ELSE?SETstr=CONCAT(str,x,'','');?ENDI
F;?ENDLOOP;?SELECTstr;END;SQL以上存储过程仅构造具有偶数字符串的字符串，例如2,4,6等。在LOO
P语句之前放置一个loop_label循环标签。如果x的值大于10，则由于LEAVE语句，循环被终止。如果x的值是一个奇数，ITE
RATE语句忽略它下面的所有内容，并开始一个新的迭代。如果x的值是偶数，则ELSE语句中的块将使用偶数构建字符串。执行上面查询语句
，得到以下结果-?123456789mysql>CALLtest_mysql_loop();+-------------+|
str????????|+-------------+|2,4,6,8,10,|+-------------+1row
inset?QueryOK,0rowsaffected天地棋牌http://www.dadiqipaigw.cnSQ
L八.MySQL游标简介要处理存储过程中的结果集，请使用游标。游标允许您迭代查询返回的一组行，并相应地处理每行。MySQL游标为只
读，不可滚动和敏感。只读：无法通过光标更新基础表中的数据。不可滚动：只能按照SELECT语句确定的顺序获取行。不能以相反的顺序获取
行。此外，不能跳过行或跳转到结果集中的特定行。敏感：有两种游标：敏感游标和不敏感游标。敏感游标指向实际数据，不敏感游标使用数据的
临时副本。敏感游标比一个不敏感的游标执行得更快，因为它不需要临时拷贝数据。但是，对其他连接的数据所做的任何更改都将影响由敏感游标使
用的数据，因此，如果不更新敏感游标所使用的数据，则更安全。MySQL游标是敏感的。您可以在存储过程，存储函数和触发器中使用MyS
QL游标。使用MySQL游标首先，必须使用DECLARE语句声明游标：?1DECLAREcursor_nameCURSORF
ORSELECT_statement;SQL游标声明必须在变量声明之后。如果在变量声明之前声明游标，MySQL将会发出一个错误。
游标必须始终与SELECT语句相关联。接下来，使用OPEN语句打开游标。OPEN语句初始化游标的结果集，因此您必须在从结果集中提取
行之前调用OPEN语句。?1OPENcursor_name;SQL然后，使用FETCH语句来检索光标指向的下一行，并将光标移动到
结果集中的下一行。?1FETCHcursor_nameINTOvariableslist;SQL之后，可以检查是否有任何行
记录可用，然后再提取它。最后，调用CLOSE语句来停用光标并释放与之关联的内存，如下所示：?1CLOSEcursor_name;
SQL当光标不再使用时，应该关闭它。当使用MySQL游标时，还必须声明一个NOTFOUND处理程序来处理当游标找不到任何行时的情
况。因为每次调用FETCH语句时，游标会尝试读取结果集中的下一行。当光标到达结果集的末尾时，它将无法获得数据，并且会产生一个条
件。处理程序用于处理这种情况。要声明一个NOTFOUND处理程序，参考以下语法：?1DECLARECONTINUEHAND
LERFORNOTFOUNDSETfinished=1;SQLfinished是一个变量，指示光标到达结果集的结尾。
请注意，处理程序声明必须出现在存储过程中的变量和游标声明之后。下图说明了MySQL游标如何工作。MySQL游标示例为了更好地演示，
我们将开发一个存储过程，来获取MySQL示例数据库(yiibaidb)中employees表中所有员工的电子邮件列表。首先，声明一
些变量，一个用于循环员工电子邮件的游标和一个NOTFOUND处理程序：?12345678910DECLAREfinished
INTEGERDEFAULT0;DECLAREemailvarchar(255)DEFAULT"";?--decla
recursorforemployeeemailDEClAREemail_cursorCURSORFOR?SELEC
TemailFROMemployees;?--declareNOTFOUNDhandlerDECLARECONTI
NUEHANDLERFORNOTFOUNDSETfinished=1;SQL接下来，使用OPEN语句打开email
_cursor：?1OPENemail_cursor;SQL然后，迭代电子邮件列表，并使用分隔符(;)连接每个电子邮件：?123
45678get_email:LOOP?FETCHemail_cursorINTOv_email;?IFv_finish
ed=1THEN?LEAVEget_email;?ENDIF;?--buildemaillist?SETemai
l_list=CONCAT(v_email,";",email_list);ENDLOOPget_email;SQL之后，
在循环中，使用v_finished变量来检查列表中是否有任何电子邮件来终止循环。最后，使用CLOSE语句关闭游标：?1CLOSE
email_cursor;SQLbuild_email_list存储过程所有代码如下：?123456789101112131415
161718192021222324252627282930313233343536DELIMITER$$?CREATEPRO
CEDUREbuild_email_list(INOUTemail_listvarchar(4000))BEGIN??DE
CLAREv_finishedINTEGERDEFAULT0;?DECLAREv_emailvarchar(100)
DEFAULT"";??--declarecursorforemployeeemail棋牌评测网http://w
ww.77884.net?DEClAREemail_cursorCURSORFOR?SELECTemailFROMem
ployees;??--declareNOTFOUNDhandler?DECLARECONTINUEHANDLER?
FORNOTFOUNDSETv_finished=1;??OPENemail_cursor;??get_email:
LOOP??FETCHemail_cursorINTOv_email;??IFv_finished=1THEN?L
EAVEget_email;?ENDIF;??--buildemaillist?SETemail_list=CON
CAT(v_email,";",email_list);??ENDLOOPget_email;??CLOSEemail_cursor;?END$$?DELIMITER;SQL可以使用以下脚本测试build_email_list存储过程：?123SET@email_list="";CALLbuild_email_list(@email_list);SELECT@email_list;SQL注：由于内容比较长，这里就不放上输出结果了。九.显示储存过程显示存储过程字符要显示存储过程的字符，请使用SHOWPROCEDURESTATUS语句如下：?1SHOWPROCEDURESTATUS[LIKE''pattern''|WHEREexpr];SQLSHOWPROCEDURESTATUS语句是对SQL标准的MySQL扩展。此语句提供存储过程的字符，包括数据库，存储过程名称，类型，创建者等。可以使用LIKE或WHERE子句根据各种标准过滤出存储过程。要列出您有权访问的数据库的所有存储过程，请使用SHOWPROCEDURESTATUS语句，如下所示：?1SHOWPROCEDURESTATUS;捕鱼游戏http://www.44771.netSQL如果要在特定数据库中显示存储过程，可以在SHOWPROCEDURESTATUS语句中使用WHERE子句：?1SHOWPROCEDURESTATUSWHEREdb=''yiibaidb'';SQL如果要显示具有特定模式的存储过程，例如，名称包含product字符，则可以使用LIKE操作符，如以下命令：?1SHOWPROCEDURESTATUSWHEREnameLIKE''%product%''SQL显示存储过程的源代码要显示特定存储过程的源代码，请使用SHOWCREATEPROCEDURE语句如下：?1SHOWCREATEPROCEDUREstored_procedure_nameSQL在SHOWCREATEPROCEDURE关键字之后指定存储过程的名称。例如，要显示GetAllProducts存储过程的代码，请使用以下语句：?1SHOWCREATEPROCEDUREGetAllProducts;