PL / SQL 에 대하여 (DBeaver 사용)

⭐️ PL / SQL 이란 ?

오라클 자체에 내장되어 있는 절차적 언어이다.

SQL 문장 내에서 변수의 정의 , 조건(IF) , 반복 (FOR , WHILE) 등을 지원하여 SQL 단점을 보완한다.

다수의 SQL 문을 한번에 실행도 가능 ‼️

 

[구조]

[선언부] : DECLARE로 시작 , 변수나 상수를 선언 및 초기화 하는 부분.

실행부 : BEGIN으로 시작 , SQL문 또는 제어문등의 로직을 기술하는 부분.

[예외처리부] : EXCEPTION으로 시작 , 예외발생시 해결하기 위한  구문.

 

---

 

 

사용하기 앞서 DBeaver는 따로 설정하지 않아도 되지만 다른 개발환경을 사용한다면

SET SERVEROUTPUT ON;

 

위 코드를 입력해야지 출력문을 사용 할 수 있게된다. 자바의 Scanner처럼 선언 해줘야지 사용 가능.

 

[코드]

--출력문
BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
END;
/

 

위와 같이 출력문을 사용할 수 있게된다 ‼️ 그리고 END; 이후에 / 로 문장이 끝났음을 표시해주자.

 

만약 OUTPUT 창이 보이지 않는다면 Ctrl + Shift + O 를 누르면 보인다 

 

---

⭐️ 래퍼런스 타입 변수 선언 및 초기화

래퍼런스 타입 변수 선언 및 초기화 ( 어떤 테이블의 어떤 컬럼의 데이터타입을 참조해서 그 타입으로 지정)

 

 

[코드]

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY
    INTO EID, ENAME, SAL
    FROM EMPLOYEE
    WHERE EMP_ID = ${사번};
    
    DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('SAL : ' || SAL);
END;
/

 

ORACLE Devloper를 사용할 경우 WHERE 절에 ${사번} 대신  &사번 이라고 작성해주면 된다.

 

래퍼런스 타입으로 선언하게 되면 위 코드와 같이 EMPLOYEE 테이블에 접근하여 해당 데이터 타입을 

참조해서 사용할 수 있게 된다.

 

${사번} 은 자바의 SCANNER , 자바스크립트의 ALERT 창 등과 같이 입력 할 수 있는 칸이 나온다.

 

직접 입력하는 칸이 나옴

 

해당 칸에 200을 입력하면 EMP_ID = 200 인 사원의 EID , ENAME , SAL이 출력되게 된다.

 

EMP_ID가 200인 사원의 출력문

 

---

 

⭐️ ROW 타입 변수 선언 

ROW 타입 변수 선언 (테이블의 한 행에 대한 모든 컬럼값을 한번에 담을 수 있는 변수)

 

[코드]

DECLARE
	E EMPLOYEE%ROWTYPE;
BEGIN
	SELECT *
	INTO E
	FROM EMPLOYEE e 
	WHERE EMP_ID = ${사번};

	DBMS_OUTPUT.PUT_LINE('사원명 : ' || E.EMP_NAME);
	DBMS_OUTPUT.PUT_LINE('급여 : ' || E.SALARY);
	DBMS_OUTPUT.PUT_LINE('보너스 : ' || E.BONUS);
END;
/

 

위의 래퍼런스 타입은 지정한 컬럼의 데이터 타입을 참조해서 가져온다면 , ROW 타입은

한 행에 대한 모든 컬럼값들을 한번에 가져올 수 있다.

 

---

⭐️ IF 

 

IF 문은 다른 언어들과 유사하다 . 

 

[표현법]

1) IF 조건식 THEN 실행내용 END IF; (IF문 단독으로 사용할 때)

2) IF 조건식 THEN 실행내용 ELSE 실행내용 END IF; (IF-ELSE)

3) IF 조건식1 THEN 실행내용1 ELSIF 조건식2 THEN 실행내용2.... [ELSE 실행내용] END IF;

 

 

---

 

⭐️ 반복문

1. BASIC LOOP문

 

[표현식]

LOOP 
반복적으로 실행할 구문
반복문을 빠져나갈 수 있는 구문
END LOOP;
	
* 반복문을 빠져나갈 수 있는 구문 (조건 아무데나 작성가능)
1) IF 조건식 THEN EXIT; 
2) EXIT WHEN 조건식;

 

[코드]

DECLARE
	I NUMBER := 0;
BEGIN
	LOOP
		--IF I = 10 THEN EXIT; END IF;
		I := I + 1;
        	DBMS_OUTPUT.PUT_LINE(I);
	
		EXIT WHEN I = 10;
	END LOOP;
	
END;
/

 

IF 조건식 THEN EXIT; 또는

EXIT WHEN 조건식; 으로 반복문 빠져나오기 가능 ‼️

SQL에서는 := 가 다른 언어의 = 이라고 생각하면 된다. EX ) i = i  + 1

 

---

 

 

2. FOR LOOP문 (횟수가 정해진 반복에 주로 사용)

 

[표현식]

FOR 변수 IN [REVERSE] 초기값..최종값
LOOP
	반복적으로 실행할 문장;
END LOOP;

 

[코드]

BEGIN
	FOR I IN 1..10
	LOOP
		DBMS_OUTPUT.PUT_LINE(I);
	END LOOP;
	
END;
/

BEGIN
	FOR I IN REVERSE 1..10
	LOOP
		DBMS_OUTPUT.PUT_LINE(I);
	END LOOP;
	
END;
/

 

REVERSE를 사용하면 1 ~ 10이 출력되는것이 아닌 , 10 ~ 1 로 반대로 출력이 된다.

 

---

 

3. WHILE LOOP문

 

[표현식]

WHILE 반복문이 수행될 조건
LOOP
	반복할 명령어
END LOOP;

 

[코드]

DECLARE 
	I NUMBER := 0;
BEGIN
	WHILE I < 10
	LOOP
		DBMS_OUTPUT.PUT_LINE(I);
		I := I + 1;
	END LOOP;
END;
/

 

 

다른 언어에 있는 FOR , WHILE이랑 비슷하다. 횟수가 정해져있으면 FOR , 그렇지 않으면 BASIC LOOP 

또는 WHILE LOOP문을 이용하자 ‼️

 

---

⭐️ 예외 처리부

예외 (EXCEPTION) : 실행중 발생하는 오류

 

[표현식]

EXCEPTION
	WHEN 예외명1 THEN 처리구문1;
	WHEN 예외명2 THEN 처리구문2;
	...
		
	* 시스템 예외(오라클에서 미리 정해둔 예외)
	- NO_DATE_FOUND : SELECT한 결과가 한 행도 없을 때
	- TOO_MANY_ROW : SELECT한 결과가 여러행일 경우
	- ZERO_DIVIDE : 0으로 나눌 경우
	- DUP_VAL_ON_INDEX : UNIQUE제약조건 위배 
	...

 

[코드]

DECLARE
	RESULT NUMBER;
BEGIN
	RESULT := 10 / ${숫자};
	DBMS_OUTPUT.PUT_LINE('결과 : ' || RESULT);
EXCEPTION
	--WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('나누기 연산시 0으로 나눌수 없습니다.');
	WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('예외가 발생하였습니다.');
END;
/

 

위 코드와 같이 오류 명을 알면 WHEN 예외명을 사용해주고 , 만약 예외명을 모른다면

OTHERS(모든 예외처리 받음) 사용하여 예외 처리를 진행해도 된다 ‼️

 

 

---

 

PL / SQL은 다른 언어에도 존재하는 IF, FOR ,WHILE , EXCEPTION이
존재한다. 하지만 추후에 JAVA와 연동하면 SQL에서 PL / SQL을
사용할 일이 많이 줄어들 것 같다‼️