Comparative Techniques
This post is designed to illustrate PL/SQL code with techniques employed before Oracle Database 12c with manual sequence references rather than auto generated IDs. The trick is small but should be made with older code. It impacts creation statement, conditional drop statements, and modification to stored procedures that manage transactions across two or more tables.
The key to the difference is in the table creation. Prior to Oracle Database 12c, there were no automatic sequences that you could assign to table definitions. Tables were created without sequences, and sequences were created independently. Likewise, there was no “IF EXISTS” clause for a DROP statement, which meant conditional drops were required as anonymous blocks.
Old tables and sequences from the Oracle Database 11g would use the following definitions:
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 | /* Conditionally drop grandma table and grandma_s sequence. */ BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ','GRANDMA','GRANDMA_SEQ') ORDER BY 1 DESC, 2) LOOP IF i.object_type = 'TABLE' THEN /* Use the cascade constraints to drop the dependent constraint. */ EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id NUMBER CONSTRAINT grandma_nn1 NOT NULL , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL , CONSTRAINT grandma_pk PRIMARY KEY (grandma_id) ); /* Create the sequence. */ CREATE SEQUENCE grandma_seq; /* Create the table with primary and foreign key out-of-line constraints. */ CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER CONSTRAINT tweetie_bird_nn1 NOT NULL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn2 NOT NULL , grandma_id NUMBER CONSTRAINT tweetie_bird_nn3 NOT NULL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); /* Create sequence. */ CREATE SEQUENCE tweetie_bird_seq; |
The key to the differences in Oracle Database 12c are in the table creation, and then in the INSERT statement because you can exclude the surrogate key column. A surrogate key column is also known as an ID column or sequence identified column.
New tables and sequences from the Oracle Database 12c forward use the following definitions to manage sequences:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | /* Conditionally drop grandma table and grandma_s sequence. */ DROP TABLE IF EXISTS tweetie_bird; DROP TABLE IF EXISTS grandma; /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id NUMBER DEFAULT grandma_seq.NEXTVAL , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL , CONSTRAINT grandma_pk PRIMARY KEY (grandma_id) ); /* Create the table with primary and foreign key out-of-line constraints. */ CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER DEFAULT tweetie_bird_seq.NEXTVAL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn1 NOT NULL , grandma_id NUMBER DEFAULT grandma_seq.CURRVAL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); |
This is the pattern that I setup to explain the new default surrogate primary and foreign keys when I wrote the Oracle Database 12c PL/SQL Programming book (pp. 20-22). Unfortunately, it was just a simple example and fits this use case:
- You write one create_something procedure or function, also known interchangeably as a method, to insert initial records; and
- You write one add_something method add a subsequent dependent record; and
This is a common practice by many but it creates unnecessary code that you need to maintain, which translates to accumulated technical debt throughout a design. The technical debt grows by a factor of two when you want to overload behaviors for security features or batch processing.
A better solution is to write one method that accommodates both an creates a new record and adds dependent records. The warner_brother procedure will do that later in this post. To prepare for the warner_brother procedure the dependent tweetie_bird table needs to change.
The default of the .currval of a “parent” table’s sequence value is no longer guaranteed in an independent action. The create and add methods now share the same method, and inspection of an existing independent record is necessary for both the create and add methods.
The new tweetie_bird table looks like:
/* Create the table with primary and foreign key out-of-line constraints. */ CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER DEFAULT tweetie_bird_seq.NEXTVAL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn1 NOT NULL , grandma_id NUMBER CONSTRAINT tweetie_bird_nn2 NOT NULL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); |
The next part of the post discusses how to write the create and add procedure in pre-12c and 12c forward implementations. Clearly, the automatic generated sequence values are the best approach when you use user-defined names for them as shown earlier.
The warner_brother procedure models the idea that any grandma may have one or more Tweetie birds, which means you must check if there’s a row in the grandma table. If there’s a row in the grandma table, you shouldn’t insert a new row in the grandma table. The internal get_grandma_id function returns:
- A zero if there is no existing row in the grandma table. After which, the nested if-block will insert a new row into the grandma table; and then assign the grandma_seq.CURRVAL value to a local variable.
- An existing grandma_id value, which can be used as a foreign key value in the subsequent insert statement to the tweety_bird table.
The warner_brother procedure for Oracle Database 11g is shown below:
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 | /* Create or replace procedure warner_brother. */ CREATE OR REPLACE PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 ) IS /* Declare a local variable for an existing grandma_id. */ lv_grandma_id NUMBER; FUNCTION get_grandma_id ( pv_grandma_house VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_grandma_id ( cv_grandma_house VARCHAR2 ) IS SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_grandma_id; BEGIN /* Set the savepoint. */ SAVEPOINT starting; /* * Identify whether a member account exists and assign it's value * to a local variable. */ lv_grandma_id := get_grandma_id(pv_grandma_house); /* * Conditionally insert a new member account into the member table * only when a member account does not exist. */ IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_id , grandma_house ) VALUES ( grandma_seq.NEXTVAL , pv_grandma_house ); /* Assign grandma_seq.currval to local variable. */ lv_grandma_id := grandma_seq.CURRVAL; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_id , tweetie_bird_house , grandma_id ) VALUES ( tweetie_bird_seq.NEXTVAL , pv_tweetie_bird_house , lv_grandma_id ); /* If the program gets here, both insert statements work. Commit it. */ COMMIT; EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; / |
The warner_brother procedure for Oracle Database 12c forward simplifies somethings and complicates others. The DEFAULT for the grandma_id foreign key column in the tweetie_bird table must be overwritten with a copy of the correct primary key value. More or less, it requires the correct grandma_id column value by finding the existing row in the grandma table. The internal get_grandma_id function grabs that value while returning a value that precludes writing to the grandma table.
The warner_brother procedure for Oracle Database 12c forward shown below shows that the grandma_id column default value is overridden by a local lv_grandma_id value returned by the internal get_grandma_id function on lines 60 thru 65 below.
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 | /* Create or replace procedure warner_brother. */ CREATE OR REPLACE PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 ) IS /* Declare a local variable for an existing grandma_id. */ lv_grandma_id NUMBER; FUNCTION get_grandma_id ( pv_grandma_house VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_grandma_id ( cv_grandma_house VARCHAR2 ) IS SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_grandma_id; BEGIN /* Set the savepoint. */ SAVEPOINT starting; /* * Identify whether a member account exists and assign it's value * to a local variable. */ lv_grandma_id := get_grandma_id(pv_grandma_house); /* * Conditionally insert a new member account into the member table * only when a member account does not exist. */ IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_house ) VALUES ( pv_grandma_house ); /* Assign grandma_seq.currval to local variable. */ lv_grandma_id := grandma_seq.CURRVAL; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_house , grandma_id ) VALUES ( pv_tweetie_bird_house , lv_grandma_id ); /* If the program gets here, both insert statements work. Commit it. */ COMMIT; EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; / |
You can test either version with the following anonymous PL/SQL block:
/* Test the warner_brother procedure. */ BEGIN warner_brother( pv_grandma_house => 'Yellow House' , pv_tweetie_bird_house => 'Cage'); warner_brother( pv_grandma_house => 'Yellow House' , pv_tweetie_bird_house => 'Tree House'); END; / |
Ultimately, we’ll use a natural join to test the integrity of primary and foreign key relationship:
/* Query results from warner_brother procedure. */ COL grandma_id FORMAT 9999999 HEADING "Grandma|ID #" COL grandma_house FORMAT A20 HEADING "Grandma House" COL tweetie_bird_id FORMAT 9999999 HEADING "Tweetie|Bird ID" COL tweetie_bird_house FORMAT A20 HEADING "Tweetie Bird House" SELECT * FROM grandma NATURAL JOIN tweetie_bird; |
As always, I hope this helps those trying to leverage existing PL/SQL code into more modern code.
PL/SQL Table Function
Eleven years ago I had an interesting conversation about table functions in Oracle’s PL/SQL; and the fact that they’re not available in MySQL. When I explained they were available in Microsoft T-SQL User-Defined Functions (UDFs) with Microsoft’s Common Language Infrastructure (CLI). Naturally, I wrote an example for them in T-SQL. Now a reader wants an equivalent PL/SQL example.
I figured that borrowing that sample data was appropriate. This creates the conquistador table:
CREATE TABLE conquistador ( conquistador_id NUMBER GENERATED ALWAYS AS IDENTITY , conquistador VARCHAR2(30) , actual_name VARCHAR2(30) , nationality VARCHAR2(30) , lang VARCHAR2(2)); |
Then, you can insert the following data:
INSERT INTO conquistador ( conquistador, actual_name, nationality, lang ) VALUES ('Juan de Fuca','Ioánnis Fokás','Greek','el') ,('Nicolás de Federmán','Nikolaus Federmann','German','de') ,('Sebastián Caboto','Sebastiano Caboto','Venetian','it') ,('Jorge de la Espira','Georg von Speyer','German','de') ,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it') ,('Wenceslao Linck','Wenceslaus Linck','Bohemian','cs') ,('Fernando Consag','Ferdinand Konšcak','Croatian','sr') ,('Américo Vespucio','Amerigo Vespucci','Italian','it') ,('Alejo García','Aleixo Garcia','Portuguese','pt'); |
Next, create a UDT struct(ure) and table of that structure:
/* Create type of object structure. */ CREATE OR REPLACE TYPE conquistador_struct IS OBJECT ( conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30)); / /* Create table of object structure. */ CREATE OR REPLACE TYPE conquistador_table IS TABLE OF conquistador_struct; / |
Now, we can create a table function that filters on nationality:
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 | CREATE OR REPLACE FUNCTION getConquistador (pv_lang IN VARCHAR) RETURN conquistador_table IS /* Declare a return variable. */ lv_retval CONQUISTADOR_TABLE := conquistador_table(); /* Declare a dynamic cursor. */ CURSOR get_conquistador ( cv_lang VARCHAR2 ) IS SELECT c.conquistador , c.actual_name , c.nationality FROM conquistador c WHERE c.lang = cv_lang; /* Local procedure to add to the song. */ PROCEDURE ADD ( pv_input CONQUISTADOR_STRUCT ) IS BEGIN lv_retval.EXTEND; lv_retval(lv_retval.COUNT) := pv_input; END ADD; BEGIN /* Read through the cursor and assign to the UDT table. */ FOR i IN get_conquistador(pv_lang) LOOP ADD(conquistador_struct( conquistador => i.conquistador , actual_name => i.actual_name , nationality => i.nationality )); END LOOP; /* Return collection. */ RETURN lv_retval; END; / |
You can select only the rows with German nationality, qualified by the ISO standard DE with the following query:
COL conquistador FORMAT A21 COL actual_name FORMAT A21 COL nationality FORMAT A12 SELECT * FROM TABLE(getConquistador('de')); |
It should return:
CONQUISTADOR ACTUAL_NAME NATIONALITY --------------------- --------------------- ------------ Nicolás de Federmán Nikolaus Federmann German Jorge de la Espira Georg von Speyer German |
I retested this on Oracle Database 23ai Free, and found the NLS character set fails to support the characters for Nicolás de Federmán. It returns:
CONQUISTADOR ACTUAL_NAME NATIONALITY --------------------- --------------------- ------------ Nicol??s de Federm??n Nikolaus Federmann German Jorge de la Espira Georg von Speyer German |
This is interesting because it brought to my attention that Oracle does try to account for unreadable ASCII values based on character sets. In Google’s GO language these would be considered “Runes”.
As always, I hope these coding samples help those solving new problems.
PL/SQL Coupled Loops
A standard approach using fall through in languages that support it, like C, C++, and Java leverages a case structure. Unfortunately, in language that follow the Pascal pattern case statements act like if-else-if-else statements. In the Pascal model, you need to use coupled loops.
PL/SQL follows that Pascal, via Ada, structure. So, you must couple the performance of the inner descending loop to the maximum value of the outer ascending loop. Here’s an example of how you would code that in PL/SQL with the 12 Days of Christmas song, which has a chorus that grows with each verse.
It requires the definition of two ADTs (Attribute Data Types), a UDT (User-Defined Data Type) like a struct(ure), and a UDT table of the UDT struct. They’re all done in this code snippet.
/* Create a list of strings. */ CREATE OR REPLACE TYPE days IS TABLE OF VARCHAR2(8); / /* Create a list of strings. */ CREATE OR REPLACE TYPE song IS TABLE OF VARCHAR2(36); / /* Create a record structure. */ CREATE OR REPLACE TYPE lyric IS OBJECT ( DAY VARCHAR2(8) , gift VARCHAR2(24)); / /* Create a list of the record structure. */ CREATE OR REPLACE TYPE lyrics IS TABLE OF LYRIC; / |
The twelve_days function that will display the lyrics of the 12-Days of Christmas. It reads forward through the 12 days of Christmas and backwards through the chorus in the inner loop beginning with the current day of the outer 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 | /* Create a function. */ CREATE OR REPLACE FUNCTION twelve_days ( pv_days DAYS , pv_gifts LYRICS ) RETURN song IS /* Initialize the collection of lyrics. */ lv_retval SONG := song(); /* Local procedure to add to the song. */ PROCEDURE ADD ( pv_input VARCHAR2 ) IS BEGIN lv_retval.EXTEND; lv_retval(lv_retval.COUNT) := pv_input; END ADD; BEGIN /* Read forward through the days. */ FOR i IN 1..pv_days.COUNT LOOP ADD('On the ' || pv_days(i) || ' day of Christmas'); ADD('my true love sent to me:'); /* Read backward through the lyrics based on ascending value of the day. */ FOR j IN REVERSE 1..i LOOP IF i = 1 THEN ADD('- '||'A'||' '||pv_gifts(j).gift); ELSE ADD('- '||pv_gifts(j).DAY||' '||pv_gifts(j).gift); END IF; END LOOP; /* A line break by verse. */ ADD(CHR(13)); END LOOP; /* Return the song's lyrics. */ RETURN lv_retval; END; / |
The typical test case for the function in PL/SQL is:
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 | /* Test the function in PL/SQL. */ SET SERVEROUTPUT ON SIZE UNLIMITED DECLARE /* * Declare an lv_days array of an 8 character variable length string * and initialize it with values. */ lv_days days := days('first','second','third','fourth' ,'fifth','sixth','seventh','eighth' ,'nineth','tenth','eleventh','twelfth'); /* * Declare an lv_gifts array of the user-defined LYRIC data type and * initialize it with values. */ lv_gifts lyrics := lyrics(lyric(DAY => 'and a', gift => 'Partridge in a pear tree') ,lyric(DAY => 'Two', gift => 'Turtle doves') ,lyric(DAY => 'Three', gift => 'French hens') ,lyric(DAY => 'Four', gift => 'Calling birds') ,lyric(DAY => 'Five', gift => 'Golden rings' ) ,lyric(DAY => 'Six', gift => 'Geese a laying') ,lyric(DAY => 'Seven', gift => 'Swans a swimming') ,lyric(DAY => 'Eight', gift => 'Maids a milking') ,lyric(DAY => 'Nine', gift => 'Ladies dancing') ,lyric(DAY => 'Ten', gift => 'Lords a leaping') ,lyric(DAY => 'Eleven',gift => 'Pipers piping') ,lyric(DAY => 'Twelve',gift => 'Drummers drumming')); /* * Declare an lv_days array of an 36 character variable length string * and initialize it with values. */ lv_song song := song(); BEGIN /* Call the twelve_days function and assign the results to the local * lv_song variable. */ lv_song := twelve_days(lv_days,lv_gifts); /* * Read the lines from the local lv_song variable and print them. */ FOR i IN 1..lv_song.LAST LOOP dbms_output.put_line(lv_song(i)); END LOOP; END; / |
It displays:
On the first day of Christmas my true love sent to me: - A Partridge in a pear tree On the second day of Christmas my true love sent to me: - Two Turtle doves - and a Partridge in a pear tree On the third day of Christmas my true love sent to me: - Three French hens - Two Turtle doves - and a Partridge in a pear tree ... On the twelfth day of Christmas my true love sent to me: - Twelve Drummers drumming - Eleven Pipers piping - Ten Lords a leaping - Nine Ladies dancing - Eight Maids a milking - Seven Swans a swimming - Six Geese a laying - Five Golden rings - Four Calling birds - Three French hens - Two Turtle doves - and a Partridge in a pear tree |
As always, I hope this helps those learning how to write PL/SQL and/or code.
PL/SQL List to Struct
This post shows you how to take a list of strings and convert them into a struct(ure) of a date, number, and string. Oracle implements IDL (Interface Description Language), which means the solution requires creating:
- An attribute data type (ADT), or collection of a standard date type, which in this case is a varchar2 (variable length string).
- A user defined type (UDT), which is an object type with or without methods. In this case, it implements a UDT without methods.
- A collection of the UDT object type (known in Oracle’s parlance as a table).
Here’s the data definition language (DDL) for the three required structures:
- The ADT of strings:
CREATE OR REPLACE TYPE tre IS TABLE OF VARCHAR2(100); /
- The UDT struct(ure) object type:
CREATE OR REPLACE TYPE struct IS OBJECT ( xdate DATE , xnumber NUMBER , xstring VARCHAR2(20)); /
- The UDT structs collection or Oracle table:
CREATE OR REPLACE TYPE structs IS TABLE OF struct; /
Next, we create a function that accepts a collection of strings and returns a record UDT, which is the struct(ure) object type. As PL/SQL code gets larger, using conditional compilation can help discover problems.
Enable conditional compilation with the following statement:
ALTER SESSION SET PLSQL_CCFLAGS = 'debug:1'; |
The cast_strings function is defined as:
CREATE OR REPLACE FUNCTION cast_strings ( pv_list TRE ) RETURN struct IS /* Declare a UDT and initialize an empty struct variable. */ lv_retval STRUCT := struct( xdate => NULL , xnumber => NULL , xstring => NULL); /* A debugger function. */ FUNCTION debugger ( pv_string VARCHAR2 ) RETURN VARCHAR2 IS /* Declare return value. */ lv_retval VARCHAR2(60); BEGIN /* Conditional compilation evaluation. */ $IF $$DEBUG = 1 $THEN lv_retval := 'Evaluating ['||pv_string||']'; $END /* Return debug value. */ RETURN lv_retval; END debugger; BEGIN /* Loop through list of values to find only the numbers. */ FOR i IN 1..pv_list.LAST LOOP /* Print debugger remark. */ dbms_output.put_line(debugger(pv_list(i))); /* Ensure that a sparsely populated list can't fail. */ IF pv_list.EXISTS(i) THEN /* Order if number evaluation before string evaluation. */ CASE /* Implement WHEN clause that checks that the xnumber member is null and that the pv_list element contains only digits; and assign the pv_list element to the lv_retval's xnumber member. */ WHEN REGEXP_LIKE(pv_list(i),'^[[:digit:]]*$') THEN lv_retval.xnumber := pv_list(i); /* Implement WHEN clause that checks that the xdate member is null and that the pv_list element is a valid date; and assign the pv_list element to the lv_retval's xdate member. */ WHEN verify_date(pv_list(i)) THEN lv_retval.xdate := pv_list(i); /* Implement WHEN clause that checks that the xstring member is null and that the pv_list element contains only alphanumeric values; and assign the pv_list element to the lv_retval's xstring member. */ WHEN REGEXP_LIKE(pv_list(i),'^([[:alnum:]]|[[:punct:]]|[[:space:]])*$') THEN lv_retval.xstring := pv_list(i); ELSE NULL; END CASE; END IF; END LOOP; /* Print the results. */ RETURN lv_retval; END; / |
The following tests the cast_strings function:
DECLARE /* Define a list. */ lv_list TRE := tre('16-APR-2018','Day after ...','1040'); /* Declare a structure. */ lv_struct STRUCT := struct( xdate => NULL , xnumber => NULL , xstring => NULL); BEGIN /* Assign a parsed value set to get a value structure. */ lv_struct := cast_strings(lv_list); /* Print the values of the compound struct variable. */ dbms_output.put_line('xstring ['||lv_struct.xstring||']'); dbms_output.put_line('xdate ['||TO_CHAR(lv_struct.xdate,'DD-MON-YYYY')||']'); dbms_output.put_line('xnumber ['||lv_struct.xnumber||']'); END; / |
If the PLSQL_CCFLAGS is enabled for the session, the function will display these three rows:
Evaluating [16-APR-2018] Evaluating [DAY after ...] Evaluating [1040] |
Whether the PLSQL_CCFLAGS is set or not, the test case returns the following:
xstring [DAY after ...] xdate [16-APR-2018] xnumber [1040] |
As always, I hope this helps those looking for new approaches.
No Set f(x) in GO
Curious little tidbit as I continue my exploration of the GO programming language. I discovered that it doesn’t have a native set function that would let you create a set from a list. For example, converting a non-unique list of integers into a sorted set of integers is easy in Python:
a = [1, 2, 3, 1, 7, 6, 2, 5, 1, 3, 2] for x in sorted(list(set(a))): print(x) |
That returns:
1 2 3 5 6 7 |
Unfortunately, there’s no equivalent to Python’s IDLE (Integrated Development and Learning Environment) for GO. The best you have is to use the GO Playground; which gives you a shell where you can write small functions and your main() method to test concepts.
Here’s the equivalent of the two (or if you like three) lines of Python code in the GO Programming language to print the unique integers in an ascending sort:
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 | // You can edit this code! // Click here and start typing. package main import ( "fmt" "sort" ) // Convert a slice of non-unique integers into a set. func set(slice []int) []int { // Define a map and slice. keys := make(map[int]bool) set := make([]int, 0, len(slice)) // Traverse slice, assign new values as map keys, and assign map keys to new slice. for _, val := range slice { if !keys[val] { keys[val] = true set = append(set, val) } } // Return the set of integers. return set } func main() { // Create a slice of non-unique integers. a := []int{1, 2, 3, 1, 7, 6, 2, 5, 1, 3, 2} // Create a map of unique integers, as keys of the Boolean map. b := set(a) // Sort the unordered keys into ordered keys. sort.Ints(b) // Print the sorted unique results from the original non-unique slice. for _, i := range b { fmt.Println(i) } } |
You can past this into the GO Playground and click the run button to get those same sorted and unique values:
1 2 3 5 6 7 |
As always, I hope this helps a few users of the blog.
Hidden thought SQL question
I’m always amazed at how people ask questions about skill sets. A few weeks ago, I ran into a neat Oracle SQL question that hinges on whether the individual truly understands One Phase Commit (1PC) and Two Phase Commit (2PC) operations.
It’s a simple question that tests two levels of understanding. The question is:
- If you create table A and insert a row of data and subsequently you create table B and insert a row of data before issuing a ROLLBACK; statement, how many rows of data will you find in table A and table B?
Level 1 Understanding
Here’s the test script:
CREATE TABLE a1 ( text VARCHAR2(12) ); INSERT INTO a1 ( text ) VALUES ('Hello World!'); CREATE TABLE b1 ( text VARCHAR2(12) ); INSERT INTO b1 ( text ) VALUES ('Hello World!'); ROLLBACK; |
The answer is 1 row in table A1 and no row in table B1 because the second CREATE statement issues an implicit COMMIT. However, the INSERT statement to table B1 is a 2PC and the ROLLBACK statement undoes the first phase of the INSERT statement and removes the data. If you were using a sequence value in the INSERT statement, the sequence value would be consumed because it’s not replaced by a ROLLBACK statement.
Level 2 Understanding
Here’s the test script:
CREATE TABLE a2 AS (SELECT 'Hello World!' AS text); CREATE TABLE b2 AS (SELECT 'Hello World!' AS text); ROLLBACK; |
The answer is 1 row in table A2 and 1 row in table B2 because a CREATE statement using the AS clause subquery issues an implicit COMMIT on the data inserted from the subquery because its a 1PC transaction.
Most likely and interviewer would be looking for level one understanding but you can demonstrate mastery by sharing level two understanding. As always, I hope this helps those reading it.
Go Config & PostgreSQL
Finally, I got around to building a GO environment with PostgreSQL on Ubuntu. Next, I’ll need to sort out how to extend it to Oracle and MySQL. The only tedious part was figuring out where the current PostgreSQL ODBC driver was on GitHub.
The installation of GO has two options. You can install the version from the Linux Distro’s repo, or you can install the most current version from the GO website. The generic install on Ubuntu uses the following two commands:
sudo apt update sudo apt install -y golang-go |
This will install GO in the /usr/bin directory, which means you don’t need to add it manually to your $PATH environment variable in your .bashrc file.
Download from the GO website into your /home/username/Downloads directory and then run this command:
sudo tar -C /usr/local -xzf /home/student/Downloads/go1.24.0.linux-amd64.tar.gz |
If you haven’t installed the distro’s repo of the GO programming environment, you should add this to your .bashrc file:
# Added to PATH for Go export set PATH=$PATH:/usr/local/go/bin |
However, add the following if you have installed the Linux distorts GO repo and prefer to use the most current release available (a which -a go command displays the go program files in your current $PATH variable and you can call the first one in the list without an absolute or fully qualified file name but the second requires a fully qualified file name):
# Added to PATH for Go export set PATH=/usr/local/go/bin:$PATH |
Now, you should decide where you want to put your GO programs. I chose to create a base .go directory and then project directories beneath that. Within each project directory you should create bin, pkg, and src subdirectories, as shown below:
mkdir .go .go/hello .go/hello/bin .go/hello/pkg .go/hello/src |
You can write a “Hello World” program like the following in you ~.go/hello/src directory, which I’ve named hello-world.go for illustrative purposes:
// Declare package. package main // Import library. import "fmt" // Define main function using fmt library to print. func main() { fmt.Println("Hello World!") } |
Next, you should create a makefile, like this in the same ~.go/hello/src directory. If you’re new to the rules for creating a makefile, there can be no leading or trailing white spaces, and you should use before commands:
.DEFAULT_GOAL := build .PHONY: fmt vet build fmt: go fmt ./... vet: fmt go vet ./... build: vet go build -o ../bin/hello hello-world.go |
Before you run the make utility, you need to initialize the module with this syntax:
go mod init |
It will create the go.mod file. You will see something like the following when you display the content of the go.mod file.
module hello-world.go go 1.24.0 |
You have completed your project preparation. You run the make command to create the hello executable in the ~/.go/hello/bin directory.
Run the make utility:
make |
You can run it from your current directory with this command:
~/.go/hello/bin/hello |
It will return to console:
Hello World! |
Having built the GO development environment and a rudimentary starting program, it’s time to learn how to write a GO program that queries the PostgreSQL database. The first step is finding the correct PostgreSQL library in GitHub.com. A number of web sites post examples using the pq library but that’s no longer correct.
You need to download the pgx ODBC driver with the following GO command:
go get github.com/jackc/pgx@latest |
It will display the following to the console:
go: downloading github.com/jackc/pgx v3.6.2+incompatible go: downloading github.com/pkg/errors v0.9.1 go: downloading golang.org/x/crypto v0.35.0 go: downloading golang.org/x/text v0.22.0 go: added github.com/jackc/pgx v3.6.2+incompatible go: added github.com/pkg/errors v0.9.1 go: added golang.org/x/crypto v0.35.0 go: added golang.org/x/text v0.22.0 |
To use the standard database/sql approach you’ll also need to run this command:
go get github.com/jackc/pgx/v5/stdlib |
It will display the following to the console:
go: downloading github.com/jackc/pgx/v5 v5.7.2 go: downloading github.com/jackc/pgpassfile v1.0.0 go: downloading github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 go: downloading github.com/jackc/puddle/v2 v2.2.2 go: downloading golang.org/x/sync v0.11.0 go: added github.com/jackc/pgpassfile v1.0.0 go: added github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 go: added github.com/jackc/pgx/v5 v5.7.2 go: added github.com/jackc/puddle/v2 v2.2.2 |
Change back to your home directory and run this command to build a new connect project:
mkdir .go/postgres .go/postgres/connect .go/postgres/connect/bin .go/postgres/connect/pkg .go/postgres/connect/src |
Create postgres-connect.go program in the .go/postgres/connect/src directory:
package main import ( "database/sql" "fmt" "os" _ "github.com/jackc/pgx/v5/stdlib" ) func main() { // Open connection and defer connection close. db, err := sql.Open("pgx", "host=localhost user=student password=student dbname=videodb sslmode=disable") if err != nil { fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err) os.Exit(1) } defer db.Close() // Declare an output variable for the query. var version string err = db.QueryRow("SELECT version()").Scan(&version) if err != nil { fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err) os.Exit(1) } // Print database version. fmt.Println(version) } |
Next, you should create another makefile in the same ~.go/postgres/connect/src directory.
.DEFAULT_GOAL := build .PHONY: fmt vet build fmt: go fmt ./... vet: fmt go vet ./... build: vet go build -o ../bin/connect postgres-connect.go |
Before you run the make utility, you need to initialize the go.mod module file and check for dependencies in it. That requires the following two commands:
go mod init |
Which returns the following list of dependencies:
go: finding module for package github.com/jackc/pgx/v5/stdlib go: found github.com/jackc/pgx/v5/stdlib in github.com/jackc/pgx/v5 v5.7.2 |
There are two ways to fix this but the easiest is to run this command:
go mod tidy |
It adds the dependencies to the go.mod file. You will see something like the following when you display the content of the go.mod file.
module postgres-connect.go go 1.24.0 require github.com/jackc/pgx/v5 v5.7.2 require ( github.com/jackc/pgpassfile v1.0.0 // indirect github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect github.com/jackc/puddle/v2 v2.2.2 // indirect golang.org/x/crypto v0.31.0 // indirect golang.org/x/sync v0.10.0 // indirect golang.org/x/text v0.21.0 // indirect ) |
You should also note that the go mod tidy command created new go.sum file for the dependencies, which should look like the following:
github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c= github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM= github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg= github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 h1:iCEnooe7UlwOQYpKFhBabPMi4aNAfoODPEFNiAnClxo= github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM= github.com/jackc/pgx/v5 v5.7.2 h1:mLoDLV6sonKlvjIEsV56SkWNCnuNv531l94GaIzO+XI= github.com/jackc/pgx/v5 v5.7.2/go.mod h1:ncY89UGWxg82EykZUwSpUKEfccBGGYq1xjrOpsbsfGQ= github.com/jackc/puddle/v2 v2.2.2 h1:PR8nw+E/1w0GLuRFSmiioY6UooMp6KJv0/61nB7icHo= github.com/jackc/puddle/v2 v2.2.2/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4= github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM= github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4= github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME= github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI= github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg= github.com/stretchr/testify v1.8.1 h1:w7B6lhMri9wdJUVmEZPGGhZzrYTPvgJArz7wNPgYKsk= github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4= golang.org/x/crypto v0.31.0 h1:ihbySMvVjLAeSH1IbfcRTkD/iNscyz8rGzjF/E5hV6U= golang.org/x/crypto v0.31.0/go.mod h1:kDsLvtWBEx7MV9tJOj9bnXsPbxwJQ6csT/x4KIN4Ssk= golang.org/x/sync v0.10.0 h1:3NQrjDixjgGwUOCaF8w2+VYHv0Ve/vGYSbdkTa98gmQ= golang.org/x/sync v0.10.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk= golang.org/x/text v0.21.0 h1:zyQAAkrwaneQ066sspRyJaG9VNi/YJ1NfzcGB3hZ/qo= golang.org/x/text v0.21.0/go.mod h1:4IBbMaMmOPCJ8SecivzSH54+73PCFmPWxNTLm+vZkEQ= gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0= gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA= gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= |
Run the make utility:
make |
You can now run make to create the connect executable, which you can call with the following command:
~/.go/postgres/connect/bin/connect |
It will return to console:
PostgreSQL 14.17 (Ubuntu 14.17-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit |
Adding polars to Python3
Just a quick note to correct the Polars instruction, you should install using pip3 with the following command:
pip3 install polars |
You’ll get something like the following:
Defaulting to user installation because normal site-packages is not writeable
Collecting polars
Downloading polars-1.21.0-cp39-abi3-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (31.6 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 31.6/31.6 MB 11.5 MB/s eta 0:00:00
Installing collected packages: polars
Successfully installed polars-1.21.0 |
While I always add a python link to alias python3, that’s not possible with the pip3 utility because there is also a pip utility in the /usr/bin directory.
As always, I hope this helps somebody with the task at hand.
Using Python’s getopt
A couple of my students wanted me to write a switch and parameter handler for Python scripts. I wrote it just to show them it’s possible but I also show them how to do it correctly with the Python getopt library, which was soft-deprecated in Python 3.13 and replaced by the Python argparse library. The debate is which one I show you first in the blog.
This is the getops.py script that uses Python’s getopt library. There is a small trick to the options and long options values. You append a colon (:) to the option when it has a value, and append an equal (=) to the long option when it has a value.
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 | #!/usr/bin/python # Import libraries. import getopt, sys import mysql.connector from mysql.connector import errorcode # Define local function. def help(): # Declare display string. display = \ """ Program Help +---------------+-------------+-------------------+ | -h --help | | Help switch. | | -o --output | output_file | Output file name. | | -q --query | query_file | Query file name. | | -v --verbose | | Verbose switch. | +---------------+-------------+-------------------+""" # Return string. return display # ============================================================ # Set local variables for switch and parameter placeholders. # ============================================================ display = False log = [] output_file = '' query_file = '' verbose = False opts = "ho:q:v" long_opts = ["help","output=","query=","verbose"] # ============================================================ # Capture argument list minus the program name. # ============================================================ args = sys.argv[1:] # ============================================================ # Use a try-except block. # ============================================================ try: # Assign the results of the getopt function. params, values = getopt.getopt(args, opts, long_opts) # Loop through the parameters. for curr_param, curr_value in params: if curr_param in ("-h","--help"): print(help()) elif curr_param in ("-o","--output"): output_file = curr_value elif curr_param in ("-q","--query"): query_file = curr_value elif curr_param in ("-v","--verbose"): verbose = True # Append entry to log. log.append('[' + curr_param + '][' + curr_value + ']') # Print verbose parameter handling. if verbose: print(" Parameter Diagnostics\n-------------------------") for i in log: print(i) # Exception block. except getopt.GetoptError as e: # output error, and return with an error code print (str(e)) |
You can run the program in Linux or Unix with the following syntax provided that you’ve already set the parameters to 755. That means granting the file owner with read, write, and execute privileges, and group and other with read and execute privileges.
./getopts.py -h -o output.txt -q query.sql -v |
It would return the following:
Program Help +---------------+-------------+-------------------+ | -h --help | | Help switch. | | -o --output | output_file | Output file name. | | -q --query | query_file | Query file name. | | -v --verbose | | Verbose switch. | +---------------+-------------+-------------------+ Parameter Diagnostics ------------------------- [-h][] [-o][output.txt] [-q][query.sql] [-v][] |
If you didn’t notice, I also took the opportunity to write the help display in such a way that a maintenance programmer could add another switch or parameter easily. This way the programmer only needs to add a new row of text and add an elif statement with the new switch or parameter.
I think using Python’s getopt library is the cleanest and simplest way to implement switch and parameter handling, after all it’s the basis for so many C derived libraries. However, if you must write your own, below is an approach that would work:
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 | #!/usr/bin/python # Import libraries. import sys import mysql.connector from mysql.connector import errorcode # ============================================================ # Set local variables for switch and parameter placeholders. # ============================================================ help = False display = \ """ Program Help +---------------+-------------+-------------------+ | -h --help | | Help switch. | | -o --output | output_file | Output file name. | | -q --query | query_file | Query file name. | | -v --verbose | | Verbose switch. | +---------------+-------------+-------------------+""" log = [] output = '' query = '' verbose = False # ============================================================ # Capture argument list minus the program name. # ============================================================ args = sys.argv[1:] # ============================================================ # If one or more args exists and the first one is an # a string that can cast to an int, convert it to an int, # assign it to a variable, and ignore any other args # in the list. # ============================================================ if len(args) > 1 and args[0].isdigit(): powerIn = int(args[0]) # Check for switches and parameters. if isinstance(args,list) and len(args) >= 1: # Set the limit of switches and parameters. argc = len(args) # Enumerate through switches first and then parameters. for i in range(argc): if args[i][0] == '-': # Evaluate switches and ignore any parameter value. if args[i] in ['-h','--help']: help = True # Append entry to log. log.append('[' + str(args[i]) + ']') elif args[i] in ['-v','--verbose']: verbose = True # Append entry to log. log.append('[' + str(args[i]) + ']') # Evaluate parameters. elif i < argc and not args[i+1][0] == '-': if args[i] in ['-q','--query']: query = args[i+1] elif args[i] in ['-o','--output']: output = args[i+1] # Append entry to log. log.append('[' + str(args[i]) + '][' + args[i+1] + ']') else: continue continue # Print the help display when if help: print(display) # Print the parameter handling collected in the log variable. if verbose: for i in log: print(i) |
As you can see from the example, I didn’t give it too much effort. I think it should prove you should use the approach adopted by the general Python community.
SQL Calculations #1
This was principally written for my SQL students but I thought it might be useful to others. SQL calculation are performed row-by-row in the SELECT-list. In its simplest form without even touching a table, you can add two literal numbers like this:
1 | SELECT 2 + 2 AS result; |
It will display the result of the addition to the column alias result as a derived table, or the following result:
+--------+ | result | +--------+ | 4 | +--------+ 1 row in set (0.00 sec) |
Unfortunately, the use of literal values as shown above doesn’t really let you see how the calculation is made row-by-row because it only returns one row. You can rewrite the two literal values into one variable by using a Common Table Expressions (CTEs). The CTE creates an struct tuple with only one x element. Another way to describe what the CTE does would say, it creates a derived table named struct with a single x column in the SELECT-list.
The CTE runs first, then a subsequent query may use the CTE’s derived table results. Below is a query that uses the value in the struct.x derived table (or references the struct tuple’s x element) twice while assigning the value to a new column alias, labelled result. The FROM clause places the struct tuple in the queries namespace, which lets you reference it in the SELECT-list.
1 2 3 4 | WITH struct AS (SELECT 2 AS x) SELECT struct.x + struct.x AS result FROM struct; |
Like the literal example, it will display the result of the addition to the column alias result as a derived table of one row:
+--------+ | result | +--------+ | 4 | +--------+ 1 row in set (0.00 sec) |
Having laid a basis for a simple calculation in one row, let’s expand the example and demonstrate how to perform row-by-row calculations. The example requires introducing some new concepts. One uses the UNION ALL set operator to fabricate a CTE derived table with three rows. Another uses a comma within the WITH clause to create two derived tables or CTEs. The last uses the CROSS JOIN to add the single row CTE’s single y column to each of the rows returned by the multiple row CTE.
The CROSS JOIN is a Cartesian product, which multiplies the rows in one table against the rows in another table while adding the columns from each table. That means fabricating a table of one column and one row lets you put a variable into all the rows of another table or set of tables combined through an equijoin or non-equijoin operation.
The query below takes a struct1 derived table of one column and three rows and a struct2 derived table of one column and one row, then uses a CROSS JOIN to create a new derived table, which would be a table of two columns and three rows. The Cartesian product only provides the two columns that we will multiply to create new data.
The SELECT-list lets us fabricate a new column where we multiply the values of column x and column y to create a set of new results in column result.
1 2 3 4 5 6 7 8 9 10 | WITH struct1 AS (SELECT 1 AS x UNION ALL SELECT 2 AS x UNION ALL SELECT 3 AS x) , struct2 AS (SELECT 10 AS y) SELECT struct1.x AS x , struct2.y AS y , struct1.x * struct2.y AS result FROM struct1 CROSS JOIN struct2; |
The query returns the following results, which show the values used to calculate the result and the result:
+---+----+--------+ | x | y | result | +---+----+--------+ | 1 | 10 | 10 | | 2 | 10 | 20 | | 3 | 10 | 30 | +---+----+--------+ 3 rows in set (0.00 sec) |
As a rule, the columns x and y would not be displayed in the final derived table. You would only see the result columns’ values.
Let’s use an example from Alan Bwaulieu’s Learning SQL book with a twist. Rather than manually fabricating the ordinal numbers twice, let’s use the scope reference of a subsequent CTE to reference an earlier CTE. That would create two ten row tables of one column each, or a Cartesian product of a 100 row table with two columns. Then, let’s use the SELECT-list lets us fabricate only a new column, which will add 1 to the numbers 0 to 99 to give us the numbers 1 to a 100.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | WITH ones AS (SELECT 0 AS x UNION ALL SELECT 1 AS x UNION ALL SELECT 2 AS x UNION ALL SELECT 3 AS x UNION ALL SELECT 4 AS x UNION ALL SELECT 5 AS x UNION ALL SELECT 6 AS x UNION ALL SELECT 7 AS x UNION ALL SELECT 8 AS x UNION ALL SELECT 9 AS x ) , tens AS (SELECT x * 10 AS x FROM ones) SELECT ones.x + tens.x + 1 AS ordinal FROM ones CROSS JOIN tens ORDER BY ordinal; |
It returns the following result set:
+---------+ | ordinal | +---------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | ... | 98 | | 99 | | 100 | +---------+ 100 rows in set (0.00 sec) |
Moving on to more complex math, let’s create a numerals table with the result from our prior query. It will enable calculating the factors of exponents. The easiest way to create the table is shown below (only caveat is that it will build it with a biting rather than an int data type).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE numerals AS WITH ones AS (SELECT 0 AS x UNION ALL SELECT 1 AS x UNION ALL SELECT 2 AS x UNION ALL SELECT 3 AS x UNION ALL SELECT 4 AS x UNION ALL SELECT 5 AS x UNION ALL SELECT 6 AS x UNION ALL SELECT 7 AS x UNION ALL SELECT 8 AS x UNION ALL SELECT 9 AS x ) , tens AS (SELECT x * 10 AS x FROM ones) SELECT ones.x + tens.x + 1 AS ordinal FROM ones CROSS JOIN tens ORDER BY ordinal; |
Line #15 sets the column alias that determines the name of the column for the numerals table. It can be described after running the foregoing script in MySQL as:
+---------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------+------+-----+---------+-------+ | ordinal | bigint | NO | | 0 | | +---------+--------+------+-----+---------+-------+ 1 row in set (0.00 sec) |
The next query accepts a substitution variable into the WITH clause, which means an external program will call it. (Although, you could use a session level variable, which I would discourage.) This query returns the factors for any given exponent:
1 2 3 4 5 6 7 8 9 | WITH magic AS (SELECT %s AS vkey) SELECT CONCAT(magic.vkey,'^',LOG(magic.vkey,n.ordinal)) AS powers , n.ordinal AS result FROM numerals n CROSS JOIN magic WHERE MOD(n.ordinal,magic.vkey) = 0 AND LOG(magic.vkey,n.ordinal) REGEXP '^[0-9]*$' OR n.ordinal = 1 ORDER BY n.ordinal; |
FYI, in the WHERE clause the regular expression is guarantees only rows returning integer values, and the 1 returns the identity property of an exponent raised to the zero power.
Assuming you created the numerals table, put the foregoing query in a query.sql file (because I was to lazy to write the full parameter handling), and you run it in the same directory as this Python program, it’ll take any valid integer as a value.
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 | #!/usr/bin/python # ------------------------------------------------------------ # Name: power.py # Date: 19 Oct 2024 # ------------------------------------------------------------ # Purpose: # ------- # The program shows you how to provide a single agrument # to a query and print the formatted output. # # You can call the program: # # ./power.py 3 # # ------------------------------------------------------------ # Import libraries. import sys import mysql.connector from mysql.connector import errorcode # ============================================================ # Define a local padding function. # ============================================================ def pad(valueIn): # Define local variable. padding = '' # Convert single digit numbers to strings. if isinstance(valueIn,int) and len(str(valueIn)) == 1: padding = ' ' # Return padding space. return padding # ============================================================ # End local function defintion. # ============================================================ # Define any local variables. powerIn = 2 query = "" # ============================================================ # Capture argument list minus the program name. # ============================================================ arguments = sys.argv[1:] # ============================================================ # If one or more arguments exists and the first one is an # a string that can cast to an int, convert it to an int, # assign it to a variable, and ignore any other arguments # in the list. # ============================================================ if len(arguments) >= 1 and arguments[0].isdigit(): powerIn = int(arguments[0]) # ============================================================ # Use a try-catch block to read and parse a query from a # a file found in the same local directory as the Python # program. # ============================================================ try: file = open('query.sql','r') query = file.read().replace('\n',' ').replace(';','') file.close() except IOError: print("Could not read file:", fileName) # ============================================================ # Attempt connection in a try-catch block. # ============================================================ # -------------------------------------------------------- # Open connection, bind variable in query and format # query output before closing the cursor. # -------------------------------------------------------- try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Create cursor. cursor = cnx.cursor() # Execute cursor, and coerce string to tuple. cursor.execute(query, (powerIn,)) # Display the rows returned by the query. for (powers, result) in cursor: print((" {} is: {}").format(powers, pad(result) + str(result))) # Close cursor. cursor.close() # -------------------------------------------------------- # Handle MySQL exception # -------------------------------------------------------- except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # -------------------------------------------------------- # Close connection after try-catch completes. # -------------------------------------------------------- # Close the connection when the try block completes. else: cnx.close() |
If you forget to call it with a numeric parameter, it uses 2 as the default. You would call it as follows from a Linux prompt from the local directory:
./power.py |
It returns:
2^0 is: 1 2^1 is: 2 2^2 is: 4 2^3 is: 8 2^4 is: 16 2^5 is: 32 2^6 is: 64 |
If you call it with a numeric parameter, it uses the numeric value. You would call it as follows from a Linux prompt from the local directory:
./power.py 3 |
It returns:
3^0 is: 1 3^1 is: 3 3^2 is: 9 3^3 is: 27 3^4 is: 81 |
As always, I hope the post helps folks sort out how and why things work.
Subscribe via RSS