행복만땅 개발자

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

#   source site : blog.naver.com/tangamjaelt

#   master : 강용운

#   email : tangamjaelt@korea.com

#   last release : 2007.06.09

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

 

패키지

- 서로 관련된 스키마 오브젝트들을 묶어 캡슐화 한 것.

- 컴파일되어 스키마 오브젝트로 데이터딕셔너리에 저장된다.

- 비슷한 작업을 수행하는 프로시저, 함수 등을 함께 묶어 사용한다.

- 패키지 자체는 호출해도 사용하지 못한다. 내부의 특정 서브 프로그램(프로시저,함수 등)을 호출해야 한다.

- "패키지.서브프로그램" 형식으로 호출한다.

- 패키지 구성 : 서브 프로그램 명세(Specification) + 서브 프로그램 본문(Body)

- 패키지 명세가 바뀌면 그 패키지를 참조하는 모든 스토어드 서브 프로그램을 다시 컴파일해야 한다.

- 패키지 본문은 바뀌어도 컴파일이 필요없다.

 

 

패키지를 사용하는 이유

- 보다 효율적인 모듈 단위로 구성하여 이해하기 쉽다.

- 패키지의 public 변수와 커서는 세션이 열려 있는 동안 지속되므로 세션에서 실행되는 모든 커서와 프로시저는 공유할 수 있다.

- 여러개의 오브젝트를 한번에 메모리로 올리기 때문에 성능 향상에 도움을 준다.

- 스토어드 프로시저와 함수를 갖고 있는 라이브러리를 사용해서 코드 재사용을 증진시켜 불필요한 코딩을 줄인다.

 

 

패키지 명세

- 패키지의 이름과 인수의 이름, 데이터형을 public으로 선언하는 부분.

- 패키지에 선언된 오브젝트는 패키지 어디에서나 접근 가능하다. (패키지에 대해 전역적)

- 응용 프로그램이 스토어드 서브 프로그램을 실행해야 하는 모든 정보는 패키지 명세에 들어 있다.

- 즉, 패키지 내에 어떤 오브젝트들이 있는지 미리 이름과 형식을 알리는 것임.

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

CREATE [OR REPLACE] PACKAGE 패키지이름

[AUTHID {CURRENT_USER | DEFINER} {IS | AS}

[패키지 본문 오브젝트 선언]

END 패키지이름;

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

- AUTHID : 패키지 실행시 호출할 권한의 유형.

       - CURRENT_USER : 현재 사용자(호출자)

       - DEFINER : 생성자

- 패키지 본문 오브젝트 선언 : 패키지에서 생성될 오브젝트의 목록을 적는 부분

 

 

패키지 본문

- 패키지 본문에는 명세에서 선언한 실제 오브젝트의 코드가 들어간다.

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

CREATE [OR REPLACE] PACKAGE BODY 패키지이름 {IS | AS}

   [패키지 본문 오브젝트 선언]

BEGIN

   프로그램문장

END 패키지이름;

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

 

패키지 서브 프로그램 호출

1. 사용자가 서브 프로그램에 대한 실행 권한을 가지고 있는지 확인한다.

2. Dictionary View에서 프로시저가 VALID 상태인지 확인한다. INVALID 라면 실행 전에 자동으로 재컴파일한다.

3. 패키지의 서브 프로그램을 실행한다.

4. 점(.)으로 패키지와 서브 프로그램을 구분하여 사용한다.

    - 패키지.형

    - 패키지.오브젝트

    - 패키지.서브프로그램

 

 

 

[예제1] id10 의 나이와 마일리지를 변경하는 프로시저를 포함하는 패키지 작성

 

1. 아래처럼 USERID 가 'id10' 인 홍길동이 있다.

 

 

 

2. 패키지 명세를 작성하고 실행한다. (패키지 이름 : change_pkg)

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

CREATE OR REPLACE PACKAGE change_pkg AS
  PROCEDURE change_ages (i_id IN VARCHAR2);
  PROCEDURE change_mileage (i_id IN VARCHAR2);
END change_pkg;
/
********************************************************************************************************************

3. 패키지 몸체(Body)를 작성하고 실행시킨다. (change_ages, change_mileage)

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

CREATE OR REPLACE PACKAGE BODY change_pkg AS

        i_id VARCHAR2(10);

PROCEDURE change_ages (i_id IN VARCHAR2) IS
BEGIN
        UPDATE member
        SET AGE=100
        WHERE userid = i_id;
END change_ages;

PROCEDURE change_mileage (i_id IN VARCHAR2) IS
BEGIN
        UPDATE member
        SET MILEAGE=777
        WHERE userid = i_id;
END change_mileage;

END change_pkg;
/
********************************************************************************************************************

4. 패키지의 프로시저를 실행시킨다.

SQL> exec change_pkg.change_ages('id10');

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

#   source site : blog.naver.com/tangamjaelt

#   master : 강용운

#   email : tangamjaelt@korea.com

#   last release : 2007.05.07

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

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

#   source site : blog.naver.com/tangamjaelt

#   master : 강용운

#   email : tangamjaelt@korea.com

#   last release : 2007.05.04

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

PL/SQL을 마스터하기 위해서는 블럭 구조와 블럭의 다양한 종류, 블럭의 사용법을 이해하는 것이 필수적이다.

Anonymous Block (이름 없는 블럭)

- 아무런 이름이 없는 블럭으로 프로시저나 함수, 트리거의 본문을 형성하지 않는 블럭

- 이는 SQL*Plus 스크립트의 일부로 인라인으로 사용될 수 있으며, 오류 처리 목적으로 프로시저와 함수 블럭 내부에 포함될 수도 있다.

- DECLARE 라는 예약어를 사용하여 시작하고 다음 예약어인 BEGIN 사이에 변수를 선언한다.

- BEGIN 예약어는 블럭의 절차적 부분이 시작된다는 것을 알려준다. 프로그램 코드가 이 부분에 온다.

- EXCEPTION 예약어는 블럭에서 예외 처리 코드를 갖는다.

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

DECLARE

      변수 선언문;

BEGIN

      프로그램 코드

EXCEPTION

      오류처리 코드

END;

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

Function Block (함수 블럭)

- DECLARE 예약어 대신 그 자리에 함수 헤더가 온다.

- 함수 헤더는 함수 이름과 파라미터들을 기술하고, 반환값의 형을 나타낸다.

- 인수목록 : 함수에 대한 입력 파라미터와 출력 파라미터 목록을 기술한다.

- RETURN : 반환값에 대한 데이터형을 기술한다.

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

FUNCTION 이름 [(인수목록)] 

RETURN 데이터형 {IS, AS}

      변수 선언문;

BEGIN

      프로그램 코드

(EXCEPTION

      오류처리 코드)

END;

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

DECLARE
        temp NUMBER;

        FUNCTION iifn(boolean_expression IN BOOLEAN,
                        true_number IN NUMBER,
                        false_number IN NUMBER)
        RETURN NUMBER IS

        BEGIN
                IF boolean_expression THEN
                        RETURN true_number;

                END IF;
        END;

BEGIN
        DBMS_OUTPUT.PUT_LINE(iifn(2>1,1,0));
        DBMS_OUTPUT.PUT_LINE(iifn(2>3,1,0));

        temp := iifn(null, 1, 0);

        IF temp IS NULL THEN
                DBMS_OUTPUT.PUT_LINE('NULL');
        ELSE
                DBMS_OUTPUT.PUT_LINE(temp);
        END IF;
END;
/

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

Procedure Block (프로시저 블럭)

- 함수와 비슷하다.

- 함수와 다른점은 함수는 값을 반환하고 식에 사용할 수 있지만, 프로시저는 값을 반환하지 않으며 식에 활용할 수 없다.

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

PROCEDURE 이름 [(인수목록)] {IS, AS}

      변수 선언문;

BEGIN

      프로그램 코드

(EXCEPTION

      오류처리 코드)

END;

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

DECLARE
        first_number NUMBER;
        second_number NUMBER;

        PROCEDURE swapn(num_one IN OUT NUMBER,
                        num_two IN OUT NUMBER) IS
                temp_num NUMBER;
        BEGIN
                temp_num := num_one;
                num_one := num_two;
                num_two := temp_num;
        END;

BEGIN
        first_number := 10;
        second_number := 20;
        DBMS_OUTPUT.PUT_LINE('First Number =' || TO_CHAR(first_number));
        DBMS_OUTPUT.PUT_LINE('Second Number =' || TO_CHAR(second_number));

        DBMS_OUTPUT.PUT_LINE('Swapping...');
        swapn(first_number, second_number);

        DBMS_OUTPUT.PUT_LINE('First Number =' || TO_CHAR(first_number));
        DBMS_OUTPUT.PUT_LINE('Second Number =' || TO_CHAR(second_number));
END;
/
********************************************************************************************************************

 중첩 블럭

- Block 내에 Block 이 들어감

- 변수는 블럭 내부에서만 사용 가능하다. 상위 레벨의 변수는 하위 레벨에서 모두 사용 가능하다.

- BEGIN 내에 정의

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

DECLARE
        error_flag BOOLEAN := false;

BEGIN
        DBMS_OUTPUT.PUT_LINE('100~1000 counting');

        DECLARE
                hundreds_counter NUMBER(1,-2);
        BEGIN
                hundreds_counter := 100;

                LOOP
                        DBMS_OUTPUT.PUT_LINE(hundreds_counter);
                        hundreds_counter := hundreds_counter + 100;

                        IF hundreds_counter > 1000 THEN
                                EXIT;
                        END IF;
                END LOOP;
        EXCEPTION
        WHEN OTHERS THEN
                error_flag := true;
        END;

        IF error_flag THEN
                DBMS_OUTPUT.PUT_LINE('Can not count.');
        ELSE
                DBMS_OUTPUT.PUT_LINE('Count Done.');
        END IF;
END;
/
********************************************************************************************************************

Trigger Block (트리거 블럭)

- 트리거란 어떤 동작이나 사건이 발생했을 때 샐행되는 코드를 정의하는데 사용된다.

- 처음에 나타나는 구문은 트리거의 종류, 트리거와 연결된 테이블, 트리거가 발생해야 할 때를 말해주는 것이다.

- 예를 들어 특정 User가 CREATE, DROP, ALTER 등의 DDL 문을 실행하게 되면 트리거에서 사용을 막게 할 수 있다.

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

CREATE [OR REPLACE] TRIGGER 트리거이름

{BEFORE|AFTER} 동사목록 ON 테이블이름

[[REFERENCING 상호관계이름] FOR EACH ROW [WHEN (조건)]]

DECLARE

      선언문

BEGIN

      PL/SQL코드

END;

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

- 동사목록 : 트리거를 발생시키는 SQL 동사

- 테이블이름 : 트리거가 정의되는 테이블

- 조건 : 트리거 실행 조건

- 선언문 : 변수, 레코드, 커서 선언문으로 구성

- PL/SQL코드 : 트리거가 발생할 때 실행되는 PL/SQL 코드

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

#   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 를 쳐보면 된다.