[사용자 정의 예외 처리]
정해져 있는 상황(SELECT 결과가 아무것도 없을 때)이 아닌 논리적인 업무 상황을 가정하여 예외 처리를 할 경우 사용자가 직접 예외처리에 대한 상황을 정의해야 함.
** 방법
1. raise
– 예외 처리에 대한 상황을 정의하고 해당 예외에 대한 이름(예외명)을 부여
– 실행부(begin절)에서 예외명 정의, 해당 예외에 대한 출력은 예외처리부(exception)에서 처리
– 프로그램이 정상 종료됨.
2. raise_application_error
– 에러코드와 에러메세지를 정의
– 예외명 없이 바로 예외 상황에 대한 에러 발생
– declare절에서 에외명에 대한 변수 정의 불필요
– exception절에서 해당 예외에 대한 출력을 할 필요 없음
– 프로그램이 비정상 종료됨.
– 에러코드는 20000~20999 에서 맘대로 지정 가능.
** 예제) emp 테이블을 사용하여 사번을 입력하면 해당 사원의 상위 관리자 이름 출력.
7369의 상위 관리자는 FORD입니다.
7369의 상위 관리자가 없습니다.
1111은 존재하지 않습니다.
> !vi emp2.sql
accept vempno prompt '사번을 입력하세요 : '
declare
vmgr varchar2(20);
vcount number;
no_mgr exception;
begin
select e2.ename, count(e2.ename) into vmgr, vcount
from scott.EMP e1, scott.emp e2
where e1.empno = &vempno
and e1.mgr = e2.empno(+)
group by e2.ename;
-- outer join을 안 쓰면 사번이 없는 경우와 매니저가 없는 경우가 구분 불가.
-- 이러면 vempno가 없는 경우엔 vcount=null, manager 없는 경우엔 vcount=0이 됨.
if vcount=0 then
raise no_mgr;
end if;
dbms_output.put_line(&vempno||'s manager is '||vmgr||'.');
exception
when no_data_found then
dbms_output.put_line('there is no such empno '||&vempno);
when no_mgr then
dbms_output.put_line(&vempno||' does not have manager.');
end;
/** 예제) professor, student 테이블을 사용하여 교수 번호를 입력하면 해당 교수의 지도 학생 수를 출력.
9999의 지도 학생 : 김신영-일지매
9999의 지도 학생은 없습니다.
> !vi profstud.sql
------------------------------------------------------------------
accept vprofno prompt '교수 번호를 입력하세요 : '
declare
vcount number;
vlist varchar2(30);
no_student exception;
begin
select listagg(s.name,'-') within group(order by p.name),
count(s.name)
into vlist, vcount
from scott.professor p, scott.student s
where p.profno = s.profno(+)
and p.profno = &vprofno
group by p.profno;
if vcount=0 then
raise no_student;
end if;
dbms_output.put_line(&vprofno||'s student is '||vlist);
exception
when no_data_found then
dbms_output.put_line('there is no such profno '||&vprofno);
when no_student then
dbms_output.put_line(&vprofno||' does not have student.');
end;
/[cursor]
데이터를 담기 위한 임시 메모리 공간.
사용) 동시에 여러 데이터를 출력하기 위해 커서 선언, 한 줄씩 fetch
for문, dml문이 자동으로 커서를 생성한다.
[커서 예외 처리]
묵시적, 명시적 커서가 정의된 상황에서의 예외 처리.
ex) delete할 대상이 없는 경우 no_data_found로 예외 처리 불가(select문에 한해서만 작동함)
-> SQL%NOTFOUND를 사용하여 예외 처리
** 예제) emp 테이블을 사용하여 사원 번호를 입력받고 해당 사원을 삭제.
> !vi del.sql
-------------------------------------
accept vempno prompt '삭제할 사번을 입력하세요 : '
declare
vempno number := &vempno;
begin
delete from scott.emp
where empno = vempno;
if SQL%NOTFOUND then
raise_application_error(-20000, 'there is no such data');
end if;
commit;
-- 만약 그냥 raise 쓸 경우에는 commit을 if로 거르는 부분 뒤에 써야지 잘 걸러짐. commit을 먼저 해버리면 commit하면서 cursor가 날라가서 commit도 되고 if에도 걸리는 상황이 됨.
dbms_output.put_line(vempno||' data is deleted.');
end;
/[PLSQL 객체]
1. PROCEDURE
– 기능 수행 목적 (조회 후 출력, delete, update 처리, insert)
– 자체적으로 실행 가능 (exec 프로시저명;)
– input값이 존재하지 않을 수 있음.
2. FUNCTION
– 대부분은 input이 필요하지만 안 그런 함수도 있음.
– 반드시 output이 존재하는 객체.
– select, update, delete 등의 sql문 안에서만 실행 가능.
3. TRIGGER
– 특정 동작 전/후 자동으로 실행되는 프로그램 기능을 구현하는 객체
예) 로그온 트리거 (사용자가 로그온 직후 사용자 기록을 특정 테이블에 insert)
4. PACKAGE
– procedure, function을 기능에 따라 묶어둔 객체.
예) dbms_output.put_line() – dbms_output : package, put_line() : function
[PROCEDURE]
입출력값 필수 X
동작을 명령하는 구문 전달
** 문법
create [or replace] procedure 프로시저명
(입력변수1 데이터타입 := 값, -- 크기 지정 없이 데이터타입만 전달
입력변수2 데이터타입 := 값,
...)
is|as
내부변수1 데이터타입; -- 실행부(BEGIN)에서 만들어진 내부변수 정의, declare를 is|as가 대체한 느낌
내부변수2 데이터타입;
...
begin
...
end;
/** 예제1) 사번과 업데이트할 새로운 급여를 입력받고 해당 사원의 급여를 수정.
create or replace procedure scott.emp_sal_update
(vempno number,
vnewsal number)
is
begin
update scott.emp
set sal = vnewsal
where empno = vempno;
if SQL%NOTFOUND then
raise_application_error(-20001, 'there is no such empno');
end if;
commit;
dbms_output.put_line(vempno||'s sal is updated to '||vnewsal);
end;
/
exec scott.emp_sal_update(7788, 5000);
exec scott.emp_sal_update(1111, 5000);
select * from scott.emp;** 예제2) 학생번호와 시험성적을 입력받고 학점 기준에 따른 학점으로 업데이트
90이상 A, 80이상 B, 70이상 C, 70미만 D
alter table scott.student add hakjum varchar2(10);
select * from scott.student;
create or replace procedure scott.stud_hakjum
(vstudno number,
vjumsu number)
is
vhakjum varchar2(10);
begin
if vjumsu >= 90 then vhakjum := 'A';
elsif vjumsu >= 80 then vhakjum := 'B';
elsif vjumsu >= 70 then vhakjum := 'C';
else vhakjum := 'D';
end if;
update scott.student
set hakjum = vhakjum
where studno = vstudno;
if SQL%NOTFOUND then
raise_application_error(-20002, 'there is no such studno');
end if;
commit;
end;
/
exec scott.stud_hakjum(9411, 80);
exec scott.stud_hakjum(1111, 80);
select * from scott.student;[동적 Dynamic SQL]
SQL 문장이 고정적이지 않고 유동적인 경우의 SQL.
EXECUTE IMMEDIATE로 실행.
ex) 테이블명, 상수명 등이 계속 변경되는 경우의 SQL
- 고정 SQL)
select * from scott.emp;
-- 동적 SQL)
tname := 'EMP'
select count(*) from scott.tname -- 실행불가(scott.'EMP' 로 전달되므로)
vsql := 'select count(*) from scott.'||tname
execute immediate vsql;** 예제1) 테이블명을 입력하면 해당 테이블의 전체 건수 출력
create or replace procedure tab_count
(oname varchar2,
tname varchar2)
is
vsql varchar2(100);
vcount number;
begin
vsql := 'select count(*) from '||oname||'.'||tname;
execute immediate vsql into vcount;
dbms_output.put_line(tname||' 테이블 행의 수 : '||vcount);
end;
/
exec tab_count('scott','emp');** 예제2) 유저 생성 프로시저(유저명, 롤이름)
주의) SYS계정에서 생성 및 실행!! SYSTEM 유저는 직접 DBA 권한 받은 게 아니기도 하고 create user, grant any privilege 권한이 기본적으로 없어서 SYSTEM에서는 에러 남.
-- create role
create role rl_sel_a;
-- procedure
create or replace procedure create_user
(vuser varchar2,
vrole varchar2)
is
vsql1 varchar2(50);
vsql2 varchar2(50);
begin
execute immediate 'create user '||vuser||' identified by oracle';
execute immediate 'grant create session to '||vuser;
execute immediate 'grant '||vrole||' to '||vuser;
dbms_output.put_line(vuser||' is created and '||vrole||' is granted');
end;
/
-- exec 후 조회
exec create_user('itwill1000','rl_sel_a');
select * from dba_users where username like 'ITWILL%' order by created desc;
select * from dba_role_privs where grantee = 'ITWILL1000';
-- 유저 삭제
drop user itwill1000;