判断一个表中的字段是否存在,若不存在则动态的创建它:
--声明部分
V_NUM INTEGER;
V_SQL VARCHAR2(1000);
--执行部分
V_NUM := 0;
SELECT COUNT(1)
INTO V_NUM
FROM cols
WHERE table_name = UPPER('tab_name')
AND column_name = UPPER('col_name');
IF V_NUM = 0 THEN
V_SQL:='ALTER TABLE tab_name ADD col_name VARCHAR2(100)';
EXECUTE IMMEDIATE V_SQL;
COMMIT;
ELSIF V_NUM = 1 THEN
V_SQL:='ALTER TABLE tab_name DROP COLUMN col_name';
EXECUTE IMMEDIATE V_SQL;
V_SQL:='ALTER TABLE tab_name ADD col_name VARCHAR2(100)';
EXECUTE IMMEDIATE V_SQL;
COMMIT;
END IF;