[인터넷DB응용] PL-SQL

티스토리 뷰

CS/인터넷DB응용

[인터넷DB응용] PL-SQL - 2

JeongeunChoi 2023. 4. 17. 23:18

에러 처리

에러 처리 방법

PL/SQL 블록 내의 SQL문이 정상적으로 실행되지 못할 때 에러가 발생한다.
에러가 발생하면 EXCEPTION절에 의해 처리할 수 있다.
자주 발생하는 에러처리기는 오라클 사에서 기본적으로 제공하며, 자주 발생하지 않는 에러는 사용자가 직접 정의할 수 있다.

Predefined (Internal) Exception

UnDefined Exception

User Defined Internal Exception

Exception Trapping Function

미리 정의된 에러 처리

EXCEPTION 절은 자바의 try-catch 문과 같음.

DECLARE

BEGIN

 EXCEPTIION
 WHEN [예외조건-1] THEN
  [처리문장-1];
 WHEN [예외조건-2] THEN
  [처리문장-2];
 .........
 WHEN OTHERS THEN
  [처리문장-n];
  
END;

-- NO_DATA_FOUND: 조건을 만족하는 행이 없는 경우
-- NOT_LOGGED_ON: DB에 접속하지 않은 채 실행하는 경우
-- TOO_MANY_ROWS: Scalar 변수에 여러 개의 리턴 행을 저장하는 경우
-- VALUE_ERROR: 지정된 변수 길이보다 큰 값을 저장하는 경우
-- ZERO_DEVIDE: 변수 값을 0 값으로 나누려는 경우
-- INVALID_CURSOR: 잘못된 커서 연산이 발생하는 경우
-- INVALID_NUMBER: 수의 문자열 변환이 잘못된 경우
-- DUP_VAL_ON_INDEX: 이미 저장된 값을 다시 입력하는 경우

-- db_week6_1.sql
-- 에러 처리 예제1. 미리 정의된 에러
Create or replace procedure pre_exp
IS
   v_stuname  stu.stu_name%TYPE;
BEGIN
  
   select stu_name 
   into v_stuname
   from stu
   where stu_id = '9944444';
   dbms_output.put_line(v_stuname);

   exception 
   when NO_DATA_FOUND then
   dbms_output.put_line('만족하는 데이터가 없습니다.');   
   
END pre_exp;
/

미리 정의되지 않은 에러 처리

DECLARE
 예외조건-1 EXCEPTION;
 PRAGMA EXCEPTION_INIT
 ([예외조건-1], [에러코드]);

BEGIN

  EXCEPTION
 	WHEN [예외조건-1] THEN
      [처리문장-1];
     ......
     WHEN OTHERS THEN
      [처리문장-n];

END;

->

DECLARE
 e_invalid EXCEPTION;
 PRAGMA EXCEPTION_INIT
 (e_invalid, -2292);

BEGIN

  EXCEPTION
 	WHEN e_invalid THEN
     [처리문장-1];
    ......
    WHEN OTHERS THEN
     [처리문장-n];
END;

사용자가 정의하는 에러 처리

DECLARE
 예외조건-1 EXCEPTION;

BEGIN
 RAISE 예외조건-1;

  EXCEPTION
 	WHEN [예외조건-1] THEN
      [처리문장-1];
     ......
    WHEN OTHERS THEN
      [처리문장-n];

END;

-> 

DECLARE
 e_invalid EXCEPTION;

BEGIN
 RAISE e_invalid;

  EXCEPTION
 	WHEN e_invalid THEN
      [처리문장-1];
     ......
    WHEN OTHERS THEN
      [처리문장-n];

END;

-- db_week6_2.sql
-- 에러 처리 예제2. 사용자 정의 에러
Create or replace procedure user_exp
(v_stuid stu.stu_id%TYPE)
IS
   v_stuname  stu.stu_name%TYPE;
   invalid_stuname exception;
BEGIN
  
   select stu_name 
   into v_stuname
   from stu
   where stu_id = v_stuid;
   
   if v_stuname <> 'cmlee' then
      dbms_output.put_line(v_stuname);
   else 
      raise invalid_stuname;
   end if;

   exception 
   when invalid_stuname then
   dbms_output.put_line('잘못된 학생 이름입니다.');   
   
END user_exp;
/

예외 트래핑 함수

트래핑 함수

예외조건	설명
SQLCODE	에러코드를 리턴 해 줍니다.
SQLERRM	에러코드에 대한 에러 메시지를 리턴 해 줍니다.

SQLCODE 종류

예외조건	설명
0	에러 없이 정상적으로 실행되었습니다.
1	사용자가 정의한 에러가 발생하였습니다.
+100	해당 조건을 만족하는 행을 찾지 못했습니다.
양수	다른 오라클 서버 에러

에러출력 함수

Raise_Application_Error
( error_code, message {TRUE | FALSE} )

-- db_week6_3.sql
-- 에러 처리 예제2. 사용자 정의 에러
Create or replace procedure exp_trap
(v_stuid stu.stu_id%TYPE)
IS
   v_stuname  stu.stu_name%TYPE;
   invalid_stuname exception;
BEGIN
  
   select stu_name 
   into v_stuname
   from stu
   where stu_id = v_stuid;
   
   if v_stuname <> 'cmlee' then
      dbms_output.put_line(v_stuname);
   else 
      raise invalid_stuname;
   end if;

   exception 
   when invalid_stuname then
   dbms_output.put_line('잘못된 학생 이름입니다.');   
   when others then
   dbms_output.put_line(SQLCODE || ' ::: ' || SQLERRM);   
   
END exp_trap;
/

Package

패키지

자주 사용되는 프로그램과 로직을 모듈화 할 수 있습니다.
응용 프로그램을 쉽게 개발할 수 있습니다.
프로그램의 처리흐름을 노출하지 않아 보안기능이 좋습니다.
프로그램에 대한 유지보수 작업이 편해집니다.
같은 이름의 프로시저와 함수를 여러 개 생성할 수 있습니다.

Syntax

패키지 명세

패키지 안에는 이러이러한 변수가 있다.

패키지 몸체

지역변수 선언은 없다고 보고
Function이나 Procedure 안에 변수가 설정되어 있다.
패키지 안에 있는 것들을 구현하기 위함.

-- db_week6_4.sql
-- 패키지 선언 예
Create or Replace package show_stu IS
 procedure showbyid (v_stuid IN varchar2);
 procedure showbyname (v_stuname IN varchar2);
end show_stu;
/

-- 패키지 내의 함수 선언
Create or Replace package body show_stu IS
 procedure showbyid (v_stuid IN varchar2)
 IS 
   v_stuname  stu.stu_name%TYPE;
   v_studept  stu.stu_dept%TYPE;
   CURSOR stu_cursor IS
   select stu_name, stu_dept from stu
   where stu_id = v_stuid;
 BEGIN
   open stu_cursor;
   loop 
      fetch stu_cursor into v_stuname, v_studept;
      exit when stu_cursor%NOTFOUND;
      
      dbms_output.put_line(v_stuid || ' ' || v_stuname || ' ' || v_studept);
   end loop;
   close stu_cursor;
 END showbyid;
 
 procedure showbyname (v_stuname IN varchar2)
 IS 
   v_stuid  stu.stu_id%TYPE;
   v_studept  stu.stu_dept%TYPE;
   CURSOR stu_cursor IS
   select stu_id, stu_dept from stu
   where stu_name = v_stuname;
 BEGIN
   open stu_cursor;
   loop 
      fetch stu_cursor into v_stuid, v_studept;
      exit when stu_cursor%NOTFOUND;
      
      dbms_output.put_line(v_stuid || ' ' || v_stuname || ' ' || v_studept);
   end loop;
   close stu_cursor;
 END showbyname; 
end show_stu;
/

execute show_stu.showbyid('9911111');
execute show_stu.showbyname('sslee');

오버로딩

패키지 내에서 동일한 이름의 프로시저를 여러 개 만들 수 있습니다.
매개변수의 개수나 순서 혹은 데이터 타입은 달라야 합니다.(시그니처)

Forward Declaration

어떤 프로시저나 함수를 호출할 때 호출할 함수나 프로시저는 해당 프로시저보다 먼저 정의되어야 합니다.

One-Time Only 프로시저

패키지가 사용자 세션에서 처음으로 호출될 때 One-Time Only 프로시저가 한번 실행됩니다.

오라클 제공 패키지

DBMS_OUTPUT: PL/SQL 블록 내의 변수 데이터를 화면에 출력할 때 사용합니다.
DBMS_SQL: PL/SQL 블록 내에서 Dynamic-SQL문을 구현할 때 사용합니다.
DBMS_JOB: 특정 PL/SQL 블록을 스케쥴링하여 실행할 때 사용합니다.
DBMS_DDL: PL/SQL 블록 내에서 DDL문을 실행할 때 사용합니다.
DBMS_PIPE: DB에 접속된 다른 사용자에게 메시지를 전송할 때 사용합니다.
DBMS_AQ: DBMS_PIPE의 향상된 기능으로 DB에 접속된 다른 사용자에게 메시지를 전송할 때 사용합니다.
DBMS_SESSION: DB에 접속된 다른 사용자의 정보를 참조할 때 사용합니다.
UTL_FILE: O/S상의 텍스트 파일을 PL/SQL 내에서 읽을 때 사용합니다.

Trigger

어떤 테이블에 테이블 변경이 발생하는 작업들(insert update delete) 실행 전 혹은 실행 후에 같이 실행되어야 할 작업을 걸어주는 것

Data Integrity

Create trigger chk_emp
Before update or delete or insert on emp
Begin
 If Len(:new.no) <> 4 Then
 Dbms_output.put_line('입력할 수 없음 ');
 end if;
End;

Create table emp
( no number(4) Primary Key,
  name varchar2(10) Not Null,
  Loc varchar2(15) CHECK (Loc in ('서울', '부산')),
  jumin_no char(15) Unique,
  deptno number(2) References dept(deptno));

CREATE  TRIGGER   CHECK_SALARY
BEFORE  update or delete or insert on EMP
Begin
   IF (to_char(sysdate, ‘DY’) IN (‘SAT’,’SUN’))
   THEN
      raise_application_error(-20500,
           ‘주말에는 변경할 수 없습니다 !!’);
   END IF;
END;

-- cmd
update emp set sal = sal * 2;

Statement Trigger

기본 테이블에 DML문이 실행되면 여러 행에 대한 변경이 발생하더라도 트리거가 한 번만 발생합니다.

UPDATE EMP SET 급여 = 급여 * 1.1;

CREATE TRIGGER  trigger_name  IS
[타이밍]   [이벤트-1]  [OR 이벤트-2 OR ,,,,,,,]
ON  table_name
Begin
   ,,,,,,,
End;

CREATE TRIGGER  check_emp  IS
BEFORE  INSERT or UPDATE or DELETE
ON emp
Begin
   ,,,,,,,
End;

Row-Level Trigger

기본 테이블에 DML문이 실행될 때 여러 행에 대한 변경이 발생하면 각 행에 대해 트리거가 발생합니다.
변경되는 레코드마다 트리거가 한번씩 수행된다.
old와 new 구분을 위해서는 Row-Level Trigger로 만들어야 된다.

UPDATE EMP SET 급여 = 급여 * 1.1;

Syntax

CREATE TRIGGER  trigger_name
[타이밍]   [이벤트-1]  [OR 이벤트-2 OR ,,,,,,,]
ON  table_name
FOR EACH ROW
[WHEN  조건절]
Begin
   ,,,,,,,
End;

CREATE TRIGGER  check_emp
BEFORE  INSERT or UPDATE or DELETE
ON emp
FOR EACH ROW
WHEN ( :new.job = ‘SALESMAN’ )
Begin
   ,,,,,,,
End;

백업의 작업 기록 테이블

원래 테이블의 속성의 개수가 n개면, 2n개가 필요하고, 어떤 작업인지 적어줄 1개가 필요하여 2n+1개가 필요함.
본 테이블에 insert, update, delete 작업을 하면 트리거가 자동으로 작업 이력 테이블에 입력하도록,
- insert는 new 속성값만 필요
- update는 old, new 속성값 필요
- delete는 old 속성값만 필요

Create trigger backup_emp
Before update or delete or insert on emp
For each row
Begin
If Inserting then 
Insert into back_emp(deptno, dname, sal, gu)
Values(:new.deptno,:new.name,:new.sal,’입력’);
Elsif Updating then
Insert into back_emp
Values(:new.deptno,:new.name,:new.sal,
       :old.deptno,:old.name,:old.sal,’수정’);
Elsif Deleting then
Insert into back_emp(o_deptno, o_dname, o_sal, gu)
Values(:old.deptno,:old.name,:old.sal,’삭제’);
end if; 
End;

-- db_week6_5.sql
-- 트리거 예제
Create or replace trigger chk_stu
Before update or delete or insert on stu 
for each row
Begin
  If :new.stu_id = '9944444' Then
  	raise_application_error(-20500, '입력할 수 없음!!');
  end if;
End;
/

insert into stu values ('9944444','jjkim','economy');
-- 입력할 수 없음!! 에러 발생

-- 트리거 없이 원래 중복되면 
-- 무결성 제약 조건에 위배된다고 에러 뜸.

트리거의 변경

-- db_week6_6.sql
-- 트리거의 상태를 비활성화 또는 활성화 할 수 있다.
ALTER TRIGGER trigger_name DISABLE | ENABLE;

-- 해당 테이블과 관련된 모든 트리거의 상태를 비활성화 또는 활성화 할 수 있다.
ALTER TABLE table_name
DISABLE | ENABLE ALL TRIGGER;

-- 트리거를 재 컴파일할 수 있다.
ALTER TRIGGER trigger_name COMPILE;

-- 트리거를 삭제할 수 있다.
DROP TRIGGER trigger_name;

alter trigger chk_stu disable
/

insert into stu values ('9944444','jjkim','economy');

프로시저와 트리거의 차이점

프로시저	트리거
CREATE PROCEDURE 문법사용	CREATE TRIGGER 문법사용
생성하면 소스코드와 실행코드(P-CODE)가 생성됨	생성하면 소스코드와 실행코드(P-CODE)가 생성됨
EXECUTE 명령어로 실행	생성 후 자동실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 안됨

트리거의 응용범위

범위	설명
보안	데이터베이스 내 테이블에 대한 변경을 제한할 수 있습니다.
감사	사용자들의 데이터베이스 사용에 대한 모든 내용을 감시할 수 있습니다.
데이터의 무결성	테이블에 원치 않는 데이터가 저장되는 것을 방지할 수 있습니다.
테이블의 복제	기본 테이블에 대한 똑같은 복사 테이블을 온라인으로 생성, 관리할 수 있습니다.
연속적 작업수행	기본 테이블에 데이터가 입력되면 또 다른 테이블에 데이터를 변경하는 연속적인 작업을 할 수 있습니다.

실습 문제

-- 1. 학과 이름을 입력받아서 stu 테이블에서 그 학과인 학생의 stu_id와 stu_name을 출력하는 프로시저를 만드시오.
Create or replace procedure print_stuidname
( v_studept stu.stu_dept%TYPE)
IS
	v_stuid stu.stu_id%TYPE;
	v_stuname stu.stu_name%TYPE;
	CURSOR stu_cursor IS
	select stu_id, stu_name
	from stu
	where stu_dept=v_studept;
Begin
	OPEN stu_cursor;
	loop
	   fetch stu_cursor into v_stuid, v_stuname;
	   exit when stu_cursor%NOTFOUND;

	   dbms_output.put_line('학번: ' || v_stuid || ', 학과: ' || v_stuname);
	end loop;
	close stu_cursor;
End print_stuidname;
/ 

-- 2. 1번과 같은 일을 하는 프로시저를 커서의 매개 변수를 사용한 형태로 만들어 보시오.
Create or replace procedure show_stuidname
( v_studept stu.stu_dept%TYPE)
IS
	v_stuid stu.stu_id%TYPE;
	v_stuname stu.stu_name%TYPE;
	CURSOR stu_cursor(vv_studept stu.stu_dept%TYPE) IS
	select stu_id, stu_name
	from stu
	where stu_dept=vv_studept;
Begin
	OPEN stu_cursor(v_studept);
	loop
	   fetch stu_cursor into v_stuid, v_stuname;
	   exit when stu_cursor%NOTFOUND;
	   
	   dbms_output.put_line('학번: ' || v_stuid || ', 학과: ' || v_stuname);
	end loop;
	close stu_cursor;
END show_stuidname;
/


-- 3. sugang 테이블에서 stu_id가 '9933333'인 모든 투플의 stu_id를 '9922222'로 바꾸는 프로시저를 만드시오.
Create or replace procedure change_stuid
IS
	v_stuid stu.stu_id%TYPE;
	CURSOR sg_cursor IS
	select stu_id from sugang
	where stu_id = '9933333';
Begin
	OPEN sg_cursor;
	loop
	   fetch sg_cursor into v_stuid;
	   exit when sg_cursor%NOTFOUND;
	   
	   update sugang set stu_id='9922222'
	   where stu_id = v_stuid;
	end loop;
	close sg_cursor;
End change_stuid;
/

Create or replace procedure assignment3
IS       
BEGIN
        update sugang set stu_id = '9922222'
        where stu_id = '9933333'; 
END assignment3;
/
	   

-- 4. stu 테이블에 학번과 이름과 학과를 인자로 받아서 입력하는 프로시저를 만드는데  stu_id가 중복되는 레코드를 입력했을 때 에러 메시지를 출력하고 정상적으로 프로그램이 종료할 수 있도록 하는 프로시저를 만드시오.
Create or replace procedure add_stu
(v_stuid stu.stu_id%TYPE,
 v_stuname stu.stu_name%TYPE,
 v_studept stu.stu_dept%TYPE)
IS
	v_stuidcnt NUMBER;
	duplicated_stuid exception;
BEGIN
	select count(*) into v_stuidcnt
	from stu
	where stu_id = v_stuid;

	if v_stuidcnt = 0 then
	   insert into stu
	   values(v_stuid, v_stuname, v_studept);
	else
	   raise duplicated_stuid;
	end if;

	exception
	when duplicated_stuid then
	dbms_output.put_line('동일한 학번이 존재합니다.');
END add_stu;
/

-- 5. subject 테이블에 과목 번호와 과목 이름과 개설 학과를 인자로 받아서 입력하는 프로시저를 만드는데  sj_id가 중복되는 레코드를 입력했을 때 에러 메시지를 출력하고 정상적으로 프로그램이 종료하도록 하고 sj_name이 'database'이면 에러 메시지를 출력하고 정상적으로 프로그램이 종료할 수 있도록 하는 프로시저를 만드시오.
Create or replace procedure add_subject
(v_sjid subject.sj_id%TYPE,
 v_sjname subject.sj_name%TYPE,
 v_sjdept subject.sj_dept%TYPE)
IS
	v_sjidcnt NUMBER;	
	duplicated_sjid exception;
	invalid_sjname exception;
BEGIN
	select count(*) into v_sjidcnt
	from subject
	where sj_id = v_sjid;
	
	if v_sjidcnt > 0 then
	   raise duplicated_sjid;
	elsif v_sjname = 'database' then
	   raise invalid_sjname;
	else
	   insert into subject
	   values(v_sjid, v_sjname, v_sjdept);
	end if;

	exception
	when duplicated_sjid then
	dbms_output.put_line('동일한 과목번호가 존재합니다.');
	when invalid_sjname then
	dbms_output.put_line('과목이름이 database 입니다.');

END add_subject;
/	

-- 6. 수강 내역을 확인하는 프로시저들을 묶어서 패키지를 만들려고 한다. 포함되는 프로시저는 학번을 매개 변수로 하여 그 학생이 듣는 모든 과목명을 보여 주는 프로시저와 과목 id를 매개 변수로 하여 그 과목을 듣는 모든 학생들의 이름을 보여 주는 프로시저이다. 패키지를 선언하고 프로시저들을 만드시오.
Create or Replace package show_sjnamestuname IS
 procedure showbystuid (v_stuid IN stu.stu_id%TYPE);
 procedure showbysjid (v_sjid IN subject.sj_id%TYPE);
end show_sjnamestuname;
/

Create or Replace package body show_sjnamestuname IS
 procedure showbystuid (v_stuid IN stu.stu_id%TYPE)
 IS
   v_sjname subject.sj_name%TYPE;
   CURSOR sj_cursor IS
   select subject.sj_name from sugang, subject
   where sugang.sj_id = subject.sj_id
   and sugang.stu_id = v_stuid;
 Begin
   open sj_cursor;
   loop
      fetch sj_cursor into v_sjname;
      exit when sj_cursor%NOTFOUND;
      
      dbms_output.put_line('과목명 : ' || v_sjname);
   end loop;
   close sj_cursor;
 END showbystuid;

 procedure showbysjid (v_sjid IN subject.sj_id%TYPE)
 IS
   v_stuname stu.stu_name%TYPE;
   CURSOR stu_cursor IS
   select stu.stu_name from stu, sugang
   where stu.stu_id = sugang.stu_id
   and sugang.sj_id = v_sjid;
 Begin
   open stu_cursor;
   loop
      fetch stu_cursor into v_stuname;
      exit when stu_cursor%NOTFOUND;
      
      dbms_output.put_line('학생이름 : ' || v_stuname);
   end loop;
   close stu_cursor;
 END showbysjid;
END show_sjnamestuname;
/
   
-- 7. sugang 테이블에 데이터가 변경되었을 때(insert, update, delete 모두) 그 변경 내용을 기록하기 위한 테이블 backup_sg를 만들고 데이터가 변경될 때마다 그 내용을 backup_sg에 저장하는 트리거를 만드시오. 
create table backup_sg (
	stu_id VARCHAR(7),
	sj_id VARCHAR(5),
	o_stu_id VARCHAR(7),
	o_sj_id VARCHAR(5),
	gu VARCHAR(17)
);

Create trigger backup_sg
Before update or delete or insert on sugang
For each row
Begin
If Inserting then
Insert into backup_sg(stu_id, sj_id, gu)
Values(:new.stu_id, :new.sj_id, '입력');
Elsif Updating then
Insert into backup_sg
Values(:new.stu_id, :new.sj_id, :old.stu_id, :old.sj_id, '수정');
Elsif Deleting then
Insert into backup_sg(o_stu_id, o_sj_id, gu)
Values(:old.stu_id, :old.sj_id, '삭제');
end if;
End;
/

'CS > 인터넷DB응용' 카테고리의 다른 글

[인터넷DB응용] 데이터베이스 전체 설계 과정 (0)	2023.06.09
[인터넷DB응용] 데이터베이스 설계 - 1 (0)	2023.06.09
[인터넷DB응용] PL-SQL - 1 (0)	2023.04.16
[인터넷DB응용] SQL 활용 - 3 (0)	2023.04.14
[인터넷DB응용] SQL 활용 - 2 (0)	2023.04.11

je_record