Oracle PL/SQL/PL SQL/For Loop — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:45, 26 мая 2010
Содержание
- 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>