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:

DECLARE

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

WHERE sal > 2000;

CURSOR c2 RETURN dept%ROWTYPE IS

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.

DECLARE

CURSOR c1 (low INTEGER DEFAULT 0,

high INTEGER DEFAULT 99) IS SELECT ...

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
Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

BETWEEN Operator The operator BETWEEN, tests whether the value lies in a specified series. That means "greater than or equivalent to low value and less than or equivalent to hig

Perform the following queries on the Hospital1.DB using SQL Anywhere (START EARLY!). a. Which patients have purchased the drug "Tylenol"? List the names and addresses. Arrange the

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

IN OUT Mode An IN OUT parameter passes initial values to the subprogram being called and return efficient values to the caller. Within the subprogram, an IN OUT parameter acts

THEORY OF BIOGENESIS - This theory explains that the existing living organisms originated from pre-existing living beings not from non living entities. This concept of or

Define basic operators of relational algebra with an example each

Transaction Control The Oracle is transaction oriented; that is, Oracle uses the transactions to make sure the data integrity. The transaction is a sequence of SQL data manip

Scope Rules You cannot declare an exception twice in the similar block. Though, you can, declare the similar exception in 2 different blocks. The Exceptions declared in a bloc

Use of Table Expressions - Expressing Constraint Conditions With the exception of key constraints, the examples in the theory book all explicitly reference at least one relvar