복습
✏️ 커서 코드 순서
커서선언 ---> 커서 오픈 ---> 커서 패치 ---> 커서 닫기
- 이제부터
커서오픈,커서닫기를 오라클이 알아서 자동으로 하라고 하는 코드를 작성해볼 것!
오늘의 TIL
- 커서 for loop 문
커서 for loop 문
실습예제 1. for loop 문을 이용해서 숫자 1 ~ 10 출력
set serveroutput on
begin
for i in 1.. 10 loop
dbms_output.put_line(i);
end loop;
end;
/
// @test.sql 에 저장했음!실습예제 2. 커서 for loop문
💡 기존 커서문 : 커서선언, 커서오픈, 커서패치, 커서닫기 가 있어야한다. 하지만 커서 for loop문 커서문은 위 과정이 거의 생략이 된 커서선언만 있다.
declare
cursor emp_cursor is
select ename, sal, job
from emp
where job = 'SALESMAN';
begin
for emp_recode in emp_cursor loop --원래는 레코드를 선언했어야하는데
-- 이렇게하면 암시적으로 선언이 된다.
dbms_output.put_line(emp_recode.ename || chr(9) || emp_recode.sal);
end loop;
emd;
/ 문제 100. 위의 코드를 수정해서 직업을 물어보게하고 직업을 입력하면 해당 사원들의 이름, 월급이 출력되게 하시오
set verify off accept p_job prompt '이름을 입력하세요!' declare cursor emp_cursor is select ename, sal from emp where job = upper('&p_job'); begin for emp_recode in emp_cursor loop dbms_output.put_line(emp_recode.ename || chr(9) || emp_recode.sal); end loop; end; /
문제 101. 위 코드를 수정해서 우리반 테이블로 코딩을 하세요! 통신사를 물어보고, 통신사를 입력하면 해당 통신사인 학생들의 이름, 나이, 통신사가 출력되게 하세요
set verify off accept p_telecom prompt '통신사를 입력하세요!' declare cursor emp17_cursor is select ename, age, telecom from emp17 where telecom = lower('&p_telecom'); begin for emp17_recode in emp17_cursor loop dbms_output.put_line(emp17_recode.ename || chr(9) || emp17_recode.age || chr(9) || emp17_recode.telecom); end loop; end; /
subquery를 사용하는 커서 FOR 루프
✅ 커서 이름부분이었는데, 커서 선언을 그자리에 넣었다.
예제 문제 101 코드를 변경
set verify off
accept p_telecom prompt '통신사를 입력하세요!'
begin
for emp17_recode in (select ename, age, telecom
from emp17
where telecom = lower('&p_telecom')) loop
dbms_output.put_line(emp17_recode.ename || chr(9) ||
emp17_recode.age || chr(9) ||
emp17_recode.telecom);
end loop;
end;
/문제 102. 부서번호를 물어보게하고, 부서번호를 입력하면 해당 부서번호인 사원들의 이름, 월급, 부서번호가 출력되게 커서 for loop문을 작성
set verify off accept p_deptno prompt '부서번호를 입력하세요!' declare cursor emp_cursor is select ename, sal, deptno from emp where deptno = lower('&p_deptno'); begin for emp_recode in emp_cursor loop dbms_output.put_line(emp_recode.ename || chr(9) || emp_recode.sal || chr(9) || emp_recode.deptno); end loop; end; /서브쿼리 이용한 코드
set verify off accept p_deptno prompt '부서번호를 입력하세요!' begin for emp_recode in (select ename, sal, deptno from emp where deptno = lower('&p_deptno')) loop dbms_output.put_line(emp_recode.ename || chr(9) || emp_recode.sal || chr(9) || emp_recode.deptno); end loop; end; /
파라미터가 포함된 커서
다음과 같이 부서번호를 다르게 해서 커서를 여러번 열어야할 때 유용한 코드
실습예제 1.
declare
cursor emp_cursor ( v_deptno number) is
select ename, sal, deptno
from emp
where deptno = v_deptno ;
emp_record emp_cursor%rowtype;
begin
open emp_cursor(10);
loop
fetch emp_cursor into emp_record ;
exit when emp_cursor%notfound;
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.deptno);
end loop;
close emp_cursor;
open emp_cursor(20);
loop
fetch emp_cursor into emp_record ;
exit when emp_cursor%notfound;
dbms_output.put_line( emp_record.ename || chr(9) ||
emp_record.sal || chr(9) ||
emp_record.deptno);
end loop;
close emp_cursor;
end;
/문제 103. 위 코드를 수정해서, 우리반 테이블로 수행하세요. 다음과 같이 커서를 나이만 다르게해서 두번 열 수 있도록 코드를 생성하기. 출력은 이름, 나이, 주소가 출력
open emp17_cursor(25)
open emp17_cursor(30)
open emp17_cursor(25)select.. for update와 where current of절
declare
cursor emp_cursor is
select empno, ename, sal
from emp;
emp_record emp_cursor%rowtype;
v_grade varchar2(5);
begin
open emp_cursor ;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
if emp_record.sal >= 3000 then
v_grade := 'A';
elsif emp_record.sal >= 2000 then
v_grade := 'B';
elsif emp_record.sal >=1000 then
v_grade := 'C' ;
else
v_grade := 'D';
end if;
update emp
set grade = v_grade
where empno = emp_record.empno;
end loop;
close emp_cursor;
end;
/
✅ emp테이블의 이름을 모두 스캇으로 변경하고 다시 위의 스크립트에 update문으로 위 이미지와 같이 수정해서 test.sql을 돌리게 되면,
모든 사원의 grade가 C로 변경이 된다. 왜냐하면 모든 사원의 이름이 SCOTT이기 때문 ! 그래서 이와 같은 테이블의 데이터를 제대로 갱신하려면, 값이 유니크한 사원번호를 WHERE절에 작성하거나, 아래와 같이 where current of 커서이름; 으로 작성하면 됩니다.
where current of 커서이름;의 의미는 지금 현재 fetch해온 그 행의 데이터를 의미합니다. 그 행의 데이터만 갱신하게 됩니다 !!!
update emp
set grade = v_grade
where current of 커서이름;
for update;,where current of emp_cursor;추가declare cursor emp_cursor is select empno, ename, sal from emp for update; emp_record emp_cursor%rowtype; v_grade varchar2(5); begin open emp_cursor ; loop fetch emp_cursor into emp_record; exit when emp_cursor%notfound; if emp_record.sal >= 3000 then v_grade := 'A'; elsif emp_record.sal >= 2000 then v_grade := 'B'; elsif emp_record.sal >=1000 then v_grade := 'C' ; else v_grade := 'D'; end if; update emp set grade = v_grade where current of emp_cursor; end loop; close emp_cursor; end; /
명시적 커서 속성
%ISOPEN 속성
✅ 커서가 열려 있을 때만 행을 패치(fetch)할 수 있습니다.
✅ 패치(fetch)를 수행하기 전에 %ISOPEN 커서 속성을
사용하여 커서가 열려 있는지 테스트합니다.
📖 8장 예외처리
배우게 될 것들!
✅ PL/SQL 예외 정의
✅ 처리되지 않은 예외 인식
✅ 다양한 유형의 PL/SQL 예외 처리기 나열 및 사용
✅ 예상치 못한 오류 트랩
✅ 중첩 블록에서 예외 전달이 미치는 영향 설명
✅ PL/SQL 예외 메시지 커스터마이즈
🤔 예외란?
다음과 같이 사원 테이블에 같은 이름이 여러명이 있게 되면, select .. into절이 오류가 나게 됩니다. 이럴 때 오류가 나지 않도록 처리하는 기술이 예외처리 입니다.
✏️ 예외처리 예제
실습 1. 이름을 입력하면 해당 사원의 월급이 출력되는 PL/SQL 코드 작성
set serveroutput on
accept p_ename prompt '이름을 작성하세요!'
declare
v_ename emp.ename%type := upper('&p_ename');
v_sal emp.sal%type;
begin
select sal into v_sal
from emp
where ename = v_ename;
dbms_output.put_line(v_sal);
end;
/실습 2. KING의 이름을 SCOTT으로 변경하고 위의 PL/SQL 코드를 수행하는데 이름을 SCOTT으로 변경해보세요!
update emp
set ename = 'SCOTT';
where ename = 'KING';✅ 이렇게 변경 후 @test.sql 을 수행해보면(실습1 코드)
다른 사원의 이름은 문제가 없는데, scott은 에러가 난다. 이런 경우는 예외처리로 해두자 !
실습 3. 위 경우를 대비하기 위한 예외처리를 하세요!
set serveroutput on
accept p_ename prompt '이름을 작성하세요!'
declare
v_ename emp.ename%type := upper('&p_ename');
v_sal emp.sal%type;
begin
select sal into v_sal
from emp
where ename = v_ename;
dbms_output.put_line(v_sal);
exception
when too_many_rows then
dbms_output.put_line('해당 사원이름이 여러명 있습니다.
고객센터로 문의하세요.');
end;
/
예외처리의 종류 3가지
1. 오라클에서 미리 정의한 예외 (암시적)
: TOO_MANY_ROWS, NO_DATA_FOUND, INVALID_CURSOR...
2. 오라클에서 미리 정의하지 않은 예외 (암시적)
3. 사용자 정의 예외 (명시적)
문제 104. 위 코드를 다시 수행하는데, 이번에는 사원 이름을 물어볼 때 없는 사원 이름을 넣어보시오!
set serveroutput on accept p_ename prompt '이름을 작성하세요!' declare v_ename emp.ename%type := upper('&p_ename'); v_sal emp.sal%type; begin select sal into v_sal from emp where ename = v_ename; dbms_output.put_line(v_sal); exception when NO_DATA_FOUND then dbms_output.put_line('없는 사원이름입니다.'); end; /
문제 105. 위와같은 상황에 대비하기 위한 예외처리를 하시오.
NO_DATA_FOUND를 사용하여 메세지는 '해당 사원은 사원 테이블에 존재하지 않습니다. 고객센터로 문의하세요.'
set serveroutput on accept p_ename prompt '이름을 작성하세요!' declare v_ename emp.ename%type := upper('&p_ename'); v_sal emp.sal%type; begin select sal into v_sal from emp where ename = v_ename; dbms_output.put_line(v_sal); exception when TOO_MANY_ROWS then dbms_output.put_line('해당 사원이름이 여러명 있습니다. 고객센터로 문의하세요.'); when NO_DATA_FOUND then dbms_output.put_line('해당 사원은 사원 테이블에 존재하지 않습니다. 고객센터로 문의하세요.'); end; /
문제 106. 우리반 테이블을 가지고 PL/SQL 코드 작성하세요.
이름을 물어보게하고, 이름을 입력하면 해당 학생의 통신사가 출력되게 하시오. 그런데 중복된 학생 이름이 있거나 없는 학생 이름은 예외처리
set serveroutput on accept p_ename prompt '이름을 작성하세요!' declare v_ename emp17.ename%type := upper('&p_ename'); v_telecom emp17.telecom%type; begin select telecom into v_telecom from emp17 where ename = v_ename; dbms_output.put_line(v_telecom); exception when TOO_MANY_ROWS then dbms_output.put_line('해당 사원이름이 여러명 있습니다. 고객센터로 문의하세요.'); when NO_DATA_FOUND then dbms_output.put_line('해당 사원은 사원 테이블에 존재하지 않습니다. 고객센터로 문의하세요.'); end; /
미리 정의하지 않은 오라클 예외처리
오라클에서 미리 정의 하지 않은 예외처리인 경우는, pragma exception_init 를 이용해서 직접 예외처리를 생성합니다.
ORA-01400: not null 컬럼에 null 입력을 시도했을 때 나오는 에러
✅ 만약 우리회사에서 ORA-01400 에러가 많이난다면 이걸 그냥 예외처리로 만들어놓는다.
✅ (SQLERRM)는 관련된 에러메세지를 내보내는 코드
실습 1. 오라클에서 미리 정의하지 않은 예외처리
- emp, dept를 초기화합니다.
@init_emp.sql
- dept테이블에 loc컬럼에 not null 제약을 겁니다.
alter table dept modify loc not null;
- dept테이블에 데이터를 입력하는 PL/SQL프로그램을 작성합니다.
accept p_deptno prompt '부서번호를 작성하세요!' accept p_dname prompt '부서명을 작성하세요!' accept p_loc prompt '부서위치를 작성하세요!' BEGIN insert into dept values(&p_deptno, '&p_dname', '&p_loc' ); commit; END; /
- loc에 null값을 입력해봅니다.
🤔 not null 제약이 걸려있어서 널값이 안들어가는데, 부서위치가 정해지지 않아서 null값을 넣어야한다면??..- 예외처리를 합니다. (오라클에서 미리 정의하지 않은 예외)
accept p_deptno prompt '부서번호를 작성하세요!' accept p_dname prompt '부서명을 작성하세요!' accept p_loc prompt '부서위치를 작성하세요!' DECLARE e_insert_except exception; PRAGMA exception_init(e_insert_except, -01400); BEGIN insert into dept values(&p_deptno, '&p_dname', '&p_loc' ); commit; EXCEPTION WHEN e_insert_except THEN dbms_output.put_line(chr(9)); dbms_output.put_line('실패했습니다. 부서위치를 입력해주세요.'); END; /
예외 트랩에 대한 함수
DBA입장에서 PL/SQL 작성하는 이유 중 하나가, 이따가 밤에 안남고 퇴근하려고 ...! ㅎㅎ
밤 10시에 자동으로 내가 만든 프로시저가 수행되게 했습니다. 다음날 아침에 지난밤에 돌려놨던 프로시저가 에러가 나면서 작업에 실패했음을 확인했습니다. 그러려면 원인을 알아야하는데, 만약 작업을 백그라운드로 돌리게 되면 실패해도 원인을 알기가 어렵습니다.
그래서 다음의 예외트랩함수를 이용해서 log_table에 오류코드와 오류메세지가 insert 되게끔 하면 됩니다.
➡️ SQLCODE: 오류 코드에 대한 숫자 값을 반환합니다.
➡️ SQLERRM: 오류 번호와 연관된 메시지를 반환합니다.
실습!
1. 일 매출액을 집계해서 매출 테이블에 입력하는 프로시저 생성하기
create table machul
( job varchar2(10),
sumsal number(10) ); -- 테이블 생성
create or replace procedure daily_sum
is
begin
insert into machul
select job , sum(sal)
from emp
group by job;
commit;
end;
/- 밤 12시에 이 프로시저를 수행하기
exec daily_sum;
- 프로시저를 수행했을 때 오류가 나게끔 데이터 수정하기
alter table machul
modify sumsal not null;
update emp
set sal = 0
where job = 'SALESMAN';
alter table machul
add constraint m_ckk check(sumsal between 100 and 9000); 
- 그 오류가 ERROR 테이블에 입력되도록 daily_sum 프로시저를 수행합니다.
-- error 테이블 만들기
create table error
(e_date timestamp,
e_code number(10),
e_message varchar2(200) );
-- procedure 수정
create or replace procedure daily_sum
is
e_code number;
e_message varchar2(200);
begin
insert into machul
select job, sum(sal)
from emp
group by job;
commit;
exception
when others then
rollback;
e_code := SQLCODE;
e_message := SQLERRM;
insert into error values ( systimestamp, e_code, e_message );
end;
/
✅ 아침에 출근해서 간밤에 배치작업이 잘 수행되었는지 확인합니다.
error 테이블을 조회해서 문제가 있는지 확인합니다.
사용자 정의 예외처리
💡 복습 !
예외처리 3가지
1. 미리 정의한 예외처리 - 암시적 발생
2. 미리 정의하지 않은 예외처리 - 암시적 발생
3. 사용자 정의 예외처리 - 명시적 발생
✅ update문에서 뭔가 수행이되면 원래는 IF SQL%NOTFOUND THEN에 숫자가 인식? 된다. 근데 업데이트가 아무것도 수행되지 않으면 IF SQL%NOTFOUND THEN 여기에 0 즉 이 구문이 true 가 된다. 그래서 2번 코드에서 3번으로 한번에 뛰어넘어 예외처리 구문이 출력된다.
✅ 0행이 업데이트 되었습니다 -> 이거는 에러가 아니다. ORA~~이게 나와야 미리 정의하지않은 오라클 예외처리를 하는데 이건 에러가 아니기 때문에 이걸할수가 없다. 즉, 사용자 정의 예외처리를 해야하는 이유!
💡 정리 !!
PL/SQL 코드 내에서 위와같은 UPDATE문이 수행되었는데, ORA- 시작하는 에러메세지가 나온게 아니라면 오라클의 미리 정의한 예외처리든, 미리정의하지 않은 예외처리든 할 수가 없다.
그런데, 이런경우에 예외처리를 하고싶다면 사용자 정의 예외처리를 하면 된다. 그래서 예외처리 하면서UPDATE 뒤로 나오는 많은 PL/SQL 코드들을 실행하지 않고 바로 예외처리로 프로그램을 종료한다.
실습 1.
accept p_deptno prompt '부서번호를 입력하세요!'
DECLARE
v_deptno number := &p_deptno;
e_invalid exception;
BEGIN
update dept
set dname = 'testing'
where deptno = v_deptno;
IF SQL%NOTFOUND THEN
RAISE e_invalid; -- ★
여기 RAISE가 keyword !
END IF;
update emp
set sal = 0; -- 여기 건너뛰는지 보기
COMMIT;
EXCEPTION
WHEN e_invalid THEN
dbms_output.put_line('부서번호가 없어요.');
END;
/
✅ 없는 부서번호인 100번을 입력했더니, 사용자정의 예외처리가 작동되면서 바로 예외처리 섹션으로 넘어갔습니다. 그러면서 그 사이에있던 update emp 문이 수행되지 않은 것 입니다. 앞에 update dept 문이 전혀 오라클 에러메세지가 나온 경우가 아니기때문에 오라클의 미리 정의한 예외이던, 미리 정의하지 않은 예외이던 처리를 할 수 없는 경우가 됩니다. 이럴 때 사용자 정의 예외처리를 해야합니다!
문제 107. (오늘의 마지막 문제) 우리반 테이블을 가지고 PL/SQL 코드를 작성하시오
통신사를 물어보게하고, 통신사 입력을 하면 해당 통신사인 학생들의 이름, 나이, 통신사가 출력되게 하시오. 그런데 없는 통신사를 입력하면 해당 통신사는 없습니다 라는 메세지를 사용자정의 예외처리로 수행되게 하세요. (아래 코드의 예외처리가 안먹힌다.)
내 답
set verify off set serveroutput on accept p_telecom prompt '통신사를 입력하세요 ' declare v_telecom emp17.telecom%type := '&p_telecom'; e_invalid exception; -- 예외처리 만들기 begin if v_telecom not in ('kt', 'sk', 'lg') THEN -- 입력된게 얘네 아니면 RAISE e_invalid; -- 예외처리 end if; for emp17_record in ( select ename, age, telecom from emp17 where telecom= v_telecom ) loop dbms_output.put_line( emp17_record.ename || chr(9) || emp17_record.age || chr(9) || emp17_record.telecom ); end loop; -- 맞다면 loop문 commit; EXCEPTION WHEN e_invalid THEN dbms_output.put_line(chr(9)); dbms_output.put_line('해당 통신사는 없습니다.'); end; /다른 분 코드!
set serveroutput on set verify off accept p_telecom prompt '통신사 입력하세요 '; DECLARE v_num number; -- 예외처리를 위한 변수 e_exp exception; -- 예외처리할거 만들어준다. begin select count(*) into v_num -- 통신사 입력했을 때 해당 통신사의 갯수를 v_num 에 넣는다 from emp17 where telecom= lower('&p_telecom'); if v_num = 0 then -- 만약에 통신사가 emp17 안에 없는게 입력되었다면 카운트가 0일것. raise e_exp; -- 그럴 때 예외처리! end if; for emp17_record in ( select ename, age, telecom from emp17 where telecom= lower('&p_telecom') ) loop dbms_output.put_line( emp17_record.ename || chr(9) || emp17_record.age || chr(9) || emp17_record.telecom ); end loop; exception when e_exp then -- 만약에 e_exp 면 아래 메세지 출력! dbms_output.put_line('해당 통신사는 없습니다.'); end; /
알게된것
- update, delete, ? 에서만 명시적 암시적 커서가 최근에 수행된게 ....? 된다. 위 문제는 select 문이라서 if sql%notfound 를 (커서) 쓸수가없다.
-> 설명 다시듣기..ㅎㅎ
