소스: sql-src/work_08_procedure/01_body_logic.sql, 02_function_logic.sql, 03_error_case.sql. 사원 표 emp(id, name, dept, sal)는 8행이고 sal >= 0 CHECK 제약이 있습니다. sal_log(emp_id, old_sal, new_sal)는 급여 변경 로그로 처음에는 비어 있습니다.
데이터는 김대표 경영 900, 이개발·박개발·최개발 개발 500·450·380, 정영업·한영업·오영업 영업 420·350·300, 윤인사 인사 400 입니다.
raise_sal(p_dept, p_rate, p_cnt) 는 부서와 인상률(%)을 받아 급여를 올리고 대상 건수를 p_cnt 로 돌려줍니다. 본문은 세 문장과 결과 조회입니다. 아래는 개발팀 10% 인상을 평문 SQL 로 실행한 것입니다.
SELECT COUNT(*) AS p_cnt FROM emp WHERE dept = '개발';P_CNT
-----
3
(1행)대상은 3명입니다. 로그는 UPDATE 전에 남겨야 옛 급여가 보존됩니다.
INSERT INTO sal_log
SELECT
id
, sal
, sal + sal * 10 / 100
FROM emp
WHERE dept = '개발';
UPDATE emp SET sal = sal + sal * 10 / 100 WHERE dept = '개발';각각 3행이 처리됩니다. 로그를 조회합니다.
SELECT * FROM sal_log ORDER BY emp_id;EMP_ID | OLD_SAL | NEW_SAL
-------+---------+--------
2 | 500 | 550
3 | 450 | 495
4 | 380 | 418
(3행)emp 표에서는 이개발 550, 박개발 495, 최개발 418 로 바뀌고 나머지는 그대로입니다(소스에서 조회). 프로시저의 일은 결국 이 문장들의 묶음입니다.
-- Oracle · Tibero
CREATE OR REPLACE PROCEDURE raise_sal (
p_dept IN VARCHAR2
, p_rate IN NUMBER
, p_cnt OUT NUMBER
) IS
BEGIN
INSERT INTO sal_log
SELECT id, sal, sal + sal * p_rate / 100
FROM emp
WHERE dept = p_dept;
UPDATE emp
SET sal = sal + sal * p_rate / 100
WHERE dept = p_dept;
p_cnt := SQL%ROWCOUNT;
END raise_sal;
/파라미터에는 방향(IN, OUT)을 쓰고 자료형에는 길이를 쓰지 않습니다. SQL%ROWCOUNT 는 직전 DML 이 처리한 행 수이므로 UPDATE 바로 뒤에서 읽습니다. 마지막 / 는 SQL*Plus 에서 블록 정의를 실행하라는 표시입니다.
호출은 SQL*Plus 의 EXEC raise_sal('개발', 10, :v_cnt) 이나 BEGIN raise_sal('개발', 10, v_cnt); END; 익명 블록으로 합니다. 결과 p_cnt 는 3 이 됩니다(예제 1 의 대상 건수).
MySQL 클라이언트는 ; 를 만나면 문장이 끝났다고 봅니다. 프로시저 본문에도 ; 가 있어서 정의하는 동안 구분자를 바꿔야 합니다.
-- MySQL
DROP PROCEDURE IF EXISTS raise_sal;
DELIMITER //
CREATE PROCEDURE raise_sal (
IN p_dept VARCHAR(10)
, IN p_rate INT
, OUT p_cnt INT
)
BEGIN
INSERT INTO sal_log
SELECT id, sal, sal + sal * p_rate / 100
FROM emp
WHERE dept = p_dept;
UPDATE emp
SET sal = sal + sal * p_rate / 100
WHERE dept = p_dept;
SET p_cnt = ROW_COUNT();
END //
DELIMITER ;DELIMITER 는 서버 문법이 아니라 mysql 클라이언트 명령입니다. 애플리케이션에서 JDBC 로 정의할 때는 쓰지 않습니다. OUT 값은 세션 변수로 받습니다.
-- MySQL
CALL raise_sal('개발', 10, @cnt);
SELECT @cnt;도식(H2 미지원, 실행 결과 아님)으로 @cnt 는 3 입니다.
-- MSSQL
CREATE OR ALTER PROCEDURE raise_sal
@p_dept VARCHAR(10)
, @p_rate INT
, @p_cnt INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO sal_log
SELECT id, sal, sal + sal * @p_rate / 100
FROM emp
WHERE dept = @p_dept;
UPDATE emp
SET sal = sal + sal * @p_rate / 100
WHERE dept = @p_dept;
SET @p_cnt = @@ROWCOUNT;
END;SET NOCOUNT ON 은 "N행 영향 받음" 메시지를 끄는 관용구입니다. 메시지가 많으면 클라이언트가 결과 처리에 시간을 쓰기 때문입니다. 파라미터에는 @ 를 붙이고 출력은 뒤에 OUTPUT 을 씁니다.
-- MSSQL
DECLARE @cnt INT;
EXEC raise_sal '개발', 10, @cnt OUTPUT;
SELECT @cnt AS cnt;도식(H2 미지원, 실행 결과 아님)으로 cnt 는 3 입니다. 호출에도 OUTPUT 을 붙여야 값이 돌아옵니다. 빠뜨리면 @cnt 는 NULL 로 남습니다.