Oracle :: PL/SQL "Package, Trigger"
2010. 6. 5. 03:07 - 청년코더
저장함수#
저장함수 생성
- create or replace function cal_bonus
( vempno in emp.empno%type)
return number
is
vsal number(7,2);
begin
select sal into vsal
from emp
where empno = vempno;
return (vsal*200);
end;
/
실행
- SQL>variable var_x number;
- SQL>execute :var_x := cal_bonus(7788);
- SQL>print var_x;
- SQL>select sal, cal_bonus(7788)
- from emp
- where empno=7788;
- variable 하면 현재 variable로 지정한 값들을 볼 수 있다.
Package#
- 자주사용되는 프로그램 로직(pprocedure, function)을 모듈화 할 수 있다.
- 응용 프로그램을 쉽게 개발할 수 있다.
- 프로그램의 처리 흐름을 노출하지 않아 보안 기능이 좋다.
- 프로그램에 대한 유지 보수 작업이 편리하다.
- 같은 이름의 프로시저와 함수를 여러개 생성할 수 있다.
- create or replace package emp_comm is
g_comm number := 0; ---패키지내의 모든 프로시저와 함수에서 참조할 수 있는 글로벌 변수
procedure reset_comm
(v_comm IN number);
end emp_comm;
/
show errors
create or replace package body emp_comm is
function validate_comm
(v_comm IN number)
return boolean
is
v_max_comm number;
begin
select max(comm)
into v_max_comm
from emp;
if v_comm > v_max_comm then
return (false);
else return (true);
end if;
end validate_comm;
procedure reset_comm
(v_comm IN number)
is
v_valid boolean;
begin
v_valid := validate_comm(v_comm);
if v_valid = true then g_comm :=v_comm;
dbms_output.put_line(g_comm);
else
raise_application_error(-20210, 'Invalid Commission');
end if;
end reset_comm;
end emp_comm;
/
show errors
실행
- SQL>set serveroutput on
- SQL>exec emp_comm.reset_comm(1500);
- SQL>exec emp_comm.reset_comm(1000);
프로시저의 오버로딩#
패키지 내에 동일한 이름의 프로시저를 여러 개 만들 수 있다. 단, 프로시저의 매개변수의 타입, 순서, 개수가 달라야한다.
- create or replace package emp_comm is
g_comm number := 0; ---패키지내의 모든 프로시저와 함수에서 참조할 수 있는 글로벌 변수
procedure reset_comm
(v_comm IN number);
procedure reset_comm
(v_comm IN number,
v_sal IN varchar2);
end emp_comm;
/
show errors
create or replace package body emp_comm is
function validate_comm
(v_comm IN number)
return boolean
is
v_max_comm number;
begin
select max(comm)
into v_max_comm
from emp;
if v_comm > v_max_comm then
return (false);
else return (true);
end if;
end validate_comm;
procedure reset_comm
(v_comm IN number)
is
v_valid boolean;
begin
v_valid := validate_comm(v_comm);
if v_valid = true then g_comm :=v_comm;
dbms_output.put_line(g_comm);
else
raise_application_error(-20210, 'Invalid Commission');
end if;
end reset_comm;
procedure reset_comm
(v_comm in number,
v_sal in varchar2)
is
begin
raise_application_error(-20210, 'Invalid Commission');
end reset_comm;
end emp_comm;
/
show errors
Forward Declaration#
패키지 body내에서 어떤 프로시저나 함수를 호출할 때 해당 프로시저보다 먼저 정의되어야 합니다.
- create or replace package emp_comm is
g_comm number := 0; ---패키지내의 모든 프로시저와 함수에서 참조할 수 있는 글로벌 변수(전역변수)
procedure reset_comm
(v_comm IN number);
end emp_comm;
/
show errors
create or replace package body emp_comm is
procedure reset_comm
(v_comm IN number)
is
v_valid boolean;
begin
v_valid := validate_comm(v_comm);
if v_valid = true then g_comm :=v_comm;
dbms_output.put_line(g_comm);
else
raise_application_error(-20210, 'Invalid Commission');
end if;
end reset_comm;
procedure reset_comm
(v_comm IN number,
v_sal IN varchar2)
is
begin
raise_application_error(-20210, 'Invalid Commission');
end reset_comm;
function validate_comm
(v_comm IN number)
return boolean
is
v_max_comm number;
begin
select max(comm)
into v_max_comm
from emp;
if v_comm > v_max_comm then
return (false);
else return (true);
end if;
end validate_comm;
end emp_comm;
/
show errors
One-Time Only 프로시저#
- 패키지가 사용자 세션에서 처음으로 호출될 때 one-time only 프로시저가 한번 실행됩니다.
- 주로 패키지가 샐행될 때 기본적으로 처리해야 할 로직이나 변수들의 초기화 값을 설정해야 하는 경우 사용되는 기능
- 패키지 body의 가장 마지막 부분에 begin절과 함께 정의하면 된다.
- create or replace package emp_comm is
g_comm number := 0; ---패키지내의 모든 프로시저와 함수에서 참조할 수 있는 글로벌 변수
procedure reset_comm
(v_comm IN number);
procedure reset_comm
(v_comm IN number,
v_sal IN varchar2);
end emp_comm;
/
show errors
create or replace package body emp_comm is
function validate_comm
(v_comm IN number)
return boolean
is
v_max_comm number;
begin
select max(comm)
into v_max_comm
from emp;
if v_comm > v_max_comm then
return (false);
else return (true);
end if;
end validate_comm;
procedure reset_comm
(v_comm IN number)
is
v_valid boolean;
begin
v_valid := validate_comm(v_comm);
if v_valid = true then g_comm :=v_comm;
dbms_output.put_line(g_comm);
else
raise_application_error(-20210, 'Invalid Commission');
end if;
end reset_comm;
procedure reset_comm
(v_comm in number,
v_sal in varchar2)
is
begin
raise_application_error(-20210, 'Invalid Commission');
end reset_comm;
begin -- one-time only procedure
select avg(sal)
into g_comm
from emp;
dbms_output.put_line(g_comm);
end emp_comm;
/
show errors
실행
- SQL>exec emp_comm.reset_comm(1000);
Trigger#
트리거 종류
- statement level trigger : 문장에서 한번만 수행되는지
- row level trigger : row 마다 수행되는지
- before trigger : 문장실행 후 수행
- after trigger : 문장실행 전 수행
트리거 기능
- 보안 - 데이터베이스 내 테이블에 대한 변경을 제한 할 수 있습니다.
- 감사 - 사용자들의 데이터베이스 사용에 대한 모든 내용을 감사할 수 있습니다.
- 데이터의 무결성 - 테이블에 원치 않는 데이터가 저장되는 것을 방지할 수 있습니다.
- 테이블의 복제 - 기본 테이블에 대한 똑같은 테이블을 온라인으로 생성, 관리할 수 있습니다.
- 연속적인 조지 처리 - 기본 테이블에 데이터가 입력되면 또 다른 테이블에 데이터를 변경하는 연속적인 작업을 할 수 있습니다.
트리거 이벤트
insert, update, delete
트리거 조건
when절로 지정
- create or replace trigger check_salary
before update or delete or insert on emp
begin
if (to_char(sysdate, 'DY') in ('토', '일')) then
raise_application_error(-20500, '주말에는 emp테이블을 변경할 수 없습니다.');
end if;
dbms_output.put_line('emp테이블의 변경에 대한 trigger 수행');
end;
/
실행
- insert into emp(empno, sal)
- values(2001, 2000);
- 시스템 날짜를 토요일로 변경하고 다시해본다.
트리거 조회
- select * from user_triggers
- col trigger_name fromat a20
- col triggering_event format a20
- select trigger_name, trigger_type, tirggering_event
- from user_triggers;
- create table dept_sam
as
select * from dept where 1=2;
alter table dept_sam
add(o_deptno number(2),
o_dname varchar2(14),
o_loc varchar2(10),
gunbun varchar2(10),
check_date date default sysdate); - create or replace trigger dept_audit_trigger
after insert or update or delete on dept
for each row -- row levle trigger
begin
if inserting then
insert into dept_sam(deptno, dname, loc, gunbun)
values(:new.deptno, :new.dname, :new.loc, '입력');
elsif updating then
insert into dept_sam(deptno, dname, loc, o_deptno, o_dname, o_loc, gunbun)
values(:new.deptno, :new.dname, :new.loc, :old.deptno, :old.dname, :old.loc, '수정');
elsif deleting then
insert into dept_sam(deptno, dname, loc, gunbun)
values(:old.deptno, :old.dname, :old.loc, '삭제');
end if;
end;
/
트리거의 상태를 활성화 도는 비활성화 할 수 있습니다.
alter trigger 트리거 이름 enable | disable;
해당 테이블과 관련된 트리거의 상태를 활성화 또는 비활성화
alter table 테이블이름 disalbe | enalbe all triger;
- 트리거 재 컴파일 명령어
alter trigger 트리거이름 complie; - 트리거 삭제 명령어
drop trigger 트리거 이름;
# 문제 : 부서번호(department_id)를 입력하면 그 부서에 근무하는 사원들의 employee_id,
last_name, job_id, salary를 화면에 출력하는 프로시져를 생성하시오. 단, 그 부서의
매니저가 제일 먼저 나오고 그 다음은 salary가 높은 사람이 먼저 나오게 하시오.
- create or replace procedure up_employees_dept_sawon
(p_department_id employees.department_id%type)
is
CURSOR employees_cursor IS
select employee_id, last_name, job_id, salary
from employees
where department_id = p_department_id
order by salary desc;
manager_rec employees_cursor%rowtype;
begin
select employee_id, last_name, job_id, salary
into manager_rec
from employees
where employee_id = (select manager_id
from departments
where department_id = p_department_id);
dbms_output.put_line('부서장 : '||to_char(manager_rec.employee_id)||' '||
manager_rec.last_name||' '||manager_rec.job_id||' '||
to_char(manager_rec.salary));
for employees_rec in employees_cursor loop
dbms_output.put_line('사원 : '||to_char(employees_rec.employee_id)||' '||
employees_rec.last_name||' '||employees_rec.job_id||' '||
to_char(employees_rec.salary));
end loop;
end;
/
exec up_employees_dept_sawon(50)
- create or replace procedure up_employees_dept_sawon
(p_department_id employees.department_id%type)
is
CURSOR employees_cursor(dept_id number, mgr_id number) IS
select employee_id, last_name, job_id, salary
from employees
where department_id = dept_id
and employee_id <> mgr_id
order by salary desc;
manager_rec employees_cursor%rowtype;
begin
select employee_id, last_name, job_id, salary
into manager_rec
from employees
where employee_id = (select manager_id
from departments
where department_id = p_department_id);
dbms_output.put_line('부서장 : '||to_char(manager_rec.employee_id)||' '||
manager_rec.last_name||' '||manager_rec.job_id||' '||
to_char(manager_rec.salary));
for employees_rec in employees_cursor(p_department_id, manager_rec.employee_id) loop
dbms_output.put_line('사원 : '||to_char(employees_rec.employee_id)||' '||
employees_rec.last_name||' '||employees_rec.job_id||' '||
to_char(employees_rec.salary));
end loop;
end;
/
show errors
exec up_employees_dept_sawon(50)
Index#
Index 종류
- unique index
oracle server가 자동으로 primary key, unique key 를 constraint 정의할 때
create unique index 인덱스명 on 테이블명(컬럼명); - non unique index
중복된 컬럼값에 대한 인덱스 생성
create index 인덱스명 on 테이블명(컬럼명);; - single column index
- composite column index
- reverse index
- compress index
- function index
- table을 저장하는 table space와 index를 저장하는 table space를 분리 table에 대해 insert, update, delete가 수행되면 oracle server는 index에 대해 insert, update, delete를 수행한다.
- create talbe idx_test
- (num1 nuimber,
- num2 number);
- desc idx_test
- declare
- begin
- for i in 1..800000 loop
- insert into idx_test values(i, i*2);
- end loop
- end;
- /
- select count(*) from idx_test;
- set timing on
- select num1, num2
- from idx_test
- where num1 = 245697;
create index test_num1_idx on idx_test(num1);- drop index test_num1_idx;
'컴퓨터 기술 > SQL,PL SQL' 카테고리의 다른 글
| Oracle :: O'REILLY의 oracle pl/sql programmi REF CURSOR (0) | 2010.06.04 |
|---|---|
| Oracle :: REF CURSOR와 CURSOR 변수 (0) | 2010.06.04 |
| Oracle :: DLL 트리거 사용법 (0) | 2010.06.04 |
| Oracle :: 전체 요약 정리 (SQL,PL/SQL) (0) | 2010.06.03 |
| Oracle :: 테이블 관련(2) 테이블의 제약조건 (0) | 2010.06.02 |
댓글 로드 중…