Nested loop in oracle jumps out of the internal loop body instance

In many databases, there is a simple syntax such as loop, which is no exception in oracle. Let's take a look at the nested loop in El and explain it out of the internal loop body with instance 2.

The Code is as follows:	Copy code
PROCEDURE test (P_MSG out VARCHAR2) is Begin <Illoop> For I in 1 .. 10 loop <Jloop> For j in 1 .. 5 loop Dbms_output.put_line ('I:' | 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 description

① I = 1
If j is obtained from 1 to 100, the result is not equal to 500, so the internal loop is fully executed. That is, the result before the next External Loop should be 1*100 (I = 1, j = 100 ).
② Next: I = 2, similar to I = 1, result <500, so the final result is 200.
③ Until: I = 5, result = 500, the internal loop is also executed.
④ When I = 6, 7, 8, 9, I * j will never be equal to 500, so the result is printed after each inner loop is executed, so the result is equal to 600,700,800,900 in turn.
⑤ When I = 10 and the inner loop j = 50, result = 500. At this time (exit when result = 500), the inner loop exists, then execute the next External Loop I = 11.
6 ...... Wait and you can think about which I will jump out of the inner loop in advance?

Yes, it is very clever. When I is an approximate number of 500, the inner loop will generate a result equal to 500, so it will jump out of the inner loop in advance.

Similarly, when I * j = 1000, it jumps out of the External Loop, that is, the entire loop. The earliest occurrence should be I = 10, j = 100, but when I = 10, the result will be 100 (I = 10, j = 50) when j gets 500 ), therefore, this is passed out.
This case also requires that I be an appointment of 1000, because 500 <1000, When I is an appointment of 500 and 1000 at the same time, the result is not equal to 1000, when result = 500, it jumps out of the internal loop, and the result is less than 1000.
Therefore, we are looking for the smallest natural number that can divide 1000 instead of 500!
So, I = 40!
Therefore, when I = 40, j = 25, the loop will jump out. In the previous print, I = 39, j = 100.

 

In fact, a nested loop is a loop in a loop. This only adds the loop control identifier <outer> and <inner> (before for, used to mark which loop is used, in this way, we can use exit outer/inner... Statement to control which loop to exit.

Explain oracle cyclically

Oracle Nested loop (Nested Loops, NL ):

A) if driving row source (External table) is small and has a unique index on inner row source (internal table), or has a highly selective non-unique index, using this method can improve efficiency.

B) NESTED LOOPS does not have the advantage of other Connection Methods: You can first return connected rows, instead of waiting for all connection operations to finish before returning data, this enables fast response time.

Hash Join (HJ ):

A) This method was introduced later in Oracle7 and uses a more advanced connection theory. In general, the efficiency should be better than the other two connections, but this connection can only be used in the CBO optimizer, in addition, you must set the hash_area_size parameter to achieve better performance.

B) a relatively good efficiency will be achieved when two large row sources are connected, and a better efficiency will be achieved when one row source is smaller.

C) It can only be used for equivalent connections.

Cartesian Product)

When two row sources are connected, but there is no association condition between them, the flute product will be made in the two row sources, this is usually caused by code omission (that is, the programmer forgets to write association conditions ). The Cartesian product is that each row of a table matches all rows in the other table in sequence. In special cases, we can use the flute product, such as in a star connection. In addition, we should try to use the flute product.