SQLJ를 배우자! - DDL 문과 트랜잭션

한빛미디어

2002-05-03

by HANBIT

11,296

저자: 『Java Programming with Oracle SQLJ』의 저자 제이슨 프라이스(Jason Price), 역 김대곤

이 기사는 "SQLJ를 배우자" 시리즈의 마지막 기사로 SQLJ 프로그램에서 SQL DDL(데이터 정의 언어)문을 사용하는 방법에 대하여 살펴볼 것이다. DDL문은 데이터베이스 객체를 생성하고 수정하는 작업을 수행할 때 사용되는 것으로 Create Table, Alter Table, Drop Table, Create Index, Create Sequence와 같은 문장들로 구성되어 있다.

여기에서 사용된 SQL 스크립트, 소스 코드, 기타 파일들은 오라일리 웹사이트에서 다운로드 할 수 있다. 사용된 파일에 대한 자세한 정보와 SQLJ에 대한 소개는 본 시리즈의 첫번째 기사인 "SQLJ 프로그램 개발을 위한 환경 설정하기"를 참고하면 된다.

DDL(데이터 정의 언어)의 사용

SQLJ 프로그램에서 DDL문을 사용할 때는 다른 SQL문과 동일하게 #sql 토큰 안에 DDL문을 쓰면 된다. 아래 예제는 고객의 주소가 저장되는 addresses라는 이름을 가진 테이블을 생성하는 Create Table문 사용법을 보여주고 있다.

#sql {
  CREATE TABLE addresses (
    id           NUMBER
      CONSTRAINT addresses_pk PRIMARY KEY,
    customer_id  NUMBER
      CONSTRAINT addresses_fk_customers
      REFERENCES customers(id),
    street       VARCHAR2(255) NOT NULL,
    city         VARCHAR2(255) NOT NULL,
    state        CHAR(2) NOT NULL,
    country      VARCHAR2(255) NOT NULL
  )
};

트랜잭션

이전 기사(SQLJ를 배우자! - DML 문)에서 데이터베이스 트랜잭션의 개념과 트랜잭션 내에서 수행한 변경내용을 데이터베이스에 물리적으로 저장하기 위해 SQL COMMIT을 어떻게 사용하는지에 대하여 설명하였다. 또한 트랜잭션 내에서 수행한 변경을 되돌리는 ROLLBACK 문장을 수행하는 방법도 설명하였다. SQLJ프로그램에서 수행되는 SQL문에도 동일한 트랜잭션 개념이 적용된다.

SQLJ프로그램에서 COMMIT와 ROLLBACK문을 실행하는 문법은 아래와 같다.

#sql { COMMIT [WORK] };
#sql { ROLLBACK [WORK] };




		Java Programming with Oracle SQLJ

문법의 각 요소들은 다음과 같은 의미를 가지고 있다.

COMMIT: 트랜잭션을 커밋한다. 변경사항을 물리적으로 저장한다.
ROLLBACK: 트랜잭션을 롤백한다. 데이터베이스를 트랜잭션이 시작되기 이전 상태로 되돌린다. 트랜잭션 안에서 수행되었던 모든 SQL문장으로 인한 변경은 취소된다.
WORK: 지원되는 SQL문법의 한 부분으로 옵션이다.

아래 예제는 INSERT문을 수행하고 난 후 ROLLBACK문을 수행한다.

#sql {
  INSERT INTO
    customers (id, first_name, last_name, dob, phone)
  VALUES
    ("7", "John", "Smith", "01-JAN-1970", "650-555-1212")
};

#sql { ROLLBACK };

이 예제에서는 customers 테이블에 한 행이 입력되고 ROLLBACK문의 결과로 입력이 취소된다.

Auto-Commit

기본적으로 트랜잭션 안에서 발생한 변경사항을 데이터베이스에 물리적으로 저장하기 위해서는 각 트랜잭션 끝에 COMMIT문을 수행해야 한다. 그러나 각 DML문이 수행될 때마다 SQLJ가 자동적으로 COMMIT문을 수행하도록 할 수 있다. 이러한 방식은 각 트랜잭션이 하나의 SQL 문장으로 구성되어 있거나 프로그램 작성시 트랜잭션에 대해 고려하지 않기를 바랄 때는 편리한 방법이다. 이러한 기능을 Auto-Commit라고 하며 데이터베이스에 처음 접속할 때 이러한 기능을 사용하도록 설정할 수 있다.

Auto-Commit 기능을 사용하기 위해서는 oracle.sqlj.runtime.Oracle 클래스의 connect() 메소드를 호출할 때 네 번째 값을 넘겨주어야 한다. 이 값은 선택사항이며 넘겨주지 않았을 경우에는 false 값을 넘긴 것으로 간주된다. 이 네 번째 값은 불린(Boolean) 값으로 true/false가 되며 Auto-Commit 기능의 사용여부를 나타낸다. Auto-Commit 기능을 사용하고자 한다면 true 값을 넘겨야 한다. 아래 예제는 Oracle.connect() 메소드를 사용할 경우 Auto-Commit 기능을 사용할 수 있도록 하는 설정법을 보여주고 있다.

Oracle.connect(
  "jdbc.oracle.thin:@localhost:1521:orcl",
  "fundamental_user",
  "fundamental_password",
  true
);

이러한 연결을 통해 수행되는 모든 SQL문 다음에는 SQLJ에서 자동으로 COMMIT문을 수행한다. 즉, 각 SQL문은 문장 자체가 하나의 트랜잭션이다. SQLJ에서 COMMIT문을 수행하기 때문에 프로그램에서 COMMIT문을 수행할 필요가 없다.

또한 Auto-Commit 기능은 기본 Connection Context 객체를 생성할 때 설정할 수 있다. 그러나 기억해야 할 것은 Auto-Commit 기능은 사용하지 않는 것이 기본 설정이라는 것이다. 이것은 Auto-Commit 기능을 사용하는 것으로 설정하지 않으면, 각 트랜잭션을 끝날 때마다 COMMIT문을 명시적으로 수행해야 한다는 것을 의미한다. Auto-Commit 기능을 사용하도록 설정해야 Commit이 자동적으로 수행된다.

지금까지 여러분은 데이터베이스 스키마에 접속한 SQLJ문을 작성하는 법을 살펴보았다. 여기에는 임베디드 SQL, DML, DDL, 트랜잭션 관리문이 포함되어 있다. 이제부터는 DML, DDL, 트랜잭션 관리 SQL를 사용하는 SQLJ 프로그램 전체를 살펴볼 것이다.

예제 프로그램: FundamentalExample1.sqlj

FundamentalExample1.sqlj 프로그램은 fundamental_user 데이터베이스 사용자로 데이터베이스에 접속하는 방법과 SQLJ에서 실행 가능한 문장을 사용하는 완벽한 SQLJ 프로그램이다. 이 프로그램은 다음 같은 작업을 수행할 것이다.

Oracle.connect() 메소드를 사용하여 fundamental_user 사용자로 데이터베이스에 접속한다.
INSERT문을 사용하여 customers 테이블에 행을 추가한다.
SELECT문을 사용하여 customer 테이블에 추가된 새로운 행의 컬럼 값들을 조회하고, 그 결과를 화면에 출력한다.
DELETE문을 사용하여 customer 테이블에 추가된 새로운 행을 삭제한다.
INSERT와 DELETE로 구성된 트랜잭션을 COMMIT문을 사용하여 데이터베이스 물리적으로 저장한다.
UPDATE문을 사용하여 products 테이블의 한 행의 price값을 변경한다.
SELECT문을 사용하여 products 테이블에서 변경된 price값을 조회하고, 그 결과를 화면에 출력한다.
ROLLBACK문을 사용하여 UPDATA문으로 구성된 트랜잭션을 취소한다.
CREATE TABLE문을 사용하여 addresses 테이블을 생성한다.
DROP TABLE문을 사용하여 addresses 테이블을 삭제한다.
finally 블록에서 Oracle.close() 메소드를 호출하여 데이터베이스와 연결을 종료한다.

다음은 FundamentalExample1.sqlj 프로그램 소스이다.

/*
   The program FundamentalExample1.sqlj illustrates how to 
   connect to a database, how to embed SQL DML operations 
   in SQLJ executable statements, and how to use host 
   expressions.
*/

// import required packages
import java.sql.*;
import oracle.sqlj.runtime.Oracle;

public class FundamentalExample1 {

  public static void main(String [] args) {

    try {

      Oracle.connect(
        "jdbc:oracle:thin:@localhost:1521:orcl",
        "fundamental_user",
        "fundamental_password"
      );

      // add a new customer
      int customer_id = 6;
      String first_name = "Jerry";
      String last_name = "Fieldtop";
      Date dob = new Date(80, 1, 1);
      String phone = "650-555-1222";
      #sql {
        INSERT INTO
          customers (id, first_name, last_name, dob, phone)
        VALUES
          (:customer_id, :first_name, :last_name, :dob, :phone)
      };

      // display new customer
      #sql {
        SELECT
          first_name, last_name, dob, phone
        INTO
          :first_name, :last_name, :dob, :phone
        FROM
          customers
        WHERE
          id = :customer_id
      };
      System.out.println("Customer with id " + customer_id +
        " has the following details:");
      System.out.println("  First name: " + first_name);
      System.out.println("  Last name: " + last_name);
      System.out.println("  DOB: " + dob);
      System.out.println("  Phone: " + phone);

      // delete the customer
      #sql {
        DELETE FROM
          customers
        WHERE
          id = :customer_id
      };

      // commit the transaction
      #sql { COMMIT };

      // update the first product price
      int product_id = 1;
      double product_price = 11.25;
      #sql {
        UPDATE
          products
        SET
          price = :product_price
        WHERE
          id = :product_id
      };

      // display the first product
      int type_id = 0;
      String name = null;
      String description = null;
      double price = 0.0;
      #sql {
        SELECT
          type_id, name, description, price
        INTO
          :type_id, :name, :description, :price
        FROM
          products
        WHERE
          id = :product_id
      };
      System.out.println("Product with id " + product_id +
        " has the following details: ");
      System.out.println("  Type id: " + type_id);
      System.out.println("  Name: " + name);
      System.out.println("  Description: " + description);
      System.out.println("  Price: " + price);

      // rollback the update
      #sql { ROLLBACK };

      // create a table to hold customer addresses
      #sql {
        CREATE TABLE addresses (
          id            NUMBER CONSTRAINT addresses_pk PRIMARY KEY,
          customer_id   NUMBER CONSTRAINT addresses_fk_customers
            REFERENCES customers(id),
          street        VARCHAR2(255) NOT NULL,
          city          VARCHAR2(255) NOT NULL,
          state         CHAR(2) NOT NULL,
          country       VARCHAR2(255) NOT NULL
        )
      };
      System.out.println("Successfully created addresses table.");

      // drop the addresses table
      #sql {
        DROP TABLE addresses
      };

    } catch ( SQLException e ) {

      System.err.println("SQLException " + e);

    } finally {

      try {

        Oracle.close();

      } catch ( SQLException e ) {

        System.err.println("SQLException " + e);

      }

    }

  } // end of main()

}

관련 기사: "SQLJ를 배우자!" 시리즈
• SQLJ 프로그램 개발을 위한 환경 설경하기
• SQLJ를 배우자! - DML 문

명령 프롬프트에서 아래와 입력하여 FundamentalExample1.sqlj 프로그램을 변환하고 컴파일 할 수 있다.

sqlj FundamentalExample1.sqlj

sqlj 명령라인 유틸리티는 FundamentalExample1.sqlj 파일을 SQLJ 런타임 라이브러리 호출을 포함하는 FundamentalExample1.java 파일로 변환한다. 그리고 FundamentalExample.java를 FundamentalExample.class 파일로 컴파일하기 위해 자바 컴파일러인 javac를 호출한다. 필자는 SQLJ 시리즈의 첫 번째 기사인 "SQLJ 프로그램 개발을 위한 환경설정하기"에서 sqlj 명령 및 변환 프로세스에 대해 상세히 다루었다. FundamentalExample 클래스를 실행하면 아래와 같은 내용이 화면에 출력될 것이다.

Customer with id 6 has the following details: 
  First name: Jerry
  Last name: Fieldtop
  DOB: 1980-02-01
  Phone: 650-555-1222
Product with id 1 has the following details: 
  Type id: 1
  Name: Beyond Understanding
  Description: The frontiers of human knowledge
  Price: 11.25
Successfully created addresses table.

필자는 이 기사가 독자들에게 유익하고 재미있는 정보를 제공했기를 바라며 SQLJ에 대한 더 상세한 정보는 『Java Programming with Oracle SQLJ』를 참고하기 바란다.

Jason Price는 현재 오라클 제품 관리자로 재직하고 있으며 데이터베이스 서버, 어플리케이션 서버, 몇몇 CRM 애플리케이션을 포함한 많은 오라클 제품 개발에 참여하고 있다.

TAG :

이전 글 : 에릭 하게만 시리즈 1 - 수치처리 파이썬의 기초

다음 글 : 다중 엔티티 프로그래밍

최신 콘텐츠

한빛출판네트워크

인기 검색어 (최근 1개월간)

책

강의/세미나

채널.H

IT/모바일

SQLJ를 배우자! - DDL 문과 트랜잭션

최근 본 상품0