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; -- 커서 닫기