How To Define a Cursor Variable

Q

How To Define a Cursor Variable? - Oracle DBA FAQ - Working with Cursors in PL/SQL

✍: FYIcenter.com

A

To define cursor variable, you must decide which REF CURSOR data type to use. There are 3 ways to select a REF CURSOR data type:

Define your own specific REF CURSOR types using the TYPE ... RETURN statement.
Define your own generic REF CURSOR type using the TYPE ... statement.
Use the system defined REF CURSOR type: SYS_REFCURSOR.

The follwoing tutorial exercise defines 3 cursor variables in 3 different ways:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  TYPE emp_ref IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE any_ref IS REF CURSOR;
  emp_cur emp_ref;
  any_cur any_ref;
  sys_cur SYS_REFCURSOR;
BEGIN
  NULL;
END;
/

2007-04-28, 4988👍, 0💬

How To Pass a Parameter to a Cursor
How To Pass a Parameter to a Cursor? - Oracle DBA FAQ - Working with Cursors in PL/SQL When you define a cursor, you can set a formal parameter in the cursor. The formal parameter will be replaced by an actual parameter in the OPEN cursor statement. Here is a good example of a cursor with two parame... 2007-04-28, 5302👍, 0💬

What Is a Cursor Variable
What Is a Cursor Variable? - Oracle DBA FAQ - Working with Cursors in PL/SQL A cursor variable is a variable of a specific REF CURSOR data type, which is a pointer to a data structure resource connects to query statement result, similar to the CURSOR data type.. The advantage of using cursor variabl... 2007-04-28, 5231👍, 0💬

How To Loop through a Cursor Variable
How To Loop through a Cursor Variable? - Oracle DBA FAQ - Working with Cursors in PL/SQL Once a cursor variable is opened with a query statement, it will have the same attributes as a normal cursor and it can be used in the same way a normal cursor too. The following sample script shows you how to l... 2007-04-28, 5065👍, 0💬

How To Open a Cursor Variable
How To Open a Cursor Variable? - Oracle DBA FAQ - Working with Cursors in PL/SQL A cursor variable must be opened with a specific query statement before you can fetch data fields from its data rows. To open a cursor variable, you can use the OPEN ... FOR statement as shown in the following tutorial ... 2007-04-28, 5008👍, 0💬

How To Use an Explicit Cursor without OPEN Statements
How To Use an Explicit Cursor without OPEN Statements? - Oracle DBA FAQ - Working with Cursors in PL/SQL If you want to open a cursor and loop through its data rows in quick way, you can use the FOR ... IN ... LOOP statement in the same way as the implicit cursor. The following tutorial exercise giv... 2007-04-29, 4991👍, 0💬

How To Define a Cursor Variable
How To Define a Cursor Variable? - Oracle DBA FAQ - Working with Cursors in PL/SQL To define cursor variable, you must decide which REF CURSOR data type to use. There are 3 ways to select a REF CURSOR data type: Define your own specific REF CURSOR types using the TYPE ... RETURN statement. Define yo... 2007-04-28, 4989👍, 0💬

Why Cursor Variables Are Easier to Use than Cursors
Why Cursor Variables Are Easier to Use than Cursors? - Oracle DBA FAQ - Working with Cursors in PL/SQL Cursor variables are easier to use than cursors because: Cursor variables are easier to define. No need to give a specific query statement. Cursor variables are easier to open. You can specify the ... 2007-04-28, 4974👍, 0💬

How To Pass a Cursor Variable to a Procedure
How To Pass a Cursor Variable to a Procedure? - Oracle DBA FAQ - Working with Cursors in PL/SQL A cursor variable can be passed into a procedure like a normal variable. The sample script below gives you a good example: CREATE OR REPLACE PROCEDURE FYI_CENTER AS sys_cur SYS_REFCURSOR; PROCEDURE emp_pr... 2007-04-28, 4963👍, 0💬

How To Use FETCH Statement in a Loop
How To Use FETCH Statement in a Loop? - Oracle DBA FAQ - Working with Cursors in PL/SQL If you have a cursor opened ready to use, you can also use the FETCH statement in a loop to retrieve data from the cursor more efficiently. But you need to remember to use an EXIT statement break the loop when th... 2007-04-29, 4936👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.