[Oracle] PL/SQL

반복문

 

LOOP문 예제1:

CREATE OR REPLACE PROCEDURE ex_sum
IS
l_num NUMBER := 0;
l_sum NUMBER := 0;
BEGIN
LOOP
l_num := l_num + 1;
l_sum := l_sum + l_num;

DBMS_OUTPUT.PUT_LINE(l_num ||' '|| l_sum);
EXIT WHEN l_num = 10;
END LOOP
END;

 

FOR문 예제2:

CREATE OR REPLACE PROCEDURE ex_sum
IS
l_num NUMBER := 0;
l_sum NUMBER := 0;
BEGIN
-- i는 인덱스이고 '시작값..종료값'까지 1씩 증가하면서 반복
FOR i IN 1..10 LOOP
l_num := l_num + 1;
l_sum := l_sum + l_num;

DBMS_OUTPUT.PUT_LINE(l_num ||' '|| l_sum);
END LOOP
END;

 

실행:

EXEC ex_sum

 

결과:

---

프로시저: 트랜잭션을 수행할 수 있는 PL/SQL 블록

- DB 내에서 이미 컴파일 되어 있는 형태로 저장

 

프로시저와 함수의 차이 ?

- 함수는 리턴값이 존재하지만, 프로시저는 리턴값 X

 

프로시저

CREATE OR REPLACE PROCEDURE output_department
(p_dept_id IN DEPARTMENTS.DEPARTMENT_ID%TYPE)
IS
-- LOCAL 변수 선언
l_department_name DEPARTMENTS.DEPARTMENT_NAME%TYPE := NULL;
l_manager_id      DEPARTMENTS.MANAGER_ID%TYPE := NULL;
l_location_id     DEPARTMENTS.LOCATION_ID%TYPE := NULL;
BEGIN
-- PARAMETER 변수로부터 부서 아이디를 받아 해당 부서의 정보를 질의
SELECT department_name, manager_id, location_id
INTO l_department_name, l_manager_id, l_location_id
FROM DEPARTMENTS
WHERE department_id = p_dept_id;
END output_department;

- SELECT 절에서 질의된 결과를 INTO 절의 로컬 변수에 삽입한다. 

---

커서

- 여러 행에 대해 FETCH를 하기 위해서는 LOOP문이 필요함

- SELECT INTO 절은 1개의 행에 대한 정보만 받아올 때 사용할 수 있으므로, 2개 이상의 행의 정보를 받아올 때 커서를 사용한다.

DECLARE
-- 커서 선언
CURSOR 커서이름
IS SELECT 구문;

BEGIN
-- 커서 열기
OPEN 커서이름;

-- 커서로부터 데이터 읽기
-- 여러 행에 대한 결과를 얻기 위해서는 LOOP문이 필요함
[LOOP]
	FETCH 커서이름 INTO 로컬변수;
	[EXIT WHEN 커서이름%NOTFOUND;]
[END LOOP;]
-- 커서 닫기
CLOSE 커서이름;
END;

---

예외 처리

DECLARE
-- 예외 선언
-- 예외 이름 EXCEPTION;
BEGIN
-- 예외 발생
-- RAISE 예외 이름;
EXCEPTION
-- 예외 처리
-- WHEN 예외 이름 THEN 구문;
END;

 

Oracle에서 제공하는 예외

예외	설명
NO_DATA_FOUND	SELECT INTO 구문 실행 후 반환되는 행이 없는 경우
TOO_MANY_ROWS	SELECT INTO 구문이 하나 이상의 행을 반환하는 경우
VALUE_ERROR	데이터의 변환 과정에서의 오류 또는 선언된 변수가 데이터를 저장하기에 크기가 작은 경우
NOT_LOGED_ON	Oracle에 접속하지 않은 채 데이터베이스를 Call한 경우
INVALID_CURSOR	Cursor의 동작이 부적절한 경우 (Open하지 않은 커서를 닫음)
CURSOR_ALREADY_OPEN	이미 열려있는 Cursor를 열려고 한 경우
DUP_VAL_ON_INDEX	Unique Index가 설정된 컬럼에 중복된 값을 입력하는 경우

 

예시:

DECLARE
-- 예외 선언
exc_test EXCEPTION;

BEGIN
-- 예외 발생
-- RAISE 예외 이름;
IF(p_num IS NULL) THEN
RAISE exc_test;
END IF;

EXCEPTION
-- 예외 처리
-- WHEN 예외 이름 THEN 구문;
WHEN exc_test THEN
DBMS_OUTPUT.PUT_LINE('예외: p_num이 널 값 입니다.');
END;

---

패키지

: 업무와 관련된 Stored Procedure 및 Stored Function을 관리하고, 이를 패키지 단위로 배포할 때 사용한다.

 

선언부:

-- 패키지 선언부
CREATE OR REPLACE PACKAGE 패키지명 IS
PROCEDURE 프로시저1;
PROCEDURE 프로시저2;
FUCTION 함수1(매개변수 데이터타입) RETURN 리턴타입;
END 패키지명;

 

본문:

-- 패키지 본문
CREATE OR REPLACE PACKAGE BODY 패키지명 IS
PROCEDURE 프로시저1
IS

BEGIN

END 프로시저1;
----------------------
PROCEDURE 프로시저2
IS

BEGIN

END 프로시저2;
----------------------
FUNCTION 함수1(매개변수 데이터타입) RETURN 리턴타입
IS

BEGIN

END
RETURN 데이터;
END 함수1;
END 패키지명;

 

실행:

-- 패키지 실행부
EXEC 패키지명.함수1(매개변수);
EXEC 패키지명.프로시저2;

---

트리거

:  DBMS에서 데이터의 입력, 갱신, 삭제 등의 이벤트가 발생할 때 마다 자동적으로 수행되는 사용자 정의 프로시저

- 사용 예시: 무결성 제약조건을 지키게 하거나, 로그 테이블에 변경 기록을 남길 수 있다.

- 트리거는 TABLE에 관해서만 정의될 수 있다. (VIEW는 트리거 적용 안됨)

- 트리거는 트랜잭션 제어어(TCL) 사용할 수 없다.

- 트리거가 걸려있는 대상이 COMMIT이나 ROLLBACK을 할 때, 트리거의 작업도 같이 적용된다.

CREATE OR REPLACE TRIGGER 트리거이름
-- 트리거 실행 타이밍
[BEFORE | AFTER]
-- 트리거 실행 이벤트
[INSERT | UPDATE | DELETE]
ON 테이블이름
-- 행마다 실행 또는 한번만 실행
[FOR EACH ROW]
[DECLARE]

BEGIN
-- 1개 이상의 SQL 구문을 포함해야함 아니면 오류남

IF INSERTING THEN END IF;
IF UPDATING THEN END IF;
IF DELETING THEN END IF;

END;

 

:OLD.컬럼이름 -> 데이터가 추가,변경,삭제 되기 전의 값

:NEW.컬럼이름 -> 데이터가 추가,변경된 후의 값