MySQL Dynamic DDL in Prozedure or Function
Yesterday I ran into a challenge on mysql.
A procedure to create an drop a database on mysql has been needed. My first thought was “easy”, it wasn’t.
There is no “execute immediate” or dynamic variable replacement in mysql. The workaround to execute dynamic DDL in a mysql procedure was found in a bug description. (http://bugs.mysql.com/bug.php?id=9150)
It can be solved with a prepared statement.
set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;
My template to create a DB I just poste here. Maybe it helps:
delimiter //
DROP PROCEDURE IF EXISTS create_schema;
CREATE PROCEDURE create_schema (p_schema_name VARCHAR(50))
SQL SECURITY DEFINER
BEGIN
DECLARE v_schema_count INTEGER;
startb:BEGIN
-- check exists
SELECT count(1) INTO v_schema_count FROM information_schema.SCHEMATA WHERE upper(SCHEMA_NAME)=upper(p_schema_name);
IF v_schema_count > 0 THEN
SELECT 'Error: DB Exists' FROM dual;
leave startb;
END IF;
-- check name
IF ( INSTR(p_schema_name ,'AD_') = 0) THEN
SELECT 'Error: Invalid schema name' FROM dual;
leave startb;
END IF;
-- create db
set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;
-- grant permissiosn
set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;
END startb;
END;
//