This article is written about how to declare a cursor in Oracle/PLSQL with syntax and examples.
Description
A cursor is a SELECT declaration that is described inside the announcement part of your PLSQL code. We’ll take a seem at three distinct syntaxes to declare a cursor.
Cursor without parameters (simplest)
Declaring a cursor except any parameters is the simplest cursor. Let’s take a nearer look.
Syntax
The syntax for a cursor without parameters in Oracle/PLSQL is:
CURSOR cursor_name
IS
SELECT_statement;Example
For example, you could define a cursor referred to as c1 as below.
CURSOR c1
IS
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in;The result set of this cursor is all course_numbers whose course_name suits the variable known as name_in.
Below is a function that uses this cursor.
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in;
BEGIN
OPEN c1;
FETCH c1 INTO cnumber;
if c1%notfound then
cnumber := 9999;
end if;
CLOSE c1;
RETURN cnumber;
END;Cursor with parameters
As we get extra complicated, we can declare cursors with parameters.
Syntax
The syntax for a cursor with parameters in Oracle/PLSQL is:
CURSOR cursor_name (parameter_list)
IS
SELECT_statement;Example
For example, you may want to outline a cursor called c2 as below.
CURSOR c2 (subject_id_in IN varchar2)
IS
SELECT course_number
FROM courses_tbl
WHERE subject_id = subject_id_in;The result set of this cursor is all course_numbers whose subject_id fits the subject_id surpassed to the cursor through the parameter.
Cursor with return clause
Finally, we can declare a cursor with a return clause.
Syntax
The syntax for a cursor with a return clause in Oracle/PLSQL is:
CURSOR cursor_name
RETURN field%ROWTYPE
IS
SELECT_statement;Example
For example, you should define a cursor referred to as c3 as below.
CURSOR c3
RETURN courses_tbl%ROWTYPE
IS
SELECT *
FROM courses_tbl
WHERE subject = 'Mathematics';The end result set of this cursor is all columns from the course_tbl the place the concern is Mathematics.