데이터베이스 쿼리문 작성하기(2)

함수 만들기, 사용하기, 삭제하기

create function sf_password(password varchar(255))
	returns varchar(255)
begin
	return 
		case
			when length(password)>2 then 
				concat(substring(password,1,2),'**')
			else 
				'****'
		end;
end;

select m.member_type , m.user_id , m.name , sf_password(password)
from member as m ;

drop function sf_password;

딜리미터 사용

create procedure sp_select_member()
begin
	select *
	from member;
end


call sp_select_member

drop PROCEDURE sp_select_member 

트리거

변경사항을 저장할 member_detail_history생성

select * 
from member_detail_history


alter table member_detail_history  add new_mobile_no varchar(50) after mobile_no;


create table member_detail_history
(
	id int auto_increment,
	member_type varchar(10),
	user_id varchar(50),
	mobile_no varchar(20),
	update_date datetime,
	primary key(id)
)

update시 발동되는 트리거 생성

create trigger tg_member_no_history
	before update on member_detail 
	for each ROW  
	BEGIN
		insert into member_detail_history
			(member_type, user_id, mobile_no, new_mobile_no,update_date)
			values(
			old.member_type, old.user_id, old.mobile_no,new.mobile_no, now() 
			);
	END;

실제로 값 수정해보기

update member_detail 
set 
mobile_no = '01088888888'
where member_type  = "email"
and user_id = 'test@naver.com'
;

결과