Database

delimiter //
drop procedure if exists sp_overdue_sales //
create procedure sp_overdue_sales()
begin
  drop temporary table if exists overdue_sales_tmp;
  create temporary table overdue_sales_tmp as select
  sales_id, customer_id, sale_date, quantity, sale_value
  from sales where sale_status = 'A' limit 10;
end;
//

delimiter //
drop procedure if exists sp_issue_invoice //
create procedure sp_issue_invoice()
begin
  declare var_sales_id int;
  declare last_sale int default 0;

  declare sales_csr cursor for
  select sales_id from overdue_sales_tmp;

  declare continue handler for not found set last_sale = 1;
  open sales_csr;
  sales_repeat: repeat
     if last_sale then
        leave sales_repeat;
     end if;
     fetch sales_csr into var_sales_id;
     select var_sales_id;
  until last_sale end repeat;
end;
//

| var_sales_id |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)

+--------------+
| var_sales_id |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1329 | No data - zero rows fetched, selected, or processed |
+-------+------+-----------------------------------------------------+

delimiter //
   drop procedure if exists example_cursor;
   create procedure example_cursor() reads sql data
   begin
    -- declare variables
    declare var_wage numeric(8,2);
    declare var_employee_id int;
    declare var_department_id int;

    -- declarar done state
    declare done int default 0;

    -- create cursor for table
    declare cur1 cursor for
    select employee_id, salary, department_id from employees;

    -- set done to 1 if data wasn't found
    declare continue handler for not found set done = 1;

    -- open cursor
    open cur1;
    -- start loop
    emp_loop:loop
       -- get data from cursor
       fetch cur1 into var_employee_id, var_wage, var_department_id;
    if(done = 1) then
       leave emp_loop;
     end if;
    end loop emp_loop;
    -- close cursor
    close cur1;
end;
 //

CREATE TABLE `employees` (
  `EMPLOYEE_ID` int(11) NOT NULL auto_increment,
  `SURNAME` varchar(40) NOT NULL,
  `FIRSTNAME` varchar(40) NOT NULL,
  `ADDRESS1` varchar(40) NOT NULL,
  `ADDRESS2` varchar(40) NOT NULL,
  `ZIPCODE` varchar(6) NOT NULL,
  `DATE_OF_BIRTH` datetime NOT NULL,
  `PHONENO` varchar(12) NOT NULL,
  `MANAGER_ID` decimal(8,0) NOT NULL,
  `SALARY` decimal(8,0) NOT NULL,
  `STATUS` varchar(9) NOT NULL,
  `DEPARTMENT_ID` decimal(8,0) default NULL,
  `COMMENT_ID` decimal(8,0) default NULL,
  `DEPARTMENT_NAME` varchar(40) default NULL,
  `ADJUSTED_SALARY` decimal(8,0) default NULL,
  `HIRE_DATE` datetime default NULL,
  PRIMARY KEY  (`EMPLOYEE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;