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
Need for Dynamic SQL: You need dynamic SQL in the situations as follows: 1) You would like to execute a SQL data definition statement (like CREATE), a data control statemen

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding

The SQL ‘CREATE TABLE' scripts for all the tables you have implemented. Note that your tables must correspond exactly to the ERD you have provided in 1. above, or you will lose ma

Using LOCK TABLE You use the LOCK TABLE statement to lock the whole database tables in the specified lock mode so that you can share or deny the access to them. For illustrati

Operator Precedence The operations within an expression are completed in a particular order depending on their precedence (priority). The table shows the default order of the op

Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

OUT Mode An OUT parameter returns values to the caller of a subprogram. Within the subprogram, an OUT parameter act like a variable. That means that you can use an OUT formal

Effects of NULL for union - SQL The treatment of NULL in invocations of EXCEPT is as for UNION. This is different from its treatment in those of NOT IN and quantified compari

Variable Declaration - SQL SQL's support for variables is very similar to Tutorial D's, except that the syntax for creating persistent  variables-base tables-is quite differen

Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If