An example of a stored procedure in MySql

Example of a MySql stored procedure: SQL code 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 SECURITY DEFINER comment' calculation service package price 'in in declare minPeriodType varchar (36); declare minPeriodNum INT; declare fetchOk INT; declare distinct 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