Declaring a cursor, PL-SQL Programming

Declaring a Cursor

The Forward references are not allowed in the PL/SQL. Therefore, you must declare a cursor before referencing it in other statements. Whenever you declare a cursor, you name it and relate it with a specific query using the syntax as shown:

CURSOR cursor_name [(parameter[, parameter]...)]

[RETURN return_type] IS select_statement;

Where return_type should represent the record or a row in a database table, and parameter stand for the syntax as shown below:

cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression]

For illustration, you might declare cursors named c1 and c2, as shown below:


CURSOR c1 IS SELECT empno, ename, job, sal FROM emp

WHERE sal > 2000;


SELECT * FROM dept WHERE deptno = 10;

The cursor name is an undeclared identifier, not the name of the PL/SQL variable. You cannot assign values to the cursor name or use it in an expression. Though, cursors and variables follow the similar scoping rules. Naming cursors after the database tables is allowed but not suggested.

A cursor can take parameters that can appear in the related query wherever constants can appear. The formal parameters of a cursor should be IN parameters. And thus, they cannot return values to the actual parameters. Also, you cannot force the constraint NOT NULL on a cursor parameter.

As the illustration below shows, you can initialize the cursor parameters to default values.

In that way, you can pass various numbers of actual parameters to the cursor, accept or override the default values as you please. You can also add new formal parameters without having to change every reference to the cursor.




The scope of the cursor parameters is local to the cursor; it means that they can be referenced only within the query specified in the cursor declaration. The values of the cursor parameters are used by the related query when the cursor is opened.

Posted Date: 10/4/2012 3:51:37 AM | Location : United States

Related Discussions:- Declaring a cursor, Assignment Help, Ask Question on Declaring a cursor, Get Answer, Expert's Help, Declaring a cursor Discussions

Write discussion on Declaring a cursor
Your posts are moderated
Related Questions
Parameter SELF in pl/sql The MEMBER methods recognize a built-in parameter named SELF that is an instance of the object type. Whether declared explicitly or implicitly, it is

Bulk Binds advantages In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedur

Opening a Cursor Opening the cursor executes the query & identifies the result set that consists of all rows that meet the query search criteria. For the cursors declared usin

Multiset types - SQL An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an

%FOUND Until the SQL data manipulation statement is executed, the %FOUND yields NULL. Afterward, the %FOUND yields TRUE, when an INSERT, UPDATE, or DELETE statement affected o

Overview of control structures According to the structure theorem, any computer program can be written by using the basic control structures as shown in figure below. They can b

Delimiters A delimiter is a simple or compound symbol which has a special meaning to PL/SQL. For example, you use delimiters to symbolize an arithmetic operation like additio

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac

Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

#quesWrite a cursor to open an employee database and fetch the employee record whose age is greater than 45.tion..