| [문제] 사원번호를 입력 받아서 사원의 last_name을 출력해주세요. |
| /* 호출 */ execute emp_find.find(100) execute DBMS_OUTPUT.PUT_LINE(emp_find.find(100)) SELECT employee_id, emp_find.find(employee_id) FROM employees; |
| CREATE OR REPLACE PACKAGE emp_find IS PROCEDURE find(p_id IN number); FUNCTION find(p_id IN number) RETURN varchar2; END; / CREATE OR REPLACE PACKAGE BODY emp_find IS /* PROCEDURE */ PROCEDURE find(p_id IN number) IS v_name varchar2(30); BEGIN SELECT last_name INTO v_name FROM hr.employees WHERE employee_id = p_id; DBMS_OUTPUT.PUT_LINE(v_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; /* FUNCTION */ FUNCTION find(p_id IN number) RETURN varchar2 IS v_name varchar2(30); BEGIN SELECT last_name INTO v_name FROM hr.employees WHERE employee_id = p_id; RETURN v_name; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; END; / |
# 에러 확인
SHOW ERROR
SELECT * FROM user_errors;
# 객체
SELECT * FROM user_objects
# 패키지 안에 생성된 명시적 커서 지속상태
| CREATE OR REPLACE PACKAGE pack_cursor IS PROCEDURE open; PROCEDURE next(p_num IN number); PROCEDURE close; END pack_cursor; / CREATE OR REPLACE PACKAGE BODY pack_cursor IS /* private cursor : package hody에서는 어디서든지 사용할 수 있다. */ CURSOR c1 IS SELECT employee_id, last_name FROM hr.employees ORDER BY 1 desc; PROCEDURE open IS BEGIN IF NOT c1%ISOPEN THEN -- 오픈되어있지 않으면 TRUE OPEN c1; DBMS_OUTPUT.PUT_LINE('c1 CURSOR OPEN'); END IF; END; PROCEDURE next(p_num IN number) IS v_id number; v_name varchar2(30); BEGIN LOOP EXIT WHEN c1%rowcount >= p_num; FETCH c1 INTO v_id, v_name; DBMS_OUTPUT.PUT_LINE('ID : '||v_id||' NAME : '||v_name); END LOOP; -- FETCH 계속 열려있다 -> 닫기 전까지 데이터 누적 END; PROCEDURE close IS BEGIN IF c1%ISOPEN THEN CLOSE c1; DBMS_OUTPUT.PUT_LINE('c1 CURSOR CLOSE'); END IF; END; END pack_cursor; / |
| /* 호출 */ execute pack_cursor.open -- 로드. 1번행 기억 execute pack_cursor.next(3) -- 1번행 ~ 3번행까지 출력 execute pack_cursor.next(6) -- cursor.next 프로시저 안에 CLOSE가 없기 때문에 연속 진행 6번행까지 출력 execute pack_cursor.close |
| /* 출력 */ c1 CURSOR OPEN ID : 206 NAME : Gietz ID : 205 NAME : Higgins ID : 204 NAME : Baer ID : 203 NAME : Mavris ID : 202 NAME : Fay ID : 201 NAME : Hartstein c1 CURSOR CLOSE |
# 패키지안에서 생성된 global, private 변수는 session이 종료할 때까지 지속적으로 변경한 값을 사용한다.
| CREATE OR REPLACE PACKAGE comm_pkg IS PROCEDURE reset_comm(p_comm IN number); END; / CREATE OR REPLACE PACKAGE BODY comm_pkg IS g_comm number := 0.1; -- private 변수 /* private function */ FUNCTION validation_comm(v_comm IN number) RETURN BOOLEAN -- TRUE, FALSE, NULL IS v_max_comm number; -- local 변수 BEGIN SELECT max(commission_pct) INTO v_max_comm FROM hr.employees; IF v_comm > v_max_comm THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END validation_comm; /* global procedure */ PROCEDURE reset_comm(p_comm IN number) IS BEGIN IF validation_comm(p_comm) THEN DBMS_OUTPUT.PUT_LINE('OLD : '||g_comm); g_comm := p_comm; DBMS_OUTPUT.PUT_LINE('NEW : '||g_comm); ELSE RAISE_APPLICATION_ERROR(-20000,'기존 최고값을 넘을 수 없습니다.'); END IF; END reset_comm; END comm_pkg; / |
| /* 호출 */ execute comm_pkg.reset_comm(0.2); |
| /* 연속호출 결과 */ OLD : .1 NEW : .2 OLD : .2 NEW : .2 OLD : .2 NEW : .2 -- comm_pkg.reset_comm 프로시저 내 0.2 -> p_comm -> g_comm 되어 0.2로 저장되어있다. ... |
# PRAGMA SERIALLY_REUSABLE : 패키지내에서 생성된 생성자들의 변경한 값은 호출 동안에만 변경한 값을 사용하고 호출이 끝나면 원래 값으로 되돌아가는 기능
| CREATE OR REPLACE PACKAGE comm_pkg IS PRAGMA SERIALLY_REUSABLE; PROCEDURE reset_comm(p_comm IN number); END; / CREATE OR REPLACE PACKAGE BODY comm_pkg IS PRAGMA SERIALLY_REUSABLE; -- 지시어 g_comm number := 0.1; -- private 변수 /* private function */ FUNCTION validation_comm(v_comm IN number) RETURN BOOLEAN -- TRUE, FALSE, NULL IS v_max_comm number; -- local 변수 BEGIN SELECT max(commission_pct) INTO v_max_comm FROM hr.employees; IF v_comm > v_max_comm THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END validation_comm; /* global procedure */ PROCEDURE reset_comm(p_comm IN number) IS BEGIN IF validation_comm(p_comm) THEN DBMS_OUTPUT.PUT_LINE('OLD : '||g_comm); g_comm := p_comm; DBMS_OUTPUT.PUT_LINE('NEW : '||g_comm); ELSE RAISE_APPLICATION_ERROR(-20000,'기존 최고값을 넘을 수 없습니다.'); END IF; END reset_comm; END comm_pkg; / |
| /* 호출 */ execute comm_pkg.reset_comm(0.2); |
| /* 연속호출 결과 */ OLD : .1 NEW : .2 OLD : .1 NEW : .2 OLD : .1 NEW : .2 -- PRAGMA SERIALLY_REUSABLE 적용 ... |
<< 상수 표준화 >>
| CREATE OR REPLACE PACKAGE global_consts IS c_mile_2_kilo CONSTANT number := 1.6093; c_kilo_2_mile CONSTANT number := 0.6214; c_yard_2_meter CONSTANT number := 0.9144; c_meter_2_yard CONSTANT number := 1.0936; END; / |
| BEGIN DBMS_OUTPUT.PUT_LINE('20mile = '||20 * global_consts.c_mile_2_kilo||'km'); END; / BEGIN DBMS_OUTPUT.PUT_LINE('20km = '||20 * global_consts.c_kilo_2_mile||'mile'); END; / BEGIN DBMS_OUTPUT.PUT_LINE('20yard = '||20 * global_consts.c_yard_2_meter||'m'); END; / BEGIN DBMS_OUTPUT.PUT_LINE('20m = '||20 * global_consts.c_meter_2_yard||'yd'); END; / CREATE OR REPLACE FUNCTION meter_to_yard(p_m IN number) RETURN number IS BEGIN RETURN(p_m * global_consts.c_meter_2_yard); END; / SELECT meter_to_yard(100) FROM dual; |
| # 상수는 한번 입력한 값만 사용해야 한다. execute global_consts.c_mile_2_kilo := 2.6093 -- 수정 시 오류 -- PLS-00363: expression 'GLOBAL_CONSTS.C_MILE_2_KILO' cannot be used as an assignment target |
| desc departments 이름 널? 유형 --------------- -------- ------------ DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) INSERT INTO hr.departments(department_id, department_name) VALUES(280,'DA'); SELECT * FROM hr.departments; rollback; INSERT INTO hr.departments(department_id, department_name) VALUES(280,null); -- ORA-01400: cannot insert NULL into |
↓
| DECLARE e_insert_null EXCEPTION; PRAGMA EXCEPTION_INIT(e_insert_null,-01400); BEGIN INSERT INTO hr.departments(department_id, department_name) VALUES(280,null); EXCEPTION WHEN e_insert_null THEN DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED'); DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / >> 오류가 있어도 EXCEPTION 핸들링으로 정상 종료 됨. >> 사용자마다 오류명칭 다양 |
↓
<<EXCEPTION 이름 표준화>>
| CREATE OR REPLACE PACKAGE err_pkg IS e_insert_null EXCEPTION; PRAGMA EXCEPTION_INIT(e_insert_null,-01400); END; / |
| BEGIN INSERT INTO hr.departments(department_id, department_name) VALUES(280,null); EXCEPTION WHEN err_pkg.e_insert_null THEN DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED'); DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / |
■ Trigger 트리거
- 트리거는 데이터베이에 저장되고 지정된 이벤트에 대한 응답으로 실행되는 PL/SQL블록이다.
- 오라클 데이터베이스는 지정된 조건이 발생할 때 트리거를 자동으로 실행한다.
- 트리거는 테이블, 뷰, 스키마(소유자), 데이터베이스(모든 유저)에 정의할 수 있다.
- CREATE TRIGGER 시스템 권한 필요하다.
# 트리거 유형
- 특정 테이블, 뷰에 INSERT, UPDATE, DELETE 문
- CREATE, ALTER, DROP 문
- 데이터베이스 시작 또는 종료
- 특정 오류 메세지 또는 임의의 오류 메세지
- 유저 로그인, 로그오프
| /* CREATE TRIGGER 권한 확인 */ SELECT * FROM session_privs; |
DROP TABLE hr.dept PURGE;
CREATE TABLE hr.dept AS SELECT * FROM hr.departments;
<< DML TRIGGER>>
| /* BEFORE 문장 트리거 */ /* 작업 수행 전 트리거 실행. 제약조건체크 등 */ CREATE OR REPLACE TRIGGER dept_before BEFORE INSERT ON hr.dept -- INSERT 작업 전 트리거 BEGIN DBMS_OUTPUT.PUT_LINE('INSERT하기 전에 문장트리거 수행'); END; / |
| /* AFTER 문장 트리거 */ /* 작업의 영향이 있든 없든 무조건 맨 뒤에 수행 */ CREATE OR REPLACE TRIGGER dept_after AFTER INSERT ON hr.dept BEGIN DBMS_OUTPUT.PUT_LINE('INSERT한 후에 문장트리거 수행'); END; / |
| /* BEFORE 행 트리거 */ /* 누가 행을 수정하려는지 작업 전 수행 */ CREATE OR REPLACE TRIGGER dept_row_before BEFORE INSERT ON hr.dept FOR EACH ROW -- 행트리거 BEGIN DBMS_OUTPUT.PUT_LINE('INSERT하기 전에 행트리거 수행'); END; / |
| /* AFTER 행 트리거 */ /* 누가 행을 수정하고 갔는지 영향을 받은 행이 있는 경우 수행 */ CREATE OR REPLACE TRIGGER dept_row_after AFTER INSERT ON hr.dept FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('INSERT한 후에 행트리거 수행'); END; / |
| /* 생성된 트리거 확인 */ SELECT * FROM user_triggers; INSERT INTO hr.dept(department_id, department_name, manager_id, location_id) VALUES(300, 'Data Architect', 100, 1500); >> 결과 INSERT하기 전에 문장트리거 수행 INSERT하기 전에 행트리거 수행 INSERT한 후에 행트리거 수행 INSERT한 후에 문장트리거 수행 |
# FOR EACH ROW : 행 트리거, 영향을 받은 행이 있는 경우 수행되는 트리거
DROP TABLE hr.copy_emp PURGE;
CREATE TABLE hr.copy_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM hr.employees;
| CREATE OR REPLACE TRIGGER test_trig BEFORE /* DELETE OR INSERT -> 행단위 작업 // UPDATE (OF salary) -> 행, 칼럼단위 작업 */ DELETE OR INSERT OR UPDATE OF salary ON hr.copy_emp /* 행트리거 */ FOR EACH ROW /* WHEN() : 행트리거에서만 사용하는 조건절, 옵션 */ WHEN(new.department_id = 20 OR old.department_id = 10) /* 선언부에서는 new, old 앞에 기호가 없다. new : INSERT,UPDATE, old : DELETE,UPDATE */ DECLARE salary_diff number; BEGIN /* 작업마다 다르게 트리거 설정 시 조건문 사용 */ /* DML 작업에 대한 조건 설정 시 명칭 오류때문에 조건부 술어 DELETING, INSERTING, UPDATING 사용*/ IF DELETING THEN DBMS_OUTPUT.PUT_LINE('OLD SALARY : '||:old.salary); /* :old.이전데이터 , :new.수정후데이터 */ ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE('NEW SALARY : '||:new.salary); ELSE /* UPDATING */ salary_diff := :new.salary - :old.salary; -- 급여 변동 차액 DBMS_OUTPUT.PUT_LINE('ID : '||:new.employee_id||' OLD SALARY : '||:old.salary|| ' NEW SALARY : '||:new.salary||' Difference of Salary : '||salary_diff); END IF; END; / |
| UPDATE hr.copy_emp SET salary = salary * 1.1 WHERE department_id = 20; >> ID : 201 OLD SALARY : 14300 NEW SALARY : 15730 Difference of Salary : 1430 ID : 202 OLD SALARY : 6600 NEW SALARY : 7260 Difference of Salary : 660 UPDATE hr.copy_emp SET salary = salary * 1.1 WHERE department_id = 10; >> ID : 200 OLD SALARY : 5324 NEW SALARY : 5856.4 Difference of Salary : 532.4 UPDATE hr.copy_emp SET salary = salary * 1.1 WHERE department_id = 30; -- 6개 행 이(가) 업데이트되었지만 트리거 없음 |
| INSERT INTO hr.copy_emp(employee_id, last_name, salary, department_id) VALUES(300, 'Oracle', 1000, 20); >> NEW SALARY : 1000 -- new.department_id = 20 조건 적용 INSERT INTO hr.copy_emp(employee_id, last_name, salary, department_id) VALUES(400, 'HADOOP', 2000, 10); >> 트리거 없음 -- old.department_id = 10 조건으로 DELETE 또는 UPDATE에서 작동 |
| DELETE FROM hr.copy_emp WHERE department_id = 10; >> OLD SALARY : 5324 -- old.department_id = 10 조건 적용 DELETE FROM hr.copy_emp WHERE department_id = 20; >> 트리거 없음 -- new.department_id = 20 조건으로 INSERT 또는 UPDATE에서 작동 |
| DELETE FROM hr.copy_emp WHERE employee_id = 200; >> OLD SALARY : 5324 -- 200번 사원이 10번 부서 사원이기 때문에 old.department_id = 10 조건으로 트리거 작동 DELETE FROM hr.copy_emp WHERE employee_id = 100; >> 트리거 없음 -- 조건에 맞지 않아 트리거 작동안함 |
# PACKAGE
- 관련있는 것끼리 모아서 관리
- 오버로딩 : 하나의 명칭으로 여러 작업 수행
- 글로벌변수 : SPEC 영역에 선언
- 상수표준화 : CONSTANT 사용. 고정값.
- EXCEPTION 표준화 : 패키지 SPEC영역에서 EXCEPTION 선언
PRAGMA EXCEPTION_INIT(오류변수명,-오류번호);
# PRAGMA SERIALLY_REUSABLE
패키지내에서 생성된 생성자들의 변경한 값은 호출 동안에만 변경한 값을 사용하고 호출이 끝나면 원래 값으로 되돌아가는 기능
# Explicit Cursor 명시적 커서
CURSOR 커서명 IS -- 선언
OPEN 커서명; -- OPEN : 메모리 할당
FETCH 커서명 INTO 칼럼; -- FETCH : 첫번째 데이터 변수에 로
CLOSE 커서명; -- CLOSE : 종료
# TRIGGER
CREATE OR REPLACE TRIGGER dept_before 트리거 생성
BEFORE 작업 전 or AFTER 작업 후
DELETE OR INSERT OR UPDATE OF salary ON hr.copy_emp
- DELETE, INSERT : 행단위 작업
- UPDATE (OF 칼럼명) : 행, 칼럼단위 작업
FOR EACH ROW : 행단위 작업 대상 트리거 발생
WHEN(행조건, 옵션)
DECLARE
BEGIN
- DML 명칭으로 조건 검색 시 DELETING, INSERTING, UPDATING 사용
END;
- AFTER : 작업의 영향이 없어도 마지막에 실행
- AFTER ~ FOR EACH ROW : 영향을 받은 행이 있을 경우 실행
| 선언부 | DECLARE ~ END | |
| new. | :new. | INSERT, UPDATE |
| old. | :old. | DELETE, UPDATE |
'Oracle SQL' 카테고리의 다른 글
| 24 PL/SQL Autonomous Transaction (0) | 2023.11.08 |
|---|---|
| 23 PL/SQL TRIGGER (0) | 2023.11.07 |
| 21 PL/SQL PACKAGE (1) | 2023.11.03 |
| 20 PL/SQL PROCEDURE, FUNCTION (0) | 2023.11.02 |
| 19 PL/SQL PROCEDURE (0) | 2023.11.01 |