CSDN 对不齐,弄的很乱,郁闷,讲究看看。功能是,通过3个select 语言找出数据插到表中。function AddMoveDataToOrignNas(vOldNode number,vNewNode number,vdomain varchar2) return number is
cityid number;
countryid number;
num number;
ip number;
cursor cur_num is
select max(orderby) as num from auth_base_orignasinfo where enabled=1 and node=vnewNode;
cursor cur_cityid is select listparentid,listvalue from biz_sys_dd where listkey='COMMON_COUNTY';
cursor cur_ip is select ipaddr from auth_base_orignasinfo;
begin
for mobile in cur_mobile loop
insert into auth_base_orignasinfo(id,ipaddr,domain,node,cityid,countyid,orderby,modifydate,enabled,moved) values(Auth_Base_Orignasinfo_SEQ.Nextval,cur_ip.ipaddr,vdomain,vnewNode,cur_cityid.listparentid,cur_cityid.listvalue,cur_num.num,sysdate,1,1); //提示 cur_num.num out of scope
end loop;
return 1;
end AddMoveDataToOrignNas;
cityid number;
countryid number;
num number;
ip number;
cursor cur_num is
select max(orderby) as num from auth_base_orignasinfo where enabled=1 and node=vnewNode;
cursor cur_cityid is select listparentid,listvalue from biz_sys_dd where listkey='COMMON_COUNTY';
cursor cur_ip is select ipaddr from auth_base_orignasinfo;
begin
for mobile in cur_mobile loop
insert into auth_base_orignasinfo(id,ipaddr,domain,node,cityid,countyid,orderby,modifydate,enabled,moved) values(Auth_Base_Orignasinfo_SEQ.Nextval,cur_ip.ipaddr,vdomain,vnewNode,cur_cityid.listparentid,cur_cityid.listvalue,cur_num.num,sysdate,1,1); //提示 cur_num.num out of scope
end loop;
return 1;
end AddMoveDataToOrignNas;
cur_mobile ??哪来的??cursor cur_num is
select max(orderby) as num from auth_base_orignasinfo where enabled=1 and node=vnewNode;
cursor cur_cityid is select listparentid,listvalue from biz_sys_dd where listkey='COMMON_COUNTY';
cursor cur_ip is select ipaddr from auth_base_orignasinfo;
这三个游标你用它了吗?我看到了在for loop中用了???
游标最起码要经历open---->fetech---close的生命周期吧,在你这我看不到这个生命周期建议把游标怎么用的看看
我前面测试过,不经过open---->fetech-游标可以用的,能取出数据。
select max(orderby) as num from auth_base_orignasinfo where enabled=1 and node=vnewNode;
其实就一条记录,可以不用游标,但问题是我去掉cursor cur_num is ,直接写select 语句编译通不过?还清高手指教。
问题2:insert into auth_base_orignasinfo(id,ipaddr,domain,node,cityid,countyid,orderby,modifydate,enabled,moved) values(Auth_Base_Orignasinfo_SEQ.Nextval,cur_ip.ipaddr,vdomain,vnewNode,cur_cityid.listparentid,cur_cityid.listvalue,cur_num.num,sysdate,1,1);
红色游标名未见打开这些游标。
问题3:
create or replace function AddMoveDataToOrignNas(vOldNode number,vNewNode number,vdomain varchar2) return number
as
l_num number:=0;
begin
select max(orderby) into l_num from auth_base_orignasinfo where enabled=1 and node=vnewNode;
end AddMoveDataToOrignNas;这样写可以不用游标查询出你需要的num。
你的这个函数错误很多,有很多地方的游标不知道从哪里来的(如:cur_mobile ),有些地方的游标没有显示打开就在使用。