PL/SQL Cursor Variable – Ref Cursors (Strong & Weak Type)

In previous blog we have explored the Cursors in PL/SQL. Where we have explored Implicit & Explicit Cursors. Implicit & Explicit cursors are static in nature and always tied up with the some defined SQL statement. Once the explicit cursor is declared, you can not change the SQL statement in the PL/SQL program.

Cursor variable is like an explicit cursor that is not limited to one query. Cursor variable can be opened for any query, and for different query in one PL/SQL program.

Declaring REF Cursors :

Create a reference cursor type & declare the actual cursor variable based on type.

TYPE CURSOR_NAME IS REF CURSOR [RETURN return_type];
CURSOR_VARIABLE CURSOR_NAME;

Return clause is optional with REF CURSOR type statement. See below both type of declaration.

-- Declaration without return type
TYPE CUST_CO IS REF CURSOR;
CUST_CO_VAR CUST_CO;

-- Declaration with return type
TYPE CUST_CO IS REF CURSOR RETURN CUSTOMER%ROWTYPE;
CUST_CO_VAR CUST_CO;

The first declaration of REF CURSOR is Weak Type, as there is no return type defined. Here cursor variable is not associated with any data structure. Cursor variable can be used for any query or any structure.

Second declaration where return type is defined is called Strong Type, as it is associated with the data structure. Cursor variable declared for this type can only fetch into data structure match with the return type.

Example for Weak Type:
DECLARE
TYPE CUST_CO IS REF CURSOR;
CUST_CO_VAR CUST_CO;

CUST_REC CUSTOMERS%ROWTYPE;
ORDITEMS_REC ORDER_ITEMS%ROWTYPE;
ORD_REC ORDERS%ROWTYPE;
BEGIN
OPEN CUST_CO_VAR FOR SELECT * FROM CUSTOMERS;
FETCH CUST_CO_VAR INTO CUST_REC;
dbms_output.put_line(CUST_REC.CUSTOMER_ID || ' ' || CUST_REC.EMAIL_ADDRESS || ' - ' ||    CUST_REC.FULL_NAME );

OPEN CUST_CO_VAR FOR SELECT * FROM ORDER_ITEMS;
FETCH CUST_CO_VAR INTO ORDITEMS_REC;
dbms_output.put_line(ORDITEMS_REC.ORDER_ID || ' ' || ORDITEMS_REC.PRODUCT_ID );

OPEN CUST_CO_VAR FOR SELECT * FROM ORDERS;
FETCH CUST_CO_VAR INTO ORD_REC;
dbms_output.put_line(ORD_REC.ORDER_ID || ' ' || ORD_REC.CUSTOMER_ID || ' '|| ORD_REC.ORDER_STATUS );
CLOSE CUST_CO_VAR;
END;
/

SYS_REFCURSOR – predefined weak ref cursor which comes built-in with the Oracle database. So no need to define a weakly typed REF CURSOR type.

DECLARE
cust_cursor  SYS_REFCURSOR;

CUST_REC CUSTOMERS%ROWTYPE;
PRDCT_REC PRODUCTS%ROWTYPE;
BEGIN
dbms_output.put_line('==============Customers==============' );
OPEN cust_cursor FOR SELECT * FROM CUSTOMERS where CUSTOMER_ID <=5;
LOOP
FETCH cust_cursor INTO CUST_REC;
exit when cust_cursor%NOTFOUND;
dbms_output.put_line(CUST_REC.CUSTOMER_ID || ' ' || CUST_REC.EMAIL_ADDRESS || ' - ' ||    CUST_REC.FULL_NAME );
END LOOP;

dbms_output.put_line('==============Products==============' );
OPEN cust_cursor FOR SELECT * FROM PRODUCTS where PRODUCT_ID <=5;
LOOP
FETCH cust_cursor INTO PRDCT_REC;
exit when cust_cursor%NOTFOUND;
dbms_output.put_line(PRDCT_REC.PRODUCT_ID || ' ' || PRDCT_REC.PRODUCT_NAME || ' - ' ||    PRDCT_REC.UNIT_PRICE );
END LOOP;

CLOSE cust_cursor;
END;
/
Example for Strong Type:
DECLARE
TYPE CUST_CO IS REF CURSOR RETURN CUSTOMERS%ROWTYPE;
CUST_CO_VAR CUST_CO;
CUST_REC CUSTOMERS%ROWTYPE;
BEGIN
OPEN CUST_CO_VAR FOR SELECT * FROM CUSTOMERS where CUSTOMER_ID <=10;
LOOP
FETCH CUST_CO_VAR INTO CUST_REC;
exit when CUST_CO_VAR%NOTFOUND;
dbms_output.put_line(CUST_REC.CUSTOMER_ID || ' ' || CUST_REC.EMAIL_ADDRESS || ' - ' ||    CUST_REC.FULL_NAME );
END LOOP;
CLOSE CUST_CO_VAR;
END;
/

SYS_REFCURSOR as Argument in PL/SQL Subprogram:

This the best way to provide sql query result as output i.e. passing out parameter as SYS_REFCURSOR. Refer below sample code.

CREATE OR REPLACE PROCEDURE get_customer_orders(
    p_cust_id IN CUSTOMERS.CUSTOMER_ID%TYPE,
    p_out_cursor OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_out_cursor FOR 
  SELECT CUSTOMERS.FULL_NAME , CUSTOMERS.EMAIL_ADDRESS, PRODUCTS.PRODUCT_NAME 
  FROM orders JOIN ORDER_ITEMS ON orders.ORDER_ID= ORDER_ITEMS.ORDER_ID 
  JOIN PRODUCTS ON ORDER_ITEMS.PRODUCT_ID=PRODUCTS.PRODUCT_ID 
  JOIN CUSTOMERS ON orders.CUSTOMER_ID=CUSTOMERS.CUSTOMER_ID 
  WHERE orders.CUSTOMER_ID = p_cust_id;
END get_customer_orders;
/
DECLARE
  lc_cursor  SYS_REFCURSOR;
  lv_custname   CUSTOMERS.FULL_NAME%TYPE;
  lv_custemail   CUSTOMERS.EMAIL_ADDRESS%TYPE;
  lv_prodcutname   PRODUCTS.PRODUCT_NAME%TYPE;
BEGIN
  get_customer_orders (5,lc_cursor);      
  LOOP 
    FETCH lc_cursor INTO  lv_custname,lv_custemail, lv_prodcutname;
    EXIT WHEN lc_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(lv_custname || ' - ' || lv_custemail || ' - '|| lv_prodcutname );
  END LOOP;
  CLOSE lc_cursor;
END;

Oracle Schema used in blog: Customer-Orders Sample Schema | Oracle 12c


Thanks!

Happy Learning! Your feedback would be appreciated!

One thought on “PL/SQL Cursor Variable – Ref Cursors (Strong & Weak Type)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s