数据库表添加新字段(支持重复执行)
Oracle
DECLARE COLEXIST NUMBER; BEGIN SELECT COUNT(1) INTO COLEXIST FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER('HISTORY') AND COLUMN_NAME=UPPER('NAME');--判断列是否存在 IF COLEXIST = 0 THEN --如果不存在,进行创建 EXECUTE IMMEDIATE 'ALTER TABLE HISTORY ADD NAME VARCHAR(64) DEFAULT ('' '') NOT NULL'; END IF ; END; /
或者
CREATE OR REPLACE PROCEDURE COL_V1 ( TBNAME IN VARCHAR, COLNAME IN VARCHAR, COLTYPE IN VARCHAR ) AS COLEXIST NUMBER(4); BEGIN SELECT COUNT(1) INTO COLEXIST FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER(TBNAME) AND COLUMN_NAME=UPPER(COLNAME);--判断列是否存在 IF COLEXIST = 0 THEN INSERT INTO C_TEST (ID) VALUES (1); EXECUTE IMMEDIATE 'ALTER TABLE '||TBNAME||' ADD '||COLNAME||' '||COLTYPE; END IF; COMMIT; END; / CALL COL_V1('C_TEST','TPPID2','NUMBER(4) DEFAULT 0 NOT NULL');
Mysql数据库表增加字段
DELIMITER ;; DROP PROCEDURE IF EXISTS COLUMNADD; CREATE PROCEDURE `COLUMNADD`(TBNAME VARCHAR(30),COLNAME VARCHAR(32),COLTYPE VARCHAR(64)) BEGIN DECLARE P_STR VARCHAR(300); IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME = TBNAME AND COLUMN_NAME =COLNAME) THEN -- 判断列是否存在 SET P_STR= CONCAT('ALTER TABLE ',TBNAME,' ADD ',COLNAME ,' ',COLTYPE); SET @SQL = P_STR; PREPARE STMT FROM @SQL; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF; END;; DELIMITER ; -- 调用该存储过程 CALL COLUMNADD('LF_WCD_P1','WCD_URLP1','VARCHAR(64) NOT NULL DEFAULT ''''');
Mysql数据库表删除字段
DELIMITER ;; DROP PROCEDURE IF EXISTS COLUMNDEL; CREATE PROCEDURE `COLUMNDEL`(TBNAME VARCHAR(30),COLNAME VARCHAR(32)) BEGIN DECLARE P_STR VARCHAR(300); IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME = TBNAME AND COLUMN_NAME =COLNAME) THEN SET P_STR= CONCAT('ALTER TABLE ',TBNAME,' DROP COLUMN ',COLNAME ,' ',COLTYPE); SET @SQL = P_STR; PREPARE STMT FROM @SQL; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF; END;; DELIMITER ;
-- 调用该存储过程
CALL COLUMNADD('LF_WCD_P1','WCD_URLP1');
sqlserver数据库新增字段
IF EXISTS ( SELECT * FROM SYS.OBJECTS WHERE TYPE='P' AND NAME='COLUMNADD' ) BEGIN DROP PROCEDURE [COLUMNADD] END GO CREATE PROCEDURE COLUMNADD ( @TBNAME VARCHAR(15), @COLNAME VARCHAR(32), @COLTYPE VARCHAR(64) ) AS BEGIN --DECLARE @CURRENTDATABASE VARCHAR(100) DECLARE @P_STR VARCHAR(300) --set @CURRENTDATABASE = select DB_NAME() if COL_LENGTH(@TBNAME, @COLNAME) is NULL SET @P_STR = 'ALTER TABLE ' + @TBNAME + ' ADD ' + @COLNAME + ' ' + @COLTYPE EXEC(@P_STR) END GO exec COLUMNADD 'LF_MTTASK','FINISHTIME','DATETIME NOT NULL DEFAULT GETDATE()' GO
作者:小念
本文版权归作者和博客园共有,欢迎转载,但必须给出原文链接,并保留此段声明,否则保留追究法律责任的权利。