1장.
PL/SQL 소개
2장.
PL/SQL 변수
3장.
PL/SQL 실행문 작성
4장.
PL/SQL 내에서의 DML문장(INSERT, UPDATE, DELETE..)
5장.
IF문과 LOOP문
6장.
조합 데이터 유형 변수(레코드, 컬렉션)
7장.
명시적 커서
8장.
예외처리
9장.
프로시저와 함수
마지막장
: 현업에서 사용하는 PL/SQL 프로시저
서브블럭 사용하는 이유는, 하나의 PLSQL 안에 여러개의 업무를 넣으려고!
✅ 메인 블럭에서 선언한 사용자 정의 예외인 e_no_rows를 서브 블럭에서 사용할 수 있으면
서브블럭에서 e_no_rows예외를 raise시켜 메인 블럭의 exception절
로 넘길 수 있습니다.
💡
복습!
메인블럭에서 선언한 변수는 서브블럭에서 호출할 수 있지만, 서브블럭에서 선언한 변수는 메인블럭에서 호출할 수 없다.
➡️ 그냥 오류를 내버리고 프로그램 끝내버리는 것
declare
begin
실행문1; <------ 10억을 이체할 계좌번호를 받는 실행문
if 만약 유효한 계좌번호가 아니면 then
raise 사용자정의 예외처리;
실행문2; <------ 10억을 이체 시도하는 실행문 (★중요한 실행문★)
실행문3; <------
end;
/
✅ 유효한 계좌번호가 아니라면 사용자정의 예외처리로 메세지 띄우고 끝나지만 실행문2에 혹시 insert나 update들이 수행될까봐 불안하다. 너무 중요한 실행문이라서!
이럴 때 RAISE_APPLICATION_ERROR
를 사용하면 예외처리 단계에서 그냥 프로그램이 죽어버린다.
✅ 사용자 정의 예외처리 경우는 유효한 계좌번호가 아닙니다. 라는 메세지가 나오면서 두번째 실행문을 실행하지 않고 예외처리를 합니다. 그러면서 PLSQL이 정상적으로 처리되었다는 메세지가 나옵니다. 이것이 불안하다면 유효한 계좌번호가 아닐 시, 그자리에서 프로그램이 유언을 남기고 죽는것이 두번째 실행문을 실행하지 않았다는 가장 확실한 방법입니다. (RAISE_APPLICATION_ERROR
사용)
✏️ 예제 ! (프로시저 생성)
CREATE OR REPLACE PROCEDURE verify_emp (
p_empno NUMBER -- 9장에서 배울 프로시저 실행 관련 코드. 값을 입력받아 실행할 수 있는 코드
)
IS
v_ename emp.ename%TYPE;
v_job emp.job%TYPE;
v_mgr emp.mgr%TYPE;
v_hiredate emp.hiredate%TYPE;
BEGIN
SELECT ename, job, mgr, hiredate
INTO v_ename, v_job, v_mgr, v_hiredate FROM emp
WHERE empno = p_empno; -- 입력받은 사원번호 들어간다.(7788~)
-- 변수들이 null이면 프로그램 끝내버리겠다. (너무 중요한 정보라서)
IF v_ename IS NULL THEN
RAISE_APPLICATION_ERROR(-20010, 'No name for ' || p_empno);
END IF;
IF v_job IS NULL THEN
RAISE_APPLICATION_ERROR(-20020, 'No job for' || p_empno);
END IF;
IF v_mgr IS NULL THEN
RAISE_APPLICATION_ERROR(-20030, 'No manager for ' || p_empno);
END IF;
IF v_hiredate IS NULL THEN
RAISE_APPLICATION_ERROR(-20040, 'No hire date for ' || p_empno);
END IF;
-- 에러가 없으면 정상적으로 출력되었다 메세지 나온다.
DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||
' validated without errors');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
/
CALL verify_emp(7839); -- CALL, EXIT해도 됨
SQLCODE: -438
SQLERRM: SQL0438N Application raised error or warning with
diagnostic text: "No manager for 7839". SQLSTATE=UD030
문제 108. 사원번호를 물어보게하고 해당 사원번호를 지우는 익명 PL/SQL 프로그램 작성
set verify off set serveroutput on accept p_empno prompt '사원번호를 입력하세요 ' declare v_empno emp.empno%type := &p_empno; v_cnt number(10); begin delete from emp where empno = v_empno; v_cnt := SQL%ROWCOUNT; dbms_output.put_line ( v_cnt ||' 행이 지워졌습니다'); end; /
문제 109. 위 코드를 수정해서 없는 사원번호를 입력하면 다음과같이 출력되게 하시오!
사원번호를 입력하세요 -> 9999
SQLCODE: -20031
SQLERRM: ORA-20031: No empno for 9999
set verify off set serveroutput on accept p_empno prompt '사원번호를 입력하세요 ' declare v_empno emp.empno%type := &p_empno; v_cnt number(10); begin delete from emp where empno = v_empno; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20031, 'No empno for' || v_empno); END IF; DBMS_OUTPUT.PUT_LINE('Employee ' || v_empno || ' validated without errors'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE); DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM); end; /
➡️ PL/SQL 예외 정의
➡️ PL/SQL 블록에 EXCEPTION 섹션을 추가하여 런타임에
예외 처리
➡️ 다양한 유형의 예외 처리:
– 미리 정의된 예외
– 미리 정의되지 않은 예외
– 유저 정의 예외
➡️ 중첩 블록 및 호출 응용 프로그램에서의 예외 전달
배우게 될 것들!
✅ 익명 블록과 서브 프로그램 구분
✅ 간단한 프로시저 작성 및 익명 블록에서 프로시저 호출
✅ 간단한 함수 작성
✅ 파라미터를 받아들이는 간단한 함수 작성
✅ 프로시저와 함수 구분
⭐ 익명블럭은 매번 컴파일된다. (우리가 알수있는 코드를 기계어로 바뀌는것)
: 기계어
란, 0과 1!
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
IS|AS
procedure_body;
✅ mode는 디폴트가 입력(in) 이지만 출력할 변수로도 쓸 수 있다. (in, out)
✅ 대괄호는 있어도 없어도 된다는 뜻. 입력매개변수 만드는 자리!
✅ IS 또는 AS 사용가능
✍🏻 예제
✍🏻 실습
- dept2 테이블을 dept 테이블의 구조로 생성한다.
create table dept2 as select * from dept where 1=2; select * from dept2; or desc dept2
- dept2 테이블에 데이터를 입력하는 프로시저를 생성한다.
create procedure pro1 is v_deptno dept2.deptno%type; v_dname dept2.dname%type; begin v_deptno := 10; v_dname := 'RESEARCH'; insert into dept2(deptno, dname) values(v_deptno, v_dname); dbms_output.put_line(SQL%ROWCOUNT || '행이 입력되었습니다!'); end; /
⭐ 프로시저가 생성되었다는 것은, PL/SQL코드 문법이 오류없이 잘 컴파일 되었다는 뜻이다. 그리고 프로시저 이름(예- pro1)으로 db에 PL/SQL을 저장한 것이다. 실행한것이 아니다!
⭐ 프로시저 실행은,EXEC
나CALL
명령어로 따로 해주어야한다.
문제 110. [점심시간 문제] 지금 만든 pro1의 프로시저를 실행하는 코드를 작성하시오!
1. 첫번째 방법 :
exec pro1
2. 두번째 방법 :
begin
pro1;
end;
/
create or replace procedure pro1
( p_deptno dept2.deptno%type,
p_dname dept2.dname%type ) -- 이부분 추가!
is
v_deptno dept2.deptno%type;
v_dname dept2.dname%type;
begin
v_deptno := p_deptno; -- 이부분 수정
v_dname := p_dname;
insert into dept2(deptno, dname)
values(v_deptno, v_dname);
dbms_output.put_line(SQL%ROWCOUNT || '행이 입력되었습니다!');
end;
/
문제 111. 아래와 같이 프로시저를 수행하면 해당 부서번호의 사원들의 커미션이 자신의 월급의 20%로 갱신되는 프로시저를 생성하시오!
exec pro2(20);
// 4명의 데이터가 갱신되었습니다.
create or replace procedure pro2 ( p_deptno emp.deptno%type) is begin update emp set comm = sal * 0.2 where deptno = p_deptno; dbms_output.put_line(SQL%ROWCOUNT || '행이 갱신되었습니다!'); end; /
✅ 익명블럭이랑다른점은 p_deptno앞에
&
가 없다.
문제 112. 위 프로시저에 예외처리를 해서 다음과 같이 실행하면, 해당 부서번호는 없는 부서입니다. 라고 출력되게 하시오!
exec pro2(70);
해당 부서번호는 없는 부서입니다.
create or replace procedure pro2 ( p_deptno emp.deptno%type ) is e_exception exception ; begin update emp set comm = sal * 0.2 where deptno = p_deptno; if SQL%NOTFOUND then raise e_exception; end if; dbms_output.put_line ( SQL%ROWCOUNT || ' 행이 갱신되었습니다'); exception when e_exception then dbms_output.put_line('해당 부서번호는 없는 부서입니다'); end; /
✍🏻 문법
CREATE [OR REPLACE] FUNCTION function_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
RETURN datatype -- ⭐RETURN 무조건 있어야한다.⭐
IS|AS
function_body;
✅ 함수는 무조건 결과를 null값이라도 리턴한다.
✍🏻 예제
✍🏻 예제 실습
월급이 3000 이상이면 A를 리턴하고, 월급이 2000 이상이면 B를 리턴하고, 월급이 1000 이상이면 C를 리턴하고, 나머지는 D리턴하는 함수를 생성하시오!
create or replace function func1
( p_sal emp.sal%type )
return varchar2 -- 리턴되는게 A,B,C..문자라서 varchar2 !! 숫자는 number 가로열고 쓰는거 없다.
is
v_grade varchar2(5);
begin
if p_sal >= 3000 then
v_grade := 'A';
elsif p_sal >= 2000 then
v_grade := 'B';
elsif p_sal >= 1000 then
v_grade := 'C';
else
v_grade := 'D';
end if;
return v_grade;
end;
/
-----------
select ename, sal, func1(sal)
from emp;
✅ 사용자 정의 함수를 생성하게되면, SQL을 아주 심플하게 작성할 수 있게된다.
문제 113. 다음과 같이 SQL을 수행하면 결과가 출력되는 함수를 생성하시오!
select ename, sal, job, find_loc(deptno)
from emp;
// KING 5000 PRESIDENT NEW YORK
내 답
create or replace function find_loc ( p_deptno emp.deptno%type ) return varchar2 is v_loc dept.loc%type; begin select loc into v_loc from dept where deptno = p_deptno; return v_loc; end; /
쌤 답
create or replace function find_loc ( p_deptno emp.deptno%type ) return varchar2 is v_loc dept.loc%type; begin if p_deptno = 10 then v_loc := 'NEW YORK'; elsif p_deptno = 20 then v_loc := 'DALLAS'; elsif p_deptno = 30 then v_loc := 'CHICAGO'; else v_loc := 'BOSTON'; end if; return v_loc; end; /
✅ 사용자 정의 함수의 장점: SQL을 아주 심플하게 작성할 수 있다. 조인을 사용하지 않고 조인된 결과를 볼 수 있다.
문제 114. 달라스에서 근무하는 사원들의 이름, 월급, 부서위치를 출력하시오
select ename, sal, find_loc(deptno) from emp where find_loc(deptno) = 'DALLAS';
문제 115. (튜닝전 SQL) 부서번호, 부서번호별 평균월급을 출력하세요
select deptno, round(avg(sal)) from emp group by deptno;
문제 116. 이름, 월급, 자기가 속한 부서번호의 평균월급을 출력하시오!
// 1. 스칼라 서브쿼리 select ename, sal, ( select avg(sal) from emp where deptno = o.deptno) from emp o; -------------------------- // 2. partition by 사용 select ename, sal, avg(sal) over (partition by deptno) 평균월급 from emp;
문제 117. 이름, 월급, 자기가 속한 부서번호의 평균 월급을 출력하는데 자기의 월급이 자기가 속한 부서번호의 평균월급보다 더 큰 사원들만 출력
// from절 서브쿼리 select ename, sal, 평균월급 from ( select ename, sal, avg(sal) over (partition by deptno) 평균월급 from emp ) where sal > 평균월급;
문제 118. 부서번호를 입력, 실행하면 해당 부서번호의 평균월급이 출력되는 사용자 정의 함수를 생성하기
create or replace function deptno_avgsal ( p_deptno emp.deptno%type ) return number is v_avgsal number(10,2); begin select avg(sal) into v_avgsal from emp where deptno = p_deptno; return v_avgsal; end; /
문제 119. 위에 만든 함수를 이용해서 아래의 sql을 튜닝하세요
튜닝 전
select ename, sal, 평균월급
from (
select ename, sal, avg(sal) over (partition by deptno) 평균월급
from emp
)
where sal > 평균월급;
튜닝 후
select ename, sal, deptno_avgsal(deptno) from emp;
✅ 사용자정의함수를 너무많이 쓰면 느려진다.
이 단원에서는 다음 항목에 대해 배웠습니다!
➡️ 간단한 프로시저 작성
➡️ 익명 블록에서 프로시저 호출
➡️ 간단한 함수 작성
➡️ 파라미터를 받아들이는 간단한 함수 작성
➡️ 익명 블록에서 함수 호출 (근데 우리는 sql에서 호출해보았다)
문제 120. (SQL문제) 직업, 직업별 토탈월급을 출력하는데 가로로 출력하세요!
select sum(decode (job, 'ANALYST', sal, null) ) as "ANALYST", sum(decode (job, 'CLERK', sal, null) ) as "CLERK", sum(decode (job, 'MANAGER', sal, null) ) as "MANAGER", sum(decode (job, 'PRESIDENT', sal, null) ) as "PRESIDENT", sum(decode (job, 'SALESMAN', sal, null) ) as "SALESMAN" from emp;
문제 121. (SQL문제) 위 SQL로 부서번호별 직업별 토탈월급을 출력하시오
select deptno, sum(decode (job, 'ANALYST', sal, null) ) as "ANALYST", sum(decode (job, 'CLERK', sal, null) ) as "CLERK", sum(decode (job, 'MANAGER', sal, null) ) as "MANAGER", sum(decode (job, 'PRESIDENT', sal, null) ) as "PRESIDENT", sum(decode (job, 'SALESMAN', sal, null) ) as "SALESMAN" from emp group by deptno;
문제 122. 위 결과를 프로시저로 수행하시오
// 프로시저를 만드는 부분 create or replace procedure get_data --get_data은 프로시저 이름 -- p_x는 파라미터, out자리는 in도 쓸수있는데 in이 디폴트값이라 지금까지 생략했던 것. -- out은 프로시저를 실행했을 때 결과를 담아내는 것이다. -- in은 입력값을 담아서 결과를 출력하는 것 -- 테이블처럼 생긴 표 데이터를 담아내려고 refcursor 를 썼다. (p_x out sys_refcursor) -- 이 코드는 결과테이블을 담을 변수 선언 as -- 400 바이트를 넣은 이유는, 121번 문제의 sum+decode 코드를 다 넣을거라서 l_query varchar2(400) :='select deptno '; begin for x in (select distinct job from emp order by 1) loop -- 실행문! 위 x가 salesman일 때 실행문 실행, clerk일때 실행문 실행.... l_query := l_query ||replace(', sum(decode(job,''$X'',sal)) as $X ' ,'$X',x.job ); end loop; l_query := l_query ||' from emp group by deptno '; open p_x for l_query; end; / // 프로시저를 실행하는 부분 variable x refcursor; -- variable를 사용해서 만든 변수는 호스트변수(바인드변수) exec get_data(:x); print x;
x가 analyst일 때?
l_query || replace(', sum(decode(job,''X'',sal)) as $X ' ,'X',x.job );
select deptno 위에서 할당했으니까
select deptno || , sum(decode(job,''$X'',sal)) as $X --라는 문자열 . 얘를 x.job으로 리플레이스 하겠다.
l_query := 'select deptno , sum(decode(job, 'ANALYST', sal)) as ANALYST'
x가 CLERK일 때?
'select deptno , sum(decode(job, 'ANALYST', sal)) as ANALYST'
|| replace(', sum(decode(job,''X'',sal)) as $X ' ,'X',x.job );
위 아래쪽 문장은 아래와 같다.
, sum (decode(job, 'CLERK',sal) ) as CLERK
두개를 이어붙히면,
select deptno , sum(decode(job, 'ANALYST', sal)) as ANALYST , sum (decode(job, 'CLERK',sal) ) as CLERK
x가 MANAGER일 때?
select deptno ,sum(decode(job, 'ANALYST', sal)) as ANALYST , sum (decode(job, 'CLERK',sal) ) as CLERK
,sum (decode(job, 'CLERK',sal) ) as CLERK
,sum (decode(job, 'MANAGER',sal) ) as MANAGER
x가 SALESMAN일 때?
select deptno ,sum(decode(job, 'ANALYST', sal)) as ANALYST , sum (decode(job, 'CLERK',sal) ) as CLERK
,sum (decode(job, 'CLERK',sal) ) as CLERK
,sum (decode(job, 'MANAGER',sal) ) as MANAGER
,sum (decode(job, 'PRESIDENT',sal) ) as PRESIDENT
,sum (decode(job, 'SALESMAN',sal) ) as SALESMAN
END LOOP를 만나고 l_query := l_query ||' from emp group by deptno ';를 만나면
select deptno ,sum(decode(job, 'ANALYST', sal)) as ANALYST , sum (decode(job, 'CLERK',sal) ) as CLERK
,sum (decode(job, 'CLERK',sal) ) as CLERK
,sum (decode(job, 'MANAGER',sal) ) as MANAGER
,sum (decode(job, 'PRESIDENT',sal) ) as PRESIDENT
,sum (decode(job, 'SALESMAN',sal) ) as SALESMAN
from emp
group by deptno;
가 되는것이다.
open p_x for l_query; 를 통해 l_query의 sql결과를(위코드) p_x커서에 할당합니다.
✅ replace(컬럼, 변경전, 변경후)
문제 123. (SQL문제) 우리반 테이블로 아래의 결과를 출력하세요
성별별, 통신사별 평균 나이 출력
select gender, avg(decode (telecom, 'kt', age, null) ) as "kt", avg(decode (telecom, 'lg', age, null) ) as "lg", avg(decode (telecom, 'sk', age, null) ) as "sk" from emp17 group by gender;
문제 124. 위 결과를 프로시저로 작성해서 수행하시오
create or replace procedure get_data2 --get_data은 프로시저 이름 (p_i out sys_refcursor) as l_query varchar2(400) :='select gender '; begin for i in (select distinct telecom from emp17 order by 1) loop l_query := l_query ||replace(', avg(decode(telecom,''$I'',age)) as $I ' ,'$I',i.telecom ); end loop; l_query := l_query ||' from emp17 group by gender '; open p_i for l_query; end; / -------------------------------------------------------------------------------- variable i refcursor; exec get_data2(:i); print i;
✅ 왜i.telecom
를 썼는가!? -> kt.telecom, lg.telecom ...하게되기 위해
원래 for i in 1.. 10 이라고 썼다면 출력할 때 i만 써도 되는데, 지금은 컬럼에서 선택하는거라서!begin for i in 1.. 10 loop dbms_output.put_line(i) end loop; end; /
✅ 리플레이스 쪽에 달러표시 없어도 된다.
문제 125. 아래의 SQL의 결과를 프로시저로 구현하기
select job,
sum(decode(to_char(hiredate,'RRRR'), '1980',sal) ) "1980",
sum(decode(to_char(hiredate,'RRRR'), '1981',sal) ) "1981",
sum(decode(to_char(hiredate,'RRRR'), '1982',sal) ) "1982",
sum(decode(to_char(hiredate,'RRRR'), '1983',sal) ) "1983"
from emp
group by job;
create or replace procedure get_data5 (p_i out sys_refcursor) as l_query varchar2(1000) :='select job '; begin for i in (select distinct to_char(hiredate,'RRRR')as hiredate from emp order by 1) loop l_query := l_query ||replace(', sum(decode(to_char(hiredate,''RRRR''),''$I'',sal)) as "$I" ' ,'$I',i.hiredate); end loop; l_query := l_query ||' from emp group by job '; open p_i for l_query; end; / ------------------------------------------------------------------- variable i refcursor; exec get_data5(:i); print i;
좋은 글 감사합니다.