쌍용강북교육센터 국비 학원 Day 32일차 Oracle (PL/SQL문, Procedure, Function, 제어문)

 

쌍용 강북 교육센터 국비학원 Day 32일차

 

PL/SQL

PL/SQL (Oracle's Procedural Language extension to SQL) 은 SQL의 확장된 개념으로 오라클에서 지원하는 프로그래밍 언어의 특성을 수용한 SQL의 확장한 절차적 언어이며, PL/SQL 블럭내에서 SQL의 DML(데이터 조작어) 문과 Query(검색어)문, 절차형 언어(IF, LOOP) 등을 사용하여 절차적 프로그래밍을 가능하게 한 강력한 트랜잭션 언어이다.

 

 

 

기본 PL/SQL Block 구조

 

 

영역	설명	옵션/필수
DECLARE (선언부)	PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분 -> 변수/상수/커서 등 을 선언	옵션
BEGIN (실행부)	절차적 형식으로 SQL문을 실행할수있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술할수있는 부분	필수
EXCEPTION  (예외 처리부)	PL/SQL문이 실행되는 중에 발생할 수 있는 에러 or 예외를 처리하는 부분	옵션
END (실행문 종료)	구문이 종료되는곳	필수

 

 

DECLARE (선언부)

Declare 문으로 시작하며 블록에서 사용될 변수, 상수, 커서, 예외를 선언하는 부분으로 필요하지 않으면 생략할수 있는 섹션이다.

 - 변수 선언

 - 상수 선언

 - 커서 선언

 - Exception 선언

 

 

BEGIN (실행부)

Begin 문으로 시작하여 End ; 문으로 종료하며 수행될 작업의 몸체이다.

SQL문, 제어문, 반복문, 커서속성 등을 이용하여 블록에서 실행할 몸체를 구성할 수 있으며 생략할 수 없는 필수섹션이다.

 - Select / Update, Insert, Delete

 - IF 문

 - Loop 문

 - 커서속성

 

 

EXCEPTION (예외 처리부)

예외처리 섹션은 End ; 문 바로 앞에 위치하며 미리정의된 예외를 처리하고 명시된 조건이 발생할 경우에 취할 작업을 정의하는 선택적 섹션이다.

 - 미리 정의된 예외

 - 사용자 정의 예외

 - Exception 함수 (sqlcode, sqlerrm)

 

 

 

 

 

PL/SQL Block의 종류

 

익명 블록

이름이 없는(declare) PL/SQL Block을 말한다. DB에 저장되지 않고 사용자가 필요할 때마다 반복적으로 작성, 실행

 

이름 있는 블록

생성 이후 DB에 정보가 저장됨. 인자를 받아서 호출되고 실행하며 로직을 처리하고 블록의 흐름을 제어

Procedure (프로시저) - 리턴 값을 하나 이상 가질 수 있는 프로그램을 말한다.

Function (함수) - 리턴 값을 반드시 반환해야 하는 프로그램을 말한다.

Package (패키지) - 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음을 말한다.

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

 

※ 오라클에서 화면 출력을 위해서는 PUT_LINE이란 프로시저를 이용 

 

 

 

 

변수 선언

 

블록내에서 변수를 사용하려면 선언부(DECLARE)에서 선언해야하며 변수명 다음에 데이터 타입을 기술해야 한다.

 

 [문법]

변수명 [CONSTANT] 데이터타입 [NOT NULL] [:=DEFAULT expression] ;

 

CONSTANT - 상수 (초기값을 반드시 지정해야 함)

NOT NULL - 값을 반드시 포함

expression - 리터럴(상수), 다른 변수, 연산자나 함수를 포함하는 표현식

 

변수는 서로 다른 블럭에서라면 동일한 이름을 가질 수 있고, 변수명은 블럭에 사용된 테이블의 칼럼명과 같아서는 안된다.

 

 

스칼라 - 변수 Type을 선언할때 꼭 명시적으로 작성하지 않고 사용하는 방법도 있다.

 

%TYPE 을 이용한 변수선언

해당 테이블의 컬럼 속성을 지정하여 그대로 들고 오는 형태이다.

[문법] - 변수명 테이블명.컬럼명%TYPE

 

%ROWTYPE 을 이용한 변수선

해당 테이블이나 뷰의 컬럼 속성을 그대로 들고 오는 형태이다. 

[문법] - 변수명 테이블명%ROWTYPE

 

 

 

 

변수 대입 방법

 

 - 명시적인 값 대입

변수값을 대입하기 위해서는 := (콜론 =) 를 사용한다.

변수명 := 값 ;

 

 - SELECT 문을 이용하여 값 대입

기존 SELECT 문과는 다르게 INTO절이 추가 된다. INTO절에는 조회 결과 값을 저장할 변수를 기술. select 문은 INTO절에 의해 하나의 행만을 저장 가능

 

※ SELECT 다음에 기술한 컬럼은 INTO 절에 있는 변수와 1:1로 대응해야하기 때문에 개수와 데이터 타입, 길이를 일치시켜야함

 

 

 

 

Procedure (프로시저)

리턴 값을 하나 이상 가질 수 있는 작업을 수행하는 PL/SQL블럭으로, 반복실행을 위한 DB객체로 DB에 저장된다. 매개변수를 가질 수 있고 호출될 수 있으며 재활용성과, 유지가능성을 높여준다.

 

 

[문법]

create or replace procedure 프로시저명

(파라미터변수명 IN 파라미터변수의타입)

is

   변수선언 ;

begin

   실행문 ;

end 함수명 ;  

 

 

Procedure 생성 예제

 

 

또한 매개변수 없이 Procedure를 생성하지 않고 Anonymous Procedure (이름이 없는 익명 프로시저) 로 일회용으로 사용할 수 있다.  (DECLARE)

 

 

 

 

 

 

Function (사용자 정의 함수)

매개변수를 가질 수 있고, 리턴 값을 반드시 반환해야 하는 PL/SQL 블록이다. 반복실행을 위한 DB객체로 DB에 저장되고

선언부와 실행부에 return절이 포함되어 있어야 한다. (Insert, Update, Delete 명령어는 허용되지 않는다)

 

 

[문법]

create or replace function 함수명

(파라미터변수명 IN 파라미터변수의타입)

return 리턴되어질 타입

is

   변수선언 ;

begin

   실행문 ;

    return 리턴되어질 값 ;

end 함수명 ;  

 

 

 

 

 

 

IF 문

[문법]

IF        조건1 then 실행문장1 ;

ELSIF 조건2 then 실행문장2 ;

ELSIF 조건3 then 실행문장3 ;

ELSE                    실행문장4 ;

END IF ; 

 

조건 - BOOLEAN 변수 또는 표현식 (TRUE, FALSE 또는 NULL)

ELSIF = ELSE IF 와 같은말

 

 

LOOP 반복문

일련의 문장들을 여러번 반복 수행하는 문장으로 3가지 종류가 있다.

 

1. 기본 LOOP문

 

기본루프를 통해 문장을 반복. 루프에 들어갈 때 조건이 이미 일치하더라도 최소 한 번은 문장이 실행됨.

 

LOOP
          반복해야할 실행문장 ;
EXIT WHEN 탈출조건 ;   (탈출조건이 참 이라면 LOOP 를 탈출한다.)
END LOOP ;

 

 

2. FOR LOOP문

 

값이 증가하거나 감소하면서 정해진 값에 도달하면 탈출하는 반복문 (REVERSE는 감소하는 반복일때 사용)

 

FOR 변수 IN [REVERSE] 시작값..마지막값 LOOP
           반복해야할 실행문장 ;

END LOOP ;

 

 

3. WHILE LOOP

 

조건이 TRUE가 아닐때 까지 반복. NOT 연산자로 반대의 뜻을 이용

 

WHILE  조건  LOOP
       반복해야할 실행문장 ;   (조건이 참이라면 실행함. 조건이 거짓이 되어지면 반복문을 빠져나간다)
END LOOP ;
   
   
WHILE NOT 조건  LOOP
       반복해야할 실행문장 ;   (조건이 참이라면 반복문을 빠져나간다)
END LOOP;

 

 

 

 

 

 

---

참고 출처 : https://goddaehee.tistory.com/99