CREATE [OR REPLACE] Procedure [Procedure_name] 
( argument1 [mode] data_type1, 
argument2 [mode] date_type2, 
... ... ) 
IS [AS] 
... ... 
BEGIN 
... ... 
EXCEPTION 
... ... 
END; 
/

-- CREATE : 프로시저 생성. DB내 저장되며 필요시 호출하여 실행 할 수 있다.
--[OR REPLACE] : DB내 같은 이름의 프로시저가 있을 경우, 
--          기존의 프로시저를 무시하고 새로운 내용으로 덮어쓰겠다는 의미.
--argument : 프로시저 호출시 들어올 값, 
--          혹은 프로시저에서 처리한 결과값을 운영 체제로 리턴시킬 매개변수를 지정할 때 사용
--[mode] : 매개변수 지정. 3가지 유형이 있다. 
--          1. IN : 운영 체제에서 프로시저로 전될될 변수
--          2. OUT : 프로시저에서 처리된 결과. 운영체제로 전달
--          3. INOUT : IN과 OUT 두 가지의 기능을 동시에 수행
-- / : 마지막 슬래쉬는 DB에 프로시저를 컴파일 하라는 명령어이다.

--생성된 프로시저 삭제 
DROP Procedure [Procedure_name];

CREATE Procedure [schema_name.]Procedure_name 
@parameter1 data_type1 [mode], 
@parameter2 date_type2 [mode], 
... ... 
WITH AS 
... ... 
BEGIN 
... ... 
ERROR 처리 
... ... 
END;

--[mode] 부분에 지정할 수 있는 매개변수는 4가지가 있다.
-- 1. VARYING : 결과 집합이 출력 매개 변수로 사용되도록 지정한 CURSOR매기변수에만 적용된다.
-- 2. DEFAULT : 프로시저 호출시 지정된 매개변수가 지정되지 않을 경우 기본값으로 처리한다.
-- 3. OUT / OUTPUT : 프로시저에서 처리된 결과 값을 EXECUTE문 호출 시 반환한다.
-- 4. READONLY : 프로시저 본문 내 매개 변수를 업데이트하거나 수정할 수 없다. 
--          (ex. 매개변수 유형이 사용자 정의 테이블 형식인 경우)

--WITH 부분에 지정할 수 있는 옵션은 3가지가 있다.
-- 1. RECOMPILE : 프로시저가 런타임에 컴파일된다. DB엔진에서 현재 프로시저의 계획을 캐시하지 않는다. 
--          저장 프로시저 안에 있는 개별 쿼리에 대한 계획을 삭제할 때 RECOMPILE 힌트를 사용한다.
-- 2. ENCRYPTION : CREATE PROCEDURE문의 원본 텍스트가 알아보기 어려운 형식으로 변환된다.
--          변조된 출력은 카탈로그 뷰 어디에도 직접 표시되지 않는다.
--          원본을 볼 수 있는 방법이 없기 때문에 반드시 원본 백업을 해두어야한다.
-- 3. EXECUTE AS : 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정한다.

--프로시저 삭제 명령어
DROP Procedure [schema_name.]Procedure_name;

--oracle
CREATE OR REPLACE Procedure p_DEPT_insert       -------------① 
    ( v_DEPTNO in number, 
    v_dname in varchar2, 
    v_loc in varchar2, 
    v_result out varchar2)
IS 
cnt number := 0; 
BEGIN 
    SELECT COUNT(*) INTO CNT                    -------------② 
    FROM DEPT 
    WHERE DEPTNO = v_DEPTNO 
        AND ROWNUM = 1; 
    if cnt > 0 then                             -------------③ 
        v_result := '이미 등록된 부서번호이다'; 
    else 
        INSERT INTO DEPT (DEPTNO, DNAME, LOC)   -------------④ 
        VALUES (v_DEPTNO, v_dname, v_loc); 
        COMMIT;                                 -------------⑤ 
        v_result := '입력 완료!!'; 
    end if; 
EXCEPTION                                       -------------⑥ 
    WHEN OTHERS THEN 
    ROLLBACK; 
    v_result := 'ERROR 발생'; 
END; /


--SQL Server 
CREATE Procedure dbo.p_DEPT_insert -------------① 
@v_DEPTNO int, 
@v_dname varchar(30), 
@v_loc varchar(30), 
@v_result varchar(100) OUTPUT 
AS 
DECLARE @cnt int 
SET @cnt = 0 
BEGIN 
    SELECT @cnt=COUNT(*) -------------② 
    FROM DEPT WHERE DEPTNO = @v_DEPTNO 
    IF @cnt > 0 -------------③ 
    BEGIN  
        SET @v_result = '이미 등록된 부서번호이다' 
        RETURN 
    END 
    ELSE 
    BEGIN 
        BEGIN TRAN 
        INSERT INTO DEPT (DEPTNO, DNAME, LOC) -------------④ 
        VALUES (@v_DEPTNO, @v_dname, @v_loc) 
        IF @@ERROR<>0 
            BEGIN ROLLBACK -------------⑥ 
            SET @v_result = 'ERROR 발생' 
            RETURN 
        END 
        ELSE 
        BEGIN 
            COMMIT -------------⑤ 
            SET @v_result = '입력 완료!!' 
            RETURN 
        END 
    END 
END


/*
① - 테이블에 들어갈 칼럼 값을 입력받는다.
② - 입력 받은 코드가 존재하는지 확인한다.
③ - 코드가 존재하면 '이미 등록된 부서번호이다' 라는 메시지를 출력값에 넣는다.
④ - 코드가 존재하지 않으면 입력받은 필드 값으로 새로운 레코드를 입력한다.
⑤ - 새 값이 정상적으로 입력되었을 경우 COMMIT명력어를 통해 트랜잭션을 종료한다.
⑥ - 에러 발생시 모든 트랜잭션을 취소하고 에러 메시지를 출력값에 넣는다.
*/

--oracle
CREATE OR REPLACE Function UTIL_ABS 
(v_input in number) ---------------- ① 
    return NUMBER 
IS 
    v_return number := 0; ---------------- ② 
BEGIN 
    if v_input < 0 then ---------------- ③ 
        v_return := v_input * -1; 
    else 
        v_return := v_input; 
    end if; 
    RETURN v_return; ---------------- ④ 
END; 
/


--SQL Server 
CREATE Function dbo.UTIL_ABS 
(@v_input int) ---------------- ① 
RETURNS int 
AS 
BEGIN 
    DECLARE @v_return int ---------------- ② 
    SET @v_return=0 
    IF @v_input < 0 ---------------- ③ 
        SET @v_return = @v_input * -1 
    ELSE 
        SET @v_return = @v_input 
    RETURN @v_return; ---------------- ④ 
END

/*
① - 값을 입력받는다.
② - 리턴 값을 받아 줄 변수인 v_return 을 선언한다.
③ - 입력 값이 음수이면 -1을 곱하여 v_return 변수에 대입한다.
④ - v_return변수를 리턴한다.
*/

--ex) 주문한 건이 입력될 때마다, 일자별 상품별로 판매수량과 판매금액을 집계하여 집계자료를 보관
--Oracle 
CREATE OR REPLACE Trigger SUMMARY_SALES ---------------- ① Trigger선언
AFTER INSERT  --레코드가 입력이 된 후 Trigger발생
ON ORDER_LIST   --ORDER_LIST 테이블에 Trigger설정
FOR EACH ROW    --각 row마다 Trigger적용
DECLARE ---------------- ②  : 값을 저장할 변수를 선언하고, 신규입력 데이터를 저장한다.
    o_date ORDER_LIST.order_date%TYPE; 
    o_prod ORDER_LIST.product%TYPE; 
BEGIN 
    o_date := :NEW.order_date;  -- NEW : 신규로 입력된 레코드의 정보를 가지고있는 구조체 (OLD는 수정, 삭제전 레코드를 가지고 있는 구조체)
    o_prod := :NEW.product; 
    UPDATE SALES_PER_DATE ---------------- ③ 먼저 입력된 주문 내역의 주문 일자와 상품을 기준으로 테이블 업데이트
        SET qty = qty + :NEW.qty, 
        amount = amount + :NEW.amount 
    WHERE sale_date = o_date 
    AND product = o_prod; 
    if SQL%NOTFOUND then ---------------- ④ 처리결과가 SQL%NOTFOUND시 SALES_PER_DATE에 집계데이터 입력
        INSERT INTO SALES_PER_DATE 
        VALUES(o_date, o_prod, :NEW.qty, :NEW.amount); 
    end if; 
END; 
/

--SQL Server 
CREATE Trigger dbo.SUMMARY_SALES ---------------- ① 선언
ON ORDER_LIST 
AFTER INSERT 
AS 
DECLARE 
    @o_date DATETIME,@o_prod INT,@qty int, @amount int 
BEGIN 
    SELECT @o_date=order_date, @o_prod=product, @qty=qty, @amount=amount 
    FROM inserted ---------------- ② inserted : 신규 입력 레코드 담는 구조체 / deleted : 수정,삭제전 레코드값 담는 구조체
    UPDATE SALES_PER_DATE ---------------- ③  먼저입력된 값 기준으로 SALES_PER_DATE 업데이트
        SET qty = qty + @qty, 
            amount = amount + @amount 
    WHERE sale_date = @o_date 
    AND product = @o_prod; 
    IF @@ROWCOUNT=0 ---------------- ④ 처리결과가 0일시 SALES_PER_DATE에 집계데이터 입력
        INSERT INTO SALES_PER_DATE 
        VALUES(@o_date, @o_prod, @qty, @amount) 
END;