说明
接上篇,此篇描述Oracle的写法,同时适用于DM和Oscar
存储过程
DECLARE
VCOUNT1 INT := 0;
EXISTFLAG INT := 0;
audittable varchar(20);
sqlstring varchar(100);
BEGIN
audittable := ('GSPAUDIT' || to_char(sysdate,'yyMM'));
sqlstring := 'alter table ' || audittable || ' add seclevel VARCHAR2(256)';
SELECT COUNT(1) INTO VCOUNT1 FROM USER_ALL_TABLES WHERE TABLE_NAME = audittable;
IF (VCOUNT1 > 0)
THEN
SELECT COUNT(1) INTO EXISTFLAG FROM USER_TAB_COLS WHERE COLUMN_NAME='SECLEVEL' AND TABLE_NAME=audittable;
IF EXISTFLAG = 0 THEN
EXECUTE IMMEDIATE sqlstring;
END IF;
END IF;
END;
注意事项
1、注意使用USER_ALL_TABLES 、USER_TAB_COLS