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;
/
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
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 = '[프로시저명]';