행복만땅 개발자

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

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