메뉴 건너뛰기

Korea Oracle User Group

21c

NF 21c NF - PL/SQL Extended Iteration 2

명품관 2021.02.04 11:38 조회 수 : 493

21c NF - PL/SQL Extended Iteration 2


연관글

21c NF - PL/SQL Extended Iteration 1

 


이전 글에 이어 다 확인하지 못한 사항에 대해 더 확인해보도록 하겠다.

 

 

0. 테스트 데이터 생성

1. Mutable Iteration Variables

2. Cursor Iteration Controls

3. Qualified Expression 방식으로 FOR .. LOOP 간단히 표시해 보기

4. Summary

 

0. 테스트 데이터 생성

 

CREATE TABLE TEST_CUR_ITER
(
ID NUMBER,
NAME VARCHAR2(30)
);
 
INSERT INTO TEST_CUR_ITER VALUES('101','James');
INSERT INTO TEST_CUR_ITER VALUES('102','Scarlett');
INSERT INTO TEST_CUR_ITER VALUES('103','Michael');
INSERT INTO TEST_CUR_ITER VALUES('104','Andrew');
INSERT INTO TEST_CUR_ITER VALUES('105','Paul');
COMMIT;
cs

 

1. Mutable Iteration Variables

Mutable 키워드를 통해 Loop 내에서 Iterand를 변경할 수 있도록 해 준다.

아래의 테스트 내용을 살펴보도록 하자.

 

Iterand가 수정이 안되는 부분을 보도록 하겠다.

 

19c 테스트 결과

 

BEGIN
    FOR i in 1 .. 20 loop
        DBMS_OUTPUT.PUT_LINE('i : ' || i);
        i := i+4;
    END LOOP;
END;
/
 
ORA-06550: line 4, column 3: PLS-00363: expression 'I' cannot be used as an assignment target ORA-06550: line 4, column 3: PL/SQL: Statement ignored
cs

 

21c 테스트 결과

 

BEGIN
    FOR i in 1 .. 20 loop
        DBMS_OUTPUT.PUT_LINE('i : ' || i);
        i := i+4;
    END LOOP;
END;
/
 
ORA-06550: line 4, column 3: PLS-00363: expression 'I' cannot be used as an assignment target ORA-06550: line 4, column 3: PL/SQL: Statement ignored
cs

 

19c와 같이 에러가 발생한다.

즉 i 라는 iterand 를 Loop 내에서 수정할 경우 에러가 발생하며 수정을 할 수 없다.

하지만 아래와 같이 mutable 이란 키워드를 사용해 보도록 하자.

 

BEGIN
    FOR i MUTABLE in 1 .. 20 loop
        DBMS_OUTPUT.PUT_LINE('i : ' || i);
        i := i+4;
    END LOOP;
END;
/
 
i : 1
i : 6
i : 11
i : 16
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.009
cs

 

위와 같이 에러 없이 수행되는 것을 확인할 수 있다.

 

mutable 키워드는 21c 버전부터 지원된다.

 

for 뒤에 사용하는 루프 인덱스 역할의 iterand 값이 기존에는 immutable 성격이였다.

loop내의 로직에서 변경시 ORA-06550 에러를 만나게 된다.

하지만 21c에서 mutable 키워드를 사용할 경우 immutable 특성이 mutable로 변경된다.

주의할 점은 iterand를 수정할 경우 for loop 문장이 무한루프에 빠지지 않도록 유의하면서 코드를 작성해야 한다는 점이다.

 

2. Cursor Iteration Control

문법구조

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

 

cursor iteration control은 1번 글의 collection iteration의 VALUES OF와 같은 의미로 보인다.

REVERSE 키워드와는 같이 사용할 수 없다.

 

1) CURSOR를 사용한 Collection Iteration 중 VALUES 방법

 

BEGIN
  FOR CUR IN VALUES OF (SELECT NAME
                        FROM TEST_CUR_ITER) 
  LOOP
    DBMS_OUTPUT.PUT_LINE(CUR.NAME);
  END LOOP;
END;
/
 
James
Scarlett
Michael
Andrew
Paul
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.012
cs

 

2) CURSOR를 사용한 Collection Iteration 중 INDICES 방법

 

BEGIN
  FOR IND IN INDICES OF (SELECT IDNAME
                         FROM TEST_CUR_ITER) 
  LOOP
    DBMS_OUTPUT.PUT_LINE(IND);
  END LOOP;
END;
/
 
1
2
3
4
5
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.007
cs

 

3) CURSOR를 사용한 Collection Iteration 중 PAIRS 방법

 

BEGIN
  FOR IND, CUR IN PAIRS OF (SELECT IDNAME
                            FROM TEST_CUR_ITER) 
  LOOP
    DBMS_OUTPUT.PUT_LINE('INDEX : ' || IND || ' ID : ' || CUR.ID || ', NAME : ' || CUR.NAME);
  END LOOP;
END;
/
 
INDEX : 1 ID : 101NAME : James
INDEX : 2 ID : 102NAME : Scarlett
INDEX : 3 ID : 103NAME : Michael
INDEX : 4 ID : 104NAME : Andrew
INDEX : 5 ID : 105NAME : Paul
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.011
cs

 

4) Dynamic SQL을 사용한 CURSOR 사용 방법

VALUES OF / INDICES OF / PAIRS OF 방법

 

DECLARE
  SQL_STMT VARCHAR2(100) := 'SELECT NAME FROM TEST_CUR_ITER';
BEGIN
  FOR VAL VARCHAR2(100IN VALUES OF (EXECUTE IMMEDIATE SQL_STMT) 
  LOOP
    DBMS_OUTPUT.PUT_LINE(VAL);
  END LOOP;
END;
/
 
James
Scarlett
Michael
Andrew
Paul
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.010
cs

 

DECLARE
  SQL_STMT VARCHAR2(100) := 'SELECT ID FROM TEST_CUR_ITER';
BEGIN
  FOR IND, VAL_ID NUMBER IN PAIRS OF (EXECUTE IMMEDIATE SQL_STMT) 
  LOOP
    DBMS_OUTPUT.PUT_LINE('INDEX : ' || IND || ' ID : ' || VAL_ID);
  END LOOP;
END;
/
 
INDEX : 1 ID : 101
INDEX : 2 ID : 102
INDEX : 3 ID : 103
INDEX : 4 ID : 104
INDEX : 5 ID : 105
 
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.009
cs

 

DECLARE
  SQL_STMT VARCHAR2(100) := 'SELECT ID, NAME FROM TEST_CUR_ITER';
  TYPE REC IS RECORD(ID NUMBERNAME VARCHAR2(30));
BEGIN
  FOR IND, CUR REC IN PAIRS OF (EXECUTE IMMEDIATE SQL_STMT) 
  LOOP
    DBMS_OUTPUT.PUT_LINE('INDEX : ' || IND || ', ID : ' || CUR.ID || ', NAME : ' || CUR.NAME);
  END LOOP;
END;
/
 
INDEX : 1ID : 101NAME : James
INDEX : 2ID : 102NAME : Scarlett
INDEX : 3ID : 103NAME : Michael
INDEX : 4ID : 104NAME : Andrew
INDEX : 5ID : 105NAME : Paul
 
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.009
cs

 

EXECUTE IMMEDIATE 방식을 사용할 경우 Return 값들의 데이터 타입이 컴파일 시점에 확인이 불가능하여

Iterand의 데이터 값이 명시적으로 맞아야 에러 없이 수행되는 듯하다.

 

3. Qualified Expression 방식으로 FOR .. LOOP 간단히 표시해 보기

Qualified Expression을 사용하지 않은 방법

 

DECLARE
   TYPE TEST_T IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   TT  TEST_T := TEST_T(10,20,30,40,50,60,70,80,90,100);
   NEWTEST_T TEST_T;
BEGIN
    FOR I IN 1..10 BY 2 LOOP
        NEWTEST_T(i) := TT(i);
    END LOOP;
        
    FOR I,J IN PAIRS OF NEWTEST_T LOOP
      DBMS_OUTPUT.PUT_LINE(I || '=>'|| J);
    END LOOP;
END;
/
 
1=>10
3=>30
5=>50
7=>70
9=>90
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.006
cs

 

아래는 Qualified Expression의 표현법으로 변경한 코드이다.

 

DECLARE
   TYPE TEST_T IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
   TT  TEST_T := TEST_T(10,20,30,40,50,60,70,80,90,100);
   NEWTEST_T TEST_T;
BEGIN
    NEWTEST_T := TEST_T(FOR I IN 1..10 BY 2 => TT(i));
    FOR I,J IN PAIRS OF NEWTEST_T LOOP
      DBMS_OUTPUT.PUT_LINE(I || '=>'|| J);
    END LOOP;
END;
/
 
1=>10
3=>30
5=>50
7=>70
9=>90
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.012
cs

 

 

FOR I IN 1..10 BY 2 LOOP
    NEWTEST_T(i) := TT(i);
END LOOP;
cs
  =

위 부분이 아래와 같이 변경될 수 있다.

 

NEWTEST_T := TEST_T(FOR I IN 1..10 BY 2 => TT(i));
cs

 

컬렉션에 데이터를 입력하는 방법이 수월해 질 수 있다.

활용 방법에 대해서는 더 많은 상황에 대해서 경험을 해봐야 알 거 같다.

 

4. Summary

2개의 게시글로 21c 버전에서 확장된 PL/SQL Iteration 사용예를 확인하며 New Feature 부분을 살펴보았다.

새로운 기능에 대해서 기능적인 측면에서 확인해 본 상태일 뿐이지 활용도 측면에서는 겉핡기 식인 내용 뿐이다.

내게있어서 이런 부분은 경험이나 상황을 통해서 활용도를 늘려나갈 수 있는 부분이라서 아직까지는 한계가 있는 듯하다.

이런 부분은 염두하고 글을 읽어 주었으면 한다.

위로