SQL 기초 - 16. 저장 프로시저, 인수, 함수(+테이블 반환 함수)

이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.

 

SQL 기초 - 16. 저장 프로시저, 인수, 함수

SQL 명령 열개를 모으면 코드가 되며, 이를 텍스트로 저장하면 스크리트, 서버에 저장하면 저장 프로시져가 된다.

형식은 다음과 같다.

CREATE [OR REPLACE] PROCEDURE 이름(인수 목록)
AS 또는 IS
    변수선언
BEGIN
    본체
END [이름];

모든 DB오브젝트는 고유한 이름을 가져야 한다. 또한 프로시져를 Ctrl + Enter로 실행할 때는 코드 블락을 선택해서 실행하는 것이 안전하다. 추후 수정을 용이하게 하기 위해 OR REPLACE를 붙여 주는 것이 좋다.

프로시저 정의문은 항상 단독으로 실행하는 게 좋으며, 전역적으로 공유한다. 따라서 다른 DB에서 호출할 수도 있으므로 오브젝트의 소속을 밝혀야 한다. (tCity보다는 SYSTEM.tCity로 소속을 밝히는 것이 안전함)

프로시저는 모든 SQL문을 다 사용할 수 있지만 DDL은 주의해서 사용해야 한다. CREATE 명령의 경우, 프로시저 안에서 또 다른 프로시저를 만드는 것 등이 이에 해당한다.

단순 쿼리를 이용하는 것에 비해, 프로시저는 빠르다. 다음의 쿼리의 진행 과정이다.

 

또한 프로시저는 모듈화에 유리하다. 이는 일관성과 재사용성이 높다는 것이다.

-- CREATE PROCEDURE를 통해 새 프로시저를 생성한다.
CREATE PROCEDURE SP_GetPopu
AS
    v_popu INT;
BEGIN
    SELECT popu INTO v_popu FROM tCity WHERE name = '서울';
    DBMS_OUTPUT.PUT_LINE(v_popu);
END;

-- 실행시에는 EXECUTE를 붙이는데, 줄여서 EXEC로 표현할 수 있다.
EXECUTE SP_GetPopu();

SELECT * FROM user_objects WHERE object_type = 'PROCEDURE';

SELECT * FROM user_source WHERE name = 'SP_GETPOPU';

CREATE PROCEDURE SP_GetPopu
...
    SELECT popu INTO v_popu FROM tCity WHERE name = '부산';

-- 프로시저 삭제 시는 DROP을 이용한다.
DROP PROCEDURE SP_GetPopu;

CREATE OR REPLACE PROCEDURE SP_GetPopu
AS
    v_popu INT;
BEGIN
    SELECT popu INTO v_popu FROM tCity WHERE name = '부산';
    DBMS_OUTPUT.PUT_LINE(v_popu);
END;

CREATE OR REPLACE PROCEDURE SP_GetPopu
AS
    v_popu INT;
BEGIN
    SELECT population INTO v_popu FROM tCity WHERE name = '부산';
    DBMS_OUTPUT.PUT_LINE(v_popu);
END;

DROP TABLE tCity;

-- 프로시저는 (인자 IN 타입)을 통해 인풋을 받을 수 있다.
CREATE OR REPLACE PROCEDURE SP_GetCityPopu(p_name IN CHAR)
AS
    v_popu INT;
BEGIN
    SELECT popu INTO v_popu FROM tCity WHERE name = p_name;
    DBMS_OUTPUT.PUT_LINE(p_name || '의 인구는 ' || v_popu || '만명입니다.');
END;

EXEC SP_GetCityPopu('오산');		-- 오산의 인구는 21만명입니다.
EXEC SP_GetCityPopu('청주');		-- 청주의 인구는 83만명입니다.

EXEC SP_GetCityPopu();      		-- 에러
EXEC SP_GetCityPopu('서울', '부산');    	-- 에러

-- 타입 지정 및 DEFAULT 값 지정이 가능하다.
CREATE OR REPLACE PROCEDURE SP_GetCityPopu(p_name IN tCity.name%TYPE := '서울')
....

CREATE OR REPLACE PROCEDURE SP_GrantBonus(p_member CHAR, p_bonus INT)
AS
BEGIN
    UPDATE tMember SET money = money + p_bonus WHERE member = p_member;
    COMMIT;
END;

EXEC SP_GrantBonus('춘향', 123);

-- 순서에 상관없이 사용하고 싶을 때는 인자 명을 지정할 수 있다.
EXEC SP_GrantBonus(p_member=>'춘향', p_bonus=>123);
EXEC SP_GrantBonus(p_bonus=>123, p_member=>'춘향');

-- IN과 마찬가지로, OUT을 통해 출력 값을 설정할 수 있다.
CREATE OR REPLACE PROCEDURE SP_OutCityPopu(p_name IN CHAR, o_popu OUT INT)
AS
BEGIN
    SELECT popu INTO o_popu FROM tCity WHERE name = p_name;
END;

DECLARE 
    v_popu INT;
BEGIN
    SP_OutCityPopu('서울', v_popu);
    DBMS_OUTPUT.PUT_LINE(v_popu);
END; 

CREATE OR REPLACE PROCEDURE SP_OutCityAreaPopu(p_name IN CHAR, o_area OUT INT, o_popu OUT INT)
AS
BEGIN
    SELECT area, popu INTO o_area, o_popu FROM tCity WHERE name = p_name;
END;

DECLARE 
    v_area INT;
    v_popu INT;
BEGIN
    SP_OutCityAreaPopu('부산', v_area, v_popu);
    DBMS_OUTPUT.PUT_LINE(v_area || ' ,' || v_popu);
END;

CREATE OR REPLACE PROCEDURE SP_GetCityPopu(p_name IN tCity.name%TYPE := '서울')
AS
    v_popu INT;
BEGIN
    SP_OutCityPopu(p_name, v_popu);
    DBMS_OUTPUT.PUT_LINE(p_name || '의 인구는 ' || v_popu || '만명입니다.');
END;

EXECUTE SP_GetCityPopu('평양');

-- 복잡한 프로시저를 설정할 때는 적절한 예외처리가 도움이 된다.
CREATE OR REPLACE PROCEDURE SP_GetCityPopu(p_name IN tCity.name%TYPE := '서울')
AS
    v_popu INT;
BEGIN
    SELECT popu INTO v_popu FROM tCity WHERE name = p_name;
    DBMS_OUTPUT.PUT_LINE(p_name || '의 인구는 ' || v_popu || '만명입니다.');
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('없는 도시입니다.');
        WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE('도시가 너무 많습니다.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('알 수 없는 예외입니다.');
END;

SELECT popu INTO v_popu FROM tCity WHERE name = p_name;
IF v_popu IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('없는 도시입니다.');
END IF;

-- 간단한 함수를 사용하는 예제이다. 함수는 리턴값이 항상 1개이다.
CREATE OR REPLACE FUNCTION FN_AddInt(a INT, b INT) 
RETURN INT
AS
BEGIN
	RETURN a + b;
END;

SELECT FN_AddInt(2, 3) FROM dual;
SELECT * FROM tCity WHERE popu > FN_AddInt(10, 20);

DECLARE v_sum INT;
BEGIN
    v_sum := FN_AddInt(2, 3);
    DBMS_OUTPUT.PUT_LINE(v_sum);
END;

CREATE OR REPLACE FUNCTION FN_GetSum(p_upBound INT)
RETURN INT
AS
    v_total INT := 0;
BEGIN
    FOR v_num IN 1 .. p_upBound
    LOOP
        v_total := v_total + v_num;
    END LOOP;
    RETURN v_total;
END;

SELECT FN_GetSum(10) FROM dual;

-- 테이블 반환 함수 : 테이블을 생성하여 리턴한다(열과 행으로 구성한 대규모의 정보 리턴 가능)
CREATE OR REPLACE TYPE gu_row AS OBJECT
(
    dan INT, 
    num INT, 
    multi INT
);

-- 다음 구구단을 만드는 예제를 통해 실습해보자.
CREATE OR REPLACE TYPE gu_table AS TABLE OF gu_row;

CREATE OR REPLACE FUNCTION makeDan(p_dan INT)
RETURN gu_table PIPELINED
AS
    v_row gu_row;
BEGIN
    FOR v_num IN 1..9
    LOOP
        v_row := gu_row(p_dan, v_num, p_dan * v_num);
        PIPE ROW(v_row);
    END LOOP;
    RETURN;
END;

SELECT * FROM TABLE(makeDan(5));
SELECT * FROM TABLE(makeDan(7));


-- 아래는 SQL SERVER의 프로시저에 대한 코드이다.
CREATE PROCEDURE PROC_GetCityPopu
	@p_name CHAR(10)
AS
BEGIN
	SELECT popu FROM tCity WHERE name = @p_name;
END

EXEC PROC_GetCityPopu @p_name = '서울';
EXEC PROC_GetCityPopu '서울';
PROC_GetCityPopu '서울';

ALTER PROCEDURE PROC_GetCityPopu
	@p_name CHAR(10)
AS
DECLARE
	@popu INT
BEGIN
	SELECT @popu = popu FROM tCity WHERE name = @p_name;
	PRINT @popu
END

ALTER PROCEDURE PROC_GetCityPopu
	@p_name CHAR(10)
AS
BEGIN
	SELECT popu FROM tCity2 WHERE name = @p_name;
END

CREATE PROCEDURE PROC_GrantBonus
	@p_member CHAR(20),
	@p_bonus INT = 100
AS
BEGIN
    UPDATE tMember SET money = money + @p_bonus WHERE member = @p_member;
END;

PROC_GrantBonus '춘향', 2000;
PROC_GrantBonus '춘향';

PROC_GrantBonus @p_member = '춘향', @p_bonus = 2000;
PROC_GrantBonus @p_bonus = 2000, @p_member = '춘향';

CREATE PROCEDURE PROC_OutCityPopu
	@p_name CHAR(10), 
	@o_popu INT OUTPUT
AS
BEGIN
    SELECT @o_popu = popu FROM tCity WHERE name = @p_name;
END;

DECLARE @popu INT;
EXECUTE PROC_OutCityPopu '서울', @popu OUTPUT;
PRINT '서울의 인구는 ' + CAST(@popu AS VARCHAR(10)) + '만명입니다.';

CREATE PROCEDURE PROC_RetCityPopu
	@p_name CHAR(10)
AS
DECLARE
	@popu INT;
BEGIN
    SELECT @popu = popu FROM tCity WHERE name = @p_name;
    RETURN @popu;
END;

DECLARE @popu INT;
EXECUTE @popu = PROC_RetCityPopu '서울';
PRINT '서울의 인구는 ' + CAST(@popu AS VARCHAR(10)) + '만명입니다.';

PRINT '서울의 인구는 ' + CAST(PROC_RetCityPopu '서울' AS VARCHAR(10)) + '만명입니다';
SELECT PROC_RetCityPopu '서울';

CREATE PROCEDURE PROC_InsertSeoul
AS
BEGIN
	INSERT INTO tCity VALUES ('서울',605,974,'y','경기');
END

ALTER PROCEDURE PROC_InsertSeoul
AS
BEGIN
	INSERT INTO tCity VALUES ('서울',605,974,'y','경기');
	IF @@ERROR != 0
	BEGIN
		PRINT('새 레코드를 삽입하지 못했습니다.');
	END
END

ALTER PROCEDURE PROC_InsertSeoul
AS
BEGIN
	BEGIN TRY
		INSERT INTO tCity VALUES ('서울',605,974,'y','경기');
	END TRY

	BEGIN CATCH
		PRINT '에러 번호 : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
		PRINT '에러 메시지 : ' + ERROR_MESSAGE();
	END CATCH
END

CREATE FUNCTION FN_AddInt(@a INT, @b INT) RETURNS INT
AS
BEGIN
	RETURN @a + @b;
END

SELECT Study.dbo.FN_AddInt(2, 3);

CREATE FUNCTION FN_GetCity(@region CHAR(10))
RETURNS TABLE
AS
RETURN SELECT * FROM tCity WHERE region = @region;

SELECT * FROM dbo.FN_GetCity('강원');

SELECT * FROM dbo.FN_GetCity('강원') WHERE popu > 10;

CREATE FUNCTION FN_GetCityTable(@region CHAR(10))
RETURNS @result TABLE
	(name VARCHAR(10),popu INT)
AS
BEGIN
	INSERT INTO @result SELECT name, popu FROM tCity WHERE region = @region;
	RETURN;
END

SELECT * FROM dbo.FN_GetCityTable('경기');