프로시저
이터베이스에서 특정 작업을 수행하는 일련의 SQL 문장들을 그룹화하고, 이를 하나의 단위로 실행할 수 있는 저장된 코드 블록
특징
-
이름 지정: 프로시저는 고유한 이름을 가지며, 이를 사용하여 호출
-
매개변수: 프로시저는 입력 매개변수를 받아들일 수 있으며 이를 통해 호출 시에 다양한 값을 전달
-
로직과 제어 구조: 프로시저 내에서는 조건문(IF, ELSE), 반복문(WHILE), 변수 선언 등과 같은 로직 및 제어 구조를 사용
-
트랜잭션 관리: 프로시저는 트랜잭션을 명시적으로 관리할 수 있어 여러 SQL 문장을 하나의 트랜잭션으로 묶거나 롤백 가능
-
재사용성: 프로시저는 코드의 재사용을 촉진하며, 동일한 로직을 여러 곳에서 호출하여 사용
-
보안 및 권한 제어: 데이터베이스에서는 프로시저에 대한 실행 권한을 부여하여 보안을 강화
CREATE
[OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
*/
-- delimiter $$ 기호 변경 가능
delimiter ##
-- 프로시저 삭제
DROP PROCEDURE if EXISTS p_multiselect;
-- 프로시저 생성
delimiter $$
CREATE PROCEDURE p_multiselect()
begin
SELECT * from emp WHERE ename='SMITH';
SELECT * from dept WHERE dname='SALES';
END $$
delimiter ;
-- procedure 실행
CALL p_multiselect(); -- 전달값 없는 경우
delimiter $$
CREATE PROCEDURE p_inVar(IN username VARCHAR(15)) -- in 변수
begin
SELECT * from member WHERE mem_name = username;
END $$
delimiter ;
CALL p_inVar('에이핑크'); -- 전달값
-- 여러개 경우
delimiter $$
CREATE PROCEDURE p_inVar2(
IN userNum INT,
IN userheight int
) -- in 변수
begin
SELECT *
FROM member
WHERE mem_number > userNum AND height > userheight;
-- 넘겨받은 값보다 더 큰 경우 출력
END $$
delimiter ;
CALL p_inVar2(5,'160');
CALL p_inVar2(3,'170'); -- 재사용성
매게변수
- in : IN 매개변수는 프로시저에 값을 전달할 때 사용. 이 매개변수는 프로시저 내에서 읽기 전용이며, 외부에서 전달된 값을 사용하는데 활용
CREATE PROCEDURE example_procedure
(IN param1 INT, IN param2 VARCHAR(255))
BEGIN -- param1과 param2를 읽어서 작업 수행
END;
- out : OUT 매개변수는 프로시저에서 값을 반환할 때 사용. 이 매개변수는 프로시저 내에서 값이 할당되어 외부로 전달되는데 사용
CREATE PROCEDURE example_procedure(OUT result INT)
BEGIN
-- 작업 수행 후 result에 값을 할당
END;- inout : INOUT 매개변수는 프로시저에 값을 전달하고, 작업 수행 후 그 값을 변경하여 외부로 전달할 때 사용. 즉, 입력과 출력을 모두 담당
CREATE PROCEDURE example_procedure(INOUT inout_param INT)
BEGIN
-- inout_param을 읽고, 작업 수행 후 inout_param에 새로운 값을 할당
END;[예시]
delimiter $$
CREATE PROCEDURE p_inOutVar(
IN txtValue CHAR(10),
OUT outValue int
) -- in, out 변수
begin
INSERT INTO testTBL VALUES(NULL, txtValue);
SELECT MAX(id) INTO outValue from testTBL;
END $$
delimiter ;
-- error
DESC testTBL;
-- 저장 프로시저를 만드는 시점에서는 테이블이 없어도 된다. 문제가 없음
-- 실행시점에서는 문제가 됨. 실행시점에서 테이블이 불려지기 때문
CALL p_inOutVar('test', @myValue);
-- error , table 이 없기 때문 테이블 만들어주자
CREATE TABLE testTBL
(id INT AUTO_INCREMENT PRIMARY KEY, myValue VARCHAR(10));
SELECT * FROM testTBL;
INSERT INTO testTBL VALUES(NULL,'aa');
INSERT INTO testTBL VALUES(NULL,'bb');
INSERT INTO testTBL VALUES(NULL,'cc');
-- 파라미터 넣어 확인해보기
CALL p_inOutVar('test1', @myValue);
CALL p_inOutVar('test2', @myValue);
-- out값 확인하기
SELECT CONCAT('입력된 id값 ==>', @myValue) --ㅡmax(id)
SELECT @myvalue; -- out 변수는 select로 확인한다.
반복문
while : 조건이 참일 때 반복되며, 거짓이 되는 순간 반복 안함.
/*
while 조건 do
실행문
end while ;
*/
DELIMITER $$
DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
CREATE PROCEDURE test_mysql_while_loop()
BEGIN
DECLARE x INT; -- 변수 선언
DECLARE str VARCHAR(255);
SET x = 1;
SET str = ' ';
WHILE x <= 5 DO
SET str = CONCAT(str,x,',');
SET x = x + 1;
END WHILE ;
SELECT str;
END $$
DELIMITER ;
CALL test_mysql_while_loop();
DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
DECLARE hap INT; -- 합계
DECLARE num INT; -- 1부터 100까지 증가
SET hap = 0; -- 합계 초기화
SET num = 1;
WHILE (num <= 100) DO -- 100까지 반복.
SET hap = hap + num;
SET num = num + 1; -- 숫자 증가
END WHILE;
SELECT hap AS '1~100 합계';
END $$
DELIMITER ; -- 띄어쓰기 필수
repeat : until 뒤에 조건이 거짓일 때 반복되며, 참이 되는 순간 반복 안함.
/*
repeat
실행문;
until 조건
end repeat
*/
DELIMITER $$
CREATE PROCEDURE test_mysql_repeat_loop()
BEGIN
DECLARE x INT;
DECLARE val VARCHAR(255);
SET x = 1; SET val = '';
REPEAT
SET val = CONCAT(val,x,',');
SET x = x + 1;
UNTIL X > 5
END REPEAT;
SELECT val;
END$$
DELIMITER ;
CALL test_mysql_repeat_loop();loop : 특정 횟수만큼 반복되는 루프를 생성하는 데 사용
CREATE PROCEDURE test_loop()
BEGIN
DECLARE x INT; -- 변수 x 선언
DECLARE val VARCHAR(255); --변수 val을 선언
SET x = 1; SET val = '';
-- 변수 x를 1로 초기화하고, val은 빈 문자열로 초기화
loop_lable : Loop -- LOOP는 첫 번째 무한 루프를 정의
loop_lable은 라벨(식별자)로 사용되며,
나중에 LEAVE 또는 ITERATE 문에서 참조
if x > 10 then
leave loop_lable ;
END if; -- x가 10을 초과하면 루프를 빠져나감
loop_lable : Loop
if (x MOD 2) then
iterate loop_lable ;
else
SET val =CONCAT(str, X, ',');
END if;
x가 홀수일 때 iterate loop_lable;을 사용하여 루프의 처음으로 이동하고,
그렇지 않으면 SET val = CONCAT(str, X, ',');를 통해
val에 문자열을 추가
END loop;
SELECT val;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE ifelse_proc(IN memName VARCHAR(10))
BEGIN
DECLARE debutYear INT; -- 변수 선언
SELECT YEAR(debut_date) into debutYear FROM member
WHERE mem_name = memName;
IF (debutYear >= 2015) THEN
SELECT '신인 가수네요. 화이팅 하세요.' AS '메시지';
ELSE
SELECT '고참 가수네요. 그동안 수고하셨어요.'AS '메시지';
END IF;
END $$
DELIMITER ;
변수 선언 및 사용
-- 변수 선언 및 사용-----------------------------
SET @txt = '가수 이름==> ' ;
SET @height = 166;
SELECT @txt , mem_name, height FROM member WHERE height > @height ;
SELECT * FROM member;
SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @COUNT;
-- ----------------------------------------
SET @COUNT = 5;
PREPARE mySQL FROM 'SELECT mem_name, height
FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @COUNT;
-- ----------------------------------------------------
DELIMITER $$
CREATE PROCEDURE dynamic_proc(
IN tableName VARCHAR(20)
)
BEGIN
SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
PREPARE myQuery FROM @sqlQuery;
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;
CALL dynamic_proc ('member');
사용자 함수
사용자가 직접 정의한 함수로, 데이터베이스에서 특정 작업을 수행하기 위해 사용
함수 만들기 전에 반드시 실행
SET GLOBAL log_bin_trust_function_creators = 1;
CREATE FUNCTION 함수이름(매개변수1 데이터타입, 매개변수2 데이터타입, ...)
RETURNS 반환값의데이터타입
BEGIN
-- 함수 내용
END;
CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT
BEGIN
DECLARE result INT;
SET result = a + b;
RETURN result;
END;
-- 함수 호출
SELECT add_numbers(3, 5); -- 결과: 8
함수 이름 (FUNCTION_NAME): 함수의 이름을 정의합
매개변수 (PARAMETERS): 함수가 받는 입력 값으로, 데이터 타입과 이름을 지정
반환 값 (RETURNS): 함수가 반환하는 값의 데이터 타입을 지정
BEGIN과 END: 함수 내용의 시작과 끝을 나타내는 블록
DECLARE: 지역 변수를 선언하는데 사용
SET: 변수에 값을 할당하는데 사용
RETURN: 함수가 반환할 값을 지정cursor
결과 집합을 조작하고 탐색하는 데 사용되는 개체. 주로 저장 프로시저나 트리거에서 사용되며, 결과 집합의 행을 한 번에 하나씩 처리할 때 유용
단계
1) 커서 선언 : declare cursor
2) 반복조건 서언
3) 커서 열기 : open
4) 커서에서 데이터 가져오기 : fetch
5) 데이터처리
6) 커서 닫기 : closeDROP PROCEDURE IF EXISTS u_cursor_proc;
DELIMITER $$
CREATE PROCEDURE u_cursor_proc()
BEGIN
DECLARE memNumber INT; -- 회원의 인원수
DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수 set cnt=0;
DECLARE totNumber INT DEFAULT 0; -- 인원의 합계
DECLARE endOfRow BOOLEAN DEFAULT FALSE;
-- 행의 끝 여부(기본을 FALSE)
DECLARE memberCuror CURSOR FOR-- 커서 선언
SELECT mem_number FROM member;
DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입
FOR NOT FOUND SET endOfRow = TRUE;
OPEN memberCuror; -- 커서 열기
cursor_loop: LOOP
FETCH memberCuror INTO memNumber; -- 데이터 인출
IF endOfRow THEN
LEAVE cursor_loop;
END IF;
SET cnt = cnt + 1;
SET totNumber = totNumber + memNumber;
END LOOP cursor_loop;
SELECT (totNumber/cnt) AS '회원의 평균 인원 수';
CLOSE memberCuror; -- cloes고정
END $$
DELIMITER ;
-- 선언
DECLARE employee_cursor CURSOR FOR SELECT emp_id, emp_name
FROM employees WHERE department = 'IT';
-- 열기
OPEN employee_cursor;
-- 데이터 가져오기
FETCH employee_cursor INTO emp_id_var, emp_name_var;
-- 반복문을 통한 데이터 처리
WHILE NOT done DO
-- 처리할 코드
-- ...
-- 다음 행 가져오기
FETCH employee_cursor INTO emp_id_var, emp_name_var;
-- 더 이상 가져올 행이 없으면 반복문 종료
IF no_more_rows THEN
SET done = TRUE;
END IF;
END WHILE;
-- 커서 닫기
CLOSE employee_cursor;
새로운 시작. 그리고 도약