Oracle:if statement loop loop cursor usage (i)

1 overview

Most of the time when designing pl-sql programs, we follow these steps:
Open cursor, start loop, fetch value from cursor, check that row is returned----close the cursor with a closed loop,

2 Format: Copy content to Clipboard/program code
Cursor < cursor name > is
< cursor name >%ROWTYPE;
BEGIN
OPEN < cursor name >
LOOP
FETCH < cursor name > into;
EXIT when < cursor name >%NOTFOUND;

< other code to execute >
END LOOP;
CLOSE < cursor name >;
END < stored procedure name >;
/

3 Code

code example: copying content to the Clipboard Program code
TRUNCATE TABLE loop_test;

DECLARE

CURSOR Ao_cur is
Select SUBSTR (object_name,1,5) firstfive
From ALL_OBJS
Where SUBSTR (object_name,1,5) between ' A ' and ' M ';

Ao_rec Ao_cur%rowtype;

BEGIN
OPEN ao_cur;
LOOP
FETCH ao_cur into Ao_rec;
EXIT when Ao_cur%notfound;

Insert into Loop_test VALUES (ao_rec.firstfive);
END LOOP;
COMMIT;
CLOSE ao_cur;
END;
/

Select COUNT (*) from loop_test;

[Code]

Demo
[Code]
Fetch Demo

Create TABLE T1 (tcol number);

Create TABLE T2 (c number);

BEGIN
For I in 1..500 LOOP
Insert into T1 VALUES (i);
END LOOP;
END;
/

Select COUNT (*) from T1;

COMMIT;
Create or REPLACE FUNCTION p (c_in number) RETURN number is

PRAGMA autonomous_transaction;

BEGIN
--Every time this was called, write a row to table T2
Insert into T2 VALUES (c_in);
COMMIT;
RETURN c_in;
END p;
/
DECLARE
BEGIN
/* Open up a cursor for loop, also selecting
* the "P" function which would write rows to
* T2 for every row fetched from T1. */

For Crec in (Select Tcol, P (tcol) from T1) LOOP
--Break out of the loop immediately
EXIT;
END LOOP;
END;
/

Select COUNT (*) from T2;

Note:%rowtype can also be defined with a cursor name, but the cursor must be declared first, so the above example takes the cursor declaration shown


Cursor for Loop

Format:

copy content to clipboard Program code
Create or REPLACE PROCEDURE < stored procedure name > is

Cursor < cursor name > is


BEGIN
For in < cursor name >
LOOP
< other code to execute >
END LOOP;
END < stored procedure name >;
/

Code:

copy content to clipboard Program code
TRUNCATE TABLE loop_test;

DECLARE
CURSOR Ao_cur is
Select SUBSTR (object_name,1,5) firstfive
From ALL_OBJS
Where SUBSTR (object_name,1,5) between ' N ' and ' W ';
BEGIN
For Ao_rec in Ao_cur LOOP
Insert into Loop_test VALUES (ao_rec.firstfive);
END LOOP;
COMMIT;
END;
/

Select COUNT (*) from loop_test;

Curcor for loop is an implicit cursor declaration.
Format:

copy content to clipboard Program code
BEGIN
For in
LOOP
< other code to execute >
END LOOP;
END < stored procedure name >;
/

Demo

copy content to clipboard Program code
TRUNCATE TABLE loop_test;

BEGIN
For Ao_rec in (
Select SUBSTR (object_name,1,5) firstfive
From ALL_OBJS
Where SUBSTR (object_name,1,5) between ' N ' and ' Z ')
LOOP
Insert into Loop_test VALUES (ao_rec.firstfive);
END LOOP;
COMMIT;
END;
/

Select COUNT (*) from loop_test;

Cursor Loop with Where current of Clause

Format:

copy content to clipboard Program code
Create or REPLACE PROCEDURE < stored procedure name > is



BEGIN
For in (< cursor name >)
LOOP
< other code to execute >
Update
SET =
Where Current of < cursor name >
END LOOP;
END < stored procedure name >;
/

Demo:

copy content to clipboard program code     
Create TABLE Test (
Pid  number (3),
Cash  number (10,2));

Insert into Test VALUES (100, 10000.73);
Insert into Test VALUES (200  25000.26);
Insert into Test VALUES (300, 30000.11);
Insert into Test VALUES (400, 45000.99);
Insert into Test VALUES (500, 50000.08);
COMMIT;
Create or REPLACE PROCEDURE WCO is

CURSOR x_cur are
Select pid, cash
from Test
Where Cash < 35000
for Update;

BEGIN
  for x_rec in x_cur LOOP
    update test
     set cash = floor (Cash)
    where Current of x_cur;
  end LOOP;
  COMMIT;
END WCO;
/
exec WCO;

Select * from test;

Nested cursor loops Nested cursor Loops

Test Sample Table

copy content to clipboard Program code
Create TABLE Airplanes (
program_id VARCHAR2 (3),
Line_number Number (10),
customer_id VARCHAR2 (4),
Order_date date,
Delivered_date date)
PCTFREE 0;

Create INDEX Programid_idx
On airplanes (program_id)
PCTFREE 0;

Create TABLE Parts (
program_id VARCHAR2 (3),
Line_type VARCHAR2 (4),
Part_type VARCHAR2 (10),
Quantity Number (3));

Create TABLE Ap_parts as
Select a.customer_id, P.part_type, p.quantity
From airplanes A, parts p
Where a.program_id = p.program_id
and 1=2;

Pl-sql Example

copy content to clipboardProgram code
DECLARE
ProgID Airplanes.program_id%type;
Lineno Airplanes.line_number%type;
CustID airplanes.customer_id%type: = ' AAL ';
Orddate Airplanes.order_date%type;
Deldate Airplanes.delivered_date%type;
BEGIN
For I in 1.. 5 LOOP
Select DECODE (i, 1, ' 737 ', 2, ' 747 ', 3, ' 757 ', 4, ' 767 ', 5, ' 777 ')
into ProgID
from dual;

For Lineno in 1..250 LOOP
Select DECODE (CustID, ' AAL ', ' dal ', ' dal ', ' sal ', ' sal ', ' ILC ',
' ILC ', ' SWA ', ' SWA ', ' NWO ', ' NWO ', ' AAL ')
Into CustID
from dual;

IF ProgID = ' 737 ' Then
Orddate: = Sysdate + Lineno;
Deldate: = orddate + Lineno + 100;
elsif ProgID = ' 747 ' Then
Orddate: = Sysdate + lineno+17;
Deldate: = orddate + Lineno + 302;
elsif ProgID = ' 757 ' Then
Orddate: = Sysdate + lineno+22;
Deldate: = orddate + Lineno + 202;
elsif ProgID = ' 767 ' Then
Orddate: = Sysdate + lineno+43;
Deldate: = orddate + Lineno + 189;
elsif ProgID = ' 777 ' Then
Orddate: = Sysdate + lineno-69;
Deldate: = orddate + Lineno + 299;
END IF;

Insert into airplanes
(program_id, Line_number, customer_id, Order_date,
Delivered_date)
VALUES
(ProgID, Lineno, CustID, Orddate, deldate);
END LOOP;
END LOOP;
COMMIT;
END Load_airplanes;
/

copy content to clipboardProgram code
Load Airplane Parts BEGIN
Insert into parts VALUES (' 737 ', ' even ', ' Wing ', 2);
Insert into parts VALUES (' 747 ', ' even ', ' Wing ', 2);
Insert into parts VALUES (' 757 ', ' even ', ' Wing ', 2);
Insert into parts VALUES (' 767 ', ' even ', ' Wing ', 2);
Insert into parts VALUES (' 777 ', ' even ', ' Wing ', 2);
Insert into parts VALUES (' 737 ', ' ODD ', ' Wing ', 2);
Insert into parts VALUES (' 747 ', ' odd ', ' Wing ', 2);
Insert into parts VALUES (' 757 ', ' Odd ', ' Wing ', 2);
Insert into parts VALUES (' 767 ', ' Odd ', ' Wing ', 2);
Insert into parts VALUES (' 777 ', ' Odd ', ' Wing ', 2);
Insert into parts VALUES (' 737 ', ' even ', ' galley ', 1);
Insert into parts VALUES (' 747 ', ' even ', ' galley ', 3);
Insert into parts VALUES (' 757 ', ' even ', ' galley ', 3);
Insert into parts VALUES (' 767 ', ' even ', ' galley ', 2);
Insert into parts VALUES (' 777 ', ' even ', ' galley ', 3);
Insert into parts VALUES (' 737 ', ' ODD ', ' Galley ', 2);
Insert into parts VALUES (' 747 ', ' odd ', ' galley ', 4);
Insert into parts VALUES (' 757 ', ' Odd ', ' Galley ', 3);
Insert into parts VALUES (' 767 ', ' ODd ', ' Galley ', 4);
Insert into parts VALUES (' 777 ', ' OdD ', ' Galley ', 4);
Insert into parts VALUES (' 737 ', ' even ', ' Tire ', 10);
Insert into parts VALUES (' 747 ', ' even ', ' Tire ', 18);
Insert into parts VALUES (' 757 ', ' even ', ' Tire ', 12);
Insert into parts VALUES (' 767 ', ' even ', ' Tire ', 14);
Insert into parts VALUES (' 777 ', ' even ', ' Tire ', 16);
Insert into parts VALUES (' 737 ', ' ODD ', ' Tire ', 14);
Insert into parts VALUES (' 747 ', ' Odd ', ' Tire ', 20);
Insert into parts VALUES (' 757 ', ' Odd ', ' Tire ', 14);
Insert into parts VALUES (' 767 ', ' Odd ', ' Tire ', 16);
Insert into parts VALUES (' 777 ', ' Odd ', ' Tire ', 18);
Insert into parts VALUES (' 737 ', ' even ', ' seats ', 105);
Insert into parts VALUES (' 747 ', ' even ', ' seats ', 255);
Insert into parts VALUES (' 757 ', ' even ', ' seats ', 140);
Insert into parts VALUES (' 767 ', ' even ', ' seats ', 200);
Insert into parts VALUES (' 777 ', ' even ', ' seats ', 210);
Insert into parts VALUES (' 737 ', ' ODD ', ' seats ', 137);
Insert into parts VALUES (' 747 ', ' Odd ', ' seats ', 20);
Insert into parts VALUES (' 757 ', ' Odd ', ' seats ', 166);
Insert into parts VALUES (' 767 ', ' Odd ', ' seats ', 345);
Insert into parts VALUES (' 777 ', ' Odd ', ' seats ', 267);
COMMIT;
END;
/
[Code]


Nesting of fixed cursors

Nested Loops with Static Cursors

DeMo:
[Code]

Create or REPLACE PROCEDURE Nested_loop is

CURSOR A_cur is
Select program_id, Line_number, customer_id
from airplanes;

A_rec A_cur%rowtype;

CURSOR P_cur is
Select Part_type, Quantity
From parts
Where program_id = a_rec.program_id
and UPPER (Line_type) =decode (MOD (a_rec.line_number,2), 0, ' even ', ' ODD ');

P_rec P_cur%rowtype;

BEGIN
OPEN a_cur;
LOOP
FETCH a_cur into A_rec;
EXIT when A_cur%notfound;

OPEN p_cur;
LOOP
FETCH p_cur into P_rec;
EXIT when P_cur%notfound;
Insert into Ap_parts
(customer_id, Part_type, quantity)
VALUES
(a_rec.customer_id, P_rec.part_type, p_rec.quantity);
END LOOP;
CLOSE p_cur;

END LOOP;
COMMIT;
CLOSE a_cur;
END Nested_loop;
/

Nesting a loop that uses Bluk collect to get a dataset

Nested Loops with Bulk Collect



This demo is intended to show the data in a cursor in a nested loop that is copied through bulk collection

Demo

copy content to clipboardProgram code
Create or REPLACE PROCEDURE bulk_nested is
CURSOR A_cur is
Select program_id, Line_number, customer_id
from airplanes;

TYPE Ap_array is TABLE of Airplanes.program_id%type
INDEX by Binary_integer;
Ap_rec Ap_array;

TYPE Al_array is TABLE of Airplanes.line_number%type
INDEX by Binary_integer;
Al_rec Al_array;

TYPE Ac_array is TABLE of Airplanes.customer_id%type
INDEX by Binary_integer;
Ac_rec Ac_array;

TYPE Pp_array is TABLE of Parts.part_type%type
INDEX by Binary_integer;
Pp_rec Pp_array;

TYPE Pq_array is TABLE of Parts.quantity%type
INDEX by Binary_integer;
Pq_rec Pq_array;
BEGIN
OPEN a_cur;
LOOP
FETCH a_cur BULK COLLECT into Ap_rec, Al_rec, Ac_rec LIMIT 500;

For I in 1.. Ap_rec. COUNT LOOP
DECLARE
CURSOR P_cur is
Select Part_type, Quantity
From parts
Where program_id = Ap_rec (i)
and UPPER (Line_type) =decode (MOD (Al_rec (i), 2), 0, ' even ', ' ODD ');
BEGIN
OPEN p_cur;
LOOP
FETCH p_cur BULK COLLECT into Pp_rec, Pq_rec;

FORALL J in Pp_rec. First: Pp_rec. Last
Insert into Ap_parts
(customer_id, Part_type, quantity)
VALUES
(Ap_rec (i), Pp_rec (j), Pq_rec (j));

EXIT when P_cur%notfound;
END LOOP;
CLOSE p_cur;
END;
END LOOP;

EXIT when A_cur%notfound;
END LOOP;
COMMIT;
CLOSE a_cur;
END bulk_nested;
/

Oracle:if statement loop loop cursor usage (i)