
DROP PROCEDURE IF EXISTS p_AlterColumn;
GO

CREATE PROCEDURE p_AlterColumn
(
    TableName        VARCHAR(50),  
    ColumnName       VARCHAR(50),  
	-- ModifyFlag='1000' ColumnType = 'NULL' 表示删除
    ColumnType       VARCHAR(50),
	-- 'NULL' 或 'NOT NULL'
    ColumnIsNull     VARCHAR(10),
	-- 数据类型，精度，为空，缺省值分别占1位。改了缺省值和精度该值为：'0101'
    ModifyFlag       VARCHAR(10),
    DefaultValue VARCHAR(255) 
) SQL SECURITY INVOKER 
BEGIN 
	DECLARE v_defaultvalue	VARCHAR(500);
	DECLARE v_defaultsql	VARCHAR(500);
	DECLARE v_ColumnType	VARCHAR(500);

	IF ColumnType = 'IMAGE' THEN
		SET v_ColumnType = 'LONGBLOB';
	ELSEIF ColumnType = 'NCLOB' THEN
		SET v_ColumnType = 'LONGTEXT';
	ELSEIF ColumnType = 'XMLTYPE' THEN
		SET v_ColumnType = 'LONGTEXT';
	else
		SET v_ColumnType = ColumnType;
	END IF;

SET v_ColumnType = REPLACE(v_ColumnType, 'NVARCHAR', 'VARCHAR');
  set v_defaultsql = '';
	IF DefaultValue is not null AND LENGTH(DefaultValue) > 0 AND DefaultValue <> 'NULL' THEN
		IF DefaultValue = 'GETDATE()' THEN
			set v_defaultvalue = ' current_timestamp ';
		-- ELSIF DefaultValue = 'NEWID()' THEN
		-- 	v_defaultvalue := ' SYS_GUID() ';
		ELSE
			IF INSTR(DefaultValue, '{ts') = 1 THEN
			    SET v_defaultvalue = REPLACE(REPLACE(DefaultValue, '{ts', ''), '}', '');
			ELSE
		    	set v_defaultvalue = DefaultValue;
			END IF;
		END IF;
		set v_defaultsql = concat(' DEFAULT ', v_defaultvalue);
	END IF;
    
	-- 修改数据类型（varchar改为number，varchar(20)改为（40））
	IF SUBSTR(ModifyFlag,1,1) = '1' AND	
			-- 删除字段
			ColumnType = 'NULL' THEN		
		set @ifSql = concat('SELECT count(1) into @existcol FROM information_schema.columns WHERE TABLE_SCHEMA = SCHEMA() AND table_name = ''', TableName, ''' AND column_name = ''', ColumnName, ''' '); 
		prepare stmt from @ifSql;
		execute stmt;
		deallocate prepare stmt;
		IF @existcol = 1 THEN
			set @sqlcounts = concat(' ALTER TABLE ', TableName, ' drop column ', ColumnName);
			prepare stmt from @sqlcounts;
			execute stmt;
			deallocate prepare stmt;
		END IF;
	ELSE
		-- 修改字段类型、修改字段长度、修改为空、修改缺省值
		IF SUBSTR(ModifyFlag,3,1) = '1' AND ColumnIsNull = 'NOT NULL' AND (v_defaultvalue IS NOT NULL AND LENGTH(v_defaultvalue) > 0) THEN
			-- mysql不允许空修改为非空
			set @sqlcounts1 = concat('UPDATE ', TableName, ' SET ', ColumnName, ' = ', v_defaultvalue, ' WHERE ', ColumnName, ' IS NULL ');
			prepare stmt1 from @sqlcounts1;
			execute stmt1;
			deallocate prepare stmt1;
		END IF;
		set @sqlcounts = concat('ALTER TABLE ', TableName, ' modify ', ColumnName, ' ', v_ColumnType, ' ', ColumnIsNull, v_defaultsql, '');
		prepare stmt from @sqlcounts;
		execute stmt;
		deallocate prepare stmt;
	END IF;
END;
GO

DROP PROCEDURE IF EXISTS p_AlterPK;
GO
CREATE PROCEDURE p_AlterPK
(
	PkName      VARCHAR(50),
	TableName   VARCHAR(50),
	FieldName   VARCHAR(2000),
	-- 0非聚集   1聚集
	IsClustered int	
) SQL SECURITY INVOKER 
BEGIN 
  DECLARE oldPkName VARCHAR(30);

  -- 删除主键
  if EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = SCHEMA() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = TableName) THEN
    -- 删除原主键再增加
	set @sqlcounts = concat('ALTER TABLE ', TableName, ' DROP primary key');
	prepare stmt from @sqlcounts;
		execute stmt;
	deallocate prepare stmt;
  END IF;
  IF FieldName is not null AND FieldName <> 'NULL' AND FieldName <> '' THEN
	set @sqlcounts = concat('ALTER TABLE ', TableName, ' ADD primary key(', FieldName, ')');
	prepare stmt from @sqlcounts;
		execute stmt;
	deallocate prepare stmt;
  END IF;
END;
GO

DROP PROCEDURE IF EXISTS p_DropIdx;
GO
CREATE PROCEDURE p_DropIdx
(
	IdxName VARCHAR(50), 
	TableName VARCHAR(50)
) SQL SECURITY INVOKER 
BEGIN
	DECLARE
	 oldIdxName VARCHAR(30);
	
	IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = TableName AND INDEX_NAME = IdxName) THEN
		SET @sqlcounts = concat( 'DROP INDEX ', IdxName, ' ON ', TableName);
		PREPARE stmt 
		FROM
		 @sqlcounts;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
	END IF;
END;
GO

DROP PROCEDURE IF EXISTS p_ModifyObjectName;
GO
create procedure p_ModifyObjectName
(
	TableName	VARCHAR(50),
	ObjectName	VARCHAR(50),
	NewObjectName	VARCHAR(50),
	ObjectType		VARCHAR(10),
    FieldType	varchar(50) 
) SQL SECURITY INVOKER 
begin
	if ObjectType = 'OBJECT' and exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = SCHEMA() and table_name = ObjectName) then
 		set @sqlcounts = concat('ALTER TABLE ', ObjectName, ' RENAME TO ', NewObjectName);
		prepare stmt from @sqlcounts;
			execute stmt;
		deallocate prepare stmt;
    else 
		if ObjectType = 'COLUMN' and exists(select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = SCHEMA() and table_name = TableName and COLUMN_NAME = ObjectName) then
			set @sqlcounts = concat('ALTER TABLE ', TableName, ' CHANGE COLUMN ', ObjectName, ' ', NewObjectName, ' ', FieldType);
			prepare stmt from @sqlcounts;
			execute stmt;
			deallocate prepare stmt;
		end if;
    end if;
end;
GO

