存储过程的创建和调用。。。

--创建一个存储过程sel，查询m_message_list表的所有记录
 --create  procedure  sel()  select *   from  m_message_list;
--调用sel查询
call  sel();



--循环插值
drop procedure if exists  autoinsert;
create  procedure  autoinsert()
begin   
        declare  i  int  default 1;
        while i<30
        DO
insert into  m_message_list(user_id,terminal_id,send_name,contents,is_read,operator,send_date,title) values(32,null,
'吉海','aaaaa',0,'jimi','2008-04-17 17:21:08','标题');
           set i=i+1;
        end WHILE;
end;

评论

2 楼 weiweichen1985 2009-04-11  

游标的使用

引用

CREATE DEFINER=`root`@`localhost` PROCEDURE `updatejifens`()
begin
  DECLARE cardid,error,dealjifensum,accountalljifensum int;
  DECLARE cur_1 CURSOR for select card_id from u_deal_info where deal_type_id=16;
  DECLARE continue handler for not found set error=1;
  set error=0;
   open cur_1;
   repeat
     fetch cur_1 into cardid;
    
     select  sum(deal_jifen) into dealjifensum from  u_deal_info where card_id=cardid;
    
     select  sum(jifen_balance+partjifen) into accountalljifensum from u_user_account where id=(select account_id from u_card_info where id=cardid);
    
    
     if(dealjifensum>accountalljifensum) then
          update u_user_account SET  jifen_balance=jifen_balance+100 where id=(select account_id from u_card_info where id=cardid);
     end if;
  
     until error=1
   end repeat;
   close cur_1;
end;

1 楼 weiweichen1985 2008-08-21  

嵌套循环

CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinsert_u_deal_info_chongzhi`()
begin      
        declare  i  int  default 1;   
        declare  j  int  default 31;
         
            while j<81
              DO 
               set i=1;
               while i<4000  
        DO 
INSERT INTO u_deal_info
(card_id,terminal_id,service_id,
deal_type_id,deal_amounts,charges,
balance,deal_jifen,tongbi_balance,
jifen_balance,add_jifen,is_cancel,
transfer_status,canceled,name,deal_date)
VALUES (j,2,NULL,9,200,200,200,NULL,0,0,NULL,0,NULL,0,'001','2008-07-23 09:50:27');
                       set i=i+1;   
        end WHILE;                                                                
             set  j=j+1;                                                      
          end WHILE;   
          
end;