#
# Dynamic SQL in Stored Procedures: examples.
#
delimiter |
#
# Prepare the tables used in the presentation.
#
DROP TABLE IF EXISTS statements|
#
# ’statements’ table contains various SQL statements, such as SELECT, DROP.
# This table will be used to analyze what statements are supported in
# Dynamic SQL.
#
CREATE TABLE statements (c_id INTEGER PRIMARY KEY AUTO_INCREMENT,
c_text CHAR(45) NOT NULL, c_status VARCHAR(20))|
INSERT INTO statements (c_text) VALUES
(”select 1″), (”flush tables”), (”handler statements open as ha”),
(”analyze table statements”), (”check table statements”),
(”checksum table statements”), (”optimize table statements”),
(”repair table statements”),
(”describe extended select * from statements”),
(”help help”), (”show databases”), (”show tables”),
(”show table status”), (”show open tables”), (”show storage engines”),
(”insert into statements (c_id) values (1)”),
(”update statements set c_status=””),
(”delete from statements”), (”truncate statements”),
(”call dsql_example2()”)|
#
# ‘keywords’ table contains an (incomplete) list of SQL identifiers that
# are reserved by the standard. In this presentation, we’ll write a stored
# procedure that checks which keywords are reserved by MySQL and which are
# not.
#
DROP TABLE IF EXISTS keywords|
#
# The source for this list is an article in DBAzine by Senior Software
# Architect at MySQL, Peter Gulutzan. The article is located at
# http://www.dbazine.com/db2/db2-disarticles/gulutzan5
# Below is a shortened list. The full list can be found in the article.
#
#
CREATE TABLE keywords (c_text CHAR(35) NOT NULL)|
INSERT INTO keywords (c_text) VALUES
(”abs”), (”absolute”), (”access”), (”acquire”), (”action”), (”ada”),
(”add”), (”admin”), (”after”), (”aggregate”), (”alias”), (”all”),
(”allocate”), (”allow”), (”alter”), (”and”), (”any”), (”are”),
(”array”), (”as”), (”asc”), (”asensitive”), (”assertion”), (”asutime”),
(”asymmetric”), (”at”), (”atomic”), (”audit”), (”authorization”), (”aux”),
(”auxiliary”), (”avg”), (”backup”), (”before begin”), (”between”), (”bigint”),
(”binary”), (”bit”), (”bit_length”), (”blob”), (”boolean”), (”both”),
(”breadth”), (”break”), (”browse”), (”bufferpool”), (”bulk”), (”by”),
(”call”), (”called”), (”capture”), (”cardinality”), (”cascade”), (”cascaded”),
(”case”), (”cast”), (”catalog”), (”ccsid”), (”ceil”), (”ceiling”),
(”char”), (”char_length”), (”character”), (”character_length”), (”check”);
#
DROP PROCEDURE IF EXISTS dsql_example1|
#
# Example 1: Degrees of flexibility of various SQL
# Introduce the syntax of SQL
# prepared statements and Dynamic SQL statements.
# For more information see:
#
CREATE PROCEDURE dsql_example1()
BEGIN
DECLARE var INTEGER DEFAULT 1;
# A stored procedure variable in any context that allows an expression.
# In ORDER BY clause, a variable is interpreted as a constant, not
# as the index of the column.
SELECT var FROM statements WHERE var ORDER by var LIMIT 1;
# A prepared statement placeholder can also be used in the LIMIT clause
PREPARE stmt FROM “SELECT ? FROM statements WHERE ? LIMIT ?, ?”;
SET @a=”a”, @b=”1″, @c=”0″, @d=”1″;
EXECUTE stmt USING @a, @b, @c, @d;
# Dynamic SQL provides full flexibility
SET @table_name=”statements”;
SET @stmt_text=CONCAT(”select * from “, @table_name, ” limit ?”);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt USING @d;
DEALLOCATE PREPARE stmt;
END|
CALL dsql_example1()|
#
# To check and control the total amount of prepared statements in the
# server, global system variables prepared_stmt_count,
# max_prepared_stmt_count can be used.
SELECT @@prepared_stmt_count, @@max_prepared_stmt_count|
#
DROP PROCEDURE IF EXISTS dsql_example2|
#
# Example 2. Using Dynamic SQL to check the list of supported statements.
# Dynamic SQL can be used with SQL PSM Continue Handlers.
# This procedure opens a cursor for a table with SQL statements, attempts
# to PREPARE each record of the table, and updates the table with results
# of PREPARE.
#
CREATE PROCEDURE dsql_example2()
BEGIN
DECLARE v_id INTEGER;
DECLARE v_text VARCHAR(45);
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT c_id, c_text FROM statements;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000′ SET done = 1;
DECLARE CONTINUE HANDLER FOR 1295 — ER_UNSUPPORTED_PS
SET @status=’not supported’;
OPEN c;
REPEAT
FETCH c INTO v_id, v_text;
IF NOT done THEN
SET @status=”supported”;
SET @stmt_text=v_text;
PREPARE stmt FROM @stmt_text;
UPDATE statements SET c_status=@status WHERE c_id=v_id;
END IF;
UNTIL done END REPEAT;
DEALLOCATE PREPARE stmt;
END|
CALL dsql_example2()|
SELECT c_text, c_status FROM statements|
#
# Example 3. What SQL standard keywords are reserved in MySQL?
# Generalize the code of the previos procedure.
# Demonstrate limitations of Dynamic SQL and suggest workarounds.
# This procedure iterates over a table with SQL statements, attemts to
# prepare each statement, and prints the result to the user.
#
CREATE PROCEDURE dsql_example3(t_name VARCHAR(35), f_name VARCHAR(35),
status_yes VARCHAR(20), status_no VARCHAR(20))
BEGIN
# We can’t open a cursor for a Dynamic SQL query.
# Let’s create a view to represent the query, and open a cursor for that
# view.
DROP VIEW IF EXISTS v1;
SET @stmt_text=CONCAT(”CREATE VIEW v1 AS SELECT c_text FROM “, t_name);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
BEGIN
DECLARE v_text VARCHAR(45);
DECLARE done INT DEFAULT 0;
DECLARE c cursor FOR SELECT c_text FROM v1;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000′ SET done= 1;
OPEN c;
REPEAT
FETCH c INTO v_text;
IF NOT done THEN
# Dynamic SQL can’t access stored procedure variables. Work this around
# by using a user variable.
SET @a=v_text;
SET @stmt_text=concat(”set @stmt_text=”, f_name, “(@a)”);
prepare stmt from @stmt_text;
EXECUTE stmt;
BEGIN
DECLARE CONTINUE HANDLER FOR 1295 — ER_UNSUPPORTED_PS
SET @status=status_no;
DECLARE CONTINUE HANDLER FOR 1064 — ER_SYNTAX_ERROR
SET @status=status_no;
# Reset the status to initial value.
SET @status=status_yes;
# @stmt_text is updated by the previous dynamic sql statement
PREPARE STMT FROM @stmt_text;
SELECT v_text, @status;
END;
end if;
UNTIL done END REPEAT;
END;
DROP VIEW v1;
DEALLOCATE PREPARE stmt;
END|
CREATE FUNCTION construct1(part VARCHAR(35)) returns VARCHAR(35)
RETURN CONCAT(”CREATE TABLE “, part)|
CALL dsql_example3(”keywords”, “construct1″, “not reserved”, “reserved”)|
CREATE FUNCTION construct2(part VARCHAR(45)) returns VARCHAR(45)
RETURN part|
CALL dsql_example3(”statements”, “construct2″, “supported”, “not supported”)|
#
# Example 4. Using Dynamic SQL to automate administrative tasks.
# The below stored procedure applies the submitted command to every table in
# a given database.
#
DROP PROCEDURE IF EXISTS dsql_example4|
CREATE PROCEDURE dsql_example4(db_name VARCHAR(64), template VARCHAR(65535))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tname VARCHAR(64);
DECLARE c CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_schema=db_name;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000′ SET done= 1;
OPEN c;
REPEAT
FETCH c INTO tname;
SET @stmt_text=REPLACE(template, ” ?”, concat(” “, tname));
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SELECT CONCAT(”‘”, @stmt_text, “‘ command failed”) as “error”;
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE prepare stmt;
END;
UNTIL done END REPEAT;
END|
CALL dsql_example4(”test”, “optimize table ?”)|
#
# Clean up.
#
DROP TABLE statements, keywords|
DROP PROCEDURE dsql_example1|
DROP PROCEDURE dsql_example2|
DROP PROCEDURE dsql_example3|
DROP PROCEDURE dsql_example4|
DROP FUNCTION construct1|
DROP FUNCTION construct2|
Loading ...
