-- 임직원의 id를 1로 시작하는 10자리 정수로 랜덤하게 발급하는 함수
delimiter $$ -- delimiter(구문을 끝내는 표현)을 '$$'로 변경
CREATE FUNCTION id_generator()
RETURNS int -- 반환할 타입
BEGIN
-- delimiter를 바꾸지 않을시 여기서 앞선 구문들이 실행
RETURN(1000000000 + floor(rand() * 1000000000));
END
$$
delimiter ; -- delimiter 원상복원
-- id_generator를 사용해 임직원 데이터 추가
INSERT INTO employee
VALUES(id_generator(), 'JEHN', '1991-08-04', 'F', 'PO', 100000000, 1005);
-- 부서의 ID를 파라미터로 받아 해당 부서의 평균 연봉을 알려주는 함수
delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
BEGIN
DECLARE avg_sal int;
SELECT avg(salary) into avg_sal from employee WHERE dept_id = d_id;
return sav_sal;
END
$$
delimiter ;
-- dept_avg_salary를 통해 각 부서별 평균 연봉 구하기
SELECT *, dept_avg_salary(id) FROM department;
-- 토익 점수가 800이상인지 확인하는 함수
delimiter $$
-- 함수이름 앞에 함수를 저장할 DB를 명시해줄 수 있음
CREATE FUNCTION school.toeic_pass_fail(toeic_score int)
RETURNS char(4)
BEGIN
DECLARE pass_fail char(4);
IF toeic_score is NULL THEN SET pass_fail = 'fail';
ELSEIF toeic_score < 800 THEN SET pass_fail = 'fail';
ELSE SET pass_fail = 'pass';
END IF;
RETURN pass_fail;
END
$$
delimiter ;
- 학생이 토익점수 조건을 충족했는지를 같이 출력하기
SELECT *, toeic_pass_fail(toeic) FROM student;
DROP FUNCTION stored_function_name;
구문 사용SHOW FUNCTION STATUS
구문 사용SHOW FUNCTION STATUS WHERE DB = 'company';
-- 실제 함수 내용을 확인하고 싶을떄는 SHOW CREATE FUNTION 사용
SHOW CREATE FUNCTION STATUS id_generator;
- 두 정수의 곱셈 결과를 가져오는 프로시저
delimiter $$
CREATE PROCEDURE product(IN a int, IN b int, OUT result int)
BEGIN
SET result = a * b;
END
$$
delimiter ;
CALL product(5, 7, @result); -- @기호를 통해 직접 변수선언가능
SELECT @result -- 테이블에 35 출력
- 두 정수를 맞바꾸는 가져오는 프로시저
delimiter $$
CREATE PROCEDURE product(INOUT a int, INOUT b int)
BEGIN
SET @temp = a;
SET a = b;
SET b = @temp;
END
$$
delimiter ;
SET @a = 5, @b = 7;
CALL swap(@a, @b);
SELECT @a, @b;
-- 각 부서별 평균 연봉을 가져오는 프로시저
delimiter $$
CREATE PROCEDURE get_dept_avg_salary()
BEGIN
SELECT dept_id, avg(salary) FROM employee GROUP BY dept_id;
END
$$
delimiter ;
CALL get_dept_avg_salary();
-- 사용자가 닉네임을 변경하면 기존 닉네임을 로그에 저장하고 새로운 닉네임으로 변경하는 프로시저
delimiter $$
-- IN, OUT, INOUT을 명시하지 않을시 기본값은 IN
CREATE PROCEDURE change_nickname(user_id INT, new_nick varchar(30))
BEGIN
INSERT INTO nickname_logs(
SELECT id, nickname, now() from users WHERE id = user_id
);
UPDATE users SET nickname = new_nickname where id = user_id;
END
$$
delimiter ;
CALL change_nickname(1, 'ZIDANE');
특징 | Stored Procedure | Stored Function |
---|---|---|
CREATE 문법 | CREATE PROCEDURE | CREATE FUNCTION |
RETURN 키워드로 값 반환 | 불가능 | 가능 |
파라미터로 값 가능 | 가능 | postgreSQL, oracle(권장x) 가능 |
값의 필수 반환 여부 | 필수 아님 | 필수 |
SQL문에서 호출 | 불가능 | 가능 |
Transaction 사용 | 가능 | 불가능(oracle은 가능) |
주된 사용 목적 | 비지니스 로직 처리 | computation |
-- 사용자의 닉네임 변경 이력을 제작하는 트리거
delimiter $$
CREATE TRIGGER log_user_nickname_trigger
BEFORE UPDATE
ON users FOR EACH ROW
BEGIN
INSERT INTO users_log VALUES(OLD.id, OLD.nickname, now());
END
$$
delimiter ;
- 사용자가 마트에서 구매할때마다 누적 구매액을 구하는 트리거
delimeter $$
CREATE TRIGGER sum_buy_prices_trigger
AFTER INSERT
ON buy FOR EACH ROW
BEGIN
DECLARE total INT;
DECLARE user_id INT DEFAULT NEW.user_id;
SELECT SUM(price) INTO total FROM buy WHERE user_id = user_id;
UPDATE user_buy_stats SER price_sum = total WHERE user_id = user_id;
END
$$
delimiter ;
CREATE TRIGGER avg_empl_salary_triger
AFTER INSERT OR UPDATE OR DELETE
ON employee FOR EACH STATEMENT -- PostgreSQL에서는 statement단위로 트리거 실행 가능
EXECUTE FUNCTION update_avg_empl_salary();
CREATE TRIGGER log_user_nickname_trigger
BEFORE UPDATE
ON users FOR EACH ROW
WHEN (NEW.nickname IS DISTINCT FROM(OLD.nickname)
EXECUTE FUNCTION log_user_nickname();
출처:
https://www.youtube.com/watch?v=I1jjR58Rzic&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=10
https://www.youtube.com/watch?v=I1jjR58Rzic&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=11
https://www.youtube.com/watch?v=I1jjR58Rzic&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=12
https://www.youtube.com/watch?v=mEeGf4ZWQKI&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=13