6장. 함수와 저장프로시저
CREATE OR REPLACE FUNCTION 이름 ( 파라미터 ) -- 파라미터가 없으면 ( )도 생략
RETURN 반환값유형
AS
변수선언부
BEGIN
PL/SQL Statements. ...
RETURN 반환값
END;
OR REPLACE : 함수를 변형하게 될 때 덮어쓰기를 할 수 있다
CREATE OR REPLACE FUNCTION F_Add(a number, b number)
RETURN NUMBER
AS
Total NUMBER;
BEGIN
Total := a + b;
RETURN Total;
END;
<함수 호출>
-- 함수 호출 1 : Prompt에서 호출
VAR result NUMBER;
EXECUTE :result := F_Add(2020,35);
-- EXECUTE 뒤의 문장은 무명프로시저로 봐야 함.
-- 외부 변수를 사용하려면 ':변수이름'형식을 사용해야 함
-- 함수 호출 2 : SQL 내에서 호출
SELECT F_Add(2020, 35) FROM DUAL;
-- 함수 호출 3 : 프로시저 내에서 호출
DECLARE
result1 NUMBER;
BEGIN
result1 := F_Add(2020,50);
DBMS_OUTPUT.PUT_LINE(result1);
END;
<PIPELINED TABLE FUNCTION>
테이블을 반환하는 함수
[타입정의] - 튜플+테이블순
CREATE OR REPLACE TYPE 튜플타입이름 AS OBJECT
(
열이름 데이터타입,
... ...
);
CREATE OR REPLACE TYPE 테이블타입이름 AS TABLE OF 튜플타입이름;
[테이블함수 정의]
CREATE OR REPLACE FUNCTION 함수이름( )
RETURN 테이블타입이름 PIPELINED
AS
BEGIN
PL/SQL문장들...(반복문과 PIPE_ROW( )사용)
RETURN;
END;
[테이블함수 사용]
SELECT * FROM TABLE(함수이름( ) );
<저장 프로시저>
DB 서버에 저장해 놓은 프로시저, 언제든지 다시 호출해서 사용 가능하다
파라미터 없고, 커서 불필요(SQL문의 결과가 단일값)
OR REPLACE : 대체하고자 할 때 또는 여러번 수정 후 컴파일하고자 할 때. 함수랑 똑같음
[저장 프로시저 정의 형식]
CREATE [OR REPLACE] PROCEDURE 프로시저이름(
파라미터선언부
)
AS
변수선언부
BEGIN
PL/SQL 문장들
...
END
[저장 프로시저 실행 형식]
EXECUTE 프로시저이름( Parameter );
[파라미터선언부의 형식]
[입력 파라미터 선언부 형식]
파라미터변수 IN 데이터타입 [ := 디폴트값]
IN => 내부 프로그램에 제공
[출력 파라미터 선언부 형식]
파라미터변수 OUT 데이터타입
프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당
[파라미터 전달 방식]
EXECUTE 저장프로시저이름(파라미터값);
CREATE OR REPLACE PROCEDURE SP_InOut1 (
Pi_ID IN CHAR,
Pi_Name IN CHAR,
Po_적립금 OUT NUMBER
) AS
--v_count VARCHAR(10);
BEGIN
INSERT INTO 고객(고객아이디, 고객이름, 등급) VALUES(Pi_ID, Pi_Name, 'Silver');
SELECT MAX(적립금) INTO Po_적립금 FROM 고객;
END ;
CREATE OR REPLACE PROCEDURE SP_Error (
Pi_고객이름 IN 고객.고객이름%TYPE,
Po_고객아이디 OUT 고객.고객아이디%TYPE
) AS
BEGIN
SELECT 고객아이디 INTO Po_고객아이디 FROM 고객
WHERE 고객이름 = Pi_고객이름;
EXCEPTION WHEN NO_DATA_FOUND THEN ==> 저장프로시저의 예외처리
Po_고객아이디 := 'Anonymous';
END ;
DECLARE
비회원여부 NCHAR(10);
BEGIN
SP_Error('조나단', 비회원여부);
DBMS_OUTPUT.PUT_LINE (비회원여부);
END;
<저장 프로시저의 장점>
서버 중심의 보안 관리에 편리함
네트워크 전송량의 감소. 단, 서버의 부하 증가
모듈식 루틴 관리 가능
유지 관리의 용이성
예외 처리 가능
트리거와 연계
<패키지>
함수와 저장 프로시저의 묶음
사용자 생성 패키지
관리가 편하고 효율적
자주 함께 사용하는 변수, 함수, 저장 프로시저 등을 묶음
[사용자 생성 패키지 HEADER 형식]
CREATE OR REPLACE PACKAGE 패키지이름
AS
선언부 - 변수, 커서, 예외, 함수, 저장 프로시저
END {패키지 이름};
[사용자 생성 패키지 BODY 형식]
CREATE OR REPLACE PACKAGE BODY 패키지이름
AS
구현부 - 변수, 커서, 예외, 함수, 저장 프로시저
END {패키지 이름};
<커서(Cursor)>
커서란 특정 SQL 문장을 처리한 결과를 담고있는 메모리 영역을 가리키는 일종의 포인터
==> 부하 불일치 해결
커서 정의 => 커서 열기 => 커서가 가리키는 데이터 인출(Fetch) : 반복수행 => 커서 닫기
한 튜플씩
CURSOR C IS
SELECT 고객아이디, 적립금 FROM 고객; -- 커서 정의
OPEN C; -- 커서 열기
LOOP ... LOOP END — Fetch 단계
CLOSE C; -- 커서 닫기