带参数的游标函数示例

--date2016-7-3 

--带参数的游标函数示例

create or replace function teacher_cursor_func(v_u number := 3) return varchar2 as 

--v_u number(5):= 3;
Cursor v_teacher_cursor(v_user_id number) is
select user_id,sex from teachers where user_id = v_user_id;


v_1 number(5);
v_sex varchar2(20) := 'not found!';
v_result varchar2(20);


begin


  if not v_teacher_cursor%isopen then
     open v_teacher_cursor(v_u);
  end if;


  loop
    fetch v_teacher_cursor into v_1,v_sex;
    exit when v_teacher_cursor%notfound;


    dbms_output.put_line(v_1||' '||v_sex);


  end loop;


  close v_teacher_cursor;
  
  v_result := v_sex;
  return v_result;


end;