CREATE PROCEDURE DJ_SQ11 () DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
  -- SQL 存储过程
------------------------------------------------------------------------
P1 :BEGIN
 declare t_hymldm varchar(6);
 declare t_hymlmc varchar(200);
 
 declare t_hyxldm varchar(6);
 declare t_hyxlmc varchar(200);
 
 declare not_found condition for sqlstate '02000';
 declare at_endc1 int default 0;
 declare at_endc2 int default 0;
 
 -- 声明临时表
 declare global temporary table session.tmp_dj_sq11(hydm varchar(6), hymc varchar(200))
   with replace not logged;
  
 p2: begin 
  -- 声明游标:行业门类
  declare cursor1 cursor
    with return for
    select hyml_dm, hyml_mc
    from dm_hyml
    where dm_hyml.hyml_dm in ('01','02','03')
    order by hyml_dm;
  declare continue handler for not_found set at_endc1 = 1;
   
  open cursor1;
  --遍历 cursor1
  while(at_endc1 < 1) do
  fetch cursor1 into t_hymldm, t_hymlmc;
  if (at_endc1 < 1) then
  insert into session.tmp_dj_sq11 values(t_hymldm, t_hymlmc);
  end if;
  
  p3: begin
   -- 声明游标：行业细类
   declare cursor2 cursor
     with return for
     select t.hy_dm,t.hy_mc  
     from dm_hy t
     where t.fqhy = t_hymldm
     order by t.hy_dm;
   declare continue handler for not_found set at_endc2 = 1;
   open cursor2;
   if (at_endc1 = 0) then
   set at_endc2 = 0;
   end if;
   
   --遍历 cursor2
   while(at_endc2 < 1) do
   fetch cursor2 into t_hyxldm, t_hyxlmc;
   if (at_endc2 < 1) then
   --把得到的行业细类插入临时表
   insert into session.tmp_dj_sq11 values(t_hyxldm, t_hyxlmc);
   end if;
   end while;
   close cursor2;
  end p3;
  
  end while;
  close cursor1;
 end p2;
 P4 :BEGIN
  declare c_result CURSOR
    with return for select * from session.tmp_dj_sq11;
  open c_result;
 END p4;
END p1本文出自 51CTO.COM技术博客