스토어드와 인덱스, 커서, 트리거

1. 스토어드 프로시저(Stored Procedure, 저장 프로시저)

쿼리문의 집합으로 어떤 동작을 일괄 처리하기 위한 용도로 사용

 

쿼리 모듈화

- 필요할 때마다 호출만 하면 훨씬 편하게 SQL 사용 가능

- CALL 스토어드 프로시저 이름( ) 을 통해 사용함

 

기본 형식

DELIMITER $$

CREATE PROCEDURE 스토어드 프로시저이름( IN 또는 OUT 파라미터 )

BEGIN

    SQL 프로그래밍 코딩

END $$

DELIMITER $$;

CALL 스토어드 프로시저 이름( );

 

매개변수가 1개인 프로시저 생성

 

 

매개변수가 2개인 프로시저 생성

 

출력 매개변수 사용하는 프로시저 생성

(원래 값이 1이 나와야 하나, 위의 프로시저를 두번 실행시켜 값이 2가 나옴)

 

스토어드 프로시저의 수정과 삭제

- 수정: ALTER PROCEDURE

- 삭제: DROP PROCEDURE

 

매개변수의 사용

- 입력 매개 변수를 지정하는 형식

IN 입력_매개변수_이름 데이터_ 형식

 

- 입력 매개 변수가 있는 스토어드 프로시저 실행 방법

CALL 프로시저_이름(전달 값)

 

- 출력 매개 변수 지정 방법

OUT 출력_매개변수_이름 데이터_형식

 

*출력 매개 변수에 값 대입하기 위해 주로 SELECT...INTO문 사용

*전역 변수에는 @가 들어가게 됨

 

- 출력 매개 변수가 있는 스토어드 프로시저 실행 방법

CALL 프로시저_이름(@변수명);

SELECT @변수명;

 

스토어드 프로시저 내의 오류 처리

DECLARE EXIT HANDLER FOR 1264

 

스토어드 프로시저 특징

1. MySQL 성능 향상

- 긴 쿼리가 아니라 짧은 프로시저 내용만 클라이언트에서 서버로 전송

    => 네트워크부하 줄일 수 있음

2. 유지관리가 간편

- 응용 프로그램에서 프로시저만 저장

3. 모듈식 프로그래밍 가능

- 언제든지 실행 가능

- 스토어드 프로시저로 저장해 놓은 쿼리의 수정, 삭제 등의 관리 수월

4. 보안 강화

- 사용자 별로 테이블 접근 권한 주지 않고 스토어드 프로시저에만 접근 권한을 주어 보안 강화

    => 뷰 또한 스토어드 프로시저와 같이 보안 강화 가능

 

2. 스토어드 함수(Stored Fuction)

기본 형식

DELIMITER $$

CREATE  FUCTION 스토어드 함수이름( 파라미터 )

RETURNS 반환형식

BEGIN

    SQL 프로그래밍 코딩

    RETURN 반환값;

END $$

DELIMITER $$;

SELECT 스토어드_함수이름( );

 

스토어드 함수 생성 및 호출

 

 

각각의 전역 변수를 선언 후 반환값 출력

 

 

함수로 테이블 조회

 

 

스토어드 함수의 이름 및 내용 확인

 

스토어드 함수 삭제

스토어드 함수 특징 

파라미터에 IN, OUT 등을 사용할 수 없음

RETURNS문으로 반환할 값의 데이터 형식 지정

본문 안에 안에서는 RETURN문으로 하나의 값 반환

    => 단답의 기능을 수행하는 것들은 함수로 생성함

SELECT 문장 안에서 호출

함수 안에선즌 집합 결과 반환하는 SELECT 사용 불가

함수 사용 위해서는 스토어드 함수 생성 권한 부여해야 함

(SET GLOBAL log_bin_trust_function_creators = 1)

 

트리거(Trigger)

테이블에 무슨 일이 일어나면 자동ㅇㅡ로 실행

제약 조건과 더불어 데이터 무결성을 위해 사용

테이블에 DML문 이벤트가 발생할 때 작동

테이블에 부착되는 프로그램 코드

직접 실행 불가(테이블에 이벤트가 일어나야 자동 실행)

IN,OUT 매개변수 사용 불가

MySQL은 VIEW에 트리거 부착 불가

 

기본 문법

CREATE
[DEFINER = user]
    TRIGGER 트리거_이름
    트리거_적용시점 트리거_이벤트
    ON 테이블 이름 FOR EACH ROW
    [트리거 수행순서 지정]

 

트리거_적용시점: BEFORE | AFTER

트리거_이벤트: INSERT | UPDATE | DELETE

트리거 수행순서 지정: FOLLOWS | PRECEDES   다른_트리거_이름

 

트리거 생성

 

트리거 생성

트리거 수행 결과

 

커서(Cursor)

스토어드 프로시저 내부에 사용

일반 프로그래밍 언어의 파일 처리와 방법이 비스샇ㅁ

    => 행의 집합을 다루기 편리한 기능 제공

테이블에서 여러 개의 행을 쿼리한 후, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식

커서 생성 및 호출