Archive for the ‘Stored Procedures’ Category
Oracle’s Sparse Lists
Oracle’s PL/SQL Programming Language is really quite nice. I’ve written 8 books on it and still have fun coding in it. One nasty little detail about Oracle’s lists, introduced in Oracle 8 as PL/SQL Tables according their documentation, is they rely on sequential numeric indexes. Unfortunately, Oracle lists support a DELETE method, which can create gaps in the sequential indexes.
Oracle calls a sequence without gaps densely populated and a sequence with gaps sparsely populated. This can cause problems when PL/SQL code inadvertently removes elements at the beginning, end, or somewhere in the middle of the list. That’s because a program can then pass the sparsely populated list as a parameter to another stored function or procedure where the developer may traverse the list in a for-loop. That traversal may raise an exception in a for-loop, like this when it has gaps in the index sequence:
DECLARE * ERROR AT line 1: ORA-01403: no data found ORA-06512: AT line 20 |
Oracle’s myriad built-in libraries don’t offer a function to compact a sparsely populated list into a densely populated list. This post provides a compact stored procedure that converts a sparsely populated list to a densely populated list.
The first step to using the compact stored procedure requires that you create an object type in SQL, like this list of 20-character strings:
DROP TYPE list; CREATE OR REPLACE TYPE list IS TABLE OF VARCHAR2(20); / |
Now, you can implement the compact stored procedure by passing the User-Defined Type as it’s sole parameter.
CREATE OR REPLACE PROCEDURE compact ( sparse IN OUT LIST ) IS /* Declare local variables. */ iterator NUMBER; -- Leave iterator as null. /* Declare new list. */ dense LIST := list(); BEGIN /* Initialize the iterator with the starting value, which is necessary because the first element of the original list could have been deleted in earlier operations. Setting the initial iterator value to the first numeric index value ensures you start at the lowest available index value. */ iterator := sparse.FIRST; /* Convert sparsely populated list to densely populated. */ WHILE (iterator <= sparse.LAST) LOOP dense.EXTEND; dense(dense.COUNT) := sparse(iterator); iterator := sparse.NEXT(iterator); END LOOP; /* Replace the input parameter with the compacted list. */ sparse := dense; END; / |
Before we test the compact stored procedure, let’s create deleteElement stored procedure for our testing:
CREATE OR REPLACE PROCEDURE deleteElement ( sparse IN OUT LIST , element IN NUMBER ) IS BEGIN /* Delete a value. */ sparse.DELETE(element); END; / |
Now, let’s use an anonymous block to test compacting a sparsely populated list into a densely populated list. The test program will remove the first, last, and one element in the middle before printing the sparsely populated list’s index and string values. This test will show you gaps in the remaining non-sequential index values.
After you see the gaps, the test program compacts the remaining list values into a new densely populated list. It then prints the new index values with the data values.
DECLARE /* Declare a four item list. */ lv_strings LIST := list('one','two','three','four','five','six','seven'); BEGIN /* Check size of list. */ dbms_output.put_line('Print initial list size: ['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); /* Delete a value. */ deleteElement(lv_strings,lv_strings.FIRST); deleteElement(lv_strings,3); deleteElement(lv_strings,lv_strings.LAST); /* Check size of list. */ dbms_output.put_line('Print modified list size: ['||lv_strings.COUNT||']'); dbms_output.put_line('Print max index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); FOR i IN 1..lv_strings.LAST LOOP IF lv_strings.EXISTS(i) THEN dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']'); END IF; END LOOP; /* Call a procedure by passing current sparse collection and the procedure returns dense collection. */ dbms_output.put_line('==================================='); dbms_output.put_line('Compacting list.'); compact(lv_strings); dbms_output.put_line('==================================='); /* Print the new maximum index value and list size. */ dbms_output.put_line('Print new index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); FOR i IN 1..lv_strings.COUNT LOOP dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']'); END LOOP; dbms_output.put_line('==================================='); END; / |
It produces output, like:
Print initial list size: [7] =================================== Print modified list size: [4] Print max index and size: [6][4] =================================== List list index and item: [2][two] List list index and item: [4][four] List list index and item: [5][five] List list index and item: [6][six] =================================== Compacting list. =================================== Print new index and size: [4][4] =================================== List list index and item: [1][two] List list index and item: [2][four] List list index and item: [3][five] List list index and item: [4][six] =================================== |
You can extend this concept by creating User-Defined Types with multiple attributes, which are essentially lists of tuples (to draw on Pythonic lingo).
MySQL Shell Parsing
I’ve been experimenting with the mysqlsh since installing it last week. It’s been interesting. Overall, I’m totally impressed but I did find a problem with how it parses stored procedures.
First thought is always, is it my code? I checked the file by running it as a script file through MySQL Workbench. It ran perfectly in MySQL Workbench but failed repeatedly when run from the mysqlsh utility. Next step, reduce the code to a small test case, retest it, and log a bug if it is replicated. My test case in a test.sql file generates the following errors when run from the mysqlsh utility:
MySQL localhost:33060+ ssl studentdb SQL > source test.sql Query OK, 0 rows affected (0.0003 sec) ERROR: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE PROCEDURE test ( pv_input1 CHAR(12) , pv_input2 CHAR(19)) MODIFIES SQL ' at line 2 Query OK, 0 rows affected (0.0002 sec) Query OK, 0 rows affected (0.0003 sec) ERROR: 1305: PROCEDURE studentdb.test does not exist |
The same file generates this series of successful messages when run as a script file from MySQL Workbench:
Preparing... Importing test.sql... Finished executing script Statement CREATE PROCEDURE test pv_input1 One Operation completed successfully |
For those who are curious enough to review the test case, here it is:
-- Reset the delimiter so that a semicolon can be used as a statement and block terminator. DELIMITER $$ SELECT 'CREATE PROCEDURE test' AS "Statement"; CREATE PROCEDURE test ( pv_input1 CHAR(12) , pv_input2 CHAR(19)) MODIFIES SQL DATA BEGIN SELECT CONCAT(pv_input1,', ',pv_input2) AS message; END; $$ -- Reset the standard delimiter to let the semicolon work as an execution command. DELIMITER ; -- Call the test procedure. CALL test('One','Two'); |
The reply in the bug explained the behavior difference between MySQL Workbench and the MySQL Shell (mysqlsh) environments. MySQL Workbench uses the MySQL client, which supports multiple client statements with the CLIENT_MULTI_STATEMENTS option. Recognizing that, the logging entry SELECT statement should move to a position before setting the DELIMITER, like:
-- Set a label for the log file. SELECT 'CREATE PROCEDURE test' AS "Statement"; -- Reset the delimiter so that a semicolon can be used as a statement and block terminator. DELIMITER $$ CREATE PROCEDURE test ( pv_input1 CHAR(12) , pv_input2 CHAR(19)) MODIFIES SQL DATA BEGIN SELECT CONCAT(pv_input1,', ',pv_input2) AS message; END; $$ -- Reset the standard delimiter to let the semicolon work as an execution command. DELIMITER ; -- Call the test procedure. CALL test('One','Two'); |
The new test case only submits one statement at a time. The logging query is submitted by the semicolon, and the test procedure by the double dollar ($$) symbol set.
So, I was correct identifying a parsing behavior difference between MySQL Workbench and MySQL Shell. It appears to be a difference by design but the MySQL Shell documentation fails to explain it can’t manage multiple statements. I hope identifying this saves others time.
It’s also true that the MySQL client software supports TEE and NOTEE to write log files. Unfortunately, MySQL Shell (mysqlsh) doesn’t support the TEE and NOTEE syntax. You can only do minimal logging with the control of standard error (stderr) by using the application and AdminAPI log utilities, which are covered in Chapter 8 of the MySQL Shell 8.0 documentation.
Postgres Overloaded Routines
Earlier I showed how to write an anonymous block in PostgreSQL PL/pgSQL to drop routines, like functions and procedures. However, it would only work when they’re not overloaded functions or procedures. The following lets you drop all routines, including overloaded functions and procedures. Overloaded procedures are those that share the same name but have different parameter lists.
Before you can test the anonymous block, you need to create a set of overloaded functions or procedures. You can create a set of overloaded hello procedures with the following syntax:
CREATE FUNCTION hello() RETURNS text AS $$ DECLARE output VARCHAR; BEGIN SELECT 'Hello World!' INTO output; RETURN output; END $$ LANGUAGE plpgsql; CREATE FUNCTION hello(whom text) RETURNS text AS $$ DECLARE output VARCHAR; BEGIN SELECT CONCAT('Hello ',whom,'!') INTO output; RETURN output; END $$ LANGUAGE plpgsql; CREATE FUNCTION hello(id int, whom text) RETURNS text AS $$ DECLARE output VARCHAR; BEGIN SELECT CONCAT('[',id,'] Hello ',whom,'!') INTO output; RETURN output; END $$ LANGUAGE plpgsql; |
You can test the overloaded hello function, like so from the videodb schema:
videodb=> SELECT hello(); hello -------------- Hello World! (1 ROW) videodb=> SELECT hello('Captain Marvel'); hello ----------------------- Hello Captain Marvel! (1 ROW) videodb=> SELECT hello(1,'Captain America'); hello ---------------------------- [1] Hello Captain America! (1 ROW) |
Then, you can query the information_schema to verify that you’ve created a set of overloaded procedures with the following query:
SELECT proc.specific_schema AS procedure_schema , proc.specific_name , proc.routine_name AS procedure_name , proc.external_language , args.parameter_name , args.parameter_mode , args.data_type FROM information_schema.routines proc left join information_schema.parameters args ON proc.specific_schema = args.specific_schema AND proc.specific_name = args.specific_name WHERE proc.routine_schema NOT IN ('pg_catalog', 'information_schema') AND proc.routine_type IN ('FUNCTION','PROCEDURE') ORDER BY procedure_schema , specific_name , procedure_name , args.ordinal_position; |
It should return the following:
procedure_schema | specific_name | procedure_name | external_language | parameter_name | parameter_mode | data_type ------------------+---------------+----------------+-------------------+----------------+----------------+----------- public | hello_35451 | hello | PLPGSQL | | | public | hello_35452 | hello | PLPGSQL | whom | IN | text public | hello_35453 | hello | PLPGSQL | id | IN | integer public | hello_35453 | hello | PLPGSQL | whom | IN | text (4 rows) |
The set session command maps the videodb catalog for the following anonymous block program.
SET SESSION "videodb.catalog_name" = 'videodb'; |
The following anonymous block lets you get rid of any ordinary or overloaded function and procedure:
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 | DO $$ DECLARE /* Declare an indefinite length string for SQL statement. */ sql VARCHAR; /* Declare variables to manage cursor return values. */ row RECORD; arg VARCHAR; /* Declare parameter list. */ list VARCHAR; /* Declare a routine cursor. */ routine_cursor CURSOR FOR SELECT routine_name , specific_name , routine_type FROM information_schema.routines WHERE specific_catalog = current_setting('videodb.catalog_name') AND routine_schema = 'public'; /* Declare a parameter cursor. */ parameter_cursor CURSOR (cv_specific_name varchar) FOR SELECT args.data_type FROM information_schema.parameters args WHERE args.specific_schema = 'public' AND args.specific_name = cv_specific_name; BEGIN /* Open the cursor. */ OPEN routine_cursor; <<row_loop>> LOOP /* Fetch table names. */ FETCH routine_cursor INTO row; /* Exit when no more records are found. */ EXIT row_loop WHEN NOT FOUND; /* Initialize parameter list. */ list := '('; /* Open the parameter cursor. */ OPEN parameter_cursor(row.specific_name::varchar); <<parameter_loop>> LOOP FETCH parameter_cursor INTO arg; /* Exit the parameter loop. */ EXIT parameter_loop WHEN NOT FOUND; /* Add parameter and delimit more than one parameter with a comma. */ IF LENGTH(list) > 1 THEN list := CONCAT(list,',',arg); ELSE list := CONCAT(list,arg); END IF; END LOOP; /* Close the parameter list. */ list := CONCAT(list,')'); /* Close the parameter cursor. */ CLOSE parameter_cursor; /* Concatenate together a DDL to drop the table with prejudice. */ sql := 'DROP '||row.routine_type||' IF EXISTS '||row.routine_name||list; /* Execute the DDL statement. */ EXECUTE sql; END LOOP; /* Close the routine_cursor. */ CLOSE routine_cursor; END; $$; |
Now, you possess the magic to automate cleaning up your schema when you combine this with my earlier post on dynamically dropping tables, sequences, and triggers.
Functions disallow NDS
My students asked if you could embed an OFFSET x ROWS FETCH NEXT y ROWS ONLY clause in a SQL Server T-SQL user-defined function. The answer is no, it isn’t Oracle (yes, you can do that in Oracle Database 12c with an NDS statement). There’s an example in Chapter 2 of my Oracle Database 12c PL/SQL Programming book if you’re interested. I also demonstrate a different approach to SQL Server T-SQL table functions in this older post. However, an attempt to add the clause to a SQL Server T-SQL function, like this:
CREATE FUNCTION studentdb.getBatch (@rows AS INT ,@offset AS INT) RETURNS @output TABLE ( marvel_id INT , avenger_name VARCHAR(30) , first_name VARCHAR(20) , last_name VARCHAR(20)) AS BEGIN /* Insert the results into the table variable. */ INSERT @output SELECT marvel_id , avenger_name , first_name , last_name FROM studentdb.marvel OFFSET (@offset - 1) ROWS FETCH NEXT @rows ROWS ONLY; /* Return the table variable from the function. */ RETURN; END; |
Throws the following errors trying to compile the function:
Msg 102, Level 15, State 1, Procedure getBatch, Line 16 Incorrect syntax near '@offset'. Msg 153, Level 15, State 2, Procedure getBatch, Line 16 Invalid usage of the option NEXT in the FETCH statement. |
If you have a strong background in Oracle and can sort through the dynamic SQL syntax for T-SQL, you might try re-writing the function to use the EXEC SP_EXECUTESQL @var; command. That rewrite that attempts to use NDS (Native Dynamic SQL) would look like this:
CREATE FUNCTION studentdb.getBatch (@rows AS INT ,@offset AS INT) RETURNS @output TABLE ( marvel_id INT , avenger_name VARCHAR(30) , first_name VARCHAR(20) , last_name VARCHAR(20)) AS BEGIN DECLARE /* Declare a variable for a dynamic SQL statement. */ @stmt VARCHAR(400); /* Assign the SQL statement to a variable. */ SET @stmt = N'SELECT marvel_id ' + N', avenger_name ' + N', first_name ' + N', last_name ' + N'FROM studentdb.marvel ' + N'OFFSET ' + (@offset - 1) + N' ' + N'ROWS FETCH NEXT ' + @rows + N' ROWS ONLY;'; BEGIN /* Insert the results into the table variable. */ INSERT @output EXEC sp_executesql @stmt; END; /* Return the table variable from the function. */ RETURN; END; |
Throws the following exception because you can’t use dynamic dispatch inside a T-SQL function:
Msg 443, Level 16, State 14, Procedure getBatch, Line 23 Invalid use of a side-effecting operator 'INSERT EXEC' within a function. |
On the other hand you can rewrite the statement with a BETWEEN operator and it works somewhat like an OFFSET and FETCH operation. That refactored function would be written as follows:
CREATE FUNCTION studentdb.getBatch (@rowa AS INT ,@rowb AS INT) RETURNS @output TABLE ( marvel_id INT , avenger_name VARCHAR(30) , first_name VARCHAR(20) , last_name VARCHAR(20)) AS BEGIN /* Insert the results into the table variable. */ INSERT @output SELECT marvel_id , avenger_name , first_name , last_name FROM studentdb.marvel WHERE marvel_id BETWEEN @rowa AND @rowb; /* Return the table variable from the function. */ RETURN; END; |
It doesn’t raise an exception. You can call the table function like this:
SELECT * FROM getBatch(2,3); |
It returns the two rows for Iron Man and Black Widow. As always, I hope this helps.
If you want to create the test case, here’s the script you need:
SELECT 'Conditionally drop studentdb.marvel table.' AS "Statement"; IF OBJECT_ID('studentdb.marvel','U') IS NOT NULL DROP TABLE studentdb.marvel; SELECT 'Create studentdb.marvel table.' AS "Statement"; CREATE TABLE studentdb.marvel ( marvel_id INT NOT NULL IDENTITY(1,1) CONSTRAINT marvel_pk PRIMARY KEY , avenger_name VARCHAR(30) NOT NULL , first_name VARCHAR(20) NOT NULL , last_name VARCHAR(20) NOT NULL); /* Insert the rows. */ INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Hulk','Bruce','Banner'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Iron Man','Tony','Stark'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Black Widow','Natasha','Romanoff'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Thor','Thor','Odinsson'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Captain America','Steve','Rogers'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Hawkeye','Clint','Barton'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Winter Soldier','Bucky','Barnes'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Iron Patriot','James','Rhodey'); /* Query the contents of the MARVEL table. */ SELECT * FROM studentdb.marvel; |
Capture MySQL Foreign Keys
Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.
You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:
1 2 3 4 5 6 7 8 9 10 11 | SELECT CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' ' ,'ADD CONSTRAINT',' fk_',tc.constraint_name,' ' ,'FOREIGN KEY (',kcu.column_name,')',' ' ,'REFERENCES',' ',kcu.referenced_table_schema,'.',kcu.referenced_table_name,' ' ,'(',kcu.referenced_column_name,');') AS script FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'foreign key' ORDER BY tc.TABLE_NAME , kcu.column_name; |
In MySQL 8 forward, you must make a case sensitive comparison, like:
9 | WHERE tc.constraint_type = 'FOREIGN KEY' |
You would add a line in the WHERE clause to restrict it to a schema and a second line to restrict it to a table within a schema, like this:
AND tc.table_schema = 'your_mysql_database' AND tc.table_name = 'your_table_name' |
Unfortunately, when the primary and foreign keys involve two or more columns you require a procedure and function. The function because you need to read two cursors, and the NOT FOUND can’t be nested in the current deployment of MySQL’s SQL/PSM stored programs. In this example the storedForeignKeys procedure finds the table’s foreign key constraints, and the columnList function adds the column detail. The command_list table stores the commands to restore foreign key constraints.
The command_list table that stores the values is:
CREATE TABLE command_list ( command_list_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , sql_command VARCHAR(6) NOT NULL , sql_object VARCHAR(10) NOT NULL , sql_constraint VARCHAR(11) , sql_statement VARCHAR(768) NOT NULL); |
This is the storedForeignKeys procedure:
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 | CREATE PROCEDURE storeForeignKeys ( pv_schema_name VARCHAR(64) , pv_table_name VARCHAR(64)) BEGIN /* Declare local variables. */ DECLARE lv_schema_name VARCHAR(64); DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); DECLARE sql_stmt VARCHAR(1024); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor for foreign key table, it uses null replacement because the procedure supports null parameters. When you use null parameters, you get all foreign key values. */ DECLARE foreign_key_table CURSOR FOR SELECT tc.table_schema , tc.table_name , tc.constraint_name FROM information_schema.table_constraints tc WHERE tc.table_schema = IFNULL(lv_schema_name, tc.table_schema) AND tc.table_name = IFNULL(lv_table_name, tc.table_name) AND tc.constraint_type = 'FOREIGN KEY' ORDER BY tc.table_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Assign parameter values to local variables. */ SET lv_schema_name := pv_schema_name; SET lv_table_name := pv_table_name; /* Open a local cursor. */ OPEN foreign_key_table; cursor_foreign_key_table: LOOP /* Fetch a row into the local variables. */ FETCH foreign_key_table INTO lv_schema_name , lv_table_name , lv_constraint_name; /* Catch handler for no more rows found from the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key_table; END IF; /* The nested calls to the columnList function returns the list of columns in the foreign key. Surrogate primary to foreign keys can be resolved with a simply query but natural primary to foreign key relationships require the list of columns involved in the primary and foreign key. The columnList function returns the list of foreign key columns in the dependent table and the list of referenced columns (or the primary key columns) in the independent table. */ SET sql_stmt := CONCAT('ALTER TABLE ',' ',lv_schema_name,'.',lv_table_name,' ' ,'ADD CONSTRAINT ',lv_constraint_name,' ' ,'FOREIGN KEY (',columnList(lv_schema_name,lv_table_name,lv_constraint_name)); /* Record the SQL statements. */ INSERT INTO command_list ( sql_command , sql_object , sql_constraint , sql_statement ) VALUES ('ALTER' ,'TABLE' ,'FOREIGN KEY' , sql_stmt ); END LOOP cursor_foreign_key_table; CLOSE foreign_key_table; END; $$ |
This is the columnList function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 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 | CREATE FUNCTION columnList ( pv_schema_name VARCHAR(64) , pv_table_name VARCHAR(64) , pv_constraint_name VARCHAR(64)) RETURNS VARCHAR(512) BEGIN /* Declare local variables. */ DECLARE lv_schema_name VARCHAR(64); DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); DECLARE lv_column_count INT UNSIGNED; DECLARE lv_column_name VARCHAR(64); DECLARE lv_column_list VARCHAR(512); DECLARE lv_column_ref_list VARCHAR(64); DECLARE lv_referenced_table_schema VARCHAR(64); DECLARE lv_referenced_table_name VARCHAR(64); DECLARE lv_referenced_column_name VARCHAR(64); DECLARE lv_return_string VARCHAR(768); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor for foreign key column. */ DECLARE foreign_key_column CURSOR FOR SELECT kcu.column_name , kcu.referenced_table_schema , kcu.referenced_table_name , kcu.referenced_column_name FROM information_schema.key_column_usage kcu WHERE kcu.referenced_table_schema = lv_schema_name AND kcu.table_name = lv_table_name AND kcu.constraint_name = lv_constraint_name ORDER BY kcu.column_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Assign parameter values to local variables. */ SET lv_schema_name := pv_schema_name; SET lv_table_name := pv_table_name; SET lv_constraint_name := pv_constraint_name; /* Set the first column value. */ SET lv_column_count := 1; /* Open the nested cursor. */ OPEN foreign_key_column; cursor_foreign_key_column: LOOP /* Fetch a row into the local variables. */ FETCH foreign_key_column INTO lv_column_name , lv_referenced_table_schema , lv_referenced_table_name , lv_referenced_column_name; /* Catch handler for no more rows found from the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key_column; END IF; /* Initialize the column list or add to it. */ IF lv_column_count = 1 THEN SET lv_column_list := lv_column_name; SET lv_column_ref_list := lv_referenced_column_name; /* Increment the counter value. */ SET lv_column_count := lv_column_count + 1; ELSE SET lv_column_list := CONCAT(lv_column_list,',',lv_column_name); SET lv_column_ref_list := CONCAT(lv_column_ref_list,',',lv_referenced_column_name); END IF; END LOOP cursor_foreign_key_column; CLOSE foreign_key_column; /* Set the return string to a list of columns. */ SET lv_return_string := CONCAT(lv_column_list,')',' ' ,'REFERENCES',' ',lv_referenced_table_schema,'.',lv_referenced_table_name,' ' ,'(',lv_column_ref_list,');'); RETURN lv_return_string; END; $$ |
You can call the procedure with a schema and table name, and you’ll get the foreign keys from just that table. You can create the following parent and child tables to test how multiple column foreign keys work in the script (provided because most folks use surrogate keys):
CREATE TABLE parent ( first_name VARCHAR(20) NOT NULL DEFAULT '' , last_name VARCHAR(20) NOT NULL DEFAULT '' , PRIMARY KEY (first_name, last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE child ( child_name VARCHAR(20) NOT NULL , first_name VARCHAR(20) DEFAULT NULL , last_name VARCHAR(20) DEFAULT NULL , PRIMARY KEY (child_name) , KEY fk_parent(first_name, last_name) , CONSTRAINT fk_parent FOREIGN KEY (first_name, last_name) REFERENCES parent (first_name, last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
You call the storeForeignKeys procedure for the child table with this syntax:
CALL storeForeignKeys('studentdb', 'child'); |
You call the storeForeignKeys procedure for all tables in a schema with this syntax:
CALL storeForeignKeys('studentdb', null); |
While unlikely you’ll need this, the following calls the storeForeignKeys procedure for all tables in all schemas:
CALL storeForeignKeys(null, null); |
You can export the command sequence with the following command to a script file:
SELECT sql_statement INTO OUTFILE 'c:/Data/MySQL/apply_foreign_keys.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' FROM command_list; |
While preservation of tables and foreign keys is best managed by using a tool, like MySQL Workbench, it’s always handy to have scripts to do specific tasks. I hope this helps those looking for how to preserve foreign keys. You also can find a comprehensive treatment on how to write SQL/PSM code in Chapter 14 of my Oracle Database 11g and MySQL 5.6 Developer Handbook.
Signal from a procedure
As I review with my students, a stored function works like a standalone program, while a stored procedure runs in the scope of another program unit. For example, you can compare the result of a function as an expression in an IF statement, like:
IF add_numbers(1,3) > 3 THEN ... ELSE ... END IF; |
You can’t call procedures inside an IF statement, but you can call the procedure and use a single OUT-mode (pass-by-reference) parameter from the procedure in a subsequent IF statement. You can implement a a wait procedure like that with the following example.
The example first creates two tables, the road_runner and coyote tables:
-- Drop road_runner table if exists. DROP TABLE IF EXISTS road_runner; -- Create roadrunner table. CREATE TABLE road_runner ( road_runner_id int unsigned auto_increment primary key , road_runner_text varchar(20) , CONSTRAINT road_runner_nk UNIQUE (road_runner_text)); -- Drop coyote table if exists. DROP TABLE IF EXISTS coyote; -- Create coyote table. CREATE TABLE coyote ( coyote_id int unsigned auto_increment primary key , coyote_text varchar(20) , road_runner_id int unsigned , CONSTRAINT coyote_nk UNIQUE (coyote_text)); |
The following creates a procedure that:
- Writes data to two tables when the values are unique, returning a value of zero when it works.
- Writes data to neither table when the values to either table are non-unique, returning a value of one when it fails.
The procedure uses a 0 as a false value and a 1 as a true value. The use of a 0 and 1 for truth is a consistent approach for languages where they don’t support a Boolean data type.
-- Change the delimiter to a "$$" DELIMITER $$ -- Drop the paired procedure. DROP PROCEDURE IF EXISTS two_table$$ -- Create the paired procedure. CREATE PROCEDURE two_table (IN pv_road_runner_text varchar(20) ,IN pv_coyote_text varchar(20) , OUT pv_confirm_it int) BEGIN /* Declare a variable to hold a sequence value for an auto incrementing value. */ DECLARE lv_road_runner_id int unsigned; /* Declare a condition variable for attempting to write a non-unique record to a table. */ DECLARE duplicate CONDITION FOR 1062; /* Declare an event handler for a duplicate condition variable, rollback transaction, and set 1 as a false condition. */ DECLARE EXIT HANDLER FOR duplicate BEGIN ROLLBACK to all_or_none; SET pv_confirm_it = 1; END; /* Start the transaction. */ START TRANSACTION; /* Set the save point for a multiple table transaction. */ SAVEPOINT all_or_none; /* Insert into road runner table. */ INSERT INTO road_runner (road_runner_text) VALUES (pv_road_runner_text); /* Capture the auto incrementing sequence value as a local variable. */ SET lv_road_runner_id := last_insert_id(); /* Insert into the coyote table. */ INSERT INTO coyote (coyote_text ,road_runner_id) VALUES (pv_coyote_text ,lv_road_runner_id); /* Commit the record. */ COMMIT; /* Set the control variable to a true value. */ SET pv_confirm_it := 0; END; $$ DELIMITER ; |
You can test the pass-by-reference procedure with the following code:
-- Set a control variable with a null value. SET @sv_control := null; -- Query the results from a join between the road_runner and coyote tables. SELECT * FROM road_runner r INNER JOIN coyote c ON r.road_runner_id = c.road_runner_id; -- Call the two_table procedure with unique results. CALL two_table('Road Runner 1','Coyote 1',@sv_control); -- Query the control variable result from the second call to the -- two_table procedure. SELECT @sv_control AS "1st Insert"; -- Reset the value for a second test. SET @sv_control := 0; -- Call teh two_table procedure with unique results. CALL two_table('Road Runner 2','Coyote 1',@sv_control); -- Query the results from a join between the road_runner and coyote tables. SELECT * FROM road_runner r INNER JOIN coyote c ON r.road_runner_id = c.road_runner_id; -- Query the control variable result from the second call to the -- two_table procedure. SELECT @sv_control AS "2nd Insert"; |
I hope this helps anybody trying to implement a pass-by-reference procedure with a control variable. You can find other examples in Chapter 14 of Oracle Database 11g and MySQL 5.6 Developer Handbook (pp. 446-449 and 450-451).
Cleanup a MySQL Schema
My students required way to remove all their tables, views, and constraints from a MySQL database (or the alias schema). Since they’re using referential or foreign key constraints, I also wrote one procedure to drop all foreign key constraints from a database. There’s also another to drop views. The final stored procedure calls the procedure that drops foreign keys, then calls the procedure to drop views before dropping all the tables.
Here’s the dropForeignKeys stored procedure, but if you want to capture ALTER statements that add these back later please check my follow-up Capture MySQL Foreign Keys post.
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 | -- Provide a log file debugging statement. SELECT 'DROP PROCEDURE IF EXISTS dropForeignKeys'; -- Conditionally drop the procedure. DROP PROCEDURE IF EXISTS dropForeignKeys; -- Provide a log file debugging statement. SELECT 'CREATE PROCEDURE dropForeignKeys'; -- Change delimiter to create procedure. DELIMITER $$ -- Create procedure. CREATE PROCEDURE dropForeignKeys ( pv_database VARCHAR(64) , pv_referenced_table VARCHAR(64)) BEGIN /* Declare local statement variables. */ DECLARE lv_stmt VARCHAR(1024); /* Declare local cursor variables. */ DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor. */ DECLARE foreign_key_cursor CURSOR FOR SELECT rc.table_name , rc.constraint_name FROM information_schema.referential_constraints rc WHERE constraint_schema = IFNULL(pv_database,database()) AND referenced_table_name = pv_referenced_table ORDER BY rc.table_name , rc.constraint_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Open a local cursor. */ OPEN foreign_key_cursor; cursor_foreign_key: LOOP FETCH foreign_key_cursor INTO lv_table_name , lv_constraint_name; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key; END IF; /* Set a SQL statement by using concatenation. */ SET @SQL := CONCAT('ALTER TABLE',' ',lv_table_name,' ','DROP FOREIGN KEY',' ',lv_constraint_name); /* Prepare, run, and deallocate statement. */ PREPARE lv_stmt FROM @SQL; EXECUTE lv_stmt; DEALLOCATE PREPARE lv_stmt; END LOOP cursor_foreign_key; CLOSE foreign_key_cursor; END; $$ -- Reset delimiter to run SQL statements. DELIMITER ; |
Here’s the dropViews stored procedure:
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 | -- Provide a log file debugging statement. SELECT 'DROP PROCEDURE IF EXISTS dropViews'; -- Conditionally drop the procedure. DROP PROCEDURE IF EXISTS dropViews; -- Provide a log file debugging statement. SELECT 'CREATE PROCEDURE dropViews'; -- Change delimiter to create procedure. DELIMITER $$ -- Create procedure. CREATE PROCEDURE dropViews ( pv_database VARCHAR(64)) BEGIN /* Declare local statement variables. */ DECLARE lv_stmt VARCHAR(1024); /* Declare local cursor variables. */ DECLARE lv_view_name VARCHAR(64); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor. */ DECLARE view_cursor CURSOR FOR SELECT v.table_name FROM information_schema.views v WHERE table_schema = IFNULL(pv_database, database()) ORDER BY v.table_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Open a local cursor. */ OPEN view_cursor; cursor_view: LOOP FETCH view_cursor INTO lv_view_name; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_view; END IF; /* Set a SQL statement by using concatenation. */ SET @SQL := CONCAT('DROP VIEW',' ',lv_view_name); /* Prepare, run, and deallocate statement. */ PREPARE lv_stmt FROM @SQL; EXECUTE lv_stmt; DEALLOCATE PREPARE lv_stmt; END LOOP cursor_view; CLOSE view_cursor; END; $$ -- Reset delimiter to run SQL statements. DELIMITER ; |
Here’s the dropTables stored procedure:
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 | CREATE PROCEDURE dropTables ( pv_database VARCHAR(64)) BEGIN /* Declare local statement variables. */ DECLARE lv_stmt VARCHAR(1024); /* Declare local cursor variables. */ DECLARE lv_table_name VARCHAR(64); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor. */ DECLARE table_cursor CURSOR FOR SELECT t.table_name FROM information_schema.tables t WHERE table_schema = IFNULL(pv_database, database()) ORDER BY t.table_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Drop the views. */ CALL dropViews(null); /* Open a local cursor. */ OPEN table_cursor; cursor_table: LOOP FETCH table_cursor INTO lv_table_name; /* Place the catch handler for no more rows found immediately after the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_table; END IF; /* Drop the tables. */ CALL dropForeignKeys(null,lv_table_name); /* Set a SQL statement by using concatenation. */ SET @SQL := CONCAT('DROP TABLE',' ',lv_table_name); /* Prepare, run, and deallocate statement. */ PREPARE lv_stmt FROM @SQL; EXECUTE lv_stmt; DEALLOCATE PREPARE lv_stmt; END LOOP cursor_table; CLOSE table_cursor; END; $$ -- Reset delimiter to run SQL statements. DELIMITER ; |
You put these in a rerunnable script, run it, and then call the dropTables stored procedure. You can pass a database (or schema) name or a null value. When you pass a null value, it uses the current database, like:
CALL dropTables(null); |
As always, I hope this helps others.
SQL Injection Risks
While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind inputs to data types, and avoid providing completely dynamic WHERE clauses.
Here’s the correct way to dynamically generate a result from a MySQL Stored Procedure:
CREATE PROCEDURE hello (IN pv_input VARCHAR(50)) BEGIN SELECT sample_id , sample_name FROM sample WHERE sample_name = pv_input; END; $$ |
A call to this hello procedure will only return the row or rows where the pv_input value matches the sample_name column value. Any attempt to exploit it like the one below fails.
CALL hello('\'Harriet\' OR 1 = 1'); |
It fails because there’s no matching 'Harriet' OR 1 = 1 in the table’s sample_name column. However, it works well when we submit 'Harriet' by herself, without the intended SQL inject phrase “OR 1 = 1“, as you can see:
+-----------+-------------+ | sample_id | sample_name | +-----------+-------------+ | 2 | Harriet | +-----------+-------------+ 1 row in set (0.00 sec) |
There are two approaches that you should never put in your code because they can be readily exploited unless you carefully parse the incoming string parameter. The problem in both cases is causes by concatenation rather than binding. The first example is extremely unlikely as an error but possible.
CREATE PROCEDURE hello (IN pv_input VARCHAR(50)) BEGIN SET @sql := CONCAT('SELECT sample_id, sample_name FROM sample WHERE sample_name = ',pv_input); PREPARE stmt FROM @sql; EXECUTE stmt; END; $$ |
The last hello procedure using concatenation and a prepared statement is vulnerable to SQL injection. A call like the following would return all two rows in the sample table.
CALL hello('\'Juliette\' OR 1 = 1'); |
It would display:
+-----------+-------------+ | sample_id | sample_name | +-----------+-------------+ | 1 | Hank | | 2 | Harriet | +-----------+-------------+ 2 rows in set (0.00 sec) |
While there’s no sample_name value of 'Juliette', the “OR 1 = 1” is true. Therefore, the SELECT statement filters out nothing and returns all the data from the table. It’s probably clear you’d never do this type of prepared statement inside a stored procedure, but most SQL Injection attacks exploit your scripting language implementation. Unfortunately, bad coding practices can infrequently expose this type of vulnerability; and they typically occur when a junior programmers is following a bad coding example.
A solution with the WHERE clause as part of the parameter would look like this:
CREATE PROCEDURE hello (IN pv_where VARCHAR(50)) BEGIN SELECT sample_id , sample_name FROM sample pv_where; END; $$ |
The modified call:
CALL hello('WHERE sample_name = \'Juliette\' OR 1 = 1'); |
returns all rows from the table.
A solution without the WHERE clause as part of the parameter would look like the following but it fails during runtime and returns no rows [Updated in response to comment]. The failure has nothing to do with the comment’s dialog on the CONCAT function, which also added nothing to the example once I tested it.
CREATE PROCEDURE hello (IN pv_where VARCHAR(50)) BEGIN SELECT sample_id , sample_name FROM sample WHERE pv_where; END; $$ |
It’s simply works only when you provide a “1 = 1” or other comparison without embedded apostrophes (‘) but fails with embedded apostrophes. That means the following statement fails:
CALL hello('sample_name = \'Juliette\' OR 1 = 1'); |
but this SQL injection statement works:
CALL hello('1 = 1'); |
returns all rows from the table.
This example, when you omit the white space also works with embedded strings or numeric operands and an operator:
CREATE PROCEDURE hello (IN pv_where VARCHAR(50)) BEGIN SELECT sample_id , sample_name FROM sample WHEREpv_where; END; $$ |
It returns all rows with a call like this:
CALL hello('sample_name = \'Juliette\' OR 1 = 1'); |
My take initially was that it might be a bug, and I logged one (Bug 68903). That’s was a dumb thing to do because WHEREpv_where simply becomes a table alias in the query.
In conclusion, the first example is a good practice. The other two should never exist! Well, they shouldn’t exist unless you’re parsing the web form inputs vigilantly.
Hope this helps those trying to understand how to avoid SQL injection attacks. Always try to solve dynamic SQL statement problems by binding variables into statements.
MySQL Database Triggers
One of the students wanted an equivalent example to an Oracle DML trigger sample that replaces a white space in a last name with a dash for an INSERT statement. Apparently, the MySQL trigger example in the Oracle Database 11g and MySQL 5.6 Developer Handbook was a bit long. I have to agree with that because the MySQL DML trigger demonstrated cursors and loops in the trigger code.
Triggers can be statement- or row-level actions. Although some databases let you define statement-level triggers, MySQL doesn’t support them. MySQL only supports row-level triggers. Row-level triggers support critical or non-critical behaviors. Critical behavior means the trigger observes an insert, update, or delete that must be stopped, which means it raises an error. Non-critical behavior means the trigger observes a DML statement and logs it or implements a change during the context of the DML activity.
The first example shows you non-critical behavior. It observes an attempt to enter a two-part last name, and replaces the white space with a dash (you can find help on MySQL Regular Expressions in this other post). This means the trigger ensures compliance on how names are entered in the database, which should be protected in the web form (through JQuery or JavaScript) and the database.
DELIMITER $$ DROP TRIGGER IF EXISTS contact_insert$$ CREATE TRIGGER contact_insert BEFORE INSERT ON contact FOR EACH ROW BEGIN IF new.last_name REGEXP '^.* .*$' THEN SET new.last_name := REPLACE(new.last_name,' ','-'); END IF; END; $$ DELIMITER ; |
The problem with implementing a non-critical trigger is that the database performs the work but clerks entering the data don’t learn the business rule. A critical trigger simply disallows non-conforming data entry. The next program shows a critical behavior with an UPDATE statement row-level trigger. After all, won’t a data entry clerk update the entry with a white space after the INSERT statement didn’t?
Yes, that was a rhetorical question. Spelling out the business rule in the UPDATE statement row-level trigger should educate the persistent errant behavior. While letting the INSERT statement row-level trigger manage the behavior probably saves time for most end-users who make a casual mistake.
DELIMITER $$ DROP TRIGGER IF EXISTS contact_update$$ CREATE TRIGGER contact_update BEFORE UPDATE ON contact FOR EACH ROW BEGIN IF new.last_name REGEXP '^.* .*$' THEN SIGNAL SQLSTATE '42000'; END IF; END; $$ DELIMITER ; |
Somebody wanted to know why I choose SQLSTATE 42000. That’s because it signals an error in the SQL statement, and that’s the closest existing SQLSTATE to the actual behavior that exists. Moreover, the error identified by the critical trigger signals non-compliance with the application’s SQL standards that protects the data.
Hopefully, this helps somebody looking for a MySQL database trigger example that raises an exception. The example works with MySQL 5.5 forward because the critical trigger uses the SIGNAL feature, which was introduced in MySQL 5.5. Another article shows you how to leverage MyISAM tables to create a logging mechanism for critical event triggers, and you click this link to the MySQL Triggers with Logging blog entry.
Reset sequence START WITH
Two things contributed to this post. One was a student question about the difference between the MAX_VALUE of a sequence and the actual sequence value. The other was a comment on an earlier post addressing an NDS approach to resetting sequences.
The student wanted to understand why there were gaps in the sequence, since they created it with the default values, like this:
CREATE SEQUENCE sequence_name; |
A default sequence in an Oracle database sets the cache value to 20, which means you get gaps every time a new set is allocated. This Ask Tom question and answer holds that information.
The blog comment was on an NDS statement post. I wasn’t quite sure what the comment wanted because there really wasn’t a question or a complete code example. Having demonstrated how to dynamically drop and recreate a new sequence without a gap in a comment reply, I thought that was adequate.
Having pointed the student to the Ask Tom column and my answer, he wanted to know how to create a stored procedure to reset sequences dynamically. It took me a couple weeks to get back to this but here’s the procedure. The tricky element of the procedure is the “no binding values allowed” restriction placed on NDS statements that process DDL statements.
The procedure uses two local procedures and two local functinons. The local find_sequence finds a sequence name in the schema, and find_next_sequence function returns the next value. The local create_sequence and drop_sequence procedures respectively isolate the logic for dynamic drops and creates of the sequence.
Warning: The restriction with this design assumes that the table name and sequence name are linked by using the table name and an _ID suffix.
-- Declare an autonomous procedure to drop sequences. CREATE OR REPLACE PROCEDURE reset_sequence ( pv_table_name VARCHAR2 , pv_sequence_name VARCHAR2 , pv_cache_value NUMBER DEFAULT 0 ) IS -- Declares stubs to remove forward reference limits. PROCEDURE create_sequence ( pv_sequence_name VARCHAR2, pv_cache_value NUMBER ); PROCEDURE drop_sequence ( pv_sequence_name VARCHAR2 ); FUNCTION find_sequence ( pv_sequence_name VARCHAR2 ) RETURN VARCHAR2; FUNCTION find_next_sequence ( pv_table_name VARCHAR2 ) RETURN VARCHAR2; -- Drops sequence. PROCEDURE drop_sequence ( pv_sequence_name VARCHAR2 ) IS -- Declare local variable(s). lv_statement VARCHAR2(200); lv_sequence_name VARCHAR2(30); BEGIN /* Conditionally drop any sequence using a local function to find a valid sequence name before attempting to drop it. */ IF find_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)) > 0 THEN /* Dynamically drop sequence, which requires concatenating the sanitized sequence name because you can't bind values on a DDL statement. */ lv_statement := 'DROP sequence '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name); -- Execute statement immediately. EXECUTE IMMEDIATE lv_statement; END IF; END drop_sequence; -- Drops sequence. PROCEDURE create_sequence ( pv_sequence_name VARCHAR2 , pv_cache_value NUMBER ) IS -- Declare local variable(s). lv_statement VARCHAR2(200); lv_next_sequence NUMBER; BEGIN -- Assign the next sequence value if one exists. lv_next_sequence := find_next_sequence(pv_table_name); -- Check whether a cache value has been provided. IF pv_cache_value > 0 THEN /* Dynamically create a sequence statement with a new start value that is one greater than the highest value in the table that uses the sequence. */ lv_statement := 'CREATE SEQUENCE '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)||CHR(10) || 'MINVALUE 1 NOMAXVALUE INCREMENT BY 1'||CHR(10) || 'START WITH '||lv_next_sequence||' CACHE '||pv_cache_value||' NOORDER NOCYCLE'; ELSE /* Dynamically create a sequence statement with a new start value that is one greater than the highest value in the table that uses the sequence. */ lv_statement := 'CREATE SEQUENCE '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)||CHR(10) || ' MINVALUE 1 NOMAXVALUE'||CHR(10) || 'INCREMENT BY 1 START WITH '||lv_next_sequence||' NOCACHE NOORDER NOCYCLE'; END IF; -- Execute create sequence statement. EXECUTE IMMEDIATE lv_statement; END create_sequence; -- Finds whether a sequence exists. FUNCTION find_sequence ( pv_sequence_name VARCHAR2 ) RETURN VARCHAR2 IS -- Declare local return variable(s). lv_statement VARCHAR2(200); lv_sequence_name VARCHAR2(30); lv_return_value NUMBER := 0; -- Declare local system reference cursor. lv_cursor SYS_REFCURSOR; BEGIN -- Dynamically find sequence. lv_statement := 'SELECT sequence_name'||CHR(10) || 'FROM user_sequences'||CHR(10) || 'WHERE sequence_name = :seq_name'; OPEN lv_cursor FOR lv_statement USING DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name); FETCH lv_cursor INTO lv_sequence_name; CLOSE lv_cursor; -- Convert valid sequence name to positive integer, which represents truth. lv_return_value := LENGTH(lv_sequence_name); -- Return value. RETURN lv_return_value; EXCEPTION -- Reached when DBMS_ASSERT check fails. WHEN OTHERS THEN RETURN lv_return_value; END find_sequence; -- Finds the next sequence value. FUNCTION find_next_sequence ( pv_table_name VARCHAR2 ) RETURN VARCHAR2 IS -- Declare local return variable(s). lv_statement VARCHAR2(200); lv_sequence_value NUMBER; lv_return_value NUMBER := 1; -- Declare local system reference cursor. lv_cursor SYS_REFCURSOR; BEGIN -- Dynamically find sequence. lv_statement := 'SELECT MAX('||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_table_name)||'_ID) + 1'||CHR(10) || 'FROM '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_table_name); OPEN lv_cursor FOR lv_statement; FETCH lv_cursor INTO lv_sequence_value; CLOSE lv_cursor; -- Assign the return value from the NDS statement to a local return variable. lv_return_value := lv_sequence_value; -- Return value. RETURN lv_return_value; EXCEPTION -- Reached when DBMS_ASSERT check fails. WHEN OTHERS THEN RETURN lv_return_value; END find_next_sequence; -- Set precompiler instruction to run in a discrete process. PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- Conditionally drop sequence. drop_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)); -- Create sequence. create_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name), pv_cache_value); END; / |
You can test this procedure with the following script, which includes COMMIT statements. Those are requires because the stand alone procedure runs independently of the current session as an autonomous procedure.
-- Conditionally drop existing tables and sequences. BEGIN FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('SAMPLE','SAMPLE_S')) LOOP IF i.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; dbms_output.put_line(i.object_name); ELSIF i.object_type = 'SEQUENCE' THEN EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; dbms_output.put_line(i.object_name); END IF; END LOOP; END; / -- Create test table. CREATE TABLE sample (sample_id NUMBER); -- Create test table. CREATE SEQUENCE sample_s; -- Insert a seeding row. INSERT INTO sample VALUES (10); -- Commit the values because the procedure is autonomous. COMMIT; -- Reset the sequence value. EXECUTE reset_sequence('SAMPLE','SAMPLE_S'); -- Insert the next sequence value. INSERT INTO sample VALUES (sample_s.nextval); -- Commit the values because the procedure is autonomous. COMMIT; -- Query the result to ensure the numbers are sequential. SELECT * FROM sample; EXECUTE reset_sequence('SAMPLE','SAMPLE_S',500); -- Insert the next sequence value. INSERT INTO sample VALUES (sample_s.nextval); -- Query the result to ensure the numbers are sequential. SELECT * FROM sample; |
Hope this helps anybody interested in automating the process of resetting a START WITH value of an Oracle sequence.
Subscribe via RSS