|
||
| Pearson Technology Consulting | ||
|
P_PTC_SQLGEN_MYSQL5
Back to SQL Generator DELIMITER $$
DROP PROCEDURE IF EXISTS mysql.P_PTC_SQLGEN_MYSQL5$$
CREATE PROCEDURE P_PTC_SQLGEN_MYSQL5(
_table_name varchar(64))
BEGIN
/*-----------------------------------------------------------------------*/
/* (c) Pearson Technology Consulting Incorporated - All rights reserved. */
/*-----------------------------------------------------------------------*/
/* */
/* A utility for generating basic SQL statements and stored procedures. */
/* */
/* Pass a table name and receive multiple result sets containing SQL for */
/* inserting, selecting, updating and deleting rows from that table. */
/* */
/* Feel free to modify to fit your needs but please give credit in this */
/* comment block to Pearson Technology Consulting Incorporated. */
/* */
/* Use at your own risk (but you know that this warning applies to all */
/* resources you download from the internet or receive in email). */
/* */
/* See http://www.pearsontechnology.com to send feedback or comments. */
/* */
/*-----------------------------------------------------------------------*/
DECLARE _idcol varchar(64);
DECLARE _type int;
CREATE TEMPORARY TABLE TblCol (
colname varchar(64) not null,
colseq int not null,
idcol int not null,
fulltype varchar(64) not null);
INSERT TblCol
SELECT
A.column_name,
A.ordinal_position,
CASE WHEN A.extra = 'auto_increment' THEN 1 ELSE 0 END,
CASE WHEN Instr(A.column_type, 'enum(') > 0 THEN 'varchar(50)' WHEN Instr(A.column_type, 'set(') > 0 THEN 'text' WHEN Instr(A.column_type, 'int(') = 0 THEN A.column_type ELSE A.data_type END
FROM
information_schema.columns AS A
WHERE
A.table_schema = Database() AND
A.table_name = _table_name;
CREATE TEMPORARY TABLE TblKey (
colname varchar(64) not null,
keyseq int not null);
INSERT TblKey
SELECT
A.column_name,
A.ordinal_position
FROM
information_schema.columns AS A
WHERE
A.table_schema = Database() AND
A.table_name = _table_name AND
A.column_key = 'PRI';
CREATE TEMPORARY TABLE SqlGen (
id int unsigned not null auto_increment,
sqltype int not null,
sqlline varchar(200) not null,
primary key(id));
CREATE TEMPORARY TABLE SqlGen2 (
id int unsigned not null auto_increment,
sqltype int not null,
sqlline varchar(200) not null,
primary key(id));
SET _type = 1;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, Concat('INSERT ', _table_name, ' ('));
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(' ', colname, ',') FROM TblCol WHERE idcol = 0 AND fulltype <> 'timestamp' ORDER BY colseq;
UPDATE SqlGen SET sqlline = Concat(Substring(sqlline, 1, Char_Length(sqlline) - 1), ')') WHERE id = Last_Insert_Id() + Row_Count() - 1;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'VALUES (');
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(' _', colname, ',') FROM TblCol WHERE idcol = 0 AND fulltype <> 'timestamp' ORDER BY colseq;
UPDATE SqlGen SET sqlline = Concat(Substring(sqlline, 1, Char_Length(sqlline) - 1), ');') WHERE id = Last_Insert_Id() + Row_Count() - 1;
SET _type = 2;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'SELECT');
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(' A.', colname, ',') FROM TblCol ORDER BY colseq;
UPDATE SqlGen SET sqlline = Substring(sqlline, 1, Char_Length(sqlline) - 1) WHERE id = Last_Insert_Id() + Row_Count() - 1;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'FROM');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, Concat(' ', _table_name, ' AS A'));
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'WHERE');
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(' A.', colname, ' = _', colname, ' AND') FROM TblKey ORDER BY keyseq;
UPDATE SqlGen SET sqlline = Concat(Substring(sqlline, 1, Char_Length(sqlline) - 4), ';') WHERE id = Last_Insert_Id() + Row_Count() - 1;
SET _type = 3;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, Concat('UPDATE ', _table_name, ' SET'));
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(' ', colname, ' = _', colname, ',') FROM TblCol WHERE colname NOT IN (SELECT colname FROM TblKey) AND fulltype <> 'timestamp' ORDER BY colseq;
UPDATE SqlGen SET sqlline = Substring(sqlline, 1, Char_Length(sqlline) - 1) WHERE id = Last_Insert_Id() + Row_Count() - 1;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'WHERE');
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(' ', colname, ' = _', colname, ' AND') FROM TblKey ORDER BY keyseq;
UPDATE SqlGen SET sqlline = Concat(Substring(sqlline, 1, Char_Length(sqlline) - 4), ';') WHERE id = Last_Insert_Id() + Row_Count() - 1;
SET _type = 4;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'DELETE FROM');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, Concat(' ', _table_name));
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'WHERE');
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(' ', colname, ' = _', colname, ' AND') FROM TblKey ORDER BY keyseq;
UPDATE SqlGen SET sqlline = Concat(Substring(sqlline, 1, Char_Length(sqlline) - 4), ';') WHERE id = Last_Insert_Id() + Row_Count() - 1;
INSERT SqlGen2 (sqltype, sqlline) SELECT sqltype, sqlline FROM SqlGen ORDER BY id;
SET _type = 10;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'DELIMITER $$');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
SET _type = 11;
INSERT SqlGen (sqltype, sqlline) VALUES(_type, Concat('DROP PROCEDURE IF EXISTS P_', _table_name, '_I1$$'));
INSERT SqlGen (sqltype, sqlline) VALUES (_type, Concat('CREATE PROCEDURE P_', _table_name, '_I1 ('));
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(Cast(CASE WHEN idcol = 0 THEN ' ' ELSE ' OUT' END AS Char), ' _', colname, ' ', fulltype, ',') FROM TblCol WHERE fulltype <> 'timestamp' ORDER BY idcol, colseq;
UPDATE SqlGen SET sqlline = Concat(Substring(sqlline, 1, Char_Length(sqlline) - 1), ')') WHERE id = Last_Insert_Id() + Row_Count() - 1;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'BEGIN');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
INSERT SqlGen (sqltype, sqlline) SELECT _type, sqlline FROM SqlGen2 WHERE sqltype = 1 ORDER BY id;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
SELECT colname INTO _idcol FROM TblCol WHERE idcol > 0;
IF _idcol IS NOT NULL THEN
INSERT SqlGen (sqltype, sqlline) VALUES (_type, Concat('SET _', _idcol, ' = Last_Insert_Id();'));
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
END IF;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'END$$');
SET _type = 12;
INSERT SqlGen (sqltype, sqlline) VALUES(_type, Concat('DROP PROCEDURE IF EXISTS P_', _table_name, '_S1$$'));
INSERT SqlGen (sqltype, sqlline) VALUES (_type, Concat('CREATE PROCEDURE P_', _table_name, '_S1 ('));
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(' _', A.colname, ' ', A.fulltype, ',') FROM TblCol AS A JOIN TblKey AS B ON A.colname = B.colname ORDER BY keyseq;
UPDATE SqlGen SET sqlline = Concat(Substring(sqlline, 1, Char_Length(sqlline) - 1), ')') WHERE id = Last_Insert_Id() + Row_Count() - 1;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'BEGIN');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
INSERT SqlGen (sqltype, sqlline) SELECT _type, sqlline FROM SqlGen2 WHERE sqltype = 2 ORDER BY id;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'END$$');
SET _type = 13;
INSERT SqlGen (sqltype, sqlline) VALUES(_type, Concat('DROP PROCEDURE IF EXISTS P_', _table_name, '_U1$$'));
INSERT SqlGen (sqltype, sqlline) VALUES (_type, Concat('CREATE PROCEDURE P_', _table_name, '_U1 ('));
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(' _', colname, ' ', fulltype, ',') FROM TblCol WHERE fulltype <> 'timestamp' ORDER BY colseq;
UPDATE SqlGen SET sqlline = Concat(Substring(sqlline, 1, Char_Length(sqlline) - 1), ')') WHERE id = Last_Insert_Id() + Row_Count() - 1;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'BEGIN');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
INSERT SqlGen (sqltype, sqlline) SELECT _type, sqlline FROM SqlGen2 WHERE sqltype = 3 ORDER BY id;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'END$$');
SET _type = 14;
INSERT SqlGen (sqltype, sqlline) VALUES(_type, Concat('DROP PROCEDURE IF EXISTS P_', _table_name, '_D1$$'));
INSERT SqlGen (sqltype, sqlline) VALUES (_type, Concat('CREATE PROCEDURE P_', _table_name, '_D1 ('));
INSERT SqlGen (sqltype, sqlline) SELECT _type, Concat(' _', A.colname, ' ', A.fulltype, ',') FROM TblCol AS A JOIN TblKey AS B ON A.colname = B.colname ORDER BY keyseq;
UPDATE SqlGen SET sqlline = Concat(Substring(sqlline, 1, Char_Length(sqlline) - 1), ')') WHERE id = Last_Insert_Id() + Row_Count() - 1;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'BEGIN');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
INSERT SqlGen (sqltype, sqlline) SELECT _type, sqlline FROM SqlGen2 WHERE sqltype = 4 ORDER BY id;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'END$$');
SET _type = 15;
INSERT SqlGen (sqltype, sqlline) VALUES (_type, 'DELIMITER ;');
INSERT SqlGen (sqltype, sqlline) VALUES (_type, '');
SELECT sqlline AS 'Insert SQL' FROM SqlGen WHERE sqltype = 1 ORDER BY id;
SELECT sqlline AS 'Select SQL' FROM SqlGen WHERE sqltype = 2 ORDER BY id;
SELECT sqlline AS 'Update SQL' FROM SqlGen WHERE sqltype = 3 ORDER BY id;
SELECT sqlline AS 'Delete SQL' FROM SqlGen WHERE sqltype = 4 ORDER BY id;
SELECT sqlline AS 'Stored Procedure SQL' FROM SqlGen WHERE sqltype IN (10,11,12,13,14,15) ORDER BY id;
DROP TABLE TblCol;
DROP TABLE TblKey;
DROP TABLE SqlGen;
DROP TABLE SqlGen2;
END$$
DELIMITER ;
Back to SQL Generator |