Oracle 기초 : 실전(17) PL/SQL - Loop Statements, Random Number Generator

Introduction

PL/SQL에서 제공하는 반복문 문법 중 while loop Statement와 for loop Statement의 예제를 중심으로 해당 문법 및 그 활용을 정리해본다.

---

Syntax: While Loop

<<Syntax of the while loop>>
DECLARE
Val DataType := n;
BEGIN
while Val Operator Num loop
Execute-Statement;
Increment/Decrement;
end loop;
end;
/

DECLARE
	n number := 5;
BEGIN
	while n <= 10 loop
	dbms_output.put(n || ' ');
	n := n+1;
	end loop;
	dbms_output.new_line;
END;
/

if-Statement문과 같이, loop Statement 역시 end loop;으로 반복문의 종류를 선언해 주어야 한다. line feed를 자체적으로 포함한 출력문인 dbms_output.put_line('CONTENTS'); 외에 dbms_output.put('CONTENTS')도 실행 구문으로 선언할 수 있으나, 반드시 BEGIN절의 후위에 dbms_output.new_line;을 작성하여 buffer에 잔류하는 후자 코드를 출력해 주어야 한다.

---

Syntax: Multiplication Table with loop & if

<<Syntax of the Multiplication Table with loop & if>>

DECLARE
    Multiplicand number;
    Multiplier number;
BEGIN
    Multiplicand := &number;
    Multiplier := 1;
    if Multiplicand between 2 and 9 then
    while Multiplier <= 9 loop
   	 dbms_output.put_line(Multiplicand || 
   	 ' * ' || Multiplier || ' = ' || 
    	Multiplicand * Multiplier);
    	Multiplier := Multiplier + 1;
        end loop;
    else 
    dbms_output.put_line('system: ' ||
    Multiplicand || ' is invalid number.');
    end if;
END;
/

사용자에게 정수 2에서 9사이의 Multiplicand(피승수)를 Prompt Popup으로 입력받아 Multiplication Table(구구단)을 출력하는 코드이다. Multiplier(승수)는 1부터 9까지 1씩 증가한다. 복잡한 구조는 아니지만, 아직 문법에 익숙하지 않다면 1. Increment/Decrement의 필수 작성, 2. end if;와 end loop;를 통한 Conditional Expression/Loop Statement의 종료를 선언하는 부분을 보다 유의해야 하겠다. 또한, increment/Decrement를 빠트리고 작성하는 경우 Complie 시에는 문제를 일으키지 않지만 Runtime시에 [ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes] 에러가 발생한다.

---

Syntax: Continue & Exit Statement

<<Continue & Exit Statement>>
Syntax of countinue Statement:
BEGIN
Increment/Decrement
continue when Condition;

Syntax of exit Statement:
BEGIN
exit when Condition;
Increment/Decrement

DECLARE
n number;
BEGIN
n := &number;
if n between 1 and 3 then
while n < 30 loop
n := n * n;
--e.g.EXIT 
exit when n > 15;
--e.g. CONTINUE
continue when mod(n, 2) = 0;
...

여기서 주의할 점, continue Statement는 증감식 후위에 작성하여야 하며 exit Statement는 증감식의 전위에 작성하여야 한다. 전자의 경우 증감식 전위에 작성하게 되면 증감식이 종료된 후 다시 해당 구문으로 회귀하므로 해당 구문이 종료될 수 없게 되며, 후자의 경우 주어진 증감식이 이미 끝난 후에 탈출 구문이 실행되므로 작성 의미가 없게 된다.

---

Syntax: for in, for in reverse loop Statement

<<for in loop Statement>>
Syntax of the for in/for in reverse loop:
BEGIN
for Num in FirstVal..LastVal loop
--OR
for Num in reverse FirstVal..LastVal loop
Execute-Statement
end loop;
END;
/

BEGIN
    for m in 5..10 loop
    --OR
    for m in reverse 5..10 loop
    dbms_output.put('result: '|| m);
    end loop;
    dbms_output.new_line;
END;
/

Increment/Decrement를 DECLARE Clause에 선언하지 않아도 되며, 자동 Increment/Decrement(reverse)로 선언한 FirstVal부터 LastVal까지 무조건 1씩 증가 또는 1씩 감소한다. 문법상 FirstVal과 LastVal 사이에 point가 2개인 것을 유의하여 작성하여야 한다.

---

Syntax: Generate Random Numbers

<<Generate Random Numbers>>
Syntax of the random.value:
DECLARE
ValName DataType;
BEGIN
ValName := dbms_random.value(MinVal, MaxVal);
--If wanna get Integer-Val,
--Must use to trunc to random.Val
Execute-Statement(add'l)
END;
/

DECLARE
    rnd number;
BEGIN
    rnd := trunc(dbms_random.value(1, 101));
    dbms_output.put_line(rnd);
END;
/

---

또한 Sequence와 더불어 응용한 다음의 예제도 있다:

[요구사항]
새로운 테이블을 생성하고 1부터 100까지의 난수를 발생시킨 후,
Sequence를 통해 채번한 정수와 난수를 테이블에 삽입하라.

<<Insert Random Numbers and Sequence Number to Table>>

--CREATE A TABLE FIRST:
create table tbl_rnd(
pk_num number constraint PK_NUM primary key,
rnd number);
--CREATE A SEQUENCE:
create sequence num_seq;
--INSERT THEM TO TABLE
DECLARE
    rndno number;
    sum_no number := 0;
BEGIN
	for no in 1..50 loop
    rndno := trunc(dbms_random.value(1, 101));
    insert into tbl_rnd values(num_seq.nextval, rndno);
    sum_no := sum_no + rndno;
    dbms_output.put_line(sum_no);
    end loop;
    commit;
END;
/
--TESTCODE
select * from tbl_rnd;
select sum(rnd) from tbl_rnd;

주: sum은 예약어이므로 변수명으로 사용하면 에러가 발생한다.

---

Remark: Codying Dynamic SQL Statement

<<Execute DDL Statement in PL/SQL Statement>>
Syntax of the Dynamic SQL DDL:
execute immediate 'EXECUTE-STATEMENT-ON-HERE'

execute immediate 'truncate table tbl_rnd';

PL/SQL 구문 자체적으로는 내부에서 DDL을 실행하는 것이 불가능하며, 이러한 경우에는 Dynamic Coding(동적 코딩) 문법을 별도로 작성하여 실행할 수 있다. 문법이 다소 독특한데, 명령어 execute immediate의 후위의 Quotation Marks(' ') 내부에 실행시킬 명령문을 작성한 후 세미콜론을 붙여주면 된다.

---