Chapter 6 PL SQL(Procedural Language/SQL) - 4

PLT 6.4 PL/SQL에서 SQL문장

데이터베이스에서 정보를 추출할 필요가 있을 때 또는 데이터베이스로 변경된 내용을 적용할 필요가 있을 때 SQL을 사용합니다. PL/SQL은 SQL에 있는 DML과 TRANSACTION 제어 명령을 모두 지원합니다. 테이블의 행에서 질의된 값을 변수에 할당 시키기 위해 SELECT문장을 사용합니다. DML문장은 다중 행 처리를 할 수 있지만 SELECT문장은 하나의 행만을 처리할 수 있습니다.

PL/SQL에서 SQL문장 사용

• SELECT 명령어를 사용하여 데이터베이스에서 한 행의 데이터를 추출합니다.

• DML명령어를 사용하여 데이터베이스의 행에 대해 갱신할 수 있습니다.

• COMMIT, ROLLBACK, SAVEPOINT명령어로 TRANSACTION을 제어 합니다.

• 암시적인 커서로 DML결과를 결정합니다.

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

• PL/SQL 블록은 TRANSACTION 단위가 아닙니다. COMMIT, ROLLBACK, SAVEPOINT는 블록과는 독립적이지만 블록에서 이 명령어를 사용할 수 있습니다.

• PL/SQL은 CREATE TABLE, ALTER TABLE, DROP TABLE같은 DDL을 지원하지 않습니다.

• PL/SQL은 GRANT, REVOKE과 같은 DCL을 지원하지 않습니다.

PL/SQL에서 SELECT문장

데이터베이스에서 데이터를 읽어 들이기 위해 SELECT문장을 사용합니다. SELECT문장은INTO절이 필요한데, INTO절에는 데이터를 저장할 변수를 기술한다. SELECT 절에 있는Column수와 INTO절에 있는 변수의 수는 좌측부터 1대1대응을 하며 개수와 데이터의 형, 길이가 일치하여야 한다. SELECT 문은 INTO절에 의해 하나의 행만을 저장할 수 있다. 그러므로SELECT 문장에서 조건을 만족하는 ROW가 한 개도 없거나 여러 행이 있으면 에러를 발생한다.

SELECT  select_list INTO  {variable_name1[,variable_name2,..] | record_name} FROM                table_name WHERE  condition; select_list : 열의 목록이며 행 함수, 그룹 함수, 표현식을 기술할 수 있다. variable_name : 읽어 들인 값을 저장하기 위한 스칼라 변수 record_name : 읽어 들인 값을 저장하기 위한 PL/SQL RECORD 변수 Condition : PL/SQL 변수와 상수를 포함하여 열명,표현식,상수,비교 연산자로 구성되며 오직 하나의 값을 RETURN할 수 있는 조건이어야 한다.

질의는 하나의 행만 RETURN 해야 합니다. PL/SQL 블록 내의 SELECT 문장은 다음 규칙을 적용하는 Embedded SQL의 ANSI범주에 속합니다. 질의의 결과는 하나의 행만을 RETURN해야 하고 하나의 행 이상 또는 행이 없는 것은 에러를 생성합니다. PL/SQL은 NO_DATA_FOUND와TOO_MANY_ROWS를 예외로 블록의 예외 섹션에서 추적할 수 있는 표준 예외를 조성하여 처리 합니다.

• SELECT문 사용시 한 개 이상의 ROW가 검색되면 Oracle Server는 미리 정해진EXCEPTION인 TOO_MANY_ROWS라고 부르는 에러 번호 -1422를 발생한다.

• SELECT문 사용시 아무런 ROW도 검색되지 않으면 Oracle Server는 미리 정해진EXCEPTION인 NO_DATA_FOUND라고 부르는 에러 번호 +1403인 발생한다.

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

DML명령어를 사용하여 데이터베이스 테이블에 대한 내용을 변경할 수 있다.

• INSERT문장은 테이블에 데이터의 새로운 행을 추가한다.

• UPDATE문장은 테이블에 존재하는 행을 수정한다.

• DELETE문장은 테이블에서 원치 않는 행을 제거한다.

데이터 삽입

• USER와 SYSDATE같은 SQL함수를 사용합니다.

• 데이터베이스 시퀀스를 사용하여 기본키 값을 생성합니다.

• PL/SQL 블록에서 값을 얻거나 DEFAULT값을 이용합니다.

CREATE SEQUENCE empno_sequence             INCREMENT BY 1             START WITH 8000             MAXVALUE 9999             NOCYCLE             NOCACHE; 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,'JONG','MANAGER',10); END;

데이터 갱신

• 지정 연산자 좌측에 있는 식별자는 항상 데이터베이스 열이지만 오른쪽에 있는 식별자도 데이터베이스 열 또는 PL/SQL에서 사용되는 변수도 기술 가능하다.

• PL/SQL에서의 SELECT문장과 달리 수정된 행이 없으면 에러가 발생하지 않는다.

DECLARE             v_sal emp.sal%TYPE := 1000; BEGIN             UPDATE emp             SET sal = sal + v_sal             WHERE empno = 7369; END;

데이터 삭제

PL/SQL에서 SQL의 DELETE문장을 사용하여 필요 없는 자료를 삭제할 수 있다.

DECLARE             v_empno emp.empno%TYPE := 7654; BEGIN             DELETE emp             WHERE empno = v_empno; END;

이름 지정 규약

• WHERE절에서 모호성을 피하기 위해 이름 지정 규약을 사용한다.

• 데이터베이스 열과 식별자는 다른 이름을 가져야 한다.

• PL/SQL이 테이블의 열을 첫 번째로 조사하기 때문에 구문 오류가 발생할 수도 있다.

COMMIT과 ROLLBACK 문장

COMMIT또는 ROLLBACK SQL문장으로 트랜잭션 논리를 제어 함으로써 데이터베이스를 영구적으로 변경하게 합니다. ORACLE SERVER에서와 마찬가지로 DML 트랜잭션은 COMMIT또는ROLLBACK을 수행한 다음에 시작하고 성공적인 COMMIT또는 ROLLBACK 다음에 종료합니다.

DECLARE             v_empno emp.empno%TYPE := 7934; BEGIN             DELETE emp             WHERE empno = v_empno;             COMMIT; END;

SQL CURSOR

SQL문장을 실행할 때마다 ORACLE SERVER은 명령이 분석되고 실행되는 곳에서 메모리 영역을 개방합니다. 이 영역을 CURSOR라 합니다. 블록의 실행 부분이 SQL문장을 실행할 때PL/SQL은 SQL식별자를 가지는 암시적 CURSOR를 생성합니다. PL/SQL은 자동적으로 이CURSOR를 관리합니다. 명시적 CURSOR는 명시적으로 선언되고 프로그래머에 의해 명명됩니다.

• CURSOR는 개별 SQL 작업 영역입니다.

• CURSOR에는 임시적 커서와 명시적 커서가 있습니다.

• ORACLE SERVER은 SQL문장을 분석하고 실행하기 위해 암시적 커서를 사용합니다.

• 명시적 커서는 프로그램에 의해 명시적으로 선언 됩니다.

CURSOR의 속성

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

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

VARIABLE  rows_deleted VARCHAR2(60) DECLARE             v_ordid           NUMBER := 605; BEGIN             DELETE FROM item                           WHERE ordid = v_ordid;             IF SQL%FOUND THEN                           :rows_deleted := SQL%ROWCOUNT || ' rows deleted.';             ELSE                           :rows_deleted := '삭제한 자료가 없습니다.';             END IF; END; / PRINT rows_deleted