CREATE OR REPLACE PROCEDURE "ADD_FIELD"
(
tablename in varchar2,
fieldname in varchar2,
fieldtype in varchar,
fieldlen in varchar,
p_result out number
)as
begin
if fieldlen is not null then
execute immediate 'alter table ' + tablename + 'Add ' + fieldname + ' ' + fieldtype+'(' + to_number(fieldlen)+')';
else
execute immediate 'alter table ' + tablename + 'Add ' + fieldname +' '+ fieldtype;
end if;
commit;
p_result := 0;
return;
Exception
when others then
p_result := -1;
return;
end add_field;
-------------------------------------------------------------------------
上面是存储过程
提示我execute immediate后面类型错误
请问alter table +变量 怎么写? 我的表名称、列名称、长度、类型 都是动态的
(
tablename in varchar2,
fieldname in varchar2,
fieldtype in varchar,
fieldlen in varchar,
p_result out number
)as
begin
if fieldlen is not null then
execute immediate 'alter table ' + tablename + 'Add ' + fieldname + ' ' + fieldtype+'(' + to_number(fieldlen)+')';
else
execute immediate 'alter table ' + tablename + 'Add ' + fieldname +' '+ fieldtype;
end if;
commit;
p_result := 0;
return;
Exception
when others then
p_result := -1;
return;
end add_field;
-------------------------------------------------------------------------
上面是存储过程
提示我execute immediate后面类型错误
请问alter table +变量 怎么写? 我的表名称、列名称、长度、类型 都是动态的
(
tablename in varchar2,
fieldname in varchar2,
fieldtype in varchar,
fieldlen in varchar,
p_result out number
)as
v_sql varchar2(2000);
begin
if fieldlen is not null then
v_sql:= 'alter table ' + tablename + 'Add ' + fieldname + ' ' + fieldtype+'(' + to_number(fieldlen)+')';
else
v_sql:='alter table ' + tablename + 'Add ' + fieldname +' '+ fieldtype;
end if;
execute immediate v_sql;
commit;
p_result := 0;
return;
Exception
when others then
p_result := -1;
return;
end add_field;
用+号就会报出你那个错来,但是用||连接就不会,
sql语句用变量代替,感觉可读性要好些,仅此而已.