mysql 存储过程实际应用 mysql 存储过程应用实例

mysql 存储过程应用实例

1.字段加iddeclare@iint

select@i=count(*)frommember

while@i>0

begin

Updatemembersetnickname=concat("yt_",@i)whereid=@i

set@i=@i-1

end

多值存储过程参考：

/fyqcdbdx/article/details/7335891

2.同步信用值

BEGIN

declareaint;

declarebint;

declarepriceint;

declarestrVARCHAR(300);

declarexint;

declaresintdefault0;

declarecursor_namecursorforselectcredit_score,uidfrommember_countwherecredit_score>0anduid=17orderbyuiddesclimit1;

declarecontinuehandlerforsqlstate'02000'SETs=1;//游标到最后一个记录时设置s=1

setstr="--";

//游标的使用

opencursor_name;

fetchcursor_nameintoa,b;

whiles<>1do

setstr=concat(str,x);

if(a>0)then

select@price:=sum(order_sumprice)fromhy_orderwherestatus>0anduid=b;

updatemember_countsetcredit_value=@pricewhereuid=b;

endif;

fetchcursor_nameintoa,b;

endwhile;

closecursor_name;

selectstr;

END