年末整理九 mysql存储过程例子

create procedure uchome_blog_procedure()
begin
DECLARE  maxarticleid , mount  mediumint(8) default 0;

select max(article_id)  into maxarticleid  from blog.blog_article  ;
WHILE   mount*1000 < maxarticleid  DO
insert into uchome_blog_blogfield_temp  select u.id,article_id,a.blog_user_id ,u.username,a.article_content,article_subject
click_number,comment_number,create_time,category_id
 from blog_article a left join blog_user_temp u  on  u.blog_user_id=a.blog_user_id  order by article_id asc limit  mount*1000,  1000  ;

SET  mount = mount + 1 ;
end WHILE
end

create table uchome_blog_blogfield_temp (select u.id,article_id,a.blog_user_id ,u.username,a.article_content,article_subject
,click_number,comment_number,create_time,category_id
 from blog_article a left join blog_user_temp u  on  u.blog_user_id=a.blog_user_id
 where 1 = 2 ) ;

create procedure testprocedure()
begin
SET  @mount = 2 ;
while @mount <4 do
set @i = 2 ;
end while ;
end ;

create procedure proc_name (in parameter integer)
begin
declare variable varchar(20);
if parameter=1 then
set variable='Windows';
else
set variable='Linux';
end if;
select parameter;
end;

create procedure uchome_blog_procedure()
begin
DECLARE  maxarticleid , mount  mediumint(8) default 0;

select max(article_id)  into maxarticleid  from blog.blog_article  ;
set @sql = 'insert into uchome_blog_blogfield_temp  select u.id,article_id,a.blog_user_id ,u.username,a.article_content,article_subject
click_number,comment_number,create_time,category_id
 from blog_article a left join blog_user_temp u  on  u.blog_user_id=a.blog_user_id  order by article_id asc limit '  ;

WHILE   mount*1000 < maxarticleid  do

set @sql = concat( @sql , mount*1000 , ' , 1000 ' ) ;
prepare stmt from @sql ;
execute stmt ;
set  mount = mount + 1 ;
end while ;
end ;

drop procedure if exists uchome_blog_procedure ;
delimiter //
create procedure uchome_blog_procedure()
begin
set @mount = 0 ;
set @maxarticleid = 1 ;
select max(article_id)  into @maxarticleid  from blog.blog_article  ;
set @sql = concat('insert into uchome_blog_blogfield_temp ',
' select u.id,article_id,a.blog_user_id ,u.username,a.article_content,article_subject , ',
' click_number,comment_number,create_time,category_id ' ,
' from blog_article a left join blog_user_temp u  on  u.blog_user_id=a.blog_user_id ' ,
' order by article_id asc limit ')  ;
WHILE   @mount*1000 < @maxarticleid  do
set @sqlstmt = concat( @sql , ' ' ,  @mount*1000 , ',' , 1000 ) ;
prepare stmt from @sqlstmt ;
execute stmt ;
set  @mount = @mount + 1 ;
deallocate prepare stmt;
end while ;
end ;
//
delimit ; //

 

create procedure testprocedure()
begin
SET  @mount = 2 ;
set @sql = concat('select blog_user_id   ' , ' from blog_article limit  ' ) ;
set @sql = concat(@sql ,  @mount*2  , ',' , 1 ) ;
prepare stmt from @sql ;
execute stmt ;
end ;
drop procedure uchome_blog_procedure ;
call uchome_blog_procedure ;
delete from uchome_blog_blogfield_temp ;
select count(*) from uchome_blog_blogfield_temp ;

create procedure uchome_blog_procedure()
begin
declare mount int default 0 ;
declare departments int default 0  ;
declare cursor_name cursor for  select u.id,article_id,a.blog_user_id ,u.username,a.article_content,article_subject ,
 click_number,comment_number,create_time,category_id 
 from blog_article a left join blog_user_temp u  on  u.blog_user_id=a.blog_user_id
 order by article_id asc limit mount*1000 , 1000 ;
DECLARE CONTINUE HANDLER FOR NOT FOUND set departments = 1 ;
declare id int(11)  ;
declare article_id int(11)  ;
declare blog_user_id int(11)  ;
declare username varchar(100) default ' ' ;
declare article_content longtext  ;
declare article_subject  varchar(250) default ' ' ;
declare click_number int(11)  ;
declare comment_number  int(11)  ;
declare create_time datetime default '0000-00-00 00:00:00' ;
declare category_id  int(11)  ;
select max(article_id)  into @maxarticleid  from blog.blog_article  ;
set @maxarticleid = 1 ;
set @sql = concat('insert into uchome_blog_blogfield_temp ' ,
' (id , article_id , blog_user_id , username , article_content , article_subject , ' ,
' click_number , comment_number , create_time , category_id )',
' values ( ' , id ,',', article_id ,',', blog_user_id ,','', username ,'','', article_content,'','',article_subject ,'',',
 click_number,',',comment_number,','',create_time,'',',category_id ) ;
WHILE   mount*1000 < @maxarticleid  do
open cursor ;
repeat
fetch cursor_name into id , article_id , blog_user_id , username , article_content,article_subject ,
 click_number,comment_number,create_time,category_id ;
prepare stmt from @sql ;
execute stmt ;
deallocate prepare stmt;
UNTIL departments  END REPEAT;
close cursor ;
set  mount = mount + 1 ;
end while ;
end ;

create procedure uchome_blog_procedure()
begin
set @mount = 0 ;
set @maxarticleid = 1 ;
select max(article_id)  into @maxarticleid  from blog.blog_article  ;
set @sql = concat('insert into uchome_blog_blogfield_temp ',
' ( article_id,blog_user_id ,article_content,article_subject ,',
'click_number,comment_number,create_time,category_id )' ,
' select article_id,blog_user_id ,article_content,article_subject , ',
' click_number,comment_number,create_time,category_id ' ,
' from blog_article  ' ,
' order by article_id asc limit ')  ;
WHILE   @mount*1000 < @maxarticleid  do
set @sqlstmt = concat( @sql , ' ' ,  @mount*1000 , ',' , 1000 ) ;
prepare stmt from @sqlstmt ;
execute stmt ;
set  @mount = @mount + 1 ;
deallocate prepare stmt;
end while ;
end ;