4. PL/SQL 프로그램의 SQL문

4. PL/SQL 프로그램의 SQL문

(오라클 데이터베이스 서버와 상호작용)

 

A. PL/SQL의 SQL문

§SELECT 명령을 사용한 DB 행 검색

§DML 명령을 사용한 DB 행 변경

§COMMIT, ROLLBACK, SAVEPOINT 명령을 사용한 TX 제어

*** END 키워드는 TX의 끝이 아닌, PL/SQL 블록의 끝!

     TX도 다중 블록을 확장할 수 있다.

*** PL/SQL은 DDL, DCL을 ‘직접’ 지원하지 않는다.

     => ‘동적 SQL’을 사용, DDL, DCL 문 실행 가능

 

B. PL/SQL의 SELECT 문

§ROW를 하나의 변수에 가지고 올 때, PL/SQL “RECORD” 변수

§세미콜론( ; )으로 각 SQL 문 종료

§INTO 절로 검색한 값 변수에 저장

§INTO 절 사용시 한 행만 FETCH!! (이 경우 WHILE 필수!)

§INTO 절은 필수!!  위치는 SELECT와 FROM 사이

§반환되는 행이 없거나 여러 개일 경우 오류 발생
-> NO_DATA_FOUND, TOO_MANY_ROWS로 예외 처리

§여러 행을 검색하고 데이터를 조작할 경우, 명시적 커서 사용

ex)

DECLARE

   v_emp_hiredate    employees.hire_date%TYPE;

   v_emp_salary       employees.salary%TYPE;

BEGIN

  SELECT hire_date, salary

  INTO v_emp_hiredate, v_emp_salary

  FROM employees

  WHERE employee_id = 100;

  DBMS_OUTPUT.PUT_LINE (‘Hire date is : ’ ||

    v_emp_hiredate);

  DBMS_OUTPUT.PUT_LINE (‘Salary is : ’ ||

    v_emp_salary);

END;

/

C. 이름 지정 규칙

§WHERE 절에서의 모호성 방지를 위해 사용한다.
[이름의 혼동 가능성은 WHERE절에만 있음]

§DB의 컬럼명을 식별자로 사용하지 않는다.

§PL/SQL은 데이터베이스를 먼저 검색하여 테이블의 컬럼이 있는지를 확인, 구문 오류가 발생할 수 있다.

§DB의 “컬럼명”은 “로컬변수”의 이름보다 우선한다.

§“로컬변수”와 형식 파라미터의 이름은 DB의 “테이블명”보다 우선한다.

 

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

§PL/SQL에서 DML 명령을 제약 없이 실행 가능

§ROW LOCK, TABLE LOCK은 PL/SQL 코드에
COMMIT, ROLLBACK을 넣어 해제

E. 데이터 삽입

§USER, CURRENT_DATE와 같은 SQL 함수 사용 가능

§기존 DB SEQUENCE를 사용하여 PRIMARY KEY 값 생성

§PL/SQL 블록에서 값 받을 수 있음

 ex)

BEGIN

   INSERT INTO employees

      (employee_id, first_name, last_name, email,

    hire_date, job_id, salary)

     VALUES (employees_seq.NEXTVAL, ‘Ruth’, ‘Cores’,

  ‘RCORES’, CURRENT_DATE, ‘AD_ASST’, 4000);

END;

/

F. 데이터 갱신

§할당 연산자 왼쪽에 있는 식별자는 “항상” DB의 컬럼
하지만! 할당 연산자의 오른쪽의 식별자는 DB의 컬럼일 수도,
PL/SQL의 변수일 수도 있음!
(UPDATE의 SET에서의 양쪽의 SALARY)

§WHERE절은 영향을 받는 행을 결정, UPDATE된 행이 없어도
PL/SQL의 SELECT와 달리 오류 발생하지 않음

*** PL/SQL 변수 할당은 :=

     SQL 컬럼의 할당은 =

ex)

DECLARE

   sal_increase      employees.salary%TYPE := 800;

BEGIN

   UPDATE      employees

     SET             salary = salary + sal_increase

   WHERE       job_id = ‘ST_CLERK’;

END;

/

G. 데이터 삭제

DECLARE

   deptno      departments.department_id%TYPE := 120;

BEGIN

   DELETE FROM   departments

   WHERE             department_id = deptno;

END;

/

 

H. 행 병합

 

CREATE TABLE copy_dept

AS select * from departments

WHERE department_id IN(10,20);

 

UPDATE copy_dept

SET department_name = ‘MKT’

WHERE department_id = 20;

 

BEGIN

MERGE INTO copy_dept c

    USING departments d

    ON (d.department_id = c.department_id)

      WHEN MATCHED THEN

    UPDATE SET

    c.department_name = d.department_name,

    c.manager_id = d.manager_id,

    c.location_id = d.location_id

    WHEN NOT MATCHED THEN

    INSERT VALUES (d.department_id,

       d.department_name, d.manager_id, d.location_id);

END;

/

 

 

 

I. SQL 커서

§커서는 ORACLE 서버에서 할당한 전용(PRIVATE) 메모리 영역에 대한 포인터. SELECT 문의 결과 집합을 처리하는데 사용

§커서 유형

§IMPLICIT : ORACLE 서버에서 SQL문을 처리하기 위해
       내부적으로 생성/관리

§EXPLICIT : 프로그래머가 명시적으로 선언

 

 

§EXPLICIT CURSOR

§테이블에서 여러 행을 검색, 검색된 각 행에 포인터를
지정하여 한 번에 한 행씩 처리해야 하는 경우

§PL/SQL의 DECLARE 섹션에서 프로그래머가 선언

§IMPLICIT CURSOR

§CONTEXT AREA 에 대한 포인터

§ORACLE 서버에서 자동 관리하는 커서

§프로그래머에게는 액세스 권한이 없는 커서

§SQL문을 실행해야 할 때 생성

 

§CONTEXT AREA

§ORACLE 서버가 SQL문을 처리하기 위해 할당한
‘전용 메모리 영역’

§SQL문의 구문 분석, 처리

§처리에 필요한 정보와 처리 후에 검색된 정보가 저장

§ORACLE 서버가 내부적으로 관리, USER 제어 불가

 

J. IMPLICIT CURSOR 속성

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

§SQL 커서 속성은 PL/SQL 문에서 사용

§DML 문이 테이블의 행에 영향을 주지 않으면 오류 반환X
SELECT 문이 아무 행도 검색하지 않으면 PL/SQL은 오류 반환

§‘SQL’이라는 접두어 : 묵시적 커서는 PL/SQL에 의해 자동생성, 이름 알 수 없음.
=> 커서 이름 대신 ‘SQL’을 붙여서 사용

 

§SQL%FOUND: 가장 최근의 SQL문이 한 행 이상에 영향
      ->TRUE , 부울 속성

§SQL%NOTFOUND: 가장 최근의 SQL문이 영향 준 행이 없는 경우
        ->TRUE, 부울속성

§SQL%ROWCOUNT: 가장 최근의 SQL문에 의해 영향을 받은 행 수
  -> 정수 값

ex)

DECLARE

   v_rows_deleted VARCHAR2(30);

   v_deptno copy_dept.department_id%type := 20;

BEGIN

   DELETE FROM copy_dept

   WHERE department_id = v_deptno;

   v_rows_deleted := (SQL%ROWCOUNT ||

  ‘ row deleted. ‘);

   DBMS_OUTPUT.PUT_LINE (v_rows_deleted);

END;

/