mysql存储过程总结

《mysql存储过程总结》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、1，前提需要MySQL52，HelloWorldMySQL存储过程之HelloWorld案例DELIMITER$$2.3.DROPPROCEDUREIFEXISTSHelloWorld$$4.CREATEPROCEDUREHelloWorld()5.BEGIN6.SELECT"HelloWorld!";7.END$$8.9.DELIMITER;3，变量使用DECLARE来声明，DEFAULT赋默认值，SET赋值案例1.DECLAREcounterINTDEFAULT0;2.SETcounter=counter+1;SELECTINTO查询结果赋值案例1.DECL

2、AREtotal_salesNUMERIC(8,2);2.SELECTSUM(sale_value)INTOtotal_salesFROMsalesWHEREcustomer_id=in_customer_id;4，参数IN为默认类型，值必须在调用时指定，值不能返回（值传递）OUT值可以返回（指针传递）INOUT值必须在调用时指定，值可以返回案例1.CREATEPROCEDUREtest(aINT,OUTbFLOAT,INOUTcINT)5，条件判断IFTHEN、ELSEIF、ELSE、ENDIF案例1.DELIMITER$$2.3.DROPPROCEDUREI

3、FEXISTSdiscounted_price$$4.CREATEPROCEDUREdiscunted_price(normal_priceNUMERIC(8,2),OUTdiscount_priceNUMERIC(8,2))5.BEGIN6.IF(normal_price>500)THEN7.SETdiscount_price=normal_price*.8;8.ELSEIF(normal_price>100)THEN9.SETdiscount_price=normal_price*.9;10.ELSE11.SETdiscount_price=normal_p

4、rice;12.ENDIF;13.END$$14.15.DELIMITER;6，循环LOOP、ENDLOOP案例1.DELIMITER$$2.3.DROPPROCEDUREIFEXISTSsimple_loop$$4.5.CREATEPROCEDUREsimple_loop(OUTcounterINT)6.BEGIN7.SETcounter=0;8.my_simple_loop:LOOP9.SETcounter=counter+1;10.IFcounter=10THEN11.LEAVEmy_simple_loop;12.ENDIF;13.ENDLOOPmy_si

5、mple_loop;14.END$$15.16.DELIMITER;WHILEDO、ENDWHILE案例1.DELIMITER$$2.3.DROPPROCEDUREIFEXISTSsimple_while$$4.5.CREATEPROCEDUREsimple_while(OUTcounterINT)6.BEGIN7.SETcounter=0;8.WHILEcounter!=10DO9.SETcounter=counter+1;10.ENDWHILE;11.END$$12.13.DELIMITER;REPEAT、UNTILL案例1.DELIMITER$$2.3.D

6、ROPPROCEDUREIFEXISTSsimple_repeat$$4.5.CREATEPROCEDUREsimple_repeat(OUTcounterINT)6.BEGIN7.SETcounter=0;8.REPEAT9.SETcounter=counter+1;10.UNTILcounter=10ENDREPEAT;11.END$$12.13.DELIMITER;7，异常处理如果用cursor获取SELECT语句返回的所有结果集时应该定义NOTFOUNDerrorhandler来防止存储程序提前终结如果SQL语句可能返回constraintviolati

7、on等错误时应该创建一个handler来防止程序终结8，数据库交互INTO用于存储单行记录的查询结果案例1.DECLAREtotal_salesNUMERIC(8,2);2.SELECTSUM(sale_value)INTOtotal_salesFROMsalesWHEREcustomer_id=in_customer_id;CURSOR用于处理多行记录的查询结果案例1.DELIMITER$$2.3.DROPPROCEDUREIFEXITScursor_example$$4.CREATEPROCEDUREcursor_example()5.READSSQLDAT

8、A6.BEGIN7.DE