Cursor and Ref Cursor defination with example

DECLARE

 var_rows number(5);

BEGIN

UPDATE employee SET salary = salary + 1000;

IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');

ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;


In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated.
If none of the employee’s salary are updated we get a message 'None of the salaries where updated'.
Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.

Ref Cursor ?

A cursor variable is a pointer that distinguishes the current row in a resultset from a multi-row query.
Cursor variables have several advantages over explicit cursors
including: Cursor variables can point to a variety of queries provided the queries have a suitable return type.
In contrast, explicit cursors are tied to individual queries.
Cursor variables allow cursors to be opened independently of being processed.
Cursor variables can be passed as parameters between application layers, as well as between server side components.
Cursor variables can be used to reduce client-server network traffic by allowing several cursors to be opened on the server in a single round trip.
The following sections illustrate ways in which cursor variables can be used in PL/SQL code.
A thorough understanding of these features will allow programmers to build more efficient PL/SQL APIs and may prove useful as a replacement for passing
large collections as parameters between PL/SQL.
The example code in this chapter relies on the cursor_variable_test table which is created with the cursor_variable_test.sql script listed below.
cursor_variable_test.sql

-- Create and populate a test table.
CREATE TABLE
cursor_variable_test ( id NUMBER(10), description VARCHAR2(50) );
INSERT INTO cursor_variable_test (id, description) VALUES (1, 'One');
INSERT INTO cursor_variable_test (id, description) VALUES (2, 'Two');
INSERT INTO cursor_variable_test (id, description) VALUES (3, 'Three');
COMMIT;
The examples in this chapter regularly make use of the SYS_REFCURSOR type which became available in Oracle9i.
To make this script backwards compatible, create the package specification listed in the cursor_api.sql
script and replace any reference to “SYS_REFCURSOR” with “cursor_api.refcursor”.
cursor_api.sql -- *****************************************************************
-- Copyright 2005 by Rampant TechPress Inc.
-- Free for non-commercial use! To license, e-mail info@rampant.cc --
*****************************************************************
CREATE OR REPLACE PACKAGE
cursor_api AS TYPE refcursor IS REF CURSOR;
END cursor_ api;
/ Defining Cursor Variables Cursor variables are defined using a REF CURSOR type.
The type is defined in one of two ways: Strongly Typed
- The REF CURSOR type is restricted to an individual return type using the RETURN clause.
Although this reduces the chances of runtime errors since column mismatches are detected at compilation time, it also limits the overall flexibility of the type.
Weakly Typed - The RETURN clause is omitted allowing the type to reference any return type.
This gives greater flexibility, but it increases the likelihood of runtime errors because column mismatches are not picked up at compile time.
The cursor_variable_definitions.sql script shows how strongly and weakly typed cursor variables are defined.
Cursor and refcursor:
differences.
1. (i) . Cursor is Static(no matter how many times you run that block).
(ii). Ref Cursor is Dynamic (opened based on logic).The ref cursor can be anything
2. (i) . Cursor will not return any value,but Ref cursor will return.
(ii) . a ref cursor can be passed from subroutine to subroutine -- a cursor cannot be
3. cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function).
4. Static sql (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to
- returning result sets to clients - when there is NO other efficient/effective means of achieving the goal