[ORACLE] PL/SQL (FUNCTION, PROCEDURE, PACKAGE, TRIGGER)

1. PL/SQL 개념

: Oracle's Procedural Language extension to SQL의 약자

-SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP,WHILE, FOR)등을 지원하며

-오라클 자체에 내장되어 있는 절차적 언어로 SQL의 단점을 보완

- PL/SQL 프로그램의 종류는 크게 Procedure, Function, Trigger로 나뉘어 진다.

 

1.2 PL/SQL 기능

1) 변수 선언

2) 비교 연산 처리

3) 반복 실행

 

1.3 PL/SQL 구조 

: 기본 구조 Block

[DECLARE 선언문]   --변수, 상수, 커서, 프로시저, 함수 등 선언 

BEGIN 실행부

[EXCEPTION 예외 처리문]

END;
/

Error 발생 시

실행을 중단하고 EXCEPTION절로 이동, 정상 종료

EXCEPTION절이 없으면 Error 메시지 출력 후 강제 종료

 

1.4 PL/SQL 특징

- 변수 상수 등을 선언/ SQL과 절차형 언어를 사용한다.

- 변수의 선언은 DECLARE절에서만 가능하며, BEGIN 실행부에서는 새 값을 할당 할 수 있다.

- 커서를 사용하여 여러 행을 검색하거나 처리할 수 있다.

 

2. FUNCTION

2.1 함수 개념

:  보통 값을 계산하고 결과를 반환하기 위해 사용

-구성이 프로시저와 유사하지만 IN파라미터만 사용할 수 있음

-반드시 반환 될 값의 데이터 타입을 RETURN문에 선언해야 함

-또한 PL/SQL 블록 내에서 RETURN문을 통해서 반드시 값을 반환해야 함

 

2.2 함수의 구조

CREATE OR REPLACE FUNCTION function_name (p_name IN VARCHAR2,…)

RETURN datatype

IS

BEGIN
    (실행할 문장…select ~ from ~)
    RETURN res;

END;

 

 

3. PROCEDURE

3.1 프로시저 개념

: 지정한 특정 로직을 처리하는 서브 프로그램

-데이터를 추출해 조작하고 다시 저장하거나 갱신할 때 주로 사용

 

3.2 프로시저 구조

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode1] datatype1,
  parameter2 [mode2] datatype2,
…)]
IS|AS 
Pl/SQL BLOCK;

 

4. PACKAGE

4.1 패키지 개념

: 기능 면에서 연관성이 높은 프로시저,함수,타입(레코드,컬렉션),커서 등을 모아둔 서브 프로그램

-패키지를 생성할 때는 패키지의 명세(specification)와 본문(body)를 선언

 

4.2 패키지 구조

 

패키지 명세

CREATE [OR REPLACE] PACKAGE package_name
IS | AS
[서브프로그램을 포함한 다양한 객체 선언]
END package_name;

 

패키지 본문

CREATE [OR REPLACE] PACKAGE BODY package_name
IS | AS
[패키지 명세에서 선언한 프로그램을 포함한 다양한 객체 정의]
[경우에 따라 패키지 명세에 존재하지 않는 객체 및 서브 프로그램도 정의 가능]
END package_name;

 

5. TRIGGER

5.1 트리거 개념

: 이벤트가 발생할 경우 자동으로 실행되는 기능을 정의하는 서브 프로그램

-데이터 변경과 관련된 일련의 정보를 기록해 둘 수 있어 여러 사용자가 공유하는 데이터 보안성과 안전성 문제가 발생했을 때 대처능력을 높일 수 있음

5.2 트리거 종류

5.3 트리거 구조

  CREATE [OR REPLACE] TRIGGER trigger_name
  BEFORE|AFTER (INSERT,UPDATE,DELETE ON table_name
  BEGIN
  	TRIGGER 처리 (다른 테이블 처리)
  END;
  /

 

 

[참고] 오라클에서 화면 출력을 위해서는 PUT_LINE이라는 프로시저를 이용

- DBMS_OUTPUT.PUT_LINE(출력할 내용)

 

- SET serveroutput ON 입력.

SET serveroutput ON

BEGIN 

    dbms_output.put_line('HELLO ORACLE!!');

END;

HELLO ORACLE 출력.