此文会更新下去,逐步完善敬请期待。
目录
存储过程
存储过程基础与原理
待续....
存储过程实例与方法调用
待续....
存储过程组件管理
字段的新增和修改
/*
* 安全创建表字段的存储过程:
* 首先判断字段是否存在,如果已经存在则不创建,如果不存在,则创建。
* 防止字段已经存在创建出错
*
* 参数说明:
* 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