oracle存储过程游标

--游标
--抓取一条记录
declare
  cursor x_cursor is
    select * from sys_org_element;
  x_sys_org_element sys_org_element%rowtype;
begin
  open x_cursor;
  fetch x_cursor
    into x_sys_org_element;
  dbms_output.put_line(x_sys_org_element.fd_id);
  dbms_output.put_line(x_sys_org_element.fd_name); 
  close x_cursor;
end;
--循环抓取多条记录


declare
  cursor x_cursor is
    select fd_id,butxt from hisense_hr_t001;
  v_fd_id hisense_hr_t001.fd_id%type;
  v_butxt hisense_hr_t001.butxt%type;
begin
  open x_cursor;
  loop
    exit when x_cursor%notfound;
    fetch x_cursor
      into v_fd_id,v_butxt;
    dbms_output.put_line(v_fd_id);
    dbms_output.put_line(v_butxt);
  end loop;
  close x_cursor;
end;


--带参数的游标
DECLARE
  CURSOR x_cursor IS
    SELECT fd_id, bukrs FROM hisense_hr_t001 where bukrs='2000';
  CURSOR y_cursor(x_bukrs VARCHAR) IS
    SELECT distinct pernr FROM hisense_hr_pa0001 WHERE bukrs = x_bukrs;
  t_fd_id   hisense_hr_t001.fd_id % type;
   t_bukrs   hisense_hr_t001.bukrs % type;
  p_fd_id hisense_hr_pa0001.fd_id % type;
   p_pernr hisense_hr_pa0001.pernr % type;
BEGIN
  OPEN x_cursor;
    FETCH x_cursor
      INTO t_fd_id,t_bukrs;
    dbms_output.put_line(t_fd_id);
    dbms_output.put_line(t_bukrs);
    OPEN y_cursor(t_bukrs);
    loop
      FETCH y_cursor
        INTO  p_pernr;
      exit WHEN y_cursor % notfound;
      dbms_output.put_line(p_pernr);
    END loop;
    CLOSE y_cursor;
  CLOSE x_cursor;
END;