Archive for the ‘Oracle’ Category
Alice and Assignments
As I continue down the warren hole of Persistent Stored Modules (SQL/PSM) in MySQL, I keep wondering about that mad hare, Johnny Depp. Alice isn’t a programming language to teach me anything in this dream. Moreover, TIm Burton’s tale this seems oddly familiar, like a child’s story gone mad.
A quick update on comparative SQL expression assignments between PL/SQL and MySQL. When you want to filter a value through SQL functions before assigning it to another variable in MySQL, it’s not like PL/SQL. Just like the new Alice in Wonderland movie isn’t like the book.
The programmatic differences lies in their origins. PL/SQL evolved from Pascal through Ada to become a recursive language where you can call SQL from PL/SQL and PL/SQL from SQL. MySQL implemented PSMs from the ANSI SQL:2003 specification, which didn’t see it the same way, apparently (a disclaimer since I’ve not read the details of the specification).
Personally, I think PL/SQL is easier to write but I’ve been using it for almost 20 years. Naturally, there may be a consistency thread on this that I’m missing and an opportunity that I may exploit. After all, it is dark in this warren hole.
Oracle PL/SQL Assignments from SQL Expressions
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | -- Enable output printing. SET SERVEROUTPUT ON SIZE 1000000 -- Define an anonymous block. DECLARE -- Declare a source variable. lv_right_operand VARCHAR2(10) := 'March'; -- Define a target variable for the assignment. lv_left_operand VARCHAR2(10); BEGIN -- Return the expression from a nested call parameter of the source variable. lv_left_operand := UPPER(SUBSTR(lv_right_operand,1,3)); -- Print it to console. dbms_output.put_line('Output ['||lv_left_operand||']'); END; / |
Oracle also supports this syntax, which isn’t frequently used because it’s much more verbose syntactically. It is also equivalent to the PSM syntax adopted by MySQL.
-- Define an anonymous block. DECLARE -- Declare a source variable. lv_right_operand VARCHAR2(10) := 'March'; -- Define a target variable for the assignment. lv_left_operand VARCHAR2(10); BEGIN -- Return the expression from a nested call parameter of the source variable. SELECT UPPER(SUBSTR(lv_right_operand,1,3)) INTO lv_left_operand FROM dual; -- Print it to console. dbms_output.put_line('Output ['||lv_left_operand||']'); END; / |
That means we can do it like the White Queen wants it or the Red Queen wants it in Oracle. Flexibility in PL/SQL is clearly broader because of the assignment options. Not so in MySQL, as you’ll see.
MySQL PSM Assignment from SQL Expressions
First, MySQL’s PSM approach doesn’t support anonymous blocks. The example must create a stored function or procedure, and then call it. A procedure seems like the best fit for the example.
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 | -- Conditionally drop procedure. SELECT 'DROP PROCEDURE IF EXISTS assignit' AS "Statement"; DROP PROCEDURE IF EXISTS assignit; -- Create the proceudre SELECT 'CREATE PROCEDURE assignit' AS "Statement"; DELIMITER $$ -- Define the procedure. CREATE PROCEDURE assignit() BEGIN /* Declare a source variable. */ DECLARE lv_right_operand VARCHAR(10) DEFAULT 'March'; /* Define a target variable for the assignment. */ DECLARE lv_left_operand VARCHAR(3); /* Assign the modified value through the SELECT-INTO model. */ SELECT UCASE(SUBSTRING(lv_right_operand,1,3)) INTO lv_left_operand; /* Display assigned value. */ SELECT lv_left_operand; END; $$ DELIMITER ; -- Call the procedure. CALL assignit(); |
The only question here in the warren is: Who’s the White Queen; and who’s the Red Queen. Which semantic should I choose? My I hope is that I wake up before it’s … oops, off with his head. Actually, 3D or not, I’ll probably not see it, that’s the new Alice in Wonderland film.
Likewise, when my students wake up and read this they’ll know I was just answering a question on how to perform assignments in MySQL stored procedures. By the way, I’ve updated this assignment process in my Debugging MySQL Procedures post.
As an aside, I’ve got a new MySQL debugger that I’m testing later in the week. When I complete the test cases, I’ll post a review.
Wrap a cursor function
A Gauss posted a question on my from last year’s Utah Oracle User’s Group Training Days presentation. If I understood his question correctly, this should help him work with his legacy code. Honestly, as I wrote the example something Bryn Llewellyn said kept banging around in my head, “Just because we can, doesn’t mean we should.” He was speaking of writing poorly engineered code.
Sometimes, we don’t get the opportunity to re-factor existing code. That leaves us with writing wrappers that aren’t pretty or effective. A realization and preface to showing everyone how to accomplish these tasks, and perhaps a watch out warning if you choose this path. I suspect that there may be a better way but I don’t know their code tree.
Here’s the question, as I understand it. They’ve got a library function in PL/SQL that returns a system reference cursor and is principally consumed by an external Java program. This type of architecture is more or less an Adapter OOAD pattern that I wrote about here, over a year and a half ago. The question comes to how to you wrap this approach and make it work in PL/SQL natively too.
The answer depends on some earlier posts because I don’t have a great deal of time to write new examples. It uses a COMMON_LOOKUP table, which is more or less a bunch of small tables grouped into a big table for use in user interaction forms. That way the values don’t get lost in a large code base and are always consistently maintained. These types of tables exist in all major ERP and CRM applications.
The base code for the example is found here, where I discussed how you can effectively use object tables – collections of user-defined object types (Oracle 9iR2 forward if I remember correctly). You can grab the full code at the bottom of the page by clicking the Code Script widget to unfold the code. That code also depends on the Oracle Database 11g PL/SQL Programming downloadable code, which you can download by clicking the link to the zip file location.
Here are the steps to wrap a function that returns a PL/SQL reference cursor so that it can also return a PL/SQL associative array.
- Create a package specification to hold all the components that are required to manage the process. Assuming that they may have anchored the system reference cursor to something other than a table like a shared cursor, which is a cumbersome implementation design. (I actually chose to exclude this from the book because it’s a stretch as a good coding practice. At least, it is from my perspective. Also, I couldn’t find an example in the Oracle documentation, which led me to believe they didn’t think it’s a great idea either or I could have glossed over it.) You should note that the PL/SQL
RECORD, Associative Array (collection), and theREF CURSORare defined in this package specification.
-- Create a package to hold the PL/SQL record structure. CREATE OR REPLACE PACKAGE example IS -- Force cursors to be read as if empty every time. PRAGMA SERIALLY_REUSABLE; -- Package-level record structure that mimics SQL object type. TYPE common_lookup_record IS RECORD ( common_lookup_id NUMBER , common_lookup_type VARCHAR2(30) , common_lookup_meaning VARCHAR2(255)); -- Package-level collection that mimics SQL object table. TYPE common_lookup_record_table IS TABLE OF common_lookup_record INDEX BY PLS_INTEGER; -- Cursor structure to support a strongly-typed reference cursor. CURSOR c IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup; -- Package-level strongly-typed system reference cursor. TYPE cursor_lookup IS REF CURSOR RETURN c%ROWTYPE; END; / |
- Write a function to return a strongly typed system reference cursor that’s anchored to a cursor defined in the package. This is fairly straightforward when the package specification is done right. You should notice right away that anchoring the original cursor in the package was a horrible practice because you must repeat it all again in the function. In my opinion, you shouldn’t anchor any system reference cursor explicitly to anything other than a table. The cursor could have used the generic weak cursor data type –
SYS_REFCURSOR. Doing so, saves all the extra lines required by a potential shared cursor.
CREATE OR REPLACE FUNCTION get_common_lookup_cursor ( TABLE_NAME VARCHAR2, column_name VARCHAR2) RETURN example.cursor_lookup IS -- Define a local variable of a strongly-typed reference cursor. lv_cursor EXAMPLE.CURSOR_LOOKUP; BEGIN -- Open the cursor from a static cursor OPEN lv_cursor FOR SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = TABLE_NAME AND common_lookup_column = column_name; -- Return the cursor handle. RETURN lv_cursor; END; / |
- Write a wrapper function that takes the reference cursor as a formal parameter and returns an Associative Array. You should note that this can’t be called from a SQL context. You must only use it in a PL/SQL context because system reference cursors are PL/SQL only data types.
CREATE OR REPLACE FUNCTION convert_common_lookup_cursor ( pv_cursor EXAMPLE.CURSOR_LOOKUP) RETURN example.common_lookup_record_table IS -- Declare a local counter variable. counter INTEGER := 1; -- Local PL/SQL-only variable. out_record EXAMPLE.COMMON_LOOKUP_RECORD; out_table EXAMPLE.COMMON_LOOKUP_RECORD_TABLE; BEGIN -- Grab the cursor wrapper and return values to a PL/SQL-only record collection. LOOP FETCH pv_cursor INTO out_record; EXIT WHEN pv_cursor%NOTFOUND; -- Assign it one row at a time to an associative array. out_table(counter) := out_record; -- Increment the counter. counter := counter + 1; END LOOP; -- Return the record collection. RETURN out_table; END; / |
- You can test the program in an anonymous block, like the one below. It defines a local Associative Array variable and then assigns the system reference cursor through the wrapper.
-- Open the session to see output from PL/SQL blocks. SET SERVEROUTPUT ON DECLARE -- Define a local associative array. process_table EXAMPLE.COMMON_LOOKUP_RECORD_TABLE; BEGIN -- Print title block. dbms_output.put_line('Converting a SYS_REFCURSOR to TABLE'); dbms_output.put_line('---------------------------------------------------'); -- Run the dynamic variables through the cursor generating function and then convert it. process_table := convert_common_lookup_cursor(get_common_lookup_cursor('ITEM','ITEM_TYPE')); -- Read the content of the Associative array. FOR i IN 1..process_table.COUNT LOOP dbms_output.put('['||process_table(i).common_lookup_id||']'); dbms_output.put('['||process_table(i).common_lookup_type||']'); dbms_output.put_line('['||process_table(i).common_lookup_meaning||']'); END LOOP; END; / |
I hope this answers Gauss’s question. While writing it, I could envision another question that might pop-up. How do you convert an object table type to a PL/SQL context. It was an omission not to include it in that original post on object table types. Here’s how you wrap an object table type into a PL/SQL scope collection.
You might have guessed. It’s done with another wrapper function. At least this is the easiest way to convert the SQL data type to a PL/SQL data type that I see. If you’ve another approach, a better way, let us know.
CREATE OR REPLACE FUNCTION get_common_lookup_record_table ( TABLE_NAME VARCHAR2 , column_name VARCHAR2 ) RETURN example.common_lookup_record_table IS -- Declare a local counter variable. counter INTEGER := 1; -- Define a dynamic cursor that takes two formal parameters. CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT * FROM TABLE(get_common_lookup_object_table(UPPER(table_name_in),UPPER(table_column_name_in))); -- A local PL/SQL-only collection variable. list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE; BEGIN -- Grab the cursor wrapper and return values to a PL/SQL-only record collection. FOR i IN c(TABLE_NAME, column_name) LOOP list(counter) := i; counter := counter + 1; END LOOP; -- Return the record collection. RETURN list; END get_common_lookup_record_table; / |
You can then test this in an anonymous block, like so:
-- Open the session to see output from PL/SQL blocks. SET SERVEROUTPUT ON DECLARE -- Declare a local PL/SQL-only collection and assign the value from the function call. list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE; BEGIN -- Print title block. dbms_output.put_line('Converting a SQL Collection to a PL/SQL Collection'); dbms_output.put_line('---------------------------------------------------'); -- Assign wrapped SQL collection to a PL/SQL-only collection. list := get_common_lookup_record_table('ITEM','ITEM_TYPE'); -- Call the record wrapper function. FOR i IN 1..list.COUNT LOOP dbms_output.put('['||list(i).common_lookup_id||']'); dbms_output.put('['||list(i).common_lookup_type||']'); dbms_output.put_line('['||list(i).common_lookup_meaning||']'); END LOOP; END; / |
As always, I hope this helps somebody without paying a fee for content. 😉
Stable set of rows?
My students are into the upload lab and they’ve encountered the wonderful ORA-30926 error from a MERGE statement. An example of the MERGE for an upload is in this earlier blog post.
This is the wonderful error message, which doesn’t seem to have meaning for many. The key is the non-deterministic where clauses phrase. That phrase means that the the query inside the USING clause returns a non-unique set of rows. The returned rows can’t be matched uniquely against the target table in the ON clause. The ON clause is where the MERGE statment matches the source query’s rows against the target table’s rows. The best join condition in a MERGE statement is one between a surrogate primary and foreign key column.
Error: ORA-30926 Text: Unable TO GET a stable SET OF ROWS IN the SOURCE TABLES. -------------------------------------------------------------------- Cause: A stable SET OF ROWS could NOT be got because OF LARGE dml activity OR a non-deterministic WHERE clause. Action: Remove any non-deterministic WHERE clauses AND reissue the dml. |
The problem is very much like when you write what you think is a single-row subquery but find out it’s actually a multiple-row subquery when it return an ORA-01422 error in Oracle.
As a rule, I’ve found that using the DISTINCT operator in the source SELECT statement fixes it most of the time because the join isn’t returning a unique set of rows. Although, the better solution requires that you identify how to gain a unique result set. Alternatively, you need to re-examine the logic of your WHERE clause. It also happens when the SELECT clause returns date-time data types like SYSDATE instead of date data types. A date-time can be converted by using the TRUNCate function like this:
MERGE INTO TABLE_NAME target USING (SELECT ... , TRUNC(SYSDATE) AS creation_date FROM ...) SOURCE ON (target.primary_key_column = SOURCE.primary_key_column) WHEN MATCHED THEN UPDATE SET last_updated_by = SOURCE.last_updated_by , last_update_date = SOURCE.last_update_date WHEN NOT MATCHED THEN INSERT VALUES ( column_list_of_values ); |
Hope this helps my students and others … 😉
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.
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; |
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.
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.
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.
Sample PL/SQL Cursor Loops
A few of my students wanted me to post sample cursor loops, so here are examples of simple, FOR, and WHILE loops. There are a couple variations on approaches that demonstrate %TYPE and %ROWTYPE anchoring to data dictionary table definitions and local cursors.
Part of the idea behind these examples is to show the basic structure while mimicking the \G option of MySQL. The \G (Go) displays results as a list of column names and values by row. Ever since I discovered that in MySQL, I’ve hoped Oracle would incorporate something similar in their product. While discussing my wish list, I’d also like Oracle to make the FROM dual optional (like MySQL does) when selecting a string or numeric literal. You can find an implementation here, that leverages an example from Tom Kyte.
You can click any of the titles to view the code, which isn’t needed when you don’t have JavaScript enabled or the RSS expands them for you.
Simple loop with local variables ↓
This simple loop example uses a static cursor and local variables that map to each column returned by the cursor. It uses the %TYPE to anchor local variables to the data dictionary.
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 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare local variables that are anchored to column data types. lv_title item.item_title%TYPE; lv_subtitle item.item_subtitle%TYPE; lv_rating item.item_rating%TYPE; -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block. LOOP -- Fetch a row of the cursor and assign it to the three local variables. FETCH c INTO lv_title , lv_subtitle , lv_rating; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variables on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||lv_title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||lv_subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||lv_rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
Simple loop with a local record structure variable ↓
This simple loop example uses a static cursor, a local record structure data type, and a local variable of the local record structure data type. The local record structure maps to the columns returned by the cursor. It uses explicit data types that match those of the table. You could also use the %TYPE to anchor the elements of the structure in the local data type, like the prior example. With explicit data types, you must modify the program when the definition of the table changes otherwise your program may fail at runtime.
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 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare a local record data type, with explicit data types (you could use %TYPE here too). TYPE title_type IS RECORD ( title VARCHAR2(60) , subtitle VARCHAR2(60) , rating VARCHAR2(8)); -- Declare a local variable of the local record structure data type. item_record TITLE_TYPE; -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block. LOOP -- Fetch a row of the cursor and assign it to the local record structure variable. FETCH c INTO item_record; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
Simple loop with a local cursor structure variable ↓
This simple loop example uses a static cursor, a local variable that inherits its record structure from the local cursor. It does so by using the %ROWTYPE against the cursor. Often the %ROWTYPE is only applied when you return a structure that maps to the complete table definition. Sometimes I think using cursor_name%ROWTYPE is the only real purpose for shared cursors but I know that’s not really true.
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 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; -- Declare a local variable of that inherits its structure from a local cursor. item_record c%ROWTYPE; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block. LOOP -- Fetch a row of the cursor and assign it to the local record structure variable. FETCH c INTO item_record; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
For loop with an implicit record structure variable ↓
This FOR loop example uses a static cursor. When a FOR loop uses a cursor it becomes a cursor FOR loop, and the iterator i becomes an implicit cursor record structure. You should note that this is a very compact program because a cursor FOR loop manages opening and closing the cursor, and handling the loop exit implicitly. Many programmers default to this approach whenever it fits because it is simple and easy to implement.
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 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; BEGIN -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start a cursor FOR loop block. FOR i IN c LOOP -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||i.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||i.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; END; / |
For loop without a declaration block ↓
This FOR loop example uses a static cursor. Like the prior example, this FOR loop uses a cursor but it is defined inside the actual FOR loop structure. That approach eliminates the need for the declaration block. It’s a nice feature that some may call a trick. As a rule, you should really avoid this style because formally defining your cursor is a good practice and improves code maintainability. Naturally, this is probably the most compact program because everything is managed implicitly including the cursor assignment to the loop structure.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SET SERVEROUTPUT ON SIZE 1000000 BEGIN -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start a cursor FOR loop block with the static cursor in the definition. FOR i IN (SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i) LOOP -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||i.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||i.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; END; / |
WHILE loop with a cursor guard on entry statement ↓
This WHILE loop example uses a static cursor, like the prior examples. Unlike the prior example, the WHILE loop is a guard on entry loop. The previous loops were guard on exit loops. This has much the same structure as the simple loop with a cursor record structure variable but differs on two key points.
Point one is that you must have two FETCH statements because the guard on entry condition checks whether any records are found in the cursor. The first FETCH statement checks whether at least a one row is returned. When true or false, it initializes the cursor attributes, like %FOUND. The second FETCH statement handles the second row returned to last row returned from the cursor.
Point two is that you don’t have an EXIT WHEN cursor_name%NOTFOUND inside the loop because the guard condition stops the loop when it fails to find at least one record.
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 | DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; -- Declare a local variable of that inherits its structure from a local cursor. item_record c%ROWTYPE; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Fetch the first record to put into context the cursor attributes, like %FOUND. FETCH c INTO item_record; -- Start the simple loop block with a guard on entry condition. WHILE (c%FOUND) LOOP -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); -- Fetch the second and subsequent rows of the cursor and assign it to a local variables. FETCH c INTO item_record; END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
The WHILE loop as presented is complex because of the pre-loop FETCH statement, and the internal FETCH statement. It’s may appear better to convert it to a pseudo-infinite loop. You do that by setting the condition in a WHILE loop to a TRUE constant, like this example:
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 | DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; -- Declare a local variable of that inherits its structure from a local cursor. item_record c%ROWTYPE; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block with a guard on entry condition. WHILE (TRUE) LOOP -- Fetch the record set into a user-defined variable. FETCH c INTO item_record; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
The preceding example behaves much like a simple loop, and you have to ask what is the benefit of WHILE (TRUE) LOOP over LOOP. Generally, it appears that the WHILE loop syntax is slighly longer to type.
I’m sure this will help my students and hope it helps somebody else.
Subscribe via RSS