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; /
Mysql
DELIMITER ;; DROP PROCEDURE IF EXISTS COLUMNADD; CREATE DEFINER=`root`@`%` 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 ‘‘‘‘‘);