MySQL存储过程与组件管理

一时失言乱红尘 2021-12-11 14:31 265阅读 0赞

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

**目录**

存储过程

存储过程基础与原理

存储过程实例与方法调用

存储过程组件管理

字段的新增和修改

索引的新增和修改

视图创建或修改

删除表和视图

--------------------

# 存储过程 #

## 存储过程基础与原理 ##

待续....

## 存储过程实例与方法调用 ##

待续....

## 存储过程组件管理 ##

### 字段的新增和修改 ###

/* 
     * 安全创建表字段的存储过程：
     * 首先判断字段是否存在，如果已经存在则不创建，如果不存在，则创建。
     * 防止字段已经存在创建出错
     * 
     * 参数说明：
     * 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