[Oracle/기본]18. 오라클 서버와 대화

● SQL과 PL/SQL 문장 유형 비교

 

- PL/SQL 블록은 트랜잭션 단위가 아니다. 커밋, 세이브포인트, 롤백은 블록과는 독립적이지만, 블록에서 이 명령어를 사용할 수 있다.

- PL/SQL은 DDL(CREATE, ALTER, DROP, TRUNCATE ..), DCL(GRANT, REVOKE) 을 지원하지 않는다.

- PL/SQL은 DML과 트랜잭션 언어를 사용할 수 있다.

 

● PL/SQL에서 SELECT문장

    PL/SQL 내 SELECT 문장은 하나의 행만 리턴해야 한다.

    INTO 절에서의 출력변수와, 입력변수는 그 개수와 데이터형이 일치해야 한다.

 

SELECT select_list

INTO {variable_name[, variable_nam]... / record_name}

FROM table

WHERE condition;

 

select_list       열 목록

variable_name 읽어 들인 값을 저장하기 위한 스칼라 변수이다.

record_name   읽어 들인 값을 저장하기 위한 PL/SQL RECORD 이다.

table             데이터베이스 테이블 명이다.

condition

 

Ex. DECLARE

        v_deptno NUMBER(2);

        v_loc       VARCHAR2(15);

     BEGIN

        SELECT    deptno, loc

           INTO    v_deptno, v_loc

          FROM   dept

         WHERE dname = 'SALES';

     ...

     END;

 

● PL/SQL을 사용한 데이터 조작

 

1. INSERT

 

DECLARE

   v_empno emp.empno%TYPE;

BEGIN

   SELECT empno_sequence.NEXTVAL

      INTO v_empno

     FROM dual;

   INSERT INTO emp(empno, ename, job, deptno)

      VALUES(v_empno, 'HARDING', 'CLERK', 10);

   END;

 

EMP 테이블에 새 종업원에 대한 정보를 추가한다.

 

2. UPDATE

 

DECLARE

   v_sal_increase emp.sal%TYPE := 2000;

BEGIN

   UPDATE emp

        SET sal = sal + v_sal_increase

    WHERE job = 'ANALYST';

END;

 

EMP 테이블에서 분석가인 모든 종업원의 급여를 올린다.

 

PL/SQL 변수 지정은 항상 := 을, SQL 열 지정은 항상 = 을 사용한다.

 

3. DELETE

 

DECLARE

   v_deptno emp.deptno%TYPE := 10;

BEGIN

   DELETE FROM emp

            WHERE deptno = v_deptno;

END;

 

EMP 테이블에서 부서10에 속하는 행을 삭제한다.

 

● SQL 커서

 

SQL문장을 실행할 때마다 오라클 서버는 명령이 분석되고, 실행되는 곳에서 메모리 영역을 개방하는데 이 영역을 cursor라 부른다.

 

- 암시적 커서 : 블록의 실행 부분이 SQL 문장을 실행할 때, PL/SQL은 SQL식별자를 가지는 암시적 커서를 생성한다.

                    PL/SQL은 자동적으로 이 커서를 관리한다.

- 명시적 커서 : 명시적으로 선언되고 프로그래머에 의해 명명된다.

 

PL/SQL 커서에 적용될 수 있는 이용 가능한 4가지 속성이 있다.

 

● SQL 암시적 커서 속성

 

SQL 커서속성을 사용하여, SQL문장의 결과를 테스트 할 수 있다.

 

SQL%ROWCOUNT	가장 최근의 SQL문장에 의해 영향을 받은 행의 수(정수 값)
SQL%FOUND	가장 최근의 SQL문장이 하나 또는 그 이상의 행에 영향을 미친다면 부울 속성을 TRUE로 평가된다.
SQL%NOTFOUND	가장 최근의 SQL문장이 어떤 행에도 영향을 미치지 않았다면 부울 속성은 TRUE로 평가된다,
SQL%ISOPEN	PL/SQL이 실행된 후에 즉시 암시적 커서를 닫기 때문에 항상 FALSE로 평가된다.

 

SQL커서 속성은 커서가 마지막으로 사용된 때 일어난 일을 평가할 수 있도록 해준다.

예외 섹션에서 사용할 수 있다.

 

Ex. VARIABLE rows_deleted

    DECLARE

       v_ordid NUMBER := 605;

    BEGIN

       DELETE FROM item

       WHERE ordid = v_ordid;

                  :rows_deleted := SQL%ROWCOUNT || ' row deleted.';

                  -- 연결연산자 || 로 연결해도 따옴표를 붙이지 않는다.

    END;

    /

    PRINT rows_deleted

 

    ITEM 테이블에서 지정된 명령 번호를 가지는 행을 삭제한다. 삭제된 행의 수를 인쇄한다.