Oracle PL/SQL/PL SQL/For Loop
Содержание
- 1 A numeric FOR loop with insert statement
- 2 Call EXIT to exit a for loop
- 3 Call EXIT WHEN to exit a function
- 4 Changing the loop increment
- 5 Define a looping indexer as member variable in a procedure
- 6 Exit(break) a for loop
- 7 For each reverse
- 8 For loop: counter IN 1..5
- 9 FOR Loop Ranges with variable
- 10 FOR Loop Scoping Rules
- 11 If... End if
- 12 loop index scope is limited to the FOR loop.
- 13 Loop till count(*)
- 14 Nested for loop
- 15 Nested for loop vs table join in for loop
- 16 Nesting FOR loops
- 17 Numeric FOR Loop
- 18 Numeric loop will ignore the externally scoped variable and create a new locally scoped variable.
- 19 Put DBMS_OUTPUT.PUT_LINE in for loop
- 20 REVERSE: Reversing the loop
- 21 starting_number and ending_number must be integers.
- 22 The scope of the index of a FOR LOOP.
- 23 Use for counter in insert statement
- 24 Use for loop as if statement
- 25 Use for loop to loop through result from a select statement
- 26 Use variable as an upper bound of for loop
- 27 Your first FOR loop
A numeric FOR loop with insert statement
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> BEGIN
2 FOR v_LoopCounter IN 1..50 LOOP 3 INSERT INTO MyTable (num_col) 4 VALUES (v_LoopCounter); 5 END LOOP; 6 END; 7 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
1.00 2.00 3.00 4.00 5.00 6.00 7.00 8.00 9.00 10.00 11.00 NUM_COL CHAR_COL
------------------------------------------------------------
12.00 13.00 14.00 15.00 16.00 17.00 18.00 19.00 20.00 21.00 22.00 NUM_COL CHAR_COL
------------------------------------------------------------
23.00 24.00 25.00 26.00 27.00 28.00 29.00 30.00 31.00 32.00 33.00 NUM_COL CHAR_COL
------------------------------------------------------------
34.00 35.00 36.00 37.00 38.00 39.00 40.00 41.00 42.00 43.00 44.00 NUM_COL CHAR_COL
------------------------------------------------------------
45.00 46.00 47.00 48.00 49.00 50.00
50 rows selected. SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
Call EXIT to exit a for loop
<source lang="sql">
SQL> SQL> BEGIN
2 FOR v_loopcounter IN 1..20 LOOP
3 IF MOD(v_loopcounter,2) = 0 THEN
4 DBMS_OUTPUT.PUT_LINE("The AREA of the circle is " ||v_loopcounter*v_loopcounter );
5 END IF;
6 IF v_loopcounter = 10 THEN
7 EXIT;
8 END IF;
9 END LOOP;
10 END;
11 /
The AREA of the circle is 4 The AREA of the circle is 16 The AREA of the circle is 36 The AREA of the circle is 64 The AREA of the circle is 100 PL/SQL procedure successfully completed. SQL> SQL> SQL> --
</source>
Call EXIT WHEN to exit a function
<source lang="sql">
SQL> SQL> CREATE OR REPLACE function exitfunc(p_pass_string VARCHAR2)
2 RETURN NUMBER IS 3 v_MYCOUNTER INTEGER := 1; 4 v_COUNTNOSP NUMBER := 0; 5 BEGIN 6 WHILE v_MYCOUNTER <= LENGTH(p_PASS_STRING) LOOP 7 IF SUBSTR(p_PASS_STRING,v_MYCOUNTER,1) != " " THEN 8 v_COUNTNOSP := v_COUNTNOSP + 1; 9 ELSE 10 NULL; 11 END IF; 12 v_MYCOUNTER := v_MYCOUNTER + 1; 13 EXIT WHEN SUBSTR(p_PASS_STRING,v_MYCOUNTER,1) = " "; 14 END LOOP; 15 RETURN v_COUNTNOSP ; 16 END exitfunc; 17 /
Function created. SQL> SQL> SQL> DECLARE
2 v_MYTEXT VARCHAR2(20) := "THIS IS A TEST";
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Total count is " || exitfunc(v_MYTEXT));
5 END;
6 /
Total count is 4 PL/SQL procedure successfully completed. SQL> SQL> --
</source>
Changing the loop increment
<source lang="sql">
SQL> SQL> -- Changing the loop increment. SQL> BEGIN
2 FOR i IN 1..6 LOOP
3 IF MOD(i,2) = 0 THEN
4 DBMS_OUTPUT.PUT_LINE("Loop counter is " || i);
5 END IF;
6 END LOOP;
7 END;
8 /
Loop counter is 2 Loop counter is 4 Loop counter is 6 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Define a looping indexer as member variable in a procedure
<source lang="sql">
SQL> -- create demo table SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 fname VARCHAR2(10 BYTE), 4 lname VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMM
DD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMM
DD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMM
DD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMM
DD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMM
DD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMM
DD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMM
DD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMM
DD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE looping_example IS
2 i NUMBER := 0;
3 BEGIN
4 FOR rec IN (SELECT * FROM emp)
5 LOOP
6 i := i + 1;
7 DBMS_OUTPUT.put_line ("Record " || i || " is emp " || rec.fname);
8 END LOOP;
9
10 DBMS_OUTPUT.put_line ("Procedure Looping Example is done");
11 END;
12 /
Procedure created. SQL> show errors No errors. SQL> SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Exit(break) a for loop
<source lang="sql">
SQL> SQL> SQL> --EXIT WHEN statement SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 myValue INTEGER := 5; 3 BEGIN 4 FOR i IN 1..12 LOOP 5 6 myValue := myValue +5; 7 DBMS_OUTPUT.PUT_LINE(myValue); 8 EXIT WHEN myValue > 100; 9 END LOOP; 10 END; 11 12 13 /
10 15 20 25 30 35 40 45 50 55 60 65 PL/SQL procedure successfully completed. SQL>
</source>
For each reverse
<source lang="sql">
SQL> declare
2 i number; 3 begin 4 FOR i IN REVERSE 1..5 5 LOOP 6 dbms_output.put_line(i); 7 END LOOP; 8 end; 9 /
5 4 3 2 1 PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
For loop: counter IN 1..5
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 counter INTEGER := 2; 3 BEGIN 4 FOR counter IN 1..5 LOOP 5 DBMS_OUTPUT.PUT_LINE(counter); 6 END LOOP; 7 END; 8 /
1 2 3 4 5 PL/SQL procedure successfully completed.
</source>
FOR Loop Ranges with variable
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 v_LowValue NUMBER := 10; 3 v_HighValue NUMBER := 40; 4 BEGIN 5 FOR v_Counter IN REVERSE v_LowValue .. v_HighValue LOOP 6 INSERT INTO MyTable 7 VALUES (v_Counter, "Dynamically specified loop ranges"); 8 END LOOP; 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
40 Dynamically specified loop ranges
39 Dynamically specified loop ranges
38 Dynamically specified loop ranges
37 Dynamically specified loop ranges
36 Dynamically specified loop ranges
35 Dynamically specified loop ranges
34 Dynamically specified loop ranges
33 Dynamically specified loop ranges
32 Dynamically specified loop ranges
31 Dynamically specified loop ranges
30 Dynamically specified loop ranges
NUM_COL CHAR_COL
------------------------------------------------------------
29 Dynamically specified loop ranges
28 Dynamically specified loop ranges
27 Dynamically specified loop ranges
26 Dynamically specified loop ranges
25 Dynamically specified loop ranges
24 Dynamically specified loop ranges
23 Dynamically specified loop ranges
22 Dynamically specified loop ranges
21 Dynamically specified loop ranges
20 Dynamically specified loop ranges
19 Dynamically specified loop ranges
NUM_COL CHAR_COL
------------------------------------------------------------
18 Dynamically specified loop ranges
17 Dynamically specified loop ranges
16 Dynamically specified loop ranges
15 Dynamically specified loop ranges
14 Dynamically specified loop ranges
13 Dynamically specified loop ranges
12 Dynamically specified loop ranges
11 Dynamically specified loop ranges
10 Dynamically specified loop ranges
31 rows selected. SQL> SQL> drop table MyTable; Table dropped.
</source>
FOR Loop Scoping Rules
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> SQL> DECLARE
2 v_Counter NUMBER := 7; 3 BEGIN 4 INSERT INTO MyTable (num_col) 5 VALUES (v_Counter); 6 7 FOR v_Counter IN 20..30 LOOP 8 INSERT INTO MyTable (num_col) 9 VALUES (v_Counter); 10 END LOOP; 11 INSERT INTO MyTable (num_col) 12 VALUES (v_Counter); 13 END; 14 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
7
20
21
22
23
24
25
26
27
28
29
NUM_COL CHAR_COL
------------------------------------------------------------
30
7
13 rows selected. SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
If... End if
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SQL> declare
2 v_mynumber number := 0; 3 begin 4 loop 5 if v_mynumber > 7 then 6 exit; 7 end if; 8 v_mynumber := v_mynumber + 2; 9 end loop; 10 end; 11 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> --
</source>
loop index scope is limited to the FOR loop.
<source lang="sql">
SQL> BEGIN
2 FOR i IN 1..10 LOOP
3 dbms_output.put_line("The index value is ["||i||"]");
4 END LOOP;
5 END;
6 /
The index value is [1] The index value is [2] The index value is [3] The index value is [4] The index value is [5] The index value is [6] The index value is [7] The index value is [8] The index value is [9] The index value is [10] PL/SQL procedure successfully completed. SQL> SQL>
</source>
Loop till count(*)
<source lang="sql">
SQL> -- create demo table SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 fname VARCHAR2(10 BYTE), 4 lname VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> SQL> SQL> -- prepare data SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> SQL> SQL> declare
2 empCount number;
3 i number;
4 begin
5 select count(*) into empCount from emp;
6
7 FOR i IN 1 .. empCount LOOP
8 dbms_output.put_line("emp " || i);
9 END LOOP;
10 end;
11 /
emp 1 emp 2 PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL> SQL>
</source>
Nested for loop
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> SQL> begin
2 for i in 1 .. 5000 3 loop 4 for x in ( select ename, empno, hiredate from emp ) 5 loop 6 null; 7 end loop; 8 end loop; 9 end; 10 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Nested for loop vs table join in for loop
<source lang="sql">
SQL> SQL> SQL> create table myTable1( a int primary key, y char(80) ); Table created. SQL> SQL> create table myTable2( b int primary key, a references myTable1, y char(80) ); Table created. SQL> SQL> create index myTable2_a_idx on myTable2(a); Index created. SQL> SQL> create table myTable3( c int primary key, b references myTable2, y char(80) ); Table created. SQL> SQL> create index myTable3_b_idx on myTable3(b); Index created. SQL> SQL> insert into myTable1
2 select rownum, "x" 3 from all_objects 4 where rownum <= 1000;
1000 rows created. SQL> SQL> insert into myTable2
2 select rownum, mod(rownum,1000)+1, "x" 3 from all_objects 4 where rownum <= 5000;
5000 rows created. SQL> SQL> insert into myTable3
2 select rownum, mod(rownum,5000)+1, "x" 3 from all_objects;
12588 rows created. SQL> SQL> begin
2 for i in 1 .. 1000 3 loop 4 for x in ( select myTable1.a myTable1a, myTable1.y myTable1y, 5 myTable2.b myTable2b, myTable2.a myTable2a, myTable2.y myTable2y, 6 myTable3.c myTable3c, myTable3.b myTable3b, myTable3.y myTable3y 7 from myTable1, myTable2, myTable3 8 where myTable1.a = i 9 and myTable2.a (+) = myTable1.a 10 and myTable3.b (+) = myTable2.b ) 11 loop 12 null; 13 end loop; 14 end loop; 15 end; 16 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> begin
2 for i in 1 .. 1000 3 loop 4 for a in ( select myTable1.a, myTable1.y from myTable1 where myTable1.a = i ) 5 loop 6 for b in ( select myTable2.b, myTable2.a, myTable2.y from myTable2 where myTable2.a = a.a ) 7 loop 8 for c in ( select myTable3.c, myTable3.b, myTable3.y from myTable3 where myTable3.b = b.b ) 9 loop 10 null; 11 end loop; 12 end loop; 13 end loop; 14 end loop; 15 end; 16 /
PL/SQL procedure successfully completed. SQL>
</source>
Nesting FOR loops
<source lang="sql">
SQL> SQL> -- Nesting FOR loops. SQL> BEGIN
2 FOR i IN 1..2 LOOP
3 FOR j IN 1..4 LOOP
4 DBMS_OUTPUT.PUT_LINE("Outer Loop counter is " ||
5 i || " Inner Loop counter is " || j);
6 END LOOP;
7 END LOOP;
8 END;
9 /
Outer Loop counter is 1 Inner Loop counter is 1 Outer Loop counter is 1 Inner Loop counter is 2 Outer Loop counter is 1 Inner Loop counter is 3 Outer Loop counter is 1 Inner Loop counter is 4 Outer Loop counter is 2 Inner Loop counter is 1 Outer Loop counter is 2 Inner Loop counter is 2 Outer Loop counter is 2 Inner Loop counter is 3 Outer Loop counter is 2 Inner Loop counter is 4 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Numeric FOR Loop
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> BEGIN
2 FOR v_Counter IN 1..50 LOOP 3 INSERT INTO MyTable 4 VALUES (v_Counter, "Loop Index"); 5 END LOOP; 6 END; 7 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
1 Loop Index
2 Loop Index
3 Loop Index
4 Loop Index
5 Loop Index
6 Loop Index
7 Loop Index
8 Loop Index
9 Loop Index
10 Loop Index
11 Loop Index
NUM_COL CHAR_COL
------------------------------------------------------------
12 Loop Index
13 Loop Index
14 Loop Index
15 Loop Index
16 Loop Index
17 Loop Index
18 Loop Index
19 Loop Index
20 Loop Index
21 Loop Index
22 Loop Index
NUM_COL CHAR_COL
------------------------------------------------------------
23 Loop Index
24 Loop Index
25 Loop Index
26 Loop Index
27 Loop Index
28 Loop Index
29 Loop Index
30 Loop Index
31 Loop Index
32 Loop Index
33 Loop Index
NUM_COL CHAR_COL
------------------------------------------------------------
34 Loop Index
35 Loop Index
36 Loop Index
37 Loop Index
38 Loop Index
39 Loop Index
40 Loop Index
41 Loop Index
42 Loop Index
43 Loop Index
44 Loop Index
NUM_COL CHAR_COL
------------------------------------------------------------
45 Loop Index
46 Loop Index
47 Loop Index
48 Loop Index
49 Loop Index
50 Loop Index
50 rows selected. SQL> SQL> drop table MyTable; Table dropped.
</source>
Numeric loop will ignore the externally scoped variable and create a new locally scoped variable.
<source lang="sql">
SQL> BEGIN
2 FOR i IN 1..10 LOOP
3 dbms_output.put_line("The index value is ["||i||"]");
4 END LOOP;
5 END;
6 /
The index value is [1] The index value is [2] The index value is [3] The index value is [4] The index value is [5] The index value is [6] The index value is [7] The index value is [8] The index value is [9] The index value is [10] PL/SQL procedure successfully completed. SQL> SQL>
</source>
Put DBMS_OUTPUT.PUT_LINE in for loop
<source lang="sql">
SQL> set echo on SQL> SQL> set echo on SQL> SQL> DECLARE
2 v_MyChar VARCHAR2(20) := "test";
3 v_NUMBER NUMBER;
4 V_Date DATE := SYSDATE;
5 v_counter INTEGER;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE("This is a Test");
8 DBMS_OUTPUT.PUT_LINE("Of Syntax Error Debugging");
9 For v_COUNTER IN 1..5 LOOP
10 DBMS_OUTPUT.PUT_LINE("You are in loop:" || v_counter);
11 END LOOP;
12 END;
13 /
This is a Test Of Syntax Error Debugging You are in loop:1 You are in loop:2 You are in loop:3 You are in loop:4 You are in loop:5 PL/SQL procedure successfully completed. SQL> SQL> SQL> --
</source>
REVERSE: Reversing the loop
<source lang="sql">
SQL> SQL> -- Reversing the loop. SQL> DECLARE
2 loop_start Integer := 1;
3 BEGIN
4 FOR i IN REVERSE loop_start..5 LOOP
5 DBMS_OUTPUT.PUT_LINE("Loop counter is " || i);
6 END LOOP;
7 END;
8 /
Loop counter is 5 Loop counter is 4 Loop counter is 3 Loop counter is 2 Loop counter is 1 PL/SQL procedure successfully completed. SQL>
</source>
starting_number and ending_number must be integers.
<source lang="sql">
SQL> BEGIN
2 FOR i IN 1..10 LOOP
3 dbms_output.put_line("The index value is ["||i||"]");
4 END LOOP;
5 END;
6 /
The index value is [1] The index value is [2] The index value is [3] The index value is [4] The index value is [5] The index value is [6] The index value is [7] The index value is [8] The index value is [9] The index value is [10] PL/SQL procedure successfully completed. SQL> SQL>
</source>
The scope of the index of a FOR LOOP.
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> DECLARE
2 v_Counter NUMBER := 7; 3 BEGIN 4 INSERT INTO MyTable (num_col) 5 VALUES (v_Counter); 6 FOR v_Counter IN 20..30 LOOP 7 INSERT INTO MyTable (num_col) 8 VALUES (v_Counter); 9 END LOOP; 10 INSERT INTO MyTable (num_col) 11 VALUES (v_Counter); 12 END; 13 /
PL/SQL procedure successfully completed. SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
Use for counter in insert statement
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> BEGIN
2 FOR v_Count IN 1..10 LOOP 3 INSERT INTO MyTable (num_col, char_col) 4 VALUES (v_Count, "Hello World!"); 5 END LOOP; 6 END; 7 /
PL/SQL procedure successfully completed. SQL> SQL> SELECT * FROM MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
1.00 Hello World! 2.00 Hello World! 3.00 Hello World! 4.00 Hello World! 5.00 Hello World! 6.00 Hello World! 7.00 Hello World! 8.00 Hello World! 9.00 Hello World! 10.00 Hello World!
10 rows selected. SQL> SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
Use for loop as if statement
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> create or replace trigger myTrigger
2 after delete or update or insert on emp 3 declare 4 begin 5 for irec in (select deptno, count(*) emps 6 from emp 7 group by deptno 8 having count(*) < 3 or count(*) > 8) 9 loop 10 RAISE_APPLICATION_ERROR(-20000, "Department "||irec.deptno || " has "||irec.emps||" emps!"); 11 end loop; 12 end; 13 /
SQL> SQL> drop table emp;
</source>
Use for loop to loop through result from a select statement
<source lang="sql">
SQL> -- create demo table SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 fname VARCHAR2(10 BYTE), 4 lname VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMM
DD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMM
DD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMM
DD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMM
DD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMM
DD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMM
DD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMM
DD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMM
DD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE looping_example IS
2 i NUMBER := 0;
3 BEGIN
4 FOR rec IN (SELECT * FROM emp)
5 LOOP
6 i := i + 1;
7 DBMS_OUTPUT.put_line ("Record " || i || " is emp " || rec.fname);
8 END LOOP;
9
10 DBMS_OUTPUT.put_line ("Procedure Looping Example is done");
11 END;
12 /
Procedure created. SQL> show errors No errors. SQL> SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Use variable as an upper bound of for loop
<source lang="sql">
SQL> SQL> SQL> -- Uses an upper bound: SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 upper INTEGER := 5;
3 BEGIN
4 FOR i IN 1..upper LOOP
5 DBMS_OUTPUT.PUT_LINE("This has executed" ||TO_CHAR(i)||" time(s)");
6 END LOOP;
7 END;
8 /
This has executed1 time(s) This has executed2 time(s) This has executed3 time(s) This has executed4 time(s) This has executed5 time(s) PL/SQL procedure successfully completed. SQL> SQL>
</source>
Your first FOR loop
<source lang="sql">
SQL> SQL> -- Your first FOR loop. SQL> set echo on SQL> BEGIN
2 FOR i IN 1..5 LOOP
3 DBMS_OUTPUT.PUT_LINE("Loop counter is " || i);
4 END LOOP;
5 END;
6 /
Loop counter is 1 Loop counter is 2 Loop counter is 3 Loop counter is 4 Loop counter is 5 PL/SQL procedure successfully completed.
</source>
