如下语句写在一procedure中
cursor c1(myx201 in varchar2,myserv in varchar2,myaccount in varchar2)
is
select servicecode servicecode,accountname
from myserv a,myaccount b
where a.servicecode in (select substr(myx201.caller,length(myx201.caller)-6,7) from myx201)
and a.accountnum=b.accountnum;
编译的时候报表myserv 和myaccount 不存在.请问表名不能作为参数传入的吗?
传入到myserv 和myserv 参数的实际表的大小分别在200万和150万行,每月增长
2/3万行.游标打开后用来循环,
open c1(x201,m_service,m_account);
loop
fetch c1 into mycode,myaccountname;
exit when c1%notfound;
m_sql:='update ' ¦ ¦x201 ¦ ¦' set accountname=myaccountname where caller=mycode';
execute immediate m_sql;
end loop;
close c1;
x201表在100万行的样子.
求问有什么更好的方法?
cursor c1(myx201 in varchar2,myserv in varchar2,myaccount in varchar2)
is
select servicecode servicecode,accountname
from myserv a,myaccount b
where a.servicecode in (select substr(myx201.caller,length(myx201.caller)-6,7) from myx201)
and a.accountnum=b.accountnum;
编译的时候报表myserv 和myaccount 不存在.请问表名不能作为参数传入的吗?
传入到myserv 和myserv 参数的实际表的大小分别在200万和150万行,每月增长
2/3万行.游标打开后用来循环,
open c1(x201,m_service,m_account);
loop
fetch c1 into mycode,myaccountname;
exit when c1%notfound;
m_sql:='update ' ¦ ¦x201 ¦ ¦' set accountname=myaccountname where caller=mycode';
execute immediate m_sql;
end loop;
close c1;
x201表在100万行的样子.
求问有什么更好的方法?
select * from tableName where fieldOne=param1; 这样是不可以的 select * from param1; 这里param1 必须为表名,不能使用参数。* 部分可以使用参数,但返回的值是参数本身。
type t_sor is ref cursor;
v_sor t_sor;
str varchar2(1000);
begin
str:='select servicecode servicecode,accountname
from '||myserv||' a,'||myaccount||' b
where a.servicecode in (select substr(myx201.caller,length(myx201.caller)-6,7) from '||myx201||')
and a.accountnum=b.accountnum';
open v_sor for str;
loop
fetch v_sor into ...;
exit when v_sor%notfound;
....
end loop;
close v_sor;
...
end;
/