Database/MYSQL
23.05.30 / Stored Program(프로시저,DELIMITER,매개변수,변수,함수)
낫쏘링
2023. 5. 30. 11:49
728x90
[ Stored Program ]
- 내장식 프로그램, 프로그램 내장 방식, 저장 프로그램
- MySQL 전용 언어
- * SQL 전용 언어 : SQL문에 변수, 제어문, 입출력 등 프로그래밍 기능을 추가해서 SQL문으로 처리하기 어려운 문제를 해결할 수 있다.
[[ 특징 ]]
- MySQL 성능 향상 : 긴 쿼리의 내용을 전송하지 않고 프로시저의 이름과 매개 변수 등을 전송하여 네트워크 과부하를 줄일 수 있다.
- 유지 관리 간편 : 직접 SQL문을 작성하지 않고, 생성한 이름만 호출하여 일관된 작업을 진행할 수 있다.
- 모듈식 프로그래밍 : 저장 프로그램을 한 번 생성해서 쿼리 수정, 삭제 등 관리가 수월하고, 모듈처럼 자주 사용하는 구문을 재사용할 수 있다.
- 보안 강화 : 사용자 별 테이블에 접근 권한을 주지 않고, 저장 프로그램에 접근 권한을 줘서 보안을 강화한다.
- 저장 프로그램은 우리가 작성하는 프로그램 로직을 프로시저로 구현해서 사용한다. 일반적인 프로그래밍 언어에서 사용하는 함수와 비슷한 개념. 작업 순서가 정해진 독립된 프로그램의 소행 단위. DBMS에 저장되기 때문에 저장 프로그램이라고 부른다.
[[ 종류 ]]
[ 저장 루틴 ]
< 스토어드 프로시저(Procedure : 절차) >
- SQL문을 하나로 붂어서 편리하게 사용하는 기능
- 어떤 동작을 일괄 처리하기 위한 용도로 사용
- SQL문을 하나 씩 매 번 수행하는 것이 아닌, 프로시저로 만들어 놓은 후, 호출하는 방식을 주로 사용
- 생성 : CREATE PROCEDURE 프로시저_이름 (프로시저 이름은 관례적으로 sp_프로시저_이름 / proc_이름을 사용한다.)
- 구성 : 선언부 - 변수와 매개 변수 선언 / 실행부 - 프로그램 로직을 작성
- 변수 : 저장 프로시저나 트리거 안에서 사용되는 값
- 매개 변수(Parameter) : 저장 프로시저가 호출될 때, 호출되는 프로시저로 전달되는 값
- 주석 작성 : 한줄 - 이중 대쉬(--) / 여러 줄 - /* 주석 */
- 프로시저 묶어주기 : DELIMITER $$ 쿼리문 ; DELIMITER ;
$$는 아무거나 넣어줘도 상관 없지만 다른 기호와 중복을 피하기 위해 2개 연속해서 사용하는 것이 좋다.
- DELIMITER : 구분 문자라는 뜻 (종결 문자를 잠깐 $$로 바꿔주고 프로시저가 끝날 때 다시 종결 문자를 ;로 바꿔준다.)
- 매개 변수 사용 : IN 입력매개변수이름 데이터형식 (IN 생략 가능) / OUT 출력매개변수이름 데이터형식 (OUT 생략 불가능)
DELIMITER $$
CREATE PROCEDURE sp_프로시저_이름(IN 또는 OUT Parameter/매개변수)
BEGIN
SQL문(프로시저 내용)
END $$
DELIMITER ;
- 프로시저 호출 : CALL 스토어드_프로시저_이름(전달값);
DB 네임 오른쪽 마우스 클릭하고 새로 생성 - 저장 프로시저로 만들 수도 있다.-- 회원 아이디와 비밀번호를 받아 회원의 이메일을 조회하는 프로시저 DELIMITER $$ CREATE PROCEDURE sp_user_email (IN m_id VARCHAR(200), IN m_pw VARCHAR(200)) 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_user_email('id001', 'pw001'); -- 프로시저 삭제 DROP PROCEDURE sp_user_email; DROP PROCEDURE IF EXISTS sp_user_email; -- 만약 ~가 존재한다면... : IF EXISTS (기존 프로시저를 지우고 다시 생성할때)
- 변수 선언 : DECLARE 변수이름 데이터형식;
- 변수 초기화 : SET 변수이름 := 초기 값;
- 변수 선언과 초기화 : DECLARE num INT DEFUALT 300;
-- 변수 선언과 초기화 (인수를 입력 받지 않고 변수의 값을 미리 정해뒀을 때) DELIMITER $$ CREATE PROCEDURE sp_if_test() -- 인수 입력받지 않기 때문에 매개 변수 없음 BEGIN DECLARE num INT; -- num이라는 변수 선언 SET num := 300; -- 변수 초기화 -- := 에서 = 는 비교 연산자 중 대입 연산자 / SET 다음에 작성하는 이퀄 기호는 값을 대입 -- 변수의 초기화와 다른 대입 연산 둘 다 똑같은 기능임 -> 둘이 혼동할 수 있기 때문에 := 형태로 사용한다. -- DECLARE num INT DEFUALT 300; 로 변수 선언과 초기화 동시에 가능함 IF num = 300 THEN -- 만약 num이 300이면... SELECT '300이 맞습니다.' AS '숫자'; ELSE SELECT '300이 아닙니다.' AS '숫자'; END IF; -- IF 문이 끝나면 EDN IF 해준다. END $$ DELIMITER ; CALL sp_if_test;
-- 변수 선언과 초기화 (사용자에게 인수 값을 입력받았을 때) DELIMITER $$ CREATE PROCEDURE sp_level(IN memId VARCHAR(50)) -- 매개변수가 있어야 인수 받을 수 있음 BEGIN DECLARE mLevel INT; -- 변수 선언 SET mLevel := 0; -- 변수 초기화 SELECT M.m_level INTO mLevel -- 입력 받은 인수(id)의 M.m_level 값을 mLevel 변수에 대입한다. FROM tb_member AS M WHERE M.m_id = memId; -- M.m_id가 입력 받은 인수와 일치할 경우 IF(mLevel = 1) THEN -- THEN 써주는 곳과 ; 써주는 곳 확인하기 SELECT '관리자'; ELSEIF(mLevel = 2) THEN SELECT '판매자'; ELSE SELECT '구매자'; END IF; END $$ DELIMITER ;
- JOIN으로 m.* 해서 member 테이블의 내용만 전체 확인 가능
- DELETE 사용할 때, 원래 컬럼 이름을 명시할 필요 없는데 (DELETE FROM 테이블이름) JOIN 후 g 하면 goods 테이블에 해당하는 내용만 삭제 가능하다. (g.g_code / g.* 같이 컬럼 이름 적으면 오류)
- 가장 중요한 내용은 UPDATE와 DELETE 전, SELECT를 반드시 진행해야 한다.(쿼리문 확인 과정)
- 테이블을 지울 때 관계가 가장 하위에 있는 테이블 부터 지워야 한다. (해당 PK가 다른 테이블의 FK로 들어가있으면 안 됨)
-- PK와 FK로 서로 관련되어 있는 테이블의 특정 데이터(ID 선택해서) 지우기 DROP PROCEDURE IF EXISTS sp_mem_all_delete; DELIMITER $$ CREATE PROCEDURE sp_mem_all_delete(IN memId VARCHAR(200)) BEGIN DECLARE mLevel INT; -- 변수 선언 SET mLevel := 0; SELECT M.m_level INTO mLevel -- tb_member 테이블에서 조회한 회원 레벨을 mLevel에 대입한다. FROM tb_member AS M WHERE M.m_id = memId; IF(mLevel <> 1) THEN -- <> 의 뜻 != IF(mLevel = 2) THEN -- 권한이 판매자인 경우(주문테이블, 상품테이블) 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(mLevel = 3 ) THEN -- 권한이 구매자인 경우(주문테이블) DELETE O FROM tb_order AS O WHERE O.o_id = memId; END IF; DELETE -- 멤버 테이블의 데이터를 삭제하기 위해 로그인 테이블의 데이터부터 삭제 L FROM tb_login AS L WHERE L.login_id = memId; DELETE M FROM tb_member AS M WHERE M.m_id = memId; END IF; END $$ DELIMITER ; CALL sp_mem_all_delete('id005');
- OUT : OUT 매개 변수는 출력 매개 변수에 값을 대입하기 위해 SELECT....INTO 구문 사용
SELECT....INTO 구문은 MySQL에서 사용자가 정의한 변수에 SELECT에서 조회할 열에 값을 대입하기 위해 사용
주로 프로시저에서 사용BEGIN DECLARE num1 INT; SELECT member_age INTO num1 -- tb_test 테이블에서 조회할 member_age를 위에서 선언한 num1이라는 변수에 대입하겠다. FROM tb_test END ======================================================================================= DROP PROCEDURE IF EXISTS sp_aver_price; DELIMITER $$ CREATE PROCEDURE sp_aver_price(OUT averPrice INT) -- OUT 출력 매개 변수 BEGIN SELECT ROUND(AVG(G.g_price),0) INTO averPrice -- 평균 가격 계산 후 , averPrice에 담는다. FROM tb_goods AS G; END $$ DELIMITER ; CALL sp_aver_price(@val); -- 변수 @val을 선언 후 sp_aver_price 프로시저를 호출한다. 그 후 호출 결과가 @val에 저장된다. SELECT @val; -- @val의 값을 출력-- 구매자 아이디, 이름, 총 구매 금액, 총 구매 금액을 기준으로 나눈 등급 조회 -- 총 구매 금액을 기준으로 내림차순 정렬 SELECT O.o_id AS 'id' , M.m_name AS 'name' , SUM(O.o_amount * G.g_price) AS 'total' , (CASE WHEN SUM(O.o_amount * G.g_price) >= 30000000 THEN 'VIP 등급 고객' WHEN SUM(O.o_amount * G.g_price) >= 20000000 THEN 'Daiamond 등급 고객' WHEN SUM(O.o_amount * G.g_price) >= 10000000 THEN 'Gold 등급 고객' WHEN SUM(O.o_amount * G.g_price) >= 8000000 THEN 'Silver 등급 고객' WHEN SUM(O.o_amount * G.g_price) >= 5000000 THEN 'Bronze 등급 고객' ELSE '일반 회원' END) AS 'order level' FROM tb_order AS O INNER JOIN tb_member AS M ON O.o_id = M.m_id INNER JOIN tb_goods AS G ON O.o_g_code = G.g_code GROUP BY M.m_id ORDER BY total DESC;
- WHILE : 여러 번 반복하는 WHILE 문 / WHILE은 해당 조건식이 참일 경우 반복
WHILE(조건식) DO
SQL문
END WHILE;DELIMITER $$ CREATE PROCEDURE proc_while_test() BEGIN DECLARE num INT; -- 1에서 100가지 증가할 변수 DECLARE hap INT; -- 값을 누적할 변수 SET num := 1; SET hap := 0; WHILE(num <= 100) DO SET hap := hap + num; SET num := num + 1; END WHILE; SELECT hap; END $$ DELIMITER ; CALL proc_while_test();
< 스토어드 함수 >
- 사용자가 직접 함수를 만들어서 사용한다.
- 내장 함수(Built-in Function)가 사용자를 만족하는 모든 함수를 제공하지 않는다. 필요에 의해 사용자가 직접 함수를 만들어 사용.
- 형태와 사용 용도에서 스토어드 프로시저와 차이가 있다.
- 단점 : 유지보수 복잡성 증가 -> 각 기능을 담당하는 프로그램 코드가 자바와 MySQL 스토어드 프로그램으로 분산되어 관리되기 때문에 애플리케이션의 설치나 배포가 복잡해진다.
- 정의 및 호출 : CREATE FUNCTION 스토어드_함수_이름(매개변수) -> 모두 입력 매개 변수로 사용하기 때문에 IN 생략
RETURNS 반환_형식(type) : RETURNS문으로 반환할 값의 데이터 형식 지정
DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
RETURNS 반환_형식
-- 사용 준비 완료
BEGIN
프로그래밍 코드
RETURN 반환값;
END $$
DELIMITER ;
SELECT 스토어드_함수_이름(); -- 스토어드 함수는 SELECT문 안에서 호출한다. (스토어드 프로시저는 CALL)
*스토어드 함수 이름은 보통 sf_이름; 형태로 사용한다.
-- 함수 사용 전, 생성 권한을 허용해야 한다. (한 번 만 설정하면 됨)
SET GLOBAL log_bin_trust_function_creators =1;DELIMITER $$ CREATE FUNCTION sf_sum(num1 INT, num2 INT) -- 2개의 정수형 매개 변수 RETURNS INT -- 함수가 반환할 데이터의 형식 지정 BEGIN DECLARE total INT; SET total := num1 + num2; RETURN total; -- return문으로 결과 반환 END $$ DELIMITER ; SELECT sf_sum(10,30);DROP FUNCTION IF EXISTS sf_get_mem_age; -- 회원 테이블에 회원 나이는 없는데 출생 연도는 있을 때 나이 구하기 DELIMITER $$ CREATE FUNCTION sf_get_mem_age(bYear INT) RETURNS INT BEGIN DECLARE age INT; SET age := YEAR(CURDATE()) - bYear; -- CURDATE() : 현재 날짜 함수 / 출력 형식 yyyy-mm-dd -- YEAR() : 날짜에서 연도만 추출하는 함수 RETURN age; END $$ DELIMITER ; -- 단독으로 결괏값 조회 SELECT sf_get_mem_age(2005); -- 테이블에서 연도 가져와서 조회 SELECT U.u_id , U.u_name , sf_get_mem_age(YEAR(U.u_birth)) AS '나이' FROM tb_user AS U;[ 트리거 ]
< 스토어드 프로시저 vs 스토어드 함수 >
- 스토어드 함수 : OUT 파라미터를 사용할 수 없다. 스토어드 함수의 파라미터는 모두 입력 파라미터로 사용된다.
- 스토어드 함수는 RETURNS 예약어를 통해 반환할 값의 데이터 형식을 지정하고, 본문 안에서 RETURN문으로 하나의 값을 반환해야 한다.
- 프로시저 CALL로 호출하는 독립적인 프로그램, 함수는 SELECT문 안에서 호출. 값을 제공하는 용도로 사용.
-스토어드 프로시저 안에서 SELECT문을 사용했지만 스토어드 함수는 집합 결과를 반환하는 SELECT문은 사용할 수 없다. 대신 하나의 결과만 반환하는 SELECT는 사용 가능
- 어떤 계산을 통해 하나의 값을 반환하는데 주로 사용한다.
방아쇠 - 해당 테이블에 어떤 이벤트(데이터 변경문)가 발생하면 자동으로 실행된다.
트리거 : 테이블에 부착한다.
이벤트 : 테이블에 데이터 삽입, 수정, 삭제가 발생하면 해당 테이블에 부착되어 있는 트리거가 자동으로 실행
트리거 이벤트(데이터 변경) : INSERT, UPDATE, DELETE
트리거 타임(발동 시점) : AFTER, BEFORE
< 특징 >
- 프로시저와 다르게 직접 실행할 수 없다. 해당 테이블에 이벤트(DML)가 발생하면, 자동 실행
- IN, OUT 매개 변수를 사용할 수 없다.
- 이벤트 발생 후 ROLLBACK 할 수 없다.
CREATE TRIGGER 트리거_이름
트리거_타임
트리거_이벤트
-- 트리거 타임이 AFTER / 트리거 이벤트가 INSERT라고 작성했을 때
-- AFTER INSERT -> INSERT 후 트리거 명령문 발동(UPDATE,DELETE 시에는 발동하지 않는다.)
ON 테이블_이름
-- 트리거를 부착할 테이블
FOR EACH ROW
-- 각 행마다 적용하겠다. 트리거 사용할 때 항상 작성.
-- 사용 준비 완료
BEGIN
트리거 명령문
-- SET @msg = '테이블에 회원 정보가 등록됨'
END
-- 트리거 사용 전, 생성 권한을 허용해야 한다. (한 번 만 설정하면 됨 - 함수 사용 전에 해줬으면 안 해도 됨)
SET GLOBAL log_bin_trust_function_creators =1;[ 트리거 삭제 ]
DROP TRIGGER 트리거_이름;-- AFTER DELETE TRIGGER 생성 -- tb_member2에서 데이터가 '삭제(DELETE)'된 후(AFTER)에 -- member2_backup에 삭제된 정보,누가,언제 지웠는지 insert 되는 TRIGGER 작성 DROP TRIGGER IF EXISTS member_delete_trigger; DELIMITER $$ CREATE TRIGGER member_delete_trigger -- 트리거 이름 AFTER DELETE -- 트리거 타임, 트리거 이벤트 ON tb_member2 -- 트리거를 부착할 테이블 지정 FOR EACH ROW BEGIN INSERT INTO member2_backup VALUES (OLD.m_id, OLD.m_name, OLD.m_addr, '삭제', CURDATE(), CURRENT_USER()); -- INSERT INTO 테이블_이름(컬럽1,컬럼2,컬럼3) VALUES(데이터1,테이터2,테이터3) -- 기존 member2 테이블의 데이터를 insert 하겠다. END $$ DELIMITER ; ============================================================================================= -- tb_member2 테이블에서 데이터 삭제 DELETE FROM tb_member2 WHERE tb_member2.m_id = 'id009'; ============================================================================================= -- TRIGGER 시험해보기 전에 있어야하는 테이블들(tb_member2,member2_backup) 생성 -- 연습용 테이블(기존 데이터가 있는 테이블) 복사 CREATE TABLE tb_member2 ( SELECT M.m_id , M.m_name , M.m_addr FROM tb_member AS M ); -- CREATE ~ (SELECT) : 테이블을 복사해서 새로운 테이블 생성 -- 단, 기본 키에 대한 정의는 복사되지 않는다.(키 설정 다시 해야함) -- 백업 테이블 생성(생성만 한 상태 - 데이터 없음) -- tb_member2에서 데이터가 삭제되면 누가 언제 어떤 정보를 삭제했는지 insert될 테이블 CREATE TABLE member2_backup ( mem_id VARCHAR(50) NOT NULL , mem_name VARCHAR(50) NOT NULL , mem_addr VARCHAR(50) NOT NULL , mod_type CHAR(2) -- 변경 타입 (수정 또는 삭제만 뜨게 할 것이기 때문에 2로 설정) , mod_date DATE -- 변경 일자 , mod_user VARCHAR(50) -- 변경한 사용자 );< OLD, NEW >
[ 커서 ]
- 테이블에 INSERT, UPDATE, DELETE 작업이 수행되면, 임시로 사용되는 테이블
- 사용자가 직접 생성하는 테이블이 아닌 MySQL에서 알아서 생성하고 관리해주는 테이블
- NEW : 테이블에 새로운 값이 들어가는 경우(INSERT)
- OLD : 삭제 전, 삭제 예정 값이 OLD 테이블에 잠시 저장(DELETE)
- UPDATE는 둘 다 사용 : 예전 값, 새 값이 둘 다 있기 때문
728x90