MacLochlainns Weblog

  IF add_numbers(1,3) > 3 THEN
    ...
  ELSE
    ...
  END IF;

-- Drop road_runner table if exists.
DROP TABLE IF EXISTS road_runner;
 
-- Create roadrunner table.
CREATE TABLE road_runner
( road_runner_id    int unsigned  auto_increment primary key
, road_runner_text  varchar(20)
, CONSTRAINT road_runner_nk UNIQUE (road_runner_text));
 
-- Drop coyote table if exists.
DROP TABLE IF EXISTS coyote;
 
-- Create coyote table.
CREATE TABLE coyote
( coyote_id       int unsigned  auto_increment primary key
, coyote_text     varchar(20)
, road_runner_id  int unsigned
, CONSTRAINT coyote_nk UNIQUE (coyote_text));

-- Change the delimiter to a "$$"
DELIMITER $$
 
-- Drop the paired procedure.
DROP PROCEDURE IF EXISTS two_table$$
 
-- Create the paired procedure.
CREATE PROCEDURE two_table
(IN    pv_road_runner_text  varchar(20)
,IN    pv_coyote_text       varchar(20)
,  OUT pv_confirm_it        int)
BEGIN
  /* Declare a variable to hold a sequence value for an 
     auto incrementing value. */
  DECLARE lv_road_runner_id  int unsigned;
 
  /* Declare a condition variable for attempting to write
     a non-unique record to a table. */
  DECLARE duplicate CONDITION FOR 1062;
 
  /* Declare an event handler for a duplicate condition 
     variable, rollback transaction, and set 1 as a false
     condition. */
  DECLARE EXIT HANDLER FOR duplicate
    BEGIN
      ROLLBACK to all_or_none;
      SET pv_confirm_it = 1;
    END;
 
  /* Start the transaction. */
  START TRANSACTION;
 
  /* Set the save point for a multiple table transaction. */
  SAVEPOINT all_or_none;
 
  /* Insert into road runner table. */
  INSERT INTO road_runner
  (road_runner_text)
  VALUES  
  (pv_road_runner_text);
 
  /* Capture the auto incrementing sequence value as a 
     local variable. */
  SET lv_road_runner_id := last_insert_id();
 
  /* Insert into the coyote table. */
  INSERT INTO coyote
  (coyote_text
  ,road_runner_id)
  VALUES  
  (pv_coyote_text
  ,lv_road_runner_id);
 
  /* Commit the record. */
  COMMIT;
 
  /* Set the control variable to a true value. */
  SET pv_confirm_it := 0;
 
END;
$$
 
DELIMITER ;

-- Set a control variable with a null value.
SET @sv_control := null;
 
-- Query the results from a join between the road_runner and coyote tables.
SELECT   *
FROM     road_runner r INNER JOIN coyote c
ON       r.road_runner_id = c.road_runner_id;
 
-- Call the two_table procedure with unique results.
CALL two_table('Road Runner 1','Coyote 1',@sv_control);
 
-- Query the control variable result from the second call to the
-- two_table procedure.
SELECT @sv_control AS "1st Insert";
 
-- Reset the value for a second test.
SET @sv_control := 0;
 
-- Call teh two_table procedure with unique results.  
CALL two_table('Road Runner 2','Coyote 1',@sv_control);  
 
-- Query the results from a join between the road_runner and coyote tables.  
SELECT   *
FROM     road_runner r INNER JOIN coyote c
ON       r.road_runner_id = c.road_runner_id;
 
-- Query the control variable result from the second call to the
-- two_table procedure.
SELECT   @sv_control AS "2nd Insert";