[20210610] DDL, DCL, 오라클 자료형, PL/SQL

DDL(Data Definition language, 데이터 정의 언어)

TABLE, USER, VIEW, SEQUENCE, INDEX 등을 생성(CREATE), 변경(ALTER 또는 REPLACE), 삭제(DELETE, TRUNCATE)하는 것이 가능하다.
- TABLE: 데이터가 저장된 저장 공간을 가진 객체, 사용자가 원하는 데이터를 액세스하기 위한 데이터의 의미와 속성이
정의된 객체이다.
- CONSTRAINTS(제약조건): DB 테이블에서 입력(수정, 삭제)되는 데이터가 지켜야 하는 규칙이다.
- DB에 접속한 계정이 자신의 스키마에 테이블을 생성하기 위한 조건이다.
* DB 계정에 CREATE TABLE(테이블 생성) 권한이 부여되어 있어야 한다.
* 저장 공간에 대한 권한(TABLESPACE상의 QUOTA)이 설정되어 있어야 한다.

TABLE 생성
- SQL문: CREATE TABLE 테이블명(칼럼명 1 데이터형 [, 제약조건],..., 칼럼 N 데이터형[, 제약조건])
1. 테이블명 및 칼럼명은 30바이트 초과 불가.
2. A-Z, a-z, 0-9, $, # 문자만 포함, 숫자로 시작 불가.
3. 테이블명 및 칼럼명은 오라클의 예약어 사용 불가.
4. 테이블명은 같은 유저 내에서는 중복 불가.
- DEFAULT 옵션
-- UPDATE, INSERT 문에서 DEFAULT 키워드를 이용.
-- SQL문: CREATE 테이블명 (칼럼명 1 데이터형 DEFAULT 기본값,... 칼럼명 N 데이터형 DEFAULT 기본값);
- 데이터 입력 및 수정 가능.

- 서브쿼리를 이용해서 테이블을 생성하기.
-- 테이블을 복제해서 생성, 제약조건은 복제 안 됨.
-- 테이블의 구조와 데이터 값만 복제된다.
예) CREATE TABLE E2 AS SELECT * FROM EMP;
-- 테이블의 구조만 복사한다.

TABLE 변경(칼럼 추가 및 제약 조건) 변경
- ADD는 추가, DROP은 삭제.
- SQL문: ALTER TABLE 테이블명 [ADD|DROP] 칼럼명 제약조건
            ALTER TABLE 테이블명 [ADD|DROP] CONSTRAINT

NOT NULL: NULL을 허용하지 않는다. 반드시 데이터를 입력해야 한다.
UNIQUE: 유일성, 중복을 허락하지 않는다. NULL 값은 중복 가능.
PRIMARY KEY: 기본키 == 행을 구분하는 식별자 칼럼
              NOT NULL, UNIQUE 속성을 갖는다.
FOREIGN KEY: 외래키 == 기본키를 참조하는 키,
                       다른 테이블의 기본키...
SUPER KEY: UNIQUE하게 식별되는 모든 조합을 의미.
CHECK: 입력값의 종류나 범위를 제한하는 것. NULL 값은 해당 안 됨.
USER_CONSTRAINTS

TABLE 삭제
- SQL문: DROP TABLE 테이블명

 

VIEW
뷰: 쿼리의 단축이나 보안을 위해서, 사용하는 물리적 테이블을 기반으로 생성한 가상의 테이블이다.
SYSTEM 계정에서 'GRANT CREATE VIEW TO 사용자명;'으로 뷰 생성 권한을 획득한 후에 생성이 가능하다.

생성: CREATE VIEW 뷰이름 (AS) SELECT 문;
- 제약조건
-- WITH CHECK OPTION: CHECK OPTION을 조건문에 추가하면 그 조건의 칼럼은 UPDATE VIEW 문으로 변경할 수 없다.
-- WITH READ ONLY: READ ONLY 옵션을 조건문에 추가하면 그 조건은 읽기만 가능하고 수정할 수 없다.

수정 방법: CREATE OR REPLACE 뷰이름 (AS) SELECT문;
삭제 방법: DROP VIEW 뷰이름;

 

SEQUENCE
생성
- SQL문: CREATE SEQUENCE 시퀀스명;

삭제
- SQL문: DROP SEQUENCE 시퀀스명;

CREATE SEQUENCE 시퀀스명;
OPTION
INCREMENT BY n -> n만큼 증가.
START WITH n -> n부터 시작.
NOMAXVALUE -> 범위 제한 없음.
MAXVALUE n -> 최대 n까지
NOCYCLE -> 반복 없음.
CYCLE -> 최댓값에 다다르면 다시 시작번호 돌아감.
CACHE 10 -> 한 번에 10개의 번호를 생성, DEFAULT 20개.
NOCACHE -> 그때그때 번호를 생성한다.

 

INDEX
검색 속도를 높이기 위해서 사용한다.
B* tree방식을 사용.
추가적인 저장공간 필요.
수정, 입력이 빈번하면 효율성이 감소.
따라서, REBUILD(갱신)이 필요하다.

인덱스가 필요한 경우	인덱스가 필요없는 경우
행 수가 많을 때	행 수가 적을 때
WHERE 절 검색 빈도가 높을 때	WHERE 절 검색 빈도가 낮을 때
검색 결과 데이터 2~4% 정도	검색 결과 데이터가 10~15% 정도
자주 사용되는 JOIN 절	빈번한 DML이 일어날 때
NULL 포함하는 칼럼이 많을 때	입력 수정이 빈번할 때

 

DCL(Data Control Language, 데이터 제어 언어)

1. 계정 만들기: CREATE USER 유저명 IDENTIFIED BY 암호;
2. 권한 부여: 예) GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO 유저명;
3. 롤(ROLE) 권한의 부여 (롤: 여러 가지 권한을 하나의 권한 묶음) 예) GRANT CONNECT, RESOURCE TO 유저명;
4. 권한 제거: REVOKE 권한 FROM 대상;
5. 계정 제거: DROP USER 유저명;

권한의 종류
* CREATE USER <-> DROP USER;
* CREATE TABLE <-> DROP TABLE;
* QUERY REWRITE - 질의를 재작성할 수 있는 권한
* BACKUP 테이블 - 임의의 테이블을 백업할 수 있는 권한
* CREATE SESSION - 접속 권한
* CREATE VIEW - VIEW 생성 권한
* CREATE SEQUENCE - SEQUENCE 생성 권한
* CREATE PROCEDURE

오라클 자료형

* CHAR(N): 1~2000바이트 사이의 문자의 크기가 고정된 문장: 주민번호/전화번호/우편번호
* VARCHAR2(N): 1~4000바이트 사이의 가변 문장. STRING
* NVARCHAR2(N): 1~4000바이트 국가별 국가 집합에 따른 크기의 문자 또는 가변 문장. 거의 안 씀.
* NUMBER(P, S): P: 전체 자릿수, S: 소수점 자릿수. DOUBLE
* NUMBER(N): N자리 수 정수, INT
* ROWID: 행 주소 64진수 문자: 시스템 내부에서 레코드의 고유 위치를 나타낸다.
* ROWNUM: 쿼리의 결과에 순서를 붙여서 출력한다. 소수점 2자리
* BLOB: BINARY LARGE OBJECT 대용량 이진 데이터. 최대 4기가
* CLOB: CHARACTER LARGE OBJECT 대용량 문자 파일. 최대 4기가
* BFILE: BINARY DATA를 파일로 저장. 최대 4기가
* DATE: 날짜 -> SYSDATE. STRING
* TIMESTAMP(N): 날짜 형식을 지정.
* INTERVAL YEAR TO MONTH: 기간을 저장. 예) 36개월 3년
* INTERVAL DAY TO SECOND: 기간을 저장. 예) 3600

 

PL/SQL

PL/SQL 언어: 오라클이 제공하는 오라클 데이터베이스 서버의 내장 PL/SQL PACKAGE를 이용하여 프로그래밍하는 언어. SQL DEVELOPER를 이용하여 작성한다.

* SQL: 데이터베이스 서버에 요구하여 '데이터를 처리'를 수행하는 명령어.
* PL/SQL: C, JAVA, ASP, JSP, PHP와 비슷한 기능을 제공. 오라클에서 제공하는 자체 프로그램 언어.
특징: 블록 구조로 다수의 SQL문을 한 번에 ORACLE DB로 보내 처리하므로 수행 속도를 향상. 모듈화 가능. 큰 블록 안에 소블록 선언 가능. VARIABLE, CONSTANT, CURSOR, EXCEPTION을 정의하고, SQL 문장과 PROCEDURE 문장에 사용한다.

PL/SQL 블록 구조:
   1) 선언부: 변수, 상수, 등을 선언.(생략 가능)
   2) 실행부: SQL문, 반복문, 조건문 실행. "BEGIN 시작, END;"로 끝.(생략 불가)
   3) 예외 처리: 예외 처리(생략 가능)

SHOW ALL
SET SERVEROUTPUT ON

DECLARE
    f_name varchar2(30);--String f_name;
    l_name varchar2(15);
BEGIN
    SELECT ENAME INTO f_name
    FROM EMP
    WHERE EMPNO=7369;
    --System.out.print();
    DBMS_OUTPUT.PUT_LINE(
     --"The First Name of the Emp is " + f_name;
        'The First Name of the Emp is '||f_name);
    --f_name = f_name + "--a";
    f_name := f_name||'--a';
    --System.out.print(f_name);
    DBMS_OUTPUT.PUT_LINE(f_name);    
END;
/

함수 작성

CREATE FUNCTION COMPUTE_TAX(sal in number)
    return number
IS
BEGIN
    if sal <3000 then
        return sal*0.15;
    else return sal*0.33;
    end if;
END;
/
SELECT ENAME, SAL , COMPUTE_TAX(SAL) AS TAX, SAL - COMPUTE_TAX(SAL) AS 실수령액
FROM EMP
WHERE DEPTNO=10;