1. 프로시저(Procedure)
1) 프로시저의 특징
- 어떠한 동작을 일괄 처리하기 위한 용도
- 자주 사용되는 일반적인 쿼리를 모듈화 시켜 필요할 때만 호출
- MySQL 운영에 편리
2) 프로시저의 단점
- 유지 보수 복잡성 증가
- 애플리케이션의 설치나 배포가 더 복잡해짐 (각 기능을 담당하는 프로그램 코드가
자바와 MySQL Stored 프로그램으로 분산되어 관리하기 때문에)
3) 프로시저 생성 및 실행
1. 생성할 db를 선택하고 우클릭하여 저장루틴 새로생성
2. 프로시저명과 추가할 매개변수, 데이터 유형, 문맥 등을 설정하기
3. 루틴 본문에 쿼리구문 작성
>>> 프로시저 루틴 본문의 형태는 'BEGIN 쿼리구문 END' 형태로 작성된다.
4. CALL 프로시저명(매개변수 값); 형태로 프로시저 호출
*** 프로시저 실습 ***
1. 제어문 IF 실습
▼ 프로시저 생성
▼ 프로시저 루틴 본문 작성
BEGIN
/* 변수를 선언 (DECLARE 변수명 데이터타입(길이)) */
/* private int memberLevel과 동일 */
DECLARE memberLevel INT;
/* memberLevel = 0 과 동일 */
SET memberLevel := 0;
/* int memberLevel = 0 과 동일(초기선언부터 초기값 설정) */
/* > DECLARE memberLevel INT DEFAULT 0; */
SELECT
m.m_level INTO memberLevel
FROM
tb_member AS m
WHERE
m.m_id = memberId;
/* PL/SQL IF 조건문 */
IF (memberLevel = 1) THEN
SELECT '관리자' AS '권한';
ELSEIF (memberLevel = 2) THEN
SELECT '판매자' AS '권한';
ELSEIF (memberLevel = 3) THEN
SELECT '구매자' AS '권한';
ELSE
SELECT '일반회원' AS '권한';
END IF;
END▼ 호출 후 결과 확인
2. 제어문 CASE 실습
▼ 프로시저 생성
▼ 프로시저 루틴 본문 작성
BEGIN
/* 변수를 선언 (DECLARE 변수명 데이터타입(길이)) */
/* private int memberLevel과 동일 */
/* > DECLARE memberLevel INT; */
/* memberLevel = 0 과 동일 */
/* SET memberLevel := 0; */
/* int memberLevel = 0 과 동일(변수 선언과 동시에 초기화) */
DECLARE memberLevel INT DEFAULT 0;
SELECT
m.m_level INTO memberLevel
FROM
tb_member AS m
WHERE
m.m_id = memberId;
/* PL/SQL CASE 조건문 */
CASE
WHEN (memberLevel = 1) THEN
SELECT '관리자' AS '권한';
WHEN (memberLevel = 2) THEN
SELECT '판매자' AS '권한';
WHEN (memberLevel = 3) THEN
SELECT '구매자' AS '권한';
ELSE
SELECT '일반회원' AS '권한';
END CASE;
END
▼ 호출 후 결과 확인
3. 반복문 WHILE 실습
▼ 프로시저 생성
▼ 프로시저 루틴 본문 작성
BEGIN
/* 변수 선언 초기화 */
DECLARE totalSum INT DEFAULT 0;
/* 증가하는 변수 선언 */
DECLARE i INT DEFAULT 0;
/* PL/SQL WHILE 구문 */
WHILE (i<intValue) DO
SET i := i + 1;
SET totalSum := totalSum + i;
END WHILE;
SELECT totalSum AS '총합계';
END
▼ 호출 후 결과 확인
3-1. IF조건문을 포함한 반복문 WHILE 실습(ITERATE)
▼ 프로시저 루틴 본문 작성
BEGIN
DECLARE totalSum INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
while_sum : WHILE (i < intValue) DO
SET i:= i+1;
/* Java for문의 continue와 동일 */
IF(i=2) THEN
ITERATE while_sum;
END IF;
SET totalSum := totalSum+i;
END WHILE;
SELECT totalSum AS '2를 제외한 총합';
END
▼ 호출 후 결과 확인
4. 반복문 LOOP 실습
▼ 프로시저 생성
▼ 프로시저 루틴 본문 작성
BEGIN
/* 변수 선언 및 초기화 */
DECLARE totalSum INT DEFAULT 0;
/* 반복문 사용할 변수 증가 값 */
DECLARE i INT DEFAULT 0;
loop_sum : LOOP
/* 빠져나갈 조건식 설정(미설정시 무한반복 발생) => break문과 같다 */
IF(i = intValue) THEN
LEAVE loop_sum;
END IF;
SET i:= i + 1;
SET totalSum := totalSum + i;
END LOOP;
SELECT totalSum AS '총합계';
END
▼ 호출 후 결과 확인
4-1. IF 조건문을 포함한 반복문 LOOP 실습
▼ 프로시저 루틴 본문 작성
BEGIN
/* 변수 선언 */
DECLARE totalSum INT;
/* 반복문 증가 변수 선언과 동시에 초기화 */
DECLARE i INT DEFAULT 0;
/* 변수 초기화 및 할당 */
SET totalSum := 0;
loop_sum : LOOP
IF(i = intValue) THEN
LEAVE loop_sum;
END IF;
SET i := i + 1;
IF (i <> 2) THEN
SET totalSum := totalSum + i;
END IF;
END LOOP;
SELECT totalSum AS '2를 제외한 총 합계';
END▼ 호출 후 결과 확인
5. 예외처리(Overflow)
▼ 해당 예외처리 구문만 익혀두자
***유의사항***
>>> IF, CASE 등 구문 사용시 END IF, END CASE 를 꼭 기입하고 변수 선언에 유의하며 작성할 것
*** 프로시저 실습문제풀이 ***
문제1. 사용자에게 숫자를 입력 받아 구구단의 결과를 tb_gugu에 삽입하는 프로시저를 정의하고 호출하시오
BEGIN
DECLARE resultgugu TEXT;
DECLARE i INT;
DECLARE j INT;
IF (intValue < 2) THEN
SELECT '2이상의 값을 입력해주세요';
ELSE
/* 테이블이 존재하면 DROP 하고 존재하지 않으면 CREATE 한다. */
DROP TABLE IF EXISTS tb_gugu;
CREATE TABLE IF NOT EXISTS tb_gugu(
result TEXT COMMENT '구구단 결과'
);
/* 증가하는 변수i의 초기값 설정후 WHILE 반복문 실행 */
SET i := 2;
WHILE ( i <= intValue ) DO
/* WHILE문이 한번 반복할 때 마다 변수 resultgugu와 j값을 초기화 */
SET resultgugu := '';
SET j := 1;
/* 조건에 부합할 때까지 중첩 WHILE 반복문 실행 */
WHILE ( j < 10) DO
/* 증가하는 변수 i와 j의 계산 결과를 문자열로 변환하여 resultgugu변수에 대입 */
IF ( (i*j) < 10 ) THEN
SET resultgugu := CONCAT(resultgugu, ' ', i, 'X', j, '=0', (i*j));
ELSE
SET resultgugu := CONCAT(resultgugu, ' ', i, 'X', j, '=', (i*j));
END IF;
/* 증가하는 변수 j의 증가값 설정 */
SET j := j + 1;
END WHILE;
/* INSERT 쿼리 실행 */
INSERT INTO tb_gugu (result) VALUES (resultgugu);
/* 증가하는 변수 i의 증가값 설정(초기값을 2로설정하였기 때문에 반복문이 끝날때 증가) */
SET i := i + 1;
END WHILE;
/* SELECT 쿼리 실행하여 tb_gugu에 저장된 값 출력 */
SELECT * FROM tb_gugu;
END IF;
END
문제2. 사용자에게 정수를 입력받아 해당 정수의 약수를 구하고 약수의 합계를 출력하는 프로시저를 작성해라.
BEGIN
DECLARE divisors TEXT DEFAULT '';
DECLARE totalDivisor INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
WHILE ( i <= intValue) DO
IF ((intValue % i) = 0) THEN
IF(i=1) THEN
SET divisors := '1';
ELSE
SET divisors := CONCAT(divisors, ', ', i);
END IF;
SET totalDivisor := totalDivisor + i;
END IF;
SET i := i + 1;
END WHILE;
SELECT divisors AS '약수', totalDivisor AS '약수들의 합';
END
'DBMS' 카테고리의 다른 글
| MySQL - 트리거 (0) | 2023.05.15 |
|---|---|
| MySQL - 사용자 정의 함수(프로시저와 차이점) (0) | 2023.05.15 |
| MySQL 피벗테이블, 그룹콘캣(Group-concat) (0) | 2023.05.15 |
| SQL - 서브쿼리 (0) | 2023.05.15 |
| SQL - JOIN (0) | 2023.05.15 |