DB - stored Program ; 프로시저 , 사용자 정의함수

1. Stored Program

1] 정의

① 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
② MySQL 안에서 프로그래밍 언어와 같은 기능을 제공하는 프로그램
③ 자주 사용하는 복잡한 쿼리를 하나로 묶어서 이름으로 지정하고 이름을 호출해서 실행되도록 한다.

 

2] Stored Program의 특징

① MySQL의 성능 향상 - 긴 쿼리의 내용을 전송하지 않고 프로시저의 이름 및 매개변수 등만 전송하여 네트워크 부화를 줄일 수 있다.
② 유지관리가 간편 - 직접 SQL문을 작성하지 않고 저장된 이름만 호출 함으로써 일관된 작업을 할 수 있다.
③ 모듈식 프로그래밍이 가능 - 한 번 Stored Program을 생성해 쿼리의 수정, 삭제 등의 관리가 수월해진다.
④ 보안 강화 - 사용자 별로 테이블에 접근 권한을 주지 않고 Stored Program에 접근 권한을 부여해서 보안을 강화

 

3] Stored Program의 종류

   1. Stored Procedure - 프로시저 
      ① 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용한다.
      ② 리턴 값이 없어서 단독으로 문장 구성이 가능하다.
      ③ 실행하고 끝나고 실행하고 끝나서 잘못된 결과가 그대로 반영되기 때문에 예외처리가 필요하다.

   2. Stored Function - 함수
     ① 사용자 정의 함수이다. 내장 함수가 제공하지 않는 사용자가 원하는 함수.
     ② 리턴 값이 있어서 단독으로 문장 구성을 할 수 없다.

   3. Trigger - 트리거
      ① 테이블의 동작이 일어나면 자동실행 (방아쇠)
      ② 테이블의 DML문이 작동되면 자동으로 실행된다.
      ③ DML 작동이 일어났을 때 선작업으로 실행할 것인지 후 작업으로 실행 할 것인지 결정해야 한다.

   4.  Cursor 
      ① 테이블에서 여러 개의 행을 쿼리 한 후에, 쿼리의 결과인 행 집합을 한 행씩 철하는 방식이다.
      ② 일반 프로그래밍의 파일 처리와 비슷한 방법을 제공한다.
      ③ 어떤 지점에서 어떻게 처리된 것에 대한 결과값을 가지고 포인트를 지정하는 것

 

2. 프로시저

1] 특징

① 어떠한 동작을 일괄 처리하기 위한 용도
② 자주 사용되는 일반적인 쿼리를 모듈화 시켜 필요할 때만 호출
③ MySQL 운영에 편리

 

2] 단점 

①애플리케이션의 설치나 배포가 더 복잡해짐  
   ( 각 기능을 담당하는 프로그램 코드가 자바와 MySQL Stored 프로그램으로 분산되어 관리하기 때문에)

 

3] 프로시저 형식 정의

DELIMITER $$

CREATE PROCEDURE 스토어드 프로시저 이름(IN 또는 OUT 파라미터)
BEGIN
	
	SQL 프로그래밍 코딩…

END $$
DELIMITER;


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

DROP PROCEDURE IF EXISTS memberProc;
DELIMITER $$
CREATE PROCEDURE memberProc(IN mName VARCHAR(50))
BEGIN
	SELECT
	   *
	FROM
	   tb_member AS m
	WHERE
	   m.m_name = mName;
END $$
DELIMITER

 

그러나 heidSQL에서는 저 형식이 안 읽혀서 클릭클릭으로 대신한다.

HeidSQL_프로시저 만들기

 

HeidSQL_유형 선택하기

유형에서 프로시저와 함수를 구별하여 선택할 수 있다.

 

HeidSQL_매개변수 설정

매개변수 이름, 데이터 유형, 문맥 선택 가능하다.

문맥은 in 과 out과 inout이 있다.

 

HeidSQL_프로시저 저장

 

HeidSQL_프로시저 호출

 

4] 실습

if조건문, case 제어문, while 반복문, loop 반복문을 사용하게 되면 꼭꼭 end ; 으로 닫아줘야한다.

 

 

3.  사용자 정의 함수

1] 함수의 특징 

① 내장함수가 사용자를 만족하는 모든 함수를 제공하지 않아 필요에 의해 사용자가 직접 함수를 만들어서 사용
② 형태와 사용 용도에 프로시저와 차이가 있음

 

2] 단점

①유지 보수 복잡성 증가
 - 애플리케이션의 설치나 배포가 더 복잡해짐 (각 기능을 담당하는 프로그램 코드가 자바와 MySQL 스토어드 프로그램으로 분산되어 관리하기 때문에)

 

3] 프로시저와 사용자 저의 함수 비교하기

사용자 정의 함수	프로시저
파라미터 in, out을 사용 할 수 없다. 함수의 파라미터는 모두 입력 파라미터로 사용된다.	파라미터 in, out, inout을 지정한다.
리턴값이 있다. 하나의 값을 반환해야 한다.	리턴 값이 없다. 다만, out파라미터를 사용하여 변수에 입력할 수 있다.
select 안에서 호출 된다.	call을 이용해 단독 호출이 가능하다.
집합결과 반환하는 select를  사용할 수 없다. Function 안에 순수하게 변수와 처리되는 알고리즘 밖에 없다.	select를 사용할수 있다.

 

4] 정의

DELIMITER $$

CREATE FUNCTION 스토어드 함수 이름(파라미터)
	RETURNS 반환형식
BEGIN
	
	프로그래밍 코딩…
	RETURN 반환값 ;

END $$
DELIMITER;

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

Heid SQL에서는 또 클릭클릭으로 가능하다.