preface

Continued
[ORACLE] qualified expression of version 18c and its enhancement in version 21c

At the end of the previous article, we talked about iterators. Because there are too many contents, they are put together with this article.

In the iterator, in addition to the three iterators mentioned above (basic, index and sequence), it also supports the combination of multiple keywords.

Similar to "for i in 1... 10", which is called iterator, can be used to limit expression or for loop loop. It is a common syntax. Therefore, this article will not write two examples for each usage. Readers can try to modify the writing of limit expression or for loop.

The old rule is to put on the official documents first
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-control-statements.html#GUID-C4BC9960-5945-4646-BBDE-DC00346F8702

for loop syntax

[ label ] for_loop_header 
  statements
END LOOP [ label ];

for_loop_header ::= FOR iterator LOOP

iterator ::= iterand_decl [, iterand_decl] IN iteration_ctl_seq 

iterand_decl  ::= pls_identifier [ MUTABLE | IMMUTABLE ] [ constrained_type ]          

iteration_ctl_seq ::= qual_iteration_ctl [,]...

qual_iteration_ctl ::= [ REVERSE ] iteration_control  pred_clause_seq

iteration_control ::= stepped_control 
                      | single_expression_control 
                      | values_of_control 
                      | indices_of_control 
                      | pairs_of_control  
                      | cursor_control 

pred_clause_seq ::= [ stopping_pred ] [ skipping_pred ]

stopping_pred ::= WHILE boolean_expression  

skipping_pred ::= WHEN boolean_expression 

stepped_control ::= lower_bound .. upper_bound [ BY step ]

single_expression_control ::= [ REPEAT ] expr

The following official paragraph may be wrong, "cursor _iteration _control" actually corresponds to the above "cursor_control", and there are two obvious input errors "_" repeated, "cursor _object" has one more space

cursor_iteration__control ::=  { cursor _object
                    | sql_statement
                    | cursor_variable
                    | dynamic_sql }

qual_iteration_ctl

REVERSE

"REVERSE" (not 21c added) is to arrange the following elements in REVERSE order

begin
  for i in REVERSE 1 .. 5  loop
    dbms_output.put_line ( i );
  end loop;
end;
/
---
5
4
3
2
1

iteration_control

BY (stepped_control)

Let's look at examples first

SET SERVEROUTPUT ON;

DECLARE
  TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY pls_integer;
  l_tab t_tab := t_tab(for i in 1..10 by 2  sequence => i);
BEGIN
  FOR I IN 1 .. l_tab.COUNT LOOP
    DBMS_OUTPUT.put_line(i||':'|| l_tab(I));
  END LOOP;
END;
/

---
1:1
2:3
3:5
4:7
5:9

Use of "by" in for loop

begin
  for i in 1 .. 10 by 2 loop
    dbms_output.put_line ( i );
  end loop;
end;
/

---
1
3
5
7
9

"by" means to select the interval (step) for the elements of in. For example, 2 in the above example is the second element backward each time, so it is the first, third, fifth, seventh and ninth elements in turn.

single_expression_control

REPEAT

"REPEAT", which generates elements in a loop according to the following expression

BEGIN
   FOR i IN 1, REPEAT i*2 WHILE i < 100 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;
/
---
1
2
4
8
16
32
64

VALUES OF (values_of_control)

Fetch the value from the array

DECLARE
   TYPE intvec_t IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   vec intvec_t := intvec_t(3 => 10, 1 => 11, 100 => 34);
BEGIN
   FOR i IN VALUES OF vec LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;
/
---
11
10
34

INDICES OF (indices_of_control )

Fetch the index (key) from the array

DECLARE
   TYPE intvec_t IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   vec intvec_t := intvec_t(3 => 10, 1 => 11, 100 => 34);
BEGIN
   FOR i IN INDICES OF vec LOOP 
       DBMS_OUTPUT.PUT_LINE(i);  
   END LOOP;
END;
/
---
1
3
100

PAIRS OF(pairs_of_control )

Get the index and value pairs (key value pairs)

DECLARE
   TYPE intvec_t IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   vec  intvec_t := intvec_t(3 => 10, 1 => 11, 100 => 34);
BEGIN
   FOR i,j IN PAIRS OF vec LOOP
      DBMS_OUTPUT.PUT_LINE(i || '=>'|| j);
   END LOOP;
END;
/
---
1=>11
3=>10
100=>34

cursor_control

sql_statement

Directly use a query sql (not added by 21c)

begin
  for rec in (select a.region_id, a.region_name from hr.regions a) loop
    dbms_output.put_line(rec.region_id || '-' || rec.region_name);
  end loop;
end;
/

cursor _object

First define a cursor object with a query sql, and then use this object directly (not added by 21c)

declare
  cursor c is
    select a.region_id, a.region_name from hr.regions a;
begin
  for rec in c loop
    dbms_output.put_line(rec.region_id || '-' || rec.region_name);
  end loop;
end;
/

cursor_variable

That is, ref cursor dynamic cursor (not 21c added)

declare
  l_sql varchar2(4000):='select * from hr.regions';
  l_row hr.regions%rowtype;
  type cur is ref cursor;
  curs cur;
begin
  open curs for l_sql;
  loop
    fetch curs into l_row;
    exit when curs%notfound;
    dbms_output.put_line(l_row.region_name);
    end loop;
    close curs;
end;
/

dynamic_sql

That is, dynamic sql, and supports binding variables

declare
  l_sql varchar2(4000) := 'select * from hr.regions where REGION_ID<=:1';
begin
  for r hr.regions%rowtype in (execute immediate l_sql using 3) loop
    dbms_output.put_line(r.region_name);
  end loop;
end;
/

Dynamic sql is one of the most imaginative. Unfortunately, it is limited by this restricted_type. In the above example, if the type of r is not defined or an unqualified type is used, as shown below, an error will be reported

declare
  l_sql varchar2(4000) := 'select * from hr.regions where REGION_ID<=:1';
begin
  for r in (execute immediate l_sql using 3) loop
    null;
  end loop;
end;
/

ORA-06550: Line 4, column 13:
PLS-00858: iterand type was not specified for EXECUTE IMMEDIATE iteration control.
ORA-06550: Line 4, column 3:
PL/SQL: Statement ignored

In addition, it should be noted that this method of dynamic sql cannot be used to limit the default value of expression definition, and can only be assigned after begin.

pred_clause_seq

WHEN (skipping_pred )

"WHEN" means to select only the elements that satisfy the following expression

BEGIN
   FOR i IN 1..10 when mod(i,2) =0 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;
/

---
2
4
6
8
10

WHILE (stopping_pred )

"WHILE" is to stop selecting elements until the following conditions are not met

BEGIN
   FOR i IN 1..10  WHILE i<=5 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;
/

---
1
2
3
4
5

iteration_ctl_seq ::= qual_iteration_ctl [,]...

This grammar actually means that we can put multiple quals_ iteration_ CTLs are separated by commas and combined into an iteration_ctl_seq, for example

DECLARE
   TYPE intvec_t IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   vec intvec_t := intvec_t(3 => 10, 1 => 11, 100 => 34);
   l_sql varchar2(4000):='select a.region_id from hr.regions a';
BEGIN
   FOR i IN 1..6 by 2, 
            REVERSE i+1..i+5,
            repeat i*2 while i<20,
            1..30 when mod(i,9)=0 , 
            values of vec,
            INDICES OF vec,
            vec.first,
            vec.last-1,
            values of (execute immediate l_sql) LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;
/
---
1
3
5
10
9
8
7
6
12
9
18
27
11
10
34
1
3
100
1
99
1
2
3
4

Or change it to the form of a qualified expression

DECLARE
   TYPE intvec_t IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   vec intvec_t := intvec_t(3 => 10, 1 => 11, 100 => 34);
   vec2 intvec_t;
   l_sql varchar2(4000):='select a.region_id from hr.regions a';
BEGIN
  vec2 :=intvec_t(FOR i IN  1..6 by 2, 
                            REVERSE i+1..i+5,
                            repeat i*2 while i<20,
                            1..30 when mod(i,9)=0 ,
                            values of vec,
                            INDICES OF vec,
                            vec.first,
                            vec.last-1,
                            values of (execute immediate l_sql)
                            sequence=>i);
   for i in 1..vec2.count loop
     DBMS_OUTPUT.PUT_LINE(vec2(i));
     end loop;
END;
/

Iterand for this example_ Decl is actually composed of the following nine quals_ iteration_ CTL is composed of

• "1...6 by 2",
• "REVERSE i+1...i+5",
• "repeat i*2 while i<20",
• "1...30 when mod(i,9)=0 ",
• "values of vec",
• "INDICES OF vec"
• "vec.first"
• "vec.last-1"
• "values of (execute immediate l_sql)"

You can see that first and last, which are not mentioned above, are used here. In fact, they are functions of the collection object. Similar functions include count, limit, prior, next, etc. Since it does not cover the main content of this article, it will not be extended. For more functions of the collection, please refer to the official documentation
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-collections-and-records.html#GUID-0452FBDC-D9C1-486E-B432-49AF84743A9F

summary

The enhancement of various restricted expressions from 18c to 21c and the for... Loop loop loop, combined with various operations of the set, makes the plsql programming syntax more relaxed and can greatly improve the programming efficiency.

For more examples, please refer to the following article
Better loops and qualified expressions (array constructors) in PL/SQL

• Author: DarkAthena
• Link to this article: https://www.darkathena.top/archives/21c-for-loop
• Copyright notice: all articles on this blog are in English unless otherwise stated CC BY-NC-SA 3.0 License agreement. Reprint please indicate the source!