안녕하세요
오늘은 SQL 마지막 포스팅으로
SQL에 특화된 프로그래밍 언어인
PL/SQL에 대해 알아보도록 하겠습니다!
PL/SQL이란?
SQL을 확장한 절차적 언어로
관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어입니다.
그렇다면 PL/SQL을 왜 배워야 할까요?
우선 큰 규모의 화사는 데이터를 대부분 오라클에 저장합니다.
하지만
SQL만으로 데이터를 분석하고 처리하려면 많은 노력이 수반됩니다.
PL/SQL을 사용하면 아래와 같은 장점이 있습니다.
1. PL/SQL을 이용하면 SQL로 반복해서 해야하는 작업을 보다 단순하게 처리할 수 있습니다.
2. R로 데이터 분석을 하기위해 엑셀 등으로 DB의 데이터를 내려받아 분석하는 것이 아니라, 바로 DB에서 PL/SQL을 이용해서 분석할 수 있습니다.
3. SQL 튜닝 방법 중 하나로 PL/SQL을 사용할 수 있습니다.
|
PLSQL 기본구조
declare 선언문 (변수, 예외, 커서 등을 선언한다.) begin 실행절 (SQL 문, IF 문, LOOP 문 등을 실행한다.)
end; /
|
예제를 통해 활용해보도록 합시다!
[예제를 위한 테이블]
[ emp ] : 사원테이블
사원명 | 사원번호 | 직업 | 연봉 | 부서 |
A | 1001 | MANAGER | 3000 | 파주 |
B | 1002 | SALESMAN | 1600 | 제주 |
C | 1005 | PRESIDENT | 5000 | 서울 |
D | 1024 | SALESMAN | 1400 | 파주 |
E | 2024 | ANALYST | 3000 | 서울 |
F | 2215 | SALESMAN | 1350 | 서울 |
G | 2015 | MANAGER | 2900 | 일산 |
H | 1240 | ANALYST | 3000 | 파주 |
I | 3415 | SALESMAN | 1700 | 서울 |
예제1. 사원번호를 입력하면 해당 사원의 월급에 대한 순위가 출력되게 하시오
(단, 없는 사원번호를 입력하였을 때 예외처리를 하시오)
set serveroutput on
accept p_empno prompt '사원번호를 입력하세요: '
declare
v_rnk number(10);
begin
select rnk into v_rnk
from (select 사원번호, rank() over (order by 연봉 desc) rnk
from emp)
where 사원번호 = &p_empno;
dbms_output.put_line(v_rnk || '등 입니다.');
exception
when no_data_found then
dbms_output.put_line('해당 사원은 없습니다.');
end;
/
설명)
1. set serveroutput on 을 사용하여 데이터를 출력하는 dbms_output.put_line함수를 이용할 수 있다.
2. accept는 데이터를 입력받기 위한 것으로
&치환변수를 이용하여 값을 입력받는다.
3. exception이란 예외처리하는 것으로
no_data_found란 데이터가 없을 때 발생하는 오류로
오라클에서 지정해 놓은 예외처리 방법 중 하나이다.
예제2. 직업을 입력하면 해당 직업의 토탈월급이 출력되는 함수를 생성하고 실행하시오.
create or replace function job_sal_sum
(p_job in emp.직업%type)
return number
is
v_sumsal number(10);
begin
select sum(연봉) into v_sumsal
from emp
where 직업 = p_job;
return v_sumsal;
end;
/
설명)
1. create를 이용하여 함수를 생성한다.
2. 가로의 값은 입력받을 파라미터를 지정하는 것이다.
3. return 값이 꼭 존재해야하며,
여기서는 v_sumsal의 타입인 number로 지정한다.
사용방법)
SQL> SELECT 사원명, 직업, job_sal_sum('SALESMAN') FROM emp;
예제3. 직업, 부서별 토탈월급을 출력하시오
(PL/SQL 프로시져를 이용하세요)
우선 SQL을 이용하면 아래와 같습니다.
SQL> SELECT 직업, sum( decode(부서, '서울', 연봉, 0) ) "서울", sum( decode(부서, '파주', 연봉, 0) ) "파주", sum( decode(부서, '일산', 연봉, 0) ) "일산" FROM emp GROUP BY 직업;
|
[결과]
직업 |
서울 |
파주 |
일산 |
MANAGER |
|
3000 |
2900 |
SALESMAN |
3050 |
1400 |
|
PRESIDENT |
5000 |
|
|
ANALYST |
3000 |
3000 |
|
create or replace procedure job_dept
(v_result out sys_refcursor)
is
v_stmt varchar2(200) := 'select 직업,';
begin
for record in (select distinct 부서 from emp) loop
v_stmt:= v_stmt || ' sum(decode(부서, ''' || record.부서 || ''', 연봉, 0) ) ' || record.부서 || ',';
end loop;
v_stmt := rtrim(v_stmt, ',') || ' from emp goup by 직업';
open result for v_stmt;
end;
/
사용방법)
SQL1> variable v_result1 refcursor;
SQL2> exec job_dept(:v_result1);
SQL3> print v_result3;
설명)
프로시져란 PLSQL 코드를 database에 저장하기 위한 것으로
함수와 달리 return절이 없으며 결과를 반드시 출력하지 않아도 됩니다.
1. 연결 연산자와 함수를 이용하여 SQL문을 완성합니다.
2. 만약 부서가 위의 테이블보다 훨씬 많이 존재한다고 가정한다면,
for loop문을 이용하여 만든 PLSQL문이 보다 효율적이며,
부서가 추가되었을 때에도 코드를 수정하지 않아도 된다는 장점이 있습니다.
PL/SQL을 간단히 다뤄보았습니다!
더 궁금한 점이 있다면 댓글로 남겨주세요~
