Oracle Learning-PL loops, triggers in SQL stored procedures

PL SPL provides 3 different types of cyclic structures

--Example: Index loop_counter starting from 1, ending at 10, looping total 10 times forLoop_counterinch 1..TenLOOP ... Executable statement ...ENDLOOP;--index Loop_counter starting from 10, to 1 end, the loop is executed 10 times; forLoop_counterinch REVERSE 1..TenLOOP ... Executable statement ...ENDLOOP;--the execution scope of a loop depends on the value of the variable or expression: forCalc_indexinchStart_period_number. LEAST (End_period_number, current_period) LOOP ... Executable statement ...ENDLOOP;--In This example, the number of times the loop is executed is determined at run time. The boundary value is evaluated only once before the loop starts, and then it is valid for the entire life of the cycle. 

• Simple loop or infinite loop
• For loops (numeric loops and cursor loops)
• While loop

Simple loop : The Loop keyword begins, ending with the end Loop statement, which exits the loop (or throws the exception) by the exit, exit, or return of the loop body

"Exit when followed by a Boolean expression, without exiting the condition, will become an infinite loop"

For Loop : To use a numeric for loop , you need to specify the integer value to start the loop and the end integer values, PL/SQL will do the rest, iterate over each intermediate value, and then end the loop;

"Requires minimal code and a predetermined number of times the loop body needs to be executed, and this range is given between the for and loop two keywords. If you want to execute a piece of code for a limited number of times and don't want to end the loop prematurely, you can use the numeric for loop "

A cursor-type for loop uses the same basic structure, but needs to provide an explicit cursor or SELECT statement instead of the upper and lower bounds of the integer used by the numeric for loop.

"If you need to remove each record of the cursor and then process it sequentially, you can use the cursor for loop"

While loop : While loops are very similar to simple loops, one key difference is that while loops check for termination conditions before each loop.

"Use a while loop for the following: A. You are not sure how many times you will cycle; b. You want to terminate the loop by condition; c. The loop body is not required to be executed"

Each structure consists of two parts: a cyclic boundary and a loop body.

In the simple loop, when to use exit when, what time is the split exit:

1. If there is only one conditional expression that determines whether the loop should end, it is best to use exit when this is the case

2. If you have multiple exit conditions, or if you want to set a return value based on a different exit Bar statement, use the If or case should be more appropriate to use the Exit statement in one or more clauses of these statements.

"REPEAT UNTIL" PL SQL No

in the loop , you can pause (hibernate) Dbms_lock.sleep (10) properly; --Don't do anything in 10 seconds.

PL SQL does not provide a syntax that can specify a special "stepping" of the progressive progression. In the various variants of the numeric for loop for PL SQL, progressive strides always move forward or backward in 1 units.

If you want a loop body to be able to be progressive in a non-trivial way (that is, the growth stride is not 1), you have to write some clever code, here's a simple example: using simple multiplication in a loop, the recursion takes only half the number of times: (Not quite)

 for inch 1  - LOOP     calc_values (even_number*2); END LOOP;

Looping Tags: you can give a loop name by label, format: <<label_name>>, loop the label immediately before the loop statement

<<All_emps>> forEmp_recinchemp_curloop ...ENDLOOP;--tags can also be followed by end loop reserved words, which are optional<<Year_loop>> whileYear_number<= 1995LOOP<<Month_loop>>     forMonth_numberinch 1.. ALOOP ...ENDLOOP Month_loop; Year_number:=Year_number+ 1;ENDLOOP Year_loop;

CONTINUE: Use this statement to exit from the iteration of the loop and immediately proceed to the next iteration. (Unconditional continue and conditional continue when)

Oracle Learning-PL loops, triggers in SQL stored procedures