User Tools

Site Tools


kurs:cursor_state_in_stored_procedures

cursor lokal aufrufen

declare
cl_dep pkg_dep.c_dep%rowtype;
begin
fetch pkg_dep.c_dep into cl_dep;
dbms_output.put_line('ID: '||cl_dep.department_id||' '||cl_dep.department_name);
end;
-- Start of DDL Script for Package HR.PKG_DEP
-- Generated 06.11.2008 10:55:46 from HR@ORCL

CREATE OR REPLACE 
PACKAGE pkg_dep
  IS
  
  cursor c_dep is 
    SELECT departments.department_id, departments.department_name
      FROM departments 
      where department_id < 50
      order by department_id;
 
  r_dep c_dep%rowtype;  
 
  PROCEDURE get_next_dep_id;
   
  PROCEDURE get_next_dep_name;
 
END; -- Package spec
/


CREATE OR REPLACE 
PACKAGE BODY pkg_dep
IS
   procedure open_c_dep is
   begin 
    
     if (not c_dep%isopen)  then
       open c_dep;
     end if;
      if (c_dep%notfound) then
       close c_dep;
       open c_dep;
     end if;
   end;
      
   PROCEDURE get_next_dep_id is
   begin 
     open_c_dep;
     fetch c_dep into r_dep;
     dbms_output.put_line('ID: '||r_dep.department_id||' '||r_dep.department_name);
   end;
      
   PROCEDURE get_next_dep_name is 
   begin 
     open_c_dep;
     fetch c_dep into r_dep;
     dbms_output.put_line('Name: '||r_dep.department_name||' '||r_dep.department_id);
   end;
   
END;
/


-- End of DDL Script for Package HR.PKG_DEP
kurs/cursor_state_in_stored_procedures.txt · Last modified: 2014/09/10 21:22 (external edit)