Opening a cursor variable, PL-SQL Programming

Assignment Help:

Opening a Cursor Variable

The OPEN-FOR statement relates a cursor variable with the multi-row query, executes the query, and then identifies the result set. The syntax for opening a cursor is as shown below:

OPEN {cursor_variable_name | :host_cursor_variable_name}

FOR select_statement;

Where the host_cursor_variable_name identify the cursor variable declared in the PL/SQL host environments like an OCI or Pro C program.

Dissimilar cursors, the cursor variables take no parameters. Though, no flexibility is lost as you can pass entire queries (not just parameters) to the cursor variable. The query can reference the host variables and the PL/SQL parameters, functions, and variables but cannot be FOR UPDATE. In the illustration below, you open the cursor variable emp_cv. Note that you can apply the cursor attributes (%ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT) to the cursor variable.

IF NOT emp_cv%ISOPEN THEN

/* Open cursor variable. */

OPEN emp_cv FOR SELECT * FROM emp;

END IF;

The Other OPEN-FOR statements can open similar cursor variable for various queries. You do not require closing a cursor variable before reopening it.  Whenever you reopen a cursor variable for various queries, the earlier query is lost.

Usually, you open the cursor variable by passing it to the stored procedure which declares a cursor variable as one of its formal parameters. For illustration, the packaged procedure below opens the cursor variable emp_cv:

CREATE PACKAGE emp_data AS

...

TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);

END emp_data;

CREATE PACKAGE BODY emp_data AS

...

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS

BEGIN

OPEN emp_cv FOR SELECT * FROM emp;

END open_emp_cv;

END emp_data;

Whenever you declare a cursor variable as the formal parameter of a subprogram which opens the cursor variable, you should specify the IN OUT mode. In the similar way, the subprogram can pass an open cursor back to the caller.

Or else, you can use a stand-alone process to open the cursor variable. Basically define the REF CURSOR type in the separate package, and then reference that type in the stand-alone process. For illustration, if you create the following bodiless package, you can make stand-alone process that references the types it defines:

CREATE PACKAGE cv_types AS

TYPE GenericCurTyp IS REF CURSOR;

TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;

TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

...

END cv_types;

In the next illustration, you create a stand-alone process which references the REF CURSOR type EmpCurTyp that is defined in the package cv_types:

CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS

BEGIN

OPEN emp_cv FOR SELECT * FROM emp;

END open_emp_cv;

To integrate the data retrieval, you can group the type-compatible queries in a stored procedure. In the illustration below, the packaged procedure declare a selector as one of its formal parameters. (In this framework, the selector is a variable used to select one of few alternatives in a conditional control statement.) Whenever called, the procedure opens the cursor variable emp_cv for the chosen query.

CREATE PACKAGE emp_data AS

TYPE GenericCurTyp IS REF CURSOR;

TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice NUMBER);

END emp_data;

CREATE PACKAGE BODY emp_data AS

PROCEDURE open_emp_cv (

emp_cv IN OUT EmpCurTyp,

choice NUMBER) IS

BEGIN

IF choice = 1 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;

ELSIF choice = 2 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;

ELSIF choice = 3 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;

END IF;

END open_emp_cv;

END emp_data;

For additional flexibility, you can pass a cursor variable & a selector to the stored procedure which executes queries with various return types. Consider this illustration as shown:

CREATE PACKAGE BODY emp_data AS

PROCEDURE open_cv (

generic_cv IN OUT GenericCurTyp,

choice NUMBER) IS

BEGIN

IF choice = 1 THEN

OPEN generic_cv FOR SELECT * FROM emp;

ELSIF choice = 2 THEN

OPEN generic_cv FOR SELECT * FROM dept;

ELSIF choice = 3 THEN

OPEN generic_cv FOR SELECT * FROM salgrade;

END IF;

END open_cv;

END emp_data;


Related Discussions:- Opening a cursor variable

Overriding default locking, Overriding Default Locking By default, the...

Overriding Default Locking By default, the Oracle locks the data structures for you automatically. Though, you can request exact data locks on rows or tables when it is to you

Package dbms pipe in pl/sql, DBMS_PIPE: The Package DBMS_PIPE allows va...

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

Control structure, Control Structures The Control structures are the mo...

Control Structures The Control structures are the most important PL/SQL extension to the SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data

Assignment of DBMS in SQL server, i have an assignment of DBMS subject, thi...

i have an assignment of DBMS subject, this assignment is based on SQL server and power BI

Cursor variables in pl sql, Cursor Variables:   To execute the multi-...

Cursor Variables:   To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names

Commit statement in pl sql, COMMIT Statement The COMMIT statement expli...

COMMIT Statement The COMMIT statement explicitly makes everlasting changes to the database during the present transaction. The Changes made to the database are not considered e

Need for dynamic sql - pl sql , Need for Dynamic SQL: You need dynamic...

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

Defining autonomous transactions, Defining Autonomous Transactions To ...

Defining Autonomous Transactions To define an autonomous transaction, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler

Relational operators-comparison operators, Relational Operators The rela...

Relational Operators The relational operators permit you to compare randomly complex expressions. The list below provides the meaning of each operator:

Assigning and comparing collections, Assigning and Comparing Collections ...

Assigning and Comparing Collections One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. A

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd