[Day9] Oracle 9 - PIVOT, 자료형(CHAR, VARCHAR, NUMBER, CLOB), CREATE/DROP/ALTER

[Day9] Oracle 9 [10/7]

 

--1. emp 테이블에서 job 별로 사원수 몇 명 조회(출력) 하는 쿼리 작성.
--
--      [실행결과]
--       CLERK   SALESMAN  PRESIDENT    MANAGER    ANALYST
--    ---------- ----------   ----------        ----------     ----------
--         3                 4                    1                     3                   1

SELECT 
     COUNT( DECODE( job, 'CLERK', 'O') ) CLERK -- 카운팅할 것이기 때문에 어떤 값이든 상관 없어서 'O'
     ,COUNT( DECODE( job, 'SALESMAN', 'O') ) SALESMAN
     ,COUNT( DECODE( job, 'PRESIDENT', 'O') ) PRESIDENT
     ,COUNT( DECODE( job, 'MANAGER', 'O') ) MANAGER
     ,COUNT( DECODE( job, 'ANALYST', 'O') ) ANALYST
FROM emp;

-- [ PIVOT/UNPIVOT 함수 ]
1) 오라클 11g 부터 제공하는 함수
2) 행과 열을 뒤집는 함수
3) 형식

SELECT * 
FROM (피벗 대상 서브 쿼리문)
PIVOT (그룹함수(집계컬럼) FOR 피벗컬럼 IN( 목록 피벗컬럼 값 AS 별칭...))
[출처] [Oracle] 오라클 PIVOT(피벗)함수|작성자 끄니

    ㄱ. 피벗 대상 쿼리문

     SELECT job FROM emp;

    ㄴ. 그룹함수 COUNT( job 컬럼)    FOR IN( CLERK   SALESMAN  PRESIDENT    MANAGER    ANALYST )

SELECT *    
FROM ( SELECT job FROM emp ) -- 피봇대상 
PIVOT ( COUNT(job)  FOR job  IN ( 'CLERK', 'SALESMAN', 'PRESIDENT', 'MANAGER', 'ANALYST') );

--2. emp 테이블에서  [JOB별로] 각 월별 입사한 사원의 수를 조회 
--  ㄱ. COUNT(), DECODE() 사용

--JOB         COUNT(*)         1월         2월         3월         4월         5월         6월         7월         8월         9월        10월        11월        12월
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
--CLERK              3          1          0          0          0          0          0          0          0          0          0          0          2
--SALESMAN           4          0          2          0          0          0          0          0          0          2          0          0          0
--PRESIDENT          1          0          0          0          0          0          0          0          0          0          0          1          0
--MANAGER            3          0          0          0          1          1          1          0          0          0          0          0          0
--ANALYST            1          0          0          0          0          0          0          0          0          0          0          0          1
--

    SELECT job  , COUNT(*)  
       , COUNT(  DECODE(  TO_CHAR( hiredate , 'MM' ) , '1', 'O'   )   )  "1월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 2, 'O'   )   )  "2월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 3, 'O'   )   )  "3월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 4, 'O'   )   )  "4월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 5, 'O'   )   )  "5월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 6, 'O'   )   )  "6월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 7, 'O'   )   )  "7월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 8, 'O'   )   )  "8월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 9, 'O'   )   )  "9월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 10, 'O'   )   )  "10월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 11, 'O'   )   )  "11월"
       , COUNT(  DECODE(  EXTRACT( MONTH FROM hiredate ) , 12, 'O'   )   )  "12월"
    FROM emp
    GROUP BY job;

--  ㄴ. GROUP BY 절 사용
--
--         월        인원수
------------ ----------
--         1          1
--         2          2
--         4          1
--         5          1
--         6          1
--         9          2
--        11          1
--        12          3
--
--8개 행이 선택되었습니다. 

TO_CHAR & EXTRACT

SELECT TO_CHAR( hiredate, 'MM' ) 월,COUNT(*) 인원수
FROM emp
GROUP BY TO_CHAR( hiredate, 'MM' )
ORDER BY TO_CHAR( hiredate, 'MM' );
--
SELECT EXTRACT( MONTH FROM hiredate ) 월,COUNT(*) 인원수
FROM emp
GROUP BY EXTRACT( MONTH FROM hiredate )
ORDER BY EXTRACT( MONTH FROM hiredate );

 [문제] 피봇함수를 사용해서 아래와 같이 출력.
 JOB               1월          2          3          4          5          6          7          8          9         10         11         12
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
CLERK              1          0          0          0          0          0          0          0          0          0          0          2
SALESMAN           0          2          0          0          0          0          0          0          2          0          0          0
PRESIDENT          0          0          0          0          0          0          0          0          0          0          1          0
MANAGER            0          0          0          1          1          1          0          0          0          0          0          0
ANALYST            0          0          0          0          0          0          0          0          0          0          0          1
--
-- 아래 쿼리를 피봇 대상 서브쿼리.

SELECT EXTRACT( MONTH FROM hiredate )
FROM emp;

--

SELECT *
FROM  (
    SELECT  job,  EXTRACT( MONTH FROM hiredate ) hire_month
    FROM emp
  )
PIVOT ( COUNT(hire_month)   FOR hire_month IN (1 AS "1월",2,3,4,5,6,7,8,9,10,11, 12) );

--3. emp 테이블에서 각 부서별 급여 많이 받는 사원 2명씩 출력
--  실행결과)
--       SEQ      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
------------ ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
--         1       7839 KING       PRESIDENT            81/11/17       5000                    10
--         2       7782 CLARK      MANAGER         7839 81/06/09       2450                    10
--         1       7902 FORD       ANALYST         7566 81/12/03       3000                    20
--         2       7566 JONES      MANAGER         7839 81/04/02       2975                    20
--         1       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
--         2       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
-- TOP-N, RANK 함수  

WITH 
 temp AS (
            SELECT EMPNO, ENAME ,     JOB ,             MGR, HIREDATE,        SAL ,      COMM,     DEPTNO
             , RANK() OVER( PARTITION BY deptno ORDER BY  sal + NVL(comm, 0)  DESC ) seq
            FROM emp
  )
SELECT *
FROM temp
WHERE seq <= 2;

SELECT seq, ename, job, mgr, hiredate, sal, comm, deptno
FROM (
           SELECT ename, job, mgr, hiredate, sal, comm, deptno
               , ROW_NUMBER() OVER( PARTITION BY deptno  ORDER BY sal + NVL(comm, 0) DESC )  seq
           FROM emp
        ) 
WHERE seq <= 2;

---

[문제] emp 테이블에서 
   1등급   2등급        5등급
     2      3            1   
  ( 조건 : PIVOT 함수 사용해서 )      + 조인  
  
  1) GROUP BY

  SELECT s.grade || '등급'  "등급"
        , COUNT(*) 사원수
  FROM emp e, salgrade s
  --WHERE e.deptno = d.deptno   이콜조인
  WHERE e.sal BETWEEN s.losal AND s.hisal
  GROUP BY s.grade
  ORDER BY s.grade ASC; -- NON이콜조인

-> 결과 :

  2) COUNT, DECODE       -> PIVOT 함수  11g 추가

  SELECT COUNT( DECODE( grade , 1 , 'O' ) ) "1등급"
            ,COUNT( DECODE( grade , 2 , 'O' ) ) "2등급"
            ,COUNT( DECODE( grade , 3 , 'O' ) ) "3등급"
            ,COUNT( DECODE( grade , 4 , 'O' ) ) "4등급"
            ,COUNT( DECODE( grade , 5 , 'O' ) ) "5등급"
   FROM emp e, salgrade s
   WHERE e.sal BETWEEN s.losal AND s.hisal;

-> 결과 :

  3) PIVOT 
    ㄱ. 피봇대상

 SELECT grade
    FROM emp e, salgrade s
    WHERE e.sal BETWEEN s.losal AND s.hisal

    ㄴ. 형식

SELECT *
    FROM (
        SELECT grade
        FROM emp e, salgrade s
        WHERE e.sal BETWEEN s.losal AND s.hisal
    )
    PIVOT ( COUNT( grade ) FOR grade IN ( 1 AS "1등급", 2, 3, 4, 5  AS "5등급") );

 (문제) emp 테이블에서 년도별 입사사원수를 조회
 1) COUNT, DECODE

 SELECT 
    COUNT( DECODE( TO_CHAR( hiredate, 'YYYY' ) , 1980, 'O' ) ) "1980년도"
    , COUNT( DECODE( TO_CHAR( hiredate, 'YYYY' ) , 1981, 'O'  ) ) "1981년도"
    , COUNT( DECODE( TO_CHAR( hiredate, 'YYYY' ) , 1982, 'O' ) ) "1982년도"
 FROM emp

-> 결과:

 

 2) GROUP BY 

 SELECT TO_CHAR( hiredate, 'YYYY' ), COUNT(*)
 FROM emp
 GROUP BY TO_CHAR( hiredate, 'YYYY' )
 ORDER BY TO_CHAR( hiredate, 'YYYY' );

 

-> 결과 : 

 

 3) PIVOT
 피봇대상

 SELECT TO_CHAR( hiredate, 'YYYY' )
 FROM emp

  --

  SELECT *
  FROM (  SELECT TO_CHAR( hiredate, 'YYYY' ) y FROM emp  )
  PIVOT (  COUNT( y ) FOR y  IN ( 1980, 1981, 1982 ) );

-> 결과 : 

* 서브쿼리가 안됨!! (1980, 1981, 1982)에 넣는게 안된다는 말임!

-- 피봇 마지막 문제 ( 프로젝트 진행 중 ... )--
1. 테이블 생성 : TBL_PIVOT
2.       컬럼  : no, name , jumsu     국어, 영어, 수학
                            kor , eng, mat
-- 테이블 설계가 잘못되었다.

-- 정규화 잘됨. ( 제 1 정규화 위배 ) DB 모델링
--1    홍길동  90
--2    홍길동  89
--3    홍길동  99
--
--4    홍길동  90  89   99

CREATE TABLE TBL_PIVOT
(
    no NUMBER NOT NULL  PRIMARY KEY -- 고유한키 PK
    , name VARCHAR2(20) NOT NULL
    , jumsu NUMBER(3)  -- NULL 허용
)

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

INSERT INTO TBL_PIVOT ( no, name, jumsu ) VALUES ( 1, '박예린', 90 );  -- kor
INSERT INTO TBL_PIVOT ( no, name, jumsu ) VALUES ( 2, '박예린', 89 );  -- eng
INSERT INTO TBL_PIVOT ( no, name, jumsu ) VALUES ( 3, '박예린', 99 );  -- mat
 
INSERT INTO TBL_PIVOT ( no, name, jumsu ) VALUES ( 4, '안시은', 56 );  -- kor
INSERT INTO TBL_PIVOT ( no, name, jumsu ) VALUES ( 5, '안시은', 45 );  -- eng
INSERT INTO TBL_PIVOT ( no, name, jumsu ) VALUES ( 6, '안시은', 12 );  -- mat 
 
INSERT INTO TBL_PIVOT ( no, name, jumsu ) VALUES ( 7, '김민', 99 );  -- kor
INSERT INTO TBL_PIVOT ( no, name, jumsu ) VALUES ( 8, '김민', 85 );  -- eng
INSERT INTO TBL_PIVOT ( no, name, jumsu ) VALUES ( 9, '김민', 100 );  -- mat

COMMIT;

[TBL_PIVOT 테이블]
1   박예린   90  k   1
2   박예린   89  e   2
3   박예린   99  m   0
4   안시은   56     1
5   안시은   45    2
6   안시은   12
7   김민   99
8   김민   85
9   김민   100
 
 -- 질문) 피봇
번호 이름 국,영,수
1 박예린  90 89 99
2 안시은  56 45 12
3 김민    99 85 100

 

-- 풀이.
  1) 피봇대상 *** 
  IN ( 국어, 영어, 수학  )
  

  SELECT *
  FROM (
          SELECT   TRUNC( (no-1)/3 )  + 1  no
                , name
                , jumsu
                , DECODE( MOD( no, 3), 1, '국어', 2, '영어', 0, '수학' ) subject -- 과목
          FROM tbl_pivot
  )
  PIVOT(  MAX(jumsu)  FOR subject   IN ('국어',  '영어',  '수학'))
  ORDER BY no ASC;

-> 결과 : 

-- 다른방법

SELECT *
FROM (
    SELECT name, jumsu
    , ROW_NUMBER() OVER(PARTITION BY name ORDER BY no) r -- subejct
    FROM tbl_pivot
    )
PIVOT( SUM(jumsu)   FOR  r  IN ( 1 AS "국", 2"영", 3"수"));

---

dbms_random 패키지
-- 자바 - 임의의 수(난수)     0.0 <=   Math.radnom()   < 1.0
-- Oracle :  dbms_random 패키지 == 관련 함수, 프로시저 등등
--           PL/SQL  6가지 종류 : 패키지(package)

SELECT 
          dbms_random.value a  -- 0.0 <=  실수    < 1.0
           , dbms_random.value( 0, 100) b -- 0 <=  실수  < 100
          -- (문제)   1~45  로또 번호
          , FLOOR(dbms_random.value( 0, 45)) +1  c --- 1<=  정수  <=45
          
           dbms_random.string('U',5) -- 대문자 5개
           , dbms_random.string('L',5) -- LOwer문자 5개
           , dbms_random.string('A',5) -- 대,소문자 5개
            , dbms_random.string('P',5)  -- 알(대,소) + 특수문자
FROM dual;

-- ( 문제 ) SMS 인증번호  숫자 6자리 
     -- 서버 -> 클라이언트 전송
     -- TRIM () 앞위의 공백  제거 X  문자 제거 O
     -- LTRIM()  left
     -- RTRIM()  right
--     0.[134124]06960968277106437169248375591966

SELECT   SUBSTR( dbms_random.value , 3 , 6 )    SMS6자리
       ,  LTRIM( 0.13412406960968277106437169248375591966, '0.' )
       ,  TRUNC( dbms_random.value( 100000, 1000000)) -- 100000 <= 실수 < 999999
       , TRUNC( dbms_random.value * 100000 )
FROM dual;

-- [오라클 자료형( data type ) 정리]  datatype 검색 -- 

--VARCHAR2 
--NUMBER
--DATE, TIMESTAMP


1) CHAR 
  ㄱ) 고정길이 문자 스트링         <-> 가변길이
  ㄴ) ['a']['b']['c'][' '][' '][][][][]  남은 공간은 blank로 채워진다.
  ㄷ) ['ㅁ']['ㅠ']['ㅊ']'ㅇ' 에러가 발생.
  ㄹ) DB 설정에 따라 1문자가 1~4바이트. 
  ㅁ) 형식
    CHAR[(SIZE [BYTE |  CHAR])] 디폴트 1바이트        , 최대값 2000 바이트 
  ㅅ)
      CHAR(3) == CHAR( 3 BYTE )
      CHAR    == CHAR(1 BYTE ) == CHAR( 1 )
      CHAR( 3 CHAR ) -- 3문자를 저장
      
   ㅈ) 테스트

   CREATE TABLE tbl_char(
      aa CHAR   -- char(1) == char(1 byte)
      , bb CHAR(3)-- char(3 byte)
      , cc CHAR(3 CHAR)
   );

 

   DESC tbl_char;
   --
   INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( 'a', 'abc', '세글자' );
   
   -- ORA-12899: value too large for column "SCOTT"."TBL_CHAR"."AA" (actual: 3, maximum: 1)
   -- INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( '한', 'abc', '세글자' );
   
   -- ORA-12899: value too large for column "SCOTT"."TBL_CHAR"."BB" (actual: 9, maximum: 3)
   --INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( 'a', '세글자', '세글자' );
                                               --   ['b'][blank][blank] 고정길이 3byte 유지
   INSERT INTO tbl_char ( aa, bb, cc ) VALUES ( 'a', 'b', '세글자' );
   -- 1 행 이(가) 삽입되었습니다.
   COMMIT;

tbl_char

2) NCHAR = U[N]ICODE + CHAR  
          'a' -> 1 바이트
          '가' -> 3 바이트
          모든 문자 -> 2바이트 처리.

    SELECT VSIZE('a'), VSIZE('가')      
    FROM dual;

   ㄱ) NCHAR[( SIZE )]
   ㅈ) 테스트
     

CREATE TABLE tbl_nchar(
          aa NCHAR   -- char(1)  
          , bb NCHAR(3)-- char(3 byte)
          , cc CHAR(3)
       );
    -- Table TBL_NCHAR이(가) 생성되었습니다.

 

    INSERT INTO tbl_nchar ( aa, bb, cc ) VALUES ( '가', '세글자', 'abc' );
    INSERT INTO tbl_nchar ( aa, bb, cc ) VALUES ( 'a', 'bac', 'abc' );
    --                                                 ['b'][blank][blank]  고정길이 , 최대 2000바이트
     INSERT INTO tbl_nchar ( aa, bb, cc ) VALUES ( 'a', 'b', 'abc' );

tbl_nchar

3) NVARCHAR2(size)    // 한글 3바이트 -> 20바이트면 6글자 / 영어면 VARCHAR
   VARCHAR2(size[BYTE ¦ CHAR])  의 시노님이 VARCHAR 자료형
   
    VAR + CHAR( SIZE [BYTE|CHAR])  최대값 4000바이트
    가변길이
    
    char(5) == char(5 byte)          고정길이    ['a'][blank][blank][blank][blank]    
    varchar2(5) == varchar2(5 byte)  가변길이    ['a']
                                     가변길이    ['a']['b']

   고정길이(char) / 가변길이( varchar2 )
   
   주민등록번호 :  모든 사람  14자리  ( 고정길이 ) 00000-00000 char/nchar
                 예) rrn char(14)

   게시글 제목 :   가변길이  varchar2 == varchar 
                           nvarchar2(20) 20문자
                           varchar2(20)   20바이트   한글6글자 최대
                           varchar2(20 char)

   숫자,알,한글
char,nchar(2000) / varchar2,nvarchar2,varchar(4000) / LONG( 2GB )

 

4) LONG - 문자 가변길이, 2GB 지원

5)NUMBER[(p[,s])]
    p : precision  전체 자릿수  1~38
    s : scale      소숫점이하 자릿수   -84~127   
 
    예) NUMBER( p ) == NUMBER( p, 0 )
        NUMBER      == NUMBER(38, 127)
        NUMBER( p, s )
        

  CREATE TABLE tbl_number(
      name  nvarchar2(10)-- char X,nchar X,nvarchar2, varchar2, long X
      , kor number(3)   -- 3자리 정수    -999~999
      , eng number(3)   -- 체크 제약조건 0~ 100 정수
      , mat number(3)
      , tot number(3,0)  -- 정수
      , avg  number(5,2) -- 실수
   );

 

   INSERT INTO tbl_number VALUES ('홍길동', 90, 89, 100, null , null );
   
   -- kor = 90.525 == 91
   INSERT INTO tbl_number VALUES ('김길동', 90.125, 80, 78, null , null );
   
   INSERT INTO tbl_number VALUES ('박길동', 90, -80, 111, null , null );
   
   UPDATE tbl_number
   SET   tot = kor + eng + mat , avg = (kor+eng+mat)/3
   
   -- WHERE
   -- ORA-01438: value larger than specified precision allowed for this column
   --                  허용된 정밀도 보다  더 큰 값...
   UPDATE tbl_number
   SET     avg =  12345.67
   
   -- PL/SQL 동적쿼리...
   UPDATE tbl_number
   SET    kor = 0
   WHERE kor <0 OR kor > 100;
   
   UPDATE tbl_number
   SET    eng = 0
   WHERE eng <0 OR eng > 100;
   
   UPDATE tbl_number
   SET    mat = 0
   WHERE mat <0 OR mat > 100;
   
   COMMIT;
   SELECT *
   FROM tbl_number;

   0.01234 ->  number(4,5)  -> 0.01234

 

tbl_number

5) FLOAT[(p)] - 내부적 NUMBER 처럼... X

6)  DATE  
   - 날짜 + 시간 정보
   - 고정길이  ( 7바이트 )
   

  SELECT SYSDATE   -- 22/10/07
   , TO_CHAR( SYSDATE, 'YYYY.MM.DD TS' )
  FROM dual;

DECODE 함수 -> CASE 함수
  =              ~    

7) TIMESTAMP[(n)]
  - DATE 형의 확장된 형태
    날짜+시간           + ms , 타임존 등등

  TIMESTAMP = TIMESTAMP(0~9)
              00.000000
            
            예)  게시글 작성일 : [ Date ], Timestamp X
8) 
INTERVAL YEAR[(n)] TO MONTH n=2  년과 월을 사용하여 날짜값의 기간을 저장 
INTERVAL DAY[(n1)] TO SECOND[(n2)] n1=2, n2=6  두 날짜값의 차이를 일, 시, 분, 초, 밀리초로 저장 

9) 2진 데이터   0/1
  ㄱ.  이미지 저장 01010101010101011010101011  -> 이미지 변환 X
  ㄴ.  서버 이미지 경로만 저장
RAW(size)        2000 바이트  
LONG RAW         2GB


2GB 

10) LOB ( [L]arge [OB]ject )  --정독--
: 2기가 보다 큰 데이터 (문자든 숫자든)

 DB 데이터를 저장하는 내부에 저장
 B  + LOB    - Binary(2기가 넘는 2진 데이터)
 C  + LOB     - Char 고정길이 + 1~4 바이트 (2기가 넘는 CHAR 자료형)
 NC + LOB    - NChar 고정길이 + 유니코드 (2기가 넘는 NCHAR 자료형)


 BFILE  (    외부에 저장  )

 예) 게시판 글 내용 content 컬럼 :  nchar  2000바이트   고정길이 X  (냉무)
                                 nvarchar2 4000 바이트
                                 long     2GB
                                 [ CLOB / ( NCLOB ) ]  2GB 이상

 - ROWNUMBER 의사컬럼 ( TOP- N 방식 )
 - ROWID 의사컬럼 = ROW(행, 레코드) + ID(고유한키)
 SELECT ROWID, dept.*
 FROM dept;
 
 --------------------------------------------------------------------------
 -- ORA-00937: not a single-group group function

-- [COUNT 함수 ] 
 【형식】
   COUNT([* ¦ DISTINCT ¦ ALL] 컬럼명) [ [OVER] (analytic 절)]

 --  정렬된 누적된 수를 카운팅하더라~ 

 SELECT name, basicpay
   ,  COUNT(*) OVER( ORDER BY basicpay ASC ) -- 질의한 행의 누적된 결과값을 반환
 FROM insa;

  -- 부서별로 누적된 결과값을 반환

  SELECT name, basicpay, buseo
   ,  COUNT(*) OVER( PARTITION BY buseo  ORDER BY basicpay ASC ) -- 질의한 행의 누적된 결과값을 반환
 FROM insa;

 
 -- [SUM]
 【형식】

   SUM ([DISTINCT ¦ ALL] expr)
               [OVER (analytic_clause)]  -- 누적된 합\

 --   ORA-00937: not a single-group group function             

 SELECT DISTINCT buseo
        --, name         
         , SUM(basicpay) OVER( ORDER BY buseo )  s
 FROM insa  
 ORDER BY s ASC;

-------------------------------------------------------------------------
*** [ DB 모델링 ] + PL/SQL ***
- 테이블(table) 생성 + 수정  + 삭제 등등
-- 회원정보를 저장할 수 있는 테이블 생성
컬럼(열) /  컬럼명 /        자료형  / 크기                    / * 필수입력항목
--           --
아이디     id              문자 10  가변   nvarchar2(10)    NOT NULL   고유키(PK)
이름       name            문자 10  가변   nvarchar2(10)    NOT NULL
나이       age             숫자(정수) 3     number(3)          NULL
전화번호   tel              문자열 13 고정  char(13)         NOT NULL
생일       birth           날짜            date               NULL
기타       etc             문자    가변        nvarchar2(100)  NULL
--
테이블을 만드는 가장 단순하면서도 일반적인 명령 형식은 다음과 같다. 
【형식】
    -- 임시 테이블 --

    CREATE [GLOBAL TEMPORARY] TABLE [schema.] table {relational_table ¦ 
                                                     object_table ¦
                                                     XML_Type_table}

【relational_table의 형식】
   [(relational_properties) [ON COMMIT {DELETE ¦ PRESERVE} ROWS]
   [physical_properties] [table_properties];
【object_table의 형식】
   OF [schema.]object_type [object_table_substitution]
    [(object_properties) [ON COMMIT {DELETE ¦ PRESERVE} ROWS]
    [OID_clause] [OID_index_clause] [physical_properties] [table_properties];
【XMP_Type_table의 형식】
   OF XMLTYPE [(object_properties) [XMLTYPE XML_Type_storage] [XML_Schema_spec]
    [XML_Type_virtual_columns] [ON COMMIT {DELETE ¦ PRESERVE} ROWS]
    [OID_clause] [OID_index_clause] [physical_properities] [table_properities]

【간단한형식】
    CREATE [GLOBAL TEMPORARY] TABLE [schema.] table
      ( 
        열이름  데이터타입 [DEFAULT 표현식] [제약조건] 
       [,열이름  데이터타입 [DEFAULT 표현식] [제약조건] ] 
       [,...]  
      ); 

CREATE TABLE scott.tbl_member (

      id               nvarchar2(10)    NOT NULL   PRIMARY KEY
      ,  name          nvarchar2(10)    NOT NULL
      , age            number(3)                                  -- DEFAULT 0         
      , tel            char(13)         NOT NULL
      , birth          date               
      ,  etc           nvarchar2(100)              
)
--  Table SCOTT.TBL_MEMBER이(가) 생성되었습니다.

 
1) 생성된 테이블 확인.

SELECT * 
FROM tabs
WHERE table_name LIKE '%MEMBER%'; 

DESC tbl_member;

2) 테이블 삭제

【형식】

     DROP TABLE [schema.]table [CASCADE CONSTRAINTS] [PURGE];

• 테이블을 삭제하면 테이블의 컬럼에 대해 생성된 인덱스도 함께 삭제된다.
• 삭제된 테이블과 관련된 뷰와 시노늄은 'invaild' 상태가 된다.
• 삭제하고자 하는 테이블의 기본 키나 유일 키를 다른 테이블에서 참조하고 있는 경우에는 해당 테이블을 삭제할 수 없다. 
이 경우에는 참조하는 테이블을 먼저 삭제한 후에 삭제하거나, CASCADE CONSTRAINTS 옵션을 사용하여 무결성 제약조건을 동시에 삭제하면 된다.

--DROP TABLE emp;
--DROP TABLE dept;
--ORA-02449: unique/primary keys in table referenced by foreign keys

 DROP TABLE scott.tbl_member  PURGE ;    -- [PURGE]; 제거하다. 
 -- PURGE 복원 X , 완전히 테이블 삭제
 

 CREATE TABLE scott.tbl_member (

      id               nvarchar2(10)    NOT NULL   PRIMARY KEY
      ,  name          nvarchar2(10)    NOT NULL
      , age            number(3)                                  -- DEFAULT 0         
  --    , tel            char(13)         NOT NULL
      , birth          date               
  --    ,  etc           nvarchar2(100)              
)

 DESC tbl_member;
1. 기존 tbl_member테이블에서  새로운 컬럼 추가. 
   ( 연락처, 기타 컬럼 추가 )
   테이블 수정
CREATE TABLE
DROP TABLE
ALTER TABLE   
 【형식】컬럼추가
   ALTER TABLE 테이블명
   ADD (컬럼명 datatype [DEFAULT 값]
       [,컬럼명 datatype]...);
--

INSERT INTO tbl_member VALUES ('admin','홍길동', 20, '2022.02.01' );
COMMIT;

SELECT * 
FROM tbl_member;

-- ORA-01758: table must be empty to add mandatory (NOT NULL) column
--             NOT NULL 컬럼을 추가하려면 테이블이 비어 있어야 한다. 
-- 새로운 컬럼 추가 실패...

ALTER TABLE  tbl_member
ADD (
       tel            char(13)       NOT NULL
     , etc           nvarchar2(100)     
    );