MacLochlainns Weblog

-- Conditionally drop procedure if it exists.
DROP PROCEDURE IF EXISTS contact_insert;
 
-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$
 
SELECT 'CREATE PROCEDURE contact_insert' AS "Statement";
CREATE PROCEDURE contact_insert
( pv_member_type         CHAR(12)
, pv_account_number      CHAR(19)
, pv_credit_card_number  CHAR(19)
, pv_credit_card_type    CHAR(12)
, pv_first_name          CHAR(20)
, pv_middle_name         CHAR(20)
, pv_last_name           CHAR(20)
, pv_contact_type        CHAR(12)
, pv_address_type        CHAR(12)
, pv_city                CHAR(30)
, pv_state_province      CHAR(30)
, pv_postal_code         CHAR(20)
, pv_street_address      CHAR(30)
, pv_telephone_type      CHAR(12)
, pv_country_code        CHAR(3)
, pv_area_code           CHAR(6)
, pv_telephone_number    CHAR(10)) MODIFIES SQL DATA
 
BEGIN
 
  /* Declare variables to manipulate auto generated sequence values. */
  DECLARE member_id            int unsigned;
  DECLARE contact_id           int unsigned;
  DECLARE address_id           int unsigned;
  DECLARE street_address_id    int unsigned;
  DECLARE telephone_id         int unsigned;
 
  /* Declare local constants for who-audit columns. */
  DECLARE lv_created_by        int unsigned DEFAULT 1001;
  DECLARE lv_creation_date     DATE         DEFAULT UTC_DATE();
  DECLARE lv_last_updated_by   int unsigned DEFAULT 1001;
  DECLARE lv_last_update_date  DATE         DEFAULT UTC_DATE();
 
  /* Declare a locally scoped variable. */
  DECLARE duplicate_key INT DEFAULT 0;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Start the transaction context. */
  START TRANSACTION;
 
  /* Create a SAVEPOINT as a recovery point. */
  SAVEPOINT all_or_none;
 
  /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */
  INSERT INTO member
  ( member_type
  , account_number
  , credit_card_number
  , credit_card_type
  , created_by
  , creation_date
  , last_updated_by
  , last_update_date )
  VALUES
  ((SELECT   common_lookup_id
    FROM     common_lookup
    WHERE    common_lookup_context = 'MEMBER'
    AND      common_lookup_type = pv_member_type)
  , pv_account_number
  , pv_credit_card_number
  ,(SELECT   common_lookup_id
    FROM     common_lookup
    WHERE    common_lookup_context = 'MEMBER'
    AND      common_lookup_type = pv_credit_card_type)
  , lv_created_by
  , lv_creation_date
  , lv_last_updated_by
  , lv_last_update_date );
 
  /* Preserve the sequence by a table related variable name. */
  SET member_id = last_insert_id();
 
  /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */
  INSERT INTO contact
  VALUES
  ( null
  , member_id
  ,(SELECT   common_lookup_id
    FROM     common_lookup
    WHERE    common_lookup_context = 'CONTACT'
    AND      common_lookup_type = pv_contact_type)
  , pv_first_name
  , pv_middle_name
  , pv_last_name
  , lv_created_by
  , lv_creation_date
  , lv_last_updated_by
  , lv_last_update_date );  
 
  /* Preserve the sequence by a table related variable name. */
  SET contact_id = last_insert_id();
 
  /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */
  INSERT INTO address
  VALUES
  ( null
  , last_insert_id()
  ,(SELECT   common_lookup_id
    FROM     common_lookup
    WHERE    common_lookup_context = 'MULTIPLE'
    AND      common_lookup_type = pv_address_type)
  , pv_city
  , pv_state_province
  , pv_postal_code
  , lv_created_by
  , lv_creation_date
  , lv_last_updated_by
  , lv_last_update_date );  
 
  /* Preserve the sequence by a table related variable name. */
  SET address_id = last_insert_id();
 
  /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */
  INSERT INTO street_address
  VALUES
  ( null
  , last_insert_id()
  , pv_street_address
  , lv_created_by
  , lv_creation_date
  , lv_last_updated_by
  , lv_last_update_date );  
 
  /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */
  INSERT INTO telephone
  VALUES
  ( null
  , contact_id
  , address_id
  ,(SELECT   common_lookup_id
    FROM     common_lookup
    WHERE    common_lookup_context = 'MULTIPLE'
    AND      common_lookup_type = pv_telephone_type)
  , pv_country_code
  , pv_area_code
  , pv_telephone_number
  , lv_created_by
  , lv_creation_date
  , lv_last_updated_by
  , lv_last_update_date);
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
  /* This commits the write when successful and is harmless otherwise. */
  COMMIT;
 
END;
$$
 
-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;

SELECT 'CALL contact_insert() PROCEDURE 5 times' AS "Statement";
CALL contact_insert('INDIVIDUAL','R11-514-34','1111-1111-1111-1111','VISA_CARD','Goeffrey','Ward','Clinton','CUSTOMER','HOME','Provo','Utah','84606','118 South 9th East','HOME','011','801','423\-1234');
CALL contact_insert('INDIVIDUAL','R11-514-35','1111-2222-1111-1111','VISA_CARD','Wendy',null,'Moss','CUSTOMER','HOME','Provo','Utah','84606','1218 South 10th East','HOME','011','801','423-1234');
CALL contact_insert('INDIVIDUAL','R11-514-36','1111-1111-2222-1111','VISA_CARD','Simon','Jonah','Gretelz','CUSTOMER','HOME','Provo','Utah','84606','2118 South 7th East','HOME','011','801','423-1234');
CALL contact_insert('INDIVIDUAL','R11-514-37','1111-1111-1111-2222','MASTER_CARD','Elizabeth','Jane','Royal','CUSTOMER','HOME','Provo','Utah','84606','2228 South 14th East','HOME','011','801','423-1234');
CALL contact_insert('INDIVIDUAL','R11-514-38','1111-1111-3333-1111','VISA_CARD','Brian','Nathan','Smith','CUSTOMER','HOME','Spanish Fork','Utah','84606','333 North 2nd East','HOME','011','801','423-1234');