Constraint

테이블에 올바른 데이터만 입력받고 잘못된 데이터는 들어오지 못하도록 컬럼별로 정해진 규칙.(학번은 중복되지 않게 하거나, 학년에는 1-4까지의 숫자만 들어오게 하는 등).

하나의 제약 조건이 여러 컬럼에 중복으로 설정 될 수 있고, 또 하나의 컬럼에 여러 개의 제약 조건들이 중복으로 설정 될 수 있음.(primary key는 테이블당 1개만 설정가능)

DML

테이블안의 데이터들을 관리하는 명령어

INSERT

테이블에 새로운 데이터를 입력할 때 사용하는 명령어 숫자 값 이외에는 홑따옴표 써야함.

(1) 1행씩 입력하기

INSERT INTO table (col1, co2, ...,)
VALUES (value1, value2, ...,) ;

SCOTT>INSERT INTO dept2 (dcode, dname, pdept, area)
  2  VALUES (9000, 'temp_1', 1006, 'Temp Area');

모든 컬럼에 데이터를 입력할 경우에는 테이블명 뒤에 컬럼 이름 생략가능.

데이터 입력시 컬럼에 값을 입력하지 않으면 자동으로 null 값이 들어감. (값에 null 입력해도됨)

SCOTT>CREATE TABLE t_minus
  2  ( no1 NUMBER,
  3    no2 NUMBER(3),       // 정수 3자리.
  4    no3 NUMBER(3,2));  // 소수점이하 2자리까지.

Table created.

SCOTT>INSERT INTO t_minus VALUES(1, 1, 1);

1 row created.

SCOTT>INSERT INTO t_minus VALUES(1.1, 1.1, 1.1);

1 row created.

SCOTT>INSERT INTO t_minus VALUES(-1.1, -1.1, -1.1);

1 row created.

SCOTT>SELECT * FROM t_minus;

       NO1        NO2        NO3
---------- ---------- ----------
         1          1          1
       1.1          1        1.1
      -1.1         -1       -1.1

(2) 서브쿼리를 사용해 여러행 입력하기

SCOTT>CREATE TABLE professor3
  2  AS
  3    SELECT * FROM professor
  4    WHERE 1 = 2;            //  데이터는 입력안되도록

Table created.

SCOTT>SELECT * FROM professor3;

no rows selected

SCOTT>INSERT INTO professor3
  2  SELECT * FROM professor;   // professor3과 컬럼의 개수, 데이터 형이 동일해야함.

16 rows created.

위처럼 서브쿼리를 사용해 여러 데이터를 가져와 입력하는 방법을 현업에서는 ITAS 라고 부름. 조건도 사용가능

(3) INSERT ALL 을 이용해 여러 테이블에 여러 행 입력하기

예제

Professor 테이블에서 교수번호가 1000 번 에서 1999번까지 인 교수의 번호와 교수이름은 prof_3 테이블에 입력하고 교수번호가 2000 번에서 2999 번까지 인 교수의 번호와 이름은 prof_4 테이블에 입력.

SCOTT>CREATE TABLE prof_3
  2  ( profno  NUMBER,
  3    name    VARCHAR2(25));

Table created.

SCOTT>CREATE TABLE prof_4
  2  ( profno  NUMBER,
  3    name    VARCHAR2(25));

Table created.

SCOTT>INSERT ALL
  2  WHEN profno BETWEEN 1000 AND 1999 THEN
  3       INTO prof_3 VALUES(profno, name)
  4  WHEN profno BETWEEN 2000 AND 2999 THEN
  5       INTO prof_4 VALUES(profno, name)
  6  SELECT profno, name
  7  FROM professor;

prof_3 과 prof_4 테이블의 데이터를 TRUNCATE 로 삭제한 후 Professor 테이블에서 교수번호가 3000번 에서 3999 번인 교수들의 교수 번호와 이름을 prof_3테이블과 prof_4 테이블에 동시에 입력

SCOTT>TRUNCATE TABLE prof_3;

Table truncated.

SCOTT>TRUNCATE TABLE prof_4;

Table truncated.

SCOTT>INSERT ALL
  2     INTO prof_3 VALUES (profno, name)
  3     INTO prof_4 VALUES (profno, name)
  4  SELECT profno, name
  5  FROM professor
  6  WHERE profno BETWEEN 3000 AND 3999;

UPDATE

UPDATE table
SET column = value
WHERE 조건;

예제

1) Professor 테이블에서 직급이 조교수(assistant professor) 인 교수들의 BONUS 를 200 만원으로 인상하시오.

SCOTT>UPDATE professor
  2  SET bonus = 200
  3  WHERE position = 'assistant professor';

2) Professor 테이블에서 'Sharon Stone' 교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250 만원이 안 되는 교수들의 급여를 15% 인상하시오.

SCOTT>UPDATE professor
  2  SET pay = pay * 1.15
  3  WHERE position = ( SELECT position
  4                     FROM  professor
  5                     WHERE name = 'Sharon Stone' )
  6  AND pay < 250;

DELETE

DELETE FROM table
WHERE 조건;

예제

Dept2 테이블에서 부서번호(DCODE)가 9000 번에서 9999 번 사이인 매장들을 삭제하시오.

SCOTT>DELETE FROM dept2
  2  WHERE dcode >= 9000 AND dcode <= 9999;

MERGE

여러 테이블의 데이터를 병합.

MERGE INTO table1
  USING table2
  ON (병합 조건절)
  WHEN MATCHED THEN
         UPDATE SET 업데이트내용
         DELETE  WHERE 조건
  WHEN NOT MATCHED THEN
         INSERT VALUES(컬럼명);

table1 테이블과 table2 데이터를 합쳐 table1(집계테이블)에 병합. 병합조건이 만족하면 UPDATE 또는 DELETE 수행, 만족 안하면 INSERT수행.
집계 테이블에 데이터가 많아질수록 merge 작업수행 속도는 늦어짐.(조건절에 index가 잘 만들어져 있어야 빨리 수행됨)

사용예

일별 사용 요금 테이블인 charge_01과 charge_02 테이블이 있고 집계 테이블인 ch_total 테이블이 있다. 매일 새벽에 일별 사용 요금 테이블과 요금 집계테이블을 MERGE 한다고 가정.

실습을 위해 두 테이블을 생성하고 데이터 입력.

SCOTT>CREATE TABLE charge_01
  2  ( u_date  VARCHAR2(6),
  3    cust_no NUMBER,
  4    u_time  NUMBER,
  5    charge  NUMBER ) ;

Table created.

SCOTT>CREATE TABLE charge_02
  2  ( u_date  VARCHAR2(6),
  3    cust_no NUMBER,
  4    u_time  NUMBER,
  5    charge  NUMBER ) ;

Table created.


SCOTT>INSERT INTO charge_01 VALUES('141001', 1000, 2, 1000);

1 row created.

SCOTT>INSERT INTO charge_01 VALUES('141001', 1001, 2, 1000);

1 row created.

SCOTT>INSERT INTO charge_01 VALUES('141001', 1002, 1, 500);

1 row created.

SCOTT>INSERT INTO charge_02 VALUES('141002', 1000, 3, 1500);

1 row created.

SCOTT>INSERT INTO charge_02 VALUES('141002', 1001, 4, 2000);

1 row created.

SCOTT>INSERT INTO charge_02 VALUES('141002', 1003, 1, 500);

1 row created.

SCOTT>COMMIT;

집계용 테이블 생성

SCOTT>CREATE TABLE ch_total
  2  ( u_date  VARCHAR2(6),
  3    cust_no NUMBER,
  4    u_time  NUMBER,
  5    charge  NUMBER ) ;

Table created.

MERGE 1 (charge_01 과 ch_total 병합)

SCOTT>MERGE INTO ch_total total
  2  USING charge_01 ch01
  3  ON (total.u_date = ch01.u_date)
  4  WHEN MATCHED THEN
  5     UPDATE SET total.cust_no = ch01.cust_no
  6  WHEN NOT MATCHED THEN
  7     INSERT VALUES (ch01.u_date, ch01.cust_no, ch01.u_time, ch01.charge);

3 rows merged.

MERGE 2 (charge_02 과 ch_total 병합)

SCOTT>MERGE INTO ch_total total
  2  USING charge_02 ch02
  3  ON (total.u_date = ch02.u_date)
  4  WHEN MATCHED THEN
  5     UPDATE SET total.cust_no = ch02.cust_no
  6  WHEN NOT MATCHED THEN
  7     INSERT VALUES (ch02.u_date, ch02.cust_no, ch02.u_time, ch02.charge);

3 rows merged.

결과 조회

U_DATE    CUST_NO     U_TIME     CHARGE
------ ---------- ---------- ----------
141001       1002          1        500
141001       1001          2       1000
141001       1000          2       1000
141002       1003          1        500
141002       1001          4       2000
141002       1000          3       1500

주의할점:
ON 절에 해당하는 조건이 중복되면 에러발생. 그래서 일반적으로 집계테이블 조건 컬럼에는 PK나 UNIQUE INDEX를 많이 설정함.

TRANSACTION

논리적인 작업단위.쉽게말하면 여러 DML 작업들을 하나의 단위로 묶어둔것.

트랜잭션의 시작은 DML, 완료는 TCL, DCL, DDL

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

DDL (Data Definition Language)

CREATE

새로운 오브젝트나 스키마를 생성. (이번장에서는 table만)

테이블 생성시 제한사항

1. 테이블 이름은 반드시 문자로 시작. 특수문자도 가능하지만 테이블 생성시 “ (겹따옴표) 로 감싸야 하며 권장하지 않음.
2. 테이블 이름이나 컬럼 이름은 최대 30 bytes 까지 가능. 즉 한글로 테이블 이름을 생성할 경우 최대 15글자 까지만 가능.
3. 테이블 이름은 한 명의 사용자가 다른 오브젝트들의 이름과 중복으로 사용할 수 없음. ( 예를 들어 scott 사용자가 테이블명을 test 로 생성한 후 다른 테이블 이름을 test 로 동일하게 사용할 수 없다는 것. 그러나 다른 사용자인 hr 사용자는 test 테이블 생성할 수 있음)
4. 테이블 이름이나 오브젝트 이름을 오라클이 사용하는 키워드를 사용하지 말것. (미리 정해진 SELECT , FROM 등과 같은 키워드로 생성이 안되는 것은 아니지만 사용하지 않는것을 권장.)

(1) 일반 테이블 생성하기

SCOTT>CREATE TABLE new_table
  2  ( no    NUMBER(3),
  3    name  VARCHAR2(10),
  4    birth DATE ) ;
Table created.

No 컬럼은 숫자(NUMBER) 데이터만 들어갈 수 있고 최대 길이는 3자리. Name 컬럼은 가변형 문자(VARCHAR2), 최대 길이는 10 bytes 까지. Birth 컬럼은 날짜(DATE) 데이터.

값을 입력하지 않을경우 NULL값이 자동으로 입력됨. 

(2) 기본 입력 값 설정과 함께 테이블 생성하기

 SCOTT>CREATE TABLE tt02
  2  ( no        NUMBER(3,1)    DEFAULT 0,
  3    name      VARCHAR2(10)   DEFAULT 'NO NAME',
  4    hiredate  DATE           DEFAULT SYSDATE ) ;

  Table created.

사용자가 값을 입력하지 않을 경우 DEFAULT 뒤에 적힌 값을 자동으로 넣으라는 의미

(3) Global Temporary Table (임시 테이블) 생성하기

CREATE GLOBAL TEMPORARY TABLE 테이블명
( COL1  데이터타입,
  COL2  테이터타입,  ...., )
ON COMMIT [delete | preserve] ROWS ;

1. 저장 목적이 아닌 임시 작업용(테스트나 조회용)으로 이 테이블은 마치 뷰 처럼 테이블을 생성하면 그 정의만 딕셔너리에 저장돼 있다가 사용자가 해당 테이블에 엑세스하면 메모미 상에 해당 테이블을 만들고 데이터를 가져옴.
2. 예를 들어 어떤 기능을 테스트를 하기 위해 데이터가 잠시 필요할 경우 일반 테이블을 만들고 데이터를 입력하면 리두 로그가 생성돼 시간도 오래 걸리는데 Temporary Table 을 사용할 경우 아주 빠르게 생성됨. 그리고 작업이 끝나면 자동으로 내용을 삭제까지 해주므로 편함.
3.그리고 세션별로 만들어지기 때문에 다른 세션에서 테이블을 공유할 수 없다.

다른 특징들

1. Redo Log 를 생성하지 않음.
2. Index , View , Trigger 를 생성할 수 있으나 이 오브젝트들의 타입도 전부 Temporary 임.
3. 이 테이블에 들어 있는 데이터는 이전이나 백업을 할 수 없음.

실습1. 터미널 2개 열어서 한쪽에서 생성후 다른쪽에서 조회하기

SCOTT>CREATE GLOBAL TEMPORARY TABLE temp01
  2  (  no    NUMBER ,
  3     name  VARCHAR2(10) )
  4  ON COMMIT delete ROWS;

Table created.

SCOTT>INSERT INTO temp01 VALUES(1, 'AAAAA');

1 row created.

SCOTT>SELECT * FROM temp01;

        NO NAME
---------- ----------
         1 AAAAA

SQL> SELECT * FROM temp01;

no rows selected

이와같이 다른 터미널에서는 조회 안됨. 같은 계정이라도 다른 창에서는 다른 사람이 어떤 작업을 하는지 알수 없다.
세션 1에서 아직 COMMIT 을 수행하지 않았기 때문에 temp01 테이블에 데이터가 있지만 TEMPORARY TABLE 생성 옵션이 ON COMMIT delete ROWS 이므로 커밋을 하면 모두 삭제됨

실습2. 생성되어 있는 temporary table 조회하기

SCOTT>SELECT temporary, duration
  2  FROM user_tables
  3  WHERE table_name = 'TEMP01';

T DURATION
- ------------------------------
Y SYS$TRANSACTION

(4) 테이블 복사하기 (CTAS)

새로운 테이블을 생성 할 때 기존에 만들어져 있는 테이블을 참조하여 생성하는 방법.

모든 컬럼 다 복사하기 / 특정 컬럼만 복사하기

SCOTT>CREATE TABLE dept3
  2  AS
  3    SELECT * FROM dept2;

Table created.

SCOTT>CREATE TABLE dept4
  2  AS
  3    SELECT dcode, dname
  4    FROM dept2;

Table created.

테이블의 구조(컬럼)만 가져오기 (데이터X)

SCOTT>CREATE TABLE dept5
  2  AS SELECT * FROM dept2
  3     WHERE 1 = 2 ;
Table created.

SCOTT>SELECT * FROM dept5;
no rows selected

이 방법은 주로 데이터는 필요 없이 테이블 구조만 가져올 때 많이 사용하는 방식. 3번 WHERE 줄에 틀린 조건을 줄 경우 그 조건에 해당되는 데이터가 없기 때문에 데이터는 못 가져오고 테이블 구조만 생성하는 것

(5) 가상 컬럼 테이블 생성하기

SCOTT>CREATE TABLE vt1
  2  ( col1 NUMBER,
  3    col2 NUMBER,
  4    col3 NUMBER GENERATED ALWAYS AS (col1 + col2) );

Table created.

col3은 col1+col2의 값을 가지는 가상컬럼.

SCOTT>INSERT INTO vt1 VALUES (1,2,3);
INSERT INTO vt1 VALUES (1,2,3)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

SCOTT>INSERT INTO vt1 (col1, col2) VALUES (1,2);

1 row created.

위와 같이 가상 컬럼에는 사용자가 데이터를 입력못함

SCOTT>SELECT * FROM vt1 ;

      COL1       COL2       COL3
---------- ---------- ----------
         1          2          3

col3에는 데이터를 입력하지 않았지만 자동으로 테이블생성시 계산된 식의값으로 입력됨을 알수있음

SCOTT>UPDATE vt1
  2  SET col1 = 5;

1 row updated.

SCOTT>SELECT * FROM vt1;

      COL1       COL2       COL3
---------- ---------- ----------
         5          2          7

기존컬럼 값이 변경될 경우 즉시 가상 컬럼에도 반영됨

SCOTT>ALTER TABLE vt1
  2  ADD (col4 GENERATED ALWAYS AS ((col1+col2)+col2));

Table altered.

SCOTT>SELECT * FROM vt1;

      COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
         5          2          7          9

SCOTT>SELECT column_name,
  2          data_type,
  3          data_default
  4  FROM  user_tab_columns
  5  WHERE table_name = 'VT1'
  6  ORDER BY column_id;

COLUMN_NAM DATA_TYPE  DATA_DEFAULT
---------- ---------- -------------------------
COL1       NUMBER
COL2       NUMBER
COL3       NUMBER     "COL1"+"COL2"
COL4       NUMBER     "COL1"+"COL2"+"COL2"

SCOTT>CREATE TABLE sales10
  2  ( no     NUMBER,
  3    pcode  CHAR(4),
  4    pdate  CHAR(8),
  5    pqty   NUMBER,
  6    pbungi NUMBER(1)
  7     GENERATED ALWAYS AS
  8      (
  9        CASE
 10            WHEN SUBSTR(pdate,5,2) IN ('01','02','03') THEN 1
 11            WHEN SUBSTR(pdate,5,2) IN ('04','05','06') THEN 2
 12            WHEN SUBSTR(pdate,5,2) IN ('07','08','09') THEN 3
 13            ELSE 4
 14        END  ) virtual );

Table created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(1,'100','20110112',10);

1 row created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(2,'200','20110505',20);

1 row created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(3,'300','20110812',30);

1 row created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(4,'400','20111024',40);

1 row created.

SCOTT>COMMIT;

Commit complete.

SCOTT>SELECT * FROM sales10;

        NO PCOD PDATE          PQTY     PBUNGI
---------- ---- -------- ---------- ----------
         1 100  20110112         10          1
         2 200  20110505         20          2
         3 300  20110812         30          3
         4 400  20111024         40          4

ALTER

만들어져 있는 오브젝트를 변경하는 명령어.
테이블의 경우, 컬럼을 추가하거나 삭제하고 컬럼이름이나 테이블 이름을 바꾸는 등의 작업을 할때 사용함.(부하가 많이 걸리는 명령어니 사용량이 많은 시간에 수행하는 것은 위험)

(1) 새로운 컬럼 추가하기

SCOTT>CREATE TABLE dept6
  2  AS
  3    SELECT dcode, dname
  4    FROM   dept2
  5    WHERE  dcode IN(1000,1001,1002);

Table created.

SCOTT>SELECT * FROM dept6;

DCODE  DNAME
------ ------------------------------
1000   Management Support Team
1001   Financial Management Team
1002   General affairs

위의 코드에서 LOCATION 컬럼 추가

SCOTT>ALTER TABLE dept6
  2  ADD (location VARCHAR2(10));

Table altered.

SCOTT>SELECT * FROM dept6;

DCODE  DNAME                          LOCATION
------ ------------------------------ ----------
1000   Management Support Team
1001   Financial Management Team
1002   General affairs

기본값으로 NULL값으로 입력됨. NULL 이외의 다른 값을 기본값으로 입력하고 싶다면 DEFAULT 값 지정하면 됨.

SCOTT>ALTER TABLE dept6
  2  ADD (location2 VARCHAR2(10) DEFAULT 'SEOUL');

Table altered.

SCOTT>SELECT * FROM dept6;

DCODE  DNAME                          LOCATION   LOCATION2
------ ------------------------------ ---------- ----------
1000   Management Support Team                   SEOUL
1001   Financial Management Team                 SEOUL
1002   General affairs                           SEOUL

(2) 컬럼이름 변경하기

SCOTT>ALTER TABLE dept6
  2  RENAME COLUMN location2
  3  TO loc;

SCOTT>RENAME dept6 TO dept7; // 테이블 이름변경

(3) 컬럼의 데이터 크기 변경하기

SCOTT>DESC dept7;
 Name              Null?    Type
 ----------------- -------- ------------------------
 DCODE                        VARCHAR2(6)
 DNAME                        NOT NULL VARCHAR2(30)
 LOCATION                     VARCHAR2(10)
 LOC                          VARCHAR2(10)

SCOTT>ALTER TABLE dept7
  2  MODIFY (loc  VARCHAR2(20));

Table altered.

SCOTT>DESC dept7;
 Name              Null?    Type
 ----------------- -------- ------------------------
 DCODE                        VARCHAR2(6)
 DNAME                        NOT NULL VARCHAR2(30)
 LOCATION                     VARCHAR2(10)
 LOC                          VARCHAR2(20)

(4) 컬럼 삭제하기

SCOTT>ALTER TABLE dept7
  2  DROP COLUMN loc;

SCOTT>ALTER TABLE dept7
  2  DROP COLUMN loc CASCADE CONSTRAINTS;

참조키로 설정돼있는 부모테이블의 컬럼을 삭제 할 경우에는 두번째처럼 제약조건을 설정하면됨.

(5) 읽기 전용 테이블로 변경하기

SCOTT>CREATE TABLE t_readyonly
  2  ( no NUMBER,
  3    name VARCHAR2(10) ) ;

Table created.

SCOTT>INSERT INTO t_readyonly
  2  VALUES (1, 'AAA');

1 row created.

SCOTT>COMMIT;

Commit complete.

SCOTT>SELECT * FROM t_readyonly;

        NO NAME
---------- ----------
         1 AAA

SCOTT>ALTER TABLE t_readyonly read only;

Table altered.

읽기전용으로 변경후 테이블에 데이터 INSERT하거나 컬럼추가 하려고 하면 에러남. DROP TABLE로 테이블은 삭제가능.

읽기전용인 테이블을 다시 읽기/쓰기 모드로 변경하기

SCOTT>ALTER TABLE t_readonly read write;

현재 테이블이 읽기 전용인지를 조회하려면 user_tables 의 read_only 컬럼의 값이 YES 이면 읽기전용이고 NO 이면 읽기/쓰기 모드.

TRUNCATE

테이블의 데이터를 전부삭제. 테이블은 유지.
해당 테이블의 데이터가 모두 삭제되지만 테이블 자체는 지워지는 것이 아니며 생성되어 있던 인덱스의 내용도 함께 TRUNCATE됨.

SCOTT>TRUNCATE TABLE dept7;

DROP

테이블 자체를 삭제하는 명령어

SCOTT>DROP TABLE dept7;

DELETE, TRUNCATE, DROP 비교

Data Dictionary

오라클은 데이터베이스를 운영하기 위한 정보들을 모두 특정테이블들에 모아두고 관리하는데 그 테이블들을 데이터 딕셔너리 라고 한다. 이 딕셔너리에는 아래와 같은 주요 정보들이 저장됨.

이외에도 많은 정보들이 저정되어 있고, 이 정보들은 사람으로 비유하면 두뇌에 해당 되는 정보들이기 때문에 만약 장애나 잘못 관리 될 경우 오라클 데이터베이스를 사용할 수 없고 더 심할 경우 장애 시 아예 복구조차 할 수 없게 될 수도 있다. 그래서 이 딕셔너리를 Base Table 과 Data Dictionary View 로 나누어 두고 Base Table 은 DBA 라 할 지라도 접근을 못하게 막아놨다. 그리고 사용자(DBA 포함)들은 Data Dictionary View 를 통해서만 딕셔너리를 SELECT 할 수 있게 허용함.

만약 데이터베이스에 변경 사항이 생겨 (예를 들어 새로운 테이블이 생성된다든지 하는 상황) 딕셔너리 내용을 변경해야 할 경우 사용자가 직접 수동으로 딕셔너리를 변경하지 못하고 해당 DDL 문장을 수행하는 순간 SERVER Process 가 사용자를 대신해서 해당 딕셔너리 내용을 변경해 줌.

Data Dictionary View 는 크게 2가지 종류가 있다.

• Static Data Dictionary View
  • USER_XXX : 해당 사용자가 생성한 오브젝트만 조회 할수 있음.
  • ALL_XXX : 해당 사용자가 생성한 오브젝트를 포함하여 해당 사용자가 접근 가능한 모든 오브젝트를 조회 할 수 있음.
  • DBA_XXX : 데이터베이스 내의 거의 모든 오브젝트들을 볼 수 있지만 DBA권한을 가진 사람만 이 딕셔너리를 조회 할 수 있음.
• Dynamic Performance View
  • v$XXXX

STATIC DATA DICTIONARY VIEW

연습용 테이블 static_table 생성하고 데이터입력

SCOTT>CREATE TABLE st_table
  2  (no number);

Table created.

SCOTT>BEGIN
  2     FOR i IN 1..1000 LOOP
  3        INSERT INTO st_table VALUES (i);
  4     END LOOP;
  5  COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.

데이터 딕셔너리를 조회해 해당 테이블에 데이터가 몇 건 있는지 확인

SCOTT>SELECT COUNT(*) FROM st_table;

  COUNT(*)
----------
      1000

SCOTT>SELECT num_rows, blocks
  2  FROM user_tables
  3  WHERE table_name='ST_TABLE';

  NUM_ROWS     BLOCKS
---------- ----------

실제 데이터는 1000건이 있지만 딕셔너리 내용이 변경이 안되서 값이 조회 되지 않음.

딕셔너리 수동으로 업데이트 한 후 다시 조회

SCOTT>ANALYZE TABLE st_table COMPUTE STATISTICS;

Table analyzed.

SCOTT>SELECT num_rows, blocks
  2  FROM user_tables
  3  WHERE table_name='ST_TABLE';

  NUM_ROWS     BLOCKS
---------- ----------
      1000          5

ANALYZE 명령어는 실제 테이블이나 인덱스, 클러스터 등을 하나씩 조사해서 그 결과를 딕셔너리에 반영시키는 명령어

Dynamic Performance View

Static Data Dictionary 와 반대로 Dynamic Performance View 는 정보를 실시간으로 조회하게 되며 사용자가 Dynamic Performance View 에 조회를 하게 되면 오라클은 해당 정보를 Control file 이나 현재 메모리에서 조회하여 보여준다.

연습문제

1) 아래와 같은 구조의 일반 테이블을 생성하시오.

SCOTT>CREATE TABLE new_emp
  2  ( no   NUMBER(5),
  3    name VARCHAR2(20),
  4    hiredate DATE,
  5    bonus NUMBER(6,2) );

Table created.

2) 위 1번 문제에서 생성한 new_emp 테이블에서 NO , NAME , HIREDATE 컬럼만 가져와서 아래 그림과 같이 new_emp2 테이블을 생성하는 쿼리를 쓰세요.

SCOTT>CREATE TABLE new_emp2
  2  AS
  3    SELECT no, name, hiredate
  4    FROM new_emp;

3) 위 2번 문제에서 생성한 new_emp2 테이블과 동일한 구조의 테이블을 new_emp3 이름으로 생성하되 테이블 구조만 가져오고 데이터는 가져오지 않도록 하는 쿼리를 쓰세요.

SCOTT>CREATE TABLE new_emp3
  2  AS
  3    SELECT no, name, hiredate
  4    FROM new_emp2
  5    WHERE 1 = 2;

4) 위 2번 문제에서 생성한 new_emp2 테이블에 DATE 타입을 가진 BIRTHDAY 컬럼을 추가하는 쿼리를 쓰세요. 단 해당 컬럼이 추가될 때 기본값으로 현재날짜 ( SYSDATE ) 가 자동으로 입력되도록 하세요.

SCOTT>ALTER TABLE new_emp2
  2  ADD (birthday DATE DEFAULT SYSDATE);

5) 위 4번 문제에서 생성한 new_emp2 테이블의 BIRTHDAY 컬럼 이름을 BIRTH 로 변경하는 쿼리를 쓰세요.

SCOTT>ALTER TABLE new_emp2
  2  RENAME COLUMN birthday TO birth;

6) 위 4번 문제에서 생성한 new_emp2 테이블의 NO 컬럼의 길이를 NUMBER(7) 로 변경하는 쿼리를 쓰세요

SCOTT>ALTER TABLE new_emp2
  2  MODIFY (no NUMBER(7));

7) new_emp2 테이블의 컬럼 중에서 BIRTH 컬럼을 삭제하는 쿼리를 쓰세요.

SCOTT>ALTER TABLE new_emp2
  2  DROP COLUMN birth;

8) new_emp2 테이블의 컬럼은 남겨 놓고 데이터만 지우는 쿼리를 쓰세요.

SCOTT>TRUNCATE TABLE new_emp2;

9) new_emp2 테이블을 완전히 삭제하는 쿼리를 쓰세요

SCOTT>DROP TABLE new_emp2;

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

 JOIN

정규화를 수행하면 하나의 테이블이 여러 개의 테이블들로 나누어져서 저장되고, 이때 여러 테이블에 흩어져 있는 데이터들을 조합해서 가져오는 기술.

Join 문법은Oracle 용 문법이 있고 모든 제품들에서 공통적으로 사용 가능한 표준 (ANSI ) join 문법이 있음.

SQL> SELECT a.col1, b.col1         // 테이블별칭.컬럼명
2  FROM   table1 a, table2 b     // table1의 별칭 = a
3  WHERE a.col2 = b.col2;

SQL> SELECT a.col1, b.col1
2  FROM   table1 a [INNER] JOIN table2 b
3  ON a.col2 = b.col2;

• INNER : default. 조인에 참여하는 모든 테이블에 존재하는 데이터만 출력(equi join, non-equi join)
• OUTER : 한쪽 테이블에는 데이터가 있고 한쪽 테이블에 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 출력(outer join)

조인이 수행될 때는 두 개 이상의 테이블이 사용되는데 이때 둘 중 하나의 테이블을 먼저 읽고 조인 조건 절을 확인하여 나머지 테이블에 가서 데이터를 가져 오게 됨.

먼저 읽는 테이블을 선행 테이블 (driving table 또는 Inner table) 이라고 하고 뒤에 읽는 테이블을 후행 테이블 (driven table 또는 Outer table) 이라고 함. 그리고 선행 테이블은 조회할 데이터가 적은 테이블로 선택해야 속도 면에서 유리.

Cartesian Product (카티션 곱)

join 쿼리 중에 WHERE 절에 기술하는 join 조건이 잘못 되었거나 지정하지 않을경우 해당 테이블에 대한 모든 데이터를 전부 가져오게됨. (ANSI SQL 에서는 CROSS JOIN 이라고도 함)

예제

SQL>CREATE TABLE cat_a (no NUMBER , name VARCHAR2(1));
Table created.
SQL>INSERT INTO cat_a VALUES (1,'A');
1 row created.
SQL>INSERT INTO cat_a VALUES (2,'B');
1 row created.

SQL>CREATE TABLE cat_b (no NUMBER, name VARCHAR2(1));
Table created.
SQL>INSERT INTO cat_b VALUES (1,'C');
1 row created.
SQL>INSERT INTO cat_b VALUES (2,'D');
1 row created.

SQL>CREATE TABLE cat_c (no NUMBER, name VARCHAR2(1));
Table created.
SQL>INSERT INTO cat_c VALUES (1,'E');
1 row created.
SQL>INSERT INTO cat_c VALUES (2,'F');
1 row created.

SQL>commit;
Commit complete.

SQL>SELECT * FROM cat_a;

        NO NAME
---------- -----
         1 A
         2 B

SQL>SELECT * FROM cat_b;

        NO NAME
---------- -----
         1 C
         2 D

SQL>SELECT * FROM cat_c;

        NO NAME
---------- -----
         1 E
         2 F

SQL>SELECT a.name, b.name
  2  FROM cat_a a, cat_b b
  3  WHERE a.no = b.no;

NAME  NAME
----- -----
A     C
B     D

SQL>SELECT a.name, b.name
  2  FROM cat_a a, cat_b b;         // where 조건 빠짐

NAME  NAME
----- -----
A     C
A     D
B     C
B     D

SQL>SELECT a.name, b.name, c.name
  2  FROM cat_a a, cat_b b, cat_c c
  3  WHERE a.no = b.no
  4  AND a.no = c.no;

NAME  NAME  NAME
----- ----- -----
A     C     E
B     D     F

SQL>SELECT a.name, b.name, c.name
  2  FROM cat_a a, cat_b b, cat_c c
  3  WHERE a.no = b.no;              // cat_c 테이블 조건이 빠짐

NAME  NAME  NAME
----- ----- -----
A     C     E
A     C     F
B     D     E
B     D     F

테스트용 테이블에 1만 건 정도 insert 한 후 카티션 곱을 사용하면 1만 X 1만 = 1억 건의 데이터가 순식간에 생기게 되는데 이처럼 현업에서 쿼리 성능을 테스트할때 의도적으로 대량의 테이블을 생성해 테스트 하는 경우도 있음.

EQUI Join (등가 Join)

가장 많이 사용되는 조인으로 선행 테이블에서 데이터를 가져 온 후 조인 조건절을 검사해서 동일한 조건을 가진 데이터를 후행 테이블에서 꺼내 오는 방법. (조건절에서 Equal 연산자 (=) 를 사용해서 EQUI Join)

ex1) emp 테이블과 dept 테이블 조회하여 이름과 부서명 출력하기

    SQL>SELECT e.empno, e.ename, d.dname
    2  FROM emp e, dept d
    3  WHERE e.deptno = d.deptno;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7782 CLARK      ACCOUNTING
      7839 KING       ACCOUNTING
      7566 JONES      RESEARCH
      7369 SMITH      RESEARCH
      7654 MARTIN     SALES
      7844 TURNER     SALES

    SQL>SELECT e.empno, e.ename, d.dname
    2  FROM emp e JOIN dept d
    3  ON e.deptno = d.deptno;

컬럼 이름이 하나의 테이블에만 있을 경우에는 테이블 이름을 생략해도 자동으로 테이블 이름을 찾아서 실행 하기도 함. 그러나 양쪽 테이블에 모두 있는 컬럼일 경우는 반드시 테이블 이름을 적어야 함.

ex2) 학생 테이블(student)과 교수 테이블(professor)을 join 하여 학생의 이름과 지도교수 이름 출력하기

SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno;

STU_NAME                       PROF_NAME
------------------------------ --------------------
James Seo                      Audie Murphy
Billy Crystal                  Angela Bassett
Richard Dreyfus                Angela Bassett
Tim Robbins                    Winona Ryder
Rene Russo                     Winona Ryder
Nicholas Cage                  Michelle Pfeiffer
Sandra Bullock                 Julia Roberts
Demi Moore                     Meryl Streep
Macaulay Culkin                Meryl Streep
Wesley Snipes                  Susan Sarandon
Danny Glover                   Nicole Kidman

SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s JOIN professor p
  3  ON s.profno = p.profno;

Equi join 의 특성:
양쪽 테이블에 모두 데이터가 존재해야 결과가 나오는데 위의예제에서 5명의 학생은 지도 교수가 결정이 안된 상태라서 결과에서 빠졌음. 만약 이렇게 한쪽테이블에 없는 데이터까지 모두 출력하고 싶다면 Outer Join 을 사용하면 됨.

ex3) 학생 테이블(student)과 학과 테이블(department) , 교수 테이블(professor) 을 Join하여 학생의 이름과 학생의 학과이름, 학생의 지도교수 이름 출력하기

SQL>SELECT s.name "STU_NAME", d.dname "DEPT_NAME", p.name "PROF_NAME"
  2  FROM student s, department d, professor p
  3  WHERE s.deptno1 = d.deptno
  4  AND s.profno = p.profno;

STU_NAME             DEPT_NAME                           PROF_NAME
-------------------- ----------------------------------- --------------------
James Seo            Computer Engineering                Audie Murphy
Billy Crystal        Computer Engineering                Angela Bassett
Richard Dreyfus      Computer Engineering                Angela Bassett
Tim Robbins          Multimedia Engineering              Winona Ryder
Rene Russo           Multimedia Engineering              Winona Ryder
Nicholas Cage        Multimedia Engineering              Michelle Pfeiffer
Sandra Bullock       Software Engineering                Julia Roberts
Macaulay Culkin      Electronic Engineering              Meryl Streep
Demi Moore           Electronic Engineering              Meryl Streep
Wesley Snipes        Electronic Engineering              Susan Sarandon
Micheal Keaton       Mechanical Engineering              Nicole Kidman
Danny Glover         Mechanical Engineering              Nicole Kidman

SQL>SELECT s.name "STU_NAME", d.dname "DEPT_NAME", p.name "PROF_NAME"
  2  FROM student s JOIN department d
  3  ON s.deptno1 = d.deptno
  4  JOIN professor p
  5  ON s.profno = p.profno5

ANSI Join 의 경우는 쿼리의 2번과 3번 행의 조건으로 먼저 Join 을 수행 후 나온 결과 값을 가지고 4번과 5번 행의 조건으로 Join 을 수행. Join 테이블이 더 늘어날 경우 계속 JOIN~ON 조건을 추가 하면 됨.

ex4) student 테이블을 조회하여 1전공(deptno1)이 101번인 학생들의 이름과 각 학생들의 지도교수 이름 출력하기.

SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno
  4  AND s.deptno1 = 101;

STU_NAME             PROF_NAME
-------------------- --------------------
James Seo            Audie Murphy
Richard Dreyfus      Angela Bassett
Billy Crystal        Angela Bassett

SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s JOIN professor p
  3  ON s.profno = p.profno
  4  AND s.deptno1 = 101;

3번 줄은 Join 을 수행하기 위한 조건이라서 Join 조건이라 부르고 4번 줄은 검색을 하는 용도로 사용되기 때문에 검색 조건이라고 부름. 위와 같이 Join조건과 검색조건이 동시에 있을 경우 당연히 검색 조건을 먼저 검색해서 데이터의 검색 범위를 줄여 놓고 Join 작업을 수행하게 된다. 검색 조건을 잘 활용해야 Join 작업의 속도가 빨라짐.

Non-Equi Join (비등가 Join)

같은 조건이 아닌 크거나 작거나 하는 경우의 조건으로 조회를 해야 할 경우.

ex1) Customer 테이블과 gift 테이블을 Join하여 고객별로 마일리지 포인트를 조회한 후 해당 마일리지 점수로 받을 수 있는 상품을 조회하여 고객의 이름과 받을 수 있는 상품 명 출력하기

SQL> SELECT c.gname "C_NAME", TO_CHAR(c.point, '999,999') "POINT",
  2         g.gname "G_NAME"
  3  FROM customer c, gift g
  4  WHERE c.point BETWEEN g.g_start AND g.g_end;      // 비트윈보다 비교연산자가 더 성능좋음

C_NAME                         POINT    G_NAME
------------------------------ -------- ------------------------------
Bill Pullman                     65,000 Tuna Set
Mel Gibson                       73,000 Tuna Set
Brad Pitt                       110,000 Shampoo Set
Samuel Jackson                  153,000 Shampoo Set
Arnold Scharz                   265,000 Car wash Set
Ahnjihye                        273,000 Car wash Set
Jim Carrey                      315,000 Kitchen Supplies Set

SQL> SELECT c.gname "C_NAME", TO_CHAR(c.point, '999,999') "POINT",
  2         g.gname "G_NAME"
  3  FROM customer c JOIN gift g
  4  ON c.point >= g.g_start
  5  AND c.point <= g.g_end;           //    비교연산자 사용

ex2) Student 테이블과 score 테이블 , hakjum 테이블을 조회하여 학생들의 이름과 점수와 학점 출력하기

SQL> SELECT s.name "STU_NAME", r.total "SCORE", h.grade "GRADE"
  2  FROM student s, score r, hakjum h
  3  WHERE s.studno = r.studno
  4  AND r.total >= h.min_point
  5  AND r.total <= h.max_point;

STU_NAME                            SCORE GRA
------------------------------ ---------- ---
James Seo                              97 A+
Macaulay Culkin                        95 A0
Richard Dreyfus                        89 B+
Anthony Hopkins                        84 B0
Sandra Bullock                         83 B0
Bill Murray                            79 C+
Rene Russo                             78 C+
Demi Moore                             62 D

SQL> SELECT s.name "STU_NAME", r.total "SCORE", h.grade "GRADE"
  2  FROM student s JOIN score r
  3  ON s.studno = r.studno
  4  JOIN hakjum h
  4  ON r.total >= h.min_point
  5  AND r.total <= h.max_point;

OUTER Join

Inner Join(등가,비등가조인)과는 반대로 한쪽 테이블에는 데이터가 있고 한쪽 테이블에 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 출력하게 하는 방법. (Oracle Join 과 ANSI Join 구문이 차이가 있음.)

ex1) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름을 출력. 단, 지도교수가 결정되지 않은 학생의 명단도 함께 출력하기

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno(+);      //데이터가 없는쪽에 (+)표시 추가

STU_NAME                       PROF_NAME
------------------------------ --------------------
James Seo                      Audie Murphy
Richard Dreyfus                Angela Bassett
Billy Crystal                  Angela Bassett
Tim Robbins                    Winona Ryder
Rene Russo                     Winona Ryder
Nicholas Cage                  Michelle Pfeiffer
Sandra Bullock                 Julia Roberts
Macaulay Culkin                Meryl Streep
Demi Moore                     Meryl Streep
Wesley Snipes                  Susan Sarandon
Steve Martin                   Nicole Kidman
Micheal Keaton                 Nicole Kidman
Danny Glover                   Nicole Kidman
Daniel Day-Lewis               Jodie Foster
Bill Murray                    Jodie Foster
Anthony Hopkins
Charlie Sheen
Christian Slater
Sean Connery
Danny Devito

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s LEFT OUTER JOIN professor p
  3  ON s.profno = p.profno;

Oracle Outer Join 의 경우는 데이터가 없는 쪽에 (+) 표시를 하지만 ANSI Outer Join 은 데이터가 존재하는 쪽에 표시를 함. ON 조건절중에 = 기호를 기준으로 왼쪽인 학생 데이터는 존재하고 교수는 존재하지 않으니까 LEFT.

ex2) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름 출력하기. 단 지도학생이 결정되지 않은 교수의 명단도 함께 출력

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno(+) = p.profno
  4  ORDER BY s.name;

STU_NAME                       PROF_NAME
------------------------------ --------------------
Bill Murray                    Jodie Foster
Billy Crystal                  Angela Bassett
Daniel Day-Lewis               Jodie Foster
Danny Glover                   Nicole Kidman
Demi Moore                     Meryl Streep
James Seo                      Audie Murphy
Macaulay Culkin                Meryl Streep
Micheal Keaton                 Nicole Kidman
Nicholas Cage                  Michelle Pfeiffer
Rene Russo                     Winona Ryder
Richard Dreyfus                Angela Bassett
Sandra Bullock                 Julia Roberts
Steve Martin                   Nicole Kidman
Tim Robbins                    Winona Ryder
Wesley Snipes                  Susan Sarandon
                               Jessica Lange
                               Andie Macdowell
                               Meg Ryan
                               Holly Hunter
                               Sharon Stone
                               Whoopi Goldberg
                               Emma Thompson

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s RIGHT OUTER JOIN professor p
  3  ON s.profno = p.profno;

ex3) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름 출력하기. 단 지도학생이 결정 안 된 교수 명단과 지도 교수가 결정 안된 학생 명단을 한꺼번에 출력

Oracle에서는 양쪽 데이터를 모두 출력하려면 두 Outer Join 을 각 각 수행 한 후 Union 을 사용하여 결과를 인위적으로 합쳐서 출력을 시키는 방법을 많이 사용함.(ANSI 에서는 FULL OUTER JOIN 사용하면됨)

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno(+)
  4  UNION
  5  SELECT s.name "STU_NAME", p.name "PROF_NAME"
  6  FROM student s, professor p
  7  WHERE s.profno(+) = p.profno;

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s FULL OUTER JOIN professor p
  3  ON s.profno = p.profno;

STU_NAME                       PROF_NAME
------------------------------ --------------------
James Seo                      Audie Murphy
Rene Russo                     Winona Ryder
Sandra Bullock                 Julia Roberts
Demi Moore                     Meryl Streep
Danny Glover                   Nicole Kidman
Billy Crystal                  Angela Bassett
Nicholas Cage                  Michelle Pfeiffer
Micheal Keaton                 Nicole Kidman
Bill Murray                    Jodie Foster
Macaulay Culkin                Meryl Streep
Richard Dreyfus                Angela Bassett
Tim Robbins                    Winona Ryder
Wesley Snipes                  Susan Sarandon
Steve Martin                   Nicole Kidman
Daniel Day-Lewis               Jodie Foster
Danny Devito
Sean Connery
Christian Slater
Charlie Sheen
Anthony Hopkins
                               Emma Thompson
                               Andie Macdowell
                               Whoopi Goldberg
                               Meg Ryan
                               Sharon Stone
                               Jessica Lange
                               Holly Hunter

OUTER Join을 조심해야 하는 이유
모든 데이터를 다 출력할 수 있기 때문에 좋을 것 같지만 이 Join방식은 DB 성능에 아주 나쁜 영향을 줄 수 있다. 왜냐면 예를들어, A 테이블과 B 테이블을 Outer Join 을 수행해서 A 테이블에 있는 데이터를 다 검색하는 경우 만약 A 테이블에 인덱스가 있어도 인덱스를 쓰지 않고 Full Scan 을 하기 때문. 그리고 아우터 조인이 발생할 경우 튜닝에서 아주 중요하게 생각하는 조인 순서가 고정되어 사용자의 뜻대로 변경할 수 없기 때문에 심각한 문제를 유발 할 수 있다.

작성한 SQL 이 속도가 늦을 경우 실행계획을 확인하는 방법을 예시를 통해 알아보자.

SCOTT> conn / as sysdba ;
SYS>@?/sqlplus/admin/plustrce.sql
SYS>
SYS>drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SYS>create role plustrace;
Role created.

SYS>grant select on v_$sesstat to plustrace;
Grant succeeded.

SYS>grant select on v_$statname to plustrace;
Grant succeeded.

SYS>grant select on v_$mystat to plustrace;
Grant succeeded.

SYS>grant plustrace to dba with admin option;
Grant succeeded.

SYS>
SYS>set echo off

SYS>GRANT plustrace TO scott;
Grant succeeded.

SYS>CONN scott/tiger
Connected.
SCOTT>
SCOTT>@?/rdbms/admin/utlxplan.sql
Table created.

실행계획 확인할 준비 끝! 이제 쿼리를 수행하기 전에 실행계획을 사용하라고 설정을 한 후 쿼리를 수행하면 된다.

SCOTT>SET autot traceonly;
SCOTT>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno(+) = p.profno
  4  AND p.profno > 4004;

Execution Plan
----------------------------------------------------------
Plan hash value: 427587263

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    36 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN OUTER             |              |     1 |    36 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PROFESSOR    |     1 |    18 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0011128 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | STUDENT      |     2 |    36 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."PROFNO"(+)="P"."PROFNO")
   3 - access("P"."PROFNO">4004)
   4 - filter("S"."PROFNO"(+)>4004)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        608  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

실행계획 해석하는 방법 :
먼저 윗줄과 아랫줄을 비교해서 오른쪽으로 들여쓰기 된 줄을 먼저 실행한다. 그리고 만약 윗줄과 아랫줄이 들여쓰기가 같다면 윗 줄부터 실행한다.
위 실행계획(Execution Plan)에서 id 가 0 번줄과 1번줄을 비교하면 1번줄을 먼저 해야 하고 1번 줄과 2번 줄을 비교하면 2번줄을 먼저 수행해야 하는데 2번 밑에 3번줄이 있기 때문에 2번줄을 수행하기 전에 3번 줄과 한번 더 비교한다. 2번 줄과 3번 줄은 들여쓰기가 같으므로 위 실행계획에서 가장 먼저 실행되는 것은 id 가 2 번인 줄이며 TABLE ACCESS FULL 은 테이블 전체를 다 읽었다라는 뜻이다.
실행계획의 Name 컬럼을 보면 PROFESSOR 테이블을 먼저 읽었고 Rows 컬럼을 보면 3건을 가져왔다는 것을 알 수 있다.(즉 Professor 테이블을 전부 읽어서 데이터를 3 건 찾아오고 student 테이블도 전부 읽어서 2 건의 데이터를 가져왔다는 의미.)
현재 professor 테이블의 profno 컬럼에 인덱스가 있는 상황에서 일부 교수들만 찾으라고 쿼리를 작성했지만 위 화면에서 아래에서 두 번째 줄을 보면 TABLE ACCESS FULL PROFESSOR 부분에서 테이블을 모두 읽었다고 알려주고 있다. (예를 들어 교수 테이블에 100 만 건 데이터가 있고 그 중에서 1 건만 읽으라고 시켜도 OUTER Join 은 필요 없는 100 만 건 데이터를 모두 다 읽고 그 중에서 1 건을 선택을 한다는 의미)

만약 아우터 조인이 아닌 일반 조인으로 작업을 했을 경우에 해당 테이블에 인덱스가 있을 경우 아래와 같이 인덱스를 사용하는 방법을 선택. 일반적으로 이 방법이 더 빠른 방법. 

동일한 테이블에 동일한 조건으로 쿼리를 수행했을 때 OUTER Join 이 아닐경우는 속도가 빠른 방법을 선택하지만 OUTER Join 을 사용할 경우 속도가 늦어 진다는 것을 확인.

결론: OUTER JOIN은 쓰지말자.

SELF Join

원하는 데이터가 하나의 테이블에 다 들어 있을 경우(데이터를 가지고 있는 하나의 테이블을 메모리에서 별명을 두 개로 사용해서 호출하는 방법으로 2개의 테이블로 만든 후 일반적인 Join 작업을 수행함.)

ex) 사원들의 상사이름 출력하기

SQL> SELECT e1.ename "ENAME", e2.ename "MGR_ENAME"
  2  FROM emp e1, emp e2
  3  WHERE e1.mgr = e2.empno;

ENAME      MGR_ENAME
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
WARD       BLAKE
MARTIN     BLAKE
ALLEN      BLAKE
MILLER     CLARK
CLARK      KING
JONES      KING
BLAKE      KING
SMITH      FORD

SQL> SELECT e1.ename "ENAME", e2.ename "MGR_ENAME"
  2  FROM emp e1 JOIN emp e2
  3  ON e1.mgr = e2.empno;

연습문제

1) Emp2 테이블과 p_grade 테이블을 조회하여 사원들의 이름과 나이, 현재 직급 , 예상 직급 을 출력하시오. 예상 직급은 나이로 계산하며 해당 나이가 받아야 하는 직급을 의미함. 나이는 오늘(sysdate)을 기준으로 하되 trunc 로 소수점 이하는 절삭해서 계산.

SQL 문
접기
  
SQL> SELECT e.name "NAME",
  2         TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 "AGE",
  3         e.position "CURR_POSITION",
  4         p.position "BE_POSITION"
  5  FROM emp2 e RIGHT OUTER JOIN p_grade p
  6  ON TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 BETWEEN s_age AND e_age;

접기

SQL 문
접기
  
SQL> SELECT e.name "NAME",
  2         TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 "AGE",
  3         e.position "CURR_POSITION",
  4         p.position "BE_POSITION"
  5  FROM emp2 e, p_grade p
  6  WHERE TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 >= s_age 
  7  AND TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 <= e_age;

접기

2) customer 테이블과 gift 테이블을 Join하여 고객이 자기 포인트보다 낮은 포인트의 상품 중 한가지를 선택할 수 있다고 할 때 Notebook 을 선택할 수 있는 고객명과 포인트, 상품명을 출력하시오.

SQL 문
접기
  
SQL> SELECT c.gname "CUST_NAME", c.point "POINT",
  2         g.gname "GIFT_NAME"
  3  FROM customer c JOIN gift g
  4  ON g.gname = 'Notebook'
  5  AND   c.point <= g.g_end
  6  AND   c.point >= g.g_start ;

접기

SQL 문
접기
  
SQL> SELECT c.gname "CUST_NAME", c.point "POINT",
  2         g.gname "GIFT_NAME"
  3  FROM customer c, gift g
  4  WHERE g.gname = 'Notebook'
  5  AND   c.point <= g.g_end
  6  AND   c.point >= g.g_start ;

접기

3) professor 테이블에서 교수번호, 교수이름, 입사일, 자신보다 입사일 빠른 사람 인원수를 출력하시오. 단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력.

SQL 문
접기
  
SQL> SELECT p.profno, p.name, p.hiredate,
  2         COUNT(p2.hiredate) "COUNT"
  3  FROM professor p, professor p2
  4  WHERE p2.hiredate < p.hiredate
  5  GROUP BY p.profno, p.name, p.hiredate
  6  ORDER BY 4;

접기

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

 복수행 함수

단일 행 함수와 달리 한꺼번에 여러 건의 데이터가 함수로 입력됨.

GROUP 함수종류

대부분의 그룹함수는 함수에 * 를 사용하면 Null 을 포함하고 해당 컬럼에 데이터가 있는 경우만 ( 즉 NULL 값을 제외하고 ) 작업을 해서 출력함.

SCOTT>SELECT COUNT(*), COUNT(comm)
  2  FROM emp;

  COUNT(*) COUNT(COMM)
---------- -----------
        13           4

SCOTT>SET null --null-- ;
SCOTT>SELECT ename, comm
  2  FROM emp;

ENAME            COMM
---------- ----------
SMITH      --null--
ALLEN             300
WARD              500
JONES      --null--
MARTIN           1400
BLAKE      --null--
CLARK      --null--
SCOTT      --null--
KING       --null--
TURNER              0
JAMES      --null--
FORD       --null--
MILLER     --null--



SCOTT>SELECT COUNT(comm), SUM(comm)
  2  FROM emp;

COUNT(COMM)  SUM(COMM)
----------- ----------
          4       2200

주의해야할 사항은 모든 그룹함수를 사용할 때, null값이 존재할 경우 자동으로 null값은 제외하고 계산하기 때문에 의도치 않는 오류가 발생할 수 있다는 점이다. 그래서 NVL함수로 null값을 처리해줘야 정확한 계산이 나온다.

SCOTT>SELECT COUNT(comm), SUM(comm), AVG(comm)
  2  FROM emp;

COUNT(COMM)  SUM(COMM)  AVG(COMM)
----------- ---------- ----------
          4       2200        550

SCOTT>SELECT COUNT(*), SUM(comm), AVG(NVL(comm,0))
  2  FROM emp;

  COUNT(*)  SUM(COMM) AVG(NVL(COMM,0))
---------- ---------- ----------------
        13       2200       169.230769

여러 건의 데이터를 입력 받아서 순서대로 정렬을 하고 그중 최대값/최소값을 추출하기 때문에 시간이 오래 걸리는 함수 중 한가지이며 그렇기에 사용할 때 주의해야함. 최대값/ 최소값을 구할 때는 MAX / MIN 함수 대신 인덱스 권장함.

SCOTT>SELECT MAX(sal), MIN(sal)
  2  FROM emp;

  MAX(SAL)   MIN(SAL)
---------- ----------
      5000        800

SCOTT>SELECT MAX(hiredate) "MAX",
  2          MIN(hiredate) "MIN"
  3  FROM emp;

MAX          MIN
------------ ------------
19-APR-87    17-DEC-80

GROUP BY 절 사용하기

전체 값을 조회하는 것이 아니라 특정 조건을 주고 해당 조건에 맞는 결과를 출력.

SCOTT>SELECT deptno, AVG(NVL(sal,0)) "AVG"
  2  FROM emp
  3  GROUP BY deptno;          //부서별로 평균 급여 출력

    DEPTNO        AVG
---------- ----------
        30 1566.66667
        20    2443.75
        10 2916.66667

SCOTT>SELECT deptno, job, AVG(NVL(sal,0)) "AVG_SAL"
  2  FROM emp
  3  GROUP BY deptno, job       //부서별로 먼저 그룹핑후 같은 학과일 경우 직급별로 한번더 그룹핑
  4  ORDER BY 1,2;              //정렬

    DEPTNO JOB          AVG_SAL
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 ANALYST         3000
        20 CLERK            800
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        1400

GROUP BY 절 사용시 주의사항:
1. SELECT 절에 사용된 그룹함수 이외의 칼럼이나 표현식은 반드시 GROUP BY 절에 사용되어야 함.
2. GROUP BY 절에 사용된 컬럼이라도 SELECT 절에는 사용되지 않아도 됨.
3. 반드시 컬럼명이 사용되어야 함 (Alias 사용불가)

HAVING 절 사용하기 (그룹핑한 조건으로 검색)

WHERE 절은 그룹함수를 비교조건으로 쓸 수가 없음. 만약, 그룹함수를 조건으로 사용하고 싶을 경우에는 WHERE 대신에 HAVING 절을 사용하면 해결됨.

SCOTT>SELECT deptno, AVG(NVL(sal,0))
  2  FROM emp
  3  WHERE deptno > 10
  4  GROUP BY deptno
  5  HAVING AVG(NVL(sal,0)) > 2000;

    DEPTNO AVG(NVL(SAL,0))
---------- ---------------
        20         2443.75

기본적인것 이외의 GROUP 함수들

SCOTT>SELECT deptno, position, COUNT(*), SUM(PAY)
  2  FROM professor
  3  GROUP BY ROLLUP(deptno, position);

    DEPTNO POSITION                         COUNT(*)   SUM(PAY)
---------- ------------------------------ ---------- ----------
       101 instructor                              1        270
       101 a full professor                        1        550
       101 assistant professor                     1        380
       101                                         3       1200
       102 instructor                              1        250
       102 a full professor                        1        490
       102 assistant professor                     1        350
       102                                         3       1090
       103 instructor                              1        290
       103 a full professor                        1        530
       103 assistant professor                     1        330
       103                                         3       1150

실습을 위해 professor 테이블 복사 후 101번 학과에 중복된 직급 데이터를 추가로 입력.

SCOTT>CREATE TABLE professor2
  2  AS SELECT deptno, position, pay
  3     FROM professor;

Table created.

SCOTT>SELECT * FROM professor2;

    DEPTNO POSITION                              PAY
---------- ------------------------------ ----------
       101 a full professor                      550
       101 assistant professor                   380
       101 instructor                            270
       102 instructor                            250
       102 assistant professor                   350
       102 a full professor                      490
       103 a full professor                      530
       103 assistant professor                   330
       103 instructor                            290


SCOTT>INSERT INTO professor2 VALUES(101,'instructor', 100);

1 row created.

SCOTT>INSERT INTO professor2 VALUES(101,'a full professor', 100);

1 row created.

SCOTT>INSERT INTO professor2 VALUES(101,'assistant professor', 100);

1 row created.

SCOTT>commit;

Commit complete.

SCOTT>SELECT * FROM professor2
  2  ORDER BY deptno, position;

 D  EPTNO POSITION                              PAY
---------- ------------------------------ ----------
       101 a full professor                      100
       101 a full professor                      550
       101 assistant professor                   100
       101 assistant professor                   380
       101 instructor                            270
       101 instructor                            100
       102 a full professor                      490
       102 assistant professor                   350
       102 instructor                            250
       103 a full professor                      530
       103 assistant professor                   330
       103 instructor                            290

아래 예시에서 ROLLUP 함수는 GROUP BY 절에 주어진 주건으로 소계값을 구해줌. BUT, 각 부서별로 소계는 알수 있지만 교수가 몇 명 인지는 나오지 않음(즉 총 합계는 출력안됨) 그래서 전체 총 합계까지 출력하고 싶다면 ROLLUP 대신 CUBE 함수를 사용하면 됨

SCOTT>SELECT deptno, position, SUM(pay)
  2  FROM professor2
  3  GROUP BY deptno, ROLLUP(position);

    DEPTNO POSITION                         SUM(PAY)
---------- ------------------------------ ----------
       101 instructor                            370
       101 a full professor                      650
       101 assistant professor                   480
       101                                      1500
       102 instructor                            250
       102 a full professor                      490
       102 assistant professor                   350
       102                                      1090
       103 instructor                            290
       103 a full professor                      530
       103 assistant professor                   330
       103                                      1150
       201 a full professor                      570
       201 assistant professor                   330
       201                                       900
       202 instructor                            260
       202 assistant professor                   310
       202                                       570
       203 a full professor                      500
       203                                       500
       301 instructor                            220
       301 assistant professor                   290
       301                                       510

SCOTT>SELECT deptno, position,
  2          COUNT(*), SUM(pay)
  3  FROM professor
  4  GROUP BY CUBE(deptno, position);

    DEPTNO POSITION                         COUNT(*)   SUM(PAY)
---------- ------------------------------ ---------- ----------
                                                  16       5920
           instructor                              5       1290
           a full professor                        5       2640
           assistant professor                     6       1990
       101                                         3       1200
       101 instructor                              1        270
       101 a full professor                        1        550
       101 assistant professor                     1        380
       102                                         3       1090
       102 instructor                              1        250
       102 a full professor                        1        490
       102 assistant professor                     1        350
       103                                         3       1150
       103 instructor                              1        290
       103 a full professor                        1        530
       103 assistant professor                     1        330
       201                                         2        900
       201 a full professor                        1        570
       201 assistant professor                     1        330
       202                                         2        570
       202 instructor                              1        260
       202 assistant professor                     1        310
       203                                         1        500
       203 a full professor                        1        500
       301                                         2        510
       301 instructor                              1        220
       301 assistant professor                     1        290

SCOTT>SELECT deptno,
2          LISTAGG(ename, '->') WITHIN GROUP(ORDER BY hiredate) "LISTAGG"
3  FROM emp
4  GROUP BY deptno;

  DEPTNO       LISTAGG
---------- --------------------------------------------------
       10  CLARK->KING->MILLER
       20  SMITH->JONES->FORD->SCOTT
       30  ALLEN->WARD->BLAKE->TURNER->MARTIN->JAMES

QUIZ

1) emp 테이블을 사용하여 사원 중에서 급여(sal)와 보너스(comm)를 합친 금액이 가장 많은 경우와 가장 적은 경우 , 평균 금액을 구하세요. 단 보너스가 없을 경우는 보너스를 0 으로 계산하고 출력 금액은 모두 소수점 첫째 자리까지만 나오게 하세요

SQL 문
접기
  
SCOTT>SELECT MAX(sal+NVL(comm,0)) MAX,
  2          MIN(sal+NVL(comm,0)) MIN,
  3          ROUND(AVG(sal+NVL(comm,0)), 1) AVG
  4  FROM emp;

접기

2) student 테이블의 birthday 컬럼을 참조해서 아래와 같이 월별로 생일자수를 출력하세요.

SQL 문
접기
  
SCOTT>SELECT COUNT(*)||'EA' "TOTAL",
  2          COUNT(DECODE(SUBSTR(birthday,4,3), 'JAN', 1))||'EA' "JAN",
  3          COUNT(DECODE(SUBSTR(birthday,4,3), 'FEB', 1))||'EA' "FEB",
  4          COUNT(DECODE(SUBSTR(birthday,4,3), 'MAR', 1))||'EA' "MAR",
  5          COUNT(DECODE(SUBSTR(birthday,4,3), 'APR', 1))||'EA' "APR",
  6          COUNT(DECODE(SUBSTR(birthday,4,3), 'MAY', 1))||'EA' "MAY",
  7          COUNT(DECODE(SUBSTR(birthday,4,3), 'JUN', 1))||'EA' "JUN",
  8          COUNT(DECODE(SUBSTR(birthday,4,3), 'JUL', 1))||'EA' "JUL",
  9          COUNT(DECODE(SUBSTR(birthday,4,3), 'AUG', 1))||'EA' "AUG",
 10          COUNT(DECODE(SUBSTR(birthday,4,3), 'SEP', 1))||'EA' "SEP",
 11          COUNT(DECODE(SUBSTR(birthday,4,3), 'OCP', 1))||'EA' "OCP",
 12          COUNT(DECODE(SUBSTR(birthday,4,3), 'NOV', 1))||'EA' "NOV",
 13          COUNT(DECODE(SUBSTR(birthday,4,3), 'DEC', 1))||'EA' "DEC"
 14  FROM student; 


SCOTT>ed
Wrote file afiedt.buf

  1  SELECT COUNT(*)||'EA' "TOTAL",
  2          COUNT(DECODE(TO_CHAR(birthday,'mm'), '01', 1))||'EA' "JAN",
  3          COUNT(DECODE(TO_CHAR(birthday,'mm'), '02', 1))||'EA' "FEB",
  4          COUNT(DECODE(TO_CHAR(birthday,'mm'), '03', 1))||'EA' "MAR",
  5          COUNT(DECODE(TO_CHAR(birthday,'mm'), '04', 1))||'EA' "APR",
  6          COUNT(DECODE(TO_CHAR(birthday,'mm'), '05', 1))||'EA' "MAY",
  7          COUNT(DECODE(TO_CHAR(birthday,'mm'), '06', 1))||'EA' "JUN",
  8          COUNT(DECODE(TO_CHAR(birthday,'mm'), '07', 1))||'EA' "JUL",
  9          COUNT(DECODE(TO_CHAR(birthday,'mm'), '08', 1))||'EA' "AUG",
 10          COUNT(DECODE(TO_CHAR(birthday,'mm'), '09', 1))||'EA' "SEP",
 11          COUNT(DECODE(TO_CHAR(birthday,'mm'), '10', 1))||'EA' "OCT",
 12          COUNT(DECODE(TO_CHAR(birthday,'mm'), '11', 1))||'EA' "NOV",
 13          COUNT(DECODE(TO_CHAR(birthday,'mm'), '12', 1))||'EA' "DEC"
 14* FROM student

 

접기

3) Student 테이블의 tel 컬럼을 참고하여 아래와 같이 지역별 인원수를 출력하세요. 단 02 –서울 , 031 – 경기 , 051 – 부산 , 052 – 울산 , 053 – 대구 , 055 – 경남으로 출력하세요

SQL 문
접기
  
SCOTT>SELECT COUNT(*) "TOTAL",
  2          COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel,')')-1), '02', '*')) "SEOUL",
  3          COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel,')')-1), '031', '*')) "GYEONGGI",
  4          COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel,')')-1), '051', '*')) "BUSAN",
  5          COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel,')')-1), '052', '*')) "ULSAN",
  6          COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel,')')-1), '053', '*')) "DAEGU",
  7          COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel,')')-1), '055', '*')) "GYEONGNAM"
  8  FROM student;
 
접기

4) 먼저 emp 테이블에 아래의 두 건의 데이터를 입력 하신 후 작업하세요.

Emp 테이블을 사용하여 아래의 화면과 같이 부서별로 직급별로 급여 합계 결과를 출력하세요.

SCOTT>INSERT INTO emp (empno,deptno,ename,sal) 

        2 VALUES (1000,10,'Tiger',3600) ; 

SCOTT> INSERT INTO emp (empno,deptno,ename,sal)

         2 VALUES (2000,30,'Cat',3000); 

SCOTT> COMMIT ;

SQL 문
접기
  
SCOTT>SELECT deptno,
  2          SUM(DECODE(job, 'CLERK', sal, 0)) "CLERK",
  3          SUM(DECODE(job, 'MANAGER', sal, 0)) "MANAGER",
  4          SUM(DECODE(job, 'PRESIDENT', sal, 0)) "PRESIDENT",
  5          SUM(DECODE(job, 'ANALYST', sal, 0)) "ANALYST",
  6          SUM(DECODE(job, 'SALESMAN', sal, 0)) "SALESMAN",
  7          SUM(NVL2(job,sal,0)) "TOTAL"
  8  FROM emp
  9  GROUP BY ROLLUP(deptno)
 10  ORDER BY deptno;
 
접기

5) emp 테이블을 사용하여 직원들의 급여와 전체 급여의 누적 급여금액이 아래와 같도록 출력하세요. 단 급여를 오름차순으로 정렬해서 출력하세요.

SQL 문
접기
  
SCOTT>SELECT deptno, ename, sal,
  2          SUM(sal) OVER(ORDER BY sal) "TOTAL"
  3  FROM emp;
 
접기

6) fruit 테이블을 아래와 같은 형태로 출력하세요.

SQL 문
접기
  
SCOTT>SELECT MAX(DECODE(name, 'apple', price)) APPLE,
  2          MAX(DECODE(name, 'grape', price)) GRAPE,
  3          MAX(DECODE(name, 'orange', price)) ORANGE
  4  FROM fruit;
 
접기

7) student 테이블의 Tel 컬럼을 사용하여 아래와 같이 지역별 인원수와 전체대비 차지하는 비율을 출력하세요. (단 02 –서울 , 031 – 경기 , 051 – 부산 , 052 – 울산 , 053 – 대구 , 055 – 경남으로 출력하세요)

SQL 문
접기
  
SCOTT>SELECT COUNT(*)|| 'EA('|| (COUNT(*)/COUNT(*)*100)||'%)'  "TOTAL",
  2          COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1),'02','*'))||'EA('||(COUNT(DECODE(SUBSTR(tel,1,INSTR(
  3          COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1),'031','*'))||'EA('||(COUNT(DECODE(SUBSTR(tel,1,INSTR
  4          COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1),'051','*'))||'EA('||(COUNT(DECODE(SUBSTR(tel,1,INSTR
  5          COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1),'052','*'))||'EA('||(COUNT(DECODE(SUBSTR(tel,1,INSTR
  6          COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1),'053','*'))||'EA('||(COUNT(DECODE(SUBSTR(tel,1,INSTR
  7          COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1),'055','*'))||'EA('||(COUNT(DECODE(SUBSTR(tel,1,INSTR
  8  FROM student;

접기

8) emp 테이블을 사용하여 아래와 같이 부서별로 급여 누적 합계가 나오도록 출력하세요 단 부서번호로 오름차순 출력하세요.

SQL 문
접기
  
SCOTT>SELECT deptno, ename, sal,
  2          SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) "TOTAL"
  3  FROM emp;
 
접기

9) emp 테이블을 사용하여 아래와 같이 각 사원의 급여액이 전체 직원 급여총액에서 몇 %의 비율을 차지하는지 출력하세요. 단 급여 비중이 높은 사람이 먼저 출력되도록 하세요.

SQL 문
접기
  
SCOTT>SELECT deptno, ename, sal,
  2          SUM(sal) OVER() "TOTAL_SAL",
  3          ROUND((RATIO_TO_REPORT(sal) OVER())*100,2) "%"
  4  FROM emp
  5  GROUP BY deptno, ename, sal
  6  ORDER BY sal DESC;
 
접기

10) emp 테이블을 조회하여 아래와 같이 각 직원들의 급여가 해당 부서 합계금액에서 몇 %의 비중을 차지하는지를 출력하세요. 단 부서번호를 기준으로 오름차순으로 출력하세요.

SQL 문
접기
  
SCOTT>SELECT deptno, ename, sal,
  2          SUM(sal) OVER(PARTITION BY deptno) "SUM_DEPT",
  3          ROUND((RATIO_TO_REPORT(sal) OVER(PARTITION BY deptno))*100,2) "%"
  4  FROM emp
  5  ORDER BY deptno;

접기
 

11) loan 테이블을 사용하여 1000번 지점의 대출 내역을 출력하되 대출일자,대출코드,대출금액,일자별 누적대출금액을 아래와 같이 출력하세요.

SQL 문
접기
  

SCOTT>SELECT L_DATE "date", L_CODE "code", L_QTY "times", L_TOTAL "total"
  2          , SUM(L_TOTAL) OVER(partition by L_DATE) "SUM TOTAL"
  3  FROM loan
  4  WHERE L_STORE = 1000
  5  ORDER BY L_DATE;
 
접기

12) loan 테이블을 사용하여 전체 지점의 대출코드 , 대출지점 , 대출날짜 , 대출건수 , 대출금액을 대출코드와 대출지점별로 누적 합계를 구하세요.

SQL 문
접기
  
SCOTT>SELECT l_code "CODE", l_store "STORE", l_date "DATE", l_qty "TIMES", l_total "TOTAL",
  2         SUM(l_total) OVER(PARTITION BY l_code, l_store ORDER BY l_date) "SUM_TOTAL"
  3  FROM loan; 

접기

13) professor 테이블에서 각 교수들의 급여를 구하고 각 교수의 급여액이 전체 교수의 급여 합계에서 차지하는 비율을 출력하세요

SQL 문
접기
  

SCOTT>SELECT deptno, name, pay, SUM(pay) OVER() "TOTAL_PAY",
  2          ROUND((RATIO_TO_REPORT(pay) OVER())*100,2) "RATIO"
  3  FROM professor
  4  ORDER BY pay DESC;
 
접기

14) professor 테이블을 조회하여 학과번호 , 교수명 , 급여 , 학과별 급여 합계를 구하고 각 교수의 급여가 해당 학과별 급여 합계에서 차지하는 비율을 출력하세요.

SQL 문
접기
  
SCOTT>SELECT deptno, name, pay,
  2          SUM(pay) OVER(PARTITION BY deptno) "TOTAL_DEPTNO",
  3          ROUND((RATIO_TO_REPORT(pay) OVER(PARTITION BY deptno))*100,2) "RATIO(%)"
  4  FROM professor;
 
접기

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

정규식(Regular Expression) 함수

SQL 문장에서도 유닉스에서 사용하는 정규식을 사용해 다양한 검색을 할 수 있다. 정규식은 다양한 메타 문자들을 사용해 검색방법을 확장한것. 유닉스 버전마다 조금씩 다를 수 있지만 주로 사용하는 정규식 방법은 아래와 같다

[:문자클래스:] : alpha, blank, cntrl, digit, graph, lower, print, space, uppper, xdigit

연습용 테이블 확인

SCOTT>SELECT * FROM t_reg;

TEXT
----------
ABC123
ABC 123
ABC  123
abc 123
abc  123
a1b2c3
aabbcc123
?/!@#$*&
\~*().,
123123
123abc
abc

1. REGEXP_LIKE 함수

like 함수처럼 특정 패턴과 매칭되는 결과를 검색

사용예

2. REGEXP_REPLACE 함수

Replace 함수를 확장한 개념으로 주어진 문자열에서 특정 패턴을 찾아서 주어진 다른 형태로 치환하는 함수.

 문법: REGEXP_REPLACE(source_char, pattern
                    [, replace_string
                    [, position
                    [, occurrence
                    [, match_param]]]]
                    ) 

사용예

• ex1) 예제 테이블 t_reg에서 숫자부분을 '*' 기호로 전부 변경

SCOTT>SELECT text, REGEXP_REPLACE(text, '[[:digit:]]', '*') "NO -> CHAR"
  2  FROM t_reg;

TEXT       NO -> CHAR
---------- --------------------
ABC123     ABC***
ABC 123    ABC ***
ABC  123   ABC  ***
abc 123    abc ***
abc  123   abc  ***
a1b2c3     a*b*c*
aabbcc123  aabbcc***
?/!@#$*&   ?/!@#$*&
\~*().,    \~*().,
123123     ******
123abc     ***abc
abc        abc

• ex2) 특정 패턴 찾아서 변경하기

SCOTT>SELECT text,
  2          REGEXP_REPLACE(text, '([0-9])', '\1-*') "Add Char" //숫자뒤에 -* 붙이기
  3  FROM t_reg;


TEXT       Add Char
---------- ------------------------------
ABC123     ABC1-*2-*3-*
ABC 123    ABC 1-*2-*3-*
ABC  123   ABC  1-*2-*3-*
abc 123    abc 1-*2-*3-*
abc  123   abc  1-*2-*3-*
a1b2c3     a1-*b2-*c3-*
aabbcc123  aabbcc1-*2-*3-*
?/!@#$*&   ?/!@#$*&
\~*().,    \~*().,
123123     1-*2-*3-*1-*2-*3-*
123abc     1-*2-*3-*abc
abc        abc




SCOTT>SELECT text,
  2          REGEXP_REPLACE(text, '([0-9])', '-*') "Add Char"
  3  FROM t_reg;


TEXT       Add Char
---------- ------------------------------
ABC123     ABC-*-*-*
ABC 123    ABC -*-*-*
ABC  123   ABC  -*-*-*
abc 123    abc -*-*-*
abc  123   abc  -*-*-*
a1b2c3     a-*b-*c-*
aabbcc123  aabbcc-*-*-*
?/!@#$*&   ?/!@#$*&
\~*().,    \~*().,
123123     -*-*-*-*-*-*
123abc     -*-*-*abc
abc        abc

12 rows selected.



SCOTT>SELECT no, ip,
  2          REGEXP_REPLACE(ip, '\.', '') "Dot Remove"
  3  FROM t_reg2;


  NO IP                   Dot Remove
---- -------------------- --------------------
   1 10.10.0.1            101001
   2 10.10.10.1           1010101
   3 172.16.5.100         172165100
   4 172.61.186.2         172611862
   5 172.61.168.2         172611682
   6 255.255.255.0        2552552550

6 rows selected.




SCOTT>SELECT no, ip,
  2          REGEXP_REPLACE(ip, '\.', '/', 1,1) "REPLACE"
  3  FROM t_reg2;


  NO IP                   REPLACE
---- -------------------- --------------------
   1 10.10.0.1            10/10.0.1
   2 10.10.10.1           10/10.10.1
   3 172.16.5.100         172/16.5.100
   4 172.61.186.2         172/61.186.2
   5 172.61.168.2         172/61.168.2
   6 255.255.255.0        255/255.255.0

• 사용자에게 입력받은 문자중 공백이 여러개 포함돼있을 경우 그 공백을 제거시키는 방법

SCOTT>SELECT REGEXP_REPLACE('aaa     bbb', '( ){1,}','')
  2  FROM dual;


REGEXP
------
aaabbb

위의 예제에서 {1,}부분을 {1}로 해도됨. { }내의 숫자는 앞문자가 나타나는 횟수 또는 범위를 의미함.

ex) a{5} = 'a'의 5번 반복한 aaaaa 만을 의미
ex) a{3,} = aaa, aaaa, aaaaa, .....
ex) a{3,5} = aaa, aaaa, aaaaa
ex) ab{2,3} = abb, abbb

• 'abc bbb'에서 공백이 두칸이상 인 것만 공백을 제거해 출력

SCOTT>SELECT REGEXP_REPLACE('aaa     bbb', '( ){1,}','')
  2  FROM dual;

REGEXP
------
aaabbb



SCOTT>SELECT REGEXP_REPLACE('aaa bbb', '( ){2,}', '') "ONE",
  2          REGEXP_REPLACE('aaa  bbb', '( ){2,}', '') "Two"
  3  FROM dual;


ONE     Two
------- ------
aaa bbb aaabbb



SCOTT>SELECT REGEXP_REPLACE('aaa bbb', '( ){2,}', '*') "ONE",
  2          REGEXP_REPLACE('aaa  bbb', '( ){2,}', '*') "Two",
  3          REGEXP_REPLACE('aaa   bbb', '( ){2,}', '*') "Three"
  4  FROM dual;


ONE     Two     Three
------- ------- -------
aaa bbb aaa*bbb aaa*bbb

• 사용자가 입력한 단어에 공백 문자가 처음에 포함돼있고 중간에도 공백이 포함되어 있다고 가정하고 그 단어에서 모든 공백을 제거한 후 조회하는 예제.

SCOTT>SELECT studno, name, id
  2  FROM student
  3  WHERE id = REGEXP_REPLACE('&id', '( ){1,}', '');
Enter value for id:      75    true


    STUDNO NAME                           ID
---------- ------------------------------ --------------------
      9411 James Seo                      75true



SCOTT>SELECT studno, name, id
  2  FROM student
  3  WHERE id = LOWER(REGEXP_REPLACE('&id', '( ){1,}', ''));   /소문자화
Enter value for id:      75    TRUE


    STUDNO NAME                           ID
---------- ------------------------------ --------------------
      9411 James Seo                      75true

• 특정 문자열의 형태를 다른 형태로 바꿀 때

SCOTT>SELECT REGEXP_REPLACE('20160905',
  2                         '([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})',
  3                         '\1-\2-\3')
  4  FROM dual;


REGEXP_REP
----------
2016-09-05

3. REGEXP_SUBSTR 함수

SUBSTR 함수의 확장판. 특정 패턴에서 주어진 문자를 추출해 내는 함수.

사용예

• 교수테이블(professor)테이블에서 홈페이지(hpage) 주소가 있는 교수들만 조사해서 아래의 화면처럼 나오게 출력하시오.

SCOTT>SELECT name, LTRIM(REGEXP_SUBSTR(hpage, '/([[:alnum:]]+\.?){3,4}?'), '/') "URL"
  2  FROM professor
  3  WHERE hpage IS NOT NULL ;


NAME                 URL
-------------------- --------------------
Audie Murphy         www.abc.net
Angela Bassett       www.abc.net
Jessica Lange        www.power.com
Michelle Pfeiffer    num1.naver.com

hpage 컬럼을 조회하여 ‘http://’ 부분을 제거하고 . 으로 구분되는 필드를 최소 3개에서 최대 4개까지 출력하라는 의미. 그 후에 왼쪽부분에 나오는 ‘/ ’ 기호를 LTRIM 함수로 제거함.

• Professor 테이블에서 101번 학과와 201번 학과 교수들의 이름과 메일 주소의 도메인 주소를 출력하시오. 단 메일 주소는 @뒤에 있는 주소만 출력하시오.

SCOTT>SELECT name, LTRIM(REGEXP_SUBSTR(email, '@([[:alnum:]]+\.?){3,4}?'), '@') domain
  2  FROM professor
  3  WHERE deptno IN (101,201) ;


NAME                 DOMAIN
-------------------- --------------------
Audie Murphy         abc.net
Angela Bassett       abc.net
Jessica Lange        power.com
Meryl Streep         daum.net
Susan Sarandon       def.com

• 특정기호나 문자를 기준으로 데이터를 추출할 때

SCOTT>SELECT REGEXP_SUBSTR('sys/oracle@racdb:1521:racdb',
  2                        '[^:]+', 1, 3) result           // : 기호를 기준으로 3번재의 문자열을 추출
  3  FROM dual;


RESULT
----------
racdb




SCOTT>SELECT REGEXP_SUBSTR('sys/oracle@racdb:1521:racdb',
  2                        '[^/:]+', 1, 2) result          // 슬래쉬를 기준으로 출력
  3  FROM dual;


RESULT
--------------
oracle@racdb

4. REGEXP_COUNT 함수

특정 문자의 개수를 세는 함수

사용예

• 주어진 문자열에서 대문자 'A'가 몇개인지 찾아 주는 예

SCOTT>SELECT text, REGEXP_COUNT(text, 'A')
  2  FROM t_reg;


TEXT       REGEXP_COUNT(TEXT,'A')
---------- ----------------------
ABC123                          1
ABC 123                         1
ABC  123                        1
abc 123                         0
abc  123                        0
a1b2c3                          0
aabbcc123                       0

• 검색 위치를 3으로 지정해서 3번째 문자 이후부터 해당 소문자 ‘c’ 가 나오는 개수를 세는 예

SCOTT>SELECT text, REGEXP_COUNT(text, 'c', 3)
  2  FROM t_reg;


TEXT       REGEXP_COUNT(TEXT,'C',3)
---------- ------------------------
ABC123                            0
ABC 123                           0
ABC  123                          0
abc 123                           1
abc  123                          1
a1b2c3                            1
aabbcc123                         2
?/!@#$*&                          0
\~*().,                           0
123123                            0
123abc                            1
abc                               1



SCOTT>SELECT text, REGEXP_COUNT(text, 'c') "RESULT 1",
  2                REGEXP_COUNT(text, 'c', 1, 'i') "RESULT 2"   //대소문자 구분 없이 (즉 'C' 와 'c' 모두) 몇 개가 나오는 지 세어 출력
  3  FROM t_reg;


TEXT         RESULT 1   RESULT 2
---------- ---------- ----------
ABC123              0          1
ABC 123             0          1
ABC  123            0          1
abc 123             1          1
abc  123            1          1
a1b2c3              1          1
aabbcc123           2          2
?/!@#$*&            0          0
\~*().,             0          0
123123              0          0
123abc              1          1
abc                 1          1



SCOTT>SELECT text,
  2          REGEXP_COUNT(text, 'aa') RESULT1,
  3          REGEXP_COUNT(text, 'a{2}') RESULT2,
  4          REGEXP_COUNT(text, '(a)(a)') RESULT3
  5  FROM t_reg;


TEXT          RESULT1    RESULT2    RESULT3
---------- ---------- ---------- ----------
ABC123              0          0          0
ABC 123             0          0          0
ABC  123            0          0          0
abc 123             0          0          0
abc  123            0          0          0
a1b2c3              0          0          0
aabbcc123           1          1          1
?/!@#$*&            0          0          0
\~*().,             0          0          0
123123              0          0          0
123abc              0          0          0
abc                 0          0          0

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

일반함수

함수의 입력되는 값이 숫자, 문자, 날짜 구분없이 모두 사용할 수 있는 함수. 일반적으로 많이 사용하는 함수 위주.

 CASE(조건) WHEN 결과1 THEN 출력1
           [WHEN 결과2 THEN 출력2] 
            ELSE 출력3
 END "컬럼명" 

QUIZ1

Professor 테이블에서 201번 학과 교수들의 이름과 급여, bonus , 총 연봉을 아래와 같이 출력하시오. 단 총 연봉은 (pay*12+bonus) 로 계산하고 bonus 가 없는 교수는 0으로 계산.

SCOTT>SELECT profno, name, pay, bonus, TO_CHAR(pay*12+NVL(bonus,0),'99,999') "TOTAL"
  2  FROM professor
  3  WHERE deptno = 201;

QUIZ2

아래 화면과 같이 emp 테이블에서 deptno 가 30 번인 사원들을 조회하여 comm 값이 있을 경우 'Exist' 을 출력하고 comm 값이 null 일 경우 'NULL' 을 출력하시오.

SCOTT>SELECT empno, ename, comm, NVL2(comm,'Exist','NULL') "NVL2"
  2  FROM emp
  3  WHERE deptno = 30;

QUIZ3

Student 테이블을 사용하여 제 1 전공 (deptno1) 이 101 번인 학과 학생들의 이름과 주민번호, 성별을 출력하되 성별은 주민번호(jumin) 컬럼을 이용하여 7번째 숫자가 1일 경우 “ 남자” , 2일 경우 “여자 ” 로 출력하시오.

SCOTT>SELECT name, jumin, DECODE(SUBSTR(jumin,7,1),1,'MAN',2,'WOMAN') "Gender"
  2  FROM student
  3  WHERE deptno1 = 101;

QUIZ4

Student 테이블에서 1 전공이 (deptno1) 101번인 학생의 이름과 연락처와 지역을 출력하시오. 단,지역번호가 02 는 "SEOUL" , 031 은 "GYEONGGI" , 051 은 "BUSAN" , 052 는 "ULSAN" , 055 는 "GYEONGNAM".

  SCOTT>SELECT name, tel, DECODE(SUBSTR(tel,1,INSTR(tel,')')-1), '02', 'SEOUL',
  2                                                            '031', 'GYEONGGI',
  3                                                            '051', 'BUSAN',
  4                                                            '052', 'ULSAN',
  5                                                            '055', 'GYEONGNAM') "LOC"
  6  FROM student
  7  WHERE deptno1 = 101

QUIZ5

emp 테이블을 조회하여 empno , ename , sal , LEVEL(급여등급)을 아래와 같이 출력하시오. 단 급여등급은 sal 을 기준으로 1 - 1000 이면 Level 1 , 1001 - 2000 이면 Level 2 , 2001 - 3000 이면 Level 3 , 3001 - 4000 이면 Level 4 , 4001 보다 많으면 Level 5 로 출력

SCOTT>SELECT empno, ename, sal,
  2          CASE WHEN sal BETWEEN 1 AND 1000 THEN 'LEVEL1'
  3               WHEN sal BETWEEN 1001 AND 2000 THEN 'LEVEL2'
  4               WHEN sal BETWEEN 2001 AND 3000 THEN 'LEVEL3'
  5               WHEN sal BETWEEN 3001 AND 4000 THEN 'LEVEL4'
  6                                              ELSE 'LEVEL5'
  7          END "LEVERL"
  8  FROM emp
  9  ORDER BY sal DESC;

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

숫자 함수

형변환 함수

QUIZ1

Student 테이블의 birthday 컬럼을 사용하여 생일이 1월인 학생의 이름과 birthday 를 아래 화면과 같이 출력하시오.

SCOTT>SELECT studno, name, birthday
  2  FROM student
  3  WHERE TO_CHAR(birthday,'MM') = 01;

SCOTT>SELECT studno, name, birthday
  2  FROM student
  3  WHERE SUBSTR(birthday,4,3) = 'JAN';

QUIZ2

emp 테이블의 hiredate 컬럼을 사용하여 입사일이 1,2,3 월인 사람들의 사번과 이름, 입사일을 출력하시오.

SCOTT>SELECT empno, ename, hiredate
  2  FROM emp
  3  WHERE TO_CHAR(hiredate, 'MM') < 04;

QUIZ3

emp 테이블을 조회하여 comm 값을 가지고 있는 사람들의 empno , ename , hiredate , 총연봉,15% 인상 후 연봉을 아래 화면처럼 출력하세요. 단 총연봉은 (sal*12)+comm 으로 계산하고 아래 화면에서는 SAL 로 출력되었으며 15% 인상한 값은 총연봉의 15% 인상 값임. (HIREDATE 컬럼의 날짜 형식과 SAL 컬럼 , 15% UP 컬럼의 $ 표시와 , 기호 나오게)

SCOTT>SELECT empno, ename, TO_CHAR(hiredate,'YYYY-MM-DD') "HIREDATE",
  2                        TO_CHAR((sal*12)+comm,'$99,999') "SAL",
  3                        TO_CHAR((((sal*12)+comm)*1.15),'$99,999') "15% UP"
  4  FROM emp
  5  WHERE comm IS NOT NULL;

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저