PL/SQL ( Procedural Language / Structured Query Language )
선언적인 SQL 언어와는 달리 SQL을 절차적인 방식에 의해 사용하도록 설계된 언어이다.
1. PL/SQL
DECLARE (선언)
BEGIN (실행)
EXCEPTION (예외 처리)
END;
- DECLARE (선언) : 실행문에 사용될 변수나 커서, 사용자 정의 예외 등을 정의한다.
- BEGIN (실행) : 질의문(SELECT) 나 DML문 그리고 조건이나 제어문을 사용할 수 있다.
- EXCEPTION (예외처리) : 실행시 발생 가능한 예외를 정의한다.
1) PL/SQL 실행 절차
1. 개발 환경에서 작성
2. COMPILE
3. Debugging -> 1 -> 2 -> 3
4. 실행
2. Stored Procedure
SQL의 경우 Shared Pool에 같은 실행 계획이 있는지 확인하고 있다면, 해당 실행 계획을 실행한다. 따라서 수행한 SQL문을 가능한 Shared Pool에 두고 사용한다면 좋은 성능을 기대할 수 있다.
반면 Stored Procedure의 경우 한번 생성이 되면 데이터베이스 서버에 저장이 된다. 그리고 이후 다시 사용할 때에는 이미 Compile 과정을 거쳤기 때문에 실행만으로 결과를 얻을 수 있다.
즉, 다시 Compile을 하지 않고, 클라이언트 서버간 전송되는 크기가 상대적으로 복잡하고 긴SQL문 보다 작다. 이는 Network 전송량을 줄일 수 있는 장점이 있다.
1) Stored Procedure 작성
예문 ) 사원번호를 입력받아 해당 직무의 최소/최대 급여를 얻고, 급여의 분포에 따라 최대 급여에서 최소 급여의 차가 5,000 이하일 때는 LOW, 5,000 ~ 10,000 일때는 MIDDLE 10,000을 초과할 때는 HIGH로 표시한다.
1-1) 실행문의 주된 내용은 SQL이기 때문에 먼저 질의문을 통해 위 결과를 확인할 수 있는 SQL문을 작성한다.
SQL> SELECT employee_id, last_name, job_title, min_salary, max_salary
FROM employees e JOIN jobs j
ON e.job_id = j.job_id
AND employee_id = 205;
1-2) 여기에 최소/최대 급여의 차이에 따른 등급을 나타내기 위해 CASE문을 SQL문에 적용할 수 있다.
SQL> SELECT employee_id, last_name, job_title, min_salary, max_salary,
CASE WHEN max_salary – min_salary < 5000 THEN ‘low’
WHEN max_salary – min_salary < 10000 THEN ‘middle’
ELSE ‘high’
END CASE
FROM employees e JOIN jobs j
ON e.job_id = j.job_id
AND employee_id = 205;
1-3) 실행 결과를 확인 하였다면 Stored Procedure를 작성하여 보자. Parameter Variable로 사원번호를 입력받아 BEGIN ~ END에 위질의문을 넣고 로컬 변수를 선언하여 결과를 처리한다.
SQL> CREATE OR REPLACE PROCEDURE rank_Salaryofjob
(p_empid employees.employee_id%TYPE := 0)
IS
l_last_name employees.last_name%TYPE := NULL;
l_min_salary jobs.min_salary%TYPE := 0;
l_max_salary jobs.max_salary%TYPE := 0;
l_rank VARCHAR2(10);
BEGIN
SELECT employee_id, last_name, job_title, min_salary, max_salary,
CASE WHEN max_salary – min_salary < 5000 THEN ‘low’
WHEN max_salary – min_salary < 10000 THEN ‘middle’
ELSE ‘high’
END CASE
FROM employees e JOIN jobs j
ON e.job_id = j.job_id
AND employee_id = 205;
DBMS_OUTPUT.PUT_LINE(l_last_name||’ ‘||l_min_salary||’ ‘||l_max_salary||’ ‘||l_rank);
END rank_salaryofjob;
3. CURSOR
질의 수행 결과 반환되는 여러 행을 처리할 수 있도록 명시적 커서를 이용하고 있다. oracle에서 커서란 질의의 결과 얻어진 여러 행이 저장된 메모리 상의 위치를 나타낸다. 즉, PL/SQL에 내장된 일종의 포인트이며, 결과 SET의 행들을 하나씩 얻어 오는 작업에 이용된다.
1) 문법
DECLARE
-- 커서의 선언
CURSOR 커서이름 IS SELECT 구문;
BEGIN
-- 커서 열기
OPEN 커서이름;
-- 커서로부터 데이터 읽기
FETCH 커서이름 INTO 로컬변수;
-- 커서 닫기
CLOSE 커서이름;
END;
2) 예문
SQL> CREATE OR REPLACE PROCEDURE info_Hiredate (p_year IN CHAR := 0)
IS
l_emp employees%ROWTYPE;
-- 커서의 선언
-- CURSOR cursor_name IS SELECT statement;
CURSOR emp_cur IS
SELECT employee_id, last_name, salary
FROM employees
WHERE TO_CHAR(hire_date, ‘YYYY’) = p_year;
BEGIN
-- 커서 열기
-- OPEN cursor_name;
OPEN emp_cur;
-- 커서로부터 데이터 일기
-- FETCH cursor_name INTO local variables;
LOOP
FETCH emp_cur INTO l_emp.employee_id, l_emp.last_name, l_emp.salary;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_emp.employee_id || ‘ ‘ || l_emp.last_name || ‘ ‘ || l_emp.salary);
END LOOP;
--커서 닫기
--CLOSE cursor_name;
CLOSE emp_cur;
END info_Hiredate;
3) CURSOR 속성
- %FOUND : PL/SQL 코드가 마지막으로 얻은 커서의 결과 SET에 레코드가 있다면 참
- %NOTFOUND : %FOUND 연산자와 반대의 의미
- %ROWCOUNT : 커서에서 얻은 레코드의 수를 반환
- %ISOPEN : 커서가 열렸고, 아직 닫히지 않은 상태라면 참
4. EXCEPTION
1) 문법
DECLARE
-- 예외선언
-- 예외 이름 EXCEPTION;
BEGIN
-- 예외 발생
-- RAISE 예외 이름;
EXCEPTION
-- 예외 처리
-- WHEN 예외 이름 THEN
-- 구문;
END;
2) Oracle에서 제공하는 예외
- NO_DATA_FOUND : SELECT INTO 구문 실행 후 반환되는 행이 없는 경우
- TOO_MANY_ROWS : SELECT INTO 구문이 하나 이상의 행을 반환하는 경우
- VALUE_ERROR : 데이터의 변환 과정에서 오류나 선언된 변수가 데이터의 저장하기에 크기가 작은 경우
- NOT_LOGED_ON : Oracle에 접속하지 않은 채 데이터베이스를 Call하는 경우
- INVALID_CURSOR : Cursor의 동작이 부적절한 경우
- CURSOR_ALREADY_OPEN : 이미 열려있는 Cursor가 열려고 하는 경우
- DUP_VAL_ON_INDEX : Unique Index가 설정된 칼럼에 중복된 값을 입력하는 경우
*** 참조 문헌 : 10g로 시작하는 오라클 SQL & PL/SQL - 7장 SQL의 한계를 넘어 PL/SQL
