함수, 트리거

   함수
      - 사용자가 만드는 함수
        - 복잡한 프로그램도 가능하다
        - 무조건 in이다. 그래서 in을 선언할 필요가 없다.
        - return으로 반환
        - select문안에서 호출하여 활용한다.
        - 함수안에서는 지정프로시저, 재귀함수를 사용할 수 없다.
        
        create function 함수명(매개변수 데이터타입)
         return 데이터타입
            begin
            // SQL문
         end$$
            
      drop function 함수명

 

show global variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators = 'on';

delimiter $$
create function 강의실배정1(classNum char(4))
   returns varchar(10)
    begin
      declare classLevel varchar(10);   -- 지역변수
        declare classLimit int;         -- 지역변수
        
        select 인원제한 into classLimit from 강의 where 강의번호 = classNum;
        
        if classLimit > 80 then set classLevel = '대강의실'; 
        elseif classLimit <= 80 and classLimit >= 40 then set classLevel = '중강의실';
        else 
         set classLevel = '소강의실';
      end if;
        return classLevel;
    end$$
delimiter ;
select * from 강의;
select 강의번호, 강의이름, 인원제한, concat(강의실, ' ', 강의실배정1(강의번호)) as 강의실배정 from 강의;

drop function 강의실배정1;

---

트리거(trigger)
      - 테이블에서 데이터가 변경될때 자동으로 동작되느 구현해 놓은 SQL문을 트리거라 한다.
        - insert, update, delete, before/after
        - 자동호출되는 방식으로 실행
        - 최대 6개까지 설정할수있다.
        >> 데이터의 무결성을 유지하기 위한데 활용
        
        create trigger 이름 발생시점 이벤트 종류
         on 발생테이블
            for each row
            begin
            // SQL문 - old(일어나기전 데이터), new(일어난후 데이터) 가상변수활용 구현
                // insert new, update old, new, delete old
            end

 

# 학생 테이블에서 학년을 update 하고자 한다. 학년의 범위가 벗어날 경우 4보다 크다면 4학년, 1학년보다 작다면 1학년이다.
select * from 학생;
delimiter $$
create trigger before_학생_update before update
   on 학생
    for each row
    begin
      if new.학년 < 1 then
         set new.학년 = 1;
      elseif new.학년 > 4 then
         set new.학년 = 4;
      end if;
    end$$
delimiter ;

select * from 학생;
update 학생 set 학년 = 5 where 학생번호 = '201258047';

# 학생번호로 학생을 삭제하려고 한다. 그런데 수강신청 테이블의 해당 수강신청이 같이 삭제되도록 처리하시오.

delimiter $$
create trigger before_학생_delete before delete
   on 학생
    for each row   
    begin
      delete from 수강신청 where 수강신청.학생번호 = old.학생번호;
    end $$
delimiter ;
drop trigger before_학생_delete;
select * from 학생;
select * from 수강신청;
delete from 학생 where 학생번호='201426005';

# 학생을 등록하면 학생b 테이블에 백업되도록 트리거를 작성하시오.
# 실행후에 학생b테이블에 등록되었는지 확인해보시오.

#1.백업용 테이블 생성
create table 학생b(
   학생번호 char(9) primary key,
    소속학과 varchar(30) not null,
    학생이름 varchar(20) not null,
    학년 int(11),
    성별 char(4),
    나이 int(11),
    전화번호 varchar(13) not null,
    주소 varchar(50)
);


delimiter $$
create trigger after_학생_insert after insert
   on 학생
    for each row
    begin
      insert into 학생b values(new.학생번호, new.소속학과, new.학생이름, new.학년, new.성별, new.나이, new.전화번호, new주소);
   end$$
delimiter ;

select * from 학생;
select * from 학생b;

insert into 학생 values('201258060', '전자공학과', '피글렛', 3, '남', 30, '010-1111-2222', '한양시 종로구');
insert into 학생 values('201258061', '인공지능학과', '루', 3, '여', 35, '010-2222-3333', '한양시 서대문구');

---

 트랜잭션
   . 논리적인 작업단위
    . 동시성
    . commit : 현재 트랜잭션에서 변경된 내용을 영구히 시스템에 반영하는것
      -insert, update, delete는 자동으로 자동 commit발생
        - mySQL은 자동커밋

 

create database bank;
use bank;
create table 예금통장(
   예금주아이디  varchar(45) not null,
    예금주이름   varchar(45) not null,
    잔액 bigint(20) not null default 0,
    primary key(예금주아이디)
);
select * from 예금통장;

delimiter $$
create trigger before_예금통장_insert before insert
   on 예금통장
    for each row
    begin
      declare message_text varchar(45);
        if new.잔액 < 0 then
         signal sqlstate '45000' set message_text = '잔액이 부족합니다';
      end if;
   end$$
delimiter ;

delimiter $$
create trigger before_예금통장_update before update
   on 예금통장
    for each row
    begin
      declare message_text varchar(45);
        if new.잔액 < 0 then
         signal sqlstate '45000' set message_text = '잔액이 부족합니다';
      end if;
   end$$
delimiter ;

select * from 예금통장;
insert into 예금통장 values('000001', '푸우', 4000);
insert into 예금통장 values('000002', '이요르', 3000);

# 1000원을 푸우 -> 이요르에게 이체
update 예금통장 set 잔액 = 잔액 - 1000 where '000001';
update 예금통장 set 잔액 = 잔액 + 1000 where '000002';

select * from 예금통장;
delete from 예금통장;

set autocommit = 0;
start transaction;
   savepoint 여기;
    update 예금통장 set 잔액 = 잔액 - 5000 where 예금주아이디 = '000001';
    update 예금통장 set 잔액 = 잔액 + 5000 where 예금주아이디 = '000002';
rollback to 여기;
commit;
select * from bank.예금통장;

delimiter $$
create procedure 송금(in 이체금액 int)
   begin
      declare exit handler for sqlexception rollback;   -- exit handler : 특정이벤트가 발생시 특정작업을 수행
        start transaction;
         update 예금통장 set 잔액 = 잔액 - 이체금액 where 예금주아이디 = '000001';
            
   end$$
delimiter ;

call 송금(5000);
select * from 예금통장;
call 송금(1000);
select * from 예금통장;

/*
   1. 강의 테이블의 평균 인원제한을 출력하는 저장프로시저를 만들어서 실행해보시오.
    
    2. 학생 테이블에서 입력받은 학년 미만인 학생들을 검색하는 프로시저를 만들어 보시오.
    
    3. 학생 테이블에서 학생이 삭제되면 수강신청 테이블에서 해당 학생의 레코드가 삭제되는 트리거를 생성하시오.
    
    4. 위의 송금 프로시저에서.... 
         000002번의 계좌에 이체후 잔액이 5000이상이 될 경우
            000001번의 이체처리는 실행되고
            000002번의 입금처리는 되지 않도록 트랜젝션을 구현해 보시오.   
            -- savepoint, rollback to를  추가하여 작성
            
            update 예금통장 set 잔액 = 잔액 - 이체금액 where 예금주 아이디 = '000001';
            savepoint h;
            update 예금통장 set 잔액 = 잔액 + 이체금액 where 예금주 아이디 = '000002';
            select 잔액 into b from 예금통장 where 예금주아이디 = '000002';
            if b > 5000 then
            rollback to h;
         end if;
            
            call 프로시저(3000);
            
*/