SQL Record-plsql loop

PL/SQL Loops

There may be a situation when you need to execute a block of code several times. In general, the statement order executes: The first statement of a function, first executed, then the second ... Wait a minute.

Programming languages provide a variety of control structures that allow for more complex execution paths.

The loop statement allows us to execute a statement multiple times or a group, following the general form of a looping statement in most programming languages:

PL/SQL provides the requirements for looping through the following types of processing loops. Click on the link below to see more information.

Loop Type	Description
PL/SQL Foundation loops	In this loop structure, the statement sequence is enclosed between the loop and end loop statements. In each iteration, the statement sequence is executed, and then the control is resumed at the top of the loop
PL/SQL While loop	Repeating a statement or a group, and given a condition that is true, before it tests the condition to execute the loop body
PL/SQL for loop	Execute statement sequence multiple and shorthand code to manage the loop variable
PL/SQL Inline loops	can use any of the other basic loops in one or more loops, while or looping

Mark a PL/SQL loop

PL/SQL loops can be marked. tags should be enclosed in double-angle brackets (<< and >>) and appear at the beginning of the loop statement. The label name can also appear at the end of the loop statement. You can use the label to exit the exit statement from the loop.

The following procedure illustrates this concept:

DECLARE   I number (1);   J Number (1); BEGIN   << outer_loop >> for   i in 1..3 loop      << inner_loop >> for      J in 1..3 Loop         Dbms_output.put_line (' I is: ' | | | | | ' and J is: ' | | j);      END loop Inner_loop;   END Loop Outer_loop; end;/

When the above code is executed at the SQL prompt, it produces the following results:

Loop control Statements

The loop control statement changes its normal sequential execution. When execution is out of scope, all objects created within that scope are automatically destroyed.

PL/SQL supports the following control statements. The tag loop also takes out out-of-loop control. Click on the links below to view their details.

Control Statements	Description
Exit statement	Returns are completed immediately after the Exit statement end Loop, and control is entered into the statement
Continue statements	Causes the loop to skip the remainder of its principal and immediately re-test its usage statement before
Goto statement	Control transfers the statement to the label. Although it is not recommended to use a goto statement in a program

PL/SQL Basic Loop statement

The basic loop structure encapsulates the sequence of statements between the loop and end loop statements. With each iteration, the statement order is executed, and then the process is controlled at the top of the loop.

Grammar:

The syntax for a basic loop of the PL/SQL programming language is:

LOOP  Sequence of statements; END LOOP;

Here, the sequence of the declaration (S) can be a single statement or a block of statements. The exit or exit when statement needs to exit the loop.

Example:

DECLARE   x Number: = 10; BEGIN   LOOP      dbms_output.put_line (x);      X: = x + ten;      IF x > then         exit;      END IF;   END LOOP;   --after exit, control resumes here   dbms_output.put_line (' After exit X is: ' | | x); end;/

When the above code is executed at the SQL prompt, it produces the following results:

1020304050After Exit x Is:60pl/sql procedure successfully completed.

You can use the exit when statement instead of the exit statement:

DECLARE   x Number: = 10; BEGIN   LOOP      dbms_output.put_line (x);      X: = x + ten;      Exit when x >;   END LOOP;   --after exit, control resumes here   dbms_output.put_line (' After exit X is: ' | | x); end;/

When the above code is executed at the SQL prompt, it produces the following results:

1020304050After Exit x Is:60pl/sql procedure successfully completed.

PL/SQL While loop statement

While loop statements are in the PL/SQL programming language, the target statement is executed multiple times as long as the given condition is true.

Grammar:

while condition loop   sequence_of_statementsEND loop;   

Example:

DECLAREA number(2) := 10;begin while a < 20 LOOP dbms_output. ( ' value of a: '  | |  A);  a := a +1; end Loop;end;                 

When the above code is executed at the SQL prompt, it produces the following results:

Value of A:10value of A:11value of A:12value of A:13value of A:14value of A:15value of A:16value of A:17value of a : 18value of A:19pl/sql procedure successfully completed.

PL/SQL for loop statements

For loop repeating control structure, you can effectively write a specific number of cycles that need to be executed.

Grammar:

For in.. final_value LOOP sequence_of_statements;  END LOOP;      

Here is the loop that controls the flow in a process:

• The initial step is first executed and only once. This step can declare and initialize any loop control variables.

• Then, Condition,initial_value. Final_value is calculated. If true, the loop body is executed. If False, the loop body is not executed, just after the for loop traffic control jumps to the next statement.

• After the execution of the loop body, the value of the counter variable is increased or decreased.

• The condition is now recalculated. If true, the loop executes the process repeatedly (loop the body, then increments the step and then again the condition). After the condition is false, the for-loop terminates.

The following are some of the features of the PL/SQL for loop:

• Initial_value and loop variables or calculators final_value can be literal values, variables or expressions, but the result must be computed as numbers. Otherwise, PL/SQL throws a pre-defined exception Value_error.

• Initial_value do not have to be 1; However, the loop counter increment (or decrement) must be 1.

• PL/SQL allows dynamic determination of the loop range at run time.

Example:

declare a number (2begin for a in 10 .  20 LOOP dbms_output. ( ' value of a: '  | |  A);  end Loop;end;                

When the above code is executed at the SQL prompt, it produces the following results:

Value of A:10value of A:11value of A:12value of A:13value of A:14value of A:15value of A:16value of A:17value of a : 18value of A:19value of A:20pl/sql procedure successfully completed.

Invert A For Loop statement

By default, the iteration progresses from the initial value to the final value, largely from the upper bound to the lower bound. You can reverse the order by using the reverse keyword. In this case, the iteration progresses in other ways. The loop counter decrements after each iteration.

However, the bounds of the range must be written in ascending (non-descending) order. The following procedure illustrates this point:

DECLAREA number (2)  ;begin for a in REVERSE 10 .  20 LOOP dbms_output. ( ' value of a: '  | |  A);  end Loop;end;                

When the above code is executed at the SQL prompt, it produces the following results:

Value of A:20value of A:19value of A:18value of A:17value of A:16value of A:15value of A:14value of A:13value of a : 12value of A:11value of A:10pl/sql procedure successfully completed.

PL/SQL Nested loops

PL/SQL allows you to use one loop to nest another loop. The following shows a few examples to illustrate this concept.

The syntax for nesting basic loop statements in PL/SQL is as follows:

Loop of    statements1   loop of       statements2   END loop;  END LOOP;         

The syntax for nesting a for statement in the PL/SQL loop is as follows:

For in.. For in .. END LOOP;  END LOOP;          

The syntax for nesting while loop statements in Pascal is as follows:

While Condition1 loop   sequence_of_statements1 while    condition2 Loop      sequence_of_ Statements2   END LOOP;  END LOOP;        

Example:

The following program uses a basic nesting loop to find the prime number in 2-100:

DECLAREI number(3);J Number(3);BEGINI:= 2;LOOP J:= 2;LOOPExit When ((MoD(I,J) = 0) Or (J=I));J:=J+1; ENDLOOP; IF (J=I) ThenDbms_output.Put_Line (i | |   ' is prime ' ); end if; I := I + 1  exit when I = 50;end Loop;end;                

When the above code is executed at the SQL prompt, it produces the following results:

2 is Prime3 are Prime5 is Prime7 are Prime11 is Prime13 are Prime17 is, PRIME19 is are Prime23 is, is to be prime29 is, Prime31 is Prime41 is prime43 are Prime47 is Primepl/sql procedure successfully completed.

SQL Record-plsql loop