PL/SQL (1) 개요, 조건문, 반복문, 예제

본 포스팅은 Oracle 11g를 이용하고있으며, Tool은 Toad for Oracle을 이용했습니다.

Procedural Language extension to Structured Query Language

• SQL과 일반 프로그래밍 언어의 특성을 결합한 언어
• 변수, 상수 선언 가능
• 조건문, 반복문 사용 가능

DBMS의 역할이 커지면서 SQL을 넘어서는 일반 프로그래밍 언어가 처리할 수 있는 기능들이 필요하게되었는데,

1989년 Oracle 6 버전부터 PL/SQL 이 등장 하게 되었다.

 

💡 변수가 없으며, 한번에 하나의 명령문만 사용 가능 (트래픽 증가) 그리고
제어문이 없으며 (IF, LOOP) , 예외처리가 없었던 SQL의 단점들을 개선해준다.

 

✅ 왜 PL/SQL을 써야 할까?

 

C나 JAVA에 비해 유리한 점은 애플리케이션 서버 측에서 수행되는 것이 아니라 데이터베이스 엔진 쪽에서 수행된다는 데 있는데, 데이터와 가장 가까운 곳에 위치하고 있으므로 애플리케이션 서버로 데이터를 주고 받는 데 따르는 네트워크 트래픽이 최소화된다는 장점이 있다.

 

1. SQL과 긴밀히 통합되어 있다.
2. 성능이 뛰어나다.
3. 생산성 및 관리의 용이성
4. 이식성

❗ 주의

SQL로 처리 가능하다면 SQL 처리하는 것이 가장 효율적.

데이터베이스에는 SQL을 처리하는 SQL 엔진과 PLSQL을 처리하는 PLSQL 엔진이 따로 존재한다.

 

사용에 따른 결과를 충분히 주의해서 사용해야 한다는 의미이고 조인 SQL을 단순화하려는 목적으로는 사용하지 말자

 

종류

 

익명 블록 : 이름이 없는 PL/SQL Block을 말한다.

이름 있는 블록 : DB의 객체로 저장되는 블록이 있다.

   - 프로시저 : 결과값을 반환하지 않음

   - 함수 : 리턴 값을 반드시 반환해야 하는 프로그램

   - 패키지 : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음

   - 트리거 : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록

 

 

✔ 선언부, 실행부, 예외처리부가 하나의 PL/SQL 블록을 구성하고, 오라클은 이 블록 단위로 처리한다.

 

기본 구조

선언부 ( Declarative part )

- declare키워드 사용

- 실행부에서 사용할 변수나 상수를 선언하는 부분

변수와 상수 선언은 반드시 선언부에서만 선언해야 함 

 

실행부 (Executable part)

- 실제 처리할 로직(logic) 부분 담당

- Begin으로 시작, End 로 끝

- 변수에 값 할당, 조건문, 반복문, sql문장 등을 처리

- PL/SQL의 엔진 역할

- 실행부에 여러 개의 sql 문장들을 순차적으로 사용하여 블록 단위로 한 번에 처리할 수 있다는 장점 

 

예외처리부(Exception-building part)

- Exception 키워드 사용

- 실행부에서 예외/각종 오류 가 발생했을때 처리하는 부분

오류가 발생할 경우 처리할 로직을 기술하는 부분

 

자료형

PL/SQL의 주요 자료형은 NUMBER, CHAR, VARCHAR2, DATE, TIMESTAMP가 있다.

 

💡  변수값을 저장하기 위해서는 := 를 사용
 := 의 좌측에는 변수
우측에는 값을 기술한다.

 

---

기본 예제

> dbms_output.put_line() - ()안에 명시한 내용을 출력하는 기능의 시스템 패키지

예제 1)

declare --선언부 - 변수를 선언하는 부분
counter number; 
begin   --실행부   - 처리할 로직을 넣는 부분
counter := 1;   --변수에 값 할당
--로직 처리
counter := counter/0;
if counter is not null then
    dbms_output.put_line('counter => '|| counter);
end if;
exception when others then  --예외처리부
--exception when ZERO_DIVIDE then
    dbms_output.put_line('0으로 나누면 안됩니다.');
end;

Toad 툴에서는 하단에 DBMS Output 탭을 아래와 같이 초록불 들어오게 클릭 후, 우측에 새로고침 버튼을 클릭하면 
dbms_output.put_line( '내용' ) 이 출력 된다.

counter := counter/0의 결과 => 0으로 나누면 안됩니다 출력

 

 

 

 

예제 2) 1~10 까지 for문을 이용하여 반복 처리

declare
i number;
result number;
begin
for i in 1..10 loop
result := i*2;
dbms_output.put_line(result);
end loop;
exception when others then  --예외처리부
    dbms_output.put_line('error!');
end;

 

위의 블록을 실행한 결과

 

 

 

예제 3) while 문 이용

declare
i number;
result number;
begin
i:=1;
while i<=10 loop
    result := i*3;
    dbms_output.put_line(i || '*3 => ' || result);
    i:=i+1;
end loop;
exception when others then  --예외처리부
    dbms_output.put_line('error!');
end;

위의 결과

 

✔ 변수, 상수 선언

변수명 데이터타입; --변수선언
변수명 constant 데이터타입; --상수선언
변수명 테이블명.컬럼명%type; --변수선언 
    => 해당 테이블의 해당 컬럼과 동일한 타입의 변수 선언

예)
name          varchar2(30);
curYear       constant       number := 2019; (값 할당 한것)
empno        employees.employee_id%type;

 

조건문

✅ 기본 문법

1) if문
if 조건1 then
문장1;
elsif 조건2 then
문장2; else
문장3;
end if;


2) case문
case
when 조건 then
             문장1;
else
             문장2;
end case;

 

조건문 예제

1) if 문 이용

declare
grade char;
result varchar2(50);
begin
grade := 'B';
if grade = 'A' then
    result := 'Excellent';
elsif grade = 'B' then
    result := 'Good';
elsif grade = 'C' then
    result := 'Fair';
elsif grade = 'D' then
    result := 'Poor';
else
    result := 'Not found!';
end if;

dbms_output.put_line(grade || '=>' || result);

exception when others then
    dbms_output.put_line('error!');
end;

위의 블록을 실행한 결과

2) case 문 이용

 

declare
grade char;
result varchar2(50);
begin
grade :='C';
case grade
    when 'A' then
        result := 'Excellent';
    when 'B' then
        result := 'Good';
    when 'C' then
        result := 'Fair';
    when 'D' then
        result := 'Poor';
    else    
        result := 'Not found';
end case;    

dbms_output.put_line('case문 : ' || grade || '=>' || result);
    
exception when others then
    dbms_output.put_line('error!');
end;

위의 case 문 블록 실행 결과

 

반복문

✅ 기본 문법

1)
Loop
exit when 조건;
     end loop;

2)
for 변수 in [reverse] 초기값..종료값 loop
     처리할 문장;
end loop;

3)
while 조건 loop
        처리할 문장;
end loop;

 

반복문 예제

 

1) loop문 이용

declare
i number;
result number;
begin
i:=1;
loop
    result := i*2;
    
    exit when result>20;    --조건을 만족하면 반복문 탈출
    dbms_output.put_line(result);
    
    i:=i+1;
end loop;

exception when others then
    dbms_output.put_line('error');
end;

위의 loop문 예제 실행 결과

 

 

2) for문 이용

declare
i number;
result number;
begin
for i in 1..10 loop
result := i*5;
dbms_output.put_line('i=' || i || ', result=' || result);
end loop;
dbms_output.put_line('-------for문 reverse 이용----------');
result:=0;
for i in reverse 1..10 loop
    result := i*4;
    dbms_output.put_line('i=' || i || ', result=' || result);
end loop;

exception when others then
    dbms_output.put_line('error');
end;

위의 for문 예제 실행 결과

 

 

3) while문 이용

declare
i number;
result number;
begin
i:=1;
result:=0;
while result < 20 loop  -- 20까지 결과를 찍는다..
    result := i*2;  --2, 4,..16,18, 20
    dbms_output.put_line(result);  --2, 4,...16,18, 20
    
    i:=i+1; --2,3,...9,10,11 
    --*i=11이 되었고 계산을 하려니까 result=20 이라고 했으니까 loop 빠져나감*
end loop;

exception when others then
    dbms_output.put_line('error');    
end;
--실제 데이터엔 저장되어있지 않음...

위의 while문 실행 결과

 

 

---

참고 링크 

blog.naver.com/gwise/220870689217

PL/SQL을 사용하는 이유?