[FUNCTION]
input과 output 존재(input은 생략될 수 있음)
input과 output의 관계를 정의하는 객체
select sysdate from dual; -- input value가 없는 함수 sysdate
select substr('abcde', 2) from dual ; -- 세번째 인수 생략 (default = -1)** 문법
CREATE [OR REPLACE] FUNCTION 함수명
(입력변수1 데이터타입 := 값,
입력변수2 데이터타입 := 값,
...)
RETURN 데이터타입
IS|AS
내부변수1 데이터타입 := 값;
내부변수2 데이터타입 := 값;
BEGIN
...
RETURN 변수명
EXCEPTION
END;
/** 예제1) emp 테이블을 사용하여 부서 번호를 입력받으면 해당 부서의 최대 급여를 return하는 함수
CREATE OR REPLACE FUNCTION max_sal
(vdeptno number
)
RETURN number
IS
vmaxsal number;
BEGIN
select max(sal) into vmaxsal
from scott.emp
where deptno = vdeptno;
RETURN vmaxsal;
END;
/
-- 조회
select max_sal(10) from dual;** 예제1-2) 위에서 만든 함수를 사용하여 각 부서별 최대 급여자의 이름, 부서번호, 급여 출력.
select ename, deptno, sal, max_sal(deptno)
from scott.emp
where sal = max_sal(deptno);
-- 한 행이라서 where에서 비교하는 게 가능하다.** 예제2) student, exam_01, hakjum 테이블을 사용하여 학번을 입력받고 해당 학생의 학점을 return하는 함수 생성
CREATE OR REPLACE FUNCTION studno_hakjum
(vstudno number
)
RETURN varchar2
IS
vgrade varchar2(10);
BEGIN
select h.grade into vgrade
from scott.student s, scott.exam_01 e, scott.hakjum h
where e.total between h.min_point and h.max_point
and s.studno = e.studno
and s.studno = vstudno;
RETURN vgrade;
END;
/
-- 조회
select studno_hakjum(9412) from dual;
select name, studno_hakjum(studno) as 학점
from scott.student;[함수 응용]
** 채번 방법
1. 시퀀스
select seq1.nextval from dual;
insert into table1 values(seq1.nextval, …);
2. 테이블 채번 : 테이블 실제 최대값 조회 후 최대값 + 1을 리턴하는 방식
select next_key(스키마명, 테이블명, 컬럼명) from dual;
CREATE OR REPLACE FUNCTION next_key
(vschema varchar2,
vtable varchar2,
vcolumn varchar2
)
RETURN number
IS
vsql varchar2(100);
vmax number;
BEGIN
vsql := 'select max('||vcolumn||') + 1 from '||vschema||'.'||vtable;
execute immediate vsql into vmax;
RETURN vmax;
END;
/
-- 조회
select next_key('scott','emp','empno') from dual;** long -> varchar2 변환
long -> varchar2 변환의 이유 : CTAS 안됨. dblink로 조회도 불가능함. 나중에 현업가서도 아래 코드에서 vsql부분 잘 수정해서 써먹어야됨.
1) long type data 출력
2) 출력된 결과를 직접 varchar2로 변환
varchar2는 원래 4000byte의 사이즈까지만 표현할 수 있지만 PLSQL 블럭에서의 varchar2는 32767byte까지 표현이 가능하다.
-- long type data의 예)
select data_default -- long type
from dba_tab_columns
where table_name = 'EMP'
and column_name = 'HIREDATE';
create table t1234
as
select table_name, column_name, data_default
from dba_tab_columns
where table_name = 'EMP';
-- error, ORA-00997: LONG 데이터 유형은 사용할 수 없습니다-- 함수 생성) 권한 문제때문에 sys계정에서 실행시키기
create or replace function get_column_default
(tname varchar2,
cname varchar2)
return varchar2
is
vsql varchar2(32767);
vreturn varchar2(32767);
begin
vsql := 'select data_default from dba_tab_columns where table_name = '''||tname||''' and column_name = '''||cname||'''';
execute immediate vsql into vreturn;
return vreturn;
end;
/
-- 함수를 사용한 테이블 생성 시도) 권한 문제때문에 sys계정에서 실행시키기
create table t1234
as
select table_name, column_name, get_column_default(table_name, column_name) as data_default
from dba_tab_columns
where table_name = 'EMP';
-- 조회
select * from sys.t1234;[TRIGGER]
어떤 행위 전/후에 자동으로 수행하는 작업에 대해 정의하는 객체.
예) 접속 후 접속기록(유저명, 접속시간, IP, terminal 등) 정보를 특정 테이블에 저장
** 행위 종류에 따라 분류 : timing 정의 시 필요
1) dml 트리거
2) ddl 트리거
3) database 트리거
** 문법
** 문법
CREATE [OR REPLACE] TRIGGER 트리거명
BEFORE|AFTER|INSTEAD -- 특정 동작 전/후/일치 시에 대한 타이밍 정의
INSERT|UPDATE|DELETE [OF event1 OR 컬럼명 ] -- DML 트리거 시
ON {table_name|view_name|SCHEMA|DATABASE} -- 대상 정의
[REFERENCING OLD AS old | NEW AS new] -- 변경 전/후 값에 대한 참조 필요 시
[FOR EACH ROW ] -- 행 수준의 트리거 작성 시 사용
[WHEN ( condition ) ] -- 트리거 수행 조건 전달 시 사용
trigger_body
;log on 트리거
사용자 접속 기록을 남기는 트리거
** 사전 준비
-- step1) 접속 기록을 저장할 테이블 생성
create table log_on_off
(userid varchar2(50),
sessionid number,
ipaddr varchar2(15),
terminal varchar2(50),
host varchar2(50),
module varchar2(50),
stime timestamp,
etime timestamp,
status varchar2(50)
);
-- step2) 세션 정보 확인
select sys_context('USERENV','ip_address'),
sys_context('USERENV','sessionid'),
sys_context('USERENV','terminal'),
sys_context('USERENV','host'),
sys_context('USERENV','module'),
sys_context('USERENV','session_user')
from dual;** 트리거 생성
create or replace trigger t_logon
after logon on database
begin
insert into log_on_off(userid, sessionid, ip_addr, terminal, host, module, stime, status)
values(user,
sys_context('USERENV','sessionid'),
sys_context('USERENV','ip_address'),
sys_context('USERENV','terminal'),
sys_context('USERENV','host'),
sys_context('USERENV','module'),
systimestamp,
'SUCCESS');
commit;
end;
/
-- 조회
select * from log_on_off;log off 트리거
-- log off 트리거 생성)
create or replace trigger t_logoff
before logoff on database
begin
update log_on_off
set etime = systimestamp
where sessionid = sys_context('USERENV','sessionid');
commit;
end;
/
** 참고 : orange의 경우 oracle 접속과정에 OCISessionEnd를 사용하지 않고 직접적으로 OCIserverdetach를 호출하는 식으로 세션 관리를 하기 때문에 log off 트리거가 동작하지 않을 수 있다.[접속 제한 트리거]
목적 : Oracle 접속 시 허가된 유저, 접속 시간 등을 관리할 목적으로 생성.
-- step1. 트리거 생성
create or replace trigger t_logon
after logon on database
begin
if upper(user) = 'HR' then
insert into log_on_off(userid, sessionid, ip_addr, terminal, host, module, stime, status)
values (user,
sys_context('USERENV','sessionid'),
sys_context('USERENV','ip_address'),
sys_context('USERENV','terminal'),
sys_context('USERENV','host'),
sys_context('USERENV','module'),
systimestamp,
'FAILED');
commit;
raise_application_error(-20005, '허가되지 않은 사용자다!!!!!!!!!!!!');
else
insert into log_on_off(userid, sessionid, ip_addr, terminal, host, module, stime, status)
values (user,
sys_context('USERENV','sessionid'),
sys_context('USERENV','ip_address'),
sys_context('USERENV','terminal'),
sys_context('USERENV','host'),
sys_context('USERENV','module'),
systimestamp,
'SUCCESS');
commit;
end if;
end;
/
-- step2. hr계정으로 접속 시도(error 발생)
-- step3. 접속 기록 테이블 조회
select * from log_on_off;
-- step4. trigger 삭제
drop trigger t_logon;
select *
from dba_objects
where object_name = 'T_LOGON';[DML 트리거]
DML 제한(계정, 실행시간), DML 작업 기록을 남기기 위해 주로 사용.
placeholder를 사용하여 값을 전달.
1) :OLD : 이전 값을 나타내는 플레이스홀더
ex) DELETE 시 이전 데이터 저장
: INSERT INTO T_LOG … VALUES(:OLD.EMPNO, :OLD.ENAME, …);
2) :NEW : 새로운 값을 나타내는 플레이스홀더
insert는 :NEW만 존재. delete는 :OLD만 존재.
dml 트리거 내에서는 commit 사용 불가. 최초 수행되는 dml문에 의해 트리거가 작동하기 때문에 하나의 트랜잭션으로 간주, 따라서 최초 수행되는 dml문의 commit과 rollback에 의존.
** 예제)
도서 대여 테이블이 있다고 하자. 도서를 대출할 때 값이 입력되고 반납될 때 반납날짜가 update 되면 자동으로 연체요금이 계산되도록 하는 트리거를 작성하라;
연체요금 : 일당 200원
대여기간 : 3일
– 대출 테이블(대출번호, 고객번호, 도서번호, 대출날짜, 반납날짜)
– 연체 테이블(대출번호, 연체요금)
--1. 테이블 생성
create table rent(
rent_no number,
gogak_no number,
book_no number,
sdate date,
edate date);
create table delay(
rent_no number,
delay_fare number);
--2. rent 테이블에 데이터 입력 시 자동으로 delay 테이블 insert 트리거 작성 (sqlplus system/oracle에서 수행)
create or replace trigger t_rent
after insert on rent
for each row
begin
insert into delay(rent_no) values(:new.rent_no);
end;
/
--대출 발생 insert 시도)
insert into rent(rent_no, gogak_no, book_no, sdate) values(1000, 1, 100000, sysdate);
insert into rent(rent_no, gogak_no, book_no, sdate) values(1001, 2, 200000, sysdate+1);
insert into rent(rent_no, gogak_no, book_no, sdate) values(1003, 3, 500000, sysdate);
commit;
select * from rent;
select * from delay;
--3. rent 테이블에 반납날짜가 수정될 때 자동으로 delay 테이블에 연체요금 update 트리거 작성 (sqlplus system/oracle에서 수행)
create or replace trigger t_delay_fare
after update of edate on rent -- update column 제한
for each row
declare
vfare number;
begin
if (:new.edate - :old.sdate) <= 3 then
vfare := 0;
else
vfare := trunc(((:new.edate - :old.sdate) - 3) * 200);
end if;
update delay
set delay_fare = vfare
where rent_no = :old.rent_no;
end;
/
--반납 발생 insert 시도)
select * from rent;
update rent set edate = sysdate + 1 where rent_no = 1000;
commit;
update rent set edate = sysdate + 7 where rent_no = 1001;
commit;
update rent set edate = sysdate + 20 where rent_no = 1003;
commit;
select * from rent;
select * from delay;[PACKAGE]
PLSQL 블럭(프로시저, 함수)의 묶음.
패키지 선언부, 패키지 body부로 구성
1) 패키지 선언부 : 패키지 내 함수나 프로시저 명, 변수 정의
2) 패키지 body부 : 함수나 프로시저의 plsql 블럭 정의
** 문법
1) 패키지 선언부
create [or replace] package 패키지명
is|as
변수 정의;
프로시저나 함수 정의;
end 패키지명;
/2) 패키지 body부
create [or replace] package body 패키지명
is|as
변수 정의;
프로시저나 함수 plsql 블럭 정의;
end 패키지명;
/입력변수가 없는 여러 프로시저를 갖는 패키지 생성
** 예제1) emp 테이블에서 emp_sum(급여총합) 프로시저, emp_avg(급여평균) 프로시저를 갖는 emp_total 패키지 생성
--step1) 패키지 선언부 생성
create or replace package emp_total
is
procedure emp_sum;
procedure emp_avg;
end emp_total;
/
--step2) 패키지 body부 생성
create or replace package body emp_total
as
procedure emp_sum
is
vcount number;
vsum number;
begin
select count(*), sum(sal) into vcount, vsum
from scott.emp;
dbms_output.put_line('인원수: '||vcount||'명, 급여총합: '||vsum||'입니다.');
end emp_sum;
procedure emp_avg
is
vcount number;
vavg number;
begin
select count(*), round(avg(sal)) into vcount, vavg
from scott.emp;
dbms_output.put_line('인원수: '||vcount||'명, 급여평균: '||vavg||'입니다.');
end emp_avg;
end emp_total;
/
--실행(server output tab에서 실행)
exec emp_total.emp_sum;
exec emp_total.emp_avg;