MySQL存储过程与组件管理

此文会更新下去，逐步完善敬请期待。

目录

存储过程

存储过程基础与原理

存储过程实例与方法调用

存储过程组件管理

字段的新增和修改

索引的新增和修改

视图创建或修改

删除表和视图

---

存储过程

存储过程基础与原理

待续....

存储过程实例与方法调用

待续....

存储过程组件管理

字段的新增和修改

/* 
 * 安全创建表字段的存储过程：
 * 首先判断字段是否存在，如果已经存在则不创建，如果不存在，则创建。
 * 防止字段已经存在创建出错
 * 
 * 参数说明：
 * p_dbname : 数据库名称
 * p_tablename ： 表名称
 * p_filedname :  字段名称
 * p_filedtype :  字段类型如: DATE , VARCHAR(50)
 * p_comment :  说明和备注
 * 
 * 举例：比如想要在数据库xxx_db的tablename这张表的字段sys_create_date和类型为date，则如下：
 * call proc_add_column('xxx_db','tablename','sys_create_date','date','系统创建时间')
 */
DROP PROCEDURE if EXISTS proc_add_column;

CREATE PROCEDURE proc_add_column(
    IN p_dbname VARCHAR(50),
    IN p_tablename VARCHAR(100),
    IN p_filedname VARCHAR(100),
    IN p_filedtype VARCHAR(100),
    IN p_comment VARCHAR(200)
)
BEGIN
   SET @str=CONCAT('ALTER TABLE ',p_tablename,' ADD ',p_filedname,' ',p_filedtype,' COMMENT ',"'",p_comment,"'");

   SET @cnt = 0;
   SELECT count(*) INTO @cnt	FROM	information_schema.COLUMNS
     WHERE		table_schema = p_dbname	AND table_name = p_tablename	AND column_name=p_filedname;

   IF (@cnt = 0) THEN
		 PREPARE stmt	FROM	@str ;
     EXECUTE stmt ;
	 END	IF;
   
END


/* 
 * 安全修改表字段的存储过程：
 * 首先判断字段是否存在，如果已经存在则修改，如果不存在，则不修改。
 * 防止字段不存在存在修改出错
 * 
 * 参数说明：
 * p_dbname : 数据库名称
 * p_tablename ： 表名称
 * p_filedname :  字段名称
 * p_filedtype :  字段类型如: DATE , VARCHAR(50)
 * p_comment :  说明和备注
 * 
 * 举例：比如想要在数据库xxx_db的tablename这张表的字段sys_create_date和类型为date，则如下：
 * call proc_modify_column('xxx_db','tablename','sys_create_date','date','系统创建时间')
 */
DROP PROCEDURE if EXISTS proc_modify_column;

CREATE PROCEDURE proc_modify_column(
    IN p_dbname VARCHAR(50),
    IN p_tablename VARCHAR(100),
    IN p_filedname VARCHAR(100),
    IN p_filedtype VARCHAR(100),
    IN p_comment VARCHAR(200)
)
BEGIN
   SET @str=CONCAT('ALTER TABLE ',p_tablename,' MODIFY ',p_filedname,' ',p_filedtype,' COMMENT ',"'",p_comment,"'");

   SET @cnt = 0;
   SELECT count(*) INTO @cnt	FROM	information_schema.COLUMNS
     WHERE		table_schema = p_dbname	AND table_name = p_tablename	AND column_name=p_filedname;

   IF (@cnt > 0) THEN
		 PREPARE stmt	FROM	@str ;
     EXECUTE stmt ;
	 END	IF;
   
END

索引的新增和修改

/* 
 * 安全创建索引的存储过程：
 * 首先判断索引是否存在，如果已经存在则不创建，如果不存在，则创建。
 * 防止索引已经存在，创建出错和删除索引重复创建原来的索引带来的开销浪费
 * 
 * 参数说明：
 * p_dbname : 数据库名称
 * p_tablename ： 表名称
 * p_idxname : 索引名称
 * p_index : 索引名称和结构，比如在 name(name) 
 * 
 * 举例：比如想要在数据库xxx_db的tablename这张表的字段column1和column2创建联合索引idx_c1c2，则如下：
 * call proc_add_index('xxx_db','tablename','idx_c1c2','idx_c1c2(column1,column2)')
 */
DROP PROCEDURE IF EXISTS proc_add_index;


CREATE PROCEDURE proc_add_index (
	IN p_dbname VARCHAR (200),
	IN p_tablename VARCHAR (200),
	IN p_idxname VARCHAR (200),
	IN p_index VARCHAR (200)
)
BEGIN
	SET @str = concat(' ALTER TABLE ',p_tablename,' ADD INDEX ',p_index	) ; 
  SET @cnt=0;
  SELECT count(*) INTO @cnt	FROM	information_schema.statistics	
    WHERE		TABLE_SCHEMA = p_dbname	AND table_name = p_tablename  AND index_name = p_idxname ;

	IF (@cnt = 0) THEN
		PREPARE stmt	FROM	@str ;
    EXECUTE stmt ;
	END	IF;

END ;


/* 
 * 安全修改索引的存储过程：
 * 首先判断索引是否存在，如果已经存在则修改，如果不存在，则不修改。
 * 防止索引已经存在，修改出错或重复创建原来的索引带来的开销浪费
 * 
 * 参数说明：
 * p_dbname : 数据库名称
 * p_tablename ： 表名称
 * p_idxname : 索引名称
 * p_index : 索引名称和结构，比如在 name(name) 
 * 
 * 举例：比如想要在数据库xxx_db的tablename这张表的字段column1和column2创建联合索引idx_c1c2，则如下：
 * call proc_modify_index('xxx_db','tablename','idx_c1c2','idx_c1c2(column1,column2)')
 */
DROP PROCEDURE IF EXISTS proc_modify_index;


CREATE PROCEDURE proc_modify_index (
	IN p_dbname VARCHAR (200),
	IN p_tablename VARCHAR (200),
	IN p_idxname VARCHAR (200),
	IN p_index VARCHAR (200)
)
BEGIN
	SET @str = concat(' ALTER TABLE ',p_tablename,' MODIFY INDEX ',p_index	) ; 
  SET @cnt=0;
  SELECT count(*) INTO @cnt	FROM	information_schema.statistics	
    WHERE		TABLE_SCHEMA = p_dbname	AND table_name = p_tablename  AND index_name = p_idxname ;

	IF (@cnt = 0) THEN
		PREPARE stmt	FROM	@str ;
    EXECUTE stmt ;
	END	IF;

END ;

视图创建或修改

/* 
 * 安全创建或修改视图的存储过程：
 * 
 * 参数说明：
 * p_dbname : 数据库名称
 * p_viewname ： 表视图名称
 * p_sqlquery : 视图结果查询语句
 * 
 * 举例：比如想要在tablename这张表的字段a、b、c......，则如下：
 * call proc_add_or_modify_view('tablename','SELECT a,b,c FROM tablename')
 */
DROP PROCEDURE IF EXISTS proc_create_replace_view;


CREATE PROCEDURE proc_create_replace_view (
	IN p_viewname VARCHAR (200),
	IN p_sqlquery VARCHAR (1000)
)
BEGIN
	SET @str = concat('CREATE OR REPLACE  VIEW ',p_viewname,' AS ',p_sqlquery) ; 
  PREPARE stmt	FROM	@str;
  EXECUTE stmt ;

END ;

删除表和视图

/* 
 * 安全删除表的存储过程：
 * 
 * 参数说明：
 * p_dbname : 数据库名称
 * p_tablename ： 表名称
 * 
 * 示例：call proc_delete_table('xxx_db','tablename')
 */
DROP PROCEDURE if EXISTS proc_delete_table;

CREATE PROCEDURE proc_delete_table(
    IN p_dbname VARCHAR(50),
    IN p_tablename VARCHAR(100)
)
BEGIN
   SET @str=CONCAT("DROP TABLE IF EXISTS `",p_tablename,"`");

   SET @cnt = 0;
   SELECT count(*) INTO @cnt	FROM	information_schema.`TABLES`
         WHERE		table_schema = p_dbname	AND table_name = p_tablename;
   IF (@cnt > 0) THEN
		 PREPARE stmt	FROM	@str ;
     EXECUTE stmt ;
	 END	IF;
   
END


/* 
 * 安全删除表视图的存储过程：
 * 
 * 参数说明：
 * p_dbname : 数据库名称
 * p_viewname ： 表视图名称
 * 
 * 示例：call proc_delete_view('xxx_db','tablename')
 */
DROP PROCEDURE if EXISTS proc_delete_view;

CREATE PROCEDURE proc_delete_view(
    IN p_dbname VARCHAR(50),
    IN p_viewname VARCHAR(100)
)
BEGIN
   SET @str=CONCAT("DROP VIEW IF EXISTS `",p_viewname,"`");

   SET @cnt = 0;
   SELECT count(*) INTO @cnt	FROM	information_schema.VIEWS
         WHERE		table_schema = p_dbname	AND table_name = p_viewname;
   IF (@cnt > 0) THEN
		 PREPARE stmt	FROM	@str ;
     EXECUTE stmt ;
	 END	IF;
   
END