다른 프로그램에서 직접 SQL문을 작성하지 않고 이름만 호출하도록 설정함으로써, 데이터베이스에서 관련된 스토어드 프로시저의 내용을 일관되게 수정/유지보수 등의 작업을 할 수 있다.
모듈식 프로그래밍이 가능하다.
한번 생성해 놓으면 언제든지 실행가능. 코드 재사용 가능
수정, 삭제 관리 수월
보안을 강화할 수 있다.
사용자 별로 테이블에 접근 권한을 주지 않고, 스토어드 프로시저에만 접근 권한 줌으로써 좀 더 보안 강화
MySQL에서 제공되는 프로그래밍 기능
쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용
함수와 비슷하다. 매개변수도 사용가능
쿼리 모듈화
필요할 때마다 호출만 하면 훨씬 편리하게 MySQL운영
CALL 프로시저_이름() 으로 호출
DROP PROCEDURE IF EXISTS userPoc;
DELIMITER $$
CREATE PROCEDURE userProc()
BEGIN
SELECT * FROM usertbl;
END $$
DELIMITER ; -- 다시 ; 로 바꿔줘야함
CALL userProc();
입력 매개 변수 - IN
DROP PROCEDURE IF EXISTS userProc2;
DELIMITER $$
CREATE PROCEDURE userProc2(IN userBirth INT, userHeight INT)
BEGIN
SELECT * FROM usertbl WHERE birthYeaar > userBirth AND height > userHeight;
END $$
DELIMITER ;
CALL userProc2(1970, 178);
출력 매개 변수 - OUT 출력_매개변수_이름 데이터 형식
DROP PROCEDURE IF EXISTS userProc3;
DELIMITER $$
CREATE PROCEDURE userProc3(IN txtValue CHAR(10), OUT outValue INT)
BEGIN
INSERT INTO testtbl VALUES(NULL, txtValue);
SELECT MAX(id) INTO outValue FROM testtbl;
END $$
DELIMITER ;
CALL userProc3('7번째 호출', @myValue);
SELECT CONCAT('현재 입력된 ID값 -> ', @myValue);
SELECT * FROM testtbl;
스토어드 프로시저를 이용한 프로그래밍
형식
DELIMITER $$
CREATE PROCEDURE 스토어드 프로시저이름()
BEGIN
이 부분에 SQL 프로그램 코딩 ..
END $$
DELIMITER ;
CALL 스토어드 포르시저 이름();
IF ELSE
DELIMITER $$
CREATE PROCEDURE ifesleProc(IN userName VARCHAR(10))
BEGIN
DECLARE bYear INT;
SELECT birthYear into bYear FROM usertbl WHERE name = userName;
IF(bYear >=1980)
THEN SELECT '아직 젊군요';
ELSE
SELECT '나이가 지긋하시네요';
END IF;
END $$
DELIMITER ;
SELECT * FROM usertbl;
CALL ifesleProc('이승기');
CASE
조건에 따라 분기
다중 분기
DELIMITER $$
CREATE PROCEDURE caseProc(IN userName VARCHAR(10))
BEGIN
DECLARE bYear INT;
DECLARE tti CHAR(3);
SELECT birthYear INTO bYear FROM usertbl WHERE name = userName;
CASE
WHEN (bYear%12=0) THEN SET tti = '원숭이';
WHEN (bYear%12=1) THEN SET tti = '닭';
WHEN (bYear%12=2) THEN SET tti = '개';
WHEN (bYear%12=3) THEN SET tti = '돼지';
WHEN (bYear%12=4) THEN SET tti = '쥐';
WHEN (bYear%12=5) THEN SET tti = '소';
WHEN (bYear%12=6) THEN SET tti = '호랑이';
WHEN (bYear%12=7) THEN SET tti = '토끼';
WHEN (bYear%12=8) THEN SET tti = '용';
WHEN (bYear%12=9) THEN SET tti = '뱀';
WHEN (bYear%12=10) THEN SET tti = '말';
ELSE SET tti = '양';
END CASE;
SELECT CONCAT(userName, '띠 : ', tti);
END $$
DELIMITER ;
CALL caseProc('임재범');A
WHILE문
다른 프로그래밍 언어의 WHILE 과 동일한 개념
해당 부울식이 참인 동안에 계속 반복되는 반복문
DROP TABLE IF EXISTS gugutbl;
CREATE TABLE gugutbl (txt VARCHAR(100));
DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
DECLARE str VARCHAR(100);
DECLARE i INT;
DECLARE k INT;
SET i = 2;
WHILE(i<10) DO
SET str = '';
SET k = 1;
WHILE (k<10) DO
SET str = CONCAT(str, ' ', i, 'x', k, '=', i*k);
SET k = K + 1;
END WHILE;
SET i = i + 1;
INSERT INTO gugutbl VALUES(str);
END WHILE;
END $$
DELIMITER ;
CALL whileProc();
SELECT * FROM gugutbl;
오류처리
DECLARE 액션 HANDLER FOR 오류조건 처리할 문장;
액션
오류 발생 시에 행동
DELIMITER $$
CREATE PROCEDURE errorProc()
BEGIN
DECLARE i INT;
DECLARE hap INT;
DECLARE saveHap INT;
DECLARE EXIT HANDLER FOR 1264
BEGIN
SELECT CONCAT('INT 오버플로 직전의 합계 :', saveHap);
SELECT CONCAT('1+2+3+... +', i, ' =오버플로');
END;
SET i = 1;
SET hap = 0;
WHILE(TRUE)DO
SET saveHap = hap;
SET hap = hap + i;
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
CALL errorProc();
PROCEDURE 확인
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_schema = 'sqldb' AND routine_type = 'PROCEDURE';
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE specific_name = 'userProc3';
변수
DROP PROCEDURE IF EXISTS nameProc;
DELIMITER $$
CREATE PROCEDURE nameProc(IN tblName VARCHAR(20))
BEGIN
SET @sqlQuery = CONCAT('SELECT * FROM ', tblName);
PREPARE myQuery FROM @sqlQuery;
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;
CALL nameProc ('usertbl');
스토어드 함수
사용자가 직접 만들어서 사용하는 함수
스토어드 프로시저와 유사
형태와 사용 용도에 있어 차이 있음
스토어드 함수의 개요
DELIMITER $$
CREATE FUNCTION 스토어드 함수이름(파라미터)
RETURNS 반환형식
BEGIN
이 부분에 코딩
RETURN 반환값;
END $$
DELIMITER ;
SELEcT 스토어드 함수이름();
파라미터에 IN, OUT 등을 사용할 수 없음
모두 입력 파라미터로 사용
RETURNS문으로 반환할 값의 데이터 형식 지정
본문 안에서는 RETURN문으로 하나의 값 반환
SELECT 문장 안에서 호출
스토어드 함수를 사용하기 위해서는 먼저 다음 쿼리문으로 스토어드 함수 생성 권한을 허용 해줘야 한다.
SET GLOBAL log_bin_trust_function_creators = 1;
함수 생성
DROP FUNCTION IF EXISTS userFunc;
DELIMITER $$
CREATE FUNCTION userFunc(value1 INT, value2 INT) RETURNS INT
BEGIN
RETURN value1 + value2;
END $$
DELIMITER ;
SELECT userFunc(100,200);
나이 계산 함수
DROP FUNCTION IF EXISTS getAgeFunc;
DELIMITER $$
CREATE FUNCTION getAgeFunc(bYear INT) RETURNS INT
BEGIN
DECLARE age INT;
SET age = Year(CURDATE()) - bYear;
RETURN age;
END $$
DELIMITER ;
SELECT getAgeFunc(2000);
SELECT getAgeFunc(1979) INTO @age1979;
SELECT getAgeFunc(2000) INTO @age2000;
SELECT CONCAT('1979년생과 2000년생의 나이차 :',@age1979 -@age2000);
커서(Cursor)
커서
스토어드 프로시저 내부에 사용
일반 프로그래밍 언어의 파일 처리와 방법이 비슷함
행의 집합을 다루기 편리한 기능 제공
테이블에서 여러 개의 행을 쿼리한 후, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식
커서의 처리 순서
예제
평균 키 구하기
커서 이해를 위한 실습
조건이 붙은 평균은 AVG() 함수 대신 커서 활용
DROP PROCEDURE IF EXISTS cursorProc;
DELIMITER $$
CREATE PROCEDURE cursorProc()
BEGIN
DECLARE userHeight INT ;
DECLARE cnt INT DEFAULT 0;
DECLARE totalHeight INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE;
DECLARE userCursor CURSOR FOR
SELECT height FROM usertbl;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET endOfRow = TRUE;
OPEN userCursor;
cursor_loop:
LOOP
FETCH userCursor INTO userHeight;
IF endOfRow THEN
LEAVE cursor_loop; -- break와 같음
END IF;
SET cnt = cnt + 1;
SET totalHeight = totalHeight + userHeight;
END LOOP cursor_loop;
SELECT CONCAT('고객키의 평균 : ', (totalHeight/cnt));
CLOSE userCursor;
END $$
DELIMITER ;
CALL cursorProc();
SELECT AVG(height) FROM usertbl;
이렇게 함수 만든거랑 AVG()함수랑 똑같다.
AVG() 내장 함수와 동일한 기능 구현
고객 등급 정하기
SELECT * FROM usertbl;
ALTER TABLE usertbl ADD grade VARCHAR(5);
DROP PROCEDURE IF EXISTS gradeProc;
DELIMITER $$
CREATE PROCEDURE gradeProc()
BEGIN
DECLARE id VARCHAR(10);
DECLARE hap BIGINT;
DECLARE userGrade CHAR(5);
DECLARE endOfRow BOOLEAN DEFAULT FALSE;
DECLARE userCursor CURSOR FOR
SELECT U.userid, sum(B.price*B.amount)
FROM buytbl B
RIGHT OUTER JOIN usertbl U
ON B.userid = U.userid
GROUP BY U.userid, U.name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE;
OPEN userCursor;
grade_loop:
LOOP
FETCH userCursor INTO id, hap;
IF endOfRow
THEN LEAVE grade_loop;
END IF;
CASE
WHEN (hap >= 1500) THEN SET userGrade = '최우수고객';
WHEN (hap >= 1000) THEN SET userGrade = '우수고객';
WHEN (hap >= 1) THEN SET userGrade = '일반고객';
ELSE SET userGrade = '유령고객';
END CASE;
UPDATE usertbl SET grade = userGrade WHERE userID = id;
END LOOP grade_loop;
CLOSE userCursor;
END $$
DELIMITER ;
CALL gradeProc();
SELECT * FROM usertbl;
트리거(Trigger)
트리거 개요
만약 누군가 A라는 테이블에 행을 고의 또는 실수로 삭제한다면, 삭제된 행의 내용을 복구하는 것도 어렵고, 누가 지웠는지 추척하는 것도 쉬운 일이 아니다. 이러한 경우에 A테이블에서 행이 삭제되는 순간에 삭제된 행의 내용, 시간, 삭제한 사용자 등을 B테이블에 기록해 놓는다면 이러한 문제점을 해결 할 수 있다.
트리거란?
신호를 주고받으면서 실행이 되게 만들 때, 트리거를 발생 시키면 실행이 된다는 의미로 사용
제약 조건과 더불어 데이터 무결성을 위해 사용
데이블에 DML문(INSERT, UPDATE, Delete 등) 이벤트가 발생될 때 작동
테이블에 부착되는 프로그램 코드
직접 실행 불가
테이블에 이벤트 일어나야 자동 실행
IN, OUT 매개 변수를 사용할 수 없음
MySQL은 VIEW에 트리거 부착 불가
트리거의 종류
AFTER 트리거
테이블에 INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 작동하는 트리거
해당 작업 후에 작동한다.
BEFORE 트리거
이벤트가 발생하기 전에 작동하는 트리거
실습
자동 삭제 트리거 부착
CREATE DATABASE IF NOT EXISTS testDB;
USE testDB;
CREATE TABLE IF NOT EXISTS testTbl (id INT, txt VARCHAR(10));
INSERT INTO testTbl VALUES (1, '레드벨벳');
INSERT INTO testTbl VALUES (2, '잇지');
INSERT INTO testTbl VALUES (3, '블랙핑크');
DROP TRIGGER IF EXISTS testTrg;
DELIMITER //
CREATE TRIGGER testTrg
AFTER DELETE
ON testtbl
FOR EACH ROW
BEGIN
SET @msg = '가수그룹이 삭제됨'
END //
DELIMITER ;
SET @msg = '';
INSERT INTO testTbl VALUES (4, '마마무');
DELETE FROM testTbl WHERE id = 4;
SELECT @msg;
삭제 데이터 별도 보관 트리거
TRIGGER는 DELETE에만 작동하고 TRUNCATE에는 작동하지 않는다.
-- UPDATE TRIGGER
CREATE TABLE backup_usertbl -- 회원 테이블
( userID CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
name VARCHAR(10) NOT NULL, -- 이름
birthYear INT NOT NULL, -- 출생년도
addr CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
mobile1 CHAR(3), -- 휴대폰의 국번(011, 016, 017, 018, 019, 010 등)
mobile2 CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈제외)
height SMALLINT, -- 키
mDate DATE,
modType CHAR(2),
modDate DATE,
modUser VARCHAR(256)
);
DROP TRIGGER IF EXISTS backUserTbl_UpdateTrg;
DELIMITER //
CREATE TRIGGER backUserTbl_UpdateTrg
AFTER UPDATE
ON usertbl
FOR EACH ROW
BEGIN
INSERT INTO backup_usertbl
VALUES(
OLD.userID,
OLD.name ,
OLD.birthYear,
OLD.addr,
OLD.mobile1 ,
OLD.mobile2 ,
OLD.height ,
OLD.mDate ,
'수정',
CURDATE(),
CURRENT_USER()
);
END //
DELIMITER ;
UPDATE usertbl SET addr = '몽고' WHERE userID = 'JKW';
SELECT * FROM backup_usertbl;
-- DELETE TRIGGER
DROP TRIGGER IF EXISTS backUserTbl_DeleteTrg;
DELIMITER //
CREATE TRIGGER backUserTbl_DeleteTrg
AFTER DELETE
ON usertbl
FOR EACH ROW
BEGIN
INSERT INTO backup_usertbl
VALUES(
OLD.userID,
OLD.name ,
OLD.birthYear,
OLD.addr,
OLD.mobile1 ,
OLD.mobile2 ,
OLD.height ,
OLD.mDate ,
'삭제',
CURDATE(),
CURRENT_USER()
);
END //
DELIMITER ;
DELETE FROM usertbl WHERE height >= 177;
SELECT * FROM backup_usertbl;
INSERT
DROP TRIGGER IF EXISTS userTbl_InsertTrg;
DELIMITER //
CREATE TRIGGER userTbl_InsertTrg
AFTER INSERT
ON usertbl
FOR EACH ROW
BEGIN
SIGNAl SQLSTATE '45000' SET MESSAGE_TEXT = '데이터 입력시도. 귀하의 정보가 서버에 기록됨.';
END //
DELIMITER ;
INSERT INTO usertbl VALUES('ABC', '에비씨', 1977, '서울','011','11111111', 181, '2019-12-25', NULL);
Before TRIGGER
DROP TRIGGER IF EXISTS userTbl_BeforeInsertTrg;
DELIMITER //
CREATE TRIGGER userTbl_BeforeInsertTrg
BEFORE INSERT
ON usertbl
FOR EACH ROW
BEGIN
IF NEW.birthYear < 1900 THEN SET NEW.birthYear = 0;
ELSEIF NEW.birthYear > Year(CURDATE())
THEN SET NEW.birthYear = Year(CURDATE());
END IF;
END //
DELIMITER ;
INSERT INTO usertbl VALUES('ABC', '에비씨', 1977, '서울','011','11111111', 181, '2019-12-25', NULL);
INSERT INTO usertbl VALUES('AAA', '에비씨', 1888, '서울','011','11111111', 181, '2019-12-25', NULL);
INSERT INTO usertbl VALUES('BBB', '에비씨', 2060, '서울','011','11111111', 181, '2019-12-25', NULL);
SELECT * FROM usertbl;
