MySql
暂时只能新增,如果要删除和修改的功能再修改或者重新仿照写一个存储过程
DROP PROCEDURE IF EXISTS Pro_ColumnWork;
DELIMITER $$
CREATE PROCEDURE Pro_ColumnWork(TableName VARCHAR(50),ColumnName VARCHAR(50),SqlStr VARCHAR(4000),CType INT)
BEGIN
DECLARE Rows1 INT;
SET Rows1=0;
SELECT COUNT(*) INTO Rows1 FROM INFORMATION_SCHEMA.Columns
WHERE table_schema= DATABASE() AND table_name=TableName AND column_name=ColumnName;
IF (CType=1 AND Rows1<=0) THEN
SET SqlStr := CONCAT( 'ALTER TABLE ',TableName,' ADD COLUMN ',ColumnName,' ',SqlStr);
ELSE SET SqlStr :='';
END IF;
IF (SqlStr<>'') THEN
SET @SQL1 = SqlStr;
PREPARE stmt1 FROM @SQL1;
EXECUTE stmt1;
END IF;
END $$
DELIMITER ;
CALL Pro_ColumnWork ('表名','字段名','字段类型', 1);
CALL Pro_ColumnWork ('T_USER','NAME','varchar(80)', 1);
alter table 表名 MODIFY 字段名 字段类型 comment '注释';
alter table T_USER MODIFY NAME varchar(80) comment '姓名';
Oracle
CREATE OR REPLACE PROCEDURE Pro_ColumnWork
(
TBNAME IN VARCHAR,
COLNAME IN VARCHAR,
COLTYPE IN VARCHAR
)
AS
COLEXIST NUMBER(4);
BEGIN
SELECT COUNT(1) INTO COLEXIST FROM cols
WHERE TABLE_NAME=UPPER(TBNAME) AND COLUMN_NAME=UPPER(COLNAME);
IF COLEXIST = 0 THEN
EXECUTE IMMEDIATE
'ALTER TABLE '||TBNAME||' ADD '||COLNAME||' '||COLTYPE;
END IF;
COMMIT;
END;
/
CALL Pro_ColumnWork('表名','字段名','字段类型');
CALL Pro_ColumnWork('T_USER','NAME','NVARCHAR2(80)');
COMMENT ON COLUMN 表名.字段名 IS '注释';
COMMENT ON COLUMN T_USER.NAME IS '姓名';
DROP PROCEDURE Pro_ColumnWork;