[목차]
01. Stored Program
MySQL 전용 언어.
➡️ SQL문에 변수, 제어문, 입출력 등 프로그래밍 기능을 추가해서 SQL문으로 처리하기 어려운 문제를 해결할 수 있다.
- 저장 프로그램은 우리가 작성하는 프로그램 로직을 프로시저로 구현해서 사용한다.
- 일반적인 프로그래밍 언어에서 사용하는 함수와 비슷한 개념.
- 작업 순서가 정해진 독립된 프로그램의 수행 단위.
- DBMS에 저장되기 때문에 저장 프로그램이라고 부른다.
특징
👉🏻MySQL 성능 향상➡️ 긴 쿼리의 내용을 전송하지 않고 프로시저의 이름과 매개 변수 등 전송하여 네트워크 부하를 줄일 수 있다.
유지 관리가 간편
➡️ 직접 SQL문을 작성하지 않고, 생성한 이름만 호출하여 일관된 작업을 진행할 수 있다.
모듈식 프로그래밍
➡️ 저장 프로그램을 한번 생성해서 쿼리 수정, 삭제 등 관리가 수월하고, 모듈처럼 자주 사용하는 구문을 재사용할 수 있다.
보안 강화
➡️ 사용자 별 테이블에 접근 권한을 주지 않고, 저장 프로그램에 접근 권한을 줘서 보안을 강화한다.
종류
👉🏻저장 루틴(프로시저, 함수)트리거
커서
스토어드 프로시저(Procedure)
👉🏻SQL문을 하나로 묶어서 편리하게 사용하는 기능.어떤 동작을 일괄 처리하기 위한 용도로 사용
SQL문을 하나씩 매번 수행하는 것이 아닌, 프로시저로 만들어 놓은 후, 호출하는 방식으로 주로 사용한다.
➡️ 생성
👉🏻CREATE PROCEDURE 프로시저_이름➡️ 구성
👉🏻선언부와 실행부로 구성(BEGIN ~ END)
- 선언부 : 변수와 매개 변수를 선언한다.
- 실행부 : 프로그램 로직을 작성한다.
- 변수 : 저장 프로시저나 트리거 안에서 사용되는 값
- 매개변수 : 저장 프로시저가 호출될 때, 호출되는 프로시저로 전달되는 값.
➡️ 형식
👉🏻프로시저 묶어주기DELIMITER $$(@@, // 도 가능) → 종결 문자를 $$로 변경
CREATE PROCEURE sp_프로시저_이름(IN 또는 OUT Parameter)
BEGIN
SQL문(프로시저 내용)
END $$
DELIMITER ; → 다시 ;으로 종결 문자 변경
- DELIMITER : 종결 문자 변경
- SQL문을 작성했을 때 종결 문자는 ;인데, 헷갈리지 않도록 종결 문자를 정한다.
- $$말고 다른 문자도 가능하지만, 다른 기호화 중복되지 않도록 하고, 2개를 연속해서 사용하는 것이 좋다.
➡️ 매개 변수의 사용
👉🏻IN 입력매개변수이름 데이터형식 (IN은 생략 가능)OUT 출력매개변수이름 데이터형식 (OUT은 생략 불가능)
- OUT 매개변수는 출력매개변수에 값을 대입하기 위해 SELECT ~ INTO 구문을 사용한다.
- SELECT ~ INTO 구문은 MySQL에서 사용자가 정의한 변수에 SELECT에서 조회할 열에 값을 대입하기 위해 사용. 주로 프로시저에서 사용한다.
➡️ 프로시저 호출
- CALL 프로시저_이름(전달 값);
➡️ 프로시저 삭제
- DROP PROCEDURE 프로시저_이름;
➡️ 예제
회원의 이름으로 정보를 조회하는 프로시저
-- 프로시저 생성 DELIMITER $$ CREATE PROCEDURE sp_member_info(IN mName VARCHAR(20)) BEGIN SELECT * FROM tb_member AS m WHERE m.m_name = mName; -- 매개변수가 들어가는 자리 END $$ DELIMITER ; -- 프로시저 호출 CALL sp_member_info('홍07');회원의 아이디와 비밀번호를 입력 받아서 회원의 이메일을 조회하는 프로시저를 만들고, 호출하시오
-- 프로시저 생성 DELIMITER $$ CREATE PROCEDURE sp_member_email(IN m_id VARCHAR(20), IN m_pw VARCHAR(20)) -- 코멘트 작성 COMMENT '회원의 아이디와 비밀번호를 입력 받아 이메일을 조회하는 프로시저' BEGIN SELECT m.m_email FROM tb_member AS m WHERE m.m_id = m_id AND m.m_pw = m_pw; END $$ DELIMITER ; -- 프로시저 호출 CALL sp_member_email('id003', 'pw003');➡️ 제어문 예제
-- 프로시저 생성 DELIMITER $$ CREATE PROCEDURE sp_if_test() BEGIN DECLARE num INT; -- num이라는 변수 선언 SET num := 300; -- 변수에 값 대입 IF num = 300 THEN SELECT '300이 맞습니다.'; ELSE SELECT '300이 아닙니다.'; END IF; END $$ DELIMITER ;- :=
- = 기호는 비교 연산 대입 연산 SET 다음에 작성하는 이퀄 기호는 값을 대입하는게 맞는데, 혼동할 수 있으니 구분을 위해 := 로 작성함.
- DECLATE num INT DEFAULT 300;
- 변수 선언과 초기화를 동시에 가능.
회원의 아이디를 입력 받아 권한을 조회하는 프로시저를 작성하시오.
-- sp_member_level라는 프로시저가 존재하면 삭제 DROP PROCEDURE IF EXISTS sp_member_level; -- 프로시저 생성 DELIMITER $$ CREATE PROCEDURE sp_member_level(IN m_id VARCHAR(50)) BEGIN DECLARE mLevel INT; -- 변수 선언 SET mLevel := 0; -- 값 세팅 SELECT m.m_level INTO mLevel FROM tb_member AS m WHERE m.m_id = m_id; IF mLevel = 1 THEN SELECT '관리자' AS '권한'; ELSEIF mLevel = 2 THEN SELECT '판매자' AS '권한'; ELSE SELECT '구매자' AS '권한'; END IF; END $$ DELIMITER ; -- 프로시저 호출 CALL cp_member_level('id002');
실습
- JOIN으로 m.*의 내용만 확인 가능
- DELETE 시, 컬럼 이름을 명시할 필요 없는데, tb_goods의 내용을 지운다.
➡️ (tb_goods AS g) 컬럼 자리에 g를 명시하면 g에 해당하는 내용을 삭제할 수 있다.
- 가장 중요한 내용은 UPDATE, DELETE 전 반드시 SELECT를 진행한다.
- IF 조건문을 사용하여, 회원의 아이디를 입력 받으면 모든 테이블의 내용이 삭제되도록 프로시저를 작성하시오. (단, 회원 레벨 1은 삭제 할 수 없다.)
- 테이블 ERD를 보면서 테이블간 연결을 확인하면서 순차적으로 삭제해야 된다.
-- 프로시저가 존재하면 삭제 DROP PROCEDURE IF EXISTS sp_mem_all_delete; -- 프로시저 생성 DELIMITER $$ CREATE PROCEDURE sp_mem_all_delete(IN memId VARCHAR(50)) BEGIN -- 변수 선언 DECLARE memLv INT; -- tb_member에서 조회한 회원 레벨을 memLv에 대입해서 조회 SELECT m.m_level INTO memLv FROM tb_member AS m WHERE m.m_id = memId; -- 레벨에 따라서 조건문 IF(memLv <> 1) THEN -- 1이 아니라면 IF(memLv = 2) THEN -- 2인 경우 DELETE o FROM tb_order AS o INNER JOIN tb_goods AS g WHERE g.g_seller_id = memId; DELETE g FROM tb_goods AS g WHERE g.g_seller_id = memId; END IF; IF(memLv = 3) THEN -- 3인 경우 DELETE o FROM tb_order AS o WHERE o.o_id = memId; END IF; -- member 테이블을 삭제하기 위해 로그인 테이블을 먼저 삭제 DELETE l FROM tb_login AS l WHERE l.login_id = memId; -- 최종적으로 member테이블의 매개변수로 받은 id의 회원 정보 삭제 DELETE m FROM tb_member AS m WHERE m.m_id = memId; END IF; END $$ DELIMITER ; -- 프로시저 호출 CALL sp_mem_all_delete('id008');🗣 프로시저를 단계별로 나눠서 all 프로시저를 만들고 그 프로시저에서 단계별 프로시저를 순서대로 call 하는 방법도 있다.
➡️ ex) sp_all → CALL sp_1, CALL sp_2, CALL sp_3 …
tag : #데이터베이스 #DB #Stored Program #프로시저
Uploaded by N2T