행복만땅 개발자

 ###################################################################################################

#   source site : blog.naver.com/tangamjaelt

#   master : 강용운

#   email : tangamjaelt@korea.com

#   last release : 2007.05.18

###################################################################################################

함수를 사용하는 이유

1. 코딩량을 줄일 수 있다.

2. 함수를 이용하여 모듈 단위로 접근할 수 있다.

3. 반복해서 재사용 할 수 있다.

함수 정의

********************************************************************************************************************

FUNCTION 함수명 [(파라미터 {IN|OUT|IN OUT})]

            RETURN 반환데이터형 

IS|AS

            선언문

BEGIN

            실행문

(EXCEPTION)

            예외처리문

END 함수명;

/

********************************************************************************************************************

파라미터 IN/OUT : 함수 내부와 외부로 파라미터 전달

RETURN : 반환되는 데이터 형. 하나 이상의 RETURN문을 가질 수 있지만 오직 하나만 실행된다. 적어도 하나의 RETURN문은 존재.

IS | AS : 함수에 대한 변수를 지역적으로 설정.

BEGIN : 함수 안에 들어있는 문장의 실행을 시작.

EXCEPTION : 선택사항. 오류 발생시 적당한 처리를 할 수 있다.

END : 함수의 끝을 나타냄.

파라미터 정의하기

- 파라미터는 블럭에서 함수로 전달할 수 있는 값이다.

- 함수는 이 파라미터 값을 받아 일을 수행하고 결과를 반환하거나 반환하지 않을 수 있다.

- actual parameter : 실제 메모리에 저장된 파라미터 값

- formal parameter : actual parameter 에 대한 포인터

파라미터명 [MODE] 파라미터데이터형 [:= 값 | DEFAULT 값]

ex)  v_stuid  VARCHAR2

- MODE : 들어오는 파라미터를 제어하는 문장

      ① IN [NOCOPY] : 읽기전용 : 파라미터가 읽기 전용으로 되고, 값이 변경되는 것을 완전히 막을 수 있다.

      ② OUT [NOCOPY] : 쓰기전용 : 전달된 파라미터 값을 무시하고 함수 내에서 파라미터 값을 할당한다.

      ③ IN OUT [NOCOPY] : 읽기 또는 쓰기 : 파파미터를 제어. 함수를 종료할 때 함수내에서 작성된 값이 파라미터 값으로 할당된다.

- NOCOPY : 파라미터 값이 아주 큰 경우, 컴파일러에게 파라미터를 값이 아닌 참조(포인터)로 전달하라고 요청하는 힌트

파라미터에 값 할당하기

- 파라미터에 값을 할당하려면, := 또는 DEFAULT 를 사용한다.

- DEFAULT 를 사용하면 값을 지정하지 않으면 정해진 기본값으로 할당된다.

ex) 

v_txn_count NUMBER := 10

v_stuid VARCHAR2 DEFAULT '-'

반환 데이터형

- 함수를 사용할 경우 반드시 반환되는 데이터의 데이텨형을 정의하여야 한다.

예외 (Exception)

- 함수나 프로시저 처리 중 오류가 발생하였을 경우 적절한 처리를 위해 작성한다.

- 구문

********************************************************************************************************************

EXCEPTION

      WHEN OTHERS THEN

          ...

********************************************************************************************************************

스토어드 함수 (Stored Function)

- Stored Function 이란 PL/SQL 코드에서 호출할 수 있도록 데이터베이스에 저장된 함수.

- 일반 함수와 달리 stored 함수를 정의할 경우, CREATE [OR REPLACE] 를 사용하여 정의한다.

- 구문

********************************************************************************************************************

CREATE [OR REPLACE] FUNCTION 함수명 [ (파라미터 {IN | OUT | INOUT}) ]

         RETURN 반환데이터형

IS | AS

          선언문

BEGIN

          실행문

(EXCEPTION)

          에외처리문

END 함수명;

********************************************************************************************************************

[예제1] 파라미터가 없는 스토어드 함수 작성과 사용

********************************************************************************************************************

CREATE OR REPLACE FUNCTION mypi
     RETURN NUMBER
IS
BEGIN
     NULL;
     RETURN 3.14;
END mypi;
/

********************************************************************************************************************

SQL> SET SERVEROUTPUT ON;

********************************************************************************************************************

BEGIN

      DBMS_OUTPUT.PUT_LINE('mypi value is ' || mypi);

END;

/

********************************************************************************************************************

디버깅 (오류확인)

- 함수를 작성하다 Compile 에러가 발생하면 INVALID 상태가 되어 사용할 수 없다.

- show errors 명령으로 에러를 확인한다.

- 표시되는 숫자는 'LINE/COLUMN' 형식이며, 6/2 이라고 하면 6번째 줄에서 2번째 항목에 에러가 있다는 뜻.

###################################################################################################

#   source site : blog.naver.com/tangamjaelt

#   master : 강용운

#   email : tangamjaelt@korea.com

#   last release : 2007.06.11

PL/SQL 이란?

- Procedural Language / Sturctured Query Language

- 오라클사가 데이터베이스에서 SQL에 대해 절차적 로직을 실행하는 방법을 제공하기 위해 개발한 절차적 언어

- 데이터 캡슐화, 예외 처리, 정보 숨김, 객체 지향 등의 현대 S/W 공학 기능 제공

- SQL문을 블록 구조 및 프로시저 단위 코드에 포함시킬 수 있는 강력한 트랜잭션 처리 언어

PL/SQL 처리과정

- 오라클 선행 컴파일러에서 PL/SQL 블록(코드)를 제출하면 Oracle Server 내의 PL/SQL 엔진이 이를 처리한다.

- PL/SQL 엔진은 블록 내의 SQL문을 분리하여 하나씩 SQL문 실행자로 전송한다.

- PL/SQL 코드는 Oracle Server 에 저장할 수 있으며, 이름 앞에 Stored를 붙여 부른다. (Stored Procedure)

- Oracle Developer 와 같은 오라클 툴에는 Oracle Server의 엔진과는 별도로 자체 PL/SQL 엔진이 존재한다.

실습에 필요한 권한

- CREATE PROCEDURE

- CREATE SESSION

- CREATE TABLE

- CREATE TRIGGER

- CREATE VIEW

- CREATE TYPE

실습에 필요한 패키지

- DBMS_OUTPUT

- DBMS_SQL

- UTL_FILE

- DBMS_PIPE

- DBMS_ALERT

※ 패키지 확인

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER='SYS' AND OBJECT_TYPE='PACKAGE';

PL/SQL Block 구조

********************************************************************************************************************

DECLARE

     변수 선언문;

BEGIN

     프로그램 코드;

EXCEPTION

     예외 처리문;

END;

********************************************************************************************************************

- 변수선언문 : 블럭에서 사용할 변수를 정의. 커서 정의와 중첩된 PL/SQL 프로시저 및 함수도 여기서 정의.

- 프로그램코드 : 블럭을 구성하는 PL/SQL 문.

- 예외처리문 : 런타임 오류나 예외 발생시 트리거되는 프로그램 코드.

- 각 블럭에서 문장의 끝을 알리기 위해 반드시 세미콜론(;)을 붙인다.

- BEGIN 내에 다른 블럭을 내포할 수 있다.

[예제1] 간단한 작성과 실행

********************************************************************************************************************

DECLARE

       x       NUMBER;

BEGIN

       x := 65400;

END;

/

********************************************************************************************************************

- 맨 끝의 '/' 슬래시는 SQL*Plus에게 PL/SQL 코드의 입력이 끝났음을 알리고 이를 오라클 데이터베이스로 보내는 역할.

[예제2] 결과 출력하기

- 출력은 DBMS_OUTPUT 패키지가 담당한다.

- 패키지 안에 dbms_output.put_line 프로시저 사용.

- 출력을 SQL*Plus 에서 보려면

  SQL> SET SERVEROUTPUT ON 을 먼저 실행해야 한다.

********************************************************************************************************************

DECLARE

       x       NUMBER;

BEGIN

       x := 65400;

       dbms_output.put_line('The variable x = ');

       dbms_output.put_line(x);

END;

/

********************************************************************************************************************

- 내용을 /export/home/oracle/plsql/test.sql 파일에 저장하고 불러들일 수도 있다. 기본확장자는 .sql 이다.

- 간단한 내용은 vi editor를 통해 수정하면 되겠지만, Buffer는 바로 직전의 SQL문만 저장하고 있기 때문에 긴 PL/SQL 프로시저는 반드시 텍스트파일로 만들어서 실행시키는 형식을 취하도록 한다.

$ vi /export/home/oracle/plsql/test.sql

내용기입

:wq

SQL> @/export/home/oracle/plsql/test

[예제3] 함수 작성하기

- PL/SQL을 사용하여 Stored 함수와 Stored 프로시저를 작성한다. 방금까지 작성했던 코드를 Stored 함수로 캡슐화 시키면 그것을 한번만 컴파일하고 데이터베이스에 저장해 놓았다가 나중에 다시 사용할 수 있다.

- DB내에 stored 함수를 만들어 놓으면 나중엔 그 함수만 불러다 쓰면 된다.

********************************************************************************************************************

CREATE OR REPLACE FUNCTION ss_thresh

RETURN NUMBER AS

         x       NUMBER;

BEGIN

         x := 65400;

         RETURN x;

END;

/

********************************************************************************************************************

- OR REPLACE 는 동일한 함수가 존재하면 덮어쓰라는 의미이다.

- 1~2줄 CREATE 부터 AS 까지가 DECLARE 부분이다.

- 함수를 생성하는 것은 오라클이고 SQL*Plus는 결과만 보여줄 뿐이다.

SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME = 'SS_THRESH';

[결과 확인]

- 함수를 작성하고 컴파일을 하였으니 이제 사용만 하면 된다. 함수이지만 ( )를 사용하지 않는 이유는 인수가 없기 때문.

SQL> SELECT SS_THRESH FROM DUAL;

[에러확인]

- 위의 예제에서 실수로 입력이 잘못되었을 경우 에러를 확인하는 방법은 에러가 나고 바로 SHOW ERRORS 를 쳐보면 된다.