select 1 from col where tname=upper('t_test') and cname=upper('newfield');
我在存储过程中如何获取返回的结果,用输出参数输出?( colname in varchar ,num out varchar ) as
begin
select 1 from col where tname=upper('t_test')and cname=upper('||colname||');
end;
v_num number; begin select 1 into v_num from col where ...; if v_num =1 then ... end if; exception no_data_found then ...
create procedure pro as v_num number; str varchar2(50); begin select max(1) into v_num from col where ...; if v_num=1 then str:='alter table t_test drop column newfield'; else str:='alter table t_test add (newfield datatype)'; end if; execute immediate str; end; /
我的数据库是9i 我要操作的的字段名称来源于存储过程的参数,当我在存储过程中写固定字段时可以,当用参数传递时就不行了,大家看看我的代码,还有那里需要修改! ( colname in varchar ,num out varchar ) as v_num number;
begin select 1 into v_num from col where tname=upper('t_test')and cname=upper('||colname||'); if v_num=1 then num:=v_num; end if; exception when no_data_found then num:=0; end;
and cname=upper('newfield');
colname in varchar ,num out varchar
)
as
begin
select 1 from col where tname=upper('t_test')and cname=upper('||colname||');
end;
begin
select 1 into v_num from col where ...;
if v_num =1 then
...
end if;
exception no_data_found then
...
as
v_num number;
str varchar2(50);
begin
select max(1) into v_num from col where ...;
if v_num=1 then
str:='alter table t_test drop column newfield';
else
str:='alter table t_test add (newfield datatype)';
end if;
execute immediate str;
end;
/
方法如楼上所说的已经能实现了。
我要操作的的字段名称来源于存储过程的参数,当我在存储过程中写固定字段时可以,当用参数传递时就不行了,大家看看我的代码,还有那里需要修改!
(
colname in varchar ,num out varchar
)
as
v_num number;
begin
select 1 into v_num from col where tname=upper('t_test')and cname=upper('||colname||');
if v_num=1 then
num:=v_num;
end if;
exception
when no_data_found then
num:=0;
end;