[Day10] Oracle 10 - MODIFY, DROP COLUMN, Table copy, sub query(insert, update, delete), MERGE, Constraint

[Day10] Oracle 10 [10/11]

--1. 오라클 각 DataType 에 대해 상세히 설명하세요

    ㄱ. 문자,문자열          ''
        ㄴ 고정길이  char,[n]char  2000 byte
        ㄴ 가변길이  varchar2(== varchar 시노님), nvarchar2     4000byte
                    Long 2GB

    ㄴ. LOB
        ㄴ CLOB
        ㄴ BLOB
        ㄴ NCLOB

    ㄷ. 숫자
        ㄴ 정수   NUMBER( p, 0) == NUMBER( p )
        ㄴ 실수   NUMBER( p, s )

                    p 38,    s 127
                 NUMBER   == NUMBER( 38, 127 )
                 FLOAT = 실수 NUMBER
    ㄹ.  날짜
       ㄴ Date 날짜 + 시/분/초
       ㄴ TIMESTAMP(n=6)  ms, ns

       등등

--2.  emp 테이블에서 [년도별] [월별] 입사사원수 출력.( PIVOT() 함수 사용 )
--
--    [실행결과]
--    1982   1   0   0   0   0   0   0   0   0   0   0   0
--    1980   0   0   0   0   0   0   0   0   0   0   0   1
--    1981   0   2   0   1   1   1   0   0   2   0   1   2

ㄱ. 피봇대상

SELECT EXTRACT( YEAR FROM hiredate) hiredate_year
, EXTRACT( MONTH FROM hiredate) hiredate_month
FROM emp;

ㄴ.

SELECT *
FROM ( 
        SELECT EXTRACT( YEAR FROM hiredate) hiredate_year
        , EXTRACT( MONTH FROM hiredate) hiredate_month
        FROM emp
)
PIVOT( COUNT(*) FOR hiredate_month IN ( 1,2,3,4,5,6,7,8,9,10,11,12 ) );

--2-2.   emp 테이블에서 각 JOB별 입사년도별 1월~ 12월 입사인원수 출력.  ( PIVOT() 함수 사용 )
--    [실행결과]
--    ANALYST      1981   0   0   0   0   0   0   0   0   0   0   0   1
--    CLERK      1980   0   0   0   0   0   0   0   0   0   0   0   1
--    CLERK      1981   0   0   0   0   0   0   0   0   0   0   0   1
--    CLERK      1982   1   0   0   0   0   0   0   0   0   0   0   0
--    MANAGER      1981   0   0   0   1   1   1   0   0   0   0   0   0
--    PRESIDENT   1981   0   0   0   0   0   0   0   0   0   0   1   0
--    SALESMAN   1981   0   2   0   0   0   0   0   0
--

SELECT *
FROM ( 
        SELECT job
        , EXTRACT( YEAR FROM hiredate) hiredate_year
        , EXTRACT( MONTH FROM hiredate) hiredate_month
        FROM emp
)
PIVOT( COUNT(*) FOR hiredate_month IN ( 1,2,3,4,5,6,7,8,9,10,11,12 ) )
ORDER BY job ASC;

--3. emp테이블에서 입사일자가 오래된 순으로 3명 출력 ( TOP 3 )
--    [실행결과]
--    1   7369   SMITH   CLERK       7902   80/12/17   800          20
--    2   7499   ALLEN   SALESMAN   7698   81/02/20   1600   300   30
--    3   7521   WARD   SALESMAN   7698   81/02/22   1250   500   30

ㄱ. TOP-N 방식
    1) 입사일자 기준으로 정렬하는 서브쿼리
    2) ROWNUM 의사컬럼

SELECT ROWNUM  seq, t.*   
FROM (    
    SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
    FROM emp
    ORDER BY hiredate ASC
) t
WHERE ROWNUM <=3;

ㄴ. RANK 함수

WITH 
temp AS (
SELECT emp.*
       , RANK() OVER( ORDER BY hiredate ASC ) seq
FROM emp
)
SELECT *
FROM temp
WHERE seq <= 3;

--4. SMS 인증번호 임의의 6자리 숫자 출력 ( dbms_random 패키지 사용 )

SELECT dbms_random.value
     , dbms_random.value(100000, 1000000)
FROM dual;

--4-2. 임의의 대소문자 5글자 출력( dbms_random 패키지 사용 )

SELECT dbms_random.string('A',5)
FROM dual;

--5. 게시글을 저장하는 테이블 생성
--   ㄱ.   테이블명 : tbl_test
--   ㄴ.   컬럼
--         글번호    seq            자료형  크기    널허용여부    고유키
--         작성자    writer
--         비밀번호 passwd
--         글제목    title
--         글내용    content
--         작성일    regdate
--    ㄷ.  글번호, 작성자, 비밀번호, 글 제목은 필수 입력 사항으로 지정
--    ㄹ.  글번호가  기본키( PK )로 지정 : PK_TABLE_COLUMN
--    ㅁ.  작성일은 현재 시스템의 날짜로 자동 설정

CREATE TABLE tbl_test(
   seq        NUMBER(38)    NOT NULL CONSTRAINT PK_TBLTEST_SEQ PRIMARY KEY  -- SYS_????
   , writer   NVARCHAR2(10) NOT NULL
   , passwd   VARCHAR2(15)  NOT NULL
   , title    NVARCHAR2(30)   NOT NULL
   , content  NCLOB
   , regdate  DATE       DEFAULT SYSDATE
);

-- Table TBL_TEST이(가) 생성되었습니다.

--5-2. 조회수 read 컬럼을 추가 ( 기본값 0 으로 설정 )

ALTER TABLE TBL_TEST
ADD read NUMBER DEFAULT 0;  -- 한개의 컬럼 추가할 때는 () 괄호 생략 가능
ADD ();
-- Table TBL_TEST이(가) 변경되었습니다.

--5-3. 테이블 구조 확인

DESC  TBL_TEST;

--5-4. CRUD ( insert, select, update, delete )

-- ㄱ. 임의의 게시글 5개를 추가 insert

INSERT INTO TBL_TEST (SEQ, WRITER,PASSWD,TITLE, CONTENT ) VALUES (1, '관리자', '1234$', 'test-1', 'test-1');
-- 1 행 이(가) 삽입되었습니다.
-- 00947. 00000 -  "not enough values"
INSERT INTO TBL_TEST  VALUES (2, '홍길동', '1234$', 'test-2', 'test-2', SYSDATE, 0);
--1 행 이(가) 삽입되었습니다.
-- SQL 오류: ORA-00913: too many values
INSERT INTO TBL_TEST (SEQ, WRITER,PASSWD,TITLE ) VALUES (3, '김기수', '1234$', 'test-3' );

COMMIT;
-- 커밋 완료.

-- ㄴ. 게시글 조회 select

SELECT *
FROM tbl_test;
--   ㄷ. 3번 게시글의 글 제목, 내용 수정 update
-- ORA-01722: invalid number
UPDATE tbl_test
SET title = '[e]' || title , content = 'edit content'
wHERE seq = 3;

-- ㄹ. 4번 게시글 삭제 delete

DELETE FROM tbl_Test
WHERE seq = 3;

COMMIT;

--5-5. tbl_board 테이블 삭제

DROP TABLE tbl_test PURGE;

--Table TBL_TEST이(가) 삭제되었습니다.

--6-1. 오늘의 날짜와 요일 출력
-- [실행결과]
--오늘날짜 숫자요일 한자리요일 요일
---------- --- ------ ------------
--22/04/15 6 금 금요일

SELECT SYSDATE
     , TO_CHAR( SYSDATE, 'D' )
     , TO_CHAR( SYSDATE, 'DY' )
     , TO_CHAR( SYSDATE, 'DAY' ) 
     -- 일 DD
FROM dual;

--6-2. 이번 달의 마지막 날과 날짜만 출력
-- [실행결과]
--오늘날짜 이번달마지막날짜 마지막날짜(일)
---------- -------- --------------------
--22/04/15 22/04/30 30 30

SELECT SYSDATE
      , LAST_DAY( SYSDATE )
      , TO_CHAR( LAST_DAY( SYSDATE )  , 'DD' )
FROM dual;

--6-3.
-- [실행결과]
--오늘날짜 월의주차 년의주차 년의 주차
---------- ---- ---- ----
--22/04/15 3 15 15

-- IW, WW 차이점 ~ ( 기억 )

SELECT SYSDATE
    , TO_CHAR( SYSDATE, 'W') 
    , TO_CHAR( SYSDATE, 'IW') 
    , TO_CHAR( SYSDATE, 'WW') 
FROM dual;

DESC tbl_member;

이름 널?                    유형
-----       --------          --------------
ID         NOT NULL          NVARCHAR2(10)
NAME NOT NULL           NVARCHAR2(10)
AGE                     NUMBER(3)
BIRTH                      DATE
TEL NOT NULL          CHAR(13)
ETC                       NVARCHAR2(100)

1)  컬럼 추가 : tel, etc
2)   etc 컬럼의 자료형의 크기  NVARCHAR2(100) -> 200 수정

ALTER TABLE MODIFY

【형식】
        ALTER TABLE 테이블명
        MODIFY (컬럼명 datatype [DEFAULT 값]
               [,컬럼명 datatype]...);

• 데이터의 type, ***[size]***, default 값을 변경할 수 있다.
• 변경 대상 컬럼에 데이터가 없거나 null 값만 존재할 경우에는 [ size를 줄일 수 있다.]
• 데이터 타입의 변경은 CHAR와 VARCHAR2 상호간의 변경만 가능하다.
• *** 컬럼 크기의 변경은 저장된 데이터의 크기보다 같거나 클 경우에만 가능하다. **
• NOT NULL 컬럼인 경우에는 size의 확대만 가능하다.
• 컬럼의 기본값 변경은 그 이후에 삽입(INSERT)되는 행부터 영향을 준다.
• 컬럼이름의 [직접적인 변경]은 불가능하다.
• 컬럼이름의 변경은 서브쿼리를 통한 테이블 생성시 alias를 이용하여 변경이 가능하다.
• alter table ... modify를 이용하여 제약조건(constraint)를 수정할 수 없다

ALTER TABLE tbl_member
MODIFY (  etc NVARCHAR2(200)   );  -- 자료형, 크기 확대, 축소(제약)
-- Table TBL_MEMBER이(가) 변경되었습니다.

3) etc 컬럼명을 bigo 컬럼명으로 수정.
ㄱ. 별칭( alias ) 사용

  SELECT etc AS "bigo"
  FROM tbl_member;

ㄴ RENAME 컬럼명을 수정.

   ALTER TABLE tbl_member
   RENAME COLUMN etc TO bigo;
-- Table TBL_MEMBER이(가) 변경되었습니다.

4) bigo 컬럼을 삭제.

ALTER TABLE DROP COLUMN

【형식】
ALTER TABLE 테이블명
DROP COLUMN 컬럼명;

• 컬럼을 삭제하면 해당 컬럼에 저장된 데이터도 함께 삭제된다.
• 한번에 하나의 컬럼만 삭제할 수 있다.
• 삭제 후 테이블에는 적어도 하나의 컬럼은 존재해야 한다.
• DDL문으로 삭제된 컬럼은 복구할 수 없다.

ALTER TABLE tbl_member
DROP COLUMN bigo;

-- Table TBL_MEMBER이(가) 변경되었습니다.

5) tbl_member 테이블의 이름을 tbl_customer 변경(수정)

RENAME tbl_member TO tbl_customer;
-- 테이블 이름이 변경되었습니다.

-- 테이블 생성 (서브쿼리)
1) 테이블을 만드는 가장 단순하면서도 일반적인 명령 형식으로 만드는 방법.
2) SUBQUERY를 이용한 테이블을 생성 방법.
   ㄱ. 이미 존재하는 테이블이 있고,
   ㄴ. SELECT ~ 서브쿼리를 이용해서
   ㄷ. 새로운 테이블 생성 +  데이터 자동 추가.
   ㄹ. 형식)
   CREATE TABLE 테이블명 [ 컬럼명, 컬럼명... ]
   AS 서브쿼리;

   ㅁ) 다른 테이블에 존재하는 특정 컬럼과 행을 이용한 테이블을 생성하고 싶을 때 사용.
   ㅂ) 서브쿼리의 결과값으로 테이블이 생성된다.
   ㅅ)CREATE TABLE 테이블명 [ 컬럼명, 컬럼명... ]
       컬럼명을 명시할 경우에는 서브쿼리의 컬럼수와 같게 명시해야 된다.
                  하지 않을 경우에는 서브쿼리의 컬럼명과 같게 된다.
   ㅇ) 제약조건은 복사되지 않는다.  ( PK X, FK X,       NN 제약조건은 복사가 된다. )

SELECT *
   FROM user_constraints
   WHERE table_name = UPPER(  'tbl_emp10'  );   
   WHERE table_name = UPPER(  'emp'  );  -- PK, FK
   
   user_ 접두어, dba_ , all_
   FROM user_tables;  -- tabs
   FROM user_users;

  -- 예) emp 테이블의 사원번호,사원명, 입사일자, pay 컬럼만 가지는 테이블 생성
        +  10번 부서원만 데이터로 추가

   --CREATE TABLE tbl_emp10  -- [ 컬럼명, 컬럼명... ]    생략
   CREATE TABLE tbl_emp10  ( eno, name, hdate , pay  )
   AS (
      SELECT empno, ename, hiredate, sal + NVL( comm, 0 )  pay
      FROM emp
      WHERE deptno = 10
   );
-- Table TBL_EMP10이(가) 생성되었습니다.

이름 널? 유형
-------- -- ------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
HIREDATE DATE
PAY NUMBER     -- sal + comm

ㅈ) 원래 테이블 ( CRUD X )  보존한채로 복사 테이블

CREATE TABLE tbl_empCopy 
     AS (  SELeCT * FROM emp );
--     Table TBL_EMPCOPY이(가) 생성되었습니다.
SELECT *
FROM tbl_empcopy; -- delete, update, insert 등등

테이블 삭제

     DROP TABLE tbl_emp10 PURGE;
     DROP TABLE tbl_empcopy PURGE;
   
-- Table TBL_EMP10이(가) 삭제되었습니다.
-- Table TBL_EMPCOPY이(가) 삭제되었습니다.

   ㅊ) (문제) 기존 테이블의 구조는 복사해서 새로운 테이블을 생성
           + 데이터 복사 X

  CREATE TABLE tbl_empcopy
     AS (
            SELECT *
            FROM emp
            WHERE 1 = 0       -- 이 조건을 주면 구조만 복사되고 데이터는 복사안됨!
     );

DML - INSERT , UPDATE, DELETE ( COMMIT, ROLLBACK ) -- TRUNCATE
1. tbl_member 테이블 확인

SELECT *
FROM tabs
WHERE REGEXP_LIKE( table_name ,  'member', 'i' );

2. tbl_member 테이블 확인 -> 삭제

DROP TABLE  tbl_member PURGE;

3. tbl_member 테이블 생성.
         멤버(회원정보)
        추출 속성(  나이, 생일, 성별 컬럼 ) X -> 왜 ? 주민등록번호

NOT NULL      NN
PRIMARY KEY   PK

CREATE TABLE tbl_member(
   id              VARCHAR2(10)  NOT NULL  CONSTRAINT PK_TBLMEMBER_ID   PRIMARY KEY -- PK
   , name          VARCHAR2(20)  NOT NULL
   , age           NUMBER(3)
   , birth         DATE
   , regdate       DATE   DEFAULT SYSDATE
   , point        NUMBER  DEFAULT 100
);

-- Table TBL_MEMBER이(가) 생성되었습니다.

-- TBL_MEMBER 테이블의 제약조건 확인 ( 암기 )

SELECT *
FROM user_constraints
WHERE table_name = 'TBL_MEMBER';

PK : PK_TBLMEMBER_ID  / P
NN :  SYS_C009024     / C
NN :  SYS_C009025     / C
    제약조건명을 지정하지 않으면  SYS_  접두사로 자동 지정된다.
-- FK                  / R

INSERT INTO tbl_member ( id, name, age, birth, regdate, point )
           VALUES      ( 'admin', '관리자', 40,  TO_DATE('01/23/1980', 'MM/DD/YYYY'), SYSDATE, 1000);

ORA-00001: unique constraint (SCOTT.PK_TBLMEMBER_ID) violated
유일성 제약조건
PK (= UK +NN ) 제약조건 위배된다. - ID는 중복 X ( 고유한 키 )

INSERT INTO tbl_member ( id, name, age, birth, regdate, point )
           VALUES      ( 'admin', '홍길동', 20,  TO_DATE('11/20/2003', 'MM/DD/YYYY'), SYSDATE, 1000);


INSERT INTO tbl_member ( id, name, age, birth )
           VALUES      ( 'hong', '홍길동', 20,  TO_DATE('11/20/2003', 'MM/DD/YYYY') );

--  ORA-00933: SQL command not properly ended
INSERT INTO tbl_member ( id, name, age )
           VALUES      ( 'park', '박지순', 25 );
--         1 행 이(가) 삽입되었습니다.

INSERT INTO tbl_member ( id, name  )
           VALUES      ( 'kim', '김지순' );
-- 00947. 00000 -  "not enough values"
INSERT INTO tbl_member         VALUES      ( 'lee', '이지순', null, null , SYSDATE, 100 );           
-- 1 행 이(가) 삽입되었습니다.

서브쿼리를 사용하는 INSERT 문

  INSERT INTO 테이블명  [ ( 컬럼명.... )  ] VALUES      ( 컬럼값...);
  INSERT INTO 테이블명 ( 서브쿼리 );

  1) tbl_emp10 테이블 -> 생성

  CREATE TABLE tbl_emp10
  AS ( 
         SELECT *
         FROM emp
         WHERE 1 = 0
       )

  SELECT * 
  FROM tbl_emp10;

3) 3개 행 이(가) 삽입되었습니다.

  INSERT INTO tbl_emp10   ( 
                                 SELECT *
                                 FROM emp
                                 WHERE deptno = 10
                           );
  COMMIT;
  
  DROP TABLE tbl_emp10 PURGE;

-- [ Multitable insert 문  ] --
-- 하나의 INSERT 문으로 여러 개의 테이블에 동시에 행을 입력하는 문

--  4가지
1) unconditional insert all
    조건이 없는
2) conditional insert all
    조건이 있는
3) conditional first insert
4) pivoting insert



  [ 1) unconditional insert all ]
  조건과 상관없이 기술되어진 여러 개의 테이블에 데이터를 입력한다.

• 서브쿼리로부터 한번에 하나의 행을 반환받아 각각 insert 절을 수행한다.
• into 절과 values 절에 기술한 컬럼의 개수와 데이터 타입은 동일해야 한다.

【형식】
   INSERT ALL | FIRST
     [INTO 테이블1 VALUES (컬럼1,컬럼2,...)]
     [INTO 테이블2 VALUES (컬럼1,컬럼2,...)]
     .......
   Subquery;

여기서
ALL : 서브쿼리의 결과 집합을 해당하는 insert 절에 모두 입력
FIRST : 서브쿼리의 결과 집합을 해당하는 첫 번째 insert 절에 입력
subquery : 입력 데이터 집합을 정의하기 위한 서브쿼리는 한 번에 하나의 행을 반환하여 각 insert 절 수행

  예) emp 테이블에 12명 사원 존재
  SELECT * FROM emp;

  tbl_emp10 테이블생성
  tbl_emp20 테이블생성
  tbl_emp30 테이블생성
  tbl_emp40 테이블생성

  CREATE TABLE tbl_emp10
  AS 
     (
         SELECT *
         FROM emp
         WHERE deptno = 10
     )
  
    CREATE TABLE tbl_emp20
  AS 
     (
         SELECT *
         FROM emp
         WHERE deptno = 20
     )
     
       CREATE TABLE tbl_emp30
  AS 
     (
         SELECT *
         FROM emp
         WHERE deptno = 30
     )
     
       CREATE TABLE tbl_emp40
  AS 
     (
         SELECT *
         FROM emp
         WHERE deptno = 40
     )

-- 테이블 구조 복사 + 데이터 복사

전부다 들어간 형태임

  CREATE TABLE tbl_emp10  AS      (         SELECT *         FROM emp         WHERE 1 = 0      );
  CREATE TABLE tbl_emp20  AS      (         SELECT *         FROM emp         WHERE 1 = 0      );
  CREATE TABLE tbl_emp30  AS      (         SELECT *         FROM emp         WHERE 1 = 0      );
  CREATE TABLE tbl_emp40  AS      (         SELECT *         FROM emp         WHERE 1 = 0      );

 INSERT ALL
        INTO tbl_emp10 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
        INTO tbl_emp20 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
        INTO tbl_emp30 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
        INTO tbl_emp40 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
   SELECT * FROM emp;

   -- 48개 행 이(가) 삽입되었습니다.


    [ 2) conditional insert all ]
    【형식】
   INSERT ALL
   WHEN 조건절1 THEN
     INTO [테이블1] VALUES (컬럼1,컬럼2,...)
   WHEN 조건절2 THEN
     INTO [테이블2] VALUES (컬럼1,컬럼2,...)
   ........
   ELSE
     INTO [테이블3] VALUES (컬럼1,컬럼2,...)
   Subquery;
• subquery로부터 한번에 하나씩 행을 리턴받아 WHEN...THEN절에서 체크한 후, 조건에 맞는 절에 기술된 테이블에 insert 절을 수행한다.
• VALUES 절에 지정한 DEFAULT 값을 사용할 수 있다. 만약 default값이 지정되어 있지 않다면, MULL 값이 삽입된다.

  INSERT ALL
      WHEN deptno = 10 THEN
        INTO tbl_emp10 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
      WHEN deptno = 20 THEN  
        INTO tbl_emp20 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
      WHEN deptno = 30 THEN   
        INTO tbl_emp30 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
      ELSE  
        INTO tbl_emp40 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
   SELECT * FROM emp;

   -- 12개 행 이(가) 삽입되었습니다.


3) conditional first insert 문
    WHEN ~ THEN 조건 있는

Conditional first insert 문은 conditional insert all 문과 동일하게 특정 조건들을 기술하여 그 조건에 맞는 행(row)들을 원하는 테이블에 나누어 삽입하고자 할 때 사용하지만, conditional insert all 문과 달리 첫 번째 when 절에서 조건을 만족할 경우 다음의 when 절은 수행하지 않는다.
서브쿼리로부터 한번에 하나의 행을 반환받아 when ... then 절에서 조건을 체크한 후 조건에 맞는 절에 기술된 테이블에 insert 절을 수행한다.

여러 개의 when ... then 절을 사용하여 여러 조건을 사용할 수 있다. 단, 첫 번째 when 절에서 조건을 만족하면 into 절을 수행한 후 다음의 when 절은 수행하지 않는다.

【형식】
INSERT FIRST
        WHEN 조건절1 THEN
          INTO [테이블1] VALUES (컬럼1,컬럼2,...)
        WHEN 조건절2 THEN
          INTO [테이블2] VALUES (컬럼1,컬럼2,...)
        ........
        ELSE
          INTO [테이블3] VALUES (컬럼1,컬럼2,...)
Sub-Query;

• conditional INSERT FIRST는 조건절을 기술하여 조건에 맞는 값들을 원하는 테이블에 삽입할 수 있다.
• 여러 개의 WHEN...THEN절을 사용하여 여러 조건 사용이 가능하다.
단, 첫 번째 WHEN 절에서 조건을 만족한다면, INTO 절을 수행한 후 다음의 WHEN 절들은 더 이상 수행하지 않는다.
• subquery로부터 한 번에 하나씩 행을 리턴 받아 when...then절에서 조건을 체크한 후 조건에 맞는 절에 기술된 테이블에 insert를 수행한다.
• 조건을 기술한 when 절들을 만족하는 행이 없을 경우 else절을 사용하여 into 절을 수행할 수 있다. else절이 없을 경우 리턴 된 그행에 대해서는 아무런 작업도 발생하지 않는다.

7934   MILLER   CLERK   7782   82/01/23   1300      10
10번 부서원이면서 잡이   CLERK 인 밀러  사원

INSERT FIRST
      WHEN deptno = 10 THEN
        INTO tbl_emp10 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
      WHEN job = 'CLERK' THEN  
        INTO tbl_emp20 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
      ELSE  
        INTO tbl_emp40 VALUES (  empno, ename, job, mgr, hiredate, sal, comm, deptno  )
   SELECT * FROM emp;

4) Pivoting insert 문
pivoting insert 문은 unconditional insert all 문과 거의 동일하나 into 절에 단 한 개의 테이블만 올 수 있다.
대부분의 관계형 데이터베이스에서 테이블을 생성할 때 유사한 정보가 저장되는 컬럼은 하나의 공통 컬럼으로 설계하여 사용하는 것이 보통이다.
하지만, 비관계형 데이터베이스에서는 이러한 방법으로 설계하지 않고 각각 필요한 컬럼을 따로 설계하여 데이터를 저장한다.
pivoting insert 문은 이와 같이 비관계형 데이터베이스 구조의 컬럼을 읽어서 관계형 데이터베이스 구조의 테이블에 데이터를 일괄 입력시킬 때 사용한다.
여러 개의 into 절을 사용할 수 있지만, into 절 뒤에 오는 테이블은 모두 동일해야 한다.

【형식】
INSERT ALL
  WHEN 조건절1 THEN
  INTO [동일한 테이블1] VALUES (컬럼1,컬럼2,...)
  INTO [동일한 테이블1] VALUES (컬럼1,컬럼2,...)
  ..........
Sub-Query;

• 주로 여러 곳의 시스템으로부터 데이터를 받아 작업하는 dataware house에 적합하다. 정규화 되지 않은 data source들이나 다른 format으로 저장된 data source들을 Oracle의 관계형 DB에서 사용하기에 적합한 형태로 변환한다.
• 정규화 되지 않은 데이터를 oracle이 제공하는 relational한 형태로 테이블을 변경하는 작업을 pivoting이라고 한다.

create table tbl_sales(
    employee_id        number(6)   ,
    week_id            number(2)   ,
    sales_mon          number(8, 2), -- 월 판매량
    sales_tue          number(8, 2) ,
    sales_wed          number(8, 2) ,
    sales_thu          number(8, 2) ,
    sales_fri          number(8, 2)   -- 금 판매량
); 
-- Table TBL_SALES이(가) 생성되었습니다.

 insert into tbl_sales values(1101,4,100,150,80,60,120);
 insert into tbl_sales values(1102,5,300,300,230,120,150);
 
 COMMIT;

 create table tbl_sales_data(
   employee_id        number(6),
   week_id            number(2),
   sales              number(8,2)
);

-- [ PIVOTING INSERT. 문 ] == UNC~ INSERT ALL

  insert all
  
    into tbl_sales_data values(employee_id, week_id, sales_mon)
    into tbl_sales_data values(employee_id, week_id, sales_tue)
    into tbl_sales_data values(employee_id, week_id, sales_wed)
    into tbl_sales_data values(employee_id, week_id, sales_thu)
    into tbl_sales_data values(employee_id, week_id, sales_fri)       
    
    select employee_id, week_id, sales_mon, sales_tue, sales_wed,
           sales_thu, sales_fri
    from tbl_sales;

ex)
a) 1101번 사원이 4째주 월요일 100개 화요일 120개 수요일 320개 목요일 500개 ~
를 테이블에
b)
1101 4째주 월요일 100개
1101 4째주 화요일 120개
.
.
.
구현하기 위해서 즉
a 데이터를 b에 집어넣기 위해서 Pivot insert 사용

-- UPDATE / DELETE
-- 이미 존재하는 테이블을 사용해서 새로운 테이블 생성
--( 문제1 ) insa테이블에서 num, name 컬럼만 복사해서 tbl_score 테이블 생성
-- 조건) num <=1005 레코드 복사

   CREATE TABLE tbl_score
   AS (
               SELECT num, name
               FROM insa
               WHERE num <= 1005
   );

--( 문제2 ) tbl_score 테이블에 kor, eng, mat, tot, avg, grade, rank 컬럼 추가

ALTER TABLE tbl_score
ADD (
         kor   NUMBER(3) DEFAULT 0
         , eng NUMBER(3) DEFAULT 0
         , mat NUMBER(3) DEFAULT 0
         , tot NUMBER(3)
         , avg NUMBER(5,2)
         , grade CHAR(1 CHAR)
         , rank NUMBER(3)
     );

-- ( 문제3 ) kor, eng, mat 점수를 임의의 정수( 0~100)로 수정.

   UPDATE tbl_score
   SET  kor = TRUNC(  dbms_random.value( 0, 101 )  )
        , eng = TRUNC(  dbms_random.value( 0, 101 )  )
        , mat = FLOOR(  dbms_random.value( 0, 101 )  );
   --WHERE ;
  COMMIT;

-- ( 문제4 ) 모든 학생들의 tot, avg 계산 , 수정( UPDATE )

-- 100.81733122398513865487065964899694120306 -> tbl_socre - kor, eng, mat

UPDATE tbl_score
SET     tot = kor + eng +mat
        , avg = (kor + eng +mat)/3;       87.12[3 반올립]131312313123132  number(5,2)
-- WHERE;

-- ( 문제5 ) 평균 무조건 소수점 2자리 까지 출력 22.70

SELECT num, name, kor, eng, mat, tot
      , TO_CHAR( avg , '999.00' ) avg
      , grade, rank
FROm tbl_score;

-- ( 문제6 ) grade 등급 컬럼 수정
-- 평균이 90 이상 A, 80 이상 B, 70 이상 C , 60 이상 D, 그외 F char(1 char)

UPDATE tbl_score
SET  grade = 'A'
WHERE avg BETWEEN 90 AND 100;

UPDATE tbl_score
SET  grade = 'B'
WHERE avg BETWEEN 80 AND 89;

UPDATE tbl_score
SET  grade = 'C'
WHERE avg BETWEEN 70 AND 79;

UPDATE tbl_score
SET  grade = 'D'
WHERE avg BETWEEN 60 AND 69;

UPDATE tbl_score
SET  grade = 'F'
WHERE avg BETWEEN 0 AND 59;

-- CASE 함수

UPDATE tbl_score
SET  grade =  CASE
                  WHEN avg >= 90 THEN 'A'
                  WHEN avg >= 80 THEN 'B'
                  WHEN avg >= 70 THEN 'C'
                  WHEN avg >= 60 THEN 'D'
                  ELSE 'F'
              END;
-- DECODE 함수 :    = 비교연산자만 사용.
UPDATE tbl_score
SET  grade =  DECODE(  TRUNC( avg/10 ) , 10, 'A', 9 , 'A', 8, 'B', 7, 'C', 6 , 'D', 'F') ;

-- ( 문제 6 ) rank 등수 처리....

UPDATE tbl_score  a
SET rank = (     SELECT COUNT(*) + 1 FROM tbl_Score WHERE tot > a.tot );

SET rank = (
            SELECT r
             FROM ( 
                   SELECT num, tot, RANK() OVER( ORDER BY tot DESC ) r
                   FROM tbl_score
              ) t
            WHERE a.num =   t.num
);
COMMIT;

-- ( 문제7 ) 국어문제 1개 정답 없어요..
-- 모든 학생의 국어 점수를 5점 증가.

UPDATE tbl_score
SET kor = CASE 
                 WHEN kor >= 95 THEN   100
                 ELSE kor + 5
          END  ;

-- ( 문제8 *** ) 1001 번 학생의 국어,영어 점수를 1005번 학생의 국어, 영어 점수로 수정

UPDATE tbl_score
SET  kor = (  SELECT kor FROM tbl_score WHERE num = 1005 )
   , eng = (  SELECT eng FROM tbl_score WHERE num = 1005 )
WHERE num = 1001;

-- **** (기억)

UPDATE tbl_score 
SET ( kor, eng ) =  (  SELECT kor, eng FROM tbl_score WHERE num = 1005 )
WHERE num = 1001;

COMMIT;

-- ( 문제9 )tbl_score 테이블에서 [여학생]들만 영어 점수를 5점씩 증가시키는 쿼리 작성.

UPDATE tbl_score
SET eng = CASE 
                 WHEN eng >= 95 THEN 100
                 ELSE   eng + 5
          END
WHERE  num   IN (
                    SELECT num
                    FROM insa
                    WHERE MOD( SUBSTR( ssn, -7, 1 ), 2 ) = 0 
                 );

-- ANY 연산자 --

UPDATE tbl_score
SET eng = CASE 
                 WHEN eng >= 95 THEN 100
                 ELSE   eng + 5
          END
WHERE  num   = ANY (          -- >= ALL       최고급여자
                    SELECT num
                    FROM insa
                    WHERE MOD( SUBSTR( ssn, -7, 1 ), 2 ) = 0 
                 );

-- 상관 서브 쿼리

UPDATE tbl_score 
SET eng = CASE 
                 WHEN eng >= 95 THEN 100
                 ELSE   eng + 5
          END
WHERE num = (
            SELECT ts.num
            FROM tbl_score ts,(
                        SELECT num, DECODE (MOD( SUBSTR(ssn, -7,1),2),0,'여자') gender  
                        FROM insa)i
            WHERE ts.num = i.num AND gender IS NOT NULL
            ) ;

-- MERGE( 합병하다, 합치다 ) : 병합, 통합.
1) 구조가 같은 두 개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작이다.
A B => A, B
2) 예를 들어, 하루에 수만건씩 발생하는 데이터를 하나의 테이블에 관리할 경우 대량의 데이터로 인해 질의문의 성능이 저하된다.
이런 경우, 지점별로 별도의 테이블에서 관리하다가 년말에 종합 분석을 위해 하나의 테이블로 합칠 때 merge 문을 사용하면 편리하다.
3) merge하고자 하는 소스 테이블의 행을 읽어 타킷 테이블에 매치되는 행이 존재하면 새로운 값으로 UPDATE를 수행하고, 만일 매치되는 행이 없을 경우 새로운 행을 타킷 테이블에서 INSERT를 수행한다.

  A
   홍길동  15        UPDATE
   김길동   5        INSERT

4) merge 문에서 where 절은 사용할 수 없으며 대신 on이 사용된다. 또한 when matched then 절과 when not matched then 절에는 테이블명 대신 alias를 사용한다.

5.【형식】
    MERGE [hint] INTO [schema.] {table ¦ view} [t_alias]
      USING {{[schema.] {table ¦ view}} ¦ subquery} [t_alias]
      ON (condition) [merge_update_clause] [merge_insert_clause] [error_logging_clause];

【merge_update_clause 형식】
   WHEN MATCHED THEN UPDATE SET {column = {expr ¦ DEFAULT},...}
     [where_clause] [DELETE where_clause]

【merge_insert_clause 형식】
   WHEN NOT MATCHED THEN INSERT [(column,...)]
    VALUES ({expr,... ¦ DEFAULT}) [where_clause]

【where_clause 형식】
   WHERE condition

【error_logging_clause 형식】
   LOG ERROR [INTO [schema.] table] [(simple_expression)]
     [REJECT LIMIT {integer ¦ UNLIMITED}]

6) 테스트

CREATE TABLE tbl_emp(
   id   number not null constraint PK_tblEmp_id primary key
   , name varchar2(10) not null
   , salary number
   , bonus  number default 100
);

 insert into tbl_emp(id,name,salary) values(1001,'jijoe',150);
 insert into tbl_emp(id,name,salary) values(1002,'cho',130);
 insert into tbl_emp(id,name,salary) values(1003,'kim',140);
COMMIT;

CREATE TABLE tbl_bonus(
  id number
  , bonus number default 100
);

INSERT INTO tbl_bonus ( id )  ( SELECT id FROM tbl_emp );
COMMIT;

INSERT INTO tbl_bonus VALUES ( 1004, 50 );

tbl_bonus
1001   100
1002   100
1003   100
1004   50

tbl_emp
1001   jijoe   150   100
1002   cho   130   100
1003   kim   140   100

-- MERGE : tbl_emp + tbl_bonus 두 테이블 병합

-- ORA-00942: table or view does not exist
-- USING  tbl_emp  e  4컬럼 X

USING ( SELECT id, salary FROM tbl_emp ) e
ON ( b.id = e.id )
WHEN MATCHED THEN -- UPDATE
   UPDATE   SET b.bonus = b.bonus   + e.salary * 0.01
WHEN NOT MATCHED THEN -- INSERT
  INSERT (b.id, b.bonus) VALUES ( e.id, e.salary * 0.01 );

-- 병합 문제)

CREATE TABLE tbl_merge1
(
   id number primary key
   , name varchar2(20)
   , pay number
   , sudang number            --
);

INSERT INTO tbl_merge1 (id, name, pay, sudang) VALUES (1, 'a', 100, 10);
INSERT INTO tbl_merge1 (id, name, pay, sudang) VALUES (2, 'b', 150, 20);
INSERT INTO tbl_merge1 (id, name, pay, sudang) VALUES (3, 'c', 130, 0);

COMMIT;

CREATE TABLE tbl_merge2
(
   id number primary key 
   , sudang number            --
);
    
INSERT INTO tbl_merge2 (id, sudang) VALUES (2,5);
INSERT INTO tbl_merge2 (id, sudang) VALUES (3,10);
INSERT INTO tbl_merge2 (id, sudang) VALUES (4,20);

COMMIT;

--  [ tbl_merge2 ] 테이블에  두 개 테이블의 수당을  병합해서 출력(조회)
SELECT id, sudang
FROM tbl_merge1;

1   10               X  INSERT
2   20               O  UPDATE
3   0                O  UPDATE

SELECT id, sudang
FROM tbl_merge2;

2   5
3   10
4   20

-- 병합 쿼리 작성, 확인.--

1   10 INSERT
2   15 UPDATE
3   10 UPDATE
4   20

MERGE INTO tbl_merge2 t2
USING ( SELECT id, sudang FROM tbl_merge1 ) t1
ON ( t1.id = t2.id )
WHEN MATCHED THEN     -- UPDATE
   UPDATE  
   SET t2.sudang = t2.sudang + t1.sudang
WHEN NOT MATCHED THEN -- INSERT
  INSERT  ( t2.id, t2.sudang ) VALUES ( t1.id, t1.sudang );

-- 제약 조건( constraint ) --
1. 제약조건을 확인 : user_constraints 뷰(View) -- (시험)

SELECT *
FROM user_constraints
WHERE table_name = 'EMP';

2. 제약조건 사용하는 목적 ?
  ㄱ. 테이블에  행을 추가, 수정, 삭제( DML )할 때 적용되는 규칙
     DATA INTERGRITY( 데이터 무결성 )을 위해서 제약 조건을 사용한다.
     예) ID  고유한값 - 중복 X, 유일한 값
         ID  수정 X
    예)  kor  NUMBER(3)   -999~999  101추가  , 수정

3. 무결성(integrity )
  - 데이터의 정확성과 일관성을 유지하고, 데이터에 결손과 부정합이 없음을 보증하는 것
  - 예를 들어,
     데이터 무결성(data integrity)이라 하면
     데이터를 보호하고, 항상 정상인 데이터를 유지하는  것
3-2. 무결성 3가지 종류
    • 1) 개체 무결성(Entity Integrity) : pk 중복안됨
         emp  PK- empno 사원번호
         7369  SMITH~
         INSERT     empno    7369 KENIK  X

    • 2) 참조 무결성(Relational Integrity) : dept 테이블의 deptno를 참조하는데 없으면 안됨
    dept
    deptno 10,20,30,40

    emp
    INSERT     deptno 50  X

    • 3) 도메인 무결성(domain integrity) : 테이블 만들때 숫자 3개로 설정했는데 다른 문자는 안됨
      zipcode    000-000      12A-12B  X
      ssn          14             15   X

4. 제약조건을 사용하는 이유 : 데이터의 무결성을 지키기 위해 제약조건 사용
5. 제약조건의 특징

    • DML 작업에서 잘못되는 것을 제약조건에 의해 [방지]한다.
    • 모든 정보가 [***데이터 사전***]에 저장된다.
    • 언제든지  제약조건을  disable, enable시킬 수 있다.
    • 처리결과가 즉시 사용자에게 넘겨진다.
    • 한 컬럼에 여러 개의 제약조건을 지정할 수 있다.
    • 하나의 컬럼뿐만 아니라 여러 컬럼을 조합하여 하나의 key를 구성하는
        composit key(복합키)를 만들 수 있다. 예: PRIMARY KEY(pno, ename) ****
    • 제약조건의 관리는 DB server가 담당한다.

6.  제약조건 선언 및 수정할 수 있다.
• CREATE TABLE
• ALTER TABLE
• CREATE VIEW
• ALTER VIEW

-- 제약조건 복사 X,
CREATE TABLE tbl_empcopy  -- empno 컬럼 PK 제약조건 설정해 줘야 된다.
AS
  (  SELECT * FROM emp );
--
SELECT *
FROM tbl_empcopy;
-- ID가 똑같은 다른 사원을 추가
INSERT INTO tbl_empcopy (empno, ename) VALUES ( 7369, 'KENIK' );

7. 제약조건( constraint )을  생성하는 2가지 방법
  1) IN-LINE 제약조건 방법 (== COLUMN LEVEL )
  2) OUT-OF-LINE 제약조건 방법( == TABLE LEVEL )

8. 제약 조건의 5가지 종류
  1) PRIMARY KEY( PK )
  2) FOREIGN KEY( FK )
  3) NOT NULL   ( NN )
  4) UNIQUE KEY ( UK )
  5) CHECK      ( CK )

저작자표시

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

[Day12] Oracle 12 - CROSS JOIN, DB Modeling (0)	2022.10.13
[Day11] Oracle 11 - PURGE, 제약조건2, JOIN (0)	2022.10.12
[Day9] Oracle 9 - PIVOT, 자료형(CHAR, VARCHAR, NUMBER, CLOB), CREATE/DROP/ALTER (0)	2022.10.07
[Day8] Oracle 8 - RANK, HAVING, ROLLUP, CUBE (0)	2022.10.06
[Oracle/Table] 오라클 테이블 (0)	2022.10.06

[Day10] Oracle 10 - MODIFY, DROP COLUMN, Table copy, sub query(insert, update, delete), MERGE, Constraint

[Day10] Oracle 10 [10/11]

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

전체 카테고리

블로그 인기글

티스토리툴바

[Day10] Oracle 10 [10/11]

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

전체 카테고리

최근 글

최근댓글

블로그 인기글

티스토리툴바