问题:
为一系列已经存在的表,添加一个相同的字段。(比如:添加SURVEYRESULT字段)
注:表名和字段名一定要大写,否则结果不正确
1、判断表是否在数据库中存在
'SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER (''' || V_TBL_NAME || ''')'
2、判断列是否在数据库中存在
‘SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('''||V_TBL_NAME || ''') AND COLUMN_NAME = ''SURVEYRESULT'''
- CREATE OR REPLACE PROCEDURE INSERTTABLESBYENTERPRISEID (
-
ENTERPRISEID IN STRING
-
)
-
IS
-
TYPE CURSOR_TEMP IS REF CURSOR;
-
-
V_CURSOR CURSOR_TEMP;
-
V_TBL_NAME VARCHAR2 (100) := '';
-
V_SQL VARCHAR2 (500) := '';
-
V_EXESQL VARCHAR2 (500) := '';
-
V_EXITOBJ INTEGER := 0;
-
BEGIN
-
V_SQL :=
-
'SELECT DISTINCT(RESULTTABLENAME) FROM SD_T_A01_'
-
|| ENTERPRISEID
-
|| ' WHERE ISDEL=0';
-
-
OPEN V_CURSOR FOR V_SQL;
-
-
LOOP
-
FETCH V_CURSOR INTO V_TBL_NAME;
-
-
EXIT WHEN V_CURSOR%NOTFOUND;
-
-
IF (V_TBL_NAME IS NULL)
-
THEN
-
BEGIN
-
V_TBL_NAME := 'SD_T_A07_' || ENTERPRISEID;
-
END;
-
END IF;
-
-
V_EXESQL := 'SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER (''' || V_TBL_NAME || ''')';
-
EXECUTE IMMEDIATE V_EXESQL INTO V_EXITOBJ;
-
-
IF V_EXITOBJ > 0
-
THEN
-
NULL;
-
V_EXITOBJ := 0;
-
V_EXESQL := 'SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('''|| V_TBL_NAME || ''') AND COLUMN_NAME = ''SURVEYRESULT''';
-
EXECUTE IMMEDIATE V_EXESQL INTO V_EXITOBJ;
-
IF V_EXITOBJ > 0
-
THEN
-
V_SQL := 'ALTER TABLE '|| V_TBL_NAME|| ' ADD SurveyResult VARCHAR2(500)';
-
EXECUTE IMMEDIATE V_SQL;
-
END IF;
-
END IF;
-
-
END LOOP;
-
-
COMMIT;
-
END INSERTTABLESBYENTERPRISEID;
阅读(10030) | 评论(0) | 转发(1) |