For and while loops in Oracle

Conditional loops can be implemented in two ways.

1. for variable in start value... end value loop end loop;

2. while condition loop end loop;

Loop usage:

I. X: = 100;

LOOP

X: = X + 10;

If x> 1000 THEN

EXIT;

End if end loop;

Y: = X;

Ii. X: = 100;

LOOP

X: = X + 10;

Exit when x> 1000;

X: = X + 10;

End loop;

Y: = X;

There is also a GOTO usage, which will be available in later processes to generate error logs.

The following is a very simple process to familiarize yourself with loops!

Create or replace procedure count_number is

/*

Function Description: calculates the sum of 1 to 100.

T value is sum

*/

I number (10 );

T number (10 );

Begin

/*

I: = 1;

T: = 0;

-- Implement with while + loop

/* Begin

While I <= 100 loop

T: = t + I;

I: = I + 1;

End loop;

End;

*/

I: = 1;

T: = 0;

-- Implement with

Begin

For I in 1 .. 100 loop

T: = t + I;

End loop;

End;

End count_number;

For Loop:

[PL/SQL] Replace Cursor with For Loop

Cursor for Loop

FOR employee_rec in c1 --- direct use of employee_rec without prior definition

LOOP

Total_val: = total_val + employee_rec.monthly_income;

End loop;

When Using cursor for Loop, do not manually open cursor close cursor

Application:

Begin

FOR emm IN (SELECT ro_site, ns_site, product_line, wh_type

FROM eis_hq_invhl_mail_data

WHERE report_type = 'detail ')

LOOP

DBMS_OUTPUT.put_line (emm. product_line );

Eis_hq_invhl_pkg.make_mail_detailed_data

(P_ro_site =>
Emm. ro_site,

P_ns_site =>
Emm. ns_site,

P_product_line =>
Emm. product_line,

P_wh_type =>
Emm. wh_type,

P_current_day =>
To_date ('2017-11-07 ', 'yyyy-MM-dd '));

End loop;

End;

Stored Procedure DIY2-cursor and loop

While loop:

WHILE (I <= CEIL (LENGTH (p_clob_data)/4000 ))

LOOP

V_clob_data: = TRIM (SUBSTR (p_clob_data,

I X 4000,

4000 ));

DBMS_OUTPUT.put_line (SUBSTR (v_clob_data,

0,

255 ));

I: = I + 1;

End loop;