Exception ( 예외 ) : pl/sql 블럭이 실행되는 동안 발생하는 에러
pl/sql 의 EXCEPTION 절을 기술하여
다른 로직을 실행하여 예외 문제를 해결할 수 있다.
예외 구분
1. 사전 정의 예외 ( Oracle predefined Exception)
오라클에서 미리 정의한 예외로 ORA-XXXXX 로 정의
에러코드만 있고 에러이름이 없는 경우가 대다수
2. 사용자 정의 예외 ( User Defined Exception )
예외 발생시 해당 sql문은 중단
EXCEPTION 절이 있으면 : 예외 처리부에 기술된 문장을 실행
EXCEPTION 절이 없으면 : PL/SQL 블록 종료
예외처리방법
DECLARE
BEGIN
EXCEPTION
WHEN 예외명 [OR 예외명2] THEN
실행할 문장;
WHEN 예외명3 [OR 예외명4] THEN
실행할 문장;
WHEN OTHER THEN
실행할 문장 ( 여기서 SQLCODE, SQLERRM 속성을 통해 예외 코드를 확인 할 수 있다. )
END;
/
하나의 행이 조회되어야 하는 상황에서 여러개의 행이 반환된 경우 ( 예외처리가 없을 때 )
SET SERVEROUTPUT ON;
DECLARE
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM emp;
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('TWO_MANY_ROWS');
END;
/
사용자 예외 정의 , 사용
1. PL/SQL 블럭안에서 또다른 PL/SQL 블럭을 정의 하는 것이 가능하다.
NO_EMP : 사번을 통해서 사원을 검색하는데 해당 사번을 갖는 사원이 없을 때
SELECT
문제
SELECT
FROM emp
WHERE empno = =1;
==> NO_DATA_FOUND ==> NO_EMP
DECLARE
NO_EMP EXCEPTION;
v_ename emp.ename%TYPE;
BEGIN
BEGIN
SELECT ename INTO v_ename
FROM emp
WHERE empno = -1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE NO_EMP;
END;
EXCEPTION
WHEN NO_EMP THEN DBMS_OUTPUT.PUT_LINE('NO_EMP') ;
END;
/
PL/SQL ( function)
FUNCTION : 반환 값이 존재하는 PL/SQL 블럭
사번을 입력받아서, 해당 사원의 이름을 반환하는 FUNCTION
CREATE OR REPLACE FUNCTION getEmpName (p_empno emp.empno%TYPE)
RETURN VARCHAR2 IS
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM emp
WHERE empno = p_empno ;
RETURN v_ename;
END;
/
SELECT getEmpName(7369)
FROM dual;
SELECT getEmpName(empno)
FROM emp;
deptno - . dname
CREATE OR REPLACE FUNCTION getdeptname (p_deptno dept.deptno%TYPE)
RETURN VARCHAR2 IS
v_dname dept.dname%TYPE;
BEGIN
SELECT dname INTO v_dname
FROM dept
WHERE deptno = p_deptno ;
RETURN v_dname;
END;
/
SELECT getdeptname(30)
FROM dual;
SELECT getdeptname(30)
FROM dual;
사원이 속한 부서이름을 가져오는 방법 ( 다른 테이블의 컬럼을 확장하는 방법 )
1. join
SELECT empno , ename, emp.deptno , dept.dname
FROM emp , dept
WHERE emp.deptno = dept.deptno ;
2. 스칼라 서브쿼리
SELECT emp , ename, deptno , ( SELECT dname FROM dept WHERE deptno = emp.deptno ) dname
FROM emp ;
3. 함수를 쓰는 방법
function
SELECT empno , ename, deptno , get.deptname(deptno ) dname
FROM emp ;
분포도 : 흩어진 정도
데이터 분포도 : 특정 데이터가 발생하는 빈도
예 : emp 테이블에 empno 컬럼 : 14개 존재 데이터의 분포도가 좋다.
emp 테이블의 deptno : 값이 3개 존재 데이터의 분포도가 나쁘다. (중복되는 데이터가 많다. )
함수의 경우 오라클에서 기본적으로 캐쉬 기능을 사용
기본 캐쉬 사이즈가 20개
getDeptName(10) -> 10이라는 값을 리턴하는 것 : ACCOUNTING 오라클은 10번인자로 getDeptName 실행 했을 때
ACCOUNTING 이라는 결과값을 기억(캐싱)
이후에 동일한 인자로 함수를 실행을 하면 함수를 실행하지 않고 캐싱된 값을 반환
값이변경되면 -> 인자값이 동일하지 않다고 인식을 한다. -> 캐싱한 값을 불러오지 않겠지?
20개가 넘어가면 캐쉬오래된거를 삭제한다.
인자값이 몇개가 안되면 함수를 이용하는것이 유리
DEPTNO -> 조인하는것보다 함수를 이용하는 것이 좋다 ( 분포도가 나쁠때는)
분포도가 좋을때는 -> JOIN > 스칼라
사원이름, 매니저 사원이름
스칼라는 별로 좋은 방법은 아니다.
EX) 대한민국 국민을 F 와 M 으로 분류를 하려고 할때 JOIN 보다는 함수로 해서 캐싱으로
저장하는게 훨씬 더 좋다.
FUNCTION 실습 FUNCTION 2
CREATE OR REPLACE FUNCTION indent(p_level NUMBER)
RETURN VARCHAR2 IS
v_ret VARCHAR(200);
BEGIN
-- SELECT LPAD( ' ' , (P_level-1) * 4 , ' ') into v_ret
-- FROM dual;
v_ret := LPAD( ' ' , (P_level-1) * 4 , ' ');
RETURN v_ret;
END;
/
CREATE OR REPLACE FUNCTION indent(p_level NUMBER)
RETURN VARCHAR2 IS
v_ret VARCHAR(200);
BEGIN
SELECT LPAD( ' ' , (P_level-1) * 4 , ' ') into v_ret
FROM dual;
RETURN v_ret;
END;
/
SELECT indent(LEVEL) ||deptnm
FROM dept_h
START WITH deptcd = 'dept0'
CONNECT BY PRIOR deptcd = p_deptcd ;
SELECT LPAD ( ' ' , (level-1) * 4 , ' ') ||deptnm
indent(level) || deptnm
FROM dept_h
START WITH deptcd = 'dept0'
CONNECT BY PRIOR deptcd = p_deptcd ;
패키지 : 관련된 PL/SQL 블럭을 묶어 놓은 객체
JAVA 의 패키지와 유사하다.
==? 유사한 타입들의 모임
패키기 생성방법 : java 의 interface, class 사용 방법과 유사하다
Interface 객체명 = new class();
List
PL/SQL 패키지 생성 방법
1. 선언부
2. BODY ( 구현부 )
1.
CREATE OR REPLACE PACKAGE names AS
FUNCTION getEmpName ( p_empno emp.empno%TYPE ) RETURN VARCHAR2 ;
FUNCTION getDeptName ( p_deptno dept.deptno%TYPE ) RETURN VARCHAR2 ;
end names ;
/
--> 선언부분만 만든것
2.
body 부 생성하기 ( 위에 create 에서 선언부에서 함수가 2개가 만들어졌었음 )
CREATE OR REPLACE PACKAGE BODY names IS
FUNCTION getdeptname (p_deptno dept.deptno%TYPE)
RETURN VARCHAR2 IS
v_dname dept.dname%TYPE;
BEGIN
SELECT dname INTO v_dname
FROM dept
WHERE deptno = p_deptno ;
RETURN v_dname;
END;
FUNCTION getEmpName (p_empno emp.empno%TYPE)
RETURN VARCHAR2 IS
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM emp
WHERE empno = p_empno ;
RETURN v_ename;
END;
end;
/
SELECT names.getEmpName(empno) , names.getdeptname(deptno)
FROM emp ;
TRIGGER ( 방아쇠 ) : 설정한 이벤트에 따라 실행할 로직을 담은 객체
웹 : 로그인 버튼 클릭
==> 사용자 아이디 input 값과, 비밀번호 input 값을 파라미터로 서버로 전송
db : 테이블의 데이터가 추가되거나 변경되었을 때 pl/sql 블럭을 실행
users 테이블에 사용자 비밀번호가 변경 되었을 때
users_history 테이블에 기존에 사용하던 비밀번호를 이력으로 남길거다.
SELECT *
FROM users
CREATE TABLE users_history AS
SELECT userid, pass, sysdate mod_dt
FROM users
WHERE 1 = 2;
SELECT *
FROM users_history;
users 테이블의 pass 컬럼이 변경되었을 때 실행할 트리거를 생성
CREATE OR REPLACE TRIGGER trg_users_pass
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
--테이블 업데이트가 일어 났을때, 기존(:OLD.pass) 비밀번호와 업데이트 하려고 하는 비밀번호(:NEW.pass)가 다를 때
IF :OLD.pass != :NEW.pass THEN
INSERT INTO users_history VALUES ( :OLD.userid , :OLD.pass , SYSDATE) ;
END IF;
END;
/
SELECT *
FROM users; c6347b73d5b1f7c77f8be828ee3e871c819578f23779c7d5e082ae2b36a44
users_history 에는 데이터가 없는 상황
SELECT *
FROM users_history ;
UPDATE users SET pass = 'brownPass'
WHERE userid = 'brown' ;
//우리는 insert 한 적이 없지만 트리거에 의해서 들어간 것을 볼 수 있다.
trg_users_pass 트리거에 의해 users_history 테이블에 비밀번호 이력이 생긴 것을 볼 수 있다.
--트리거가 실행되지 않을만한 조건
UPDATE users SET alias = '곰탱이'
WHERE userid = 'brown' ;
SELECT *
FROM users_history;
이걸 조회하면 이전에 사용하던 비밀번호가 나온다.
트리거
장점 ( 시스템을 신규로 만드는 사람한테는 장점)
: users 테이블에 비밀번호를 바꾸는 로직은 작성을 해야한다.
users_history 에 이력을 남기는 로직(java 코드) 은 작성하지 않아도 됨 ( 직접 코드를 작성하는 것을 선호한다 )
단점 ( 시스템을 유지보수하는 사람한테는 단점)
: users_history 테이블에 데이터를 넣는 로직이 없는데 생성됨/
댓글
댓글 쓰기