3.Oracle光标的使用
--set serveroutput on
declare
--定义一个光标
cursor cemp is
select ts.id_status, ts.id_step
from t_application ts
where ts.cdate >= to_date('2019-02-28', 'yyyy-mm-dd')
order by id desc;
---为光标定义对应的变量
p1 t_application.ID_STATUS%type;
p2 t_application.ID_STEP%type;
begin
---打开光标
open cemp;
loop
fetch cemp
into p1, p2;
exit when cemp% notfound;
--打印
dbms_output.put_line('status=' || p1 || 'step' || p2);
end loop;
---关闭光标
close cemp;
end;
status=88step16
status=111step
status=2step14
status=2step14
status=2step14
status=2step14
status=2step14
status=2step14
status=102step
status=2step14
status=102step
--set serveroutput on
declare
p_source VARCHAR2(50) := '';
p_content VARCHAR2(50) := '';
p_date VARCHAR2(50) := '2019-02-28';
p_id_select NUMBER := -200;
p_out_put VARCHAR2(500) := '';
type ref_cursor is ref cursor; --动态游标的值
c1 ref_cursor;
c1Row t_applicant%ROWTYPE; --与SF_ORG表中的各个列相同
--定义一个光标
cursor cemp is
select id, cdate, ts.id_status, ts.id_step
from t_application ts
where ts.cdate >= to_date(p_date, 'yyyy-mm-dd')
AND ts.id = (CASE
WHEN p_id_select > 0 THEN
p_id_select
ELSE
ts.id
end)
order by id desc;
---为光标定义对应的变量
p_id t_application.ID%type;
p_cdate t_application.cdate%type;
p_id_status t_application.ID_STATUS%type;
p_id_step t_application.ID_STEP%type;
begin
---打开光标
open cemp;
loop
fetch cemp
into p_id, p_cdate, p_id_status, p_id_step;
exit when cemp% notfound;
p_source := '';
p_content := '';
select track.utm_source, track.utm_content
into p_source, p_content
from t_tracking track
where track.id_application = p_id;
--动态打印t_applicant中的值
OPEN c1 for
select * from t_applicant a where a.id_application = p_id;
loop
fetch c1
into c1Row;
exit when c1% notfound;
-- dbms_output.put_line('IdCardInfo-->name=' || c1Row.id || ',idCard_NBR=' ||
-- c1Row.IDCARD_NBR || 'phone_NBR=' ||
-- c1Row.PHONE_NBR);
p_out_put := 'name=' || c1Row.id || ',idCard_NBR=' ||
c1Row.IDCARD_NBR || 'phone_NBR=' || c1Row.PHONE_NBR;
end loop;
close c1;
--打印
dbms_output.put_line('app=[id=' || p_id || ',p_cdate=' ||
to_char(p_cdate, 'yyyy-mm-dd hh24:mi:ss') ||
',Source=' || p_source || ',p_content=' ||
p_content || ']' || ',p_out_put=' || p_out_put);
end loop;
dbms_output.put_line('All count is=' || cemp%rowcount);
---关闭光标
close cemp;
end;
Oralce Select into时候,一定要记得捕获异常,否则会有问题,如果为空的话,会报异常
BEGIN
select track.utm_source, track.utm_content
into p_source, p_content
from t_tracking track
where track.id_application = p_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_source := '';
p_content := '';
End;