데이터 처리 - View, 함수/프로시져, 트리거

|  VIEW

- 가상의 읽기 전용 테이블

- 장점 :

  - 독립성 : 테이블 구조가 변경되어도 뷰를 쓰는 응용 프로그램은 변경하지 않음

  - 편리성 : 복잡한 쿼리를 뷰로 생성하여 코드를 간결화

  - 보안성 : 계정 권한 수준에 따라 노출되지 않아야 하는 데이터를 숨김 처리 할 수 있음

-- 기본 구문
CREATE VIEW 뷰명 AS
SELECT *
FROM 테이블명

-- 예시
create view v_member as
select
    m.member_type, m.user_id,m.name,
    md.mobile_no, md.marketing_yn, md.register_date
from member as m
    join member_detail md
        on m.member_type = md.member_type and m.user_id = md.user_id
;

 

|  (사용자 정의)함수와 프로시져

	(사용자 정의)함수	프로시져
언제쓰나?	자주쓰는 기능 저장할때	특정 비즈니스 로직을 저장해서 꺼내 쓸 때
공통점	선언부와 구현부가 있는 함수 형태
차이점	반환값 O	반환값 X

A. 함수

-- 기본 구문
CREATE FUNCTION 함수명(매개변수명 타입)
   RETURNS 반환타입
BEGIN
   RETURN
   구현부
END;

-- 예시
-- A. 생성
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;

-- B. 호출
select 
   sf_password(password) as password_mask
from member;

-- C. 삭제
drop function sf_password;

B. 프로시저

-- 기본 구문
CREATE PROCEDURE 함수명()
BEGIN
	구현부
END
;

-- 예시
-- A. 생성
create procedure sp_select_memeber()
begin
    select *
    from member;

    select *
    from member_detail;
end;

-- B. 호출
call sp_select_memeber();

-- C. 삭제
drop procedure sp_select_memeber;

 

C. delimiter를 사용해야 할까?

- 원칙적으로, 함수나 프로시저를 생성할 때, delimiter를 사용하는 것이 필요하다.

  ** delimiter 곧, SQL 명령어 간의 구분은 기본적으로 세미콜론(';')을 통해 이루어지기에,

     SQL 명령어를 구현부에 중첩시키면 하위 명령어는 실행되지 않는 현상이 발생)

- 다만, 툴을 사용하면 delimiter를 사용하지 않아도 오류 없이 잘 실행된다.

delimiter $$  -- delimiter 변경
create procedure sp_select_memeber()
begin
    select *
    from member;

    select *
    from member_detail;
end;
delimiter ;  -- delimiter default인 ;로 원상복구

 

|  트리거

특정 조건이 만족되면 자동으로 실행 시작

rf. 일반적으로 히스토리를 남기는 데에 사용된다.

rf. 상황에 따라 양날의 검이 될 수 있는 명령문이다. (사용에 주의가 필요)

event	old	new
insert	X	O
update	O	O
delete	O	X

-- RQ. MEMBER 테이블의 핸드폰 번호를 갱신(update)할 때마다 히스토리를 남기고 싶다.

-- A. 변경 사항 담을 히스토리 테이블
create table member_detail_history
(
    id int auto_increment primary key ,
    member_type varchar(10),
    user_id varchar(50),
    mobile_no varchar(50),
    new_mobile_no varchar(50),
    update_date datetime
);

-- B. 트리거 예시
delimiter &&
create trigger tg_member_mobile_no_history
    before update on member_detail -- update 되기 이전에 트리거가 일어난다.
    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;
delimiter ;

 

 

[ 출처 ]

- 부트캠프 강의를 들은 후 정리한 내용입니다.