数据库表添加新字段(支持重复执行)

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');

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 ''''');

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');

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