[SQL] Function, Trigger (펑션, 트리거)

[Function]

 ⊙ 값을 계산하고 결과값을 반환하기 위해서 사용

 ⊙ 저장프로시저와의 차이점

   → 입력 매개변수만 사용할 수 있고 리턴 타입 (Retrun문) 을 반드시 지정해야 함 (필수)

 ⊙ 형식)

create or replace function 함수 이름
  매개변수
return  리턴 자료형
is
 변수선언
begin
 문장
end;
/

 

 

문제1) sungjuk테이블에서 국어점수의 평균을 구하시오

select avg(kor) from sungjuk;                --68.33333333333333333333333333        --행추가    insert into sungjuk(sno, uname, kor, eng, mat, addr)    values (sungjuk_seq.nextval, '이순신', 40, 60, 75, 'Busan');    --행추가 후, 66.153846153846       select * from sungjuk;    --행수정    update sungjuk set kor=95 where sno=23;     --평균 : 69.6153861538       --행 삭제    delete from sungjuk where sno=23;           --평균 : 67.5

 

sno=24의 국어점수 (40점)를 50점 추가하는 함수 생성   → sqlplus에서 제대로 인상된 국어점수가 들어가 있는 지 확인 할 것

create or replace function fn_update_kor (v_sno number)  return number  is    v_kor number;  begin    update sungjuk set kor=kor+50 where sno=v_sno;    select kor into v_kor from sungjuk where sno=v_sno;    return v_kor;                                                       --인상된 국어점수 90점  end;  /

 

 

⊙ sqlplus에서 확인

  - Function fn_update_kor 를 컴파일 한 후 cmd를 이용해서 국어점수가 +50 이 된 것을 확인

 

<sqlplus에서 적용된 명령문>
SQL> var korea number;
SQL> execute :korea := fn_update_kor(24);     --sno가 24인 것에 실행

PL/SQL procedure successfully completed.

SQL> print korea;

 

 

 

[Trigger]

⊙ 방아쇠, 데이터베이스에서의 연쇄적인 동작을 정의함

⊙ insert, update, delete문이 실행될 때 묵시적으로 수행되는 프로시저

⊙ 테이블과는 별도로 데이터베이스에 저장된다

⊙ 형식)

create or replace trigger 트리거이름
before 또는 after 
  트리거이벤트 on 테이블명               --트리거이벤트(insert, update, delete)를 수행하고
declare
  변수선언;                                     -- 연쇄동작할 것을 넣어주기
begin
  실행할 명령어;
end;
/

 

 

 

<Trigger 컴파일링>

create or replace trigger kor_trigger   after       insert or update or delete on sungjuk   declare     avg_kor number;   begin     select avg(kor) into avg_kor from sungjuk;     dbms_output.put_line('국어평균 : ' || avg_kor);  end;

<콘솔창 출력>  dbms_output.put_line를 출력하기 위해서 사용함

set serveroutput on;

→ Trigger 컴파일링을 하고 행 추가, 행 수정, 행 삭제를 수행하면 결과값도 같이 보여짐

 

 

 

문제1) sungjuk테이블에서 국어점수의 평균을 구하시오

- 행 추가

insert into sungjuk(sno, uname, kor, eng, mat, addr)   values (sungjuk_seq.nextval, '이순신', 40, 60, 75, 'Busan');

 

 

- 행 수정

update sungjuk set kor=80 where sno=25;

 

 

- 행 삭제

delete from sungjuk where sno=25;