According to the PL/SQL User Guide,
Let's include in this ways in which you can get Oracle to define a cursor for you, as well.
Please submit a comment with JUST ONE example of a cursor definition. You can submit multiple ideas, but with multiple comments.
If you include several in a single comment, I will take only the first one that has not already been suggested
A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
So how many ways can you define a cursor in PL/SQL?Let's include in this ways in which you can get Oracle to define a cursor for you, as well.
Please submit a comment with JUST ONE example of a cursor definition. You can submit multiple ideas, but with multiple comments.
If you include several in a single comment, I will take only the first one that has not already been suggested
You can define a dynamic cursor a.k.a REF CURSOR
ReplyDeleteForgot an example :)
DeleteDECLARE
TYPE t_ref_cur IS REF CURSOR;
cur_ref t_ref_cur;
v_sql_stmt VARCHAR2(4000 CHAR);
BEGIN
v_sql_stmt := 'SELECT COL1,COL2 FROM TABLE1 WHERE 1 = 1 AND COL1 = :1';
<>
OPEN cur_ref FOR v_sql_stmt USING 'blabla';
LOOP
FETCH ... BULK COLLECT ...
EXIT WHEN ...
DO SOME STUFF
END LOOP x;
CLOSE cur_ref;
END;
an implicit cursor in a select statement:
ReplyDeleteselect dummy from dual
an implicit cursor for an update statement:
ReplyDeleteupdate emp set sal = sal * 1.1
an explicit cursor for a cursor for loop
ReplyDeletedeclare
cursor c is
select empno from emp;
begin
for r in c loop
-- do exciting stuff
null;
end loop;
end;
an implicit cursor for a cursor for loop
ReplyDeletebegin
for r in (select empno from emp) loop
-- do exciting stuff
null;
end loop;
end;
an explicit cursor for a while loop
ReplyDeletedeclare
cursor c is
select * from emp;
r c%rowtype;
begin
open c;
fetch c into r;
while c%found loop
-- do exciting stuff
fetch c into r;
end loop;
close c;
end;
explicit cursor:
ReplyDeletedeclare
cur_x is
select ...;
v_x cur_x%rowtype;
begin
open cur_x;
loop
fetch
--do some stuff
end loop;
close_cur_x;
end;
Dynamic cursor definition?
ReplyDeleteEXECUTE IMMEDIATE 'SELECT ' || p_col || ' FROM ' || p_table INTO v_variable;
cursor expression:
ReplyDeleteSELECT dname
,CURSOR ( SELECT ename
FROM emp e
WHERE (e.deptno = d.deptno)
ORDER BY ename)
FROM dept d
ORDER BY dname;
Cursor as part of pipelined function? Not sure whether it counts as Christian Chelemen also described something similar.
ReplyDeleteselect * from table(pip_fnc(cursor(select * from table)));
I went through all comments here and on LinkedIn. Here's the summary:
ReplyDeleteCursor variable (REF CURSOR)
Implicit cursor: SELECT
Implicit cursor: UPDATE, DELETE, INSERT
Explicit cursor in block (DECLARE CURSOR my_cur IS SELECT ...)
Cursor FOR loop (FOR rec IN (SELECT …))
Dynamic SQL cursor (EXECUTE IMMEDIATE)
CURSOR Expression
FORALL statement (FORALL [UPDATE | DELETE | INSERT ]
Dynamic SQL cursor (DBMS_SQL.OPEN_CURSOR)
But there is still at least one way to define a cursor in PL/SQL code that is not shown above. Can anyone figure it out?
I don't see any cursor passing arguments for variables...
ReplyDeleteI think the CURSOR expression should be excluded from the list. As it really is part of the SQL language. In plsql you can pass this into a variable or parameter of cause. But then this is a cursor variable (sysrefcursor or strong ref cursor).
ReplyDeleteImplicit result sets were not mentioned yet it seems. However it also does not really create a new cursor. Instead it allows to return a cursor to the caller, e.g. sql*plus.
ReplyDeleteHere is an example
create or replace procedure testReturnResult(cnt in binary_integer)
authid definer
is
myCur sys_refcursor;
begin
open myCur for select level num# from dual connect by level <= cnt;
DBMS_SQL.RETURN_RESULT (myCur);
end testReturnResult;
/
SQL> execute testReturnResult(17);
PL/SQL-Prozedur erfolgreich abgeschlossen.
ResultSet #1
NUM#
----------
1
2
3
4
5
6
7
8
9
10
11
NUM#
----------
12
13
14
15
16
17
17 Zeilen ausgewählt.
SQL>
@sven, you are right, CURSOR is SQL operator, thanks for clarifying.
ReplyDeleteThe only other thing I was looking for was defining a cursor in a package specification - it's an explicit cursor, but has a different form from the sort you declare in a block, because you can "hide" the SELECT in the package body:
CREATE OR REPLACE PACKAGE my_pkg AUTHID DEFINER
AS
CURSOR emps_cur RETURN employees%ROWTYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY my_pkg
AS
CURSOR emps_cur RETURN employees%ROWTYPE
IS
SELECT *
FROM employees;
END;
/
Really, never thinked about ot.
ReplyDelete