프로시저, 트리거, 사용자 정의 함수

프로시저

프로시저는 일반 프로그래밍 언어에서 사용하는 함수와 비슷한 개념으로, 작업 순서가 정해진 독립된 수행 단위이다. 

프로시저의 정의 방법

① 변수와 매개변수는 @ 기호로 식별한다.
② 매개변수는 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값이다.
③ 변수는 저장 프로시저나 트리거 내에서 사용되는 값이다.
④ 주석은 /*  */ 그리고 -- 로 기술한다.

USE tempdb            <-- tempdb 데이터베이스에 프로시저를 정의하기 위한 명령어
DROP PROCEDURE tempPro    <-- 기존에 정의된 프로시저가 있을 경우 삭제하는 명령어
GO

-- tempPro 프로시저 정의

CREATE PROCEDURE tempPro(@ID INT, @NAME VARCHAR(20), Price INT) AS
BEGIN
    INSERT INTO tempTable(id, name, price) VALUES(@ID, @NAME, @Price);
END
GO

-- 프로시저의 사용
EXEC tempPro 13, "조니뎁", 25000

T-SQL의 제어문

구문	의미	문법
BEGIN-END	T-SQL 문을 블록화시킴 중첩 가능	BEGIN { SQL 문 } END
IF - ELSE	조건문	IF <조건> SQL 문  SELECT 문을 포함할 경우 반드시 ( ) 로 묶어야 한다.
WHILE	반복문	WHILE <조건> SQL문 | BREAK | CONTINUE
RETURN	프로시저를 종료 상태값을 정수로 반환 가능	RETURN <정수>

프로시저 예제

입력한 범위 내에서 합을 구해 출력

CREATE PROCEDURE SUM(@startNUM int, @endNUM int @result int OUTPUT) AS   
DECLARE @i int
SET @i = @startNUM
SET @result = 0
WHILE ( @i <= @endNUM )
BEGIN
    SET @result = @result + @i
    SET @i = @i + 1
END
RETURN 0                         
-- SELECT @result AS
DECLARE @i int
EXEC SUM 1, 100 , @i OUTPUT
SELECT @i

OUTPUT - 반환이 필요한 변수에 이 키워드를 사용
SET - 변수 값 지정
DECLARE - 변수 선언
RETURN - 종료 , 반환(숫자만 가능)


트리거

데이터의 변경(INSERT, DELETE, UPDATE)문이 실행될 때 자동으로 따라서 실행되는 프로시저 이다.

USE tempdb
DROP TRIGGER Aftertemp
GO

CREATE TRIGGER Aftertemp ON tempTable AFTER INSERT AS
BEGIN
    DECLARE @mid AS INT
    DECLARE @mprice AS INT
    DECLARE @mpublisher AS VARCHAR(40)
    DECLARE @average AS INT
    SELECT @mid=bookid, @mprice =price, @mpublisher=publisher FROM Inserted;
    SET @average = (SELECT avg(price) FROM tempTable
                    WHERE publisher LIKE @mpublisher);
    IF (@mprice = 0 )
    BEGIN
        .
        .

END

END

INSERT문이 실행될 때 사입된 새로운 값은 Inserted 라는 가상 테이블에 저장된다. 
위 문장은 SELECT를 이용해 이 값을 읽고 있다.

사용자 정의 함수

SELECT 문에 의해 호출되어 프로시저나 SQL 문에 그 값을 제공하는 용도로 사용한다.

USE tempdb
DROP FUNCTION fnc_temp
Go

CREATE FUNCTION fnc_temp ( @price INT)
RETURNS INT                        <-- RETURNS는 결과를 INT형으로 반환한다는 뜻
AS
BEGIN
    DECLARE @mInterest INT
    IF @price >= 10000
        SET @mInterest = @price * 0.1;
    ELSE SET @mInterest = @price * 0.05;
    RETURN @mInterest
END
GO

SELECT id,oid,saleprice, dbo.fnc_temp(saleprice) interest FROM  temptable;

dbo는 기본 스키마로 사용자 정의 함수를 수행하기 위해서는 객체를 소유하고 있는 스키마를 반드시 표시해야 한다.