mysql函数、存储过程、触发器、游标

《mysql函数、存储过程、触发器、游标》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、mysql函数、存储过程、触发器、游标mysql函数、存储过程、触发器、游标2010-05-2112：021.procedure(1)存储过程&游标mysqlDELIMITER//mysqlDROPPROCEDUREIFEXISTS'oh_qty'///*如果存在同名存储过程则删除*/mysqlCREATEPROCEDUREoh_qty()/*创建存储过程*/-BEGIN-DECLAREplantCHAR(4)；-DECLARElocCHAR(4)；-DECLAREpartCHAR(15)；-DECLAREonhandDECIMAL(15,4)；-DECL

2、AREfinishedINTEGERDEFAULT0；-DECLAREoh_curCURSORFOR-SELECTinv_plant,inv_loc,inv_part,inv_oh_qtyFROMinv；-/*声明游标*/-DECLARECONTINUEHANDLERFORNOTFOUNDSETfinished=1；-/*如果没有找到数据则finished=1*/-OPENoh_cur；-/*打开游标*/-calcloop：-LOOP-FETCHoh_curINTOplant,loc,part,onhand；-/*fetch命令获取游标结果集并保存到变量中

3、*/-IFfinished=1THEN-LEAVEcalcloop；-ENDIF；-/*执行leave命令立即退出循环或BEGIN/END块.*/-IFplant='SJ01'THEN-ITERATEcalcloop；-ENDIF；-/*执行iterate语句将使嵌入该语句的LOOP,REPEAT,或WHILE循环返回到顶部，并再次执行.此处将去除plant='SJ01'的数据.*/-UPDATEinvSETinv_oh_qty=inv_oh_qty*10-WHEREinv_plant=plantANDinv_loc=locANDinv_part=part

4、；-/*此处对数据集进行处理*/-ENDLOOPcalcloop；/*loop循环结束*/-CLOSEoh_cur；/*关闭游标*/-END///*存储过程结束*/QueryOK,0rowsaffected(0.00sec)mysqlDELIMITER；mysqlCALLoh_qty()；/*执行存储过程*/QueryOK,1rowaffected(0.02sec)(2)Procedure：mysqlDELIMITER//mysqlCREATEPROCEDUREget_inventory(OUTinvINT)-SELECT45INTOinv；-//mysq

5、lCALLget_inventory(@inv)；mysqlSELECT@inv；/*如果希望在存储过程外使用变量，需要将其作为OUT变量传递*/返回结果：45(3)IF语句IFyears_employed5THENSETbonus=total*.05；ELSEIFyears_employed=5andyears_employed10THENSETbonus=total*.06；ELSEIFyears_employed=10THENSETbonus=total*.07；ENDIF(4)CASE语句CASEWHENstate="AL"THENSETtax_r

6、ate=.04；WHENstate="AK"THENSETtax_rate=.00；ENDCASE；或：CASEstateWHEN"AL"THENSETtax_rate=.04；WHEN"AK"THENSETtax_rate=.00；ENDCASE；(5)REPEAT语句CREATEPROCEDUREtest_date(rowsINT)BEGINDECLAREval1FLOAT；DECLAREval2FLOAT；REPEATSELECTRAND()INTOval1；SELECTRAND()INTOval2；INSERTINTOanalysisVALUES(

7、NULL,val1,val2)；SETrows=rows-1；UNTILrows=0ENDREPEAT；END///*此存储过程将随机产生五条记录插入到analysis表中*/(6)WHILE语句CREATEPROCEDUREtest_date(rowsINT)BEGINDECLAREval1FLOAT；DECLAREval2FLOAT；WHILErows0DOSELECTRAND()INTOval1；SELECTRAND()INTOval2；INSERTINTOanalysisVALUES(NULL,val1,val2)；SETrows=rows-1；E

8、NDWHILE；END///*执行结果同上*/mysqlSHOWP