1. 사용자 함수

1-1. 기본 함수 생성

• CREATE OR REPLACE : CREATE 만 사용하여 재컴파일 시 오류가 발생하므로 REPLACE 를 함께 사용

CREATE OR REPLACE FUNCTION [함수명] ([파라미터명] [파라미터타입])
    RETURN [리턴타입]
IS
    [변수명] [변수타입]
BEGIN
    (생략)
    RETURN [리턴값(변수 등)]
END;
/

-- 예시
CREATE OR REPLACE FUNCTION fn_get_user_nm (p_user_num NUMBER)
    RETURN VARCHAR2
IS 
    v_user_nm VARCHAR2(30);
BEGIN
    SELECT USER_NM INTO v_user_nm
    FROM USER_INFO 
    WHERE USER_NUM = p_user_num;
    RETURN v_user_nm;
END; 
/

1-1-1. 기본 함수 사용

SELECT
    USER_NUM
    , fn_get_user_nm(USER_NUM) AS USER_NM
FROM USER_SCORE;

1-2. 파이프라인 테이블 함수 생성

• Object 타입, 테이블 타입을 정의하여, 파이프라인 테이블 함수 생성 시에 사용
• 파이프라인 테이블 함수 사용 시 장점
  • 결과 집합이 모두 생성될 때까지 기다리지 않고 완료된 부분을 순차적으로 처리하므로 응답 시간이 빠름
  • 스트리밍(하나의 연산을 종료하지 않고 일련의 연산을 순차적으로 반복 처리)
  • 파이프라이닝(여러 연산을 연속해서 수행)
  • 유연성(SQL 문장으로 표현하기 어려운 과정을 유연하게 처리)

1-2-1. Object 타입 생성

• 함수에서 반환하는 레코드의 스키마를 정의

CREATE OR REPLACE TYPE OBJ_STORE AS OBJECT (
    USER_NUM NUMBER(10)
    , USER_NM VARCHAR2(20)
    , STORE_NUM NUMBER(10)
    , STORE_NM VARCHAR2(50) 
    , STORE_ADDR VARCHAR2(30)
);

1-2-2. 테이블 타입 정의

• 함수에서 반환하는 레코드의 집합(테이블) 정의

CREATE OR REPLACE TYPE TABLE_STORE AS TABLE OF OBJ_STORE;

1-2-3. 파이프라인 테이블 함수 작성

• RETURN 타입은 위의 테이블 타입으로 지정
• 변수는 위의 Object 타입으로 지정

CREATE OR REPLACE FUNCTION [함수명] ([파라미터명] [파라미터타입])
    RETURN [리턴타입] PIPELINED
IS
    [변수명] [변수타입]
BEGIN
    (생략)
    RETURN;
END;

-- 예시
CREATE OR REPLACE FUNCTION fn_get_store_info (p_user_num NUMBER)
    RETURN TABLE_STORE PIPELINED
IS
    v_obj_store OBJ_STORE;
BEGIN 
    FOR v_row IN (
        SELECT 
            U.USER_NUM
            , fn_get_user_nm(U.USER_NUM) AS USER_NM
            , S.STORE_NUM
            , S.STORE_NM
            , S.STORE_ADDR
        FROM USER_INFO U
        INNER JOIN STORE_INFO S
        ON U.USER_NUM = S.OWNER_NUM
        WHERE U.USER_NUM = p_user_num
    ) LOOP
        v_obj_store := OBJ_STORE(v_row.USER_NUM, v_row.USER_NM, v_row.STORE_NUM, v_row.STORE_NM, v_row.STORE_ADDR);
        PIPE ROW(v_obj_store);
    END LOOP;
    RETURN;
END;
/

1-2-4. 파이프라인 테이블 함수 사용

SELECT * FROM TABLE(fn_get_store_info(1));

1-3. 함수 조회

SELECT * FROM USER_SOURCE WHERE TYPE = 'FUNCTION';
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';
-- 삭제
DROP FUNCTION [함수명];

2. 프로시저

• Transact-SQL 문장의 집합
• 프로시저는 PL/SQL을 통해 만들어짐
• 자주 사용하는 SQL문을 프로시저로 만들어 필요할 때마다 호출, 사용하여 작업 효율 증가
• 특정 로직을 처리할 뿐 결과 값은 반환하지 않음
• SQL Server에서 사용하는 프로그래밍 기능
• 빠르고, 여러 어플리케이션과 공유할 수 있음

2-1. 프로시저 생성

• IN 뒤에 인수의 타입을 선언
  • [파라미터명] IN VARCHAR2 (byte 크기는 지정하지 않음)
  • [파라미터명] IN [테이블명].[컬럼명]%TYPE;
  • [파라미터명] IN [테이블명].[컬럼명]%TYPE := 값;
  • [파라미터명] IN [테이블명].[컬럼명]%TYPE DEFAULT 값;

CREATE OR REPLACE PROCEDURE [프로시저명] ([파라미터명] IN [파라미터타입])
IS
    [변수명] [변수타입];
BEGIN
    (생략)
END;
/

• 회원 정보 INSERT하는 프로시저 예시

CREATE OR REPLACE PROCEDURE PROC_INSERT_USER
    (p_user_nm IN VARCHAR2, p_user_birth IN VARCHAR2)
IS
    v_user_num NUMBER(10) := USER_NUM_SEQ.NEXTVAL;
BEGIN
    INSERT INTO USER_INFO values(v_user_num, p_user_nm, p_user_birth);
    COMMIT;
END;
/

2-2. 프로시저 실행

• EXEC [프로시저명](); 을 입력하여 실행

EXEC PROC_INSERT_USER('YNJCH','20000101');

• DECLARE 절에서 변수 선언 후 실행

DECLARE 
    param1 VARCHAR2(30) := 'TEST';
    param2 VARCHAR2(30) := '19991231';
BEGIN
    PROC_INSERT_USER(param1, param2);
END;
/

2-2-1. 프로시저 예외 처리

• BEGIN~END 사이에 아래와 같이 예외 처리

(SELECT 문)

EXCEPTION 
    WHEN NO_DATA_FOUND THEN dbms_output.put_line('EXCEPTION 1'); RETURN;
    WHEN OTHERS THEN dbms_output.put_line('EXCEPTION 2'); RETURN;

2-3. 프로시저 조회

• 작성된 프로시저를 찾기 위해 데이터 사전 이용
• 데이터 사전은 대문자로 값을 저장하므로 유의

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE';
SELECT * FROM USER_SOURCE WHERE NAME = '[프로시저명]';