MySQL 저장 프로시져, 저장 함수, 트리거 예제 모음 1

[저장된 프로그램에 인베디드된 쿼리문]

create procedure example1()    begin      declare l_book_count integer;               select count(*) into l_book_count from books where author like '%HARRISON,GUY%';             select concat('Guy has wriitten (or co-written) ', l_book_count , ' books.');             update books set author = replace(author, 'GUY', 'GUILLERMO') where author like '%HARRISON,GUY%';    end

 

 

[제어와 조건 로직을 가진 저장 프로시져]

create procedure pay_out_balance(account_id_in int)     begin      declare l_balance_remaining numeric(10,2);           payout_loop:loop      set l_balance_remaining=account_balance(account_id_in);        if l_balance_remaining < 1000 then      leave payout_loop;      else      call apply_balance(account_id_in, l_balance_remaining);    end if;      end loop;    end

 

 

[생년월일로 나이를 계산하는 저장 함수]

create function f_age(in_dob datetime) returns int      no SQL     begin        declare l_age int;          if date_format(now(),'00-%m-%d') .= date_format(in_dob,'00-%m-%d') then          set l_age=date_format(now(),'%Y')-date_format(in_dob,'%Y');    else      set l_age=date_format(now(),'%Y')-date_format(in_dob,'%y')-1;         end if;      return(l_age);    end;     활용의 예   mysql> select firstname, surname, date_of_birth, f_age(date_of_virth) as age         -> from employees limit 5;

 

 

 [저장 프로그램에서 에러 처리]

create procedure sp_product_code(in_product_code varchar(2), in_product_name varchar(30))    begin       declare l_dupkey_indicator int default 0;       declare duplicate_key condition for 1062;       delcare continue handler for duplicate_key set l_dupkey_indicator=1;         insert into product_codes(product_code, product_name) values(in_product_code, in_product_name);         if l dupkey_indicator then          update product_codes set product_name=in_product_name where product_code=in_product_code;        end if;      end

 

[얻은 컬럼 값을 유지하기 위한 트리거]

create trigger employees_trg_bu      before update on employees      for each row       begin          if new.salary < 50000 then          set new.contrib_401k=500;           else          set new.contrib_401k=500+(new.salary-50000)*.01;          end if;     end