WL#2793: Dynamic SQL: support EXECUTE IMMEDIATE
Affects: Server-6.x — Status: In-Design — Priority: Medium
Introduce a convenient syntax for Dynamic SQL in stored procedures: EXECUTE IMMEDIATE <expr> <expr> can contain any expression that doesn't refer to stored functions and doesn't use tables (including temporary tables, schema tables, or views). The expression is evaluated, cast to a string, and then parsed as an SQL statement and executed. Example: EXECUTE IMMEDIATE concat("select * from ", @table_name, " where a=", @val); The syntax is available as a standalone SQL (for testing purposes) and inside a stored procedure. Any SQL statement is supported in argument of EXECUTE IMMEDIATE. In particular, DROP PROCEDURE, ALTER PROCEDURE, FLUSH TABLES, KILL are all allowed. Stored procedure variables can be part of expression. General log, binary log and slow log are supported. Instead of logging EXECUTE IMMEDIATE text, the statement that has actually been executed is logged. Execution of the statement increments all respective statistics counters, as well as a new one, Com_stmt_execute_direct. Query cache is supported. Query profiling (community feature) is supported as well. EXECUTE IMMEDIATE "EXECUTE IMMEDIATE ...", EXECUTE IMMEDIATE "PREPARE ..." and EXECUTE IMMEDIATE "EXECUTE ..." are prohibited to avoid meaningless recursion. EXECUTE IMMEDIATE is a statement that can commit transaction or return multiple result sets, and is therefore not supported inside stored functions and triggers. It is available in Events. Sample application ------------------- For taking backups: CREATE EVENT backup_weekly ON SCHEDULE ... DO BEGIN EXECUTE IMMEDIATE "BACKUP DATABASE .." END| To kill all queries of a certain user: CREATE PROCEDURE kill_all_for_user(user_name varchar(255)) BEGIN DECLARE CURSOR FOR SELECT ... FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ...; ... REPEAT EXECUTE IMMEDIATE concat("KILL ", user_connection_id); .. END REPEAT END|
New parser keyword: IMMEDIATE (reserved word according to SQL:92) is added. A new SQL word is supported: EXECUTE IMMEDIATE <expr>. <expr> can contain any expression that doesn't refer to stored functions and doesn't use tables (including temporary tables, schema tables, or views). Standard compliance ------------------- EXECUTE IMMEDIATE is part of SQL standard (part 4, SQL/PSM, Embedded SQL)
Add a new prepared statement method: bool Prepared_statement::execute_immediate(const char *query, uint length) The method is responsible for parsing and execution of the argument without affecting the current thread state (so that it can be used in nesting environments). Add /** SQLCOM_EXECUTE_IMMDEDIATE implementation. @param thd thread handle @return none: in case of success, OK (or result set) packet is sent to the client, otherwise an error is set in THD */ void mysql_sql_stmt_execute_immediate(THD *thd) - a function that handles a new SQLCOM, SQLCOM_EXECUTE_IMMEDIATE. A narrative of the contents of this function: - evaluate the expression argument of EXECUTE_IMMEDIATE - create a prepared statement - call Prepared_statement::execute_immediate() with expression text - cleanup.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.