oracle中的存储过程案例

1、无数据查询，对游标无效

create or replace procedure noData is
v_val varchar2(100);
begin
  select val1 into v_val from test where rownum = 1;
  dbms_output.put_line(v_val);
  exception
           when no_data_found then
           dbms_output.put_line('no data');
end noData;

 

 

2、嵌套的游标

create or replace procedure nestedCustor is

  cursor CUR_master is
    SELECT val1 from test;

  cursor cur_new is
    select val2 from new;

  v_test varchar2(100);
  v_new  varchar2(100);

begin
  open CUR_master;

  FETCH CUR_master
    into v_test;

  while CUR_master%found loop
    open cur_new;
    fetch cur_new
      into v_new;
    while cur_new%found loop
      dbms_output.put_line(v_new);
      fetch cur_new
        into v_new;
    end loop;
    close cur_new;
 
    dbms_output.put_line(v_test);
    FETCH CUR_master
      into v_test;
  end loop;

 

CLOSE CUR_master;

 

exception
  when others then
    dbms_output.put_line('error');

    dbms_output.put_line(substr(SQLERRM, 1, 255));
  
    if (cur_new%isopen) then
      close cur_new;
    end if;
 
    if (CUR_master%isopen) then
      close CUR_master;
    end if;
 
end nestedCustor;