Archive for the ‘Stored Procedures’ Category
Multi-row Merge in MySQL
After I wrote the post for students on the multiple row MERGE statement for an upload through an external table in Oracle, I thought to check how it might be done with MySQL. More or less because I try to keep track of how things are done in several databases.
MySQL’s equivalent to a MERGE statement is an INSERT statement with an ON DUPLICATE KEY clause, which I blogged about a while back. You may also use the REPLACE INTO when you want to merge more than one row. At the time that I wrote this, I thought there wasn’t support for an INSERT ON DUPLICATE KEY clause statement with a subquery but I found that I was wrong. Fortunately, somebody posted a comment to remind me about this and now both solutions are here for anybody that would like them.
The workaround with a VALUES clause was to write a stored procedure with two cursor loops, explicitly pass the values from the cursor to local variables, and then put the local variables in the VALUES clause. I’ll post the other with a subquery soon. On parity, clearly Oracle’s MERGE statement (shown here) is far superior than MySQL’s approach.
Demonstration
Here are the steps to accomplish an import/upload with the INSERT statement and ON DUPLICATE KEY clause. In this example, you upload data from a flat file, or Comma Separated Value (CSV) file to a denormalized table (actually in unnormalized form). This type of file upload transfers information that doesn’t have surrogate key values. You have to create those in the scope of the transformation to the normalized tables.
Step #1 : Position your CSV file in the physical directory
After creating the virtual directory, copy the following contents into a file named kingdom_mysql_import.csv in the C:\Data\Download directory or folder. If you have Windows UAC enabled in Windows Vista or 7, you should disable it before performing this step.
Place the following in the kingdom_mysql_import.csv file. The trailing commas are meaningful in MySQL and avoid problems when reading CSV files.
Narnia, 77600,'Peter the Magnificent',12720320,12920609, Narnia, 77600,'Edmund the Just',12720320,12920609, Narnia, 77600,'Susan the Gentle',12720320,12920609, Narnia, 77600,'Lucy the Valiant',12720320,12920609, Narnia, 42100,'Peter the Magnificent',15310412,15310531, Narnia, 42100,'Edmund the Just',15310412,15310531, Narnia, 42100,'Susan the Gentle',15310412,15310531, Narnia, 42100,'Lucy the Valiant',15310412,15310531, Camelot, 15200,'King Arthur',06310310,06861212, Camelot, 15200,'Sir Lionel',06310310,06861212, Camelot, 15200,'Sir Bors',06310310,06351212, Camelot, 15200,'Sir Bors',06400310,06861212, Camelot, 15200,'Sir Galahad',06310310,06861212, Camelot, 15200,'Sir Gawain',06310310,06861212, Camelot, 15200,'Sir Tristram',06310310,06861212, Camelot, 15200,'Sir Percival',06310310,06861212, Camelot, 15200,'Sir Lancelot',06700930,06821212, |
Step #2 : Connect as the student user
Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:
mysql -ustudent -p |
Connect to the sampledb database, like so:
mysql> USE sampledb; |
Step #3 : Run the script that creates tables and sequences
Copy the following into a create_mysql_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | -- This enables dropping tables with foreign key dependencies. -- It is specific to the InnoDB Engine. SET FOREIGN_KEY_CHECKS = 0; -- Conditionally drop objects. SELECT 'KINGDOM' AS "Drop Table"; DROP TABLE IF EXISTS KINGDOM; SELECT 'KNIGHT' AS "Drop Table"; DROP TABLE IF EXISTS KNIGHT; SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Drop Table"; DROP TABLE IF EXISTS KINGDOM_KNIGHT_IMPORT; -- Create normalized kingdom table. SELECT 'KINGDOM' AS "Create Table"; CREATE TABLE kingdom ( kingdom_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , kingdom_name VARCHAR(20) , population INT UNSIGNED) ENGINE=INNODB; -- Create normalized knight table. SELECT 'KNIGHT' AS "Create Table"; CREATE TABLE knight ( knight_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , knight_name VARCHAR(24) , kingdom_allegiance_id INT UNSIGNED , allegiance_start_date DATE , allegiance_end_date DATE , CONSTRAINT fk_kingdom FOREIGN KEY (kingdom_allegiance_id) REFERENCES kingdom (kingdom_id)) ENGINE=INNODB; -- Create external import table in memory only - disappears after rebooting the mysqld service. SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Create Table"; CREATE TABLE kingdom_knight_import ( kingdom_name VARCHAR(20) , population INT UNSIGNED , knight_name VARCHAR(24) , allegiance_start_date DATE , allegiance_end_date DATE) ENGINE=MEMORY; |
Step #4 : Load the data into your target upload table
There a number of things that could go wrong but when you choose LOCAL there generally aren’t any problems. Run the following query from the student account while using the sampledb database, and check whether or not you can access the kingdom_import.csv file.
1 2 3 4 5 6 | LOAD DATA LOCAL INFILE 'c:/Data/kingdom_mysql_import.csv' INTO TABLE kingdom_knight_import FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; |
Step #5 : Create the upload procedure
Copy the following into a create_mysql_upload_procedure.sql file within a directory of your choice. You should note that unlike Oracle’s MERGE statement, this is done with the ON DUPLICATE KEY clause and requires actual values not a source query. This presents few options other than a stored routine, known as a stored procedure. As you can see from the code, there’s a great deal of complexity to the syntax and a much more verbose implementation than Oracle’s equivalent PL/SQL.
Then, run it as the student account. As you look at the structure to achieve this simple thing, the long standing complaint about PL/SQL being a verbose language comes to mind. Clearly, stored procedures are new to MySQL but they’re quite a bit more verbose than PL/SQL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 | -- Conditionally drop the procedure. SELECT 'UPLOAD_KINGDOM' AS "Drop Procedure"; DROP PROCEDURE IF EXISTS upload_kingdom; -- Reset the execution delimiter to create a stored program. DELIMITER $$ -- The parentheses after the procedure name must be there or the MODIFIES SQL DATA raises an compile time exception. CREATE PROCEDURE upload_kingdom() MODIFIES SQL DATA BEGIN /* Declare local variables. */ DECLARE lv_kingdom_id INT UNSIGNED; DECLARE lv_kingdom_name VARCHAR(20); DECLARE lv_population INT UNSIGNED; DECLARE lv_knight_id INT UNSIGNED; DECLARE lv_knight_name VARCHAR(24); DECLARE lv_kingdom_allegiance_id INT UNSIGNED; DECLARE lv_allegiance_start_date DATE; DECLARE lv_allegiance_end_date DATE; /* Declare a handler variables. */ DECLARE duplicate_key INT DEFAULT 0; DECLARE foreign_key INT DEFAULT 0; DECLARE fetched INT DEFAULT 0; /* Cursors must come after variables and before event handlers. */ /* Declare a SQL cursor with a left join on the natural key. */ DECLARE kingdom_cursor CURSOR FOR SELECT DISTINCT k.kingdom_id , kki.kingdom_name , kki.population FROM kingdom_knight_import kki LEFT JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population; /* Declare a SQL cursor with a join on the natural key. */ DECLARE knight_cursor CURSOR FOR SELECT kn.knight_id , kki.knight_name , k.kingdom_id , kki.allegiance_start_date AS start_date , kki.allegiance_end_date AS end_date FROM kingdom_knight_import kki INNER JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population LEFT JOIN knight kn ON k.kingdom_id = kn.kingdom_allegiance_id AND kki.knight_name = kn.knight_name AND kki.allegiance_start_date = kn.allegiance_start_date AND kki.allegiance_end_date = kn.allegiance_end_date; /* Event handlers must always be last in the declaration section. */ /* Declare a duplicate key handler */ DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1; DECLARE CONTINUE HANDLER FOR 1216 SET foreign_key = 1; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* ---------------------------------------------------------------------- */ /* Start transaction context. */ START TRANSACTION; /* Set savepoint. */ SAVEPOINT both_or_none; /* Open a local cursor. */ OPEN kingdom_cursor; cursor_kingdom: LOOP FETCH kingdom_cursor INTO lv_kingdom_id , lv_kingdom_name , lv_population; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_kingdom; END IF; INSERT INTO kingdom VALUES ( lv_kingdom_id , lv_kingdom_name , lv_population ) ON DUPLICATE KEY UPDATE kingdom_name = lv_kingdom_name; END LOOP cursor_kingdom; CLOSE kingdom_cursor; /* Reset the continue handler to zero. */ SET fetched = 0; /* Open a local cursor. */ OPEN knight_cursor; cursor_knight: LOOP /* Fetch records until they're all read, and a NOT FOUND SET is returned. */ FETCH knight_cursor INTO lv_knight_id , lv_knight_name , lv_kingdom_allegiance_id , lv_allegiance_start_date , lv_allegiance_end_date; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_knight; END IF; INSERT INTO knight VALUES ( lv_knight_id , lv_knight_name , lv_kingdom_allegiance_id , lv_allegiance_start_date , lv_allegiance_end_date ) ON DUPLICATE KEY UPDATE knight_name = lv_knight_name; END LOOP cursor_knight; CLOSE knight_cursor; /* Reset the continue handler to zero. */ SET fetched = 0; /* ---------------------------------------------------------------------- */ /* This acts as an exception handling block. */ IF duplicate_key = 1 OR foreign_key = 1 THEN /* This undoes all DML statements to this point in the procedure. */ ROLLBACK TO SAVEPOINT both_or_none; ELSE /* This commits the writes. */ COMMIT; END IF; END; $$ -- Reset the delimiter to the default. DELIMITER ; |
Here’s the better option with an embedded query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 | -- Conditionally drop the procedure. SELECT 'UPLOAD_KINGDOM' AS "Drop Procedure"; DROP PROCEDURE IF EXISTS upload_kingdom; -- Reset the execution delimiter to create a stored program. DELIMITER $$ -- The parentheses after the procedure name must be there or the MODIFIES SQL DATA raises an compile time exception. CREATE PROCEDURE upload_kingdom() MODIFIES SQL DATA BEGIN /* Declare a handler variables. */ DECLARE duplicate_key INT DEFAULT 0; DECLARE foreign_key INT DEFAULT 0; /* Declare a duplicate key handler */ DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1; DECLARE CONTINUE HANDLER FOR 1216 SET foreign_key = 1; /* ---------------------------------------------------------------------- */ /* Start transaction context. */ START TRANSACTION; /* Set savepoint. */ SAVEPOINT both_or_none; /* Using subqueries update the targets. */ INSERT INTO knight ( SELECT kn.knight_id , kki.knight_name , k.kingdom_id , kki.allegiance_start_date AS start_date , kki.allegiance_end_date AS end_date FROM kingdom_knight_import kki INNER JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population LEFT JOIN knight kn ON k.kingdom_id = kn.kingdom_allegiance_id AND kki.knight_name = kn.knight_name AND kki.allegiance_start_date = kn.allegiance_start_date AND kki.allegiance_end_date = kn.allegiance_end_date ) ON DUPLICATE KEY UPDATE knight_id = kn.knight_id; INSERT INTO knight ( SELECT kn.knight_id , kki.knight_name , k.kingdom_id , kki.allegiance_start_date AS start_date , kki.allegiance_end_date AS end_date FROM kingdom_knight_import kki INNER JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population LEFT JOIN knight kn ON k.kingdom_id = kn.kingdom_allegiance_id AND kki.knight_name = kn.knight_name AND kki.allegiance_start_date = kn.allegiance_start_date AND kki.allegiance_end_date = kn.allegiance_end_date ) ON DUPLICATE KEY UPDATE knight_id = kn.knight_id; /* ---------------------------------------------------------------------- */ /* This acts as an exception handling block. */ IF duplicate_key = 1 OR foreign_key = 1 THEN /* This undoes all DML statements to this point in the procedure. */ ROLLBACK TO SAVEPOINT both_or_none; ELSE /* This commits the writes. */ COMMIT; END IF; END; $$ -- Reset the delimiter to the default. DELIMITER ; |
Step #6 : Run the upload procedure
You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.
CALL upload_kingdom; |
Step #7 : Test the results of the upload procedure
You can test whether or not it worked by running the following queries.
-- Check the kingdom table. SELECT * FROM kingdom; SELECT * FROM knight; |
It should display the following information:
+------------+--------------+------------+ | kingdom_id | kingdom_name | population | +------------+--------------+------------+ | 1 | Narnia | 77600 | | 2 | Narnia | 42100 | | 3 | Camelot | 15200 | +------------+--------------+------------+ +-----------+-------------------------+-----------------------+-----------------------+---------------------+ | knight_id | knight_name | kingdom_allegiance_id | allegiance_start_date | allegiance_end_date | +-----------+-------------------------+-----------------------+-----------------------+---------------------+ | 1 | 'Peter the Magnificent' | 1 | 1272-03-20 | 1292-06-09 | | 2 | 'Edmund the Just' | 1 | 1272-03-20 | 1292-06-09 | | 3 | 'Susan the Gentle' | 1 | 1272-03-20 | 1292-06-09 | | 4 | 'Lucy the Valiant' | 1 | 1272-03-20 | 1292-06-09 | | 5 | 'Peter the Magnificent' | 2 | 1531-04-12 | 1531-05-31 | | 6 | 'Edmund the Just' | 2 | 1531-04-12 | 1531-05-31 | | 7 | 'Susan the Gentle' | 2 | 1531-04-12 | 1531-05-31 | | 8 | 'Lucy the Valiant' | 2 | 1531-04-12 | 1531-05-31 | | 9 | 'King Arthur' | 3 | 0631-03-10 | 0686-12-12 | | 10 | 'Sir Lionel' | 3 | 0631-03-10 | 0686-12-12 | | 11 | 'Sir Bors' | 3 | 0631-03-10 | 0635-12-12 | | 12 | 'Sir Bors' | 3 | 0640-03-10 | 0686-12-12 | | 13 | 'Sir Galahad' | 3 | 0631-03-10 | 0686-12-12 | | 14 | 'Sir Gawain' | 3 | 0631-03-10 | 0686-12-12 | | 15 | 'Sir Tristram' | 3 | 0631-03-10 | 0686-12-12 | | 16 | 'Sir Percival' | 3 | 0631-03-10 | 0686-12-12 | | 17 | 'Sir Lancelot' | 3 | 0670-09-30 | 0682-12-12 | +-----------+-------------------------+-----------------------+-----------------------+---------------------+ |
You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.
Merge Statement for ETL
While working through examples for my students on uploading data, I thought it would be interesting to demonstrate how to create a re-runnable upload. Especially when chatting with a friend who was unaware that you could use joins inside the source element of a MERGE statement. Naturally, the MERGE statement seemed like the best approach in an Oracle database because with my criteria:
- The source file would not include any surrogate key values.
- The source file would have denormalized record sets with data that should belong to parent and child tables, technically unnormalized form (UNF).
- Primary and foreign key values would be determined on load to the tables.
- There could be a one-to-many relationship between the parent and child tables in the original source.
- Subsequent data sets may replicate data already seeded or not in the tables.
- Avoid any complex PL/SQL structures.
Step #1 : Create a Virtual Directory
You can create a virtual directory without a physical directory but it won’t work when you try to access it. Therefore, you should create the physical directory first. Assuming you’ve created a C:\Data\Download file directory on the Windows platform, you can then create a virtual directory and grant permissions to the student user as the SYS privileged user. The syntax for these steps is:
CREATE DIRECTORY download AS 'C:\Data\Download'; GRANT READ, WRITE ON DIRECTORY download TO student; |
If you want more detail on these steps, check this older post on the blog.
Step #2 : Create the Target and External Tables
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | -- Conditionally drop tables and sequences. BEGIN FOR i IN (SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME IN ('KINGDOM','KNIGHT','KINGDOM_KNIGHT_IMPORT')) LOOP EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS'; END LOOP; FOR i IN (SELECT sequence_name FROM user_sequences WHERE sequence_name IN ('KINGDOM_S1','KNIGHT_S1')) LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name; END LOOP; END; / -- Create normalized kingdom table. CREATE TABLE kingdom ( kingdom_id NUMBER , kingdom_name VARCHAR2(20) , population NUMBER); -- Create a sequence for the kingdom table. CREATE SEQUENCE kingdom_s1; -- Create normalized knight table. CREATE TABLE knight ( knight_id NUMBER , knight_name VARCHAR2(24) , kingdom_allegiance_id NUMBER , allegiance_start_date DATE , allegiance_end_date DATE); -- Create a sequence for the knight table. CREATE SEQUENCE knight_s1; -- Create external import table. CREATE TABLE kingdom_knight_import ( kingdom_name VARCHAR2(20) , population NUMBER , knight_name VARCHAR2(24) , allegiance_start_date DATE , allegiance_end_date DATE) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'DOWNLOAD':'kingdom_import.bad' DISCARDFILE 'DOWNLOAD':'kingdom_import.dis' LOGFILE 'DOWNLOAD':'kingdom_import.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('kingdom_import.csv')) REJECT LIMIT UNLIMITED; |
Step #3 : Create a Procedure to ensure an all or nothing transaction
The procedure ensures that an all or nothing transaction occurs to both tables. Inside the procedure you have two MERGE statements.
The first MERGE statement uses a LEFT JOIN to ensure that any new kingdom_name will be added to the kingdom table. The kingdom_name and population columns are the natural key in this model. The second MERGE statement uses an INNER JOIN to ensure that knight rows are only inserted when they belong to an existing kingdom_name. Naturally, the primary key capture occurs in this statement and it maps the primary key to the foreign key column in the knight table.
The complete procedure code follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | -- Create a procedure to wrap the transaction. CREATE OR REPLACE PROCEDURE upload_kingdom IS BEGIN -- Set save point for an all or nothing transaction. SAVEPOINT starting_point; -- Insert or update the table, which makes this rerunnable when the file hasn't been updated. MERGE INTO kingdom target USING (SELECT DISTINCT k.kingdom_id , kki.kingdom_name , kki.population FROM kingdom_knight_import kki LEFT JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population) SOURCE ON (target.kingdom_id = SOURCE.kingdom_id) WHEN MATCHED THEN UPDATE SET kingdom_name = SOURCE.kingdom_name WHEN NOT MATCHED THEN INSERT VALUES ( kingdom_s1.nextval , SOURCE.kingdom_name , SOURCE.population); -- Insert or update the table, which makes this rerunnable when the file hasn't been updated. MERGE INTO knight target USING (SELECT k.kingdom_id , kki.knight_name , kki.allegiance_start_date AS start_date , kki.allegiance_end_date AS end_date FROM kingdom_knight_import kki INNER JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population) SOURCE ON (target.kingdom_allegiance_id = SOURCE.kingdom_id) WHEN MATCHED THEN UPDATE SET allegiance_start_date = SOURCE.start_date , allegiance_end_date = SOURCE.end_date WHEN NOT MATCHED THEN INSERT VALUES ( knight_s1.nextval , SOURCE.knight_name , SOURCE.kingdom_id , SOURCE.start_date , SOURCE.end_date); -- Save the changes. COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK TO starting_point; RETURN; END; / |
Step #4 : Test the Process
You can test it by calling the procedure. Rerunning it will demonstrate that it doesn’t violate any of the rules.
EXECUTE upload_kingdom; |
As always, I hope this is useful to somebody besides me.
MySQL Stored Procedure
A friend, who’s a MySQL-only developer, asked me a question that I couldn’t resist sharing. He wanted to know how to simplify his PHP code by making a single call to the database for a set of inserts to multiple tables.
You can imagine my delight when I got to say a stored procedure. He said, “It’s not Oracle; that won’t work.” I said, “MySQL supports stored procedures too.” Then, he asked how do you do that. Since the reference manual is a bit sparse on that, here’s a quick example.
Here are the detailed steps, even though there are some other blog entries with more information on these topics.
1. Sign on as the root user, if you need more on that go here. Create a database – storeddb:
CREATE DATABASE storeddb; |
2. Create a user and grant them permissions:
mysql> CREATE USER 'student' IDENTIFIED BY 'student'; mysql> GRANT ALL ON storeddb.* TO 'student'@'localhost' IDENTIFIED BY 'student'; |
3. Create a create_mysql_procedure.sql file with the following contents:
-- Select the database. USE storeddb; -- Conditionally drop the objects to make this rerunnable. DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; DROP PROCEDURE IF EXISTS double_insert; -- Create the tables. CREATE TABLE a ( a_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , a_text CHAR(12)); CREATE TABLE b ( b_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , a_id INT UNSIGNED , b_text CHAR(12)); -- Reset the execution delimiter to create a stored program. DELIMITER $$ -- Create a stored procedure. CREATE PROCEDURE double_insert ( input_a CHAR(12), input_b CHAR(12)) BEGIN START TRANSACTION; INSERT INTO a VALUES (NULL, input_a); INSERT INTO b VALUES (NULL, last_insert_id(), input_b); COMMIT; END; $$ -- Reset the delimiter to the default. DELIMITER ; -- Declare a couple local session variables. SET @text1 = 'This is one.'; SET @text2 = 'This is two.'; -- Call the local procedure. CALL double_insert(@text1,@text2); SELECT * FROM a; SELECT * FROM b; |
4. Quit the session as the root user.
mysql> QUIT; |
5. Sign on as the student user.
C:\> mysql -ustudent -pstudent |
6. As the student user, source the file. You have two ways to do that. One leverage csh/Tcsh shell syntax and the other uses Bourne, Korn, or BASH shell syntax.
6(a). The csh/Tcsh syntax:
mysql> SOURCE create_mysql_procedure.sql |
6(b). The Bourne, Korn, or BASH syntax:
mysql> \. create_mysql_procedure.sql |
When you source it, you should see the following. Don’t worry if you see the three warnings because when you rerun the script they won’t be there. There telling you that the tables didn’t exist to be dropped the first time.
DATABASE changed Query OK, 0 ROWS affected, 1 warning (0.00 sec) Query OK, 0 ROWS affected, 1 warning (0.00 sec) Query OK, 0 ROWS affected, 1 warning (0.00 sec) Query OK, 0 ROWS affected (0.14 sec) Query OK, 0 ROWS affected (0.00 sec) Query OK, 0 ROWS affected (0.08 sec) Query OK, 0 ROWS affected (0.00 sec) Query OK, 0 ROWS affected (0.00 sec) Query OK, 0 ROWS affected (0.09 sec) +------+--------------+ | a_id | a_text | +------+--------------+ | 1 | This IS one. | +------+--------------+ 1 ROW IN SET (0.00 sec) +------+------+--------------+ | b_id | a_id | b_text | +------+------+--------------+ | 1 | 1 | This IS two. | +------+------+--------------+ 1 ROW IN SET (0.00 sec) |
A quick note to those new to automatic numbering in MySQL. You use a null when you don’t want to write an override signature for the INSERT statement. You can find more on SQL Automated Numbering for Oracle, MySQL, and SQL Server in this blog post.
7. Write the following PHP program, and name it call_mysql_procedure.php. We’re going to call it from the command line, but you shouldn’t have to modify it when you call it from a browser.
<?php // Attempt to connect to your database. $c = @mysqli_connect("localhost", "student", "student", "storeddb"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // Declare two variables for the test procedure call. $val1 = "Hello Hal!"; $val2 = "Hello Dave!"; // Set the call statement, like a SQL statement. $sql = "CALL double_insert(?,?)"; // Prepare the statement and bind the two strings. if (mysqli_stmt_prepare($stmt, $sql)) { mysqli_stmt_bind_param($stmt, "ss", $val1, $val2); // Execute it and print success or failure message. $success = mysqli_stmt_execute($stmt); if ($success) { print "Congrats! You've executed a MySQL stored procedure from PHP!"; } else { print "Sorry, I can't do that Dave..."; } } } ?> |
8. Run the call_mysql_procedure.php from the command line, like this:
php call_mysql_procedure.php |
You should see the following message:
Congrats! You've executed a MySQL stored procedure from PHP! |
9. You can now connect to the MySQL database storeddb and re-query the tables a and b. You should see the following, which tells you that your PHP code worked.
mysql> SELECT * FROM a; +------+--------------+ | a_id | a_text | +------+--------------+ | 1 | This IS one. | | 2 | Hello Hal! | +------+--------------+ 2 ROWS IN SET (0.00 sec) mysql> SELECT * FROM b; +------+------+--------------+ | b_id | a_id | b_text | +------+------+--------------+ | 1 | 1 | This IS two. | | 2 | 2 | Hello Dave! | +------+------+--------------+ 2 ROWS IN SET (0.00 sec) |
I guess Alice that writing PL/SQL may actually be portable to other databases? Actually, there are some severe limits on loops in MySQL’s implementation but you can move basic stored procedures as of MySQL 5.1. Officially, MySQL 5.0 supports them.
As always, I hope this helps some folks.
Subscribe via RSS