Private cursors are SQL best practice
I work in a large, separate development team. The project I am currently working on has an Oracle DB team that develops stored procedures and other related components that our J2EE web tier is talking about.
One thing that came from development is the concept of a closed cursor when no results are found.
In my experience, closed cursors are an exceptional circumstance; a programming error that is not necessarily data related. In the current context, this means no data was found when an empty set / cursor would make more sense to me.
What do people think or have experience from their point of view? Any Oracle SQL developers who could shed some light if I am wrong from a conventional point of view?
Best practics?
Hooray!
source to share
I'm with you. Returning an empty result set makes the most sense to me.
It's all about separation of concerns. Data retrieval is a service. Whereas, NO_DATA_FOUND exception handling belongs to the calling application.
change
I would expect to find cursor%NOTFOUND
in a PL / SQL procedure that handles a ref cursor. For example, the PAYROLL subroutine can use a function in the SALES subsystem that returns the cursor cursor of all orders made by salespeople (in a given department, in a certain quarter, whatever).
I would expect the PAYROLL routine to loop through the returned result set and check for cursor%NOTFOUND
. I would not expect the SALES function to do this and return an empty cursor if there are no matching sellers. Besides violating the Principle of Least Surprise, this also means that the extract function is doing more work (opening the ref cursor twice) or returning incorrect results.
SQL> create function get_emps(dno number) return sys_refcursor is
2 rc sys_refcursor;
3 begin
4 open rc for select * from emp where deptno = dno;
5 return rc;
6 end;
7 /
Function created.
SQL> var rc refcursor
SQL>
SQL> exec :rc := get_emps(10)
PL/SQL procedure successfully completed.
SQL> print rc
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- ---------- ---- ---- ------
7782 BOEHMER MANAGER 7839 09-06-1981 2450 10
7839 SCHNEIDER PRESIDENT 17-11-1981 5000 10
7934 KISHORE CLERK 7782 23-01-1982 1300 10
SQL>
SQL> create or replace function get_emps(dno number) return sys_refcursor is
2 rc sys_refcursor;
3 lrow emp%rowtype;
4 begin
5 open rc for select * from emp where deptno = dno;
6 fetch rc into lrow;
7 if rc%notfound then
8 close rc;
9 end if;
10 return rc;
11 end;
12 /
Function created.
SQL> exec :rc := get_emps(15)
PL/SQL procedure successfully completed.
SQL> print rc
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "rc"
SQL> exec :rc := get_emps(10)
PL/SQL procedure successfully completed.
SQL> print rc
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- ---------- ---- ---- ------
7839 SCHNEIDER PRESIDENT 17-11-1981 5000 10
7934 KISHORE CLERK 7782 23-01-1982 1300 10
SQL>
source to share