11.24.(금) OracleDB(17): PL/SQL

Procedual Language extension to SQL

우리가 지금껏 배운 SQL 쿼리문은 그만의 장단점이 있다.

장점으로는 사용자가 이해하기 쉬운 직관적인 명령어, 간단한 작성법, 그리고 표준화된 문법이 있다.

하지만 반복처리를 할 수 없고, 비교처리도 할 수 없으며, 예외처리도 할 수 없다. 그리고 변수선언을 할 수 없기 때문에, 가변데이터를 다룰 수 없어서 한계가 뚜렷하다.

이런 SQL의 단점을 극복하기 위해 PL/SQL은 절차적 언어의 기능을 가지고 있다.

PROCEDURE

프로시저란 쿼리문을 메소드화 시키는 방법이다. VIEW, TABLE과 같이 CREATE로 생성할 수 있다.

CREATE OR REPLACE PROCEDURE emp_info(p_empno IN emp.empno%TYPE)
IS 
	-- 변수 선언
	s_empno emp.empno%TYPE;
	s_ename emp.ename%TYPE;
	s_sal emp.sal%TYPE;
BEGIN 
	-- 실행문
	SELECT empno, ename, sal INTO s_empno, s_ename, s_sal 
	FROM emp WHERE empno=p_empno;
	-- 출력문
	dbms_output.put_line('Employee Number -> '||s_empno);
	dbms_output.put_line('Employee Name -> '||s_ename);
	dbms_output.put_line('Employee Salary -> '||s_sal);
END;

emp_info가 프로시저 이름이고, 괄호 안에는 인풋값의 변수명과 데이터타입을 정의해준다. %TYPE은 그 앞에 붙은 필드와 동일한 데이터타입을 가진다는 뜻이다.

IS 뒤에는 변수를 선언할 수 있고, BEGIN과 END사이에 쿼리문을 비롯한 명령어를 작성할 수 있다.

이렇게 생성한 프로시저는,

-- 프로시져 확인하기
SELECT * FROM user_source;

이렇게 확인이 가능하고,

실행하려면 sqlplus에서는

SET serveroutput ON;	-- DBeaver에서는 Output콘솔창을 띄우면 된다. 사실상 불필요
CALL emp_info(7788);

프로시저를 통해 조회는 물론, 수정/삭제 또한 가능하다.

-- 사원 등록하는 프로시져 만들기
-- 사원번호, 이름, 부서번호를 입력받아 사원 등록하기
CREATE OR REPLACE PROCEDURE emp_insert(
	i_empno emp.empno%TYPE,
    i_ename emp.ename%TYPE,
    i_deptno emp.deptno%TYPE)
IS 
BEGIN 
	INSERT INTO emp(empno, ename, deptno) VALUES (i_empno, i_ename, i_deptno);
	dbms_output.put_line(i_ename || '사원이 등록되었습니다.');
END;

CALL emp_insert(5656, 'ZZZZ', 40);

OUT PARAMETER

프로시저에서의 처리결과를 외부로 내보내는 방법이다.

여기서부터 DBeaver에서는 실행이 안되는 경우가 종종 있으므로, 터미널의 sqlplus에서 실행하는 것을 추천한다.

우선 프로시저를 작성하자.

-- [문제] 사원번호를 입력받아 사원명과 급여를 내보내는 프로시저를 작성하라
CREATE OR REPLACE PROCEDURE emp_find(
	i_empno IN emp.empno%TYPE,
	o_ename OUT emp.ename%TYPE,
	o_sal OUT emp.sal%TYPE)
IS
BEGIN 
	SELECT ename, sal INTO o_ename, o_sal FROM emp WHERE empno=i_empno;
END;

여기서 프로시저의 입력값은 i_empno, 출력값은 o_ename과 o_sal이다.

입력값을 넣었을때, 그에 따른 리턴값을 f_ename, f_sal로 정했다.

-- 변수 선언하기
-- 사원명을 받을 변수
VARIABLE f_sal NUMBER;
-- 급여를 받을 변수
VARIABLE f_ename VARCHAR2(10);

CALL emp_find(7788, :f_ename, :f_sal);

PRINT f_ename;
PRINT f_sal;

예외처리

예외처리는 BEGIN~END사이에 쿼리문 뒤에서 처리할 수 있다.

CREATE OR REPLACE PROCEDURE employee_insert(
	i_username IN employee.username%TYPE,
	i_password IN employee.password%TYPE,
	i_tel IN employee.tel%TYPE,
	i_dept_code IN employee.dept_code%TYPE,
	i_pos_code IN employee.pos_code%TYPE,
	o_result OUT NUMBER)	-- insert문 처리결과 내보내기
IS 
BEGIN 
	o_result := 1;	-- o_result변수에 default로 1대입
	INSERT INTO employee(empno, username, password, tel, dept_code, pos_code)
	VALUES (emp_seq.nextval, i_username, i_password, i_tel, i_dept_code, i_pos_code);
	-- 예외처리
	EXCEPTION
		WHEN OTHERS THEN
			o_result := 0;	-- 예외발생 시 데이터추가 실패
END;

VARIABLE res NUMBER;

CALL employee_insert('JOHN', '1234', '010-8888-9999', 1, 2, :res);

PRINT res;

이렇게 프로시저를 통해서 쿼리문을 간단하게 메소드화 시켜보았다.

이렇게 함으로 외부에서 데이터처리를 할때 작성해야하는 쿼리문을 간소화시킨거라고 할 수 있다.

이제 이클립스에서 DB를 연결시켜 쿼리문을 날려보자.