Archive for the ‘Stored Procedures’ Category
PHP for MySQL Striped View
Back in May I explained how to create MySQL striped views with session variables. A couple folks wanted to know how to implement them through PHP programs. The trick is sharing the same connection between a call to the function before a query against the striped view.
I’ve updated the MySQL example beyond the Hobbit analogy from the prior post. It now uses the following database components:
- An
APPLICATION_USERtable - A striped
AUTHORIZED_USERview - A
FUNCTION_QUERYview to optimize function calls - A
SET_LOGINfunction - A
GET_LOGIN_ID function - A
GET_USER_IDfunction
The definition of the APPLICATION_USER table is:
CREATE TABLE application_user ( user_id int(10) unsigned PRIMARY KEY AUTO_INCREMENT , user_name varchar(20) NOT NULL , user_role varchar(20) NOT NULL , user_group_id int(10) unsigned NOT NULL , user_type int(10) unsigned NOT NULL , first_name varchar(20) , middle_name varchar(20) , last_name varchar(20) , created_by int(10) unsigned NOT NULL , creation_date datetime NOT NULL , last_updated_by int(10) unsigned NOT NULL , last_update_date datetime NOT NULL , CONSTRAINT natural_key UNIQUE (user_name) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; |
You should note that the natural key is a user-defined user name (mind you in reality it is often set by the application administrator). This guarantees that the authorize_cursor in the set_login function below always returns only one row.
The following seeds five rows in the APPLICATION_USER table:
INSERT INTO application_user VALUES ( null, 'potterhj', 'System Admin', 2, 1, 'Harry', 'James', 'Potter', 1, NOW(), 1, NOW()) ,( null, 'weasilyr', 'Guest', 1, 1, 'Ronald', null, 'Weasily', 1, NOW(), 1, NOW()) ,( null, 'longbottomn', 'Guest', 1, 1, 'Neville', null, 'Longbottom', 1, NOW(), 1, NOW()) ,( null, 'holmess', 'DBA', 3, 1, 'Sherlock', null, 'Holmes', 1, NOW(), 1, NOW()) ,( null, 'watsonj', 'DBA', 3, 1, 'John', 'H', 'Watson', 1, NOW(), 1, NOW()); |
Before creating the striped view, you should create the functions that set the session variables and query them. The set_login function sets two session variables when successful, which requires a user name that matches a valid value in the user_name column of the application_user table. The function returns an integer of 1 on success and 0 on failure. The set_login function 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 55 | CREATE FUNCTION set_login(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED BEGIN /* Declare a local variable to verify completion of the task: || ========================================================== || a. Default value is zero, which means false. || b. Non-default value is one, which means true. || ========================================================== */ DECLARE lv_success_flag INT UNSIGNED DEFAULT 0; /* Declare local variables to hold the return values from the cursor. */ DECLARE lv_login_id INT UNSIGNED; DECLARE lv_group_id INT UNSIGNED; /* Declare a condition variable for zero rows fetched, selected, or processed. */ DECLARE no_rows_fetched CONDITION FOR 1329; /* Declare a cursor to return an authorized user id. */ DECLARE authorize_cursor CURSOR FOR SELECT a.user_id , a.user_group_id FROM application_user a WHERE a.user_name = pv_login_name; /* Declare a handler for the cursor when it fails to return a row. */ DECLARE EXIT HANDLER FOR no_rows_fetched BEGIN /* The return statement when the function is aborted through an error. */ RETURN lv_success_flag; END; /* Check whether the input value is something other than a null value. */ IF pv_login_name IS NOT NULL THEN OPEN authorize_cursor; FETCH authorize_cursor INTO lv_login_id, lv_group_id; CLOSE authorize_cursor; /* Set the success flag. */ SET @sv_login_id := lv_login_id; SET @sv_group_id := lv_group_id; /* Check whether the session variables are set. */ IF NOT ISNULL(@sv_login_id) AND @sv_login_id > 0 AND NOT ISNULL(@sv_group_id) AND @sv_group_id > 0 THEN SET lv_success_flag := 1; END IF; END IF; /* Return the success flag. */ RETURN lv_success_flag; END; $$ |
The following GET_LOGIN_ID function returns the value from the @sv_login_id variable.
1 2 3 4 5 6 | CREATE FUNCTION get_login_id() RETURNS INT UNSIGNED BEGIN /* Return the success flag. */ RETURN @sv_login_id; END; $$ |
The following GET_GROUP_ID function returns the value from the @sv_group_id variable.
1 2 3 4 5 6 | CREATE FUNCTION get_group_id() RETURNS INT UNSIGNED BEGIN /* Return the success flag. */ RETURN @sv_group_id; END; $$ |
Lastly, you create the MySQL striped AUTHORIZED_USER like this one. It looks ineffective because it includes four function calls to the get_group_id() and one to the get_login_id().
CREATE VIEW authorized_user AS SELECT au.user_id , au.user_name , au.user_role , CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name FROM application_user au WHERE (au.user_group_id = 1 AND au.user_group_id = get_group_id() AND au.user_id = get_login_id()) OR get_group_id() = 2 OR (get_group_id() > 2 AND au.user_group_id = get_group_id()); |
The prior view’s query lets you see the logic for the three types of access. You can eliminate the multiple function calls by using an inline view, like the following in a SQL statement:
CREATE VIEW authorized_user AS SELECT au.user_id , au.user_name , au.user_role , CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name FROM application_user au CROSS JOIN (SELECT get_login_id() AS login_id , get_group_id() AS group_id) fq WHERE (au.user_group_id = 1 AND au.user_group_id = fq.group_id AND au.user_id = fq.login_id) OR fq.group_id = 2 OR (fq.group_id > 2 AND au.user_group_id = fq.group_id); |
Unfortunately, the preceding query raises the following exception if you attempt to put it in a view:
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause |
MySQL raises the error because a SELECT statement can’t contain a subquery in the FROM clause, according to the Create View MySQL Reference material.
The solution to the limitation of the CREATE VIEW syntax requires that you breakup the SQL statement into queries, and put them into separate views. The following example shows the function_query view holding the function calls and the authorized_user view cross joining the function_query view.
CREATE VIEW function_query AS SELECT get_login_id() AS login_id , get_group_id() AS group_id; CREATE VIEW authorized_user AS SELECT au.user_id , au.user_name , au.user_role , CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name FROM application_user au CROSS JOIN function_query fq WHERE (au.user_group_id = 1 AND au.user_group_id = fq.group_id AND au.user_id = fq.login_id) OR fq.group_id = 2 OR (fq.group_id > 2 AND au.user_group_id = fq.group_id); |
The following PHP program calls the SET_LOGIN function before querying the AUTHORIZED_USER view. It uses a $_GET global parameter to simplify testing the concept but you should always run parameters through the $_POST global parameter. The $_GET and $_REQUEST global parameters are security risks.
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 | <html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
// Process the input parameter, which should be through a $_POST argument.
(isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = '';
// Assign credentials to connection.
$mysqli = new mysqli("localhost", "student", "student", "studentdb");
// Check for connection error and print message.
if ($mysqli->connect_errno) {
print $mysqli->connect_error."<br />";
print "Connection not established ...<br />";
}
else {
// Declare a dynamic function call.
$query = "SELECT set_login(?)";
// Attempt preparing statement.
if (!$stmt = $mysqli->prepare($query)) {
// Print failure to resolve query message.
print $mysqli->error."<br />";
print "Failed to resolve query ...<br />";
}
else {
// Bind variable to SQL statement and execute it.
$stmt->bind_param("s", $input);
$stmt->execute();
$stmt->close();
}
// Declare a static query.
$query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_user au" ;
// Loop through a result set until completed.
do {
// Attempt query and exit with failure before processing.
if (!$stmt = $mysqli->query($query)) {
// Print failure to resolve query message.
print $mysqli->error."<br />";
print "Failed to resolve query ...<br />";
}
else {
// Print the opening HTML table tag.
print '<table><tr><th class="ID">ID</th><th class="Label">User Name</th><th class="Label">User Role</th><th class="Label">Full Name</th></tr>';
// Fetch a row for processing.
while( $row = $stmt->fetch_row() ) {
// Print the opening HTML row tag.
print "<tr>";
// Loop through the row's columns.
for ($i = 0;$i < $mysqli->field_count;$i++) {
// Handle column one differently.
if ($i == 0)
print '<td class="ID">'.$row[$i]."</td>";
else
print '<td class="Label">'.$row[$i]."</td>";
}
// Print the closing HTML row tag.
print "</tr>";
}
}
} while( $mysqli->next_result());
// Print the closing HTML table tag.
print "</table>";
// Release connection resource.
$mysqli->close(); }
?>
</script>
</body>
</html> |
You can call this through a browser with the following type of URL. You have two possible striped values, and they are any user’s unique user name.
http://localhost/stripedquery1.php?user_name=potterhj |
It returns one row when the user isn’t in a privileged group, all rows when the user is the root privileged group and all rows for a privilege group when not in the root privileged group. Naturally, you can extend this level of individual and group membership.
You can test this in the web page or directly in MySQL. The MySQL test doesn’t require image files and thereby loads faster, which is why I’ve opted to show it to you that way.
- Test for the root privilege group:
SELECT set_login('potterhj'); |
You get the full five rows:
+---------+-------------+--------------+----------------------+ | user_id | user_name | user_role | full_name | +---------+-------------+--------------+----------------------+ | 1 | potterhj | System Admin | Potter, Harry James | | 2 | weasilyr | Guest | Weasily, Ronald | | 3 | longbottomn | Guest | Longbottom, Neville | | 4 | holmess | DBA | Sherlock, Holmes | | 5 | watsonj | DBA | John, Watson H | +---------+-------------+--------------+----------------------+ |
- Test for an individual in a non-privileged group:
SELECT set_login('weasilyr'); |
You get the single user’s row:
+---------+-----------+-----------+------------------+ | user_id | user_name | user_role | full_name | +---------+-----------+-----------+------------------+ | 2 | weasilyr | Guest | Weasily, Ronald | +---------+-----------+-----------+------------------+ |
- Test for a non-root privileged group:
SELECT set_login('holmess'); |
You get the two rows that belong to the non-root privileged group:
+---------+-----------+-----------+-------------------+ | user_id | user_name | user_role | full_name | +---------+-----------+-----------+-------------------+ | 4 | holmess | DBA | Holmes, Sherlock | | 5 | watsonj | DBA | Watson, John H | +---------+-----------+-----------+-------------------+ |
Naturally, it’s more effective to put these components into a function library. The trick to making this work in a session is to share the connection. The object approach to the MySQL Improved (mysqli) object holds the connection, and that’s whats passed in the following example files.
This is an optimistic setter function. It forks (starts) the MySQL SQL/PSM set_login function but it doesn’t wait see if it ran successfully. That’s the nature of optimistic programming solutions, and the principal problem with them.
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 | function set_login($mysqli, $user_name) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT set_login(?)"; // Attempt preparing statement. if (!$stmt = $mysqli->prepare($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Bind variable to SQL statement and execute it. $stmt->bind_param("s", $user_name); $stmt->execute(); $stmt->close(); $return = true; } // Return the string. return $return; } |
An optimistic setter function fails to synchronize behaviors between the PHP and MySQL coding levels. It should be rewritten to fork the MySQL SQL/PSM set_login function and evaluate it’s successful or unsuccessful completion, which makes it a pessimistic function.
There are two ways to solve this problem. One can write a wrapper that accesses the get_login_id stored function to confirm the session variable is set, and the other handles the return value from the native set_login_id stored function. The former requires knowledge of the internal workings of the database model, while the latter does not. That means the first is more tightly coupled than the latter.
The following set_login PHP function is rewritten to be pessimistic but dependent on a supplemental call to another get_login PHP function, which calls the get_login_id stored function in the MySQL Server:
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 | function set_login($mysqli, $user_name) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT set_login(?)"; // Attempt preparing statement. if (!$stmt = $mysqli->prepare($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Bind variable to SQL statement and execute it. $stmt->bind_param("s", $user_name); $stmt->execute(); $stmt->close(); // True only when query returns a row. if (get_login($mysqli)) { $return = true; } } // Return the string. return $return; } |
A more effective solution reads the return value from the set_login function, like this:
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 | function set_login($mysqli, $user_name) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT set_login(?)"; // Attempt preparing statement. if (!$stmt = $mysqli->prepare($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Bind variable to SQL statement and execute it. $stmt->bind_param("s", $user_name); // Attempt query and exit with failure before processing. if (!$stmt->execute()) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Fetch a row for processing. $result = $stmt->get_result(); $row = $result->fetch_array(MYSQLI_NUM); } // Close the statement cursor. $stmt->close(); // True only when query returns a row. if (!is_null($row[0]) && ($row[0] > 0)) { $return = true; } } // Return the string. return $return; } |
This is the getter function:
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 | function get_login($mysqli) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT get_login_id()"; // Attempt preparing statement. if (!$stmt = $mysqli->query($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Fetch a SQL statement. $row = $stmt->fetch_row(); // Close the statement. $stmt->close(); // True only when query returns a row. if (!is_null($row[0]) && ($row[0] > 0)) { $return = true; } } // Return the string. return $return; } |
The get_authorized_user PHP function gets and displays the table result from the authorized_user striped view:
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 | function get_authorized_user($mysqli) { // Define return string. $out = ''; // Declare a static query. $query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_user au" ; // Loop through a result set until completed. do { // Attempt query and exit with failure before processing. if (!$stmt = $mysqli->query($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Print the opening HTML table tag. $out .= '<table><tr><th class="ID">ID</th><th class="Label">User Name</th>' . '<th class="Label">User Role</th><th class="Label">Full Name</th></tr>'; // Fetch a row for processing. while( $row = $stmt->fetch_row() ) { // Print the opening HTML row tag. $out .= "<tr>"; // Loop through the row's columns. for ($i = 0;$i < $mysqli->field_count;$i++) { // Handle column one differently. if ($i == 0) $out .= '<td class="ID">'.$row[$i]."</td>"; else $out .= '<td class="Label">'.$row[$i]."</td>"; } // Print the closing HTML row tag. $out .= "</tr>"; } } } while( $mysqli->next_result()); // Print the closing HTML table tag. $out .= "</table>"; // Return an HTML table of the results. return $out; } |
This is the modified program using the functions:
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 | <style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
// Include the credentials file if omitted.
include_once("striping.inc");
// Process the input parameter, which should be through a $_POST argument.
(isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = '';
// Assign credentials to connection.
$mysqli = new mysqli("localhost", "student", "student", "studentdb");
// Check for connection error and print message.
if ($mysqli->connect_errno) {
print $mysqli->connect_error."<br />";
print "Connection not established ...<br />";
}
else {
// After setting the session variable, get the striped view result.
if (set_login($mysqli,$input)) {
// Print the authorized list.
print get_authorized_user($mysqli);
}
// Release connection resource.
$mysqli->close();
}
?>
</script>
</body>
</html> |
Hope this helps those implementing MySQL Striped tables.
Overloading Procedures
A student asked, “Can you successfully overload MySQL stored procedures, like PL/SQL does in stored packages?” I said, “MySQL doesn’t formally support overloading stored procedures, and doesn’t support packages like Oracle 11g. You can, however, mimic (or fake) overloading with prepared statements. It requires a careful combination of stored procedures and session variables.”
Unfortunately, they asked for an example. So I wrote this set of code. It uses queries as the dynamic statements to make it simple to test but you can substitute INSERT, UPDATE, or DELETE statements. I didn’t provide type validation in the example, which would be required for dates or date-timestamps.
It’s presented in steps with test programs at each level. If you see an opportunity to improve on any of the example components, leave a comment. As to whether I’d implement this in production code, the answer is no but I believe all possibilities should at least be explored.
Step #1
The first step requires defining a stored procedure that sets a session variable. It’s fairly straight forward, but remember to change those DELIMITER values when testing. (As a note, you don’t require a semicolon after the END statement of a stored function or procedure in MySQL. You only need the redefined DELIMITER, which is $$ in these examples.)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | DELIMITER $$ -- Drop the procedure if it exists. DROP PROCEDURE IF EXISTS set_session_var$$ -- Create the procedure. CREATE PROCEDURE set_session_var ( pv_session_name VARCHAR(32767) , pv_session_value VARCHAR(32767)) CONTAINS SQL BEGIN /* Insert statement with auto commit enabled. */ SET @sql := concat('SET',' ','@',pv_session_name,' := ','?'); SELECT @sql AS "SQL String"; PREPARE stmt FROM @sql; SET @sv_session_value := pv_session_value; EXECUTE stmt using @sv_session_value; DEALLOCATE PREPARE stmt; END; $$ |
The test case for the set_session_var is:
CALL set_session_var('sv_filter1','One'); CALL set_session_var('sv_filter2','Two'); SELECT @sv_filter1, @sv_filter2; |
Important note: If you call this from another stored program you can’t use the stmt variable name in the calling program’s scope.
Step #2
The second step exists because you can’t pass arrays in MySQL (a restriction that also exists for T-SQL in Microsoft SQL Server). You have to pass a varying list of parameters as a serialized string. This is often called flexible parameter passing, which many PHP programmers leverage outside the database (flexible parameter passing is covered in this PHP tutorial I wrote a while back).
The deserialize example does three things:
- Takes a serialized set of parameters and deserializes the parameters into a set of name-value pairs, which are stored by their name as session level variables.
- It records the number of name-value pairs written as session variables in a temporary table. The use of a temporary table is required because a MySQL stored function can’t hold a dynamic statement or a call to a procedure with a dynamic statement.
- The script checks for the existence of the table during execution and truncates it when found. It creates the table when it isn’t found. While it would be ideal to use a temporary table, they’re not disclosed in the the
information_schemaand therefore more tedious to manage. Creating the table in the MEMORY database avoids fragmenting the InnoDB tablespace.
Here’s the working code:
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 PROCEDURE deserialize ( pv_param_list VARCHAR(32767)) CONTAINS SQL BEGIN DECLARE lv_name VARCHAR(9) DEFAULT 'sv_filter'; DECLARE lv_length INT; DECLARE lv_start INT DEFAULT 1; DECLARE lv_end INT DEFAULT 1; DECLARE lv_counter INT DEFAULT 1; /* Skip when call parameter list is null or empty. */ IF NOT (ISNULL(pv_param_list) OR LENGTH(pv_param_list) = 0) THEN /* Read line by line on a line return character. */ parse: WHILE NOT (lv_end = 0) DO /* Check for line returns. */ SET lv_end := LOCATE(',',pv_param_list,lv_start); /* Check whether line return has been read. */ IF NOT lv_end = 0 THEN /* Reset the ending substring value. */ SET lv_end := LOCATE(',',pv_param_list,lv_start); CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,lv_end - lv_start)); ELSE /* Print the last substring with a semicolon. */ CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,LENGTH(pv_param_list))); END IF; /* Reset the beginning of the string. */ SET lv_start := lv_end + 1; SET lv_counter := lv_counter + 1; END WHILE parse; END IF; /* Check for a temporary table that holds a control variable, create the table if it doesn't exist, and remove rows from the table. */ IF EXISTS (SELECT null FROM information_schema.tables WHERE table_name = 'counter') THEN TRUNCATE TABLE counter; ELSE /* It would be ideal to use a temporary table here but then it's not recorded in the INFORMATION_SCHEMA and cleansing the temporary table is more tedious. */ CREATE TABLE counter ( counter INT ) ENGINE=MEMORY; END IF; /* Insert the counter value for a list of parameters. */ INSERT INTO counter VALUES ( lv_counter - 1 ); END; $$ |
Bill Karwin made a wonderful point about the overhead of looking up a table name and using a table rather than a temporary table. You can replace the logic above with a CONTINUE HANDLER and a temporary table. It should resolve the problems mentioned in the note but as mentioned earlier there are other solutions for production systems.
Bill also referenced a great post by Jay Pipes. It points out that there are serious problems scaling MySQL stored programs. This occurs because MySQL stored procedures are not compiled and stored in a global stored procedure cache, like Microsoft SQL Server or Oracle.
This deficit in MySQL certainly plays to the philosophy of developing everything in the application layer, suggested by Bill. At least, that is true when the application layer is co-resident on the server-tier as a Server Side Includes (SSI) library.
It does beg the question why Microsoft SQL Server and Oracle enable placing an efficient set of stored logic in the database server, effectively creating an application layer that can be split between an SSI library and the database server. After all, the server has been the most scalable component and the application layer can distribute to the middle-tier.
Today’s limitations on MySQL stored programs present a clear and compelling case toward their general avoidance and specific use for delivering a scalable solutions. Will that hold true beyond 2015 when licensing becomes completely proprietary to Oracle? I’d venture (a complete guess or hope on my part) a global stored procedure cache may be the future of MySQL procedures. It would certainly serve to distinguish MySQL from MariaDB and provide an incentive to remain on MySQL or to return from MariaDB to MySQL. Such a change would certainly make MySQL even more competitive against Microsoft SQL Server, which is the only rival purely relational database it competes against.
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 | CREATE PROCEDURE deserialize ( pv_param_list VARCHAR(32767)) CONTAINS SQL BEGIN DECLARE lv_name VARCHAR(9) DEFAULT 'sv_filter'; DECLARE lv_length INT; DECLARE lv_start INT DEFAULT 1; DECLARE lv_end INT DEFAULT 1; DECLARE lv_counter INT DEFAULT 1; DECLARE CONTINUE HANDLER FOR 1146 BEGIN /* Create a temporary table. */ CREATE TEMPORARY TABLE counter ( counter INT ) ENGINE=MEMORY; END; /* Skip when call parameter list is null or empty. */ IF NOT (ISNULL(pv_param_list) OR LENGTH(pv_param_list) = 0) THEN /* Read line by line on a line return character. */ parse: WHILE NOT (lv_end = 0) DO /* Check for line returns. */ SET lv_end := LOCATE(',',pv_param_list,lv_start); /* Check whether line return has been read. */ IF NOT lv_end = 0 THEN /* Reset the ending substring value. */ SET lv_end := LOCATE(',',pv_param_list,lv_start); CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,lv_end - lv_start)); ELSE /* Print the last substring with a semicolon. */ CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,LENGTH(pv_param_list))); END IF; /* Reset the beginning of the string. */ SET lv_start := lv_end + 1; SET lv_counter := lv_counter + 1; END WHILE parse; END IF; /* Truncate existing table. */ TRUNCATE TABLE counter; /* Insert the counter value for a list of parameters. */ INSERT INTO counter VALUES ( lv_counter - 1 ); END; $$ |
The test program for deserialize follows below. If you’re curious, those are the Italian version of one through four. 😉 I’ve a partiality for Italian after living in Italy for two years.
CALL deserialize('Uno,Due,Tre,Quattro'); SELECT counter AS "Parameter #" FROM counter; SELECT @sv_filter1, @sv_filter2, @sv_filter3, @sv_filter4; |
You can also eliminate the temporary table completely by replacing the counter with a @sv_counter session variable, as mentioned by Bill in his comment. The following is the version of deserialize uses a session variable.
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 | CREATE PROCEDURE deserialize ( pv_param_list VARCHAR(32767)) CONTAINS SQL BEGIN DECLARE lv_name VARCHAR(9) DEFAULT 'sv_filter'; DECLARE lv_length INT; DECLARE lv_start INT DEFAULT 1; DECLARE lv_end INT DEFAULT 1; /* Set a session variable to enable a calling scope to read it without a function return. */ SET @sv_counter := 1; /* Skip when call parameter list is null or empty. */ IF NOT (ISNULL(pv_param_list) OR LENGTH(pv_param_list) = 0) THEN /* Read line by line on a line return character. */ parse: WHILE NOT (lv_end = 0) DO /* Check for line returns. */ SET lv_end := LOCATE(',',pv_param_list,lv_start); /* Check whether line return has been read. */ IF NOT lv_end = 0 THEN /* Reset the ending substring value. */ SET lv_end := LOCATE(',',pv_param_list,lv_start); CALL set_session_var(CONCAT(lv_name,@sv_counter),SUBSTR(pv_param_list,lv_start,lv_end - lv_start)); ELSE /* Print the last substring with a semicolon. */ CALL set_session_var(CONCAT(lv_name,@sv_counter),SUBSTR(pv_param_list,lv_start,LENGTH(pv_param_list))); END IF; /* Reset the beginning of the string. */ SET lv_start := lv_end + 1; SET @sv_counter := @sv_counter + 1; END WHILE parse; END IF; /* Reduce by one for 1-based numbering of name elements. */ SET @sv_counter := @sv_counter - 1; END; $$ |
The test case changes for this version because there isn’t a table involved. You would use the following:
CALL deserialize('Uno,Due,Tre,Quattro'); SELECT @sv_filter1, @sv_filter2, @sv_filter3, @sv_filter4; |
Like the earlier procedure, there’s no exception handling but it wouldn’t be a bad idea to add it. Especially, if you’re using the new SIGNAL feature of MySQL 5.6, which is now backported to MySQL 5.5.
Step #3
The last step creates a store procedure that takes two parameters, a parameterized statement and a serialized list of parameters. This means you can build any statement on the fly as required. The example uses queries simply because they’re the easiest to demonstrate the process.
You should note that the prepared statement is dynamic_stmt not stmt because stmt is used inside the set_session_var procedure. That means using stmt in the calling scope program would impact the called scope program because they use the same identifier (more or less a namespace scope issue).
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 | CREATE PROCEDURE prepared_dml ( pv_query VARCHAR(32767) , pv_filter VARCHAR(32767)) CONTAINS SQL BEGIN /* Declare a local variable for the SQL statement. */ DECLARE dynamic_stmt VARCHAR(32767); DECLARE lv_counter INT DEFAULT 0; /* Cleanup the message passing table when a case is not found. */ DECLARE EXIT HANDLER FOR 1339 BEGIN /* Step #5: */ DEALLOCATE PREPARE dynamic_stmt; /* Cleanup the temporary table that exchanges data between procedures. */ DROP TABLE IF EXISTS counter; END; /* Step #1: ======== Set a session variable with two parameter markers. */ SET @SQL := pv_query; /* Verify query is not empty. */ IF NOT ISNULL(@SQL) THEN /* Step #2: ======== Dynamically allocated and run statement. */ PREPARE dynamic_stmt FROM @SQL; /* Step #3: ======== Assign the formal parameters to session variables because prepared statements require them. */ CALL deserialize(pv_filter); /* Secure the parameter count from a temporary table that exchanges data between procedures. */ SELECT counter INTO lv_counter FROM counter; /* Step #4: ======== Choose the appropriate overloaded prepared statement. */ CASE WHEN lv_counter = 0 THEN EXECUTE dynamic_stmt; WHEN lv_counter = 1 THEN EXECUTE dynamic_stmt USING @sv_filter1; WHEN lv_counter = 2 THEN EXECUTE dynamic_stmt USING @sv_filter1,@sv_filter2; END CASE; /* Step #5: */ DEALLOCATE PREPARE dynamic_stmt; /* Cleanup the temporary table that exchanges data between procedures. */ DROP TABLE IF EXISTS counter; END IF; END; $$ |
If you’re using the deserialize procedure that uses a temporary table, you should make the following changes prepared_dml procedure on line 19 and 62. They modify the DROP TABLE statement to a DROP TEMPORARY TABLE statement. Though the change isn’t technically required because a DROP TABLE works against temporary tables as well as ordinary tables, it generally clearer to those new to MySQL syntax.
17 18 19 | /* Cleanup the temporary table that exchanges data between procedures. */ DROP TEMPORARY TABLE IF EXISTS counter; |
The line 62 change:
60 61 62 | /* Cleanup the temporary table that exchanges data between procedures. */ DROP TEMPORARY TABLE IF EXISTS counter; |
It would be much nicer to avoid the CASE statement or an if-else-if block but prepared statements are limited on what SQL statements they support. For example, you can create or drop stored functions or procedures, and you can’t dispatch a dynamic statement as a dynamic statement. That means you can’t replace the CASE statement with something like this:
43 44 45 46 | SET @dsql := 'EXECUTE dynamic_stmt USING @sv_filter1, @sv_filter2'; PREPARE preparsed_stmt FROM @dsql; EXECUTE preparsed_stmt; DEALLOCATE PREPARE preparsed_stmt; |
Attempt that, and you’ll get the following message. Does that mean there’s hope it’ll become a feature and possibility? Or, that it isn’t recommended?
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet |
The session variable solution requires another version of the prepared_dml procedure, as shown below. As you can imagine, removing the table and replacing a session variable simplifies the prepared_dml stored procedure version.
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 | CREATE PROCEDURE prepared_dml ( pv_query VARCHAR(32767) , pv_filter VARCHAR(32767)) CONTAINS SQL BEGIN /* Declare a local variable for the SQL statement. */ DECLARE dynamic_stmt VARCHAR(32767); DECLARE lv_counter INT DEFAULT 0; /* Cleanup the message passing table when a case is not found. */ DECLARE EXIT HANDLER FOR 1339 BEGIN /* Step #5: */ DEALLOCATE PREPARE dynamic_stmt; END; /* Step #1: ======== Set a session variable with two parameter markers. */ SET @SQL := pv_query; /* Verify query is not empty. */ IF NOT ISNULL(@SQL) THEN /* Step #2: ======== Dynamically allocated and run statement. */ PREPARE dynamic_stmt FROM @SQL; /* Step #3: ======== Assign the formal parameters to session variables because prepared statements require them. */ CALL deserialize(pv_filter); /* Step #4: ======== Choose the appropriate overloaded prepared statement. */ CASE WHEN @sv_counter = 0 THEN EXECUTE dynamic_stmt; WHEN @sv_counter = 1 THEN EXECUTE dynamic_stmt USING @sv_filter1; WHEN @sv_counter = 2 THEN EXECUTE dynamic_stmt USING @sv_filter1,@sv_filter2; END CASE; /* Step #5: */ DEALLOCATE PREPARE dynamic_stmt; END IF; END; $$ |
The test programs check without a parameter and with one or two parameters, as you can see below.
SELECT 'Test Case #1 ...' AS "Statement"; SET @param1 := 'SELECT "Hello World"'; SET @param2 := ''; CALL prepared_dml(@param1,@param2); SELECT 'Test Case #2 ...' AS "Statement"; SET @param1 := 'SELECT item_title FROM item i WHERE item_title REGEXP ?'; SET @param2 := '^.*war.*$'; CALL prepared_dml(@param1,@param2); SELECT 'Test Case #3 ...' AS "Statement"; SET @param1 := 'SELECT common_lookup_type FROM common_lookup cl WHERE common_lookup_table REGEXP ? AND common_lookup_column REGEXP ?'; SET @param2 := 'item,item_type'; CALL prepared_dml(@param1,@param2); |
As always, I hope this helps those writing MySQL Stored Procedures. If you’re new to stored programs, you can find a chapter on writing them in my Oracle Database 11g & MySQL 5.6 Developer Handbook or you can use Guy Harrison’s MySQL Stored Procedure Programming.
MySQL Striped Views
A question came up today about how to stripe a MySQL view, and this post shows you how. Along with the question, there was a complaint about why you can’t use session variables in a view definition. It’s important to note two things: there’s a workaround and there’s an outstanding request to add lift the feature limitation in Bug 18433.
A striped view lets authorized users see only part of a table, and is how Oracle Database 11g sets up Virtual Private Databases. Oracle provides both schema (or database) level access and fine-grained control access. Fine grained control involves setting a special session variable during a user’s login. This is typically done by checking the rights in an Access Control List (ACL) and using an Oracle built-in package.
You can do more or less the same thing in MySQL by using stored functions. One function would set the session variable and the other would fetch the value for comparison in a view.
Most developers who try this initially meet failure because they try to embed the session variable inside the view, like this trivial example with Hobbits (can’t resist the example with the first installment from Peter Jackson out later this year):
1 2 | CREATE VIEW hobbit_v AS SELECT * FROM hobbit WHERE hobbit_name = @sv_login_name; |
The syntax is disallowed, as explained in the MySQL Reference 13.1.20 CREATE VIEW Syntax documentation. The attempt raises the following error message:
ERROR 1351 (HY000): VIEW's SELECT contains a variable or parameter |
The fix is quite simple, you write a function that sets the ACL value for the session and another that queries the ACL session value. For the example, I’ve written the SET_LOGIN_NAME and a GET_LOGIN_NAME functions. (If you’re new to stored programs, you can find a 58 page chapter on writing them in my Oracle Database 11g & MySQL 5.6 Developer Handbook or you can use Guy Harrison’s MySQL Stored Procedure Programming.)
You would call the SET_LOGIN_NAME when you connect to the MySQL database as the first thing to implement this type of architecture. You would define the function like the following. (Please note that the example includes all setup statements from the command line and should enable you cutting and pasting it. ;-)):
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 | -- Change the delimiter to something other than a semicolon. DELIMITER $$ -- Conditionally drop the function. DROP FUNCTION IF EXISTS set_login_name$$ -- Create the function. CREATE FUNCTION set_login_name(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED BEGIN /* Declare a local variable to verify completion of the task. */ DECLARE lv_success_flag INT UNSIGNED DEFAULT FALSE; /* Check whether the input value is something other than a null value. */ IF pv_login_name IS NOT NULL THEN /* Set the session variable and enable the success flag. */ SET @sv_login_name := pv_login_name; SET lv_success_flag := TRUE; END IF; /* Return the success flag. */ RETURN lv_success_flag; END; $$ -- Change the delimiter back to a semicolon. DELIMITER ; |
You can use a query to set and confirm action like this:
SELECT IF(set_login_name('Frodo')=TRUE,'Login Name Set','Login Name Not Set') AS "Login Name Status"; |
Or, you can use the actual number 1 in lieu of the TRUE, like this:
SELECT IF(set_login_name('Frodo')=1,'Login Name Set','Login Name Not Set') AS "Login Name Status"; |
Please check this older post on how MySQL manages logical constants and the realities of TRUE and FALSE constants. A more practical example in an API would be this, which returns zero when unset and one when set:
SELECT set_login_name('Frodo') AS "Login Name Status"; |
The getter function for this example, simply reads the current value of the MySQL session variable. Like the prior example, it’s ready to run too.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- Change the delimiter to something other than a semicolon. DELIMITER $$ -- Conditionally drop the function. DROP FUNCTION IF EXISTS get_login_name$$ -- Create the function. CREATE FUNCTION get_login_name() RETURNS VARCHAR(20) BEGIN /* Return the success flag. */ RETURN @sv_login_name; END; $$ -- Change the delimiter back to a semicolon. DELIMITER ; |
Before you test it, lets create a HOBBIT table, seed it with data, and create a HOBBIT_V view. They’re bundled together in the following microscript:
-- Conditionally drop the table. DROP TABLE IF EXISTS hobbit; -- Create the table. CREATE TABLE hobbit ( hobbit_id INT UNSIGNED , hobbit_name VARCHAR(20)); -- Seed two rows. INSERT INTO hobbit VALUES ( 1,'Bilbo'),( 1,'Frodo'); -- Conditionally drop the view. DROP VIEW IF EXISTS hobbit_v; -- Create the function-enabled view. CREATE VIEW hobbit_v AS SELECT * FROM hobbit WHERE hobbit_name = get_login_name(); |
A query to the table after setting the session variable will only return one row, the row with Frodo in the HOBBIT_NAME column. It also guarantees an unfiltered UPDATE statement against the view only updates the single row returned, like this:
UPDATE hobbit_v SET hobbit_id = 2; |
In a real solution, there are more steps. For example, you’d want your tables in one database, views in another, and functions and procedures in a library database. However, I hope this helps seed some ideas for those interested in creating fine-grained virtual private databases in MySQL with user-authenticated application controls.
Value or Reference?
IN-only mode) parameters and pass-by-reference (INOUT and OUT mode) parameters for stored procedures. The analogy that finally seemed to hit home for the students was linking the modes to the story of Alice in Wonderland.
Here’s the analogy and below is the code to support it:
“A pass-by-value parameter in a procedure is like sending an immutable copy of Alice into the rabbit hole, which means she can’t shrink, grow, or learn throughout the story; whereas, a pass-by-reference parameter in a procedure is like sending Alice into the rabbit hole where she can shrink, grow, fight the Jabberwocky, and learn things that make her life better when she exits the rabbit hole – consistent with the storyline of Alice’s revisit to Wonderland.”
The example code creates a stored procedure that accepts two parameters – one pass-by-value and one pass-by-reference. Inside the procedure there’s a local variable and a reassignment of value to the pass-by-reference parameter. It’s in this wonderland procedure (by the way don’t forget to manage the DELIMITER value when you test it):
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 | CREATE PROCEDURE wonderland ( IN pv_value_param VARCHAR(20) , INOUT pv_ref_param VARCHAR(20)) BEGIN /* Declare a variable. */ DECLARE lv_value_param VARCHAR(20); /* Query the local variable and reference parameter before changing values. */ SELECT 'On Entry' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter" , IFNULL(lv_value_param,' ') AS "Local Variable"; /* Assign a lowercase value parameter to a local variable. */ SET lv_value_param := LOWER(pv_value_param); /* Assign a uppercase reference parameter value to the reference parameter. */ SET pv_ref_param := UPPER(pv_ref_param); /* Query the local variable and reference parameter after changing values. */ SELECT 'On Exit ' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter" , IFNULL(lv_value_param,' ') AS "Local Variable"; END; $$ |
A tester procedure than tests how the pass-by-value and pass-by-reference modes of operation differ. It’s here:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE PROCEDURE tester ( IN pv_value_param VARCHAR(20) , IN pv_ref_param VARCHAR(20)) BEGIN /* Query the local and reference parameters. */ SELECT 'Before ' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter"; /* Call the wonderland procedure that changes the pass-by-reference parameter. */ CALL wonderland(pv_value_param, pv_ref_param); /* Query the local and reference parameters. */ SELECT 'After ' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter"; END; $$ |
You call the tester program with this syntax:
CALL tester('Alice','Alice'); |
The test case returns the following values:
+----------+-----------------+---------------------+ | Where | Value Parameter | Reference Parameter | +----------+-----------------+---------------------+ | Before | Alice | Alice | +----------+-----------------+---------------------+ 1 row in set (0.00 sec) +----------+-----------------+---------------------+----------------+ | Where | Value Parameter | Reference Parameter | Local Variable | +----------+-----------------+---------------------+----------------+ | On Entry | Alice | Alice | | +----------+-----------------+---------------------+----------------+ 1 row in set (0.02 sec) +----------+-----------------+---------------------+----------------+ | Where | Value Parameter | Reference Parameter | Local Variable | +----------+-----------------+---------------------+----------------+ | On Exit | Alice | ALICE | alice | +----------+-----------------+---------------------+----------------+ 1 row in set (0.02 sec) +----------+-----------------+---------------------+ | Where | Value Parameter | Reference Parameter | +----------+-----------------+---------------------+ | After | Alice | ALICE | +----------+-----------------+---------------------+ 1 row in set (0.03 sec) |
Basically, Alice inside the pv_ref_param parameter grows to uppercase during the trip through the wonderland procedure, while Alice inside the pv_value_param remains unchanged. If it didn’t help you learn a principle, maybe it gave you a laugh on how to view the travels of IN-only and INOUT parameters. 😉
NOTE: Line 12 in the alice procedure is impossible with an immutable variable because the value of a call parameter to an immutable IN-only formal parameter shouldn’t allow the call parameter value to change during the execution of the program. This means that MySQL IN-only mode parameter values actually hold a mutable copy of the call parameter and the call parameter can be either a variable or literal value. The parameter value is discarded at the completion of procedure. This is more easily demonstrated with this assignment procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE PROCEDURE assignment ( IN pv_value_param VARCHAR(20)) BEGIN /* Query the local and reference parameters. */ SELECT 'Before ' AS "Where" , pv_value_param AS "Value Parameter"; /* Call the wonderland procedure that changes the pass-by-reference parameter. */ SET pv_value_param := UPPER(pv_value_param); /* Query the local and reference parameters. */ SELECT 'After ' AS "Where" , pv_value_param AS "Value Parameter"; END; $$ |
Therefore a call like this prints an uppercase INBOUND string inside the program but can return nothing to the calling scope since the call parameter is a string literal.
SET @sv_session = 'inbound'; CALL assignment(@sv_session); SELECT @sv_test AS "Current Value"; |
The query displays:
+---------------+ | Current Value | +---------------+ | inbound | +---------------+ |
I hope this helps.
Updating Table View Columns
Answering a reader’s question: How can you sort data inside an Oracle table view column? This blog post shows you how to perform the trick, but for the record I’m not a fan of nested tables. A table view column is an Oracle specific user-defined type (UDT), and is nested table or varray of a scalar data type.
Oracle’s assigned a formal name to this type of UDT. It’s now labeled an Attribute Data Type (ADT). The ADT doesn’t allow you to update nested elements outside of PL/SQL program units.
This blog post reviews table view columns, and extends concepts from Oracle Database 11g & MySQL 5.6 Developer Handbook
Before you implement table view columns, you should answer two design questions and one relational modeling principal. You should also understand that this direction isn’t portable across database implementations. It currently supported fully by the Oracle database and mostly by PostgreSQL database. You can find how to join nested tables helpful in understanding the UPDATE statements used in this posting, and this earlier post on UPDATE and DELETE statements.
Design Questions:
- Should you implement full object types with access methods in PL/SQL? The object type solution says there is no value in the nested data outside of the complete object. While choosing the table view column solution says that there is value to just implementing a nested list without element handling methods.
- Should you embed the elements in an XML_TYPE? An XML solution supports hierarchical node structures more naturally, like when you only access child nodes through the parent node. While choosing the table view column solution says that you want to avoid the XML Software Development Kit and that the data set is small and more manageable in a table view column.
Design Principle:
- Should you implement an ID-dependent relational modeling concept? An ID-dependent model replaces the primary and foreign keys with the relative position of parent and child elements. This is the design adopted when you choose a table view column, and it is more complex than single subject relational tables.
You should note that table view columns are inherently static at creation. You must also update the entire nested table view column when using Oracle SQL. Oracle SQL does let you modified attributes of object types in nested tables, as qualified in my new book (page 252).
Any attempt to modify a table view column element in SQL raises an ORA-25015 error. The error message states that (you) cannot perform DML on this nested TABLE VIEW COLUMN.
You can update the table view column value by replacing it with a new collection, and that’s done with a PL/SQL function. This type of function preserves the ordered list in the table view column by finding and replacing an element in the collection.
Unfortunately, developers who use nested tables typically design table view columns with an internal ordering scheme. That means the collection is ordered during insert or update. This type of design relies on the fact that you can’t change the order without re-writing the stored structure.
While common for those you use these, it is a bad practice to rely on the ordering of elements in a collection. At least, it’s a bad practice when we’re trying to work within the relational model. All that aside, here’s how you ensure element updates while preserving element position:
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 | CREATE OR REPLACE FUNCTION update_collection ( old_element_collection STREET_LIST , old_element_value VARCHAR2 , new_element_value VARCHAR2 ) RETURN STREET_LIST IS -- Declare and initial a new counter. lv_counter NUMBER := 1; -- Declare local return collection variable. lv_element_collection STREET_LIST := street_list(); BEGIN FOR i IN 1..old_element_collection.COUNT LOOP IF NOT old_element_collection(i) = old_element_value THEN lv_element_collection.EXTEND; lv_element_collection(lv_counter) := old_element_collection(i); ELSE lv_element_collection.EXTEND; lv_element_collection(lv_counter) := new_element_value; END IF; lv_counter := lv_counter + 1; END LOOP; RETURN lv_element_collection; END update_collection; / |
Then, you can use the user-defined function (UDF) inside a SQL UPDATE statement, like this:
1 2 3 4 5 6 | UPDATE TABLE (SELECT e.home_address FROM employee e WHERE e.employee_id = 1) e SET e.street_address = update_collection(e.street_address, 'Suite 525','Suite 522') , e.city = 'Oakland' WHERE e.address_id = 1; |
The UPDATE_COLLECTION function replaces Suite 525 with Suite 522, and preserves the sequence of elements in a new nested table. The UPDATE statement assigns the modified nested table to the table view column. You can find the code to create the employee table in Chapter 6 (pages 148-149), and the code to insert the default data in Chapter 8 (page 229) of Oracle Database 11g & MySQL 5.6.
The lv_counter variable could be replaced with a reference to the for loop’s iterator (i) because the counts of both collections are the same. I opted for the local variable to make the code easier to read.
While common for those you use these, it is a bad practice to rely on the ordering of elements in a collection. At least, it’s a bad practice when we’re trying to work within the relational model. Along the same line of thought, you also have the ability of removing elements from a table view column with a similar PL/SQL function. You could write the function like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | CREATE OR REPLACE FUNCTION delete_from_collection ( old_element_collection STREET_LIST , old_element_value VARCHAR2 ) RETURN STREET_LIST IS -- Declare and initial a new counter. lv_counter NUMBER := 1; -- Declare local return collection variable. lv_element_collection STREET_LIST := street_list(); BEGIN FOR i IN 1..old_element_collection.COUNT LOOP IF NOT old_element_collection(i) = old_element_value THEN lv_element_collection.EXTEND; lv_element_collection(lv_counter) := old_element_collection(i); lv_counter := lv_counter + 1; END IF; END LOOP; RETURN lv_element_collection; END delete_from_collection; / |
Then, you can use the user-defined function (UDF) to delete an element from the collection inside a SQL UPDATE statement, like this:
1 2 3 4 5 6 | UPDATE TABLE (SELECT e.home_address FROM employee1 e WHERE e.employee_id = 1) e SET e.street_address = delete_from_collection(e.street_address,'Suite 522') , e.city = 'Oakland' WHERE e.address_id = 1; |
After understanding all that, let’s examine how you sort data in a nested table or varray of a scalar data type (the basis of a table view column). The easiest way is a BULK COLLECT INTO statement nested inside a function, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE OR REPLACE FUNCTION sort_collection ( old_element_collection STREET_LIST) RETURN STREET_LIST IS -- Declare and initial a new counter. lv_counter NUMBER := 1; -- Declare local return collection variable. lv_element_collection STREET_LIST := street_list(); BEGIN -- Sort a collection alphabetically based on case sensitivity. SELECT column_value BULK COLLECT INTO lv_element_collection FROM TABLE(old_element_collection) ORDER BY column_value; RETURN lv_element_collection; END sort_collection; / |
You could test it with this:
1 2 | SELECT column_value FROM TABLE(sort_collection(street_list('Adams', 'Lewis', 'Clark', 'Fallon'))); |
Then, you can use the user-defined function (UDF) to update a table view column like this:
1 2 3 4 5 6 | UPDATE TABLE (SELECT e.home_address FROM employee1 e WHERE e.employee_id = 1) e SET e.street_address = sort_collection(e.street_address) , e.city = 'Oakland' WHERE e.address_id = 1; |
The funny thing about database solutions these days is that some Java developers don’t appreciate the simplicity of SQL and PL/SQL and would solve the problem with Java. Especially, if it was an case insensitive sort operation. That’s the hard way (easy way at the bottom), but I figured it should be thrown in because some folks think everything is generic if written in Java. Though, I thought making it proprietary would increase the irony and wrote it as a Java library for Oracle.
Here’s the Java library, which you can run from the SQL*Plus command line, SQL Developer, or that pricey Toad:
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 | CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortOracleList" AS // Import required classes. import java.io.*; import java.security.AccessControlException; import java.sql.*; import java.util.Arrays; import oracle.sql.driver.*; import oracle.sql.ArrayDescriptor; import oracle.sql.ARRAY; // Define class. public class DemoSort { public static ARRAY getList(oracle.sql.ARRAY list) throws SQLException, AccessControlException { // Convert Oracle data type to Java data type. String[] unsorted = (String[])list.getArray(); // Sort elements. Arrays.sort(unsorted, String.CASE_INSENSITIVE_ORDER); // Define a connection (this is for Oracle 11g). Connection conn = DriverManager.getConnection("jdbc:default:connection:"); // Declare a mapping to the schema-level SQL collection type. ArrayDescriptor arrayDescriptor = new ArrayDescriptor("STRINGLIST",conn); // Translate the Java String{} to the Oracle SQL collection type. ARRAY sorted = new ARRAY(arrayDescriptor,conn,((Object[])unsorted)); return sorted; }} / |
Then, you write the PL/SQL wrapper like this:
1 2 3 4 | CREATE OR REPLACE FUNCTION sortTable(list STRINGLIST) RETURN STRINGLIST IS LANGUAGE JAVA NAME 'DemoSort.getList(oracle.sql.ARRAY) return oracle.sql.ARRAY'; / |
You could test the case insensitive sort with this:
1 2 | SELECT column_value FROM TABLE(sort_collection(street_list('Adams', 'adams', 'Lewis', 'Clark', 'Fallon'))); |
Naturally, it ignores the fact you could do it like this without Java by using the UPPER function in the purely PL/SQL SORT_COLLECTION function shown earlier in this post:
12 13 14 15 | -- Sort a collection alphabetically based on case insensitive comparison. SELECT column_value BULK COLLECT INTO lv_element_collection FROM TABLE(old_element_collection) ORDER BY UPPER(column_value); |
Anyway, it’s a bunch of thoughts about writing solutions for table view columns. Hope it helps those interested in nested tables.
Oracle & MySQL Handbook
My new Oracle Database 11g & MySQL 5.6 Developer Handbook will be available at Oracle Open World 2011 (OOW2011). It’s a great book to compare and contrast approaches in Oracle and MySQL. It covers Oracle SQL & PL/SQL and MySQL SQL & SQL/PSM (Persistent Stored Modules – functions and procedures). Unfortunately, it seems like the book won’t be available on amazon.com until much later in the month, and not available from amazon.de until November. You can read about it at McGraw Hill Professional’s web site. They’ve made a special effort to get copies to OOW2011. Here’s the source code for the book because I don’t know when it’ll be on the publisher’s web site.
I’ll also be at OOW2011. They’ve scheduled me in the bookstore (probably 2nd floor of Moscone North, as usual) from 10 to 10:30 A.M. on Monday and Wednesday for a book signing. If you’re at OOW2011 and you like to stop by and say hello, I look forward to meeting you. Many folks leave comments on the posts but only a few suggest what they’d like me to write on when I’ve got a chance, and you can do that if you stop by to chat.
It’s also interesting to know how many folks use both Oracle and MySQL (any updates on that are always appreciated). Last year at the Bioinformatics and Computation Biology (ACM-BCB 2010) Conference in Niagara Falls, I found it interesting to discover how many pharmaceutical companies and national labs were using both Oracle and MySQL. They appeared consistent about using Oracle for their systems governed by legal compliance rules and MySQL for actual research.
The pharmaceutical companies also had clear barriers between the researchers and professional IT staff, specifically the DBAs. It seems that the DBAs don’t want to cede any control over installed Oracle instances, and they place barriers to research by denying additional Oracle instances when their site licenses would allow them to do so at no incremental cost. On the other hand, the DBAs are fine with letting researchers host and pilot with the MySQL Community Edition databases. This book supports those trying to figure out how to write portable SQL and how to port solutions from MySQL to Oracle and vice versa.
Hope to meet a few new folks at OOW2011. The Kindle version of the book became available 11/25/2011.
As an addendum to this original post, some folks asked for the summary of content for the new book, and the location of the errata (the errors of omission and commission in the book). Below is a summary of the book from page XVIII of the Introduction, and the errata is in the second comment to this post:
Part I: Development Components
- Chapter 1, “Architectures,” explains the Oracle 11g and MySQL 5.6 development architectures and highlights the comparative aspects of both client and server environments.
- Chapter 2, “Client Interfaces,” explains and demonstrates the basics of how you use SQL*Plus and MySQL Monitor client software.
- Chapter 3, “Security,” explains the security barriers for database servers and Data Control Language (DCL) commands that let you manage user and account privileges in the database servers.
- Chapter 4, “Transactions,” explains the nature of ACID-compliant transactions and the Two-phase Commit (2PC) process demonstrated by INSERT, UPDATE, and DELETE statements.
- Chapter 5, “Constraints,” explains the five primary database-level constraints and covers the check, not null, unique, primary key, and foreign key constraints.
Part II: SQL Development
- Chapter 6, “Creating Users and Structures,” explains how you can create users, databases, tables, sequences, and indexes.
- Chapter 7, “Modifying Users and Structures,” explains how you modify users, databases, tables, sequences, and indexes.
- Chapter 8, “Inserting Data,” explains how you insert data into tables.
- Chapter 9, “Updating Data,” explains how you update data in tables.
- Chapter 10, “Deleting Data,” explains how you delete data from tables.
- Chapter 11, “Querying Data,” explains how you query data from a single table, from a join of two or more tables, and from a join of two or more queries through set operators.
- Chapter 12, “Merging Data,” explains how you import denormalized data from external tables or source files and insert or update records in normalized tables.
Part III: Stored Program Development
- Chapter 13, “PL/SQL Basics,” explains the basics of using PL/SQL to write transactional blocks of code.
- Chapter 14, “SQL/PSM Basics,” explains the basics of using SQL/PSM to write transactional blocks of code.
- Chapter 15, “Triggers,” explains how to write database triggers in Oracle and MySQL databases.
Part IV: Appendix
- Appendix, Covers the answers to the mastery questions at the end of the chapters.
Create a synonym in MySQL
A friend wanted to know how to create a synonym to a library of stored functions and procedures in MySQL. I had to deliver the sad news that you can’t create a synonym in MySQL 5.0, 5.1, or 5.5. You need to write a wrapper function or procedure.
A synonym (available in Oracle) would eliminate the need to call a function from another database through the component selector (or period). That’s because it maps a name to the reference and name instead of requiring you to include it with each call. The following shows a call from outside of the lib database:
SELECT lib.demo('Hello World!') AS "Statement"; |
He asked how to do it, so here’s how you do it below. Assume the following for this example:
- You have a
libdatabase where you define all your functions, procedures, and tables. - You have a
appdatabase that supports web connections. - You don’t want to allow web users to connect directly to the
libdatabase because access to the tables should only be available through the functions and procedures.
This base function is simplified to avoid interaction with table data but illustrate the technique of definer rights functions. The function takes a string of up to 20 characters and returns it enclosed in double quotes. To mimic these behaviors, as the root user, you should create the app and lib databases, the dev and web users, and grant privileges to the dev user to act in the lib database and the web user to act in the app database. The example below does this as the root user, but in real life don’t use a trivial password like the example:
/* Create the two databases. */ CREATE DATABASE app; CREATE DATABASE lib; /* Create the two users, the developer can only connect locally. */ CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev'; CREATE USER 'web'@'%' IDENTIFIED BY 'web'; /* Grant privileges to be a definer in both databases. */ GRANT ALL ON app.* TO 'dev'@'localhost'; GRANT ALL ON lib.* TO 'dev'@'localhost'; /* Grant privileges to any function or privilege in the APP database to the WEB user. */ GRANT EXECUTE ON app.* TO 'web'@'%'; |
After creating and granting all the appropriate privileges, here are the steps to create the test case.
- You create and test the function as the
devuser in thelibdatabase.
/* Set the delimiter to something other than a semi-colon so they can be typed in statements. */ DELIMITER $$ /* Create a function that echoes back the string with double quotes. */ CREATE FUNCTION demo(str VARCHAR(20)) RETURNS VARCHAR(20) BEGIN RETURN CONCAT('"',str,'"'); END; $$ /* Reset the DELIMITER value. */ DELIMITER ; /* Query the function. */ SELECT demo('Ciao amico!') AS "Statement"; |
- You create and test the wrapper function as the
devuser in theappdatabase.
/* Set the delimiter to something other than a semi-colon so they can be typed in statements. */ DELIMITER $$ /* Create a function that echoes back the string with double quotes. */ CREATE FUNCTION demo(str VARCHAR(20)) RETURNS VARCHAR(20) BEGIN RETURN lib.demo(str); END; $$ /* Reset the DELIMITER value. */ DELIMITER ; /* Query the function. */ SELECT demo('Ciao amico!') AS "Statement"; |
If you’re wondering why a GRANT wasn’t required from the lib database to the app database, it’s because the dev user has access to both databases and defined both objects.
- You can test the wrapper function as the
webuser in theappdatabase.
SELECT demo('Yes, it works!') AS "Statement"; |
This is the closest to a synonym for a function or procedure that is possible. I know this solves his problem and hope it solves a couple others too.
PHP Database Authentication
A few years ago I wrote a couple articles showing how to use PHP to connect to an Oracle Database 10g Express Edition instance. They’re still there on Oracle’s Technical Network but the source files are missing. It appears that Oracle may have migrated the articles to a new server but failed to migrate the source files.
Don’t forget that you’ll need to Install the Zend Community Server. Then, you need to create an IDMGMT1, IDMGMT2, and IDMGMT3 users and run the create_identity_db2.sql or create_identity_db3.sql seeding script for both database validation models. You’ll find links to the original articles, the source code in zip files. The newer version with CSS is the IDMGMT3 code.
The CSS sign-on form looks like this:
The portal page looks like this:
The add new user page looks like this:
The source files are as follows:
- Database Authentication #1 – IdMgmt1.zip
- Database Authentication #2 – IdMgmt2.zip
- Database Authentication #3 – IdMgmt3.zip includes the CSS but works with the same write-up as Idmgmt2.
Hope this helps those who wanted the files.
Debugging MySQL Functions
Somebody, who read this post on Debugging MySQL Procedures, asked why the strategy of selecting a string literal didn’t work in a MySQL function. That’s easy, they’re not designed to support a SELECT statement, only a SELECT-INTO statement.
Why? That’s the purpose of a function to perform something and return a single reply.
That’s also why a MySQL functions only support the IN mode of operation for formal and call parameters. When formal parameters are restricted to in-mode-only operations, they implement a pass-by-value function model. This can also be expressed from the other side of the looking glass. In that case, MySQL functions don’t support pass-by-reference functions that use the INOUT or OUT mode operations.
If you put a SELECT statement in a function to print internal values or comments, it raises an error. Take for example the following attempt to create the debugging function with an echo of output (that works in stored procedures).
CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT '[Debug #1]'; RETURN 1; END; $$ |
It fails to create the function because you’ve violated a key integrity rule. It also raises the following error:
ERROR 1415 (0A000): Not allowed to return a result set from a function |
You have two potential solutions to this problem. The first is limited and inflexible. The second isn’t as limited or inflexible and is the recommended way to debug your functions without a tool. That’s to use a temporary table to record run-time debugging events.
Session Variable Debugging ↓
Expand this section to see the steps for debugging functions with session variables.
- Create two session level variables, like these:
SET @counter := 0; SET @msg := ''; |
- Create a function that uses the
SELECT-INTOstatement to collect and store debugging information during function execution.
CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT @counter + 1 INTO @counter; SELECT CONCAT('[Debug #',@counter,']') INTO @msg; RETURN 1; END; $$ |
- Run the function and then query the session variable for results
SELECT debugger(); SELECT @msg; |
You’ll see the following text:
+------------+ | @msg | +------------+ | [Debug #1] | +------------+ |
Temporary Table Debugging ↓
Expand this section to see the steps for debugging functions with session variables.
- Only when you want a counter, create one session level variables.
SET @counter := 0; |
- Create an in-memory table to store debugging information from function execution.
CREATE TABLE debugger ( debug_comment CHAR(80)) ENGINE=MEMORY; |
- Create a function that supports inserts into the in-memory table. Naturally, you may need to make the columns larger when your debugging results are large. I’ve found that 80 characters is generally adequate for most debugging exercises.
1 2 3 4 5 6 7 | CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT @counter + 1 INTO @counter; INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']')); RETURN 1; END; $$ |
- Call the function and query the debugging results.
SELECT debugger(); SELECT debugger(); SELECT debugger(); SELECT debug_comment FROM debugger; |
You’ll see the following text:
+---------------+ | debug_comment | +---------------+ | [Debug #1] | | [Debug #2] | | [Debug #3] | +---------------+ |
Complete Code Sample ↓
Expand this section to see the sample working code for all examples.
This script creates, runs, and tests the code from the above discussions.
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 | -- Conditionally drop the function when it exists. DROP FUNCTION IF EXISTS debugger; -- Set delimiter to create a function with semicolon statment terminators. DELIMITER $$ -- Create a function that returns 1. CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT '[Debug #1]'; RETURN 1; END; $$ -- Reset the delimiter to enable normal execution. DELIMITER ; -- Declare session level variables. SET @counter := 0; SET @msg := ''; -- Conditionally drop the function when it exists. DROP FUNCTION IF EXISTS debugger; -- Set delimiter to create a function with semicolon statment terminators. DELIMITER $$ -- Create a function that writes to local session variables. CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT @counter + 1 INTO @counter; SELECT CONCAT('[Debug #',@counter,']') INTO @msg; RETURN 1; END; $$ -- Reset the delimiter to enable normal execution. DELIMITER ; -- Test the function code and read the session-level variable contents. SELECT debugger(); SELECT @msg; -- Declare a session level variable. SET @counter := 0; -- Conditionally drop the function when it exists. DROP TABLE IF EXISTS debugger; -- Create a temporary (in-memory) table to record debugging information. CREATE TABLE debugger ( debug_comment CHAR(80)) ENGINE=MEMORY; -- Conditionally drop the function when it exists. DROP FUNCTION IF EXISTS debugger; -- Set delimiter to create a function with semicolon statment terminators. DELIMITER $$ -- Create a function that writes to a debugging table. CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT @counter + 1 INTO @counter; INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']')); RETURN 1; END; $$ -- Reset the delimiter to enable normal execution. DELIMITER ; -- Test the function code and read the session-level variable contents. SELECT debugger(); SELECT debugger(); SELECT debugger(); SELECT debug_comment FROM debugger; |
Debugging MySQL Procedures
In my second database class we focus on PL/SQL but we’ve begun highlighting the alternatives in MySQL and SQL Server. A student asked how they could debug runtime variable values in a MySQL Stored Procedure (or subroutines according to some documentation). You can see this post for how to create an equivalent solutions for MySQL functions.
In Oracle, we debug with the DBMS_OUTPUT package. Packages, like DBMS_OUTPUT hold related functions and procedures, and are a corollary to System.out.println() in Java.
Before you can see the output at the command-line in Oracle (that is if you’re not using SQL*Developer or Toad), you must set a SQL*Plus environment variable. These variables don’t exist in MySQL or SQL Server command-line tools because they never served the function of a report writer like SQL*Plus.
You enable output display in Oracle by setting this in SQL*Plus:
SQL> SET SERVEROUTPUT ON SIZE 1000000 |
You can test your anonymous or named block. Since MySQL doesn’t support anonymous named block, the examples using a trivial procedure that prints Hello World! (orginal, right ;-)).
1 2 3 4 5 6 7 8 9 10 11 12 | -- Create a procedure in Oracle. CREATE OR REPLACE PROCEDURE hello_world IS BEGIN -- Print a word without a line return. DBMS_OUTPUT.put('Hello '); -- Print the rest of the phrase and a line return. DBMS_OUTPUT.put_line('World!'); END; / -- Call the procedure. EXECUTE hello_world; |
It’s seems useless to print the output because it should be evident. MySQL procedures are a bit different because there’s no OR REPLACE syntax. The equivalent to calling the DBMS_OUTPUT package procedures in MySQL is to simply select a string. Now you can do this with or without the FROM dual clause in MySQL, don’t we wish we could do the same thing in Oracle. 🙂
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | -- Conditionally drop the procedure. SELECT 'DROP PROCEDURE hello_world' AS "Statement"; DROP PROCEDURE IF EXISTS hello_world; -- Reset the delimiter to write a procedure. DELIMITER $$ -- Create a procedure in Oracle. CREATE PROCEDURE hello_world() BEGIN -- Print the phrase and a line return. SELECT 'Hello World!'; END; $$ -- Reset the delimiter back to a semicolon to work again. DELIMITER ; -- Call the procedure. SELECT 'CALL hello_world' AS "Statement"; CALL hello_world(); |
Originally, I tried to keep this short but somebody wanted an example in a loop. Ouch, loops are so verbose in MySQL. Since I was modifying this post, it seemed like a good idea to put down some guidelines for successful development too.
Guidelines for Development of Procedures
Declaration Guidelines
The sequencing of components in MySQL procedures is important. Unlike, PL/SQL, there’s no declaration block, declarations must be at the top of the execution block. They also must appear in the following order:
- Variable declarations must go first, you can assign initial values with the
DEFAULTkeyword. While not required, you should:
- Consider using something like
lv_to identify them as local variables for clarity and support of your code. - Consider grouping local variables that relate to handlers at the bottom of the list of variables.
- After local variables and before handlers, you put your cursor definitions. You should note that MySQL doesn’t support explicit dynamic cursors, which means you can’t define one with a formal signature. However, you do have prepared statements and they mimic dynamic cursor behaviors.
- Last in your declaration block, you declare your handler events.
Execution Guidelines
- Variable assignments are made one of two ways:
- You should start each execution block with a
START TRANSACTIONand then aSAVEPOINT, which ensures the procedure acts like a cohesive programming unit. - You assign a
left_operand = right_operand;as a statement. - You use the
SELECT column INTO variable;syntax to filter a value through SQL functions and assign the resulting expression to a local variable. - You assign a single row cursor output to variables using a
SELECT column INTO variable FROM ....
- You must assign values from cursors called in a loop into local variables when you want to use the results in nested SQL statements or loops.
- You must reset looping variables, like the
fetchedcontrol variable at the end of the loop to reuse the handler variable in subsequent loops. - You must assign values to local variables if you want to use them in the exception handler.
- If you’ve started a transaction, don’t forget to
COMMITyour work.
Exception Guidelines
- Leave out the exception handler until you’ve tested all outcomes, and make sure you document them and add them as potential handlers.
- When you deploy exception blocks, they’re the last element at the bottom of the exception block.
- You should consider explicit exception handlers for each error unless the action taken is the same.
- You should consider grouping all exception handlers when the action taken is the same.
- You should include a
ROLLBACKwhenever you’ve performed two or more SQL statements that may modify data.
Below is an example for putting debug code inside a loop.
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 | -- Conditionally drop a sample table. SELECT 'DROP TABLE IF EXISTS sample' AS "Statement"; DROP TABLE IF EXISTS sample; -- Create a table. CREATE TABLE sample ( sample_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , sample_msg VARCHAR(20)); -- Insert into sample. INSERT INTO sample (sample_msg) VALUES ('Message #1') ,('Message #2') ,('Message #3'); -- Conditionally drop the procedure. SELECT 'DROP PROCEDURE debug_loop' AS "Statement"; DROP PROCEDURE IF EXISTS debug_loop; -- Reset the delimiter to write a procedure. DELIMITER $$ -- Create a procedure in Oracle. CREATE PROCEDURE debug_loop() BEGIN /* Declare a counter variable. */ DECLARE lv_counter INT DEFAULT 1; /* Declare local control loop variables. */ DECLARE lv_sample_id INT; DECLARE lv_sample_msg VARCHAR(20); /* Declare a local variable for a subsequent handler. */ DECLARE duplicate_key INT DEFAULT 0; DECLARE fetched INT DEFAULT 0; /* Declare a SQL cursor fabricated from local variables. */ DECLARE sample_cursor CURSOR FOR SELECT * FROM sample; /* Declare a duplicate key handler */ DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_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 all_or_none; /* Open a sample cursor. */ OPEN sample_cursor; cursor_sample: LOOP /* Fetch a row at a time. */ FETCH sample_cursor INTO lv_sample_id , lv_sample_msg; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_sample; END IF; -- Print the cursor values. SELECT CONCAT('Row #',lv_counter,' [',lv_sample_id,'][',lv_sample_msg,']') AS "Rows"; -- Increment counter variable. SET lv_counter = lv_counter + 1; END LOOP cursor_sample; CLOSE sample_cursor; /* 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; END; $$ -- Reset the delimiter back to a semicolon to work again. DELIMITER ; -- Call the procedure. SELECT 'CALL debug_loop' AS "Statement"; CALL debug_loop(); |
This post certainly answers the student question. Hopefully, it also helps other who must migrate Oracle skills to MySQL. Since IBM DB2 has introduced a PL/SQL equivalent, wouldn’t it be nice if Oracle did that for MySQL. That is, migrate PL/SQL to MySQL. Don’t tell me if you think that’s a pipe dream, I’d like to hope for that change.
Subscribe via RSS