open Retresult FOR 'select ACC_CODE from TEMP_AU_W_REPANALY'; LOOP fetch Retresult into acccode; exit when Retresult%notfound; execute immediate sqlstr; end LOOP; end loop; commit; --输出游标变量 open RetResult for 'select * from TEMP_AU_W_REPANALY'; return RetResult;===============此处 游标变量RetResult我使用了两次,能这样使用吗?如上
LOOP
fetch Retresult into acccode;
exit when Retresult%notfound;
execute immediate sqlstr;
end LOOP;
end loop;
commit; --输出游标变量
open RetResult for 'select * from TEMP_AU_W_REPANALY';
return RetResult;===============此处
游标变量RetResult我使用了两次,能这样使用吗?如上
return RetResult;
如果我关闭了RetResult好象就RETURN不了这个变量了。
上面的代码该如何关闭游标?
如果不关闭,是否很占数据库的连接数?
prompt
prompt Creating function SEND_NEXT
prompt ===========================
prompt
create or replace function send_next(i_host in varchar2, i_port in number, v_task in scheduler%rowtype)
return varchar2 is
v_return_char varchar2(1000);
v_mode varchar2(60);
v_type varchar2(1);
v_charge_number schjob.chargenumber%type;
v_mailfrom schjob.mailfrom%type;
v_message schjob.messagecontent%type;
v_attachcnt number;
v_jobcnt number; cursor select_schjob is select chargenumber,messagecontent,mailfrom from schjob
where oid = v_task.joid;begin
v_mode := analyse_prio(v_task.usernumber, v_task.lastmode||v_task.lastphoneno);
if lengthb(v_mode) > 1 then
v_type := substrb(v_mode, 1, 1);
v_mode := substrb(v_mode, 2);
open select_schjob;
fetch select_schjob into v_charge_number, v_message, v_mailfrom;
if select_schjob%found then
if v_type = 'S' then
v_return_char := send_sms(i_host, i_port, v_task,v_mode, v_charge_number, v_message);
elsif v_type = 'V' then
select count(*) into v_attachcnt
from attachment
where attachment.joid = v_task.joid;
v_return_char := send_voice(i_host, i_port, v_task, v_mode, v_charge_number, v_task.joid, v_attachcnt, v_message);
elsif v_type = 'F' then
select count(*) into v_attachcnt
from attachment
where attachment.joid = v_task.joid;
v_return_char := send_fax(i_host, i_port, v_task, v_mode, v_charge_number, v_task.joid, v_attachcnt, v_message);
elsif v_type = 'E' then
select count(*) into v_attachcnt
from attachment
where attachment.joid = v_task.joid;
v_return_char := send_email(i_host, i_port, v_charge_number, v_mailfrom, v_mode, 'tt', v_task.joid,
v_attachcnt, v_task.feevalue, v_task.feevalue, v_message);
else
v_return_char := 'ReturnCode=EMode';
end if; update scheduler
set serial1 = analyse_substr(v_return_char, 'S1=', '&'),
serial2 = analyse_substr(v_return_char, 'S2=', '&'),
serial3 = analyse_substr(v_return_char, 'S3=', '&'),
lastmode = v_type,
lasttime = sysdate,
lastphoneno = v_mode
where oid = v_task.oid;
end if;
end if; if analyse_substr(v_return_char, 'ReturnCode=', '&') = 'OK' then
return 'OK';
else
return 'FAIL';
end if;
exception
when others then
return 'ReturnCode=fail';
end;