SQL 다루기(4) : PL-SQL, 예외처리, FUNCTION, PROCEDURE

1. PS/SQL

• 오라클사의 SQL 언어중 하나로 사용자가 정의하는 함수
• 익명 블럭을 사용하며 아래와 같은 4가지 부분으로 이어진다.
• 기본적으로 pl/sql 의 결과는 콘솔창에는 출력되지 않는다
  • 이를 해결하기 위해서는 아래의 명령어 한줄이 필수!!
• 또한 end 뒤에는 / 가 필수로 따라와야한다

SET SERVEROUTPUT ON;

영역 설명 옵션/필수

DECLARE(선언부)	PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분으로서 DECLARE 로 시작!! ⇒ 변수/상수/커서 등을 선언	옵션
BEGIN(실행부)	절차적 형식으로 SQL 문을 실행할 수 있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술 할 수 이는 부분이며 BEGIN 으로 시작	필수
EXCEPTION(예외 처리부)	PL/SQL 문이 실행되는 중 에러가 발생했을 때 이를 해결하기 위한 문장 기술 ⇒ TRY { ~~ }CATCH{~~~} 처럼	옵션
END	실행문 종료 뒤에 / 필수	필수

---

2. DECLARE : 변수 선언

• PL/SQL 에서 변수를 선언 후 대입할때는 := 를 사용하며, 구체적으로는 다음 구조를 사용한다
  • 변수명 자료형 := 초기값
• 같다 표시에는 그대로 = 인 듯

DECLARE
	-- 변수
	-- 변수명 자료형 := 초기값;
	VNO NUMBER := 20;
	VSTR VARCHAR2(20) := 'HONG';

BEGIN
	-- 선언한 변수 출력
	VNO := 10;
	DBMS_OUTPUT.PUT_LINE('VNO : ' || VNO); // PRINTLN 문
	DBMS_OUTPUT.PUT_LINE('VSTR : ' || VSTR);

END;
/

---

3. BEGIN : 실행부

• BEGIN 에서는 실제로 문장을 실행하는 실행부이다 ⇒ JAVA 로 따지면 MAIN 메소드 정도??
• 사용되는 기본 문법은 다음과 같다.

1) 반복문

• ONLY 초기값 ~ 최종값까지 1 씩 증가(+)만 가능
• REVERSE 를 쓰는 경우만 1씩 감소도 가능
• 2씩 증가, 홀수만큼 증가, 짝수만큼 감소 모두 불가능

FOR 반복할변수 초기값..최종값 LOOP
	반복할 문장
END LOOP;

----------------------------------
-- RESULT 변수에 1 ~ 100 까지의 합 담기
DECLARE 
	RESULT NUMBER := 0;

BEGIN
	FOR i IN 1..100 LOOP
		RESULT := RESULT+i;
	END LOOP;
	
	DBMS_OUTPUT.PUT_LINE(RESULT);
END;
/

2) BASIC LOOP : 무한 반복문

• 초기값과 죄종값 없이 LOOP 와 END 만 사용한다면 무한 반복문 사용이 가능하다.
• 이때 BREAK 와 같은 역할을 하는 것이 ‘ EXIT WHEN 탈출조건 ‘ 이다
  • 즉 EXIT WHEN 에서 탈출조건이 참인 경우 BREAK 된다

DECLARE
	i NUMBER := 0;
BEGIN
	-- BASIC LOOP문
	LOOP
		i := i+1;
	EXIT WHEN i>20; -- 탈출 BREAK 조건
		DBMS_OUTPUT.PUT_LINE('3 * ' || i || ' = ' || 3*i);
	END LOOP;

END;
/

3) WHILE : 조건 반복문

• WHILE 문은 해당 조건에 만족하는 동안 반복문이 실행된다 ⇒ 사실상 JAVA 의 그것과 거의 비슷하다
• 이때 BREAK 를 위해서 증감치를 WHILE 문 안에 쓰게 된다 ⇒ 이것도 비슷하네?

DECLARE 
	i NUMBER := 1;

BEGIN
	-- WHILE 조건을 참으로 만족할 때만 반복
	WHILE i<10 LOOP
	DBMS_OUTPUT.PUT_LINE('3 * ' || i || ' = ' || 3*i);
	i := i+1;
	END LOOP;
END;
/

4) IF : 조건 분기문

• 역시나 JAVA 의 그것과 거의 유사하다
• IF 조건 THEN ~ END IF;
• 단!! ELSE IF 는 JAVA 와는 다르게 ELSIF 로 쓴다

DECLARE
	VSCORE NUMBER := 60;
BEGIN
	-- 조건 분기문
	-- ELSIF => ELSE IF 문
	IF VSCORE >= 90 THEN
		DBMS_OUTPUT.PUT_LINE('A');
	ELSIF VSCORE >= 80 THEN
		DBMS_OUTPUT.PUT_LINE('B');
	ELSIF VSCORE >= 70 THEN
		DBMS_OUTPUT.PUT_LINE('C');
	ELSIF VSCORE >= 60 THEN
		DBMS_OUTPUT.PUT_LINE('D');
	ELSE
		DBMS_OUTPUT.PUT_LINE('F');
	END IF;
END;
/

---

4-1. DB 데이터 가져오기 : 단일 변수로 설정

• SELECT 문을 사용해서 DB 데이터를 가져올 수 있음
• 다만 이때 SELECT 구문 뒤에 INTO 구문을 추가해서 칼럼과 연결할 변수명을 선언해주어서 SELECT 문으로 가져온 값을 변수에 담아서 출력해야함
  • SELECT 컬럼1, 컬럼2 INTO 컬럼1변수, 컬럼2변수 FROM 테이블명 WHERE ~~;
• 마치 RESULT SET 처럼

-- 7788번 사원의 사번, 이름, 급여
DECLARE
	VEMPNO NUMBER;
	VENAME VARCHAR2(20);
	VSAL NUMBER;
BEGIN
	SELECT EMPNO, ENAME, SAL 
		-- INTO 변수명, 변수명, 변수명
		INTO VEMPNO, VENAME, VSAL
	FROM EMP
	WHERE EMPNO = 7788;

	DBMS_OUTPUT.PUT_LINE('사번 : ' || VEMPNO);
	DBMS_OUTPUT.PUT_LINE('이름 : ' || VENAME);
	DBMS_OUTPUT.PUT_LINE('급여 : ' || VSAL);
END;
/

 

4-2. DB 데이터 가져오기 : 전체 컬럼을 한번에 가져오기

• 기존에 DB 의 데이터를 가져오기 위한 방법은 컬럼에 맞는 변수를 하나하나 생성한 후 다시 INTO 구문을 통해 매칭 시켜서 가져오는 방법이다.
• 다만 이렇게하면 컬럼명이 10개면 10개의 변수를, 100개면 100개의 변수를 생성해야하는 문제가 있다.
• 이를 해결하기 위한 방법이 바로 테이블명%ROWTYPE
  • 테이블명%ROWTYPE 으로 변수를 선언하게 되면 해당 테이블의 모든 컬럼명을 멤버변수로하는 대표 변수가 생성된다.
  • 이후 [ 대표변수.컬럼명 ] 을 통해서 컬럼과 변수를 매칭시킨 후 값을 가져올 수 있다.

DECLARE
 -- 해당 테이블 전체의 컬럼명을 멤버변수로하는 대표 변수
 -- 이때 사용되는 변수명은 대표변수명.컬럼명
	VEMP EMP%ROWTYPE;
BEGIN
	SELECT * INTO VEMP
	FROM EMP
	WHERE EMPNO = 7788;

	-- 출력시에는 대표변수명.컬럼명
	DBMS_OUTPUT.PUT_LINE('사번 : ' || VEMP.EMPNO); 
	DBMS_OUTPUT.PUT_LINE('이름 : ' || VEMP.ENAME);
	DBMS_OUTPUT.PUT_LINE('MGR : ' || VEMP.MGR);
	DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || VEMP.DEPTNO);

END;
/

---

5-1. 값 입력받기

• &변수명 을 사용해서 콘솔창에서 변수값을 직접 입력받을 수 있다 → Scanner
• ACCEPT 변수명 PROMPT ‘프롬프트에서 나오는 말 : ‘ → 변수 입력할때 해당 문장이 출력된다
• 이때 변수 선언부에 적어주는 것이 가독성이 훨씬 높다

ACCEPT VNO PROMPT '검색할 사번 입력 : '

-- &변수명 을 통해서 변수값을 직접 입력할 수 있다
-- 일종의 Scanner
DECLARE
// 스칼라 변수
// 실행 시 사원 테이블의 사원번호 칼럼의 자료형과 동일한 타입
	VEMPNO NUMBER := &VNO;
	VENAME VARCHAR2(20);
	VSAL NUMBER;

BEGIN

	SELECT EMPNO, ENAME, SAL
		-- INTO 변수명, 변수명, 변수명
		INTO VEMPNO, VENAME, VSAL
	FROM EMP
	WHERE EMPNO = VEMPNO;

	DBMS_OUTPUT.PUT_LINE('사번 : ' || VEMPNO);
	DBMS_OUTPUT.PUT_LINE('이름 : ' || VENAME);
	DBMS_OUTPUT.PUT_LINE('급여 : ' || VSAL);

END;
/

 

5-2. 값 입력 받기 : 동적 자료형 선언

• 동적 자료형 선언은 테이블명.컬럼명: %TYPE
• 동적 자료형으로 선언하면 보다 편하게 변수를 선언하고 값을 가져올 수 있다

ACCEPT VNO PROMPT '검색할 사번 입력 : '

DECLARE
	VEMPNO EMP.EMPNO%TYPE := &VNO;
	VENAME EMP.ENAME%TYPE;
	VSAL EMP.SAL%TYPE;

BEGIN
	SELECT EMPNO, ENAME, SAL
		-- INTO 변수명, 변수명, 변수명
		INTO VEMPNO, VENAME, VSAL
	FROM EMP
	WHERE EMPNO = VEMPNO;

	DBMS_OUTPUT.PUT_LINE('사번 : ' || VEMPNO);
	DBMS_OUTPUT.PUT_LINE('이름 : ' || VENAME);
	DBMS_OUTPUT.PUT_LINE('급여 : ' || VSAL);

END;
/

---

6. 배열 선언하기

• PL/SQL 에서는 잘만 사용하면 배열도 선언&사용할 수 있다.
• 이때의 배열은 일반적인 INT[] 가 아닌 ARRAYLIST 형식의 크기가 지정되지 않은 가변형 배열이라고 생각하면 편한다.
• INDEX BY BINARAY_INTEGER 가 일종의 키워드로써 꼭 필요하다.

DECLARE
	-- PL/SQL TABLE 선언 : PL_TAB
	TYPE PL_TAB IS TABLE OF EMP.ENAME%TYPE --타입 선언
	INDEX BY BINARY_INTEGER;

	-- ArrayList<Integer> PL1 = NEW arrayList<Integer>();
	-- DB 상 ArrayList 배열 선언이라고 생각하면 됨
	-- 이때 변수명은 PL1 자료형이 PL_TAB
	PL1 PL_TAB;

BEGIN
	FOR i IN 1..20 LOOP
		PL1(i) := i||' : SCOTT';
	END LOOP;

	FOR j IN 1..20 LOOP
		DBMS_OUTPUT.PUT_LINE(PL1(j));
	END LOOP;
END;
/

---

7. 예외처리

• 예외명이 이미 존재하기 때문에 해당 예외명이 나타나면 어떤 것을 할 것인지 정의하면 된다
  • PRE DEFINE EXCEPTION : 미리 정의된 예외
    • NO_DATA_FOUND
    • TOO_MANY_ROWS
    • INVALID_CUSOR : 커서 사용법 부적합 → OPEN FETCH CLOSE 순서 오류?
    • DUP_VAL_ON_INDEX : PK, UK 제약이 존재하는 컬럼에 중복된 값은 허용되지 않음
    • 예외명이 존재 X, 따라서 [예외명 정의] [에러번호] [발생할시 처리 코드] 를 정의해야한다
    • 아래 사진에 나오는 -06502 가 에러번호
    • NON DEFINED EXCEPTION : 미리 정의되지 않은 예외
  • USER DEFINED EXCEPTION : 유저가 정의한 예외
    • 오류가 아닌 것을 예외로 처리예외는 대표적으로 아래 3가지

ORA 뒤에 -부터 5자리가 에러번호

• 예외 구문에 해당하는 TRY{ }CATCH{} 는 아래와 같은 방법으로 사용한다
  • 이때 주의점은 BEGIN = TRY 라고 생각하고 BEGIN 안에 들어가있는 문장들은 TRY 안에 들어간 문장이라고 생각하면 된다.
  • CATCH 의 경우 EXCEPTION 으로 대체한다.

ACCEPT VNO PROMPT '검색할 사번 입력 : '
DECLARE
	VEMPNO EMP.EMPNO%TYPE := &VNO;
	VENAME VARCHAR(20);
	VSAL NUMBER;

	-- 2. NON PREDEFINEED EXCEPTION 예외명 정의
	VEX1 EXCEPTION;
	-- 컴파일러 지시자 PRAGMA
	PRAGMA EXCEPTION_INIT(VEX1, -06502);

	-- 3. USER DEFINED EXCEPTION
	VEX2 EXCEPTION;

	-- 이외 기타 등등 에러를 잡기 위해서는 SQLERRM, SQLCODE 를 사용
	VMSG VARCHAR(20);
	VCODE NUMBER;

BEGIN -- BEGIN 전체가 통체로 TRY 문
	-- VASL := 'SCOTT'
	SELECT EMPNO, ENAME, SAL 
		INTO VEMPNO, VENAME, VSAL
	FROM EMP
	WHERE EMPNO = VEMPNO;

	DBMS_OUTPUT.PUT_LINE(VEMPNO || '  ' || VENAME || '  '|| VSAL );

	-- USER DEFINED EXCEPTION 은 예외가 아닌것을 예외로 만들 수 있다
	IF VSAL < 3000 THEN
			-- 예외 발생
			RAISE VEX2;
	END IF;

EXCEPTION -- 예외 발생시 여기로 CATCH 문
	--WHEN 예외명 THEN
	--실행문장1 => PREDEFINED EXCEPTION
	WHEN NO_DATA_FOUND THEN 
		DBMS_OUTPUT.PUT_LINE('그런 사원은 없어요!');

	--실행문장2 => NON PREDEFINED EXCEPTION
	WHEN VEX1 THEN
		DBMS_OUTPUT.PUT_LINE('숫자변수에 문자를 할당할수 X');

	-- 실행문장3 => USER DEFINED EXCEPTION
	WHEN VEX2 THEN
		DBMS_OUTPUT.PUT_LINE('급여 3000미만 사원은 사원이 아니에요!');
	
	-- 이외 기타등등 에러
	WHEN OTHERS THEN
		ROLLBACK;
		VMSG := SQLERRM; -- 에러 메시지를 VMSG 에 저장
		VCODE := SQLCODE; -- 에러 코드를 VCODE 에 저장

		DBMS_OUTPUT.PUT_LINE('위에 정의한 것 외 기타등등 에러');
		DBMS_OUTPUT.PUT_LINE('에러 코드 번호');

END;
/

---

8. Function : 사용자 정의 함수

CREATE FUNCTION 을 사용하면 SQL에서 내가 직접 함수를 정의하여 사용 할 수 있다.

return 이 반드시 필요함

• 생성시에는 CREATE OR REPLACE FUNCTION [함수명](매개변수로 받은 조건)
  • 아래에서 VEMPNO 를 매개변수로 받는다고 정의하였고(첫번째줄)
  • 다음으로 RETURN 에 내가 출력할 내용인 VSAL 를 함수의 결과로서 출력 RETURN 한다

1) 연봉 출력

-- 사원번호를 매개변수로 받으면 연봉 출력
CREATE OR REPLACE FUNCTION ANNSAL(VEMPNO EMP.EMPNO%TYPE)
	-- 최종 RETURN 값의 타입?
	RETURN NUMBER
IS
	-- 변수로 쓸 VSAL 의 타입 정의
	VSAL EMP.SAL%TYPE := 0;
BEGIN
	SELECT SAL*12+NVL(COMM, 0)
		INTO VSAL
	FROM EMP
	-- EMPNO 는 내가 함수의 매개변수로 보내준 VEMPNO 를 사용함
	WHERE EMPNO = VEMPNO;

	-- 계산된 연봉인 VSAL 을 RETURN
	RETURN VSAL;
END;
/

########## 결과 ###

SQL> SELECT ENAME, SAL, ANNSAL(EMPNO)
  2  FROM EMP;

ENAME             SAL ANNSAL(EMPNO)
---------- ---------- -------------
오로롸           1113         13356
드라이           2222         26664
SMITH             800          9600
ALLEN            1600         19500
WARD             1250         15500
JONES            2975         35700
MARTIN           1250         16400
BLAKE            2850         34200
CLARK            2450         29400
SCOTT            3000         36000
KING             5000         60000

2) 부서명 출력

-- 사원번호를 매개변수로 넣으면 부서 이름을 출력
CREATE OR REPLACE FUNCTION FIND_DNAME(VEMPNO EMP.EMPNO%TYPE)
-- 최종 RETURN 값의 타입?
	RETURN VARCHAR2
IS
	VDNAME DEPT.DNAME%TYPE := 0;
BEGIN
	SELECT D.DNAME
		INTO VDNAME
	FROM DEPT D NATURAL JOIN EMP E
	WHERE E.EMPNO = VEMPNO;

	RETURN VDNAME;
END;
/

---

PROCEDURE : 프로시저

• 프로시저는 함수와 비슷한 느낌
• 단 함수와는 다르게 계산 후 반환이 아닌 그냥 계산만 이라는 느낌
  • 즉 함수는 RETURN 이 반드시 필요하나 프로시저는 아님
• EXCUTE 프로시저명 으로 실행

1) 급여 향상 프로시저

-- 급여를 올리는 프로시저
-- 사번과 급여 향상 수치를 매개변수로 받음
CREATE OR REPLACE PROCEDURE RAISE_SAL(
	VEMPNO EMP.EMPNO%TYPE,
	VRATIO NUMBER)
IS
	VSAL EMP.SAL%TYPE := 0;
BEGIN
--	SELECT SAL*1.1
--		INTO VSAL
--	FROM EMP
--	WHERE EMPNO = VEMPNO;

--	UPDATE EMP
--	SET SAL = VSAL
--	WHERE EMPNO = VEMPNO;

-- 위에 두가지를 줄여서 한번에...!!

	UPDATE EMP
	SET SAL = SAL*(1+VRATIO/100)
	WHERE EMPNO = VEMPNO;
END;
/

## 실행 ##
SQL> EXECUTE RAISE_SAL(7839,20);

 

2) 랜덤 급여 향상, 감소 프로시저

CREATE OR REPLACE PROCEDURE LOTTO_SAL(
	VEMPNO EMP.EMPNO%TYPE,
	VRATIO NUMBER)
IS
	VSAL EMP.SAL%TYPE := 10;

-- DBMS_RANDOM.VALUE(N,I) 는 랜덤값 출력 함수
-- DBMS_RANDOM.VALUE(N, I) 라면 N < 출력 <I 사이의 값이 나온다
	RAN NUMBER := TRUNC(DBMS_RANDOM.VALUE(0, 10),0);
	-- RAN 의 값은 : 0 <= RAN < 10
	-- 왜냐하면 0.XXX부터 시작하나 TRUNC 함수를 통해서 소숫점을 삭제하였기 때문에
	-- 0 이 되서 0 이 RAN 값이 시작점
BEGIN

	IF RAN (6,7,8,9) THEN -- 6,7,8,9
		--UPDATE EMP
		--SET SAL = SAL*(1+VRATIO/100)
		--WHERE EMPNO = VEMPNO;
		RAISE_SAL(VEMPNO, VRATIO);
		DBMS_OUTPUT.PUT_LINE('증가 ' || RAN);

	ELSIF  RAN IN ( 2,3,4,5 ) THEN -- 2,3,4,5
		--UPDATE EMP
		--SET SAL = SAL*(1-VRATIO/100)
		--WHERE EMPNO = VEMPNO;
		DOWN_SAL(VEMPNO, VRATIO);
		DBMS_OUTPUT.PUT_LINE('감소 ' || RAN);

	ELSE -- 0,1
		DBMS_OUTPUT.PUT_LINE('꽝 ' || RAN);

	END IF;
END;
/

---

- 참고

[Oracle] PL/SQL 기초 (정의, 특징, 사용방법, 변수선언 방법)

[Oracle] PL/SQL 기초 (정의, 특징, 사용방법, 변수선언 방법)

[ Oracle ] Oracle PL/SQL 화면출력 & 간단 예제

[ Oracle ] Oracle PL/SQL 화면출력 & 간단 예제