函数中执行alter语句报错
函数定义如下:
create or replace function UniMonDB.updateMenus() return int is
pragma autonomous_transaction;
begin
if not exists(SELECT NAME FROM SYSCOLUMNS WHERE ID = (SELECT ID FROM SYSOBJECTS WHERE NAME LIKE 'Tbl_aaa') AND NAME LIKE 'uiddomainid') then
ALTER TABLE Tbl_aaa ADD uiddomainid varchar(128);
update Tbl_aaa set uiddomainid = (select uiddomainid from tbl_bbb limit 1) where uidDomainID = '' or uidDomainID is null;
end if;
commit;
return 1;
end;
DDL语句要动态SQL
create or replace function updateMenus()
return int
is
pragma autonomous_transaction;
v_sql varchar2(1000);
begin
v_sql:=‘ALTER TABLE test ADD c varchar(128)’;
execute immediate v_sql;
commit;
return 1;
end;
方便发一个表结构来看下吗