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
Package STANDARD package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available a

LIKE Operator You use the LIKE operator to compare the character value to a pattern. The Case is significant. LIKE returns the Boolean value TRUE when the character patterns mat

SQL Pseudocolumns The PL/SQL recognizes the following SQL pseudocolumns, that returns the specific data items: LEVEL, NEXTVAL, CURRVAL, ROWID, & ROWNUM. The Pseudocolumns are n

Obtaining a natural join by specifying the common columns Synatax: SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON USING ( StudentId ) However, a named columns join doe

BIOCHE M ICA L ORIGIN OF LIFE - It is generally agreed by astronomers, geologists and biologists that the earth is approximately 4500-5000 million years old. It is an

Difference between 9i & 10G When Oracle releases any new databases then it are having some discrepancy with them. But 10G is having much difference than oracle 9i has. Oracle

Application to Export Excel Data to MSSQL Server table I am having a table available in excel format and features the subsequent: - Some text is in Arabic (e.g. UTF-8 encodin

Challenge 1 You are required to do the project and write a test plan for it. Demo 4 is a check writer program for employees. In it, the user enters all information about the

Type versus Representation Confusion in SQL This describes how a value might have two or more distinct representations. For example, user-defined type POINT might have a decla

Advantages of PL/SQL The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown: 1) Support for SQL