Write Thinking

1. 개요

 여러 가지 제어 구조를 이용하여 PL/SQL 블럭에 있는 문장들의 논리적 흐름을 변경할 수 있다. 조건에 의해 분기하는 IF문을 이용한 조건 구조와 LOOPING 구조(조건 없이 반복하는 BASIC 루프, 계수를 이용하여 반복을 하는 FOR 루프, 문장이 TRUE인 동안에 반복을 하는 WHILE 루프, 루프를 종료하는 EXIT문)가 있다.

1.1 IF문

 PL/SQL 의 IF 문장은 다른 언어의 IF 문장과 거의 유사하다. 즉 일치하는 조건(TRUE, FALSE, NULL)에 따라 선택적으로 작업을 수행할 수 있게 해준다. TRUE 면 THEN과 ELSE 사이의 문장을 수행하고 FALSE나 NULL이면 ELSE와 END IF 사이의 문장을 수행한다.

1.1.1 Syntax

  condition    BOOLEAN 변수 또는 표현식을 기술할 수 있다. (TRUE, FALSE, NULL)

  statements  하나 이상의 PL/SQL 또는 SQL 문장을 기술한다.

  ELSIF          처음식이 FALSE 또는 NULL일 경우 추가적인 조건이 필요한 경우에 사용   

1.1.2 단순 IF 문장

 조건이 TRUE 이면 THEN 이하의 문장을 실행하고 조건이 FALSE나 NULL이면 END IF 다음 문장을 수행한다.

가) Syntax

문제 1) 이름, 급여, 부서번호를 입력받아 EMP 테이블에 자료를 등록하는 SCRIPT를 작성하여라. 단 10번 부서일 경우 입력한 급여의 20%를 추가하고 초기값이 9000부터 9999까지 1씩 증가하는 SEQUENCE(EMP_EMPNO_SEQ) 작성하여 사용하고 아래의 표를 참고하여라.

Guidelines

 1) 충족하는 조건에 따라 선택적으로 작업을 수행할 수 있다.

 2) 코드를 사용할 때 키워드의 철자를 바르게 기술하시오.(ELSIF, END IF)

 3) 제어의 조건들이 TRUE이면 THEN과 END IF 사이의 관련된 문장들이 수행됩니다.

    그러나 FALSE나 NULL이면 END IF 다음의 문장으로 제어가 넘어 갑니다.

 4) ELSE 절은 한번만 사용 가능 합니다.

 5) 명확성을 위해 조건적으로 실행되는 문장을 들여쓰기 하는 것이 좋습니다.

1.1.3 IF -THEN - ELSE 문장의 실행 흐름

 조건이 TRUE 이면 THEN 부터 ELSE 사이의 문장을 수행하고 제어는 END IF 다음 문장으로 넘어가고 FALSE 나 NULL 이면 ELSE 부터 END IF 사이의 문장을 수행하고 제어는 END IF 다음의 문장으로 이동 된다.

가) Syntax

문제 2) 이름을 입력받아 그 사람의 업무가 MANAGER', 'ANALYST' 이면 급여의 50%를 가산하여 갱신하고 업무가 MANAGER', 'ANALYST'이 아니면 20%를 가산하는 SCRIPT를 작성하여라.

1.1.4 IF-THEN-ELSIF 문장의 실행 흐름

 가능하면 중첩 IF 문장 대신 ELSIF 절을 사용하여라. 코드를 읽고 이해하기가 더 쉬우며 로직을 정확하게 식별됩니다. ELSE 절 안의 작업이 순수하게 다른 IF 문으로 구성된다면 이것은 ELSIF 절을 사용하는 것이 더욱 편리합니다. 조건과 수행이 각각 종료 시에 중첩 END IF에 대해 일일이 요구하지 않음으로써 코드를 더 명확하게 만들어 줍니다.

가) Syntax

문제 3) 이름을 입력받아 업무를 조회하여 업무별로 급여를 갱신하는 SCRIPT를 작성하여라. 단 PRESIDENT : 10%, MANAGER : 20%, ANALYST : 30%, SALESMAN : 40%, CLERK : 50%를 적용한다.

1.2 논리적 조건 설정

 비교 연산자를 써서 숫자, 문자 또는 날짜 식을 결합한 간단한 논리 조건을 만든다. 일반적으로 IS NULL 연산자로 NULL 값을 처리할 수 있다.

1.2.1 식과 비교에서 널(NULL) 논리적 조건 설정

 널 값을 공 문자열로 처리하는 연결식(Concatenation)은 예외이지만 기타 다른 널 값을 포함하는 식은 널 값을 return 하고 IS NULL 비교의 결과는 TRUE나 FALSE로 return된다.

1.2.2 논리 테이블

 논리연산자 AND, OR, NOT을 가지고 단순한 BOOLEAN 조건을 조합함으로써 복잡한 BOOLEAN 조건을 구축할 수 있다.

1.3 LOOP문

 LOOP문은 일련의 문장(SQL, PL/SQL)들을 여러 번 반복하기 위해 많은 편의를 제공한다.

 1) 조건 없이 반복 작업을 제공하기 위한 BASIC LOOP문

 2) COUNT를 기본으로 작업의 반복 제어를 제공하는 FOR LOOP문

 3) 조건을 기본으로 작업의 반복 제어를 제공하기 위한 WHILE LOOP문

 4) LOOP를 종료하기 위한 EXIT문

1.4 BASIC LOOP문

 가장 간단한 루프는 구분 문자인 LOOP 와 END LOOP 사이에 반복되는 문장 부분들로 이루어져 있다. 실행상의 흐름이 END LOOP에 도달할 때마다 그와 짝을 이루는 LOOP문으로 제어가 되돌아간다. 이러한 루프를 무한 루프라 하며, 여기서 빠져나가려면 EXIT 문을 사용한다. 기본 LOOP는 LOOP에 들어갈 때 조건이 이미 일치했다 할지라도 적어도 한번은 문장이 실행된다.

1.4.1 Syntax

1.5 EXIT 문

 EXIT 문을 이용하면 END LOOP 문 다음 문으로 제어를 보내기 때문에 루프를 종료할 수 있다. EXIT는 IF 문 내의 처리 작업으로서 또는 루프 내의 독립적인 문장으로서도 사용할 수 있다. 조건에 따라 루프를 종료할 수 있도록 WHEN 절을 덧붙일 수 있다. EXIT 문에 직면하게 되면 조건이 평가 됩니다. 조건이 TRUE를 RETURN하면 LOOP을 끝내고 LOOP 후의 다음 문장으로 제어를 전달합니다. 기본 LOOP는 여러 개의 EXIT 문장을 포함할 수 있다.

1.5.1) Syntax

문제 4) LOOP문으로 아래와 같이 출력하는 SCRIPT를 작성하여라.

문제 5)EVEN_ODD(ID:NUMBER(4) GUBIN : VARCHAR2(4)) 테이블을 작성하여 START 숫자와 END 숫자를 입력 받아 그 사이의 숫자를 ID에 ID의 숫자가 짝수이면 GUBUN에 "짝수"를 홀수이면 GUBUN에 "홀수"라고 입력하는 SCRIPT를 LOOP문으로 작성하여라.

1.6FOR LOOP 문

 FOR LOOP는 기본 LOOP와 동일한 일반 구조를 가집니다. 그리고 PL/SQL이 수행되는 수를 정하기 위해 LOOP키 워드 앞에 제어문을 기술합니다. FOR LOOP 문에서 사용되는 인덱스는 정수로 자동 선언되므로 따로 선언할 필요가 없다. FOR LOOP 문은 LOOP 을 반복할 때마다 자동적으로 1씩 증가 또는 감소한다. REVERSE는 1씩 감소함을 의미한다.

1.6.1) Syntax

 index_counter    upper_bound나 lower_bound에 도달할 때까지 LOOP를 반복함으로써 1씩 자동적으로 증가하거나 감소되는 값을 가진 암시적으로 선언된 정수입니다.

 REVERSE           upper_bound 나 lower_bound까지 반복함으로써 인덱스가 1씩 감소되도록 합니다.

 lower_bound      index_counter 값의 범위에 대한 하단 바운드값을 지정한다.

 upper_bound     index_counter 값의 범위에 대한 상단 바운드값을 지정한다.

Guidelines

 1) 일련의 문장들은 두 바운드에 의해 카운트가 결정되고 증가될 때마다 실행 됩니다.

 2) 루프 범위의 하단 바운드와 상단 바운드는 리터럴, 변수, 표현식이 가능하지만 정수로 기술되어야 합니다.

 3) 루프 범위의 하단 바운드가 상단 바운드보다 더 큰값이 기술되면 일련의 문장들은 수행되지 않습니다.

 4) 루프 내에서만 인덱스 카운터를 참조할 수 있다. 즉 루프 밖에서는 정의되지 않는다.

 5) 인덱스 카운터의 값을 참조하기 위해서 표현식을 사용한다.

 6) := 좌측에 인덱스 카운터를 기술할 수 없다.

문제 6) FOR 문으로 아래와 같이 출력하는 SCRIPT를 작성하여라.

문제 7) EVEN_ODD(ID:NUMBER(4), GUBUN:VARCHAR(4)) 테이블을 작성하여 START 숫자와 END 숫자를 입력 받아 그 사이의 숫자를 ID에 ID의 숫자가 짝수이면 GUBUN에 "짝수"를 홀수이면 GUBUN에 "홀수"라고 입력하는 SCRIPT를 FOR문으로 작성하여라.

1.7 WHILE LOOP 문

 제어 조건이 TRUE 인 동안만 일련의 문장을 반복하기 위해 WHILE LOOP 문장을 사용한다. 조건은 반복이 시작될 때 체크하게 되어 LOOP 내의 문장이 한번도 수행되지 않을 경우도 있다. LOOP을 시작할 때 조건이 FALSE이면 반복 문장을 탈출하게 된다.

1.7.1 Syntax

 condition          BOOLEAN 변수 또는 표현식을 기술(TRUE,FALSE,NULL)

문제 8) WHILE 문으로 아래와 같이 출력하는 SCRIPT를 작성하여라.

문제 9) EVEN_ODD(ID:NUMBER(4) GUBUN:VARCHAR2(4)) 테이블을 작성하여 START 숫자와 END 숫자를 입력 받아 그 사이의 숫자를 ID에 ID의 숫자가 짝수이면 GUBUN에 "짝수"를 홀수이면 GUBUN에 "홀수"라고 입력하는 SCRIPT를 WHILE문으로 작성하여라.

1.8 중첩 LOOP와 레이블

 여러 단계로 루프를 중첩할 수 있습니다. WHILE 루프 내에서 FOR 루프를, FOR루프 내에서 WHILE 루프를 중첩할 수 있습니다. 대개 중첩 루프가 종결되면 예외가 발생하지 않는 한 둘러싸는 루프가 종결되지 않습니다. 레이블 명은 다른 식별자들과 동일한 규칙을 따릅니다. 레이블은 같은 라인 또는 다음 라인에서 문장 앞에 위치됩니다. 레이블 구분 문자 안에 LOOP라는 글자 앞에 레이블을 위치 시킴으로써 루프를 레이블 시킵니다. 루프가 레이블이 되면 END LOOP 문장 후에 루프 이름을 선택적으로 쓸 수 있습니다.

1.8.1) 사용 예

1. PL/SQL에서 SQL문장

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

1.1 PL/SQL 에서 SQL문장 사용

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

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

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

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

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

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

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

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

1.2 PL/SQL에서 SELECT 문장

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

1.2.1 Syntax

 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를 예외로 블록의 예외 섹션에서 추적할 수 있는 표준 예외를 조성하여 처리 합니다.

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

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

문제 1) 이름을 입력받아 급여와 입사일을 출력하는 SCRIPT를 작성하여라.

참고

 위 문제에서 SET VERIFY OFF은 old 와 new 값을 출력하지 않고, SET SERVEROUTPUT ON은 DBMS_OUTPUT 이라는 PACKAGE 내의 PUT_LINE 함수를 사용한다. 이 함수를 SQL*Plus 에서 사용하려면 환경 변수를 사용하기 위하여 사용하였다.

문제 2) 부서번호를 입력받아 급여의 합을 출력하는 SCRIPT를 작성하여라. 

Guidelines

 1) 세미콜론(;)으로 개별 SQL 문장을 종료한다.

 2) SELECT 문장이 PL/SQL에 내장될 때 INTO 절을 사용한다.

 3) WHERE 절은 선택적이며 입력변수, 상수, 리터럴, 또는 PL/SQL의 표현식을 지정하기 위해 사용될 수 있다.

 4) SELECT 절에서의 데이터베이스 열과 INTO 절에서의 출력 변수의 수를 동일하게 좌측부터 1대1대응되게 지정해야 한다.

 5) 식별자의 데이터형과 열의 데이터형을 갖도록 보증하기 위해 %TYPE 속성을 사용합니다. INTO 절의 변수의 데이터형과 변수의 수는 SELECT에서 기술한 Column의 수와 일치하여야 합니다.

 6) 그룹 함수는 테이블의 행 그룹에 적용되기 때문에 SUM 같은 그룹 함수는 SQL에서 사용합니다.

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

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

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

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

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

1.3.1 데이터 삽입

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

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

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

문제3) 초기값이 8000부터 9999까지 1씩 증가하는 SEQUENCE(EMPNO_SEQUENCE)를 생성하여 EMP 테이블에 등록하는 SCRIPT를 작성하여라. 단 이름은 JONG, 업무는 MANAGER, 부서번호는 10 이다.

1.3.2 데이터 갱신

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

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

문제 4) 사원번호가 7369인 사원의 급여에 1000을 더하여 갱신하여라.

1.3.3 데이터 삭제

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

문제 5) 사원번호가 7654인 사원의 정보를 삭제하여라.

1.4 이름 지정 규약

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

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

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

1.5 COMMIT과 ROLLBACK 문장

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

1.6 SQL CURSOR

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

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

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

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

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

1.6.1 CURSOR의 속성

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

문제 6) ITEM 테이블에서 ORDID가 605인 자료를 모두 삭제하여라.

1. 변수

 SQL과 절차적인 문장 안에서 PL/SQL로써 변수를 선언할 수 있고 그것을 사용할 수 있다.

1.1 변수 사용

 변수는 자료를 일시적으로 저장하고 변경하고 검증하기 위해 하나 또는 그 이상의 변수를 선언하여 사용한다. 또한 변수는 데이터베이스를 액세스하지 않고 계산이나 다른 데이터 조작에 사용할 수 있다. 이러한 변수는 일단 선언되면 다른 선언적 문장을 포함한 다른 문장에서 간단하게 그것을 반복적으로 참조하여 사용할 수 있다.

 %TYPE와 %ROWTYPE을 사용하여 변수를 선언하면 테이블의 구조가 변경(데이터형과 길이)되어도 Application에서는 실행 시간에 테이블을 참조하여 변수가 정의되므로 데이터의 독립성, 유지비용 절감을 제공하고, 새로운 업무 요구에 충족시키기 위해 데이터베이스 변경에 따라 프로그램의 적응, 수정되는 것을 허용한다.

1.2 PL/SQL에서 변수 처리

 1) 선언 섹션 내에서 변수를 선언하고 초기화하여 사용합니다.

 2) 실행 섹션에서 변수에 대한 새 값을 할당 합니다.

 3) 매개변수를 통해 PL/SQL 블록으로 값을 전달합니다.

 4) 출력 변수를 통해 결과를 봅니다.

문제 1) EMp 테이블에 EMPNO_SEQUENCE의 SEQUENCE를 이용하여 이름, 급여, 부서번호를 입력받아 등록하는 SCRIPT를 작성하여라. 단 10번부서는 입력된 급여에20%의 가산하여 등록하고 30번부서는 10% 가산 점이 있다.

1.3 변수 유형

 모든 PL/SQL 변수는 저장 포맷, 제약 조건, 값의 유효 범위를 지정하는 데이터형을 가지고 있다. PL/SQL은 변수, 상수, 포인터를 선언하기 위해 사용할 수 있는 4가지 데이터형(Scalar, Composite, Reference, LOB(large objects:Oracle8)을 지원합니다.

1.3.1 PL/SQL 변수

 1) Scalar : 주로 단일 값을 보유합니다. 주요 데이터형은 ORACLE SERVER 테이블의 열 유형에 대응하는 것들입니다.

 2) Composite : 레코드 같은 조합 데이터형은 PL/SQL 블록에서 조작되고 정의되는 필드 그룹을 허용합니다.

 3) Reference : 참조 데이터형은 pointer 라 불리며 다른 프로그램 항목을 지시하는 값을 보유합니다.

 4) LOB(large objects) : LOB 데이터형은 locator라 불리며 라인 밖에서 지정된 큰 객체의 위치를 지정하는 값을 보유합니다.

1.3.2 Non-PL/SQL 변수

 1) Bind와 host variables

1.4 PL/SQL 변수 선언

 PL./SQL 블록에서 그것을 참조하기 전에 선언 섹션에서 모두 PL/SQL 식별자를 선언할 필요가 있습니다. 초기값을 할당하기 위해 옵션을 가집니다. 변수를 선언하기 위해 변수에 대한 값을 할당할 필요는 없습니다. 선언에서 다른 변수를 참조한다면 이전 문장에서 개별적으로 그것들을 반드시 선언해 놓아야 합니다.

1.4.1 Syntax

 identifier         변수의 이름

 CONSTANT      변수의 값을 변경할 수 없도록 제약합니다.

 datatype          Scalar, Composite, Reference, LOB(large objects)

 NOT NULL       값을 포함해야만 하도록 하기 위해 변수를 제약 합니다.

 Expression        Literal, 다른 변수, 연산자나 함수를 포함하는 표현식

1.4.2 사용 예

Guideline

 1) SQL 객체에 대해 사용된 동일한 이름 지정 규칙에 따라 식별자의 이름을 지정한다.

 2) 이름 지정 규약을 사용할 수 있습니다. (예: v_name는 변수를 나타내고 c_name 는 상수를 나타낸다.)

 3) NOT NULL 로 지정된 변수를 초기화 합니다.

 4) 지정 연산자(:=)를 사용하거나 예약어 DEFAULT 를 사용하여 식별자를 초기화 합니다.

 5) 한 라인에 하나의 식별자만 선언 합니다.

 6) 상수 선언에서 CONSTRAINT는 형 지정자보다 먼저 기술되어야 한다.

1.5 이름 지정 규칙

 1) 하나의 블록에서 동일 이름의 변수를 선언할 수 없습니다.

 2) 블록이 다르면 동일 이름을 선언할 수 있습니다. 객체들이 동시에 존재하는 곳에서는 현재 블록에서 정의된 객체들만이 사용될 수 있습니다.

 3) 변수에 대한 이름을 블록에서 사용되는 테이블 열의 이름과 동일하게 선택해서는 안됩니다. PL/SQL 변수가 SQL 명령에서 사용되고 열과 동일 이름을 가지면 ORACLE SERVER은 참조되는 열로 간주 합니다.

1.6 변수의 값 지정

 변수의 값을 지정하거나 재지정하기 위해 PL/SQL 지정 문자를 사용합니다. 지정 연산자(:=)의 좌측에 새 값을 받기 위한 변수를 적습니다.

1.6.1 Syntax

1.6.2 사용 예

주의

 Oracle Server 에서 DEFAULT 날짜 형식 설정이 데이터베이스마다 차이가 있을 수 있기 때문에 DEFAULT 날짜 형식을 알아야 한다. DEFAULT 날짜 형식이 YY-MM-DD 이면 v_hiredate := '99-01-01'로 값을 설정하여야 한다. 일반적으로 날짜의 형식에 의존하지 않고 사용하고자 할 경우에는 v_hiredate := TO_DATE('99-01-01','YY-MM-DD');을 사용한다.

1.7 스칼라 데이터 형

 1) 단일 값을 유지 합니다.

 2) 내부적인 구성 요소는 없습니다.

1.7.1 기본 스칼라 데이터 형

참고

 LONG 데이터 형은 LONG 값의 최대 길이가 32767 바이트인 것을 제외하고는 VARCHAR2와 유사합니다. 그러므로 32760 바이트보다 더 긴 값은 LONG 데이터베이스 열에서 LONG PL/SQL 변수로 사용할 수 없습니다.

1.7.2 스칼라 변수 선언의 예

1.7.3 %TYPE 속성

 변수의 데이터 형과 정밀도를 직접 코딩하기 보다는 이전에 선언된 다른 변수 또는 데이터베이스 열에 맞추어 변수를 선언하기 위해 %TYPE 속성을 사용할 수 있습니다. 변수에 저장되는 값이 데이터베이스의 테이블에서 오거나 변수가 테이블에 쓰여지기로 되었다면 %TYPE 속성은 자주 사용됩니다. 변수 선언에서 필요한 데이터형 대신에 속성을 사용하려면 데이터베이스 테이블과 열 이름을 접두어로 사용합니다. 또한 이전에 선언된 변수를 참조한다면 속성 앞에 변수명을 기술합니다. 데이터베이스 수준에서 테이블의 데이터형을 변경하여도 PL/SQL을 고칠 필요가 없습니다.

가) 사용 예

1.7.4 BOOLEAN 변수 선언

 1) TRUE, FALSE, NULL 값만을 BOOLEAN 변수에 대해 지정할 수 있습니다.

 2) 변수는 논리연산자 AND, OR, NOT 에 의해 접속 됩니다.

 3) 변수는 항상 TRUE, FALSE, NULL을 생성 합니다.

 4) 산술, 문자, 날짜 표현식은 BOOLEAN 값을 리턴하기 위해 사용될 수 있습니다.

가) 사용 예

1.8 조합 데이터 형(Composite Datatype)

 Composite Datatype은 내부 구성 요소를 갖고 있고 PL/SQL 에서 사용할 수 있는 Composite Datatype은 RECORD, TABLE, 중첩 TABLE, VARRAY 입니다. RECORD 데이터형은 관련은 있으나 서로 다른 데이터형들을 논리적인 하나의 단위로 묶기 위해 사용하고 TABLE 데이터 형은 전체 객체로써 데이터형이 같은 데이터의 모음을 참조하고 조작하기 위해 사용한다. 한번 정의되면 테이블과 레코드는 재이용할 수 있다.

1.8.1 PL/SQL TABLE TYPE

 테이블형의 객체는 PL/SQL 테이블이라 불립니다. PL/SQL 테이블은 행에 대해 배열처럼 액세스하기 위해 기본키를 사용합니다. 배열과 유사하고 PL/SQL 테이블을 액세스하기 위해 BINARY_INTEGAR 데이터형의 기본키와 PL/SQL 테이블 요소를 저장하는 스칼라 또는 레코드 데이터형의 열을 포함하여야 한다. 또한 이들은 동적으로 자유롭게 증가할 수 있습니다.

가) Syntax

 table_type_name         테이블형의 이름

 column_type              VARCHAR2, DATE, NUMBER과 같은 스칼라 데이터 형

 identifier                   전체 PL/SQL 테이블을 나타내는 식별자의 이름

문제 2) TABLE 변수를 사용하여 EMP 테이블에서 이름과 업무를 출력하여라.

1.8.2 PL/SQL RECORD TYPE

 PL/SQL RECORD TYPE은 데이터베이스의 테이블 ROW와 다르고 3GL 에서의 RECORD 나 STRUCTURE와 유사하다. PL/SQL RECORD는 Scalar, PL/SQL RECORD, PL/SQL TABLE 데이터 타입 중 하나 이상의 요소를 갖고 있어야 하며, 다른 데이터 타입을 가질 수도 있다. 또한 FIELD(ITEM)들의 집합을 하나의 논리적 단위로 처리할 수 있게 해 주므로 테이블의 ROW를 읽어올 때 편리하다.

가)Syntax

 type_name        RECODE형의 이름, 이 식별자는 RECODE 를 선언하기 위해 사용ㅎ나다.

 field_name        RECODE내의 필드명

나) RECORD 참조

 RECORD에서 필드는 이름으로 액세스 됩니다. 개별 필드를 참조하거나 초기화 하기 위해 ","을 사용합니다.

다) RECORD 에 대한 값 할당

 SELECT 또는 FETCH 문장을 사용함으로써 RECORD에 값을 지정할 수 있다. 열이름의 RECORD의 필드와 동일한 순서로 1대1 대응을 하여야 합니다. 두 RECORD가 동일한 구조를 가지면 하나의 RECORD를 다른 RECORD에 지정할 수 있습니다.

문제 3) EMP 테이블에서 이름을 입력 받아 아래의 형태로 출력하는 SCRIPT를 작성하여라.

Guidelines

 1) 각 RECORD는 필요한 만큼 많은 필드를 가질 수 있다.

 2) RECORD는 초기값을 지정할 수 있고 NOT NULL 로 정의될 수 있다.

 3) 초기값이 없는 필드는 NULL로 초기화 됩니다.

 4) DEFAULT 키워드는 필드를 정의할 때 사용될 수 있습니다.

 5) 임의의 블록 서브 프로그램, 패키지의 선언 부분에 RECORD 형을 정의하고 사용자 정의 RECORD를 선언할 수 있다.

 6) 중첩 RECORD를 선언하고 사용할 수 있다. RECORD는 다른 RECORD의 구성 요소가 될 수 있다.

1.8.3 %ROWTYPE 의 속성

 데이터베이스의 테이블 또는 VIEW의 일련의 열을 RECORD로 선언하기 위하여 %ROWTYPE를 사용합니다. 데이터베이스 테이블 이름을 %ROWTYPE 앞에 접두어를 붙여 RECORD를 선언하고 FIELD는 테이블이나 VIEW의 COLUMN명과 데이터 타입과 LENGTH을 그대로 가져올 수 있다.

가) Syntax

 identifier          RECORD에 대해 지정된 이름

 reference          RECORD의 기초가 되는 테이블, VIEW, CURSOR, 변수 명을 기술

나) 개별 피드를 참조하는 방법

다) %ROWTYPE을 사용 시 장점

 1) 알지 못하는 데이터베이스 COLUMN의 개수와 데이터 형식을 모르게 지정할 수 있다.

 2) 실행 시 변경되는 데이터베이스 COLUMN의 개수와 데이터 형식을지정할 수 있다.

 3) SELECT 문장으로 행을 검색할 때 유리하다.

문제 4) EMP 테이블에서 이름을 입력 받아 아래의 형태로 출력하는 SCRIPT를 작성하여라.

1,9 LOB Datatype 변수

 LOB Datatype 변수는 Oracle8 데이터형으로 구조화 되지 않은 데이터(텍스트, 그래픽, 이미지, 비디오 클립, 소리 웨이브 폼 같은 ) 블록을 4 기가 바이트 크기까지 저장할 수 있습니다. LOB Datatype은 데이터에 대한 랜덤 액세스를 지원합니다.

 1) CLOB(character large object) 데이터형은 데이터베이스 내의 단일 바이트 문자 데이터의 대형 블록을 저장하기 위해 사용한다.

 2) BOB(binary large object) 데이터형은 행의 안팎에 데이터베이스 내의 대형 이진 객체를 저장하기 위해 사용됩니다.

 3) BFILE(binary file) 데이터형은 데이터베이스 외부의 운영 시스템 파일의 대형 이진 객체를 저장하기 위해 사용됩니다.

 4) NCLOB(national language character large object) 데이터형은 데이터베이스 내의 단일 바이트, 또는 고정 길이의 멀티바이트 NCHAR 데이터를 행의 안팎에 저장하기 위해 사용됩니다.

1.10 바인드 변수

 바인드 변수는 호스트 환경에서 선언된 변수이며, 실행 시간 값을, 그것이 숫자이든지 또는 문자이든지 임의의 다른 변수처럼 그것을 사용할 수 있는 하나 이상의 PL/SQL 프로그램의 내부나 외부에서 전달하기 위해 사용합니다. 문장이 프로시저, 함수, 패키지 안에 있지 않는다면, 호출 환경 또는 호스트에서 선언된 변수를 PL/SQL 문장에서 참조할 수 있습니다. 이것은 선행 컴파일러 프로그램에서 선언된 호스트 언어 변수, Develover/2000Forms 어플리케이션의 화면 필드, SQL*Plus 바인드 변수를 포함합니다.

1.10.1 바인드 변수 생성

 SQL*Plus 환경에서, 바인드 변수를 선언하기 위해 VARIABLE 명령을 사용합니다. SQL 과 SQL*Plus는 바인드 변수를 참조할 수 있고 SQL*Plus는 그 값을 출력할 수 있습니다.

가) Syntax

1.10.2 바인드 변수 출력

 SQL*Plus 환경에서 바인드 변수의 현재 값을 출력하기 위해 PRINT 명령을 사용합니다.

1.11 Non-PL/SQL 변수 참조

 호스트 변수를 참조하기 위해 선언된 PL/SQL 변수와 호스트 변수를 구별하기 위해 콜론(;)으로 참조 접두어를 기술하여야 한다.

2. PL/SQL 블록

 PL/SQL 은 프로그램을 논리적인 블록으로 나누게 하는 구조화된 블록 언어이다. PL/SQL 블록에서 사용하는 변수는 블록에 대해 논리적으로 선언할 수 있고 사용할 수 있다. 변수들과 그 외의 식별자를 참조하고 선언함으로써 PL/SQL 블록 내에서 값을 저장하고 변경할 수 있는 논리적인 단위이다.

2.1 PL/SQL 블록 구문과 지침

 PL/SQL은 SQL의 확장이기 때문에 SQL에 적용하는 일반적인 구문은 PL/SQL 언어에 대해서도 적용 가능하다. 문장은 몇 라인 이상 계속될 수 있습니다. 문법적인 요소는 공백으로 분리될 수 있습니다.

2.1.1 식별자

 식별자는 상수, 변수, 예외, 커서, 커서 변수, SUBPROGRAM, 패키지를 포함하는 PL/SQL 프로그램 항목과 요소를 명명하기 위해 사용됩니다.

 1) 식별자는 30문자까지 포함할 수 있지만 알파벳 문자로 시작해야 합니다.

 2) 블록에서 사용된 테이블의 열 이름과 동일한 이름으로 식별자를 사용할 수 없습니다.

 3) PL/SQL 식별자가 동일 SQL 명령에 있고 열로 동일 이름을 가지고 있다면, 이때 ORACLE은 참조 중인 열로 간주합니다.

 4) 예약어는 더블 인용 부호에 둘러싸여 있지 않으면 식별자로서 사용될 수 없습니다.

 5) 에약어는 읽기 쉽도록 대문자로 사용합니다.

2.1.2 구분 문자

 구분 문자는 PL/SQL에 대한 특수한 의미를 가지는 단순 상징이거나 혼합 상징입니다.

2.1.3 Literal

 Literal은 식별자(identifier)로 표현되지 않은 숫자, 문자, 문자열, BOOLEAN 값입니다.

 1) 문자 Literal은 PL/SQL 문자 집합 내에서 인쇄 가능한 모든 문자를 포함합니다.

 2) 숫자 Literal은 단순 값(예:-32.5) 또는 과학적인 표기법

2.1.4 주석 코드

 주석 코드 각 단계를 문서화하고 디버깅을 돕기 위해 코드에 주석을 기술합니다. 주석이 단일 라인에 있으면 두 개의 대쉬(--)을 기술하면 -뒤에 기술된 것은 주석으로 인식 하고, 주석 범위가 여러 줄이라면 기호 /*와 */ 사이에 주석을 기술 한다. 주석은 철저하게 정보를 제공해야 하고 기능적인 논리 또는 데이터에 대한 어떤 조건 또는 기능을 강요해서는 안됩니다. 좋은 주석은 읽기 쉽게 하고 코드 유지를 위해 매우 중요합니다.

2.1.5 PL/SQL 에서 SQL 함수

 1) SQL 에서 이용 가능한 대부분의 함수는 PL/SQL 표현식에서도 유효합니다.

  ① 단일 행 숫자 함수

  ② 단일 행 문자 함수

  ③ 데이터형 변환 함수

  ④ 데이트 함수 그 밖의 함수

 2) 아래 함수는 절차적인 문장에서는 사용 불가능합니다.

  ① GRATEST, LEAST, DECODE.

  ② 그룹 함수(AVG, MIN, MAX, COUNT, SUM, STDDEVE, VARIANCE)는 테이블에서 행 그룹에 적용되므로 PL/SQL 블록에 있는 SQL 문장에서만 이용 가능합니다.

2.2 데이터형 변환

 PL/SQL은 데이터형이 문장에서 혼합되었다면 동적으로 데이터형 변환을 시도합니다. 예를 들면 NUMBER값을 CHAR 변수가 지정되었다면 그것이 CHAR 변수에 저장될 수 있도록 하기 위해 PL/SQL은 동적으로 숫자를 문자로 변환 합니다. 문자 표현을 숫자 값으로 나타내는 역의 상황도 적용됩니다. DATE 변수에 대해 문자 값을 지정할 수 있고 역의 상황도 적용됩니다. 혼합된 데이터형이 표현식에서 생성되면 데이터를 전환하기 위해 해당 변환 함수를 사용하여야 합니다.

2.2.1 데이터형 변환 함수의 종류

 1) TO_CHAR(value,fmt) : value 를 문자로 전환

 2) TO_DATE(value,fmt) : value를 날짜 형식으로 전환

 3) TO_NUMBER(value,fmt) : value를 숫자로 전환

Guidelines

 PL/SQL은 가능한 한 데이터형 변환을 시도하지만 성공은 수행 중인 작업에 달려있다. 명시적으로 데이터의 형 변환을 시도하는 것은 상당히 성능에 영향을 미치고 소프트웨어 버전이 변경되더라도 유효하게 유지될 수 있기 때문에 좋은 프로그램이 될 수 있다.

2.3 중첩 블록과 변수 범위

 1) 문장은 실행 명령이 허용하는 곳 어디에서든지 중첩될 수 있습니다.

 2) 중첩 블록은 하나의 문장이 됩니다.

 3) 예외 섹션에서도 중첩 블록을 사용할 수 있습니다.

 4) 객체 범위는 객체를 참조할 수 있는 프로그램 영역 입니다.

 5) 선언된 변수를 실행 섹션에서 참조할 수 있습니다.

 6) 한정시키지 않은 변수는 참조할 수 있는 영역에서 변수를 참조 가능합니다.

  ① 블록은 둘러싸는 블록을 참조할 수 있습니다.

  ② 블록은 둘러싸인 블록을 참조할 수 없습니다.

2.4 PL/SQL 에서 연산자

 연산자는 논리, 산술, 연결, 연산 제어 명령인 괄호, 지수 연산자가 있다.

2.4.1 연산 명령

 표현식에서의 연산은 그것들의 우선 순위에 따라 특별한 순서로 행해 집니다.

2.4.2 사용 예

Guidelines

 NULL 로 작업할 때 아래 규칙을 명심함으로써 몇 가지 일반적인 실수를 피할 수 있습니다.

 1) 관계 연산자로 NULL을 비교하면 항상 NULL이 됩니다.

 2) NULL에 논리 연산자 NOT을 적용하면 항상 NULL이 됩니다.

 3) 조건 제어 문장에서 조건이 NULL이 되면 관련된 문장은 실행되지 않습니다.

2.5 프로그래밍 지침 사항

 PL/SQL 블록을 개발할 때 명확한 코드 생성과 유지를 경감하기 위하여 프로그래밍 지침을 수행 합니다.

2.5.1 코드 규약

2.5.2 코드명지정 규약

2.5.3 코드 들여쓰기

 명확성을 위해 또는 읽기 쉽도록 하기 위해 코드를 각 단계별로 들여씁니다. 구조를 보여주기 위해 Carriage return을 사용합니다.

1.PL/SQL 개요

 PL/SQL(Procedural Language/SQL)은 최근의 프로그래밍 언어의 특성을 수용한, SQL의 확장이라 할 수 있다. SQL의 데이터 조작(DML)과 질의문(QUERY)을 블록 구조에 절차적 단위(IF,LOOP,FOR 등)로 된 코드를 포함할 수 있으며 절차적 프로그래밍을 가능하게 한 강력한 TRANSACTION 처리 언어이다.

1.1 PL/SQL의 장점

 PL/SQL 은 SQL 로는 얻을 수 없는 절차적 언어의 기능을 가지고 있다.

1.1.1 프로그램 개발의 모듈화

 1) 블록 내에서 논리적으로 관련된 문장들의 그룹화할 수 있다.

 2) 강력한 프로그램을 작성하기 위해 서브 블록들을 큰 블록에 포함할 수 있다.

 3) 복잡한 문제에 대한 프로그래밍이 적절히 나뉘어진 모듈들의 집합으로 구성된다.

1.1.2 식별자 선언

 1) 변수, 상수 등을 선언하고 SQL 과 절차적인 프로그램에서 사용한다.

 2) 데이터베이스의 테이블과 Record를 기반으로 하는 dynamic 한 변수 선언이 가능하다.

 3) 단일형 데이터 타입과 복합형 데이터 타입을 선언할 수 있다.

1.1.3 절차적 언어 구조로 된 프로그램 작성

 1) IF문은 조건에 따라 일련의 문장을 실행한다.

 2) LOOP문을 사용하여 일련의 문장을 반복적으로 실행한다.

 3) Explicit Cursor를 이용한 Milti-row 질의 처리한다.

1.1.4 ERROR 처리

 1) Exception 처리 루틴을 이용하여 Oracle8 Server 에러를 처리한다.

 2) 사용자 정의 에러를 선언하고 Exception 처리 루틴으로 처리 가능하다.

1.1.5 성능 향상

 1) PL/SQL은 응용 프로그램의 성능을 향상 시킬 수 있다.

 2) PL/SQL은 여러 SQL 문장을 BLOCK 으로 묶고 한번에 BLOCK 전부를 서버로 전송하기 때문에 통신량을 줄일 수 있다.

1.1.6 PL/SQL의 처리

 PL/SQL 로 작성된 Block을 Oracle Server로 보내면 그 안에 있는 PL/SQL Engine 이 SQL문과 Non SQL 문을 구분하여 Non SQL 문은 PL/SQL Engine 내의 Procedurral statement executor가 수행하고 SQL 문은 SQL statement executor가 처리하게 된다. 즉 Non SQL 문은 Client 환경에서 처리되고 SQL 문은 서버에서 실행하게 된다. 따라서 PL/SQL 을 사용하게 되면 서버쪽으로 작업의 양을 줄이게 되므로 Network traffic를 감소시켜 performance를 증가시키는 이점이 있다.

1.2 PL/SQL Block 구조

 PL/SQL 은 프로그램을 논리적인 블록으로 나누게 하는 구조화된 블록 언어이다. PL/SQL 블록은 선언부(선택적), 실행부(필수적), 예외 처리부(선택적)로 구성되고 BEGIN과 END는 키워드로 반드시 기술하여야 한다. PL/SQL 블록에서 사용하는 변수는 블록에 대해 논리적으로 선언할 수 있고 사용할 수 있다. 변수들과 그 외의 식별자를 참조하고 선언함으로써 PL/SQL 블록 내에서 값을 저장하고 변경할 수 있다.

기본적인 PL/SQL Block은 세 부분으로 구성된다.

Guidelines

 1) PL/SQL Block 내에서는 한 문장이 종료할 때마다 세미콜론(;)을 기술한다.

 2) END뒤에 세미콜론(;)을 사용하여 하나의 Block이 끝났다는 것을 명시한다.

 3) PL/SQL Block의 작성은 편집기를 통해 파일로 작성할 수 있고 SQL*Plus에서 바로 작성할 수 있다.

 4) SQL Buffer 에서 PL/SQL을 실행하기 위해 "/"을 사용하며 성공적으로 실행 된다면 PL/SQL procedure successfully completed라는 Message가 출력된다.

1.2.1 PL/SQL 블록의 유형

 PL/SQL의 모든 단위는 하나 또는 그 이상의 블록을 포함합니다. 이 블록은 다른 것으로부터 하나로 완전히 분리되어 중첩될 수 있습니다. 기본 단위 (프로시저, 함수, 서브 프로그램, 익명의 블록)는 임의의 수의 중첩된 서브 블록을 포함할 수 있는 논리적인 블록으로 구성된다. 그러므로 하나의 블록은 다른 블록의 작은 부분이 되기도 하고 또는 코드 단위의 전체중 일부가 될 수도 있습니다.

가) Anonymous Block(익명 블록)

 이름이 없는 블록을 의미한다. 그것은 실행하기 위해 프로그램 안에서 선언되고 실행 시에 실행을 위해 PL/SQL 엔진으로 전달됩니다. 선행 컴파일러 프로그램과 SQL*Plus 또는 서버 관리자에서 익명의 블록을 내장할 수 있습니다.

나) Subprogram(procedure, function)

 Subprogram은 매개변수를 사용할 수 있고 호출할 수 있는 PL/SQL BLOCK이다. Procedure 또는 Function 로 선언될 수 있습니다. 일반적으로 어떤 작업을 수행하기 위해 Procedure을 사용하고 값을 계산하기 위해 Function을 사용합니다. 서버 또는 Application 수준에서 Subprogram을 저장할 수 이습니다. Developer/2000을 사용하여 Application의 일부로써 Procedure와 Function을 선언할 수 있고 필요할 때마다 Trigger를 이용하여 사용할 수 있다.

1.2.2 프로그램의 구성

1.2.3 PL/SQL 환경

 PL/SQL 은 별개의 Oracle 제품이 아니라 Oracle8 서버와 다른 Oracle TOOL에 이용되고 있는 프로그래밍 언어이다. PL/SQL 의 블록은 Oracle8 서버나 툴에 내장되는 PL/SQL 엔진에 전달되어 처리된다. 사용하는 엔진은 PL/SQL 이 수행되는 곳에 따라 다르다.

가) Oracle8 서버에서의 PL/SQL 엔진

 Pro*프로그램, ISER-EXIT, SQL*Plus, 또는 Server Manager 에서 PL/SQL 블록을 사용하면 Oracle8 서버의 PL/SQL 엔진이 처리한다. 그리고 블록에 있는 SQL 을 별도의 문장으로 분리하여 SQL 문 실행기로 보낸다. 이는 응용 프로그램의 블록을 한 번에 Oracle8 서버에게 보낸다는 뜻이며 따라서 client/server 환경하에서 많은 성능 향상을 기대할 수 있다.

나) Oracle 툴에서의 PL/SQL

 Developer/2000을 포함한 많은 Oracle 툴은 Oracle7 서버에 있는 엔진과는 별도로 자체 PL/SQL 엔진을 갖고 있다. 이 엔진이 SQL 분장을 찾아서 Oracle7 서버의 SQL 문 실행기로 보내고, PL/SQL engine 은(데이터베이스라기보다는 이미 클라이언트 환경에 있는) 응용 프로그램에 대해 지역적인 데이터를 처리한다. 이로써 Oracle7 서버의 대한 작업량과 요구되는 메모리 커서의 수를 줄인다.

참고

  Developer/2000 응용 프로그램의 부분으로 선언된 프로시저와 함수의 일반적인 구조는 동일하더라도 데이터베이스에 저장된 것과는 다르다. Stored Sibprogram은 데이터 베이스 객체이고 데이터 사전에 저장되며 여러 응용 프로그램이 사용할 수 있다. Application subprogram 그 응용 프로그램의 지역적인 PL/SQL 엔진에 블록을 전달한다. 작업은 서버 쪽이 아닌 응용 프로그램 쪽에서 수행된다.

1.3 SQL*Plus로 하는 일

 1) SQL 명령과 PL/SQL 블록의 입력, 편집, 저장, 검색 및 실행을 해준다.

 2) 데이터 베이스의 데이터 access를 가능하게 한다.

 3) 계산수행, query 결과를 보고서 양식으로 출력한다.

 4) SQL 데이터베이스 언어와 절차적 언어의 확장인 PL/SQL 을 SQL*Plus 프로그램을 이용하여 사용할 수 있다.

 5) SQL*Plus 는 SQL 명령과 PL/SQL 블럭을 조작하는 등 많은 부가적인 작업을 수행할 수 있게 해 준다.

ERD?

- 1976년 Chen에 의해 제안된 E-R 모델은 개념적 모델에서 가장 일반적으로 사용되고 있는 모델로서 그 모델이 지니고 있는 단순성 때문에 현재 광범위한 응용 분야에서 사용

- 데이터에 대해 관리자, 사용자, 프로그래머들이 서로 다르게 인식되고 있는 뷰들을 하나로 통합할 수 있는 단일화된 설계안

- 서로 다른 뷰들을 충족시킬 수 있는 데이터 처리와 제약조건 등의 요구사항 들을 정의

- 최종 사용자의 관점에서 데이터베이스를 그림형태로 묘사한 것으로서, 세 개의 기본요소인 개체, 관계, 그리고 속성으로 구성

구성요소

Entity

- 단순히 우리가 데이터 수집의 대상이 되는 정보 세계에 존재하는 사물(thing)

- 개념적 개체 : 장소, 사건 등과 같은 눈에 보이지 않는 것

- 물리적 개체 : 물건 등과 같은 눈에 보이는 것, 현실 세계에 존재하는 사물

- 직사각형으로 나타내고 이름을 붙임

Weak Entity

- 그 개체의 키가 자체적으로 존재하지 않음

- Weak entity는 관계를 맺는 다른 개체에 있는 키를 계승 받음

Attribute

- 객체의 구조를 나타내는 특성들을 의미

 ①simple attribute   ②Composite attribute

- 직사각형에 실선으로 연결된 타원형으로 표현

Relationship

- 개체들 사이에 존재하는 연관성을 의미

 ①Unary relationship  ②Binary relationship  ③Ternary relationship

- 두 개체들을 연결하는 실선들이 있고 그 사이에 마름모 모양의 표시

Connectivity

- 한 개체가 관계를 통하여 다른 개체와 관련되는 개체들의 수

 ①One to one  ②One to many  ③Many to many

Cardinality

- 관계에 참여하는 하나의 개체에 대해 다른 개체형에서 몇개의 개체가 참여하는지를 나타냄

설계과정

- ERD 설계 과정은 모델링의 대상이 되는 조직체의 담당자(고객)와 인터뷰를 하여 데이터를 수집 및 분석한 결과인 데이터베이스 요구사항을 가지고 시작

- 기본이 되는 개체, 관계, 속성들을 규명

- 설계를 해나가는 과정에서 새로운 개체, 관계, 속성 등의 요소들이 발견되었다면 이를 ERD에 추가해야 하며, 또한 불필요한 요소들이 발견되었다면 이를 ERD 에서 삭제해야 하는 반복적인 작업

- 설계자나 고객의 요구사항이 완전히 표현되었다고 볼 때까지 반복

1. 데이터베이스 설계의 단계

 아래 질문에 대한 해결방안을 찾아가는 과정이 DB 설계 단계이다.

- 중요한 데이터는 무엇인가?

- 데이터를 어떻게 표현할 것인가?

- 데이터를 어디에 저장할 것인가?

2. ERD 작성(1)

- 데이터 요구사항 수집과 분석

 ①업무 기술서 검토

 ②현업 장표 검표

 ③현업 업무 전문가와의 면담

 ④기 구축된 시스템 산출물 검토

- 요구사항 정리(예)

 ①여러 학과가 있다.

 ②각 학과에 대해서 번호와 이름이 있다.

 ③각 학과는 여러 명의 교수들이 소속되며, 각 교수에 대하여 번호와 이름이 있다.

 ④각 교수는 여러 과목을 강의하며, 각 과목에 대해서 번호, 이름, 학점이 있다.

 ⑤각 학과는 여러 명의 학생들이 소속된다.

 ⑥각 학생에 대하여 번호, 이름, 주소, 학년, 전화번호가 있다.

 ⑦각 학생은 여러 번 등록을 할 수 있으며 등록 시 번호와 등록 일이 있다.

 ⑧각 학생은 등록 후 여러 과목을 수강하며 중간, 기말 성적이 기록된다.

-----------------------------------------------------------

테이블 설계서

1. 정의

 논리 설계 단계에서는 대략의 데이터 크기를 산정하여 소요 디스크량을 추정하고 지역 분산시의 기초 자료로 사용하기 위하여, 지역별 Raw 데이터 크기, 월증가량, 디스크 보관 주기 등을 산출해낸다.

 물리 설계 단계에서는 이러한 내용을 토대로 하여 운영 환경에 필요한 실제 디스크 소요량을 산정하고 디스크 상의 I/O(Input/Output) 부하 분산을 위한 기초 자료로 이용할 수 있도록 테이블 크기, 테이블스페이스 크기 등을 파악하며, 이를 근거로 실제로 디스크에 할당하는 작업을 수행한다.

2. 절차

3. 테이블 설계

3.1 데이터 건수 및 용량 산정

 1) 데이터 건수와 길이 산정

 - 테이블 크기를 산정하기 위한 양식을 업무 담당자에게 배포하여 데이터 건수와 산정근거를 조사한다.

 - 데이터 없이 빈 테이블을 구성하여 SQL문을 통하여 정확한 테이블별 row의 길이를 구할 수 있다.

 2) 테이블 총크기 산정

 - 조사된 데이터 건수와 길이를 바탕으로, 계산 공식이 반영된 Excel 양식을 이용하여 자동으로 산정한다.

 - Row 길이에 헤더 사이즈를 감안하여 평균 길이를 구한다.

 - 30% 정도의 여유공간을 감안하여 테이블의 총크기를 구한다.

3.2 테이블의 파라미터 값 결정

 - 파라미터 값 결정

  <INITIAL 결정>

  <PCTFREE 결정>

  <기타 파라미터>

1. 사용자 접근 제어

 다중 사용자 환경에서는 데이터베이스 액세스와 사용의 보안 유지가 요구 됩니다.

 1) 데이터베이스 액세스 제어

 2) 데이터베이스에서 특정 객체에 대한 액세스 제공

 3) 오라클 데이터 사전으로 주어지고 받는 Privilege 확인

 4) 데이터베이스 객체에 대한 동의어 생성

1.1 데이터베이스 보안의 두 범주

1.1.1 시스템 보안

 사용자에 의해 허용된 시스템 작업 같은 시스템 수준에서의 데이터베이스의 액세스와 사용을 규정합니다.

 1) 사용자 명

 2) 사용자의 비밀 번호

 3) 사용자에게 할당된 디스크 공간

1.1.2 데이터 보안

 객체에 대해 사용자가 할 수 있는 작업을 규정합니다.

1.2 사용자 생성

 DBA는 CREATE USER 문장을 사용하여 사용자를 생성 합니다. 사용자는 생성성 후 어떠한 권한도 가지지 않습니다. DBA는 이때 그 사용자에게 여러 권한을 부여 합니다. 이 권한은 데이터베이스 수준에서 사용자가 할 수 있는 것이 무엇인가를 결정 합니다.

1.2.1 Syntax

문제 1) 사용자 명은 COm, 패스워드는 COM인 사용자를 생성하고 CONNECT, RESOURCE 권한을 부여하여라.

1.3 권한

 권한은 특정 SQL 문장을 실행하기 위한 권한입니다. 데이터베이스 관리자는 데이터베이스와 그 객체에 대한 액세스를 사용자에게 부여하는 능력을 가진 상급 사용자 입니다. 사용자는 데이터베이스에 액세스하기 위해 system privilege 가 필요하고 데이터베이스에서 객체의 내용을 조작하기 위해 object privilege가 필요합니다. 사용자는 관련 권한들의 이름있는 그룹인 role 이나 다른 사용자에게 추가적으로 권한을 부여하기 위해 권한을 가질 수 있습니다.

참고

 스키마는 테이블, 뷰, 시퀀스 같은 객체의 모음입니다. 스키마는 데이터베이스 사용자에 의해 소유되고 사용자와 동일 이름을 가집니다.

1.3.1 시스템 권한

 1) 사용자와 ROLE에 대해 부여할 수 있는 시스템 권한의 종류는 80개 이상이 있다.

 2) 시스템 권한은 주로 DBA가 부여한다.

 3) DBA는 상급의 시스템 권한을 가집니다.

  ① 새로운 사용자 생성(CREATE USER)

  ② 사용자 제거(DROP USER)

  ③ 테이블 제거(DROP ANY TABLE)

  ④ 테이블 백업(BACKUP ANY TABLE)

가) Syntax

 system_privilege    시스템 권한

 user_name           사용자 명

 WITH ADMIN OPTION 받은 시스템 권한을 다른 사용자에게 부여할 수 있는 권한

나) 시스템 권한의 종류

문제 2) SCOTT에게 CREATE ROLE 권한을 부여하여라.

1.3.2 시스템 권한 최소

 REVOKE 명령으로 시스템 권한을 취소할 수 있다. WITH ADMIN OPTION을 통해 부여된 권한은 취소되지 않는다.

가) Syntax

문제 3) SCOTT에게 부여된 CREATE ROLE권한을 취소하여라.

Guidelines

 사용자가 WITH ADMIN OPTION으로 권한을 부여 받았다면 그 사용자는 WITH ADMIN OPTION으로 권한을 부여해줄 수 있어 수여자 간의 긴 체인이 가능하지만 소유자가 다른 사용자에게 부여한 권한을 취소하면 모든 권한을 연이어 취소되지 않는다.

1.3.3 객체 권한

 1) 객체 권한은 객체마다 다양하다.

 2) 객체 소유자는 객체에 대한 모든 권한을 가지고 있다.

 3) 소유자는 사용자 객체에 대한 특정 권한을 제공할 수 있습니다.

주의

 DBA는 일반적으로 시스템 권한을 할당합니다. 객체를 소유한 모든 사용자는 객체 권한을 부여할 수 있습니다. WITH GRANT OPTION으로 부여 받은 권한은부여자에 의해 다른 사용자와 ROLE에게 다시 부여될 수 있습니다. WITH GRANT OPTION으로 테이블을 질의할 수 있고 테이블에 행을 추가할 수 있도록 해 줍니다. 테이블의 소유자는 PUBLIC 키워드를 사용하여 모든 사용자에게 액세스 권한을 부여할 수 있습니다.

나) OBJECT 권한의 종류

문제 4) 앞에서 생성한 COM 사용자에게 SCOTT이 소유하고 있는 EMP 테이블을 조회하고 삽입할 수 있는 권한을 부여하여라.

1.3.4 객체 권한 철회

 다른 사용자에게 부여된 권한을 철회하기 위하여 REVOKE 문장을 사용합니다. WITH GRANT OPTION을 통해 다른 사용자에게 부여된 권한도 같이 취소된다.

가) Syntax

 CASCADE CONSTRAINTS REFERENCES    권한을 사용하여 만들어진 객체에 대한 참조 무결성 제약 조건을

                                                       제거하기 위해 사용한다.

문제 5) 앞에서 EMP 테이블에 부여한 SELECT 권한을 COM에게서 취소하여라.

Guidelines

 사용자가 WITH GRANT OPTION 으로 권한을 부여 받았다면 그 사용자는 WITH GRANT OPTION으로 권한을 부여해줄 수 있어 수여자 간의 긴 체인이 가능하지만 원형 부여는 허용되지 않는다. 소유자가 다른 사용자에게 부여한 권한을 취소하면 모든 권한을 연이어 취소된다.

1.3.5 부여된 권한 확인

문제 6) SCOTT에게 할당되어 있는 SYSTEM ROLE를 확인 하여라.

1.4 ROLE의 개념

 ROLE은 사용자에게 허가할 수 있는 관련된 권한들의 그룹이다. 이러한 ROLE을 이용하면 권한 부여와 회수를 쉽게 할 수 있다. 한 사용자가 여러 ROLE을 엑세스할 수 있고 다른 여러 사용자에게 같은 ROLE을 지정할 수있다. ROLE을 생성하기 위해서는 CREATE ROLE 권한 또는 DBA 권한이 필요하다.

1.4.1 ROLE의 작성과 지정 순서

 1) 먼저 DBA가 ROLE을 생성한다.

 2) ROLE에 권한을 지정한다.

 3) 사용자에게 ROLE을 부여한다.

1.4.2 Syntax

 role_name        생성되는 ROLE의 이름

문제 7) LEVEL 1이라는 ROLE을 생성하여라.

문제 8) LEVEL 1 이라는 ROLE 에 CREATE SESSION, CREATE TABLE, CREATE VIEW의 권한을 부여하여라. 

문제 9) TEST1/TIGER1 과 TEST2/TIGER2 라는 사용자를 생성하여라

문제 10) TEST1, TEST2에 LEVEL1이라는 ROLE를 부여하여라.

1. 인덱스(INDEX)의 개요

 Oracle8 Server의 인덱스는 포인터를 사용하여 행의 검색을 촉진시킬 수 있는 스키마 객체입니다. 인덱스는 명시적 또는 자동적으로 생성 할 수 있으며 Column에 대한 인덱스가 없으면 한 테이블 전체를 검색하게 될 것입니다. 인덱스는 QUERY 이나 COumn의 집합에 값이 유일하도록 하기 위해 인덱스를 사용할 수 있다.

1.1 인덱스의 특징

 1) 인덱스는 테이블의 값을 빠르게 액세스 하도록 하는 데이터베이스 객체이다.

 2) 데이터를 빠르게 찾기 위한 B*TREE을 써서 디스크 입출력 횟수를 줄인다.

 3) Oracle8 Server가 인덱스를 자동적으로 사용하고 유지 보수 한다.

 4) 인덱스를 만들면 사용자가 직접 조작할 필요가 없게 된다.

 5) 인덱스는 논리적으로도 물리적으로도 테이블과는 독립적이다.

 6) 언제든지 생성하거나 삭제할 수 있으며 이는 테이블이나 다른 인덱스에 영향을 주지 않는다는 의미이다.

참고

 B*INDEX에 대하여

 1) 어떤 행에 대한 액세스 횟수도 동일하게 하는 이진의 균형 탐색 TREE이다.

 2) 행이 테이블 시작이나 중간, 또는 끝에 있어도 거의 같은 홋수 내에 지정된 값을 액세스 하는 효율적인 방법이다.

 3) Oracle8 Server가 만드는 인덱스는 TREE에 정렬된여러 개의 저장소 PAGE로 구성된다.

 4) 각 페이지는 키 값이 데이터 자체를 가리킬 때까지 구조의 아래 쪽으로 향하는 페이지에 대한 포인터와 일련의 키을 갖고있다.

1.2 인덱스 생성 방법

1.2.1 자동 생성

 테이블 정의에 PRIMARY KEY 나 UNIQUE 제약 조건을 정의할 때 unique 인덱스가 자동적으로 생성된다.

1.2.2 사용자가 생성

 행에 대한 액세스 속도를 빠르게 하기 위해 column에 non_unique 인덱스 또는 unique 인덱스를 생성한다.

1.3 인덱스의 종류

1.4 사용자가 인덱스 생성

 CREATE INDEX 문장을 이용함으로써 하나 이상의 열의 인덱스를 생성합니다.

1.4.1 Syntax

1.4.2 인덱스 생성을 위한 지침

가) 많은 것이 항상 더 좋은 것은 아니다.

 테이블의 많은 인덱스가 질의의 스피드 향상을 꼭 의미하는 것은 아닙니다. 인덱스를 가지고 있는 테이블에 대한 각 DML 작업은 인덱스도 갱신되어야 함을 의미합니다. 많은 인덱스가 테이블과 관련되어 있으며, ORACLE SERVER은 DML 후에 모든 인덱스를 갱신하기 위해 더 많은 노력이 필요하게 됩니다.

나) 언제 인덱스를 생성하는가?

 1) 열은 WHERE 절 또는 조인 조건에서 자주 사용됩니다.

 2) 열은 광범위한 값을 포함합니다.

 3) 열은 많은 수의 null 값을 포함합니다.

 4) 둘 또는 이상의 열은 WHERE 절 또는 조인 조건에서 자주 함께 사용됩니다.

 5) 테이블은 대형이고 대부분의 질의들은 행의 2~4%보다 적게 읽어 들일 것으로 예상됩니다.

다) 언제 인덱스를 생성해서는 안되는가

 1) 테이블이 작다.

 2) 열의 질의의 조건으로 자주 사용되지 않는다.

 3) 대부분의 질의들은 행의 2~4%이상을 읽어 들일 것으로 예상된다.

 4) 테이블은 자주 갱신됩니다. 테이블에 하나 이상 인덱스를 가지고 있다면 테이블을 엑세스하는 DML 문장은 인덱스의 유지 때문에 상대적으로 더 많은 시간이 걸리게 됩니다.

참고

 유일성을 강행하기를 원한다면, 테이블 정의에 유일한 제약 조건을 정의해야 함을 명심하십시오. 이때 유일한 인덱스는 자동으로 생성됩니다.

문제 1) EMP 테이블에서 ename 열에 인덱스를 생성하여라

1.5 인덱스 생성 확인

 1) USER_INDEXES는 인덱스 이름과 고유성 정보를 가지고 있다.

 2) USER_IND_COLUMNS는 인덱스 명, 테이블 명, 열명을 가지고 있다.

문제 2) EMP 테이블에 이미 생성되어 있는 인덱스, 관련된 열명, 고유성 여부를 출력하여라.

참고

 Oracle에서의 최적화 방법

 가) 규칙 기준(Rule-base) 최적화

   SQL 문장을 싱행하기 위한 계획(Execution plan)을 선택할 때 내부적인 규칙에 근거하여 적절한 INDEX를 선정하여 사용하는 방식

 나) 비용 기준(Cost-base) 최적화

   SQL 문장을 실행하기 위한 계획(Execution plan)을 선택할 때 인덱스가 있는 테이블의 통계치를 분석하여 가장 비용이 적게 드는 방식으로 액세스 경로를 결정한다.

1.6 인덱스 제거

 인덱스를 수정할 수 없습니다. 인덱스를 변경하기 위해서는, 그것을 제거하고 다시 작성해야 합니다. DROP INDEX 문장을 생성하여 데이터 사전에서 인덱스 정의를 제거합니다. 인덱스를 제거하기 위해서는 인덱스의 소유자이거나 DROP ANY INDEX 권한을 가지고 있어야 합니다.

1.6.1 Syntax

문제 3) emp_ename_idx 인덱스를 삭제하여라

Guidelines

 1) 인덱스를 수정할 수는 없다.

 2) 인덱스를 변경하려면 삭제한 다음 다시 만들어야 한다.

 3) DROP INDEX 명령을 사용하여 인덱스를 삭제하라.

 4) 인덱스를 삭제하려면 그 인덱스의 소유자이거나 DROP ANY INDEX 권한을 가지고 있어야 한다.

2. 동의어

 다른 사용자가 소유한 테이블을 참조하기 위해서는 동의어를 생성한 이름 뒤에 점을 찍고 테이블 이름을 써야 합니다. 동의어 생성은 스키마 이름까지 명시할 필요를 제거시키고 테이블, 뷰, 시쿼스, 프로시저, 또는 다른 객체에 대한 또 다른 이름을 제공 합니다. 이 방법은 뷰처럼 긴 이름을 가진 객체한테 유용하게 사용될 수 있습니다.

2.1 Syntax

 PUBLIC             모든 사용자에 대해 액세스 가능한 동의어를 생성

 synonym_name  생성 되어야 할 동의어 이름

 object_name      생성된 동의어에 대한 객체를 식별합니다.

Guidelines

 1) 객체는 패키지에 포함될 수 없습니다.

 2) 개별 동의어 이름은 동일 사용자가 소유한 모든 다른 객체의 이름과 달라야 합니다.

문제 4) SALGRADE 동의어로 GUBUN를 생성하고 동의어로 조회하여라.

2.2 동의어 삭제

 동의어를 제거하기 위해 DROP SYNONYM 문장을 사용합니다. DBA만 공용(Public) 동의어를 제거할 수 있습니다.

2.2.1 Syntax

문제 5) 앞에서 생성한 동의어를 삭제하여라.

1. VIEW의 개념

 테이블이나 다른 VIEW을 기초로 한 논리적인 테이블이고 VIEW는 자체의 데이터는 없지만 테이블의 데이터를 보거나 변경할 수 있는 창과 같다. VIEW은 실제적으로는 질의 문장을 가진다.

1.1 VIEW의 장점

 1) VIEW은 데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 액세스를 제한한다.

 2) 복잡한 질의어를 통해 얻을 수 있는 결과를 간단한 질의어를 써서 구할 수 있게 한다.

 3) 데이터 독립성을 허용한다.

 4) 동일한 데이터의 다른 VIEW를 나타낸다.

 5) 조인을 한 것처럼 여러 테이블에 대한 데이터를 VIEW을 통해볼 수 있다.

 6) 한 개의 VIEW로 여러 테이블에 대한 데이터를 검색할 수 있다.

 7) 특정 평가기준에 따른 사용자 별로 다른 데이터를 액세스 할 수 있다.

1.2 VIEW의 종류

1.2.1 Simple VIEW

 1) 오직 하나의 테이블에서만 데이터가 유래된다.

 2) 데이터 그룹 또는 함수를 포함하지 않는다.

 3) VIEW를 통해 DML 수행 가능

1.2.3 Complex VIEW

 1) 다중 테이블에서 데이터가 유래된다.

 2) 데이터 그룹 또는 함수를 포함한다.

 3) VIEW을 통한 DML을 항상 허용하지 않는다.

1.3 VIEW의 생성

 1) CREATE VIEW 문장 내에서 SUBQUERY을 내장하여 VIEW를 생성한다.

 2) SUBQUERY은 복합 SELECT 구문을 포함할 수 있고 ORDER BY 절을 포함할 수 없다.

1.3.1 Syntax

 OR REPLACE               이미 존재한다면 다시 생성한다.

 FORCE                       Base Table 유무에 관계없이 VIEW을 만든다.

 NOFORCE                  기본 테이블이 존재할 경우에만 VIEW를 생성한다.

 view_name                 VIEW의 이름

 Alias                         Subquery를 통해 선택된 값에 대한 Column명이 된다.

 Subquery                   SELECT문장을 기술한다.

 WITH CHECK OPTION   VIEW에 의해 액세스 될 수 있는 행만이 입력, 갱신될 수 있다.

 Constraint                  CHECK OPTION 제약 조건에 대해 지정된 이름이다.  

 WITH READ ONLY        이 VIEW에서 DML이 수행될 수 없게 한다.

Guidelines

 1) VIEW을 정의하는 질의어는 조인, 그룹, Subquery를 포함하는 복잡한 SELECT 문장으로 구성될 수 있다.

 2) VIEW을 정의하는 질의어에는 ORDER BY절을 쓸 수 없다.

 3) 제약 조건의 이름을 명시하지 않으면 시스템이 SYS_Cn 형태의 이름을 지정한다.

 4) VIEW을 삭제하거나 재생성하지 않고 VIEW의 정의를 변경하려면 OR REPLACE 옵션을 쓸 수 있다.

문제 1) EMP 테이블에서 20번 부서의 세부 사항을 포함하는 EMP_20 VIEW를 생성 하여라

문제 2) EMP 테이블에서 30번 부서만 EMPNO를 EMP_NO로 ENAME를 NAME로 SAL를 SALARY로 바꾸어 EMP_30 VIEW를 생성 하여라.

1.4 VIEW의 구조 및 이름 확인

 일단 VIEW가 생성되면, VIEW의 이름과 VIEW 정의를 보기 위해 USER_VIEWS 라는 데이터 사전 테이블을 질의할 수 있습니다. VIEW을 만드는 SELECT 문장의 텍스트는 LONG 열에 저장됩니다.

문제 3) 현재 SESSION를 이루고 있는 사용자가 소유한 VIEW를 조회하시오.

1.5 데이터 액세스 VIEW

 VIEW을 사용하여 데이터를 액세스할 때 ORACLE SERVER은 다음 작업을 수행합니다.

 1) USER_VIEWS 데이터 사전 테이블에서 VIEW 정의를 검색합니다.

 2) VIEW 기반 테이블에 대한 액세스 권한을 확인합니다.

 3) VIEW 질의를 기본 테이블 또는 테이블들에서의 동등한 작업으로 전환합니다.

문제 4) emp_30 의 VIEW에서 자료를 조회하여라.

1.5.1 VIEW의 수정

 OR REPLACE 옵션은 비록 이 이름이 이미 존재할지라도 VIEW가 생성될 수 있도록 해주므로 그 소유자에 대한 오래된 VIEW 버전업할 수 있다.

문제 5) 부서번호 10번만 포함하고 있는 이미 생성되어 있는 VIEW의 내용을 사원번호(employee_no),이름(employee_name),업무(job_title)의 내용으로 변경하여라.

주의

 CREATE VIEW 절에서 열 별칭을 지정할 때 별칭은 SUBQUERY 의 열과 동일한 명령으로 나열됨을 명심하십시오.

1.6 복합 VIEW 생성

 두 테이블로부터 값을 출력하는 그룹 함수를 포함하는 복잡한 VIEW를 생성합니다. VIEW의 어떤 열이 함수나 표현식에서 유래되었다면 별칭은 필수적입니다.

문제 6) 부서별로 부서명, 최소 급여, 최대 급여, 부서의 평균 급여를 포함하는 DEPT_SUM VOEW을 생성하여라.

1.7 VIEW에서 DML 연산 수행

 1) 단순 VIEW에서 DML 연산을 수행할 수 있습니다.

 2) VIEW가 다음을 포함 한다면 행을 제거할 수 없습니다.

  ① 그룹 함수

  ② GROUP By절

  ③ DISTINCT 키워드

 3) 다음을 포함한다면 VIEW에서 데이터를 수정할 수 없습니다.

  ① 그룹 함수

  ② GROUP BY 절

  ③ DISTINCT 키워드

  ④ 표현식으로 정의된 열

  ⑤ ROWNUM 의사열

 4) 다음을 포함한다면 VIEW에서 데이터를 추가할 수 없습니다.

  ① 그룹 함수

  ② GROUP BY절

  ③ DISTINCT 키워드

  ④ 표현식으로 정의된 열

  ⑤ ROWNUM의사열

  ⑥ VIEW에 의해 선택되지 않은 NOT NULL열이 기본 테이블에 있을 경우

1.8 WITH CHECK OPTION 절 사용

 VIEW을 통해 참조 무결성 체크를 수행하는 것이 가능합니다. 또한 데이터베이스 LEVEL 에서 제약 조건을 적용할 수 있습니다. VIEW은데이터 무결성을 보호하기 위해 사용될 수 있지만, 사용은 매우 제한됩니다. VIEW을 통해 수행되는 INSERT와 UPDATE는 WITH CHECK OPTION 절이 있으면 VIEW을 가지고 검색할 수 없는 행 생성을 허용하지 않음을 명시합니다. 그러므로 삽입되거나 갱신되는 데이터에 대해서 무결성 제약 조건과 데이터 검증 체크를 허용합니다. VIEW가 선택하지 않은 행에 대해 DML 작업을 수행하려고 하면, 지정된 제약 조건 명과 함께 에러가 출력됩니다.

문제 7) EMP 테이블과 동일한 EMP_20(20번 부서만)이라는 VIEW를 WITH CHECK OPTION을 사용하여 생성하여라.

참고

 부서번호가 30으로 변경된다면 VIEW은 더 이상 그 종업원들을 볼 수 없기 때문에 아무행도 갱신되지 않습니다. 그러므로 WITH CHECK OPTION절로 VIEW은 부서 20종업원만 볼 수 있고, 이 종업원들에 대한 부서번호가 VIEW을 통해 변경되는 것을 허용하지 않습니다.

1.9 DML 연산 부정

 1) WITH READ ONLY 옵션으로 VIEW을 생성하면 VIEW에서 DML 연산을 수행할 수 없습니다.

 2) VIEW에서 임의의 행에서 DML 연산을 수행하려고 하면 ORACLE SERVER 에러 ORA-01752가 발생합니다.

문제 8) EMP 테이블에서 EMP_10(10 번 부서 중 EMPNO, ENAME, JOB)이라는 VIEW를 WITH READ ONLY OPTION을 사용하여 생성하여라.

1.10.1 Syntax

문제 9) 앞에서 생성한 EMP_10, EMP_20 VIEW을 삭제하여라.

1, SEQUENCE

 SEQUENCE는 테이블의 행에 대한 SEQUENCE 번호를 자동적으로 생성하기 위해 사용될 수 있다. SEQUENCE는 사용자가 생성한 데이터베이스 객체이다. SEQUENCE에 대한 전형적인 사용은 각행에 대해 유일해야 하는 PRIMARY KEY 값을 생성하기 위해서 입니다. SEQUENCE 는 Oracle8에 의해 발생되고 증가(또는 감소) 됩니다.

1.1 SEQUENCE 특징

 1) 자동적으로 유일 번호를 생성합니다.

 2) 공유 가능한 객체

 3) 주로 기본 키 값을 생성하기 위해 사용됩니다.

 4) 어플리케이션 코드를 대체합니다.

 5) 메모리에 CACHE되면 SEQUENCE 값을 액세스 하는 효율성을 향상시킵니다.

1.2 Syntax

sequence_name          SEQUENCE의 이름입니다.

INCREMENT BY n        정수 값인 n으로 SEQUENCE 번호 사이의 간격을 지정.

                               이 절이 생략되면 SEQUENCE는 1씩 증가.

START WITH n            생성하기 위해 첫번째 SEQUENCE를 지정.

                               이 절이 생략되면 SEQUENCE는 1로 시작.

MAXVALUE n             SEQUENCE를 생성할 수 있는 최대 값을 지정.

NOMAXVALUE            오름차순용 10^27 최대값과 내림차순용-1의 최소값을 지정.

MINVALUE n              최소 SEQUENCE 값을 지정.

NOMINVALUE             오름차순용 1과 내림차순용-(10^26)의 최소값을 지정.

CYCLE | NOCYCLE        최대 또는 최소값에 도달한 후에 계속 값을 생성할 지의 여부를 지정.

                               NOCYCLE이 디폴트

CACHE | NOCACHE     얼마나 많은 값이 메모리에 오라클 서버가 미리 할당하고 유지하는가를 지정.

                              디폴트로 오라클 서버는 20을 CACHE.

문제 1) DEPT 테이블의 PRIMARY KEY에 사용되는 DEPT_DEPTNO SEQUENCE를 생성 합니다.

참고

 CYCLE OPTION을 사용해서는 안됩니다. (PRIMARY KEY으로 사용될 경우)

1.3 SEQUENCE 확인

 한번 SEQUENCE 를 생성했으면 데이터 사전에 등록 됩니다. SEQUENCE 가 데이터베이스 객체가 된 이후에 USER_OBJECTS DATA DICTIONARY에서 식별할 수 있습니다. 또한 데이터 사전의 USER_SEQUENCES 테이블을 검색함으로써 SEQUENCE의 설정 값을 확인할 수 있다.

문제 2) 현재 SESSION을 이루고 있는 사용자가 소유하고 있는 SEQUENCE를 조회하여라.

1.4 SEQUENCE 사용법

 테이블에 사용할 절차적인 번호를 생성하기 위해 SEQUENCE를 사용할 수 있다. NEXTVALUE와 CURRVALUE 의사열을 사용하여 SEQUENCE 값을 참조한다.

1.4.1 NEXTVAL 과 CURRVAL 의사열

가) 특징

 1) NEXTVAL는 다음 사용 가능한 SEQUENCE 값을 반환 한다.

 2) SEQUENCE가 참조될 때 마다, 다른 사용자에게 조차도 유일한 값을 반환한다.

 3) CURRVAL은 현재 SEQUENCE 값을 얻는다.

 4) CURRBAL이 참조되기 전에 NEXTVAL이 사용되어야 한다.

나) NEXTVAL과 CURRVAL의 사용 규칙

 1) NEXTVAL과 CURRVAL을 사용할 수 있는 경우 

  ① SUBQUERY가 아닌 SELECT문

  ② INSERT문 dml SELECT문

  ③ INSERT문의 VALUES절

  ④ UPDATE문의 SET절

 2) NEXTVAL과 CURRVAL 사용할 수 없는 경우

  ① VIEW문의 SELECT문

  ② DISTINCT 키워드를 사용한 SELECT문

  ③ GROUP BY, HAVING, ORDER BY를 이용한 SELECT문

  ④ SELECT, DELETE, UPDATE 문장에서의 SUBQUERY

  ⑤ CREATE TABLE, ALTER TABLE 명령문의 DEFAULT절

문제 3) DEPT 테이블에 부서명을 영업부, 위치를 분당구 정자동을 입력하여라.

문제 4) DEPT_DEPTNO SEQUENCE의 현재 값을 확인하시오.

1.5 SEQUENCE 값 CACHE

 SEQUENCE 값에 대해 보다 빠른 액세스를 허용하기 위해 메모리에 SEQUENCE를 CACHE 합니다. CACHE는 SEQUENCE를 처음 참조할 때 형성됩니다. 다음 SEQUENCE가 사용된 후에 SEQUENCE에 요구하면 CACHE된 SEQUENCE를 메모리에 갖다 놓습니다.

1.6 SEQUENCE에서 간격의 경계

 1) SEQUENCE 값에서 간격(gap)은 아래의상황에서 발생합니다.

  ① ROLLBACK

  ② SYSTEM CRASH

  ③ SEQUENCE가 다른 테이블에서 사용될 때

1.7 SEQUENCE 수정

 INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, CACHE을 변경할 수 있습니다.

1.7.1 Syntax

1.7.2 SEQUENCE 수정 지침

 1) SEQUENCE에 대한 ALTER 권한을 가지거나 소유자여야 합니다.

 2) 이후의 SEQUENCE 번호만 영향을 받습니다.

 3) SEQUENCE는 다른 번호에서 SEQUENCE 를 다시 시작하기 위해서는 제거하고 다시 생성하여야 합니다.

 4) 유효한 검사를 수행합니다.

1.8 SEQUENCE 제거

 데이터 사전에서 SEQUENCE를 제거하기 위해 DROP SEQUENCE 문장을 사용합니다. SEQUENCE를 제거하기 위해서는 소요자이거나 DROP ANY SEQUENCE 권한을 가져야 합니다.

1.8.1 Syntax

문제 ) DEPT_DEPTNO SEQUENCE를 삭제하여라

1. 데이터(DML) 조작어

 DML(Data Manipulation Language) 명령은 데이터를 입력, 수정, 삭제하는 SQL 명령어이다. 데이터베이스에 영구적으로 반영되지 않은 데이터 조작 명령어들을 TRANSACTION 이라고 하며 오라클에서는 이를 하나의 논리적 작업 단위로 사용한다.

1,1 INSERT 문장

 테이블에 사용하여 테이블에 새로운 행을 삽입(INSERT)할 수 있다.

1.1.1 Syntax

1.1.2 사용 예

- 모든 column에 대해 값을 갖는 새로운 행을 삽입한다.

- INSERT 절의 column 은 선택적으로 기입할 수 있다. 이럴 경우 열중 NOT NULL 제약 조건이 있는 열은 반드시 포함하여야 한다.

- 문자와 날짜 값은 단일 따옴표(' ')를 둘러싼다.

Guidelines

 1) VALUES절을 가지는 INSERT 문장은 한번에 오직 하나의 행만을 추가한다.

 2) 모든 행에 값을 갖는 새로운 행을 삽입할 수 있기 때문에 column list는 INSERT 절에 필요하지 않다. 하지만 테이블에 정의된 순서에 따라 값을 나열해야 한다.

 3) 명확성을 위해 INSERT 절에 column list를 사용하면 좋다. (권장)

 4) 문자와 날짜는 단일 따옴표 안에 쓰나, 수치 값은 사용하지 않는다.

1.1.3 NULL 값을 새로운 행에 추가

 자료형에 관계없이 사용 가능하다.

가) 암시적 방법

나) 명시적 방법

1.1.4 특정 날짜 값 삽입

 형식 DD-MON-YY 는 항상 날짜 값을 입력할 때 사용한다. 이 형식은 현재 세기에 대한 DEFAULT 세기를 다시 호출한다. 또한 날짜가 시간 정보를 포함하므로 DEFAULT 시간은 자정(00:00:00)이다. 날짜를 다른 세기로 입력하거나 또는 특정 시간을 요구 한다면 TO_DATE 함수를 사용하여라.

예) 아래의 INSERT 문장은 날짜를 입력하는 방법이다.

1.1.5 치환 변수를 사용하여 값 입력

 SQL*Plus 의 치환 변수를 사용하여 사용자가 상호 작용으로 값을 추가할 수 있다. 날짜와 문자 값은 단일 인용 부호를 사용하여 감싸는 것이 사용하기에 편하고 SCRIPT로 작성하여 사용하면 입력시 매우 유용하다.

예) SCRIPT 안에서 치환 변수 사용하는 방법이다.

1.1.6 다른 테이블로부터 행 복사

 기존의 테이블로부터 값을 가져와 테이블에 추가하기 위해서 INSERT 문장을 사용할 수 있다. 즉 VALUES 절에서 subquery를 사용할 수 있다.

가) Syntax

문제 1) EMP 테이블에서 EMPNO, ENAME, SAL, HIREDATE의 COLUMN만 선택하여 EMP_10 테이블을 생성한 후 10번 부서만 선택하여 이에 대응하는 값을 EMP_10 테이블에 입력하여라.

참고

  INSERT 절의 열의 개수와 서브쿼리의 열의 개수가 좌측부터 1대1 대응하며 자료형과 길이가 같아야 한다.

1.1.7 INSERT 시 무결성 제약 조건의 오류

 무결성 제약 조건(5가지)이 위배되면 INSERT 시 ERROR가 발생합니다.

1.2 UPDATE 문장

 1) UPDATE문장으로 기존의 행을 갱신합니다.

 2) 일반적으로 단일 행을 식별하기 위해서 기본 키(primary key)를 사용합니다. 다른 열을 사용하면 원하지 않는 여러 행이 갱신될 수 있습니다.

 3) 특정 열이나 행은 WHERE 절을 이용하여 갱신할 수 있다.

1.2.1 Syntax

참고

 DEMO TABLE를 새로 생성하여 실습을 하자

문제 2) EMP 테이블에서 사원 번호가 7788인 사원의 부서를 10번으로 변경하여라.

문제 3) EMP 테이블에서 사원 번호가 7788인 사원의 부서를 20, 급여를 3500으로 변경하여라.

문제 4) EMP 테이블에서 부서를 모두 10으로 변경하여라.

1.2.2 다중 열 SUBQUERY로 갱신

 다중 열 SUBQUERY는 UPDATE 문장의 SET 절로 구현할 수 있다.

가) Syntax

문제 4) EMP 테이블에서 SCOTT 의 업무와 급여가 일치하도록 JONES 업무와 급여를 개인하여라

1.2.3 다른 테이블을 근거로 한 행 갱신

 다른 테이블의 값을 근거로 하는 테이블에서 행을 갱신하기 위해 UPDATE 문장에서 SUBQUERY를 사용한다.

문제 4) EMP 테이블을 근거로 EMPLOYEE 테이블을 생성한 후 7902의 업무에 해당하는 사원의 부서번호를 7902의 부서번호로 갱신하여라.

1.2.4 UPDATE시 무결성 제약 조건 ERROR

 무결성 제약 조건이 위배되는 값으로 UPDATE할 경우 ERROR가 발생한다.

문제 5)EMP 테이블에서 10번 부서의 사원을 모두 91번 부서로 갱신하여라.

문제 6) DEPT 테이블에서 부서 번호 10을 15로 갱신하여라.

1.3 DELETE 문장

 1) DELETE 문장을 사용하여 테이블로부터 기존의 자료를 삭제할 수 있다.

 2) WHERE 절을 명시하여 특정 행이나 행들을 삭제할 수 있다.

 3) WHERE 절을 생략하면 테이블의 모든 행이 삭제 된다.

1.3.1 Syntax

문제 7) EMP 테이블에서 사원번호가 7499인 사원의 정보를 삭제하여라.

문제 8) EMP 테이블에서 입사일자가 83년인 사원의 정보를 삭제하여라.

1.3.2 다른 테이블을 근거로 한 행 삭제

 다른 테이블을 값을 근거로 행을 삭제하기 위해서는 SUBQUERY를 사용하여야 한다.

문제 9) EMP 테이블의 자료 중 부서명이 'SALES'인 사원의 정보를 삭제하여라.

1.3.3 무결성 제약 조건 ERROR

 무결성 제약 조건을 위반하도록 행을 삭제하면 ERROR가 발생한다.

문제 10) EMP 테이블에서 사원 번호가 7902인 사원의 정보를 삭제하여라.

문제 11) DEPT 테이블에서 부서명이 'ACCOUNTING' 부서의 정보를 삭제하여라.

1.4. 데이터베이스 TRANSACTION

 ORACLE SERVER는 TRANSACTION을 근거로 하는 데이터의 일관성을 보증한다. TRANSACTION은 데이터 변경시에 보다 나은 융통성과 제어를 제공하며 그들은 사용자 프로세스 실패나 시스템 실패 같은 이벤트에서 데이터의 일관성을 책임집니다. TRANSACTION은 데이터를 일관되게 변경하는 DML 문장으로 구성됩니다. 예를 들면, 하나의 예금에 대한 차변과 똑같은 금액이 있는 다른 예금에 대한 대변을 포함해야 하는 두 개의 예금 사이에 예금을 전달합니다. 액션은 둘 모두 실패하거나 둘 모두 성공해야 합니다. 대변은 차변 없이는 인증될 수 없습니다.

1.4.1 TRANSACTION의 유형

1.4.2 TRANSACTION의 시작과 종료

가) TRANSACTION의 시작

 1) 실행 가능한 SQL 문장이 제일 처음 실행될 때

나) TRANSACTION의 종료

 1) COMMIT이나 ROLLBACK

 2) DDL이나 DCL 문장의 실행(자동 COMMIT)

 3) 기계 장애 또는 시스템 충돌(crash)
 4) deadlock 발생

 5) 사용자가 정상 종료

1.5.2 COMMIT 과 ROLLBACK의 장점

 1) 데이터의 일관성 제공

 2) 데이터를 영구적으로 변경하기 전에 데이터 변경을 확인하게 한다.

 3) 관련된 작업을 논리적으로 그룹화 할 수 있다.

1.4.3 TRANSACTION 제어

 COMMIT, SAVEPOINT, ROLLBACK 문장으로 TRANSACTION의 논리를 제어할 수 있다.

참고

 하나의 TRANSACTION 이 끝난 후에 다음의 실행 가능한 SQL 문장은 자동적으로 다음 TRANSACTION을 시작할 것이다. DDL과 DCL은 자동적으로 COMMIT되므로 TRANSACTION을 임시적으로 종료한다. SAVEPOINT는 ANSI 표준 SQL이 아니다.

1.4.3 암시적 TRANSACTION 처리

 1) 자동 COMMIT은 다음의 경우 발생

 ① DDL,DCL 문장이 완료시

 ② 명시적인 COMMIT이나 ROLLBACK 없이 SQL*Plus를 정상 종료

 2) 자동 ROLLBACK은 다음의 경우 발생

 ① SQL*Plus를 비정상 종료 또는 시스템 실패

1.4.4 COMMIT이나 ROLLBACK 이전의 데이터 상태

 1) 데이터 이전의 상태로 복구가 가능하다.

 2) 현재 사용자는 SELECT 문장으로 DML 작업의 결과를 확인할 수 있다.

 3) 다른 사용자는 SELECT 문장으로 현재 사용자 사용한 DML 문장의 결과를 확인할 수 없다.

 4) 변경된 행은 LOCK이 설정되어서 다른 사용자가 변경할 수 없다.

1.4.5 COMMIT 이후의 데이터 상태

 1) 데이터베이스에 데이터를 영구적으로 변경

 2) 데이터의 이전 상태는 완전히 상실

 3) 모든 사용자가 결과를 볼 수 있다.

 4) 변경된 행의 LOCK이 해제되고 다른 사용자가 변경할 수 있다.

 5) 모든 SAVEPOINT는 제거된다.

문제 12) EMP테이블에서 7788인 사원의 부서번호를 30번 부서로 갱신한 후 자료를 확정하여라.

1.4.6 ROLLBACK이후의 데이터 상태

 1) 데이터의 변경이 취소

 2) 데이터의 이전 상태로 복구

 3) 변경된 행들의 LOCK이 해제되어 다른 사용자가 변경할 수 있다.

1.4.7 SAVEPOINT로 변경을 ROLLBACK

 1) SAVEPOINT를 사용하여 현재 TRANSACTION내에 표시자를 생성한다.

 2) ROLLBACK TO SAVEPOINT 명령을 사용하여 표시자까지 ROLLBACK

1.4.8 문장 단위 ROLLBACK

 1) 실행 동안에 단일 DML 문장이 실패하면 단지 그 문장만이 ROLLBACK 한다.

 2) ORACLE SERVER은 암시적 SAVEPOINT를 구현 한다.

 3) 모든 다른 변경들은 유지된다.

 4) 사용자는 COMMIT이나 ROLLBACK을 실행하여 명시적으로 TRANSACTION을 종료한다.

1.5 읽기 일관성

 1) 읽기 일관성은 항상 데이터의 검색이 일관되게 보증한다.

 2) 사용자에 의해 행해진 변경은 다른 사용자에 의해 행해진 변경과 충돌하지 않는다.

 3) 데이터를 똑같게 보증한다.

1.6 Locking

 1) 동시 TRANSACTION 사이의 상호 작용이 파괴되지 않도록 막아 줍니다.

 2) 사용자 액션을 요구하지 않습니다.

 3) 자동적으로 낮은 LEVEL의 제약 조건을 사용합니다.

 4) TRANSACTION이 지속되도록 합니다.

 5) 두가지 기본적인 모드를 가집니다.

  ① Exclusive

  ② Share

1.6.1 Locking Mode