[Oracle] 패키지, 트리거

package

: 함수나 프로시저를 묶어서 사용

 

선언부(명세부) : 프로토타입( 원형만 선언 )

몸체부 : 내용을 기술 ( 정의 )

 

ed pack1

create or replace package pack1 is
procedure test2
	(v_stu_no in student.stu_no%type,
	v_stu_grade in student.stu_grade%type);
function test6
	(v_enr_grade in number)
    return	char ; 
end ;
/
show errors;

create or replace package body pack1 is

procedure test2
	(v_stu_no in student.stu_no%type,
	v_stu_grade in student.stu_grade%type)
is
begin
 update student
 	set stu_grade = v_stu_grade
    where stu_no = v_stu_no ; 
end test2; 

function test6
	(v_enr_grade in number)
    return char
is
	enr_score char ;
begin
	if	 	v_enr_grade >=90 then enr_score := 'A';
    elsif	v_enr_grade >=80 then enr_score := 'B';
    elsif	v_enr_grade >=70 then enr_score := 'C';
    elsif	v_enr_grade >=60 then enr_score := 'D';
    else enr_score := 'F';
    end if;
    return (enr_score);
end test6;
end;
/

 

one time only procedure 

=> 패키지 안에 전역변수를 초기화하기 위해서,

     패키지 실행 시, 단 1회만 실행

 

public String a (	) {	<- begin

} <- end;

create or replace package pack2 is 
	g_stu_dept	varchar2(20);
    procedure test12
    (v_stu_no in student.stu_no%type);
    
    procedure test12
    (v_stu_name	student.stu_name%type);
end;

/
show errors

create or replace package body pack2 is 

procedure test12
	(v_stu_no in student.stu_no%type)
    is 
    v_stu_name student.stu_name%type;
    begin
    	select stu_name
        	into v_stu_name
            from student
            where stu_no = v_stu_no and stu_dept = g_stu_dept;
            dbms_output.put_line(v_stu_name);
        exception
        	when no_data_found then
            	dbms_output.put_line('컴퓨터정보과에 학생이 없습니다.');
end test12;

procedure test12
	(v_stu_name in student.stu_name%type)
    is v_stu_no student.stu_no%type;
    begin
    	select stu_no
        	into v_stu_no
            from student
            where stu_name = v_stu_name and stu_dept = g_stu_dept;
            dbms_output.put_line(v_stu_name);
        exception
        	when no_data_found then
            	dbms_output.put_line('컴퓨터정보과에 학생이 없습니다.');
end test12;
	begin 
    	g_stu_dept := '컴퓨터정보';
end; 

/
show errors
    

 

trigger

insert에서는     old : null,            new : 입력 값 

update에서는   old : 변경 전의 값, new : 변경 후의 값

delete에서는    old : 삭제 전의 값, new : new 값

create table tmp_tbl1
(userid	 varchar2(10),
workdate date,
bigo	 char(1));

 

ed tri1

create or replace trigger tri1
	after update
    on student
    begin
    	insert into tmp_tbl1
        	values(user, sysdate, 'U');
end tri1;

@tri
/

 

update student
set stu_weight = stu_weight*0.9;

select * 
from tmp_tbl1;

rollback;

 

ed tri2

create or replace trigger tri2
	after update
    on student
    for each row
    begin
    	insert into tmp_tbl1
        	values(user, sysdate, 'U');
end tri2;

@ tri2
/

여기서 그대로 update하게 되면, 

이런 오류가 생긴다. 

tri1 trigger를 drop해준 다음, 다시 확인해보자. 

 

create table tmp_tbl2
(u_id varchar2(10),
wdate date,
n_sub_no char(3),
n_stu_no varchar2(9),
n_enr_grade number(3),
o_sub_no char(3),
o_stu_no varchar2(9),
o_enr_grade number(3),
bigo char(1));