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;