oracle sql 脚本:安装时,如果表没有某列则进行添加
if (select count(column_name) from cols where table_name=upper("tablename")
and column_name=upper("columnname") == 0)
then
alter table tablenam add columnname;
end if;
卸载时,如果表有莫列则进行删除
if (select count(column_name) from cols where table_name=upper("tablename")
and column_name=upper("columnname") > 0)
then
alter table tablenam drop column columnname;
end if;两个都报错。
if (select count(column_name) from cols where table_name=upper("tablename")
and column_name=upper("columnname") == 0)
then
alter table tablenam add columnname;
end if;
卸载时,如果表有莫列则进行删除
if (select count(column_name) from cols where table_name=upper("tablename")
and column_name=upper("columnname") > 0)
then
alter table tablenam drop column columnname;
end if;两个都报错。
num_col int default 0;
begin
select count(*)
into num_col
from user_tab_cols
where table_name = upper('emp')
and column_name = upper('add_col');
if (num_col = 0) then
execute immediate 'alter table emp_tmp add(add_col varchar2(20))';
end if;
end;
select * from emp_tmp;
num_col int default 0;
begin
select count(*)
into num_col
from user_tab_cols
where table_name = upper('emp')
and column_name = upper('add_col');
if (num_col = 0) then
execute immediate 'alter table emp_tmp add(add_col varchar2(20))';
end if;
end;请问这个可以直接sql> 命令行上面执行吗? 还是需要放在脚本里,然后执行脚本呢?
好像遇到; ORACLE就直接执行了吧?
把这段直接粘贴到sql>上,分行不要紧,
然后在最后一行输入 / 回车
2 alter table test add value varchar2(10);
3 end;
4 /
alter table test add value varchar2(10);
*
ERROR at line 2:
ORA-06550: line 2, column 5:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
SQL> declare v_sql varchar2(100);
2 begin
3 v_sql := 'alter table ' || 'test' || ' add ' || 'value varchar2(10)';
4 execute immediate v_sql;
5 end;
6 /PL/SQL procedure successfully completed.
sqlplus里是没有这样的语法的。不过你可以写到sqlplus里的plsql块里, 用2楼的就可以。不过结尾一行加上/SQL>declare
2 num_col int default 0;
3 begin
4 select count(*) into num_col from user_tab_cols where table_name = upper('emp') and column_name = upper('add_col');
5 if (num_col = 0) then
6 execute immediate 'alter table emp_tmp add(add_col varchar2(20))'; ---- 这里一定要注意,用execute immediate才行。
7 end if;
8 end;
9 /
老中青结合,不知道为啥想到了这个词,呵呵