Mysql procedure原理、语法 、实例

Mysql储存过程是一组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，当需要使用该组SQL语句时用户只需要通过指定储存过程的名字并给定参数就可以调用执行它了。 业务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高。

一、Mysql储存过程简介：
储存过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。存储过程通常有以下优点：

1)存储过程能实现较快的执行速度。
如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

2)存储过程允许标准组件是编程。
存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

3)存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

4)存储过程可被作为一种安全机制来充分利用。
系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

5)存储过程能过减少网络流量。
针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

二、存储程序中的变量
1）DECLARE局部变量
DECLARE var_name[,...] type [DEFAULT value]
这个语句被用来声明局部变量。
要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。 局部变量的作用范围在它被声明的BEGIN ... END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。
2）变量SET语句
SET var_name = expr [, var_name = expr] 
在存储程序中的SET语句是一般SET语句的扩展版本。
被参考变量可能是子程序内声明的变量，或者是全局服务器变量。 在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。其中不同的变量类型（局域声明变量及全局和集体变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。
3）SELECT ... INTO 语句
SELECT col_name[,...] INTO var_name[,...] table_expr
这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。 SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

三、下面来看一个mysql 存储过程的完整实例。

BEGIN 

DECLARE customerId Long;#客户ID 
DECLARE totalBuy LONG;#当前购买产品数 
DECLARE cumTotalBuy LONG;#累计购买产品数 
DECLARE redemptionPro LONG;#已到期赎回产品数 
DECLARE expiredPro LONG;#当天到期产品数 
DECLARE witAmount double(18,6) DEFAULT 0;#申请提现金额 
  DECLARE assetsid long; 

## DECLARE reqredeemAmount double(18,6) ;#申请赎回金额 
DECLARE reqredeemPro LONG;#申请赎回数量 
DECLARE done INT DEFAULT 0;#游标执行标识符 
DECLARE buyRecordId LONG;#客户购买记录ID 
DECLARE v_err_op VARCHAR(1); #异常信息标识符 
DECLARE v_err_msg VARCHAR(255); #错误信息
DECLARE cur_buyRecord  cursor  for  select customer.id,customer.BUY_RECORD_ID,ASSETS_ID from T_CUSTOM customer,T_BIND_BANK custbank  
                                            where customer.BIND_BANK_ID  = custbank.ID ; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 


open cur_buyRecord; 

loop_curBuyRecord:loop 

FETCH cur_buyRecord INTO customerId,buyRecordId,assetsid; 


if done=1 then  

leave loop_curBuyRecord;  
end if; 
##########################################当前购买产品数########################################################### 

SELECT IFNULL(COUNT(*),0) INTO totalBuy FROM T_TRAN_RECORDS WHERE TRADE_TYPE='CUSTOMBOUGHY' AND CUSTOM_ID=customerId; 

##########################################累计购买产品数########################################################### 

SELECT IFNULL(COUNT(*),0) INTO cumTotalBuy FROM T_TRAN_RECORDS WHERE ( TRADE_TYPE='CUSTOMBOUGHY' OR  TRADE_TYPE='CUSTOMERREQREDEEM' or  TRADE_TYPE='CUSTOMERREDEEM') AND CUSTOM_ID=customerId; 

##########################################已到期赎回产品数########################################################### 

SELECT IFNULL(COUNT(*),0) INTO redemptionPro FROM T_TRAN_RECORDS WHERE TRADE_TYPE='CUSTOMERREDEEM' AND CUSTOM_ID=customerId; 

##########################################当天到期产品数########################################################### 

SELECT IFNULL(COUNT(*),0) INTO expiredPro FROM T_TRAN_RECORDS tran,T_PRODUCT p WHERE p.PROD_TYPE_ID = 1 and p.DEADLIME=To_Days(NOW())-To_Days(tran.OCC_DATE) and tran.PRODUCT_ID=p.ID and tran.CUSTOM_ID =customerId and tran.TRADE_TYPE='CUSTOMBOUGHY'; 

##########################################申请提现金额############################################################# 

SELECT SUM(IFNULL(TRADE_AMOUNT,0)) INTO witAmount FROM T_TRAN_RECORDS WHERE  TRADE_TYPE='CUSTOMERREQWITHDRAW' AND CUSTOM_ID=customerId  and ( WITHDRAWFLAG = '0'  or WITHDRAWFLAG is null ) ; 

                        ##########################################申请赎回金额##################################################### 

##SELECT SUM(IFNULL(TRADE_AMOUNT,0)) INTO reqredeemAmount FROM T_TRAN_RECORDS WHERE  TRADE_TYPE='CUSTOMERREQREDEEM' AND CUSTOM_ID=customerId; 

##########################################申请赎回数量########################################## 

SELECT IFNULL(COUNT(*),0) INTO reqredeemPro FROM T_TRAN_RECORDS WHERE TRADE_TYPE='CUSTOMERREQREDEEM' AND CUSTOM_ID=customerId; 


###################################################witAmount如果交易表中状态是提现 ，那么  资材表的 闲置金额 以及总资产 减去 提现金额 
  if  witAmount > 0 then 
update  T_CUSTOM_ASSETS set TOTAL_AMOUNT = TOTAL_AMOUNT - witAmount ,ASSETS_AMOUNT = ASSETS_AMOUNT - witAmount 
  where     T_CUSTOM_ASSETS.ID = assetsid ; 

  update  T_TRAN_RECORDS  set T_TRAN_RECORDS.WITHDRAWFLAG = '1'   WHERE  T_TRAN_RECORDS.TRADE_TYPE='CUSTOMERREQWITHDRAW' AND T_TRAN_RECORDS.CUSTOM_ID=customerId; 

  end if; 



###################################################### 

#验证用户购买记录信息是否存在 

if ISNULL( expiredPro) THEN 
SET expiredPro=0; 

END IF; 

if ISNULL( witAmount) THEN 
SET witAmount=0; 
END IF; 
/* 
if ISNULL( reqredeemAmount) THEN 
SET reqredeemAmount=0; 
END IF; 
*/ 

if ISNULL(buyRecordId) THEN 

set v_err_msg = 'insert'; 

INSERT into T_CUSTOM_BUY_RECORD(TOTAL_BUY,CUM_TOTAL_BUY,REDEMPTION_PRO,EXPIRED_PRO,WIT_AMOUNT,REQREDEEM_PRO,UPDATE_DATE)
VALUES(totalBuy,cumTotalBuy,redemptionPro,expiredPro,witAmount,reqredeemPro,NOW()); 



SELECT max(id) into buyRecordId from T_CUSTOM_BUY_RECORD; 

UPDATE T_CUSTOM SET BUY_RECORD_ID=buyRecordId where ID = customerId; 

ELSE 

set v_err_msg = 'update'; 

UPDATE T_CUSTOM_BUY_RECORD set TOTAL_BUY=totalBuy, 
CUM_TOTAL_BUY=cumTotalBuy, 
REDEMPTION_PRO=redemptionPro, 
EXPIRED_PRO=expiredPro, 
WIT_AMOUNT=witAmount, 
REQREDEEM_PRO=reqredeemPro, 
UPDATE_DATE=NOW() 
where ID=buyRecordId; 
END IF; 

commit; 
END  loop; 

CLOSE cur_buyRecord; 

END

写一个定时器调用这个存储过程，定时执行运算，就可以达到想要的效果了。