实用存储过程

方式一：

drop procedure if exists p;
delimiter //
create procedure p()
begin
  declare l_maxid,l_minid,l_total_cnt,l_current_cnt int unsigned default 0;
  declare l_step int default 10000;
  select max(case_id),min(case_id),count(*) into l_maxid,l_minid,l_total_cnt from case_fix_attrs ;
  select l_maxid,l_minid;
      
  while l_minid <= l_maxid
  do
   
    update case_grade a inner join case_fix_attrs b on a.case_id = b.case_id
    set b.case_weight=a.weight where b.case_id >=l_minid and b.case_id < l_minid+l_step;
   
      set l_minid=l_minid+l_step;
      set l_current_cnt=l_current_cnt+row_count();
      select l_minid,l_maxid,now(),l_current_cnt,row_count(),
        concat(round(l_current_cnt*100.0/l_total_cnt,2),'%') as complete;
  end while;
end;
//
delimiter ;
call p();

 

方式二：使用游标存储过程

drop procedure if exists p;
delimiter //
create procedure p()
begin
declare l_court_id int default 0;
declare l_cnt_a int default 0;
declare l_cnt_b int default 0;
declare done int default 0;
declare l_cursor cursor for select court_id from tool_cpwsw_cnt_bycourt order by id;
declare continue handler FOR SQLSTATE '02000' SET done = 1;
open l_cursor;
repeat
  
fetch l_cursor into l_court_id;
   if not done then
        select count(distinct doc_id) into l_cnt_a from court_url a inner join case_fix_attrs b on a.case_id = b.case_id
      where b.court_id = l_court_id and doc_id <> '';
       
        select count(distinct doc_id) into l_cnt_b from court_url a inner join case_fix_attrs b on a.case_id = b.case_id
      where b.court_id = l_court_id and is_dup='N';
       
      update tool_cpwsw_cnt_bycourt set cu_total_docid=l_cnt_a, cu_total =l_cnt_b where court_id=l_court_id;
      select l_court_id,l_cnt_a,l_cnt_b;
   end if;
until done end repeat;
close l_cursor;
END;
//
delimiter ;
call p();

随机抽查SQL：

select c.case_id,c.title,c.case_no,c.court_name,c.reason,c.judgement_date,c.main_judge
from
(
select case_id from todel_caseid order by rand() limit 10
)u
inner join case_fix_attrs b
on u.case_id=b.case_id
inner join case_fix_attrs c on b.case_no=c.case_no