Cursor in PL/SQL- Part 2

   In the previous  post Cursor in PLSQL-Part 1 we have discussed about the basics of PL/SQL Cursor. Let us discuss more about the Cursors in this post.

Cursor FOR-LOOP:
   Using cursor FOR LOOP you can process the records in the cursor without using OPEN and Fetch the Cursor. The Cursor FOR LOOP handles open and fetch the records from the cursor.

General Syntax for cursor FOR-LOOP :
DECLARE --Cursor declaration Begin FOR var IN cursor_name LOOP --Process the records fetched in the Cursor END LOOP; EXCEPTION END;

You can refer the below example for Cursor FOR LOOP.

 DECLARE CURSOR order_details_cur IS SELECT ooha.order_number, TRUNC(ooha.creation_date) creation_date, oola.ordered_item, oola.line_number||'.'||oola.shipment_number Line_number FROM oe_order_headers_all ooha, oe_order_lines_all oola WHERE ooha.header_id=oola.header_id AND trunc(ooha.creation_date) BETWEEN '1-Jan-2014' AND '30-May-2015'; BEGIN DBMS_OUTPUT.PUT_LINE('Start of Cursor for Loop'); DBMS_OUTPUT.PUT_LINE('ORDER_NUMBER CREATION_DATE ORDERED_ITEM LINE_NUMBER'); DBMS_OUTPUT.PUT_LINE('********************************************************************'); FOR ord_loop IN order_details_cur LOOP DBMS_OUTPUT.PUT_LINE(ord_loop.ORDER_NUMBER||' '||ord_loop.creation_date||' '||ord_loop.ordered_item||' '||ord_loop.LINE_NUMBER); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error Occured: '||SQLERRM); END;

Output:

Parameterized Cursor:
  You can also pass the parameters to the cursor and while calling you need to call the cursor with parameter value.

General Syntax:
DECLARE CURSOR cursor_name(par1 datatype[par2 datatype…]) IS Select statement; BEGIN FOR var IN cursor_name(parameter_list) LOOP --Process the records fetched in the Cursor END LOOP; EXCEPTION END;

You can go through the below example for parameterized cursor.
DECLARE CURSOR order_details_cur (from_date DATE,date_to DATE) IS SELECT ooha.order_number, TRUNC(ooha.creation_date) creation_date, oola.ordered_item, oola.line_number||'.'||oola.shipment_number Line_number FROM oe_order_headers_all ooha, oe_order_lines_all oola WHERE ooha.header_id=oola.header_id AND trunc(ooha.creation_date) BETWEEN from_date AND date_to; from_date DATE :='1-Jan-2014'; date_to DATE :='30 -May-2015'; BEGIN DBMS_OUTPUT.PUT_LINE('Start of Cursor for Loop'); DBMS_OUTPUT.PUT_LINE('ORDER_NUMBER CREATION_DATE ORDERED_ITEM LINE_NUMBER'); DBMS_OUTPUT.PUT_LINE('********************************************************************'); FOR ord_loop IN order_details_cur(from_date,date_to) LOOP DBMS_OUTPUT.PUT_LINE(ord_loop.ORDER_NUMBER||' '||ord_loop.creation_date||' '||ord_loop.ordered_item||' '||ord_loop.LINE_NUMBER); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error Occured: '||SQLERRM); END;

Output:

Cursor FOR LOOP without declaring a Cursor:
    You can use the cursor FOR LOOP without declaring a cursor in DECLARE block. While using the for loop you can use the select statement.

General Syntax:
BEGIN FOR var IN (Select Statement) LOOP --Process the records fetched in the Cursor END LOOP; EXCEPTION END;
You can go through the below example.
DECLARE from_date DATE :='1-Jan-2014'; date_to DATE :='30 -May-2015'; BEGIN DBMS_OUTPUT.PUT_LINE('Start of cursor for loop without declaring the cursor'); DBMS_OUTPUT.PUT_LINE('ORDER_NUMBER CREATION_DATE ORDERED_ITEM LINE_NUMBER'); DBMS_OUTPUT.PUT_LINE('********************************************************************'); FOR ord_loop IN (SELECT ooha.order_number, TRUNC(ooha.creation_date) creation_date, oola.ordered_item, oola.line_number||'.'||oola.shipment_number Line_number FROM oe_order_headers_all ooha, oe_order_lines_all oola WHERE ooha.header_id=oola.header_id AND trunc(ooha.creation_date) BETWEEN from_date AND date_to) LOOP DBMS_OUTPUT.PUT_LINE(ord_loop.ORDER_NUMBER||' '||ord_loop.creation_date||' '||ord_loop.ordered_item||' '||ord_loop.LINE_NUMBER); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error Occured: '||SQLERRM); END;

Output: