There are cases where you need to run a certain block multiple times. LOOPs are the answer. With them, you can define the logic when to exit the loop; whether it is after the specified number of repetitions or until the block will return the desired result.
There are three loops:
- FOR
- WHILE
- CURSOR
- Implicit
- Explicit
For each of the loops, you can define a controlling statement:
- CONTINUE
- EXIT
FOR loop
FOR Loop is one of the most basic loops you can find in any programming language. It allows you to execute the block for a limited/defined number of executions. See the template below followed by the example.
FOR loop_iterator IN [REVERSE] loop_start .. loop_end LOOP -- here comes your block for processing END LOOP;
DECLARE
v_number NUMBER;
BEGIN
v_number := 1;
FOR i IN 1 .. 5
LOOP
dbms_output.put(v_number||' * '||i||' = ');
v_number := v_number * i;
dbms_output.put_line(v_number);
END LOOP;
END;
output
-----------------
1 * 1 = 1
1 * 2 = 2
2 * 3 = 6
6 * 4 = 24
24 * 5 = 120
If you used the REVERSE keyword, it would do the same expect the iterator will not be from 1 to 5 but from 5 to 1.
DECLARE
v_number NUMBER;
BEGIN
v_number := 1;
FOR i IN REVERSE 1 .. 5
LOOP
dbms_output.put(v_number||' * '||i||' = ');
v_number := v_number * i;
dbms_output.put_line(v_number);
END LOOP;
END;
output
-------------------
1 * 5 = 5
5 * 4 = 20
20 * 3 = 60
60 * 2 = 120
120 * 1 = 120
There is also a FORALL loop which is much more efficient in bulk processing than the usual FOR loop. It executes all DML statements at once and works with collections. See the example below.
DECLARE
TYPE id_list IS VARRAY(20) OF NUMBER;
order_ids id_list := id_list(1000010, 1000823, 1000382, 1000001, 1000123); -- order IDs
BEGIN
FORALL i IN order_ids .FIRST..order_ids .LAST
DELETE FROM order WHERE id_order = order_ids(i);
COMMIT;
END;
You can use the same method for inserting or updating records. Note, that there is no LOOP keyword because you are not looping any data! It is not technically a loop however, it’s worth mentioning it.
WHILE loop
WHILE loop might be a bit tricky and you have to be very careful while using it. First of all, you never know how many times it will execute the block (it might happen that the block is not executed even once). This is actually not a big deal. On the other hand, what is a big deal is an infinite loop. The infinite loop means that your code gets stuck inside the loop forever; meaning, your escape condition will never be evaluated as true. But don’t you worry 🙂 I will show you how to secure this.
Here is the basic WHILE loop template:
WHILE some_condition LOOP -- a block of processes END LOOP;
Now, let’s have a look at a real case scenario
DECLARE
v_limit NUMBER;
v_number NUMBER;
BEGIN
v_limit := 100;
v_number := 1;
WHILE v_number <= v_limit
LOOP
dbms_output.put(v_number ||' * 2 = ');
v_number := v_number * 2;
dbms_output.put_line(v_number);
END LOOP;
END;
output
--------------
1 * 2 = 2
2 * 2 = 4
4 * 2 = 8
8 * 2 = 16
16 * 2 = 32
32 * 2 = 64
64 * 2 = 128
As you can see, I am multiplying the variable v_number by 2 every loop. The last number to by multiplied will be 64 (in the sixth step) because it is still lower or equal to the limit (which is 100). The very next result is 128, the loop will check (in the WHILE section) whether it is still lower and because it is not, it will escape the loop and will continue with the following code (here is nothing to proceed; thus the end of the procedure). You don’t need to worry about the infinite loop here but what if you don’t know the input data? They might come from the database. What if the variable v_number will be negative? The loop condition will never be satisfied and therefore you have to make sure your program will not end/fail until it consumes all allocated memory. Here is the trick 🙂
DECLARE
v_limit NUMBER := 100;
v_number NUMBER := -1;-- negative number
v_iter NUMBER := 1;
BEGIN
WHILE v_number <= v_limit
LOOP
dbms_output.put(v_iter||'. iteration => '||v_number ||' * 2 = ');
v_number := v_number * 2;
dbms_output.put_line(v_number);
-- here is the trick
v_iter := v_iter + 1;
EXIT WHEN v_iter > 20;
END LOOP;
END;
output
------------------------------
1. iteration => -1 * 2 = -2
2. iteration => -2 * 2 = -4
3. iteration => -4 * 2 = -8
4. iteration => -8 * 2 = -16
5. iteration => -16 * 2 = -32
6. iteration => -32 * 2 = -64
7. iteration => -64 * 2 = -128
8. iteration => -128 * 2 = -256
9. iteration => -256 * 2 = -512
10. iteration => -512 * 2 = -1024
11. iteration => -1024 * 2 = -2048
12. iteration => -2048 * 2 = -4096
13. iteration => -4096 * 2 = -8192
14. iteration => -8192 * 2 = -16384
15. iteration => -16384 * 2 = -32768
16. iteration => -32768 * 2 = -65536
17. iteration => -65536 * 2 = -131072
18. iteration => -131072 * 2 = -262144
19. iteration => -262144 * 2 = -524288
20. iteration => -524288 * 2 = -1048576
Yes! You have to count how many times the loop was executed and make sure it will end at the specific number of executions (just in case something goes wrong). Owing to the fact that we are multiplying a negative number, it will never escape the loop condition where v_number will be lower or equal to v_limit. It is always a good habit to implement such “escape plan” just in case 🙂 Even if you are “sure” that infinite loop is not possible in your case (it always is – believe me).
CURSOR loop
There three types of cursors: Implicit, FOR and Explicit. First and foremost, the implicit cursor (together with the FOR cursor) is much faster than the explicit one. On the top of that, the implicit cursor is more intelligent than the explicit one because it already handles “failures” (such as NO_DATA_FOUND or TOO_MANY_ROWS) by default, unlike the explicit cursor where you have to define it on your own. How can you recognize them? Ok, let me show you the difference and how to use them.
Implicit cursor
DECLARE
v_emp_id employee.employee_id%TYPE;
BEGIN
SELECT id_employee
INTO v_emp_id -- implicit cursor
FROM employee
WHERE id_employee = 10050;
dbms_output.put_line('Employee ID:' || v_emp_id);
END;
/****
* here comes the example with the implicit FOR cursor
****/
BEGIN
FOR i IN (SELECT id_employee as emp_id
FROM employee
WHERE id_employee = 10050)
LOOP
dbms_output.put_line('Employee ID:' || i.emp_id);
END LOOP;
END;
Of course that the FOR cursor invokes a loop so you can easily process more rows there (not only one).
Explicit cursor
With Explicit cursors, the syntax is a little bit longer and complicated.
DECLARE
v_emp_id employee.employee_id%TYPE;
CURSOR c_emps IS -- explicit cursor
SELECT id_employee as emp_id
FROM employee
WHERE id_employee = 10050;
BEGIN
OPEN c_emps; -- open the cursor
FETCH c_emps INTO v_emp_id; -- here you can fetch multiple rows as well. See the example below
dbms_output.put_line('Employee ID:' || v_emp_id);
CLOSE c_emps; -- close the cursor
END;
-- fetching multiple rows and columns with implemented checks
DECLARE
v_emp_id employee.employee_id%TYPE;
v_emp_name employee.full_name%TYPE;
CURSOR c_emps IS -- explicit cursor
SELECT id_employee as emp_id
, full_name as emp_name
FROM employee
WHERE id_employee BETWEEN 10050 AND 10099;
BEGIN
OPEN c_emps; -- open the cursor
FETCH c_emps INTO v_emp_id, v_emp_name;
EXIT WHEN c_emps%NOTFOUND; -- will exit once the cursor is empty; will continue with the code after CLOSE <cursor_name>;
dbms_output.put_line('Employee ('||v_emp_id||') =>' || v_emp_name);
CLOSE c_emps; -- close the cursor
END;
Explicit vs Implicit vs FOR cursors comparison
Now, I will demonstrate the difference between all cursors. I will have to use a loop to demonstrate the difference in time for each of them because they are so fast that a simple demonstration won’t be enough.
DECLARE
v_start NUMBER;
v_tables all_tables%ROWTYPE;
CURSOR expl_cursor IS
SELECT *
FROM all_tables;
BEGIN
/*** EXPLICIT ***/
-- get the initial start time with the GET_TIME function
v_start := DBMS_UTILITY.get_time;
OPEN expl_cursor;
LOOP
FETCH expl_cursor INTO v_tables;
EXIT WHEN expl_cursor%NOTFOUND;
NULL; -- do nothing
END LOOP;
CLOSE expl_cursor;
-- get the runtime
dbms_output.put_line('Explicit cursor: '||(dbms_utility.get_time - v_start));
/***************************************************/
/*** EXPLICIT FOR ***/
-- get the initial start time with the GET_TIME function
v_start := DBMS_UTILITY.get_time;
FOR cur_rec IN expl_cursor
LOOP
NULL; -- do nothing
END LOOP;
-- get the runtime
dbms_output.put_line('Explicit FOR cursor: '||(dbms_utility.get_time - v_start));
/***************************************************/
/*** Implicit FOR ***/
-- get the initial start time with the GET_TIME function
v_start := DBMS_UTILITY.get_time;
FOR cur_rec IN (SELECT * FROM all_tables)
LOOP
NULL; -- do nothing
END LOOP;
-- get the runtime
dbms_output.put_line('Implicit FOR cursor: '||(dbms_utility.get_time - v_start));
/***************************************************/
END;
output
------------------------------
Explicit cursor: 54
Explicit FOR cursor: 17
Implicit FOR cursor: 16
It seems like a small difference but imagine a complex logic implemented (this is just a SELECT query). As a matter of fact, you can see the implicit cursor and the explicit FOR cursor are 3times faster than the explicit cursor. That is a LOT!
Also, you might notice I sometimes used a specific data type and sometimes I used something like %TYPE and %ROWTYPE. This is a special declaration that allows you to copy the same data type of a previously defined variable, nested table, record, database column or others.