Archive for February, 2010
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.
MySQL Boolean Data Type
Somebody posted a quick question about the outcome of defining a table with a bool data type in PHPMyAdmin. They were subsequently surprised when they checked the MySQL database and found that it was actually a tinyint(1). The natural question they had was: “What do you enter – true/false or 1/0?”
I promised to post an answer tonight, and morning counts too. You can enter a true or false because they’re synonyms for a 1 or 0 respectively. TINYINT is the supported data type, and BIT, BOOL, and BOOLEAN are synonyms for the base data type.
Here’s an example in MySQL:
mysql> CREATE TABLE data_type (TYPE bool); mysql> DESCRIBE data_type; +-------+------------+------+-----+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | Extra | +-------+------------+------+-----+---------+-------+ | TYPE | tinyint(1) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ mysql> INSERT INTO data_type VALUES (TRUE),(FALSE); mysql> SELECT * FROM data_type; +------+ | TYPE | +------+ | 1 | | 0 | +------+ |
The comment below raises the question of what happens with values in the valid range of TINYINT that aren’t 0 or 1, like 5. The simple answer is they’re not valid when compared against the true and false constants, as you can see by creating the following example.
-- Create a test table. CREATE TABLE verify ( verify_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , range_1 tinyint UNSIGNED , range_2 tinyint ); -- Insert test values. INSERT INTO verify VALUES (NULL, 0, 0) ,(NULL, 1, 1) ,(NULL, 1,-1) ,(NULL, 2, 2); -- Query results. SELECT range_1 AS "Value" , CASE WHEN range_1 = TRUE THEN 'True' WHEN range_1 = FALSE THEN 'False' ELSE 'Invalid' END AS "Unsigned" , range_2 AS "Value" , CASE WHEN range_2 = TRUE THEN 'True' WHEN range_2 = FALSE THEN 'False' ELSE 'Invalid' END AS "Signed" FROM verify; |
The results of the test demonstrate that only a 0 or 1 value validates against the false or true constants, as shown:
+-------+----------+-------+---------+ | Value | Unsigned | Value | Signed | +-------+----------+-------+---------+ | 0 | False | 0 | False | | 1 | True | 1 | True | | 1 | True | -1 | Invalid | | 2 | Invalid | 2 | Invalid | +-------+----------+-------+---------+ |
Based on the comment, the 256 value range of TINYINT is found here.
SQL Aggregation Tutorial
I’ve been working on a Basic Aggregation tutorial for my students. I think this might be close to what may benefit them. However, I thought it would be great to put it out there and solicit ideas. If you have some on improving this post, please let me know.
My first take at the post …
This is a lesson on basic aggregation in SQL. Aggregation in SQL means counting, adding, and grouping by results of counts or sums. Aggregation is a critical part of using the SQL language. At a basic level, aggregation includes the COUNT, SUM, AVERAGE, MAX, and MIN aggregation functions; and the ORDER BY, GROUP BY, and HAVING clauses.
You’ll find the setup scripts for these examples at the bottom of this blog page. The best way to use this page is to copy the setup code, run it in your database, and then test the examples as you work though them.
Data set ↓
This section discusses the data set. You unfold it by clicking on the bold text above.
The illustration of aggregation is a challenge because a trivial set of numbers doesn’t present much challenge or sometimes relevance but it shows the basic concept. A more applied sample set makes mental calculation and comprehension of functions more difficult. As a result of that trade off, this uses two copies of the ordinal numbers (ordinal numbers are the single digit integers from zero to nine).
After you run the setup script, you can display the data set with the following query.
-- Oracle only SQL*Plus formatting command to smooth output display. SET PAGESIZE 99 -- Query ordered by the sets and then the integers in ascending order. SELECT * FROM ordinal ORDER BY 2, 4; |
You could have sorted them more easily by using the ORDINAL_ID surrogate key column but it wouldn’t demonstrate the ORDER BY clause. In some database, like Oracle and MySQL, the ORDER BY lets you use column names, column aliases, or the position order of the column in the SELECT clause. Unfortunately, this isn’t true across all databases.
The ORDER BY clause does let you see that you can set nested ordering, like the numbers inside the data sets that are qualified by the LIST_SET column. This is demonstrated in the output below.
ORDINAL_ID LIST_SET LIST_NAME LIST_VALUE
---------- -------------------- ---------- ----------
1 Value Set A Zero 0
2 Value Set A One 1
3 Value Set A Two 2
4 Value Set A Three 3
5 Value Set A Four 4
6 Value Set A Five 5
7 Value Set A Six 6
8 Value Set A Seven 7
9 Value Set A Eight 8
10 Value Set A Nine 9
11 Value Set A
12 Value Set B Zero 0
13 Value Set B One 1
14 Value Set B Two 2
15 Value Set B Three 3
16 Value Set B Four 4
17 Value Set B Five 5
18 Value Set B Six 6
19 Value Set B Seven 7
20 Value Set B Eight 8
21 Value Set B Nine 9
22 Value Set B |
Single row aggregation function use ↓
This section discusses and demonstrates the five aggregation functions in the post. They return a single row of data. Like the other sections, you unfold this by clicking on the bold text above.
COUNT function
The COUNT function has two behaviors that may apply. One is when you count rows and the other is when you count values. Both return only a single row.
Counting by Reference: The following example demonstrates counting rows. In this scenario, the COUNT function takes a single call parameter, which is an asterisk (*). The asterisk actually counts the references to the rows in the table, and in Oracle is equivalent to COUNT(ROWID). The pseudo column ROWID points to the physical block address where the row is stored in the database. As such, the asterisk acts very much like a pointer in the C or C++ language (that is if you substitute a block address for a memory address).
SELECT COUNT(*) FROM ordinal; |
It returns
COUNT(*) ---------- 22 |
When you count rows, you count them whether they contain values or not. It is possible that you could insert a meaningless row with null values for all columns in a table that’s unconstrained. Moreover, the COUNT function counts all rows no matter whether contain any values.
Counting by Value: The following examples demonstrate approaches to counting values. It’s important to note that when you count values, you ignore null values. You can also count distinct things or all things when you count by value.
The LIST_SET column doesn’t contain any null values (as you can see by inspecting the data set early in this blog page). The following counts the number of values in the LIST_SET column. You could also substitute COUNT(ALL list_set) and it would return the same thing because ALL is the default and always provided unless you override it with a DISTINCT keyword.
SELECT COUNT(list_set) FROM ordinal; |
It returns:
COUNT(*) ---------- 22 |
Since the LIST_SET column only contains one of two values, you can count that to make sure. You do it with the following syntax:
SELECT COUNT(DISTINCT list_set) FROM ordinal; |
It returns:
COUNT(*) ---------- 2 |
The LIST_NAME and LIST_VALUE both contain two null values. Let’s see what they return when we count all or distinct values. This example simplifies it a step more by performing both counts side-by-side. This is possible because both columns return a single row.
SELECT COUNT(ALL list_name) AS name_number , COUNT(DISTINCT list_value) AS value_number FROM ordinal; |
It returns the twenty total string values found in the first column and the ten unique numeric values found in the second column. The COUNT function with the DISTINCT filter acts like a COUNT function with a GROUP BY clause on the LIST_VALUE column, as you’ll see later in the discussion.
NAME_NUMBER VALUE_NUMBER ----------- ------------ 20 10 |
SUM, AVERAGE, MAX, and MIN functions
Math Operations by Value: These math aggregations are done with the SUM, AVERAGE, MAX, and MIN aggregation functions. They’re restricted to columns that contain numeric values. Each of them support the default ALL or DISTINCT keywords and they behave much as the COUNT function discussed earlier.
There is a significant difference between their functionality. You can count unique strings but you can’t sum a column of numbers based on that other column without a GROUP BY clause. You see examples of the GROUP BY in the next part of this post.
Since these behave more or less the same, their examples are grouped together in sections by summing and averaging together, and taking the maximum and minimum values together. For reference, the ordinal numbers sum to 45, two sets of them are naturally 90; and the average of evenly dispersed numbers is half, or 45, and their unique values 4.5.
SELECT SUM(ALL list_value) AS sum_all , SUM(DISTINCT list_value) AS sum_distinct , AVG(ALL list_value) AS avg_all , AVG(DISTINCT list_value) AS avg_distinct FROM ordinal; |
It returns:
SUM_ALL SUM_DISTINCT AVG_ALL AVG_DISTINCT ---------- ------------ ---------- ------------ 90 45 4.5 4.5 |
The next section makes the same comparison with the MAX, and MIN functions. The maximum or minimums are the same because they the uniqueness doesn’t change the minimum or maximum of the sets.
SELECT MIN(ALL list_value) AS min_all , MIN(DISTINCT list_value) AS min_distinct , MAX(ALL list_value) AS max_all , MAX(DISTINCT list_value) AS max_distinct FROM ordinal; |
It returns:
MIN_ALL MIN_DISTINCT MAX_ALL MAX_DISTINCT ---------- ------------ ---------- ------------ 0 0 9 9 |
Multiple row aggregation function use ↓
This section discusses and demonstrates the five aggregation functions where they return more than a single row of data, which involves grouping by non-aggregated columns. This section covers the GROUP BY and HAVING clauses. Like the other sections, you unfold this by clicking on the bold text above.
COUNT function
Counting by Groups: The following example demonstrates how we can count rows or values by groups. The example counts rows by reference and value, but it does so by grouping the return values on the unique string values in the LIST_SET column.
1 2 3 4 5 | SELECT list_set AS grouping_by_column , COUNT(*) , COUNT(list_value) FROM ordinal GROUP BY list_set; |
It returns the following values:
GROUPING_BY_COLUMN COUNT(*) COUNT(LIST_VALUE) -------------------- ---------- ----------------- VALUE SET A 11 10 VALUE SET B 11 10 |
The results show that there are eleven rows for each set, and that ten of the rows contain values while one holds a null value.
You can also group on columns that contain null values or sets of columns. The following example groups by two columns, where one contains a null value.
1 2 3 4 5 6 7 8 | SELECT list_set AS grouping_by_not_null , list_name AS group_by_null_too , COUNT(*) , COUNT(list_value) FROM ordinal WHERE list_set = 'Value Set A' GROUP BY list_set , list_name; |
It returns the following values:
GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO COUNT(*) COUNT(LIST_VALUE) -------------------- ----------------- ---------- ----------------- VALUE SET A Zero 1 1 VALUE SET A Five 1 1 VALUE SET A Three 1 1 VALUE SET A Four 1 1 VALUE SET A One 1 1 VALUE SET A Two 1 1 VALUE SET A Eight 1 1 VALUE SET A Nine 1 1 VALUE SET A Seven 1 1 VALUE SET A Six 1 1 VALUE SET A 1 0 |
The last row returned is the one where the grouping by column value and counted column value are null. You should note that the GROUP BY applies to a string column and the return set includes a row grouped by its null value. The count of null column values is always zero.
Another thing that you may have not noticed is that the rows are no longer in ascending numeric order by LIST_NAME or LIST_VALUE columns. There’s a multiple edge sword when you examine why this occurs. Basically with regard to the LIST_NAME column, Oracle and many other databases use advanced sorting algorithms which may leave non-ordered sets as outcomes. The LIST_VALUE column is not in the GROUP BY clause and therefore can’t be used as an ORDER BY key.
When you attempt to use a column outside of the GROUP BY clause to order the return set, like this:
1 2 3 4 5 6 7 8 9 | SELECT list_set AS grouping_by_not_null , list_name AS group_by_null_too , COUNT(*) , COUNT(list_value) FROM ordinal WHERE list_set = 'Value Set A' GROUP BY list_set , list_name ORDER BY list_value; |
You would see the following error in an Oracle Database 10g instance:
ORDER BY list_value * ERROR at line 9: ORA-00979: NOT a GROUP BY expression |
You can filter this particular set because the conditions are limited, but this wouldn’t be a good idea in many other solution sets. However, it certainly highlights some of the potential for extraordinary sorting of result sets.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SELECT list_set AS grouping_by_not_null , list_name AS group_by_null_too , COUNT(*) , COUNT(list_value) FROM ordinal WHERE list_set = 'Value Set A' GROUP BY list_set , list_name ORDER BY CASE WHEN list_name = 'Zero' THEN 0 WHEN list_name = 'One' THEN 1 WHEN list_name = 'Two' THEN 2 WHEN list_name = 'Three' THEN 3 WHEN list_name = 'Four' THEN 4 WHEN list_name = 'Five' THEN 5 WHEN list_name = 'Six' THEN 6 WHEN list_name = 'Seven' THEN 7 WHEN list_name = 'Eight' THEN 8 WHEN list_name = 'Nine' THEN 9 END; |
It returns the following values:
GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO COUNT(*) COUNT(LIST_VALUE) -------------------- ----------------- ---------- ----------------- VALUE SET A Zero 1 1 VALUE SET A One 1 1 VALUE SET A Two 1 1 VALUE SET A Three 1 1 VALUE SET A Four 1 1 VALUE SET A Five 1 1 VALUE SET A Six 1 1 VALUE SET A Seven 1 1 VALUE SET A Eight 1 1 VALUE SET A Nine 1 1 VALUE SET A 1 0 |
A better way to sort this type of data would involve putting it into an inline view and then joining it against the base data set. This allows you to use a non-grouping column as the sort key in an ORDER BY clause. Here’s an example of that approach but note it does take system overhead and should only be done when SQL must perform the sort operation.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT ilv.grouping_by_not_null , ilv.group_by_null_too , ilv.row_count , ilv.column_count FROM (SELECT list_set AS grouping_by_not_null , list_name AS group_by_null_too , COUNT(*) AS ROW_COUNT , COUNT(list_value) AS column_count FROM ordinal WHERE list_set = 'Value Set A' GROUP BY list_set , list_name) ilv LEFT JOIN ordinal o ON ilv.grouping_by_not_null = o.list_set AND ilv.group_by_null_too = o.list_name ORDER BY o.list_value; |
That returns a perfectly ordered set because the LEFT JOIN lets us capture the null value in the inline view without matching the two columns in the join condition. It returns the following set:
GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ROW_COUNT COLUMN_COUNT -------------------- ----------------- ---------- ------------ Value Set A Zero 1 1 Value Set A One 1 1 Value Set A Two 1 1 Value Set A Three 1 1 Value Set A Four 1 1 Value Set A Five 1 1 Value Set A Six 1 1 Value Set A Seven 1 1 Value Set A Eight 1 1 Value Set A Nine 1 1 Value Set A 1 0 |
SUM, AVERAGE, MAX, and MIN functions
Math Operations by Groups: These math aggregations are done with the SUM, AVERAGE, MAX, and MIN aggregation functions. Like the previous single row return examples, they’re restricted to columns that contain numeric values. While they also support the default ALL or DISTINCT keywords, it didn’t seem necessary to demonstrate more of those behaviors here.
The following sums, averages, and gets both the minimum and maximum value by unique LIST_SET column values:
SELECT list_set AS grouping_by_not_null , SUM(list_value) AS ordinal_sum , AVG(list_value) AS ordinal_avg , MIN(list_value) AS ordinal_min , MAX(list_value) AS ordinal_max FROM ordinal GROUP BY list_set; |
It returns the following two rows:
GROUPING_BY_NOT_NULL ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX -------------------- ----------- ----------- ----------- ----------- VALUE SET A 45 4.5 0 9 VALUE SET B 45 4.5 0 9 |
Expanding the GROUP BY criteria to include the LIST_NAME column, you see what happens when the SUM and AVG functions work with only null values. They always return null when adding only column values with a null value. This is handy as you’ll see in the selective aggregation discussion in a subsequent tutorial.
As done in the prior example, this filters out the second value set, to focus on what’s happening with the rows grouped on two columns.
1 2 3 4 5 6 7 8 9 10 | SELECT list_set AS grouping_by_not_null , list_name AS group_by_null_too , SUM(list_value) AS ordinal_sum , AVG(list_value) AS ordinal_avg , MIN(list_value) AS ordinal_min , MAX(list_value) AS ordinal_max FROM ordinal WHERE NOT list_set = 'Value Set B' GROUP BY list_set , list_name; |
It returns the following set of records, which are clearly out of order based on the algorithms that optimize aggregation. It’s not hard to read because there aren’t too many rows returned.
GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX -------------------- ----------------- ----------- ----------- ----------- ----------- VALUE SET A Zero 0 0 0 0 VALUE SET A Five 5 5 5 5 VALUE SET A Three 3 3 3 3 VALUE SET A Four 4 4 4 4 VALUE SET A One 1 1 1 1 VALUE SET A Two 2 2 2 2 VALUE SET A Eight 8 8 8 8 VALUE SET A Nine 9 9 9 9 VALUE SET A Seven 7 7 7 7 VALUE SET A Six 6 6 6 6 VALUE SET A |
If SQL must sort these into numeric order, it can do so like the prior example with the result from the COUNT function. You must instrument a solution for it through an inline view. The following shows you how to do that.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SELECT ilv.grouping_by_not_null , ilv.group_by_null_too , ilv.ordinal_sum , ilv.ordinal_avg , ilv.ordinal_min , ilv.ordinal_max FROM (SELECT list_set AS grouping_by_not_null , list_name AS group_by_null_too , SUM(list_value) AS ordinal_sum , AVG(list_value) AS ordinal_avg , MIN(list_value) AS ordinal_min , MAX(list_value) AS ordinal_max FROM ordinal WHERE list_set = 'Value Set B' GROUP BY list_set , list_name) ilv LEFT JOIN ordinal o ON ilv.grouping_by_not_null = o.list_set AND ilv.group_by_null_too = o.list_name ORDER BY o.list_value; |
Now you should see an ordered set like this:
GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX -------------------- ----------------- ----------- ----------- ----------- ----------- VALUE SET B Zero 0 0 0 0 VALUE SET B One 1 1 1 1 VALUE SET B Two 2 2 2 2 VALUE SET B Three 3 3 3 3 VALUE SET B Four 4 4 4 4 VALUE SET B Five 5 5 5 5 VALUE SET B Six 6 6 6 6 VALUE SET B Seven 7 7 7 7 VALUE SET B Eight 8 8 8 8 VALUE SET B Nine 9 9 9 9 VALUE SET B |
Math Operations with the HAVING clause: The HAVING clause is very useful when you want to find rows that duplicate a column or set of column values. It’s also useful for sorting data sets.
The HAVING clause effects all aggregate rows and acts somewhat like a filter in the WHERE clause of a non-aggregated query. The difference is that the WHERE clause filters the base result set, while the HAVING clause filters the aggregated result set.
The following example captures the sum and average for only the odd LIST_VALUE column values. It does that by using modular (or modulo) mathematics through the MOD function in Oracle’s SQL implementation. Modulo math works by calculating the remainder of integer division. When you divide any number by two and the result is one, then you have an odd number.
Here’s the sample program that illustrates the HAVING clause in a CASE statement. The CASE statement only adds the number when it is an odd number. Any even number is zeroed out. The comparison operator ensures that only rows are returned when the SUM is greater than zero, which only happens for odd numbers.
1 2 3 4 5 6 7 8 9 10 11 | SELECT list_set AS grouping_by_not_null , list_name AS group_by_null_too , SUM(list_value) AS ordinal_sum , AVG(list_value) AS ordinal_avg FROM ordinal WHERE list_set = 'Value Set A' HAVING SUM(CASE WHEN MOD(list_value,2) = 1 THEN list_value ELSE 0 END) > 0 GROUP BY list_set , list_name; |
The data set, as you’ve seen with others isn’t sorted in ascending order because of how the algorithms work. Since there are only five rows returned, it is easy to see that the HAVING clause let us narrow the return set to odd numbers. What we might overlook is that the HAVING is always a filtering statement. This means we need to have a comparative operator tied to the HAVING return value.
GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG -------------------- ----------------- ----------- ----------- Value Set A Five 5 5 Value Set A Three 3 3 Value Set A One 1 1 Value Set A Nine 9 9 Value Set A Seven 7 7 |
As you’ve seen in early examples, you can also put this inside an inline view and sort the aggregated results. Here’s the code to accomplish that.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT ilv.grouping_by_not_null , ilv.group_by_null_too , ilv.ordinal_sum , ilv.ordinal_avg FROM (SELECT list_set AS grouping_by_not_null , list_name AS group_by_null_too , SUM(list_value) AS ordinal_sum , AVG(list_value) AS ordinal_avg FROM ordinal WHERE list_set = 'Value Set A' HAVING SUM(CASE WHEN MOD(list_value,2) = 1 THEN list_value ELSE 0 END) > 0 GROUP BY list_set , list_name) ilv LEFT JOIN ordinal o ON ilv.grouping_by_not_null = o.list_set AND ilv.group_by_null_too = o.list_name ORDER BY o.list_value; |
It naturally returns the ordered set of aggregated results from only odd numbers in the base set of values.
GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG -------------------- ----------------- ----------- ----------- Value Set A One 1 1 Value Set A Three 3 3 Value Set A Five 5 5 Value Set A Seven 7 7 Value Set A Nine 9 9 |
Setup Script ↓
The drop down items, unfold with source code to seed the examples.
Oracle SQL Join Supporting Script ↓
This has the setup script for the example tables.
-- Conditionally drop the table and sequence. BEGIN FOR i IN (SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME = 'ORDINAL') LOOP EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name; END LOOP; FOR i IN (SELECT sequence_name FROM user_sequences WHERE sequence_name = 'ORDINAL_S1') LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name; END LOOP; END; / -- Create the aggregation sample table and sequence. CREATE TABLE ordinal ( ordinal_id NUMBER , list_set VARCHAR2(20) , list_name VARCHAR2(5) , list_value NUMBER); CREATE SEQUENCE ordinal_s1; -- Seeding values in the aggregation sample table. DECLARE -- Define local types. TYPE listg IS TABLE OF VARCHAR2(20); TYPE listn IS TABLE OF NUMBER; TYPE lists IS TABLE OF VARCHAR2(5); -- Declare local variables. groups LISTG := listg('Value Set A','Value Set B'); labels LISTS := lists('Zero','One','Two','Three','Four','Five','Six','Seven','Eight','Nine'); valuen LISTN := listn(0,1,2,3,4,5,6,7,8,9); BEGIN -- Outer loop sets the group level. FOR i IN 1..groups.COUNT LOOP -- Inner loop sets the row level. FOR j IN 1..labels.COUNT LOOP INSERT INTO ordinal VALUES (ordinal_s1.NEXTVAL,groups(i),labels(j),valuen(j)); END LOOP; -- Insert the null values for each group. INSERT INTO ordinal VALUES (ordinal_s1.NEXTVAL,groups(i),NULL,NULL); END LOOP; COMMIT; END; / -- Commit the inserts. COMMIT; |
MySQL SQL Join Supporting Script ↓
This has the setup script for the example tables.
-- Conditionally drop the table. SELECT 'DROP TABLE ordinal' AS Statement; DROP TABLE IF EXISTS ordinal; -- Create the table. SELECT 'CREATE TABLE ordinal' AS Statement; CREATE TABLE ordinal ( ordinal_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , list_set VARCHAR(20) , list_name VARCHAR(5) , list_value INT UNSIGNED); -- Seed the row values. SELECT 'INSERT INTO ordinal' AS Statement; INSERT INTO ordinal VALUES (NULL,'Value Set A','Zero','0') ,(NULL,'Value Set A','One','1') ,(NULL,'Value Set A','Two','2') ,(NULL,'Value Set A','Three','3') ,(NULL,'Value Set A','Four','4') ,(NULL,'Value Set A','Five','5') ,(NULL,'Value Set A','Six','6') ,(NULL,'Value Set A','Seven','7') ,(NULL,'Value Set A','Eight','8') ,(NULL,'Value Set A','Nine','9') ,(NULL,'Value Set A',NULL,NULL) ,(NULL,'Value Set B','Zero','0') ,(NULL,'Value Set B','One','1') ,(NULL,'Value Set B','Two','2') ,(NULL,'Value Set B','Three','3') ,(NULL,'Value Set B','Four','4') ,(NULL,'Value Set B','Five','5') ,(NULL,'Value Set B','Six','6') ,(NULL,'Value Set B','Seven','7') ,(NULL,'Value Set B','Eight','8') ,(NULL,'Value Set B','Nine','9') ,(NULL,'Value Set B',NULL,NULL); -- Commit the inserts. COMMIT; |
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.
PHP Binding a Wildcard
Somebody asked me about how you bind a parameter in mysqli when a multiple character wildcard needs to be next to the variable value. They’d found that it worked when creating a SQL statement by concatenation (string gluing), like this:
$sql = "SELECT item_title FROM item WHERE item_title LIKE '%".$title."%'"; |
However, they tried to bind the variable following the same pattern, and found that it failed. They used the following type of syntax:
$sql = "SELECT item_title FROM item WHERE item_title LIKE '%?%'"; |
It raised the following error:
Warning: mysqli_stmt_bind_param() [function.mysqli-stmt-bind-param]: Number of variables doesn't match number of parameters in prepared statement in program_name on line # |
The reason is the parser, it expects variables to be independent tokens in the SQL string. You can’t bind a string into the shell of an apostrophe delimited string. You could naturally, make an assignment to the local PHP variable before binding it to the variable, like this:
$title = '%'.$title.'%'; |
It’s actually a better idea to concatenate the multiple character wildcard operator inside the SQL statement. The correct syntax requires that you use the CONCAT() function. You could reset to use piped concatenation but generally you should avoid that on the MySQL platform (see this post for an explanation of SQL concatenation on Oracle, MySQL, and SQL Server).
This is the required statement when using a MySQL database:
$sql = "SELECT item_title FROM item WHERE item_title LIKE CONCAT('%',?,'%')"; |
Complete Code Sample ↓
Expand this section to see the complete working code sample.
The first component for this program is an include file for the database credentials:
<?php // Connection variables. define('HOSTNAME',"localhost"); define('USERNAME',"student"); define('PASSWORD',"student"); define('DATABASE',"sampledb"); ?> |
Once you’ve placed the credentials in your directory, you can put this in the same directory and then call it from your browser. At least, you can provided you’ve created the user with the required password, and the database on the standard 3306 port.
<?php // Set database credentials. include_once("MySQLCredentials.inc"); // Return successful attempt to connect to the database. if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) { // Print user message. print "Sorry! The connection to the database failed. Please try again later."; // Assign the OCI error and format double and single quotes. print mysqli_error(); // Kill the resource. die(); } else { // Declare input variables. $title = (isset($_GET['title'])) ? $_GET['title'] : $title = "RoboCop"; query_insert($title); } // Query results afret an insert. function query_insert($title) { // Return successful attempt to connect to the database. if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) { // Print user message. print "Sorry! The connection to the database failed. Please try again later."; // Assign the OCI error and format double and single quotes. print mysqli_error(); // Kill the resource. die(); } else { // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // Declare a SQL SELECT statement returning a CLOB. $sql = "SELECT item_title FROM item WHERE item_title LIKE CONCAT('%',?,'%')"; // Prepare statement. if (mysqli_stmt_prepare($stmt,$sql)) { mysqli_stmt_bind_param($stmt,"s",$title); // Execute it and print success or failure message. if (mysqli_stmt_execute($stmt)) { // Store result. mysqli_stmt_store_result($stmt); // Bind result to local variable. mysqli_stmt_bind_result($stmt,$item_title); // Open HTML table and print header. $out = '<table border="1" cellpadding="3" cellspacing="0">'; $out .= '<tr>'; $out .= '<th align="center" style="font-weight:bold;padding:2px;margin:1px;background:#8DB3E2;width:300px">Item Title</th>'; $out .= '</tr>'; // Read result. while (mysqli_stmt_fetch($stmt)) { $out .= '<tr>'; $out .= '<td style="padding:2px;margin:1px;background:#DBE5F1;">'.$item_title.'</td>'; $out .= '</tr>'; } // Close the HTML table. $out .= '</table>'; // Print the HTML table. print $out; } } // Free system resources. mysqli_stmt_free_result($stmt); // Disconnect from database. mysqli_close($c); } } ?> |
Mac OS X Limits for Oracle
A couple months ago, I tried to install Oracle Database 10g, Release 2 for Mac OS X on Intel x86-64 on a MacBook running generic Snow Leopard. It didn’t work because you must have the Mac OS X Server. Somebody asked me to post more of what I found when I tried to hack the installation. Oracle’s documentation Oracle™ Database Installation Guide 10g Release 2 (10.2) for Apple Mac OS X (Intel), B25286-01 clearly states that the installation requires Mac OS X Server edition. By the way, this is one of the best installation guides I’ve seen from Oracle because it is very complete.
You can determine which version of Mac OS X you’re running with the following:
# sw_vers |
It won’t work when you’re on a generic Snow Leopard release, like this:
ProductName: Mac OS X ProductVersion: 10.6.2 BuildVersion: 10C540 |
The first place you’ll get stopped cold is during the installation. You can specify the kern.sysv.shmmni value in the sysctl.conf file at the required 4096 but it won’t allow reconfiguration from the default value of 32. The inability to override this value stops the Oracle installer. You can see the default value of the shared memory parameters by running this command in a terminal session:
# /usr/sbin/sysctl -a | grep \\.shm |
You should see the following:
kern.sysv.shmmax: 4194304 kern.sysv.shmmin: 1 kern.sysv.shmmni: 32 kern.sysv.shmseg: 8 kern.sysv.shmall: 1024 |
I haven’t tested it on the Mac OS X Server edition yet. I’ve a hunch that it’ll work like a charm. When I do get to that, I’ll update the post. Here’s the Apple support note on kern.sysv.shmmni for the Server version.
PL/SQL Workbook Code
I got a request Saturday for me to post code for the Oracle Database 11g PL/SQL Programming Workbook. You can download the book code here. It should also be on the McGraw-Hill web site tomorrow.
The irony for me is the timing of the request. I didn’t get it until late Saturday night when I had to make an early plane to Dallas, Texas on Sunday morning. It teaches me once again, that I should keep my book updates in one place and backup in a convenient carry-anywhere location.
I also found out that the Bulletin Board I’d set up wasn’t accessible. At least, accessible to anybody but bots. I uninstalled and re-installed it, and configured it. Now I’ll start maintaining it.
What a VMWare Thrill …
I’d upgraded from VMWare Fusion 2 to 3 and taken care of most instances. A NASTY surprise awaited me when I tried to upgrade my Linux installations this morning.
You MUST to uninstall VMWare tools from your repository of Linux operating systems before upgrading your VMWare Fusion or ESX server. If you don’t, you can’t access the console because the drivers make the console look like this:
Perhaps I missed this note when, as an early adopted I opted to move straight to VMWare Fusion 3. I would have complied with these instructions to avoid this headache.
All that’s required now, is that: (a) I export 500 GBs worth of virtual machines to another machine running VMWare Fusion 2; (b) Individually start each machine and run the uninstall VMWare Tools command individually; and, (c) Shutdown and reposition all virtual machines on the original server.
As stated in the note, the command to remove it is:
/usr/bin/vmware-tools-uninstall.pl |
Click on the note in the event the link fails to resolve, which would mean the note vanishes into oblivion some day in the future …
While I’ve another machine that’s not yet upgraded, this is a major inconvenience. It’s a shame that the Linux components don’t install automatically. It’s a shame that the install didn’t say something like, “Don’t do this if you’ve Linux virtual machines, unless you’ve removed their VMWare Tools installation.”
Another word to the wise, you don’t get the Linux Tools automatically when you download the product. The software prompts you to download the additional components when you attempt to launch a Linux environment. A cruel irony since by the time you see the prompt, you can’t! This is a change from the prior upgrade process.
Yes, haste makes waste but now I know. In the future, treat all VMWare upgrades like those from Microsoft test, re-test, re-authenticate in a small way before upgrading. Do you think VMWare really want to send that message to its customer base?
I got back to this tonight, and thanks to Red Hat’s Session Manager I was able to fix the Red Hat VMs. Launching it, I simply switched to the Failsafe Terminal and ran the following command, as per the note:
# /usr/bin/vmware-uninstall.pl |
After that, I rebooted. Then, installed (mounted the VMWare Tools disk) from the VMWare Fusion menu. Opening a terminal as root, I then re-installed and configured VMWare Fusion. Those are done. As more time allows, I’ll update about the others.
I’m now reconfiguring the network since the VMWare Fusion 2 bridged at a 172.16.153 subdomain and VMWare Fusion 3 bridges at a 172.16.123 subdomain.
My author’s copies came
My co-author got his author copies about a week before me.
The technical editor got his complementary book too the same day. I was wondering where mine got routed but now I’ve got mine, a few days after it was available on Amazon.
I think the new book looks good. John and I were probably a real pain during the editing process because we kept asking for more control during the proof copy to get things fixed. I think we messed up the release schedule by a couple weeks.
John and I think that the mastery questions in each chapter work well. My students who’ve seen it think it’s great because of the review sections and its smaller size. I’m re-verifying the code now, and it should be on McGraw-Hill’s website next week.
Subscribe via RSS