A student wanted a better example of writing a MySQL Persistent Stored Module (PSM) that maintains transactional scope across a couple tables. Here’s the one I wrote about ten years ago to build the MySQL Video Store model. It looks I neglected to put it out there before, so here it is for reference.
— 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 ;
You can then call the procedure, like:
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′);
I hope this code complete approach helps those looking to learn how to write MySQL PSMs.
