Nested loops in Oracle jump out of the internal loop body instance

The code is as follows	Copy Code
PROCEDURE Test (p_msg out VARCHAR2) is Begin <<illoop>> For I in 1. Ten loop <<jloop>> for j in 1. 5 loop Dbms_output.put_line (' I: ' | | | ' J: ' | | j); EXIT Jloop when j = 3; End Loop Jloop; End Loop Illoop; End

Instance 2

The code is as follows	Copy Code
Declare result int; Begin <<outer>> For I in 1..100 loop <<inner>> For j in 1..100 Loop Result:=i*j; Exit outer when result=1000; Exit when result=500; End loop inner; Dbms_output.put_line (result); End loop outer; Dbms_output.put_line (result); End

The above explains

①i=1
J from 1 to 100,result is not equal to 500, so the internal loop is fully implemented. That is, the result before the next outer loop should be 1*100 (i=1,j=100).
② Next: i=2, similar to i=1,result<500, so the final print out result=200.
③ all the time: The inner loop was executed when the i=5,result=500 was finished.
④ when i=6,7,8,9,i*j will never be equal to 500, so each internal loop is executed to print result, so that is equal to 600,700,800,900.
⑤ when i=10, internal loop j=50, result=500, this time (exit when result=500), so out of this cycle, and then execute the next outer loop i=11.
⑥ ... Wait a minute. You can think about what I would do to make the inner loop jump ahead of time?

Yes, it's smart, when I'm a divisor of 500, the inner loop produces a result equal to 500, so it jumps out of the loop ahead of time.

Similarly, when i*j=1000 out of the outer loop, that is, the whole cycle. The earliest occurrence should be i=10,j=100, but i=10 time, wait for J to take to 100 of the time result will fetch (I=10,J=50), so this is pass away.
This situation also requires I is 1000 of the divisors, because 500<1000, when I was also 500 and 1000 of the time, not equal to result=1000,result=500 time to jump out of the inner loop, result is less than 1000.
So what we're looking for is the smallest of the natural numbers that divide 1000 and not divide 500.
So, i=40!
Then, when the i=40,j=25 will jump out of the loop. i=39,j=100 when the last print.

In fact, nested loops are loops inside the loop, this is simply a circular control logo <<outer>> and <<inner>> (put in for the front, used to mark which loop, the inside name can be casually up), so that We can go through the exit Outer/inner ... Statement to control which loop to jump out of.

Oracle's Looping tutorial

Oracle Nested Loops (Nested Loops, NL):

A If the driving row source (external table) is smaller and has a unique index on inner row source (internal table), or has a highly selective, non unique index, this method can be more efficient.

b) One advantage of NESTED loops that there are no other connection methods is that you can return the rows that have already been connected without having to wait for all the connection operations to be processed before returning the data, which enables fast response times.

Hash joins (hash join, HJ):

A This method was introduced later in Oracle7, using the more advanced connection theory, generally speaking, its efficiency should be better than the other 2 kinds of connections, but this connection can only be used in the CBO optimizer, and need to set the appropriate hash_area_size parameters, in order to achieve better performance.

b It is relatively efficient to connect between the 2 large row source and to achieve greater efficiency when a row source is smaller.

c) can only be used in an equivalent connection

Flute Descartes Product (Cartesian product)

When two row source is connected, but they have no associated condition, they do the Descartes product in two row source, which is usually caused by writing code omissions (i.e., programmers forgetting to write associated conditions). The Cartesian product is that each row of one table matches all the rows in the other table in turn. In exceptional cases we can use the Descartes product, as in a star connection, in addition, we try to use the flute Descartes product