Database

delimiter // 
drop procedure if exists sp_optimistic_locking // 
create procedure sp_optimistic_locking 
(
  p_from_account int, 
  p_to_account int, 
  p_transfer_amount numeric(10,2),
  p_status int, 
  p_message varchar(100)
)
sql security invoker
modifies sql data 
begin 
  declare l_account_balance1 numeric(10,2);
  declare l_account_balance2 numeric(10,2);
  declare l_account_timestamp1 timestamp; 
  declare l_account_timestamp2 timestamp; 
  
  select balance, account_timestamp into l_account_balance1, l_account_timestamp1
  from account_balance where account_id = p_from_account;  
  
  if l_account_balance1 >= p_transfer_amount then 

  start transaction; 

  -- call long_running_validation(p_from_account);

  select balance, account_timestamp into l_account_balance2, l_account_timestamp2 
  from account_balance where account_id = p_from_account for update; 
    
    if (l_account_balance1 <> l_account_balance2 or 
        l_account_timestamp1 <> l_account_timestamp2) then
       rollback; 
       set p_status = -1; 
       set p_message = concat('Transaction canceled due to previous update of account ', p_from_account);
    else 
       update account_balance set balance = balance - p_transfer_amount where 
       account_id = p_from_account; 
 
       update account_balance set balance = balance + p_transfer_amount where 
       account_id = p_to_account; 

       commit;
       set p_status  = 0; 
       set p_message = 'transaction ok'; 
    end if;
    else  
     set p_status = -1; 
     set p_message = 'Transaction canceled due to account balance less than desired withdrawal'; 
  end if; 
     select 'Transaction code', p_status; 
     select 'Transaction message', p_message;
end; 
//

delimiter //
drop procedure if exists sp_loadfile //
create procedure sp_loadfile
(
  p_file_name varchar(128)
)
begin
  declare my_text text;
  set my_text = load_file(p_file_name);
  select concat(p_file_name, ' has ', length(my_text), ' bytes.') as 'Output';
end;
//

-rw-rw-rw- 1 eduardo eduardo 5 Set  1 11:04 test.txt

mysql> call sp_loadfile('/var/www/mysql/PL_loadfile/test.txt');
+--------+
| Output |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

Quote:

This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)

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 |
+-------+------+-----------------------------------------------------+