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 each of the loops, you can define a controlling statement:
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 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).
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.
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).
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.