1. 스토어드 프로시저
- 스토어드 프로시저는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 내는 것임.
- 일반 프로그래밍에서 함수를 구현하여 사용하는 것과 비슷함.
1) 생성
<입력 매개변수가 없을 경우>
DELIMITER $$
CREATE PROCEDURE stored_procedure_name ()
BEGIN
-- 여기에 stored_procedure_name에서 실행할 내용을 입력합니다.
END$$
DELIMITER ;<입력 매개변수가 있을 경우>
DELIMITER $$
CREATE PROCEDURE stored_procedure_name (IN data_name data_type)
BEGIN
-- 여기에 stored_procedure_name에서 실행할 내용을 입력합니다.
END$$
DELIMITER ;<출력 매개변수가 있을 경우>
DELIMITER $$
CREATE PROCEDURE stored_procedure_name (OUT data_name data_type)
BEGIN
-- 여기에 stored_procedure_name에서 실행할 내용을 입력합니다.
-- data_name에 저장할 값을 여기에서 설정해줍니다.
END$$
DELIMITER ;
2) 삭제
DROP PROCEDURE stored_procedure_name();
2. 스토어드 함수
- MySQL의 내장 함수 외에 직접 함수를 만드는 것,
- 스토어드 프로시저와 다르게 RETURNS 예약어를 통해 하나의 값을 반환해야 함.
1) 생성
DELIMITER $$
CREATE FUNCTION stored_function_name(var1 INT) -- 변수와 입력 데이터 타입을 지정
RETURNS INT -- 리턴 데이터 타입
BEGIN
-- 이 부분에 코딩
RETURN var1+10; -- 리턴값
END $$
DELIMITER ;2) 삭제
DROP FUNCTION stored_function_name;3) 커서
- 테이블에서 한 행씩 처리하기 위한 방법
- 첫 행부터 마지막 행까지 겁근해서 값을 처리
- 커서 선언 → 반복 조건 선언 → 커서 열기 → 데이터 가져오기 → 데이터 처리하기 → 커서 닫기 순으로 작동
<커서 선언>
DECLARE cursor1 CURSOR FOR
SELECT column1 FROM table1;cursor1이 table1의 column1 열을 순회하면서 데이터에 접근함.
<반복 조건 선언>
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET endOfRow=TRUE;- DECLARE CONTINUE HANDLER - 반복 조건을 준비하는 예약어
- FOR NOT FOUND - 더 이상 행이 없을 때 이어진 문장을 수행 (endOfRow=TRUE;)
<커서 열기>
OPEN cursor1;<데이터 가져오기 & 데이터 처리하기>
cursor_loop : LOOP
FETCH cursor1 INTO var;
IF endOfRow THEN
LEAVE cursor_loop;
END IF;
-- 실행할 내용을 입력합니다.
END LOOP cursor_loop;- cursor_loop은 반복 구간에 대해 임의로 정한 이름임.
- FETCH - 한 행씩 읽어옴. cursor에서 column1 행을 조회했으므로 var에 column1의 값이 순서대로 저장됨.
- cursor_loop : LOOP & END LOOP cursor_loop - 해당 예약어 사이에 있는 코드를 반복함.
- LEAVE cursor_loop - 해당 반복 구간을 종료하고 빠져나감.
<커서 닫기>
CLOSE cursor1;
3. AFTER 트리거
- 트리거는 자동 수행을 통해 사용자가 추가 작업을 잊어버리는 실수를 방지함.
- 예를 들어 한 테이블에서 데이터를 삭제하기 전 다른 테이블에 해당 데이터를 입력하는 작업을 트리거 하여 수동으로 백업하지 않아도 됨.
- 테이블에 INSERT, UPDATE, DELETE 등의의 이벤트가 발생할 때 작동함.
1) 생성
DELIMITER $$
CREATE TRIGGER trigger_name
AFTER DELETE
ON table1
FOR EACH ROW
BEGIN
INSERT INTO backup_table1 VALUES
(OLD.column1, OLD.column2, OLD.column3);
END $$
DELIMITER;- AFTER DELETE - DELETE 이벤트가 발생했을때 트리거 작동
- ON table1 FOR EACH ROW - table1의 각 행에 대하여 적용
- INSERT INTO ~ VALUES - backup_table1에 table1의 column1, column2, column3을 INSERT
- OLD 테이블은 UPDATE나 DELETE가 수행될 때 변경 전 데이터가 잠깐 저장되는 임시 테이블임.
'SQL' 카테고리의 다른 글
| 4. SQL 고급 문법 - 인덱스 (0) | 2022.07.23 |
|---|---|
| 3. SQL 고급 문법 - 제약조건, 뷰 (0) | 2022.07.20 |
| 2. SQL 고급 문법 - SQL 데이터 형식, JOIN (0) | 2022.07.19 |
| 1. SQL 기본 문법 - SELECT, CREATE,INSERT,ORDER BY,GROUP BY,UPDATE, DELETE (0) | 2022.07.17 |
