[SIST] Oracle_days20

728x90

1. 트랜잭션(Transaction)

1) 트랜잭션(Transaction)이란 일의 처리가 완료되지 않은 중간 과정을 취소하여 일의 시작 전 단계로 되돌리는 기능이다.

2) 결과가 도출되기까지의 중간 단계에서 문제가 발생하였을 경우 모든 중간 과정을 무효화하여 작업의 처음 시작 전 단계로 되돌리는 것

3) 일이 모두 완료되면 알리는 COMMIT 과 일의 취소를 알리는 ROLLBACK 이 쓰인다.

4) DML문을 실행하면 해당 트랜젝션에 의해 발생한 데이터가 다른 사용자에 의해 변경이 발생하지 않도록 LOCK(잠김현상)을 발생한다.

5) LOCK은 COMMIT 또는 ROLLBACK 문이 실행되면 해제된다.

예)

6) A 트랜잭션과 홍길동 트랜잭션 테스트

왼쪽 시트 창에서 UPDATE 문을 실행하고 커밋을 하지 않은 상태로 오른쪽(홍길동 사용자) 시트에서 조회를 해보면 데이터가 변경되지 않은 것을 확인할 수 있다. 즉, DML 문을 실행하면 잠금(LOCK)이 되어 있고 COMMIT, ROLLBACK을 하지 않으면 해제가 안된다는 것을 알 수 있다.

COMMIT 후 오른쪽 시트에서 조회한 결과는 아래와 같다.

변경이 잘되어진 것을 확인할 수 있다.

만약 A 사용자가 DML 실행 후 COMMIT, ROLLBACK을 하지 않고 홍길동 사용자가 DML을 실행하면 어떻게 될까?

A 사용자가 DML문을 실행 후 COMMIT, ROLLBACK 하지 않으면 홍길동 사용자는 DML 문을 실행했을 때 위와 같이 무한 루프에 빠지게 된다.

<결론>

> DML문을 사용하면 자동으로 트랜잭션이 걸린다(LOCK) -> 이상태로 DML문을 실행하면 LOCK 해제가 안되서 무한루프에 빠진다.

> DDL / DCL 실행하면 트랜잭션이 종료된다.

> 데이터베이스 종료시 트랜잭션 종료된다.

7) DEAD LOCK(데드락)

익순이 - 책상수리 중 망치 O, 드라이버 X
익준이 - 부엌수리 중 망치 X, 드라이버 O
> 둘이 없는 것을 달라고 싸움

A : 망치 + 못X UPDATE 실행중
홍길동 : 망치X + 못 UPDATE 실행중

> 서로 필요한 것이 없어서 일을 마치지 못하고 있는 상태

위와 같은 상황을 데드락이라고 한다.

8) DQL 문에서 사용할 수 있는 절 : FOR UPDATE OF

- SELECT 문을 통해 해당 레코드에 lock을 거는 문장이다. (DQL + 트랜잭션(LOCK)

- 해제할 때도 COMMIT, ROLLBACK 사용

- 형식

SELECT *
FROM emp
FOR UPDATE OF JOB NOWAIT;

2. TCL문 SAVEPOINT

- 원하는 위치에서 COMMIT 또는 ROLLBACK을 할 수 있도록 저장위치를 지정하는 것

테스트)

SELECT *
FROM dept;

1) 삭제
SAVEPOINT sp_dept_delete;
DELETE FROM dept WHERE deptno = 60; -- LOCK 상태

2) 추가
SAVEPOINT sp_dept_insert;
INSERT INTO dept VALUES(50, 'AA', 'YY'); -- LOCK 상태

3) 수정
SAVEPOINT sp_dept_update;
UPDATE dept
SET loc = 'SEOUL'
WHERE deptno = 40; -- LOCK 상태

(1) 모든 작업 취소, LOCK 해제
ROLLBACK;

(2) 삭제 작업 빼고 ROLLBACK 하고 싶다
ROLLBACK TO SAVEPOINT sp_dept_insert;

예시에서 sp_dept_insert 라는 SAVEPOINT 까지 작업만 ROLLBACK을 한다는 뜻으로

삭제했던 작업은 취소되지 않는다.

!주의!

DML문 위에 SAVEPOINT를 지정하고 먼저 실행한 후 DML 문을 실행하여야 한다.

3. 동적 SQL(동적 쿼리)

1) 동적 SQL ? 컴파일 시에 SQL 문장이 확정이 되지 않는 경우 -> 실행할 때 SQL 문장 확정

SELECT *
    FORM 게시판 테이블
    -- 제목 검색
    IF 제목 검색할 경우 THEN
        WHERE 글제목 LIKE '길동';
    -- 제목 + 내용 검색
    ELSIF 제목+내용으로 검색할 경우 THEN
        WHERE 글제목 LIKE '길동' OR 내용 LIKE '길동';
    END IF;

    5만 가지의 경우... 위와 같이 선언할 수 없다.. 이럴 때 동적쿼리 사용!

2) WHERE 조건절, SELECT 컬럼.. 이런 항목들이 동적으로 변하는 경우 사용한다.
    SELECT ?,?,?,?
    FROM
    WHERE ? AND ? OR ? ? ?......

3) PL/SQL 에서 DDL(CREATE, ALTER, DROP + TRUNCATE) 문을 실행하는 경우

4) PL/SQL 에서 ALTER SESSION / SYSTEM 명령어를 실행하는 경우 -> DBA가 아닌 이상 거의 쓸 일 없음

5) 동적 쿼리를 사용하는 2가지 방법
(1) 원시 동적 쿼리(Native Dynamic SQL : NDS)
(2) DBMS_SQL 패키지 사용 -> 이건 다음에!

6) 동적 쿼리를 실행 방법
(1) EXECUTE IMMEDIATE 동적쿼리문
[INTO 문] -> INTO 변수명, 변수명..
[USING MODE(IN, OUT, IN OUT) 문] -> USING 파라미터, 파라미터...

7) 동적 쿼리 생성(작성) -> 실행 테스트

    DECLARE
        vdsql VARCHAR2(1000);
        vdeptno emp.deptno%TYPE;
        vempno emp.empno%TYPE;
        vename emp.ename%TYPE;
        vjob emp.job%TYPE;
    BEGIN
        -- ㄱ. 동적 쿼리 작성
        vdsql := 'SELECT deptno, empno, ename, job ';
        vdsql := vdsql || ' FROM emp ';
        vdsql := vdsql || ' WHERE empno = 7369 ';

        -- ㄴ. 동적 쿼리 실행
        EXECUTE IMMEDIATE vdsql INTO vdeptno, vempno, vename, vjob;

        DBMS_OUTPUT.PUT_LINE(vdeptno || ', ' || vempno || ', ' || vename || ', ' || vjob);

    -- EXCEPTION
    END;

<결과>

예시1) 저장프로시저를 사용해서 동적쿼리 작성 및 실행

    CREATE OR REPLACE PROCEDURE up_dselEmp
    (
        pempno emp.empno%TYPE
    )
    IS
        vdsql VARCHAR2(1000);
        vdeptno emp.deptno%type;
        vempno emp.empno%type;
        vename emp.ename%type;
        vjob emp.job%type;
    BEGIN
        -- ㄱ. 동적 쿼리 작성
          vdsql :=  'SELECT deptno, empno, ename, job ';
          vdsql :=   vdsql || 'FROM emp ';
          vdsql :=  vdsql ||  'WHERE empno = :pempno ' ;

        -- ㄴ. 동적 쿼리 실행
        EXECUTE IMMEDIATE vdsql
                    INTO vdeptno, vempno, vename, vjob
                    USING pempno;

        DBMS_OUTPUT.PUT_LINE( vdeptno  || ', ' || vempno || ', ' || vename || ', ' || vjob );

    -- EXCEPTION
    END;

<실행 및 결과>

EXEC up_dselEmp(7369);

예시2) 저장프로시저를 사용해서 동적쿼리 작성 및 실행 (INSERT)

<실행 전 결과>

CREATE OR REPLACE PROCEDURE up_dinsDept
    (
      pdname dept.dname%type
      , ploc dept.loc%type

    )
    IS
        vdsql VARCHAR2(1000);
        vdeptno dept.deptno%type;
    BEGIN

        SELECT MAX(deptno)+10 INTO vdeptno FROM dept;
        -- ㄱ. 동적 쿼리 작성
        vdsql :=  'INSERT INTO dept ';
        vdsql :=   vdsql || ' VALUES ( :deptno, :dname, :loc ) ';

        -- ㄴ. 동적 쿼리 실행
        EXECUTE IMMEDIATE vdsql
                USING vdeptno, pdname, ploc;

        -- COMMIT;

    -- EXCEPTION
    END;

<실행 및 결과 확인>

EXEC UP_DINSDEPT( 'QC', 'SEOUL');

SELECT * FROM dept;

예시3) 저장프로시저를 사용해서 동적쿼리 작성 및 실행 (테이블 생성)

    DECLARE
        vsql VARCHAR2(1000);
        vtableName VARCHAR2(20);
    BEGIN
        vtableName := 'tbl_nds';
        vsql := 'CREATE TABLE ' || vtableName ;
        -- vsql := 'CREATE TABLE :tableName ' ;
        vsql := vsql || ' ( ' ;
        vsql := vsql || '       id number primary key ' ;
        vsql := vsql || '       , name varchar2(20) ' ;
        vsql := vsql || ' ) ' ;

        DBMS_OUTPUT.PUT_LINE(vsql); -- 테스트로 출력시켜보는 것

        EXECUTE IMMEDIATE vsql;
                            -- USING vtableName;
    END;

<결과 확인>

예시4) 저장프로시저를 사용해서 동적쿼리 작성 및 실행 (OPEN FOR 문 사용)

- OPEN FOR 문 설명? 동적 SQL의 실행 결과가 [여러 개의 레코드(행) 반환]하는 [SELECT문] + [커서]

CREATE OR REPLACE PROCEDURE up_nds02
(
    pdeptno dept.deptno%TYPE
)
IS
    vsql VARCHAR2(1000);
    vrow emp%ROWTYPE;
    vcursor SYS_REFCURSOR;
BEGIN
    vsql := 'SELECT * ';
    vsql := vsql || 'FROM emp ';
    vsql := vsql || 'WHERE deptno = :deptno ';

    -- EXECUTE IMMEDIATE 동적쿼리 사용X
    -- OPEN FOR 문 사용한다.
    OPEN vcursor FOR vsql USING pdeptno;
    LOOP
        FETCH vcursor INTO vrow;
        EXIT WHEN vcursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(vrow.empno || ', ' || vrow.ename);
    END LOOP;
    CLOSE vcursor;
END;

<실행 및 결과 확인>

EXEC UP_NDS02(30);