Prayer

-- for循環(huán)語句的用法 

begin atomic
 declare fullname char(40);
 for vl as
   select firstnme, midinit, lastname from employee
  do
  set fullname = lastname concat ','
    concat firstnme concat ' ' concat midinit;
  insert into tnames values (fullname);
 end for
end

 

create procedure leave_loop(out counter integer)
 language sql
 begin
   declare v_counter integer;
   declare v_firstnme varchar(12);
   declare v_midinit char(1);
   declare v_lastname varchar(15);
   declare at_end smallint default 0;
   declare not_found condition for sqlstate '02000';
   declare c1 cursor for
  select firstnme, midinit, lastname
    from employee;
   declare continue handler for not_found
  set at_end = 1;
   set v_counter = 0;
   open c1;
   fetch_loop:
   loop
  fetch c1 into v_firstnme, v_midinit, v_lastname;
  if at_end <> 0 then leave fetch_loop;
  end if;
  set v_counter = v_counter + 1;
   end loop fetch_loop;
   set counter = v_counter;
   close c1;
 end

 

   create procedure update_salary_if
     (in employee_number char(6), inout rating smallint)
     language sql
     begin
       declare not_found condition for sqlstate '02000';
       declare exit handler for not_found
         set rating = -1;
       if rating = 1
         then update employee
         set salary = salary * 1.10, bonus = 1000
         where empno = employee_number;
       elseif rating = 2
         then update employee
         set salary = salary * 1.05, bonus = 500
         where empno = employee_number;
       else update employee
         set salary = salary * 1.03, bonus = 0
         where empno = employee_number;
       end if;
     end

 

   create procedure loop_until_space(out counter integer)
     language sql
     begin
       declare v_counter integer default 0;
       declare v_firstnme varchar(12);
       declare v_midinit char(1);
       declare v_lastname varchar(15);
       declare c1 cursor for
         select firstnme, midinit, lastname
           from employee;
       declare continue handler for not found
         set counter = -1;
       open c1;
       fetch_loop:
       loop
         fetch c1 into v_firstnme, v_midinit, v_lastname;
         if v_midinit = ' ' then
           leave fetch_loop;
         end if;
         set v_counter = v_counter + 1;
       end loop fetch_loop;
       set counter = v_counter;
       close c1;
     end

 

   begin
   ...
     goto fail
   ...
     success: return 0
     fail: return -200
   end

 

set new_var.salary = 10000, new_var.comm = new_var.salary;
or:
set (new_var.salary, new_var.comm) = (10000, new_var.salary);

set (new_var.salary, new_var.comm)
  = (select avg(salary), avg(comm)
    from employee e
    where e.workdept = new_var.workdept);

 

   exec sql whenever sqlerror goto handlerr;
   exec sql whenever sqlwarning continue;
   exec sql whenever not found go to enddata;

 

   create procedure dept_median
     (in deptnumber smallint, out mediansalary double)
     language sql
     begin
       declare v_numrecords integer default 1;
       declare v_counter integer default 0;
       declare c1 cursor for
         select cast(salary as double)
           from staff
           where dept = deptnumber
           order by salary;
       declare exit handler for not found
         set mediansalary = 6666;
       set mediansalary = 0;
       select count(*) into v_numrecords
         from staff
         where dept = deptnumber;
       open c1;
       while v_counter < (v_numrecords / 2 + 1) do
         fetch c1 into mediansalary;
         set v_counter = v_counter + 1;
       end while;
       close c1;
     end