메뉴 건너뛰기

Korea Oracle User Group

21c

NF 21c NF - PL/SQL Extended Iteration 1

명품관 2021.01.28 17:28 조회 수 : 613

21c NF - PL/SQL Extended Iteration 1


연관글

21c NF - PL/SQL Extended Iteration 2

 

FOR LOOP 사용시 lower, upper limit에 대한 컨트롤 => 이전에 21c NF 나오기전에 사용하던 방법(이제는 이렇게 사용할 필요가 없겠네요)


0. 오라클 PL/SQL Loop 구문

1. Multiple Iteration Control

2. Stepped Range Iteration Controls

3. Single Expression Iterator Control

4. Collection Iteration Controls

99. Summary

 

PL/SQL Extended Iteration NF(New Feature)는 Loop 구문에서

iteratior 처리 부분이 발전된 것을 이야기 한다.

모든 부분을 살펴보기에는 글이 길어질 거 같아 글을 나누어서 살펴보도록 하겠다.

 

0. 오라클 PL/SQL Loop 구문 

Loop 구문은 아래의 문법 구조를 가진다.

 

loop_statement ::= [ iteration_scheme ] LOOP 
             loop_body 
END LOOP [ label ];
 
iteration_scheme ::= WHILE expression
                       | FOR iterator
cs

 

위 구조에서 iterator 부분이 발전된 것이다.

먼저 테스트 DB 버전에 대해 확인해 보겠다.

 

select banner from v$version;
 
BANNER                                                                 
---------------------------------------------------------------------- 
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production 
 
Elapsed: 00:00:00.018
cs

 

1. Multiple Iteration Control

오라클 21c 버전부터 동시에 여러개의 iterator 표현이 가능하게 되었다

어떻게 활용할지에 대해서는 아직은 더 써봐야 알것 같다.

 

아래 예제 내용을 살펴보자 

 

BEGIN
   FOR i IN 1..3REVERSE i+1..i+350..52 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;
/
 
1
2
3
6
5
4
50
51
52
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.008
cs

 

첫번째는 1-3 까지 루프를 돌고

두번째는 i가 3 이므로 4 - 6 인데 REVERSE 키워드가 있으므로 6 - 4 로 하나씩 줄어든다.

세번째는 다시 50-53 까지 루프를 돈다.

 

이렇게 여러개의 iterator 표현으로 기술해 사용할 수 있다.

 

2. Stepped Range Iteration Controls

루프시 인덱스의 증가 폭을 설정할 수 있게 해준다.

 
문법구조

 

stepped_control ::= [ REVERSE ] lower_bound..upper_bound [ BY step ]
lower_bound ::= numeric_expression
upper_bound ::= numeric_expression
step ::= numeric_expression
cs

 

BY step 절을 추가해 Loop 인덱스 증가 폭을 지정할 수 있다.

 

아래의 예제를 살펴보자

 

BEGIN
   FOR i number(3,1IN 1..4 BY 2REVERSE i+1..i+3 BY 0.5 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;
/
 
1
3
6
5.5
5
4.5
4
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.008
cs

 

첫번째 Iterator 에서는 Step 을 2로, 두번째는 Reverse 방식으로 0.5로 하였다.

두번째는 첫번째가 i 가 3으로 끝나서 i+1, i+3이 6-4로 해석이된다. 

 

주목할 부분은 For 다음 부분에 i 를 정의할 때 data type을 nuber(3,1)로 정의한 부분이다.

이는 두번째 Iterator 기술 부분에 Step을 0.5로 기술하였기 때문에 그에 대응하기 위해서  number(3,1)로 정의한 것이다.

만약 그렇게 정의하지 않았다면 Step 1로 아래와 같이 차감된다. 

 

BEGIN
   FOR i IN 1..4 BY 2REVERSE i+1..i+3 BY 0.5 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;
/
 
1
3
6
5
4
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.007
cs

 

문법구조에 적어 놓았듯이 By step 부분에 step이 numeric_expression이 가능하기 때문에 아래와 같이 사용할 수도 있다.

 

DECLARE
   J PLS_INTEGER := 3;
BEGIN
   FOR I IN 1..10 BY J LOOP
      DBMS_OUTPUT.PUT_LINE('I : ' || I ||', J : ' || J);
   END LOOP;
END;
/
 
I : 1, J : 3
I : 4, J : 3
I : 7, J : 3
I : 10, J : 3
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.009
 
DECLARE
   J PLS_INTEGER := 3;
BEGIN
   FOR I IN J+1..J+10 BY J+2 LOOP
      DBMS_OUTPUT.PUT_LINE('I : ' || I ||', J : ' || J);
   END LOOP;
END;
/
 
I : 4, J : 3
I : 9, J : 3
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.007
cs

 

3. Single Expression Iterator Control

 

Iterator를 정의할 때 대개 "I IN lower bound .. upper bound" 이런 식으로 표현을 하는데

single expression 로 표현이 가능하다는 것이다. 아래 내용을 보면서 이해해 보자 

 

문법구조

 

single_expression_control ::= [ REPEAT ] expr
cs

 

아래 예제로 내용을 이해해 보자

 

BEGIN
    FOR I IN 1 LOOP
        DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/
 
1
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.006
cs

 

위의 내용만 보면 달라진 부분이 없어 보이지만 "FOR I IN 1 LOOP" 와 같이 lower, upper 부분이 1로만 되어 있다.

결과를 봐도 이것을 어디에 쓸 것인가 의문을 제기할 수 있다.

 

또 이 코드가 다른 오라클 버전에서는 안 돌아가나(?) 하고 의문을 가질 수도 있다.

아래는 12c 버전에서 수행해 본 내용이다.

 

SQL> BEGIN
        FOR I IN 1 LOOP
                DBMS_OUTPUT.PUT_LINE(I);
  2    3    4   END LOOP;
  5  END;
  6  /
        FOR I IN 1 LOOP
                   *
2행에 오류:
ORA-06550: 줄 2, 열13:PLS-00103: 심볼 "LOOP"를 만났습니다 다음 중 하나가 기대될 때: * & - + /    at mod remainder
rem .. <지수(**)> || multiset
ORA-06550: 줄 4, 열2:PLS-00103: 심볼 "END"를 만났습니다 다음 중 하나가 기대될 때: begin    function pragma
procedure subtype type <식별자>
<큰 따옴표로 구분된 식별자> current cursor delete exists    prior
cs

 

위와 같이 문법 에러가 발생한다.

 

첫 예제와 같이 사용하면 어디에 활용할지 의문이 들지만 

[REPEAT] expr 구문과 같이 사용한다면 표현이 더 다채로워 진다.

 

BEGIN
   FOR i IN 1REPEAT i*2 WHILE i < 100 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;
/
 
1
2
4
8
16
32
64
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.006
 
BEGIN
   FOR i NUMBER(6,1IN 1.0REPEAT i*10.1 WHILE i < 10000 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
   END LOOP;
END;
/
 
1
10.1
102
1030.2
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.006
cs

 

4. Collection Iteration Controls

아래 예제에는 표현 방법이 오라클의 Qualified Expression으로 표기되어 있다. 

Qualified Expression은 18c에서 소개된 방식으로 추후에 다뤄보도록 하겠다.

 

문법구조

 

collection_iteration_control ::= values_of_control 
                                 | indices_of_control 
                                 | pairs_of_control 
 
values_of_control ::= VALUES OF expr 
                      | VALUES OF (cursor_object)
                      | VALUES OF (sql_statement)
                      | VALUES OF cursor_variable  
                      | VALUES OF (dynamic_sql) 
 
indices_of_control ::= INDICES OF expr 
                      | INDICES OF (cursor_object)
                      | INDICES OF (sql_statement)
                      | INDICES OF cursor_variable 
                      | INDICES OF (dynamic_sql)  
 
pairs_of_control ::= PAIRS OF expr 
                      | PAIRS OF (cursor_object) 
                      | PAIRS OF (sql_statement)
                      | PAIRS OF cursor_variable
                      | PAIRS OF (dynamic_sql) 
cs

 

컬렉션  사용시에 같이 사용할 수 있다.

아래 예제를 살펴보자.

 

DECLARE
   TYPE INTVEC_T IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   VEC INTVEC_T := INTVEC_T(3 => 101 => 11100 => 34);
BEGIN
   FOR I IN VALUES OF VEC LOOP
      DBMS_OUTPUT.PUT_LINE(I);
   END LOOP;
END;
/
 
11
10
34
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.006
cs

 

VALUES OF 절은 위과 같이 컬렉션의 인덱스에 대응하는 값을 리턴해 준다.

 

DECLARE
   TYPE INTVEC_T IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   VEC INTVEC_T := INTVEC_T(3 => 101 => 11100 => 34);
BEGIN
   FOR I IN INDICES OF VEC LOOP 
       DBMS_OUTPUT.PUT_LINE(I);  
   END LOOP;
END;
/
 
1
3
100
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.011
cs

 

INDICES OF 절은 컬렉션의 인덱스를 리턴해 준다.

 

DECLARE
   TYPE INTVEC_T IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   VEC  INTVEC_T := INTVEC_T(3 => 101 => 11100 => 34);
   RESULT INTVEC_T;
BEGIN
   RESULT := INTVEC_T(FOR I,J IN PAIRS OF VEC INDEX J => I);
   FOR I,J IN PAIRS OF RESULT LOOP
      DBMS_OUTPUT.PUT_LINE(I || '=>'|| J);
   END LOOP;
END;
/
 
10=>3
11=>1
34=>100
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.009
cs

 

PAIRS OF 절은 collection의 인덱스와 대응하는 값을 같이 리턴해 준다.

만일 PAIRS OF 절을 사용했을 시에 두 값을 받을 변수를 설정하지 않으면 아래와 같이 에러가 발생한다.

 

DECLARE
   TYPE INTVEC_T IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   VEC  INTVEC_T := INTVEC_T(3 => 101 => 11100 => 34);
   RESULT INTVEC_T;
BEGIN
   RESULT := INTVEC_T(FOR I,J IN PAIRS OF VEC INDEX J => I);
   FOR I IN PAIRS OF RESULT LOOP
      DBMS_OUTPUT.PUT_LINE(I || '=>'|| J);
   END LOOP;
END;
/
 
ORA-06550: line 7, column 13: PLS-00855: Only one iterand specified for a PAIRS OF iteration control. ORA-06550: line 7, column 4: PL/SQL: Statement ignored
cs

 

99. Summary

오라클 21c의 New Feature 들을 하나씩 살펴볼 예정이다.

 

버전이 나온지 얼마되지 않았으며 현재는 오라클 클라우드에서만 확인할 수 있기 때문에

실전에 어떻게 활용할지에 대해서는 깊이 있게 아이디어가 떠오르질 않는다. 

 

일단 기능에 대한 설명과 테스트를 통해 살펴 보기 위주로 진행해 볼 예정이다.

위로