MySql一個存儲過程的例子 MySql 存儲過程簡單示例： Sql代碼 www.2cto.com CREATE DEFINER=`root`@`%` PROCEDURE `proc_calculate_combo_price`(IN `comboId` VARCHAR(36), OUT `price` DOUBLE, OUT `reallyPrice` DOUBLE) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '計算服務套餐的價格' BEGIN declare minPeriodType varchar(36); declare minPeriodNum INT; declare fetchOk INT; declare comboPeriodNum INT; declare discountRate DOUBLE; declare discountPrice DOUBLE; declare billUnitsId varchar(36); declare serviceDetailId varchar(36); declare value INT; declare unitsPrice INT; declare unitsPeriod varchar(36); declare unitsPeriodNum INT; declare optionsFlag INT; declare minValue INT; declare stepLength INT; declare setpPrice DOUBLE; declare combo cursor for select combo.min_period_type_id,combo.min_period_num,combo.discount_rate,combo.discount_price from srv_service_combo combo where combo.id=comboId; declare comboDetail cursor for select detail.options_value,detail.value,detail.service_detail_id from srv_service_combo_detail detail where detail.delete_flag=0; declare continue handler for NOT FOUND set fetchOk = 1; set fetchOk = 0; open combo; combo_loop: loop FETCH combo INTO minPeriodType, minPeriodNum,discountRate,discountPrice; IF fetchOk=1 THEN LEAVE combo_loop; END IF; end LOOP combo_loop; close combo; select period.days_number into comboPeriodNum from srv_period_type period where period.id=minPeriodType; set fetchOk = 0; set price=0; open comboDetail; detail_loop: loop FETCH comboDetail INTO billUnitsId, value,serviceDetailId; IF fetchOk=1 THEN LEAVE detail_loop; ELSE select units.price into unitsPrice from srv_bill_units units where units.id=billUnitsId; select units.period_type_id into unitsPeriod from srv_bill_units units where units.id=billUnitsId; select period.days_number into unitsPeriodNum from srv_period_type period where period.id=unitsPeriod; select sd.options_flag into optionsFlag from srv_service_detail sd where sd.id=serviceDetailId; IF optionsFlag=1 then set price=price+unitsPrice*(comboPeriodNum/unitsPeriodNum); ELSE select sd.min_value into minValue from srv_service_detail sd where sd.id=serviceDetailId; select sd.step_length into stepLength from srv_service_detail sd where sd.id=serviceDetailId; select sd.step_price into setpPrice from srv_service_detail sd where sd.id=serviceDetailId; set price=price+(unitsPrice+(value-minValue)/stepLength*setpPrice)*(comboPeriodNum/unitsPeriodNum); end if; END IF; end LOOP detail_loop; close comboDetail; set reallyPrice=price*discountRate-discountPrice; END
