Database

[SQL] 

	IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
	BEGIN
		SET @param_days_since_last_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_noorder by created_date DESC LIMIT 1)));
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
	BEGIN
		SET' at line 1

BEGIN
	DECLARE param_item_id_v1 int;
	DECLARE param_item_id_v2 int;
	DECLARE param_file_cutoff_days int;
	DECLARE param_days_since_last_visit int;

	DECLARE param_account_number BIGINT;
	DECLARE param_price_list_id INT;

	DECLARE param_v1_fees DOUBLE(11, 5);
	DECLARE param_v2_fees DOUBLE(11, 5);

	DECLARE param_item_description_v1 VARCHAR(255);
	DECLARE param_item_description_v2 VARCHAR(255);

	SET param_account_number = (SELECT account_number FROM visits WHERE visit_id = param_visit_id);
	SET param_price_list_id = (SELECT price_list_id FROM visits WHERE visit_id = param_visit_id);

	SET param_item_id_v1 = (SELECT item_id FROM price_list_items WHERE item_code = param_v1);
	SET param_item_description_v1 = (SELECT item_name FROM price_list_items WHERE item_id  = param_item_id_v1);
	SET param_v1_fees = (SELECT price_list_item_prices.item_price FROM price_list_item_prices INNER JOIN price_list_items ON price_list_item_prices.item_id = price_list_items.item_id WHERE price_list_items.item_code = param_v1 AND price_list_item_prices.price_list_id = param_price_list_id);

	SET param_item_id_v2 = (SELECT item_id FROM price_list_items WHERE item_code = param_v2);
	SET param_item_description_v2 = (SELECT item_name FROM price_list_items WHERE item_id  = param_item_id_v2);
	SET param_v2_fees = (SELECT price_list_item_prices.item_price FROM price_list_item_prices INNER JOIN price_list_items ON price_list_item_prices.item_id = price_list_items.item_id WHERE price_list_items.item_code = param_v2 AND price_list_item_prices.price_list_id = param_price_list_id);

	SET param_file_cutoff_days = (SELECT system_param_value FROM system_param WHERE system_param_name = 'FILE_CUTOFF_DAYS');

	IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
	BEGIN
		SET param_days_since_last_visit = DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_no order by created_date DESC LIMIT 1));
	
		IF (param_days_since_last_visit > param_file_cutoff_days) THEN
		BEGIN
			INSERT INTO visit_items (file_no, visit_id, account_number, item_id, item_code, item_description, item_amount, created_user) VALUES (param_file_no, param_visit_id, param_account_number, param_item_id_v1, param_v1, param_item_description_v1, param_v1_fees, param_created_user);
		END;
		ELSE
		BEGIN
			INSERT INTO visit_items (file_no, visit_id, account_number, item_id, item_code, item_description, item_amount, created_user) VALUES (param_file_no, param_visit_id, param_account_number, param_item_id_v2, param_v2, param_item_description_v2, param_v2_fees, param_created_user);
		END;
		END IF;		
	END;
	ELSE
	BEGIN
		INSERT INTO visit_items (file_no, visit_id, account_number, item_id, item_code, item_description, item_amount, created_user) VALUES (param_file_no, param_visit_id, param_account_number, param_item_id_v1, param_v1, param_item_description_v1, param_v1_fees, param_created_user);
	END;
	END IF;

	UPDATE visits SET is_received = TRUE, recieved_user = param_created_user, recieved_date = NOW() WHERE visit_id = param_visit_id;
END

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)