Stored Program / Stored Procedure

Stored Program 저장 프로그램

• MySQL에서 제공하는 프로그래밍 언어 기능을 통틀어 일컫는 말
• 쿼리를 하나로 묶어 이름을 붙이고 호출하는 원리로 동작

 

변수 선언

DECLARE 변수 이름 데이터 형식;

 

값 대입

SET 변수 이름 = 값;

 

 

Stored Procedure 저장 프로시저

• MySQL에서 제공하는 프로그래밍 기능
• 쿼리의 집합으로서 동작을 일괄 처리하는데 사용
• 모듈화해서 필요할 때마다 호출

 

DELIMITER $$
CREATE PROCEDURE 스토어드 프로시저 이름
BEGIN
SQL 프로그래밍 코딩 부분
END $$

▶ DELIMITER$$ : 스토어드 프로시저의 코딩할 부분을 묶는 역할

  → 종료 문자를 ;에서 $$으로 변경

 

▶CREATE PROCEDURE : 새로운 프로시저 등록

 

DELIMITER ;

▶ 종료 문자를 다시 $$에서 ;으로 변경

 

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

▶ 생성한 스토어드 프로시저를 호출(실행)하는 방법

 

 

 

작성된 저장 프로시저 확인

SHOW CREATE PROCEDURE 프로시저명;

 

 

입력 매개변수 지정

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

 

 

출력 매개변수 지정

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

 

 

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

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

 

 

---

 

 

Ex 1.

EMP와 DEPT 테이블에서 정보를 출력하는 userPROC() 스토어드 프로시저를 생성해보자.

 

DELIMITER $$
CREATE PROCEDURE userPROC()
BEGIN
  SELECT * FROM EMP;
  SELECT * FROM DEPT;
END $$
DELIMITER ;

▶ userPROC() 스토어드 프로시저 생성

 

 

CALL userPROC();

▶ userPROC() 프로시저 호출

 

 

---

 

 

Ex 2.

 

STD 테이블에 데이터를 삽입할 수 있는 ADD_STD 스토어드 프로시저를 생성해보자.

 

DELIMITER $$
CREATE PROCEDURE ADD_STD
  (IN P_SNO INT,
  IN P_SNAME VARCHAR(14),
  IN P_AGE INT)
BEGIN
  INSERT INTO STD VALUES (P_SNO, P_SNAME, P_AGE);
END $$
DELIMITER ;

▶ IN : 입력 매개변수 지정(P_SNO, P_SNAME, P_AGE를 입력받음)

▶ INSERT INTO STD VALUES (P_SNO, P_SNAME, P_AGE) : 사용자에게 입력받은 데이터들을 STD 테이블에 삽입함

 

 

CALL ADD_STD(10, 'PARK', 21);
CALL ADD_STD(20, 'JANG', 18);
CALL ADD_STD(25, 'CHOI', 20);

▶ SNAME이 각각 PARK, JANG, CHOI인 데이터 삽입

 

 

▶ STD 테이블의 정보를 출력해보면 모두 삽입된 것을 확인할 수 있음

 

 

 

---

 

 

Ex 3.

 

STD 테이블에서 SNO를 사용자로부터 입력받아
해당 값의 데이터를 출력하는 스토어드 프로시저를 생성해보자.

 

DELIMITER $$
CREATE PROCEDURE PRINT_STD (IN P_SNO INT)
BEGIN
  SELECT * FROM STD
  WHERE SNO = P_SNO;
END $$
DELIMITER ;

 

▶ STD 테이블의 정보를 출력

 

▶ SNO 값이 20인 값만 호출됨

 

 

---

 

 

Ex 4.

 

EMP 테이블에 존재하는 7876번 사원의 급여가 1300 미만이면 LOW, 1300 이상 2900 이상이면 MIDDLE, 2900 이상이면 HIGH로 표시하는 SQL문을 생성해보자.

사용자로부터 EMPNO을 입력받고 급여 정도의 컬럼명은 GRADE로 출력

 

▶ 스토어드 프로시저 이름은 RANK_SAL로 지정

▶ 입력받는 매개변수의 이름은 P_EMPNO로 데이터 형식은 INT(10)으로 지정

 

 

▶ 사원번호 7876인 사원 호출

 

 

▶ 제대로 출력되었는지 확인하기 위해 EMP 테이블의 정보를 출력