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
MECHANISTI S THEORY-HAECKEL (1866) - Haeckel stating that after each catalysm, some new organism suddenly forms as a chance event in one stride from inanimate matter and sub

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and finds whether the employee ID is in the EMP

I need SQL Data Base Project Description: Network SQL database and SQL Setup two 4 workstation Skills required are Data Entry, MySQL, SQL

Creating and Destroying Base Tables: Example shows an SQL command to create the base table counterpart of the ENROLMENT variable Example  Creating a base table. CREATE T

EXECUTE IMMEDIATE Statement   The EXECUTE IMMEDIATE statement prepare (parses) and instantly executes a dynamic SQL statement or an anonymous PL/SQL block. Syntax:

DECLARE : This keyword signals the beginning of the declarative section of the PL/SQL block, that contains local declarations. The Items declared locally exist only within the

Semijoin and Composition - SQL For semijoin, the dyadic relational operator MATCHING, defined thus: r1 MATCHING r2, where r1 and r2 are relations such that r1 JOIN r2 is de

Use Native Dynamic SQL A few programs (a normal-purpose report writer for illustration) should build and process a variety of SQL statements at run time. Therefore, their full

How Bulk Binds Improve Performance The assigning of values to the PL/SQL variables in SQL statements is known as binding. The binding of the whole collection at once is know

Write a program to implement the inverted file shown in the slides (Simple Index file, LabelID file and Data file).  Use the Avail_List to point at the deleted Label IDs so that th