저장 프로시저 [Stored Procedure]

• 리턴값이 있을수도 없을수도 있다. (IN 또는 OUT)
• 리턴값이 여러개 있을 수 있다. (OUT 여러개)
• 서버에서 실행되기 때문에 속도가 빠르다.
• CALL 프로시저명() 또는 EXECUTE 프로시저명()

프로시저 생성

• DEFINER: 접근 권한
• PROCEDURE [프로시저이름]
• 매개변수 + 타입
• SET 변수설정
  • 기호가 없으면 프로시저 실행이 끝나면 초기화
  • @ 있으면 프로시저가 끝나도 계속 유지
• CONCAT(): 문자열 합치기
• PREPARE: 실행준비 (한번만 해주면 계속 EXECUTE 해줄 수 있음)
• EXECUTE: PREPARE 된 것을 실행

프로시저의 기본 형태 1

CREATE DEFINER=`DB아이디`@`%` PROCEDURE `프로시저명`
BEGIN

 SELECT * 
 FROM 테이블명

END

-- DB에 접속하는 ID명을 적어준다.
-- 여기서 ID는 권한이 있는  ID를 적어야 한다.

프로시저의 기본 형태 2

CREATE OR REPLACE PROCEDURE "프로시저명" (
	PARAMETER IN OUT DATATYPE
)
IS
-- 변수 선언부
BEGIN
-- 처리내용
EXCEPTION 
-- 예외처리부분
END;

/*
CREATE OR REPLACE PROCEDURE : 프로시저를 생성 또는 이미 있으면 기존 프로시저를 대체 변경
PARAMETER : 프로시저에게 전달할 파라미터 이름과 데이터 타입을 명시한다.
IN : 변수값을 입력받을때 사용
OUT : 프로시저 처리 후 리턴할 변수명
INOUT : 파라미터로 변수값을 받고 프로시저 처리 후 리턴할 변수명 
*/

프로시저 인자값 및 파라미터 받을 때

CREATE DEFINER=`DB아이디`@`%` PROCEDURE `프로시저명`(
   IN 변수명 자료형   // 들어오는 변수
   OUT 변수명 자료형  // 반환 변수
)
BEGIN
    SELECT *
    FROM 테이블명
    WHERE 컬럼명 = 변수명
END

-- 받을 변수명과 자료형(INT, VARCHAR(40))등 을 입력해준다. 받을때는 IN을 사용

프로시저 변수 할당

CREATE DEFINER=`DB아이디`@`%` PROCEDURE `프로시저명`(
    IN 변수명 자료형
)
BEGIN
    SET @v_code = '123'; // 이 부분에서 서브쿼리 사용 가능
    SELECT *
    FROM 테이블명
    WHERE 컬럼 = 변수명 AND
    	  컬럼 = @v_code
END

-- 변수를 할당할때 바로 값을 적어도 되지만 서브쿼리를 사용하여
-- @v_code = (SELECT 컬럼명 FROM 테이블  WHERE 조건...)
-- 등으로 할당 할 수 있다.

프로시저 조건문 사용하기 IF, ELSEIF, OR, AND..

CREATE DEFINER = 'DB아이디'@'%' PROCEDURE '프로시저명'(
		IN 변수명 자료형
)
BEGIN
		SET @v_code = '123';
		IF @v_code != '' THEN
			SELECT *
			FROM 테이블명
			WHERE 컬럼 = 변수명 AND
						컬럼 = @v_code
		END IF;
END

/*
[IF 절]
IF 조건문 OR 조건문 THEN
실행문구
ELSE IF 조건문 THEN
실행문구
END IF;
등의 형태로 쓸 수 있으며
NULL 체크는 IS NOT NULL / IS NULL 을 쓸 수 있다.
같지 않음은 != 와 <> 둘다 가능
*/

-- 프로시저 생성이 안될땐, 프로시저의 상단에 DELIMITER;; 혹은 DELIMITER $$ 를 붙여주면 된다.
-- 뭔지 찾아봐야함..

프로시저 생성 예제 1

CREATE DEFINER = 'root'@'%' PROCEDURE test_prc(
			id int(10)
)
BEGIN
SET @t1 = CONACAT('SELECT * FROM test_', id);
PREPARE s FROM @t1;
EXECUTE s;
END

-- 이런 식의, 프로시저는 테이블이름을 배열 변수에 담아서 LOOPING 돌려 한번에 처리 가능 

프로시저 생성 예제 2

//사번을 입력받아 급여를 인상하는 update_sal 프로시저
CREATE OR REPLACE PROCEDURE update_sal
     ( v_empno IN NUMBER )
IS
BEGIN
       UPDATE emp
       SET sal = sal * 1.1
       WHERE empno = v_empno;
END update_sal;