Cursor FOR Loops

Without defining a cursor explicitly we can substitute the subquery inside a FOR statement.

e.g. 
SET SERVEROUTPUT ON
BEGIN
   FOR trip_record IN (SELECT bt_id_pk, bt_duration
                      FROM business_trips) LOOP
      -- implicit open/fetch occurs
      IF trip_record.bt_duration = 1 THEN
        DBMS_OUTPUT_LINE ('trip Number ' || trip_record.bt_id_pk
                        || ' is a one day trip');
      END IF;
   END LOOP; -- implicit CLOSE occurs
END;
/

Cursor with Parameters

DECLARE
   v_hotel     business_trips.bt_hotel_id%TYPE := 12;
   v_duration  business_trips.bt_duration.%TYPE := 3;
   CURSOR trip_cursor(p_hotel NUMBER, p_duration VARCHAR2) IS
     SELECT ...

--Then to open the cursor either

OPEN trip_cursor (12, 3);

--or using the variables:

OPEN trip_cursor (v_hotel, v_duration);

--Alternatively open the cursor implicitly as part of a Cursor FOR loop
pass the parameters like this...

BEGIN
   FOR trip_record IN trip_cursor(12, 3) LOOP ...

Related:

PL/SQL looping commands EXIT - GOTO -

 
Copyright © 1999-2024 SS64.com
Some rights reserved