[번역] pro*c 데이터베이스 개념(Database Concepts) - 01

목 차

▣ 데이터베이스 연결( Connect to the Database )
▣ 연결옵션( Advanced Connection Options )
▣ Definitions of Transactions Terms
▣ How Transactions Guard Your Database
▣ 트렌잭션 시작하고 끝내는 방법(How to Begin and End Transactions)
▣ Commit문의 사용 ( Using the COMMIT Statement )
▣ SAVEPOINT문의 사용 ( Using the SAVEPOINT Statement )
▣ ROLLBACK문의 사용 ( The ROLLBACK Statement )
▣ RELEASE 옵션 ( The RELEASE Option )
▣ SET TRANSACTION문 (The SET TRANSACTION Statement )
▣ Override Default Locking
▣ Fetch Across COMMITs
▣ Distributed Transactions Handling

1) 데이터베이스 연결( Connect to the Database )

Connect문의 사용문법은 다음과 같다.

EXEC SQL CONNECT { :user IDENTIFIED BY :oldpswd | :usr_psw }
[[ AT { dbname | :host_variable }] USING :connect_string ]
[ {ALTER AUTHORIZATION :newpswd | IN { SYSDBA | SYSOPER } MODE} ] ;

Pro*C/C++ 프로그램은 데이타를 취득 및 조작하기전에 데이터베이스와 연결 해야만 한다. 로그온

하기위해서 간단히 아래의 문장 처럼 사용한다.

EXEC SQL CONNECT :username IDENTIFIED BY :password ;

위의 문장에서 username 와 password는 char 변수나 VARCHAR의 호스트 변수를 사용한다.

혹은, 아래의 문장처럼 연결하기도 한다.

EXEC SQL CONNECT :usr_pwd;

위의 문장에서 usr_pwd는 / (slash 문자)로 구분된 사용자명과 비밀번호로 이루어 졌다.

CONNECT문은 프로그램에 의해서 실행되는 처음의 SQL문장이어야만 하며, 아래의 예제처럼

두개의 변수를 사용하여 CONNECT문을 실행할 수 있다. 사용자명과 비밀번호를 사용자 입력으로 받을

수도 있고, 아래의 코딩처럼 하드코딩하여 사용 할 수도 있다.

char *username = "SCOTT";
char *password = "TIGER";
...
EXEC SQL WHENEVER SQLERROR ...
EXEC SQL CONNECT :username IDENTIFIED BY :password;

그렇지만 ,아래의 예제처럼 CONNECT문안에 사용자명과 비밀번호를 하드코딩 할 수는 없다.

EXEC SQL CONNECT SCOTT IDENTIFIED BY TIGER;
EXEC SQL CONNECT ’SCOTT’ IDENTIFIED BY ’TIGER’;

1-1) 비밀번호 변경을 위한 ALTER AUTHORIZATION 절의 사용

Pro*C/C++는 EXEC SQL CONNECT문을 확장해서 실행시 사용자의 비밀번호를 변경할 수 있는

편리한 방법을 제공한다.

일반적인 로그온 방법으로 사용자가 로그인을 했을 경우, 사용자의 기간이 만료되었거나 비밀번호가

틀릴경우가 발생할 수 있다.이런한 경우,

EXEC SQL CONNECT .. ALTER AUTHORIZATION :newpswd;

위의 문장을 사용해서 사용자가 newpswd 로 비밀번호를 변경 할 것을 나타낸다.

2) 연결옵션( Advanced Connection Options )

아래의 그림은 Oracle Net을 이용하여 한개의 로컬 데이터베이스와 3개의 원격 데이터베이스와

통신하는 모습을 도식화 한 것이다.

이번장에서는 다음에 대해서 배울것이다.

◎ 프로그램이 직/간접적으로 다른 데이터베이스를 엑세스하는 방법

◎ 동시적으로 로컬과 리모드의 데이터베이스를 엑세스하는 방법

◎ 같은 데이터베이스에 다중연결을 하는 방법

각각의 노드(node - Network에서 이야기하는 Node의 개념)에는 디폴트 데이터베이스(Default

Database)를 가지고 있다. 이름을 지정하지 않고 Connect문을 사용하면 디폴트 데이터베이스에

연결한다. 기본적인 연결은 AT절 없이 연결을 한다. 기본적으로 설정되지 않은 데이터베이스에

연결을 하고자 할경우, AT절을 이용해서 연결하며 모든 데이터베이스의 이름은 유일해야하면

연결시 같은 이름으로 여러번 설정하여 연결 할 수 있다. 이것이 바로 데이터 베이스의 다중연결을

설정하는 것이다.

2-1) Explicit Connections

▶ Single Explicit Connection
아래의 예제는 원격의 서버에 데이터베이스에 연결하는 것이다.

/*** 필요한 호스트 변수 선언 ***/
char username[10] = "scott";
char password[10] = "tiger";
char db_string[20] = "NYNON";

/*** 유일한 데이터베이스명을 준다. ***/
EXEC SQL DECLARE DB_NAME DATABASE;

/*** 데이터베이스를 연결한다. ***/
EXEC SQL CONNECT :username IDENTIFIED BY :password

AT DB_NAME USING :db_string;

혹은 아래의 예제처럼 , 호스트 변수를 AT절에 사용 할 수 도 있다.

아래의 예제에서는 DECLARE DATABASE 문장이 필요하지 않다.

/*** 필요한 호스트 변수 선언 ***/
char username[10] = "scott";
char password[10] = "tiger";
char db_name[10] = "oracle1";
char db_string[20] = "NYNON";

/* db_name 을 사용해서 데이터베이스 연결하기 */
EXEC SQL CONNECT :username IDENTIFIED BY :password
AT :db_name USING :db_string;
...

권한을 부여하는 SQL문을 실행할 경우 아래와 같이 사용할 수 있다.

EXEC SQL AT DB_NAME SELECT ...
EXEC SQL AT DB_NAME INSERT ...
EXEC SQL AT DB_NAME UPDATE ...

PL/SQL문을 작성해서 사용할 경우 아래와 같은 형태로 사용 가능하다.

EXEC SQL AT :db_name EXECUTE
begin

/* PL/SQL block here */

end;

END-EXEC;

▶ Cursor 제어

OPEN, FETCH, 그리고 CLOSE와 같은 커서제어 문은 예외이다. 이것은 AT절을 사용할 수

없으며, 커서를 사용하려면, DECLARE CURSOR 절에 AT절을 사용해야 한다.

사용예는 아래와 같다.

EXEC SQL AT :db_name DECLARE emp_cursor CURSOR FOR ...
EXEC SQL OPEN emp_cursor ...
EXEC SQL FETCH emp_cursor ...
EXEC SQL CLOSE emp_cursor;

아래의 예에서 처럼 커서가 열렸을때, 관계를 변경 할 수 없다.

EXEC SQL AT :db_name DECLARE emp_cursor CURSOR FOR ...
strcpy(db_name, "oracle1");
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH emp_cursor INTO ...
strcpy(db_name, "oracle2");
EXEC SQL OPEN emp_cursor; /* illegal, cursor still open */
EXEC SQL FETCH emp_cursor INTO ...

위의 경우가 잘못된것은 커서가 열려있는 상태에서 다른 커서를 열려고 하기때문에 생기는

문제이다. 이를 해결하고자 한다면, 열려있는 커서를 닫고 다시 커서를 열어서 사용하는

방법으로 해결 할 수 있다.

...

EXEC SQL CLOSE emp_cursor; -- close cursor first
strcpy(db_name, "oracle2");
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH emp_cursor INTO ...

▶ Dynamic SQL

Dynamic SQL은 커서제어와 비슷하게 AT절을 사용하여 데이터베이스와 연결을 시도한다.

3가지 방법으로 연결하여 사용하는 방법을 예제코드를 살펴보도록 하자.

<Method 1>

EXEC SQL AT :db_name EXECUTE IMMEDIATE :sql_stmt;

<Method 2>

EXEC SQL AT :db_name DECLARE sql_stmt STATEMENT;
EXEC SQL PREPARE sql_stmt FROM :sql_string;
EXEC SQL EXECUTE sql_stmt;

<Method 3>

EXEC SQL AT :db_name DECLARE sql_stmt STATEMENT;
EXEC SQL PREPARE sql_stmt FROM :sql_string;
EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;
EXEC SQL OPEN emp_cursor ...
EXEC SQL FETCH emp_cursor INTO ...
EXEC SQL CLOSE emp_cursor;

▶ Multiple Explicit Connections

아래의 예제처럼 2개의 다른 데이터베이스에 AT절을 이용하여 연결할 수 있다.

/* declare needed host variables */
char username[10] = "scott";
char password[10] = "tiger";
char db_string1[20] = "NYNON1";
char db_string2[20] = "CHINON";
...

/* give each database connection a unique name */
EXEC SQL DECLARE DB_NAME1 DATABASE;
EXEC SQL DECLARE DB_NAME2 DATABASE;
/* connect to the two nondefault databases */
EXEC SQL CONNECT :username IDENTIFIED BY :password
AT DB_NAME1 USING :db_string1;
EXEC SQL CONNECT :username IDENTIFIED BY :password
AT DB_NAME2 USING :db_string2;

아래의 예제는 DB Name과 Db_String을 입력받아 다중으로 데이터베이스에 연결하는

예제 코딩을 보여주고 있다.

/* declare needed host variables */
char username[10] = "scott";
char password[10] = "tiger";
char db_name[20];
char db_string[20];
int n_defs = 3; /* number of connections to make */
...

for (i = 0; i < n_defs; i++)
{
/* get next database name and OracleNet string */
printf("Database name: ");
gets(db_name);
printf("OracleNet) string: ");
gets(db_string);
/* do the connect */
EXEC SQL CONNECT :username IDENTIFIED BY :password
AT :db_name USING :db_string;
}

같은 데이터베이스 이름으로 여러번 연결하기 예제

strcpy(db_string, "NYNON");
for (i = 0; i < ndefs; i++)
{
/* connect to the nondefault database */
printf("Database name: ");
gets(db_name);
EXEC SQL CONNECT :username IDENTIFIED BY :password
AT :db_name USING :db_string;
}
...

2-2) Implicit Connections

Implicit 연결법은 쿼리에서 데이터베이스 link를 이용하여 원하는 데이터 베이스에 연결하여

데이터를 취득해오는 방법을 이야기 한다.

▶ Single Implicit Connections

아래와 같이 데이터베이스 링크를 생성해서 , 생성된 링크를 이용하여 SQL을 작성하고

취득하고자하는 데이터를 취득하는 것이다.

EXEC SQL CREATE DATABASE LINK db_link
CONNECT TO username IDENTIFIED BY password
USING ’NYNON’;

아래의 예제는 데이터베이스 링크를 이용하여 원격의 데이터베이스 서버의 EMP테이블에서 데이터

를취득하는 예제이다.

EXEC SQL SELECT ENAME, JOB INTO :emp_name, :job_title
FROM emp@db_link
WHERE DEPTNO = :dept_number;

데이터베이스 링크를 쉽게 참조하기 위해서, 아래와 같이 SYNONYM을 생성할 수 있다.

EXEC SQL CREATE SYNONYM emp FOR emp@db_link;

SYNONYM을 생성하고 아래와 같은 쿼리를 이용하여 원격에 있는 데이터베이스의 EMP 테이블

에서 데이터를 취득한다.

EXEC SQL SELECT ENAME, JOB INTO :emp_name, :job_title
FROM emp
WHERE DEPTNO = :dept_number;

이것은 위치 EMP테이블에 대한 위치 투명성을 제공한다.

▶ Multiple Implicit Connections

아래의 예제는 동시에 원격 데이터베이스 서버에 접속하는 예제이다.

우선, 두개의 데이터베이스 링크를 생성하고 SYNONYM을 생성한다.

EXEC SQL CREATE DATABASE LINK db_link1
CONNECT TO username1 IDENTIFIED BY password1
USING ’NYNON’;
EXEC SQL CREATE DATABASE LINK db_link2
CONNECT TO username2 IDENTIFIED BY password2
USING ’CHINON’;

EXEC SQL CREATE SYNONYM emp FOR emp@db_link1;
EXEC SQL CREATE SYNONYM dept FOR dept@db_link2;

그다음, 원격의 데이터베이스 서버의 EMP와 DEPT테이블에서 쿼리를 할 수 있다.

EXEC SQL SELECT ENAME, JOB, SAL, LOC
FROM emp, dept
WHERE emp.DEPTNO = dept.DEPTNO AND DEPTNO = :dept_number;

출처 - http://younbok.egloos.com/9342732