写得比较乱,不过里面有你需要的功能。 AAB001就就是我要查询的字段,你可以修改你想要的。 PROCEDURE test IS my_count INT; ret INT; nSQL NUMBER; nSQL1 NUMBER; sqlcmd VARCHAR2(512); column_name VARCHAR2(60); table_name VARCHAR2(60); pk_table_name varchar2(60); begin nSQL := DBMS_SQL.OPEN_CURSOR; sqlcmd := 'SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE (COLUMN_NAME= ''aab001'' OR COLUMN_NAME=''AAB001'') AND TABLE_NAME NOT IN ( SELECT VIEW_NAME FROM USER_VIEWS ) ORDER BY LENGTH( TABLE_NAME ) DESC'; BMS_SQL.PARSE( nSQL, sqlcmd, DBMS_SQL.v7 ); DBMS_SQL.DEFINE_COLUMN( nSQL, 1, TABLE_NAME , 60); ret := DBMS_SQL.EXECUTE( nSQL ) ; LOOP IF DBMS_SQL.FETCH_ROWS( nSQL ) = 0 THEN EXIT; END IF; DBMS_SQL.COLUMN_VALUE( nSQL, 1, table_name ) ; nSQL1 := DBMS_SQL.OPEN_CURSOR; sqlcmd := 'alter table '||tablename|| add fieldname fieldtype;'; DBMS_SQL.PARSE( nSQL1, sqlcmd, DBMS_SQL.v7 ) ; ret := DBMS_SQL.EXECUTE( nSQL1 ) ; DBMS_SQL.CLOSE_CURSOR( nSQL1 ); END LOOP; DBMS_SQL.CLOSE_CURSOR( nSQL ); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR( nSQL ); DBMS_SQL.CLOSE_CURSOR( nSQL1 ); end;
先查询一下,看有没有记录 SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME=你的新列 AND TABLE_NAME=你的表名 若没有,用:alter table 你的表名 add 你的新列 数据类型;
i_count int; strsql varchar2(100); ..... SELECT count(*) into i_count FROM USER_TAB_COLUMNS WHERE TABLE_NAME='tbname' and COLUMN_NAME='col1'; if i_count = 0 then strsql ='alter table tbname add col1 int'; execute immediate strsql; end if; .....
AAB001就就是我要查询的字段,你可以修改你想要的。
PROCEDURE test
IS
my_count INT;
ret INT;
nSQL NUMBER;
nSQL1 NUMBER;
sqlcmd VARCHAR2(512);
column_name VARCHAR2(60);
table_name VARCHAR2(60);
pk_table_name varchar2(60);
begin
nSQL := DBMS_SQL.OPEN_CURSOR;
sqlcmd := 'SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE (COLUMN_NAME= ''aab001'' OR COLUMN_NAME=''AAB001'') AND TABLE_NAME NOT IN ( SELECT VIEW_NAME FROM USER_VIEWS ) ORDER BY LENGTH( TABLE_NAME ) DESC';
BMS_SQL.PARSE( nSQL, sqlcmd, DBMS_SQL.v7 );
DBMS_SQL.DEFINE_COLUMN( nSQL, 1, TABLE_NAME , 60);
ret := DBMS_SQL.EXECUTE( nSQL ) ;
LOOP
IF DBMS_SQL.FETCH_ROWS( nSQL ) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE( nSQL, 1, table_name ) ;
nSQL1 := DBMS_SQL.OPEN_CURSOR;
sqlcmd := 'alter table '||tablename|| add fieldname fieldtype;';
DBMS_SQL.PARSE( nSQL1, sqlcmd, DBMS_SQL.v7 ) ;
ret := DBMS_SQL.EXECUTE( nSQL1 ) ;
DBMS_SQL.CLOSE_CURSOR( nSQL1 );
END LOOP;
DBMS_SQL.CLOSE_CURSOR( nSQL ); EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR( nSQL );
DBMS_SQL.CLOSE_CURSOR( nSQL1 );
end;
SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME=你的新列 AND TABLE_NAME=你的表名 若没有,用:alter table 你的表名 add 你的新列 数据类型;
strsql varchar2(100);
.....
SELECT count(*) into i_count FROM USER_TAB_COLUMNS WHERE TABLE_NAME='tbname' and COLUMN_NAME='col1';
if i_count = 0 then
strsql ='alter table tbname add col1 int';
execute immediate strsql;
end if;
.....