This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | |||
kurs:cursor_state_in_stored_procedures [2008/11/12 09:33] mh |
kurs:cursor_state_in_stored_procedures [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | cursor lokal aufrufen | ||
+ | <code> | ||
+ | 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; | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | -- 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 | ||
+ | </code> |