create procedure - wwq0912/mysql GitHub Wiki

1：

DROP PROCEDURE IF EXISTS test_procedure;
DELIMITER $$
  CREATE PROCEDURE test_procedure(
      IN classId INT,
      IN studentAge INT,
      IN studentName varchar(45)
  )
  BEGIN

	CREATE TABLE IF NOT EXISTS `student` (
	  `ID` int(11) NOT NULL AUTO_INCREMENT,
	  `CLASS_ID` int(11) NOT NULL,
	  `STUDENT_AGE` int(11) NOT NULL,
	  `STUDENT_NAME` varchar(45) DEFAULT NULL,
	  PRIMARY KEY (`ID`),
	  KEY `FK_CLASS_ID` (`CLASS_ID`)
	) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
    insert into student set CLASS_ID=classId, STUDENT_AGE=studentAge, STUDENT_NAME = studentName;
  END
$$
DELIMITER ;

调用

call test_procedure(1, 18, 'name1');
call test_procedure(1, 18, 'name2');

2： 不支持动态表名

DROP PROCEDURE IF EXISTS test_create_table_procedure;
DELIMITER $$
  CREATE PROCEDURE test_create_table_procedure(
      IN tableName varchar(45)
  )
  BEGIN
	CREATE TABLE IF NOT EXISTS tableName (
	  `ID` int(11) NOT NULL AUTO_INCREMENT,
	  `CLASS_ID` int(11) NOT NULL,
	  `STUDENT_AGE` int(11) NOT NULL,
	  `STUDENT_NAME` varchar(45) DEFAULT NULL,
	  PRIMARY KEY (`ID`),
	  KEY `FK_CLASS_ID` (`CLASS_ID`)
	) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
  END
$$
DELIMITER ;

调用

call test_create_table_procedure('testProcedure');

3：怎么实现动态表名

DROP PROCEDURE IF EXISTS test_create_table_procedure;
DELIMITER $$
CREATE PROCEDURE test_create_table_procedure(IN TEMP_TABLE_NAME varchar(100))
  BEGIN
 SET @sql = concat('CREATE TABLE ', TEMP_TABLE_NAME, ' (`id` int(11) NOT NULL, PRIMARY KEY (`id`))  ENGINE=INNODB DEFAULT CHARSET=UTF8');
PREPARE stmt1 FROM @sql;
 EXECUTE stmt1; 
 deallocate prepare stmt1;  ###释放执行过程中的资源
  END
$$
DELIMITER ;

调用

call test_create_table_procedure('testProcedure');