MS SQL:
if not exists(SELECT * FROM syscolumns WHERE (id IN
(SELECT id FROM sysobjects WHERE (name = 'Workers'))) AND (name = 'ID') )
begin
Alter Table Workers ADD [ID] bigint NULL
exec sp_addextendedproperty N'MS_Description', N'ID', N'user', N'dbo', N'table', N'Workers', N'column', N'ID'
end请问ORACLE,如何写呢?
if not exists(SELECT * FROM syscolumns WHERE (id IN
(SELECT id FROM sysobjects WHERE (name = 'Workers'))) AND (name = 'ID') )
begin
Alter Table Workers ADD [ID] bigint NULL
exec sp_addextendedproperty N'MS_Description', N'ID', N'user', N'dbo', N'table', N'Workers', N'column', N'ID'
end请问ORACLE,如何写呢?
v_count number := 0;
v_sql varchar(2000) := null;
begin SELECT count(1)
INTO v_count
FROM syscolumns
WHERE id IN (SELECT id FROM sysobjects WHERE name = 'Workers')
AND name = 'ID'; if v_count > 0 then
v_sql := 'Alter Table Workers ADD ';
execute immediate v_sql;
v_sql := 'comment on column workers....'
execute immediate v_sql;
end if;end;
/
不太懂ms sql的那个函数,解释下吧,期待楼下
同意,修改补充一下,在oracle里有关表的字段的信息在user_tab_columns表里。SELECT count(1)
INTO v_count
FROM syscolumns
WHERE id IN (SELECT id FROM sysobjects WHERE name = 'Workers')
AND name = 'ID';改为select count(1) into v_count from user_tab_columns where upper(table_name) = upper('Workers') and
upper(column_name) = upper('ID');
sysobjects是MS SQL的系统表
但不是Oracle的系统表
MSSQL:
如果系统对象表的ID,不存在在系统列表中,
即增加workers 表ID 为bigint类型
sp_addextendedproperty 系统的过程,为增加系统字段的描述
但有没有更新简便的语句处理呢?其实我想要的结果是:增加字段,不过先判断字段是否已经存在,如果存在就不添加,如果不存在就添加
用动态的就可行了。非常感谢你的帮助,如果更简洁的方法,麻烦你转告我,谢谢了