SQL，存储过程，循环

------------------------------------------------------------------------------------

sql自定义函数

DELIMITER //
CREATE FUNCTION calculate(price decimal(19,3),taxType varchar(20), taxRate decimal(19,3)) 
RETURNS decimal(19,3)
begin
   DECLARE a decimal(19,3) DEFAULT 0;
   declare excludeInPrice varchar(20) default "excludeInPrice";
   declare includeInPrice varchar(20) default "includeInPrice";
   if excludeInPrice=taxType
      THEN SET a = (price/(1+taxRate))*taxRate;
   end if;
   if includeInPrice=taxType
      THEN SET a = price * taxRate;
   end if;
   RETURN a;
END//
DELIMITER ;

update e_retail_return_product r join e_product p 
set r.tax=calculate(r.amount ,p.in_tax_type ,p.in_tax_rate),r.tax_type =p.in_tax_type,r.tax_rate =p.in_tax_rate  
where p.id = r.product_id;

DROP FUNCTION calculate;

------------------------------------------------------------------------------------

创建存储过程

create procedure insert_mhp_trans_info_his3000000a ()

define i int;

let i = 2000000;

while i < 4000000

INSERT INTO mhp_trans_info_his (trans_jnlno)

VALUES (i);

let i = i + 1;

end while;

end procedure;

执行

call insert_mhp_trans_info_his3000000a()

删除

drop procedure insert_mhp_trans_info_his3000000a ()