SQL 문장의 실행

tbPSM 프로그램에서 SQL 문장을 실행하는 방법을 설명합니다.

DCL

트랜잭션은 데이터베이스를 조회하거나 갱신 또는 조작 등을 하는 처리의 기본 단위입니다. 즉, SQL 문장의 처리가 성공하거나 실패하는 일련의 과정을 말합니다. 트랜잭션은 RDBMS의 표준으로써, 일관되지 않은 데이터가 생성되는 것을 방지합니다.

트랜잭션은 원자성(Atomic), 일관성(Consistent), 고립성(Isolated), 영속성(Durable) 등의 특성에 의해 데이터베이스 실행이 보장됩니다.

트랜잭션은 다음과 같은 특징이 있습니다.

트랜잭션은 COMMIT이나 ROLLBACK 문으로 끝납니다. tbPSM 프로그램은 사용자의 필요에 따라 실행 코드부에는 COMMIT 문을 포함시키고, 에러 처리부에는 ROLLBACK 문을 포함시킵니다. 특히 에러 처리부에서는 에러 처리뿐만 아니라 현재 트랜잭션이 갱신 한 내용도 취소할 수 있습니다. 다음은 COMMIT, ROLLBACK 문의 예입니다.

COMMIT;

ROLLBACK WORK RELEASE;

ROLLBACK 문을 이용하여 현재 트랜잭션이 갱신한 내용의 일부만을 취소할 수 있습니다. 이를 위해 저장점(savepoint)을 미리 설정해야 합니다. 다음은 SAVEPOINT 문의 예입니다. SAVEPOINT 문은 ROLLBACK 문에 반드시 포함시켜야 합니다.

SAVEPOINT sp1;
...
ROLLBACK TO SAVEPOINT sp1;

트랜잭션에 고립성의 수준을 설정할 수 있습니다. 고립성은 트랜잭션의 특성 중 하나로 임의의 트랜잭션이 동시에 실행되는 다른 트랜잭션에 영향을 주어서는 안 된다는 것입니다. 다음은 현재 트랜잭션에 고립성의 수준을 설정하고 읽기 작업만을 수행하도록 하는 예입니다.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
... 고립성의 수준(직렬화)을 설정합니다. ....

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
... 읽기 작업만을 수행합니다. ...

트랜잭션에 잠금을 설정할 수 있습니다. 갱신 또는 삭제를 하기 전에 질의 결과의 컬럼에 잠금을 설정하여, 다른 트랜잭션이 액세스하지 못하도록 막으려면 SELECT FOR UPDATE 문을 사용해야 합니다. 다음은 테이블 EMP의 DEPNO 컬럼에 잠금을 설정하는 예입니다.

SELECT * FROM EMP WHERE DEPTNO = deptno FOR UPDATE;

만약 질의가 두 개의 테이블에 조인을 수행하는 데 이중 하나의 테이블에만 잠금을 설정하고 싶다면 FOR UPDATE OF 절을 사용해야 합니다. 사용하는 방법은 FOR UPDATE OF 절에 잠금 설정을 원하는 테이블의 컬럼 이름을 포함하면 됩니다.

다음은 EMP, DEPT 테이블 중에서 EMP 테이블의 SALARY 컬럼에만 잠금을 설정하는 예입니다.

SELECT ENAME, SALARY, DNAME FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = deptno FOR UPDATE OF SALARY;

COMMIT

COMMIT 문을 사용하면 트랜잭션이 끝남과 동시에 해당 트랜잭션에서 수행된 모든 작업이 데이터베이스 에 영구히 반영됩니다. 또한 다른 세션이 해당 트랜잭션에 의해 수행된 내용을 볼 수 있으며, 트랜잭션에 설 정된 잠금이 해제됩니다.

COMMIT 문을 선언하는 방법은 다음과 같습니다.

COMMIT [task];

옵션

설명

task

SQL 문장의 가독성을 높이기 위해 추가하는 옵션

ROLLBACK

ROLLBACK 문을 사용하면 트랜잭션이 끝남과 동시에 해당 트랜잭션이 수행한 모든 작업은 취소되며, 트랜잭션이 가지고 있는 잠금을 해제할 수 있습니다. 세션이 COMMIT 문이나 ROLLBACK 문을 사용하여 현재 트랜잭션을 끝내지 않고 데이터베이스 연결을 끊는다면, 트랜잭션은 시스템에 의해 자동으로 롤백됩니다.

ROLLBACK 문을 선언하는 방법은 다음과 같습니다.

ROLLBACK [task];

옵션

설명

task

SQL 문장의 가독성을 높이기 위해 추가하는 옵션

SAVEPOINT

일반적으로 ROLLBACK 문은 트랜잭션의 전체를 취소합니다. 그러나 SAVEPOINT 문을 사용하면 트랜잭션 의 일부만을 취소할 수 있습니다.

SAVEPOINT 문을 선언하는 방법은 다음과 같습니다.

SAVEPOINT savepoint_name;

항목

설명

savepoint_name

저장점의 이름으로 tbPSM의 식별자

트랜잭션 전체에서 사용되므로 선언부에 선언하지 않음
ROLLBACK 문에서 사용되며 설정된 저장점 이후의 내용을 모두 취소할 수 있음

저장점이 정의되면, 다음과 같은 문법을 통해 정의된 저장점까지 롤백할 수 있습니다.

ROLLBACK [task] TO SAVEPOINT savepoint_name;

ROLLBACK TO SAVEPOINT 문을 사용하면 저장점 이후로 수행된 모든 작업을 롤백할 수 있습니다. ROLLBACKTO SAVEPOINT 문이 실행된 이후에도 저장점은 유지되므로 동일한 저장점으로 다시 롤백할 수 있습니다. 이 때 저장점 이후의 SQL 문장이 가지고 있던 잠금과 자원은 모두 반환됩니다. 그러나 ROLLBACK TO SAVEPOINT 문을 사용해도 트랜잭션은 끝나지 않습니다.

자율 트랜잭션

자율 트랜잭션(Autonomous Transaction)이란 기존에 이미 존재하는 트랜잭션과는 완전히 다른 독립적인 트랜잭션입니다. 자율 트랜잭션에 의해 만들어진 내용은 기존의 트랜잭션에 전혀 영향을 주지도 받지도 않 기 때문에 자율 트랜잭션 내에서 롤백을 수행하더라도 기존 트랜잭션과는 관계없습니다.

이러한 자율 트랜잭션은 현재 트랜잭션이 수행되는 중간에 독립적으로 작업하고자 할 때 유용하며 주로 서브 프로그램으로 만들어 놓고, tbPSM 프로그램을 수행하는 중간에 그 서브 프로그램을 호출함으로써 독립적인 트랜잭션 작업을 수행하게 됩니다.

자율 트랜잭션을 선언할 수 있는 부분은 한정적입니다. 따라서 다음과 같은 위치에서만 선언할 수 있습니다.

이름 없는 블록(Anonymous block)의 중첩되지 않은 최외곽 블록
독립적인 서브 프로그램과 로컬 서브 프로그램
패키지 서브 프로그램
트리거

자율 트랜잭션을 선언하는 방법은 다음과 같습니다.

PRAGMA AUTONOMOUS_TRANSACTION;

자율 트랜잭션은 아래와 같이 선언부에 선언됩니다.

DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
    INSERT INTO emp VALUES (1, 'Peter');
    ...
END;

자율 트랜잭션도 일반적인 트랜잭션과 마찬가지로 DCL을 사용하여 제어할 수 있습니다.

예를 들면 다음과 같습니다.

DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
    INSERT INTO emp VALUES (1, 'Peter');
    update_dept(1, 'marketing'); 
    COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN 
    ROLLBACK;
END;

자율 트랜잭션을 사용할 때 다음과 같은 경우에 주의해야 합니다.

기존 트랜잭션이 사용하고 있는 자원(예: LOCK 등)에 접근하려고 할 때 교착 상태(deadlock)가 발생합니다.
자율 트랜잭션 내부에서 발생한 예외 상황이 처리되지 않았을 때 그동안 수행된 내용이 롤백됩니다.
커밋이나 롤백을 하지 않은 채로 자율 트랜잭션을 종료할 때 예외 상황이 발생합니다.

다음은 트리거에서 자율 트랜잭션을 사용한 예입니다. 일반적인 트리거와는 달리 자율 트랜잭션을 사용하면 EXECUTE IMMEDIATE 절을 사용할 수 있습니다.

CREATE TRIGGER multiply_trg 
AFTER INSERT ON calc
FOR EACH ROW 
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
    INSERT INTO result VALUES (:new.c1 * :new.c2);
    EXECUTE IMMEDIATE 'DROP TABLE tmp_result';
END;

Dynamic SQL

일반적으로 tbPSM에서는 DDL이나 GRANT와 같은 DCL, ALTER SESSION과 같은 세션 관리 제어 문장을 사용할 수 없습니다. 하지만 이를 가능하게 해 주는 것이 Dynamic SQL입니다.

Dynamic SQL은 EXECUTE IMMEDIATE 절에서 사용할 수 있습니다.

EXECUTE IMMEDIATE sql_stmt
    [INTO id_list USING id_list RETURNING INTO id_list];

다음은 Dynamic SQL를 사용한 예입니다.

DECLARE
    sql_stmt VARCHAR2(2000); 
    emp_id NUMBER := 1;
    emp_name VARCHAR2(10) := 'John'; 
BEGIN
    sql_stmt := 'INSERT INTO emp VALUES (:1, :2)';
    EXECUTE IMMEDIATE sql_stmt USING emp_id, emp_name; 
    sql_stmt := 'SELECT name FROM emp WHERE id = :id'; 
    EXECUTE IMMEDIATE sql_stmt INTO emp_name USING emp_id; 
    EXECUTE IMMEDIATE 'CREATE TABLE dept (id NUMBER)';
END;

주의

일반적으로 SQL 문장을 작성할 때에는 세미콜론(;)이 필요 없지만, tbPSM에서 Dynamic SQL를 사 용할 때에는 반드시 세미콜론이 있어야 합니다.

커서

커서(Cursor)란 tbPSM에서 SQL을 수행하기 위해 하나의 문장마다 사용하는 내부 구조를 말합니다.

일반적으로 커서에는 묵시적 커서와 명시적 커서, 커서 변수가 있습니다. 또한 %ISOPEN, %FOUND, %NOTFOUND, %ROWCOUNT의 특별한 속성을 제공합니다.

묵시적 커서

묵시적 커서(Implicit cursor)란 INSERT, UPDATE, DELETE 문장과 SELECT INTO를 비롯한 SQL 문장을 실행할 때 tbPSM에서 내부적으로 사용하는 커서를 말합니다. 하나의 SQL 문장을 수행할 때마다 이 커서가 열리고 닫힙니다.

DML

tbPSM에서는 제약 없이 DML을 사용할 수 있습니다.

BEGIN
    INSERT INTO emp VALUES (1, 'Susan');
    UPDATE emp SET name = 'Peter' WHERE id = 1; 
    DELETE FROM emp WHERE id = 1;
END;

QUERY

tbPSM에서는 SELECT한 SQL 문장의 결과를 변수로 저장할 수 있습니다. 그러나 일반적인 SELECT INTO를 사용하는 경우 아래 예와 같이 한 번에 한 개의 컬럼 밖에 얻어오지 못합니다.

DECLARE
    name VARCHAR2(20); 
BEGIN
    SELECT name INTO name FROM emp WHERE id = 2; 
END;

결과 컬럼이 두 개 이상일 경우에는 TOO_MANY_ROWS 예외 상황이 발생하고, 이와는 반대로 결과 컬럼이 없을 경우에는 NO_DATA_FOUND 예외 상황이 발생합니다.

tbPSM에서는 이러한 문제를 해결하기 위해 결과 컬럼이 여러 개인 경우에도 SQL 문장이 실행될 수 있는 기능을 제공합니다. 이 기능을 커서 반복문(CURSOR_FOR_LOOP)이라고 합니다.

다음은 커서 반복문의 예입니다.

BEGIN
    FOR result IN (SELECT * FROM emp) LOOP
        DBMS_OUTPUT.PUT_LINE(result.id || '번 직원=' || result.name); 
    END LOOP;
END;

명시적 커서

명시적 커서(Explicit cursor)란 사용자가 tbPSM 프로그램 내에서 직접 선언한 커서를 말합니다. 이 커서는 전적으로 사용자에 의해 OPEN, FETCH, CLOSE가 이루어지며, tbPSM은 관여하지 않습니다.

명시적 커서는 일반적인 tbPSM의 변수와 달리 대입할 수 없고, 선택적으로 파라미터를 가질 수 있습니다. 이 파라미터는 이후에 실행되는 SQL에서 사용됩니다.

명시적 커서를 선언하는 방법은 다음과 같습니다.

CURSOR cursor_name IS SELECT ...;
CURSOR cursor_name (param1 TYPE [DEFAULT VALUE], ...) IS SELECT ...;

OPEN

명시적 커서를 열기 위해서는 OPEN 문을 사용해야 합니다.

사용 방법은 다음과 같습니다.

DECLARE
    CURSOR c1 IS SELECT * FROM emp; 
BEGIN
    OPEN c1;
END;

FETCH

명시적 커서를 Fetch하는 방법은 FETCH INTO 문을 사용하며 한 번에 하나의 로우만 받아올 수 있습니다. 사용 방법은 다음과 같습니다.

DECLARE
    emp_rec emp%ROWTYPE;
    CURSOR c1 IS SELECT * FROM emp; 
BEGIN
    OPEN c1;
    FETCH c1 INTO emp_rec; 
END;

CLOSE

명시적 커서를 닫기 위해서는 CLOSE 문을 사용합니다. 만약 이미 닫혔거나 열리지 않은 커서에 대해 CLOSE를 수행합니다면, INVALID_CURSOR 예외 상황이 발생합니다.

DECLARE
    emp_rec emp%ROWTYPE;
    CURSOR c1 IS SELECT * FROM emp; 
BEGIN
    OPEN c1;
    FETCH c1 INTO emp_rec; 
    CLOSE c1;
END;

커서 반복문의 사용

명시적 커서도 커서 반복문을 사용할 수 있습니다.

사용 방법은 다음과 같습니다.

DECLARE
    CURSOR c1 IS SELECT * FROM emp; 
BEGIN
    FOR result IN c1 LOOP
        DBMS_OUTPUT.PUT_LINE(result.id || '번 직원=' || result.name); 
    END LOOP;
END;

패키지에서의 정의

패키지에서는 선언부와 구현부로 나누어 커서를 정의합니다.

사용 방법은 다음과 같습니다.

CREATE OR REPLACE PACKAGE pkg 
IS
    TYPE rec IS record (a number, b varchar2(100), c date); 
    CURSOR c1 RETURN rec;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg 
IS
    CURSOR c1 RETURN rec IS select 1, 'tibero', sysdate from dual; 
END;
/ 
DECLARE
    a NUMBER;
    b VARCHAR2(100); 
    c DATE;
BEGIN
    OPEN pkg.c1;
    FETCH pkg.c1 INTO a, b, c; 
    DBMS_OUTPUT.PUT_LINE(a||','||b||','||c); 
    CLOSE pkg.c1;
END;
/

다음은 명시적 커서의 상태에 따른 커서 속성 값입니다.

상태

%ISOPEN

%FOUND

Before OPEN

false

INVALID_CURSOR

After OPEN

true

NULL

After first FETCH

true

false

After next FETCH

true

false

임의의 값

After last FETCH

true

false

true

임의의 값

After CLOSE

false

INVALID_CURSOR

커서 변수

커서 변수(Cursor variable)란 특정 SQL 문장에만 해당되지 않고, 커서를 열 때 주어진 SQL 문장에 따라 자유롭게 변경하여 사용할 수 있으며, 일반적인 변수처럼 서브 프로그램의 파라미터의 반환값으로 사용되는 커서를 말합니다.커서 변수를 사용하면 많은 양의 데이터를 주고 받는 대신 포인터만 전달하므로 프로 램의 성능을 개선할 수 있습니다.

커서 변수를 선언하는 방법은 다음과 같습니다.

TYPE csr_type_name IS REF CURSOR [RETURN type_name];

csr_var_name csr_type_name;

항목

설명

csr_type_name

REF CURSOR 타입의 이름입니다.

[RETURN type_name]

선택적으로 사용된다.

RETURN 절이 있는 경우에는 타입의 안정성을 갖는 커서가 생성된다.
type_name 즉 질의의 반환 타입은 RETURN 절에서 정한 타입과 호환되어야 합니다.

csr_var_name

커서 변수의 이름입니다.

커서 변수를 선언할 때는 먼저 REF CURSOR 타입을 선언한 후에 이 타입으 로 변수를 선언합니다.

다음은 커서 변수를 선언한 예입니다.

DECLARE
    TYPE ref_csr IS REF CURSOR; 
    c1 ref_csr;

선언된 커서 변수를 열기 위해서는 다음과 같이 사용해야 합니다.

OPEN csr_var_name FOR sql_query;

다음은 선언한 커서 변수를 여는 예입니다.

DECLARE
    TYPE ref_csr IS REF CURSOR; 
    c1 ref_csr;
BEGIN
    OPEN c1 FOR SELECT * FROM emp;
    OPEN c1 FOR SELECT part FROM dept;

커서 변수를 사용할 때 커서의 FETCH와 CLOSE의 사용 방법은 “명시적 커서”와 같습니다.

커서 변수는 다음과 같은 제약조건이 있습니다.

커서 변수는 NULL 허용 여부 검사나 등호 또는 부등호 비교를 할 수 없습니다.
커서 변수에는 NULL을 대입할 수 없습니다.
커서 변수는 테이블(nested table)이나 배열(varray)의 요소가 될 수 없습니다.
커서 변수는 커서 반복문에서 사용될 수 없습니다.

커서 속성

tbPSM은 하나의 SQL 문장을 수행하고 그 결과를 속성으로 저장하여 사용자에게 확인할 수 있도록 4가지의 특별한 커서 속성(Cursor attribute)을 제공합니다.

커서 속성은 묵시적 커서, 명시적 커서에 따라 그 사용 방법이 다릅니다.

묵시적 커서

SQL%{ISOPEN | FOUND | NOTFOUND | ROWCOUNT}

명시적 커서

cursor_name%{ISOPEN | FOUND | NOTFOUND | ROWCOUNT}

%ISOPEN

%ISOPEN은 커서가 OPEN되었는지 여부를 반환하는 커서 속성입니다. 묵시적 커서의 경우 SQL 문장을 수행하고 나서 항상 닫히므로 무조건 FALSE입니다. 반면에 명시적 커서의 경우 사용자가 CLOSE를 했는지의 여부에 따라 반환값이 달라집니다.

BEGIN
    INSERT INTO emp VALUES (1, 'Susan'); 
    IF SQL%ISOPEN = FALSE THEN
        DBMS_OUTPUT.PUT_LINE('묵시적 커서는 항상 자동으로 닫힌다.');
    END IF;
END;

%FOUND, %NOTFOUND

%FOUND와 %NOTFOUND는 INSERT, UPDATE, DELETE에 의해 영향을 받은 로우가 있는지 없는지, SELECT INTO에 의한 결과 로우가 있는지 없는지를 반환하는 커서 속성입니다.

BEGIN
    UPDATE emp SET name = 'John' WHERE id = 1; 
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('1번 직원은 존재하지 않는다.');
    END IF;
END;

%ROWCOUNT

%ROWCOUNT는 INSERT, UPDATE, DELETE에 의해 영향을 받은 로우의 개수나 SELECT INTO의 결과 로우의 개수를 반환하는 커서 속성입니다.

BEGIN
    DELETE FROM emp WHERE id = 1; 
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '로우가 삭제되었으므로 존재하지 않는다.');
    END IF;
END;

SYS_REFCURSOR

SYS_REFCURSOR는 커서 변수를 사용할 때 사용자의 편의를 위해 표준 패키지에서 SYS_REFCURSOR라는 타입을 제공하고 있습니다.

SYS_REFCURSOR는 전역 타입이므로, 사용자가 별도로 약한 타입의 참조 커서 타입을 선언하지 않아도 바로 사용할 수 있습니다.

DECLARE
    cx SYS_REFCURSOR;
BEGIN
    ... 
END;
/

Previous패키지 Next에러 처리

Last updated 17 days ago

hashtagDCL

hashtagCOMMIT

hashtagROLLBACK

hashtagSAVEPOINT

hashtag자율 트랜잭션

hashtagDynamic SQL

hashtag커서

hashtag묵시적 커서

hashtagDML

hashtagQUERY

hashtag명시적 커서

hashtagOPEN

hashtagFETCH

hashtagCLOSE

hashtag커서 반복문의 사용

hashtag패키지에서의 정의

hashtag커서 변수

hashtag커서 속성

hashtag%ISOPEN

hashtag%FOUND, %NOTFOUND

hashtag%ROWCOUNT

hashtagSYS_REFCURSOR

DCL

COMMIT

ROLLBACK

SAVEPOINT

자율 트랜잭션

Dynamic SQL

커서

묵시적 커서

DML

QUERY

명시적 커서

OPEN

FETCH

CLOSE

커서 반복문의 사용

패키지에서의 정의

커서 변수

커서 속성

%ISOPEN

%FOUND, %NOTFOUND

%ROWCOUNT

SYS_REFCURSOR