tbPSM 소개

tbPSM의 기본 개념과 구성요소 그리고 tbPSM 프로그램의 구조를 소개합니다.

개요

SQL은 비절차적(Non-procedural) 언어로 데이터베이스의 모든 작업을 통제합니다. SQL은 대체로 간단하고 적은 수의 명령어로 구성되어 있습니다. 따라서 일반적으로 사용자는 SQL의 기초가 되는 데이터 구조와 알고리즘을 몰라도 자유롭게 SQL 명령을 실행할 수 있습니다. SQL의 장점에도 불구하고 사용자가 원하는 프로그램을 작성하려고 할 때 여전히 불편함이 따릅니다.

tbPSM은 Tibero에서 제공하는 PSM 프로그램 언어 및 실행 시스템입니다. 순차적으로 원하는 결과를 얻어 야 하는 프로그램을 SQL 문장만으로 작성할 수 없다는 제약 때문에 tbPSM이 필요합니다.

참고

PSM(Persistent Stored Modules)은 절차적 기능을 갖춘 확장된 SQL를 생성하는 강력한 프로그래밍 언어입니다. 그 기능이 매우 단순하고 강력하여 사용자에 의해 널리 사용되고 있습니다. 또한 PSM은 데이 터베이스 프로그래밍을 위한 ISO 표준에 기반을 두고 있으며, SQL 명령을 사용하여 데이터베이스 와 상호 연동을 할 수 있습니다.

tbPSM은 SQL 문장을 제어 구조(예: if...then...else..., loop) 등에 추가하여 프로그램을 절차적으로 구성할 수 있습니다. SQL의 비절차적인 구조의 문제점을 해결하여 사용자가 원하는 프로그램을 작성할 수 있습니다.

빈번하게 수행되는 작업을 데이터베이스 시스템에 컴파일 된 상태로 저장함으로써, 매번 해당 질의를 다시 컴파일해야 하는 부담을 덜어줍니다. 뿐만 아니라 tbPSM 내부에 IF, WHILE 등과 같은 제어 문장과 변수 등을 활용할 수 있기 때문에 복잡한 작업의 수행을 위해 애플리케이션 프로그램과 데이터베이스 서버 간 의 빈번한 통신이 발생하는 것을 줄여 줍니다. 이 기능은 널리 사용되고 있는 PL/SQL 기능과 대등합니다.

tbPSM은 다음과 같은 절차형 언어(제3세대 언어)의 성격을 SQL에 추가함으로써 사용자에게 편의를 제공합니다.

• 변수와 타입

• 제어 구조

• 프러시저와 함수

구성요소

본 절에서는 tbPSM 프로그램을 작성하거나 실행하기에 앞서 기본적으로 알아야 할 구성요소를 설명합니다.

변수와 상수

본 절에서는 변수와 상수를 간략히 설명합니다.

변수

변수는 프로그램을 작성할 때 값을 나타내는 문자나 문자들의 집합입니다. 변수를 선언하는 방법은 다음과 같습니다.

변수이름 변수의 타입[제약조건] [기본값 정의]

항목	설명
변수이름	값을 나타내는 문자나 문자 세트의 이름 변수이름을 먼저 입력 변수이름 뒤에 변수의 타입과 제약조건이 따름
변수의 타입	데이터베이스 고유의 타입뿐만 아니라 tbPSM 프로그램에서 전용으로 사용 하는 타입도 포함
제약조건	데이터 타입에 따라 사용 여부가 달라짐
기본값 정의	정의된 변수에 값이 할당되지 않은 경우에는 정의된 기본값을 사용

다음은 변수를 선언하는 예입니다.

id NUMBER;
productname VARCHAR2(20) := 'Tibero';

변수를 할당하는 방법은 콜론(:)과 등호(=)를 조합하여 이루어집니다.

[할당 받는 대상] := [할당하는 대상]

항목	설명
할당 받는 대상	반드시 변수가 와야 함
:=	이 기호를 기준으로 왼쪽에 위치한 것은 할당 받는 대상이고 오른쪽은 할당 하는 대상
할당하는 대상	변수, 표현 식, 함수의 결과 등이 올 수 있으며 대상의 제약이 없음

다음은 변수를 할당하는 예입니다.

gravity := 9.8;
force := mass * acceleration;
grade := calculate_the_grade('Darwin');

상수

상수는 프로그램이 수행되는 동안은 변하지 않는 값을 나타내는 데이터로 숫자, 문자, 문자열 등이 있습니다. 상수를 선언하는 방법은 다음과 같습니다.

변수이름 CONSTANT 변수의 타입 := [기본값 정의]

항목	설명
변수이름	변수이름이 제일 먼저 위치
CONSTANT	상수의 선언은 CONSTANT라는 예약어를 사용하면 됨
변수의 타입	변수의 데이터 타입을 설정
기본값 정의	정의된 상수에 값을 할당 상수로 선언된 변수에는 어떠한 값도 다시 할 당할 수 없음

다음은 상수를 선언하고 특정 값을 할당하는 예입니다.

PI CONSTANT NUMBER := 3.141592;

제어 구조

tbPSM은 프로그램 실행 중에 발생되는 조건에 따라 특정 작업을 수행하거나 반복적인 작업을 수행하는 제어 구조(control structure)를 제공합니다. 특정 작업을 수행하는 조건 구조(conditional structure)와 반복 작업을 수행하는 반복 구조(iterative structure)를 위한 다양한 명령어를 제공합니다. 자세한 내용은 “제어 구조”를 참고합니다.

tbPSM의 제어 구조는 다음과 같이 구분할 수 있습니다.

• 선택적(selective) 제어 구조 IF 문과 CASE 문이 포함됩니다.

• 반복 제어 구조(iterative structure) LOOP 문, WHILE 문, FOR 문이 포함됩니다.

다음은 IF 문의 예입니다.

DECLARE
    evaluation_score NUMBER; 
BEGIN
    SELECT bonus into evaluation_score FROM employee WHERE emp_no = 19963077; 
    IF evaluation_score > 80 THEN
        DBMS_OUTPUT.PUT_LINE('This person gets a salary bonus.'); 
    ELSE
        DBMS_OUTPUT.PUT_LINE('This person does not get a salary bonus.'); 
        END IF;
END;

서브 프로그램과 패키지

본 절에서는 서브 프로그램과 패키지를 간략히 설명합니다.

서브프로그램

서브 프로그램(subprogram)은 일반적인 프로그래밍에서의 함수와 동일한 기능을 수행합니다.

기본적으로 프러시저(procedure)와 함수(function)를 제공하는데, 이 둘의 차이점은 프러시저는 반환값이 없고, 함수는 반환값이 있습니다는 것입니다. 자세한 내용은 “서브 프로그램”을 참고합니다.

서브 프로그램은 tbPSM의 프로그램 구조와 마찬가지로 선언부, 실행부, 예외 처리부로 구성됩니다. 다음은 서브 프로그램을 작성한 예입니다.

CREATE [OR REPLACE] PROCEDURE check_sal(name VARCHAR2) IS 
    sal NUMBER;
    invalid_id exception; 
BEGIN
    SELECT registered_num INTO sal FROM emp WHERE emp_name = name;
    IF sal < 7000 THEN
        raise invalid_sal; 
    END IF;
    DBMS_OUTPUT.PUT_LINE('Correct'); 
EXCEPTION
    WHEN invalid_id THEN
        DBMS_OUTPUT.PUT_LINE('Invalid registration number');
END;

패키지

패키지(package)는 서브 프로그램과 변수, 상수의 집합이라 할 수 있습니다. 즉, 관련된 기능을 그룹으로 묶어사용할 수 있어 프로그램을 관리하기가 쉽습니다. 또한 패키지는 사용하는 시점에 패키지의 구성요소가 일시에 메모리에 로드되므로 특정 작업을 수행할 때 데이터베이스 서버의 성능을 향상하는 데 도움이 되기도 합니다. 모듈 간의 의존성 축소화(dependency minimizing)를 위해서도 사용된다. 자세한 내용은 “패키지”를 참고합니다.

패키지는 선언부(specification)와 구현부(body)로 구성됩니다.

• 선언부 패키지에서 사용할 함수와 프러시저의 이름 그리고 변수, 상수, 타입, 예외 상황, 커서의 선언 정보가 포함됩니다.

다음은 선언부의 작성 예입니다.

CREATE [OR REPLACE] PACKAGE book_manager IS 
    book_cnt NUMBER;
    PROCEDURE add_new_book(author VARCHAR2, name VARCHAR2, publish_year DATE); 
    PROCEDURE remove_lost_book(author VARCHAR2, name VARCHAR2);
    FUNCTION search_book_position(author VARCHAR2, name VARCHAR2) 
             RETURN NUMBER;
    FUNCTION get_total_book_cnt RETURN NUMBER; 
END;

• 구현부 선언된 프러시저나 함수가 실제로 구현되는 부분입니다.

다음은 구현부의 작성 예입니다.

CREATE [OR REPLACE] PACKAGE BODY book_manager IS
    PROCEDURE add_new_book(v_author VARCHAR2, v_name VARCHAR2,
                            publish_year DATE) IS
    BEGIN
        IF substr(v_name, 1, 1) >= 'a' AND 
        substr(v_name, 1, 1) < 'k' THEN
            INSERT INTO books
                VALUES (1, v_author, v_name, publish_year);
        ELSE
            INSERT INTO books
                VALUES (2, v_author, v_name, publish_year);
        END IF;
        COMMIT;
        book_cnt := book_cnt + 1; 
    END;
    
    PROCEDURE remove_lost_book(v_author VARCHAR2, v_name VARCHAR2) IS 
    BEGIN
        DELETE FROM books WHERE author = v_author AND name = v_name; 
        COMMIT;
    END;
    
    FUNCTION search_book_position(v_author VARCHAR2, v_name VARCHAR2) 
    RETURN NUMBER IS
        book_position NUMBER; 
    BEGIN
        SELECT kind INTO book_position FROM books 
            WHERE author = v_author AND name = v_name;
        RETURN book_position; 
    EXCEPTION
        WHEN NO_DATA_FOUND THEN 
            DBMS_OUTPUT.PUT_LINE('NOT EXIST...SORRY'); 
            RAISE;
    END;
    
    FUNCTION get_total_book_cnt RETURN NUMBER IS 
    BEGIN
        RETURN book_cnt; 
    END;
BEGIN
    book_cnt := 0; 
END;

커서

커서(cursor)는 SQL 문장을 처리한 후 얻은 결과 집합에 대한 포인터입니다.

데이터베이스에서 탐색된 여러 로우를 처리하기 위해 사용되며, 프로그램은 커서를 사용하여 질의(query)의 결과로 생성된 집합을 한 번에 한 로우씩 검사하고 처리할 수 있습니다. 커서는 질의의 결과와 반환되는 결과 집합이 매우 크거나 크기를 예상할 수 없는 경우에 유용하게 사용됩니다.

tbPSM은 커서와 커서의 변수를 제공합니다. 자세한 내용은 “커서”를 참고합니다.

다음은 커서를 사용한 예입니다.

DECLARE
    EMP_NO VARCHAR(8);
    CURSOR c1 IS SELECT EMP_NO FROM employee; 
BEGIN
    OPEN c1;
    LOOP
        FETCH c1 INTO EMP_NO; 
        EXIT WHEN c1%NOTFOUND;
    END LOOP;
END;

에러 처리

tbPSM 프로그램은 실행 중에 에러가 발생할 수 있습니다. 예를 들어 SELECT INTO 문을 실행했을 때 반환되는 로우가 없는 경우를 들 수 있습니다. 이러한 에러를 예외 상황이라 합니다.

tbPSM 프로그램 내에서는 실행 중에 발생할 수 있는 예외 상황(exception)을 처리하기 위한 루틴을 포함시킬 수 있습니다. 이러한 루틴을 에러 처리 루틴(error handling routine)이라고 합니다. 에러가 발생했을 때 그 에러에 대한 처리 루틴이 정의된 경우 해당 루틴을 실행하게 됩니다.

이러한 루틴을 통해tbPSM 프로그램은 구조가 명확해지고 관리가 쉬워진다는 장점이 있습니다. 자세한 내용은 “에러 처리”를 참고합니다.

다음은 에러를 처리하는 예입니다.

DECLARE
    no_bonus exception; 
    the_sales NUMBER := 12000;
BEGIN
    IF the_sales < 20000 THEN 
        raise no_bonus;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Your bonus is $' || the_sales * 0.1); 
EXCEPTION
    WHEN no_bonus THEN
        DBMS_OUTPUT.PUT_LINE('Sorry, increase the sales');
END;

프로그램 구조

tbPSM 프로그램은 기본적으로 블록(block) 구조로 이루어져 있습니다. 하나의 블록은 크거나 작은 하나의 작 업을 수행하는 모듈이며, 다른 블록을 포함할 수 있습니다.

전형적인 tbPSM의 블록은 다음과 같이 세 부분으로 구성됩니다.

• 선언부 DECLARE와 함께 시작되며, 블록 내에서 사용할 데이터 변수를 선언하는 부분입니다. 선언부는 다음과 같은 특징이 있습니다.

  • 생략할 수 있습니다.

  • 선언부는 실행 코드부와 에러 처리부와는 달리 서브 블록을 포함할 수 없습니다.

  • 실행 코드부에서 사용하는 변수가 없으면 선언부는 블록에 포함하지 않아도 됩니다.

• 실행 코드부 BEGIN 예약어와 END 예약어 중간에 포함됩니다. 실행 코드부는 다음과 같은 특징이 있습니다.

  • 데이터베이스를 액세스하기 위한 일반적인 SQL 문장과 제어 구조 문장이 포함됩니다.

  • 실행 코드부 내의 모든 SQL 문장과 코드는 항상 세미콜론(;)으로 끝납니다.

• 에러 처리부 BEGIN 예약어와 END 예약어 중간에 포함되며, 실행 코드부에서 발생한 에러를 처리하는 부분입니다. 이러한 에러를 예외 상황이라 합니다. 예외 상황에는 시스템이 미리 정의해 둔 것과 사용자가 프로그램에서 정의한 것이 있습니다.

다음은 tbPSM 프로그램의 구조를 나타내는 예입니다.

DECLARE
    -- 선언부
    name varchar(32);

BEGIN
    -- 실행 코드부
    SELECT emp_name INTO name FROM employee WHERE emp_no = 100;

EXCEPTION
    -- 에러 처리부
WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('employee not exist');

END;

위 예를 기준으로 tbPSM 프로그램이 수행되는 순서는 다음과 같습니다.

1. 선언부 DECLARE와 함께 name이라는 변수를 선언합니다. 데이터 타입은 varchar이며, 길이는 32입니다.

2. 실행 코드부 EMPLOYEE 테이블에서 emp_no 컬럼이 100인 emp_name의 데이터를 검색합니다. 검색한 결과는 선언부에서 정의한 name에 할당합니다. 만약, 검색된 결과가 없으면 3번 순서로 이동합니다.

3. 에러 처리부 검색된 결과가 없을 때 처리되는 부분으로 메시지 버퍼에 employee not exist라는 메시지를 EOF 문자와 함께 저장합니다.