PL/SQL (P14-1)

• 절차적인 언어를 확장해서 프로그래밍 하듯이 쓰는것

Advantages of PL/SQL (P14-2)

• 오라클은 한번에 하나의 SQL 문장을 수행
• 여러 SQL 문장을 한번에 오라클에 보낼 수 있음
• 오라클과의 커뮤니케이션을 줄일 수 있다, 네트워크 트래픽 등도 줄음.
• 중간꺼(3~6등 같은 거)를 가져오는 방법은 여러가지가 있다.  -> SQL 문장의 간단한 것보다는 빠른게 좋은 쿼리문이다.

PL/SQL Block Structure (P14-3)

• Block = BEGIN ~ END
• DECLARE : BEGIN ~ END 사이에 사용가능, 변수, 커서 등 선언 가능
• EXCEPTION : 예외처리

PL/SQL Category (P14-5)

• Anonymous blocks : 이름 없이 잠깐 사용~

SQL*Plus Commands (P14-6)

• ACCPET : 입력받기
• VARIABLE : 변수 선언
• PRINT : 변수인쇄
• EXECUTE : 저장프로시저 실행

PL/SQL Block Debugging (P14-8)

• ERRORS : 오류보기

Anonymous Block (P14-9)

• EDIT anony : 치면 메모장이 뜸~
• ACCPET p_sal PROMT "Enter the salary:' -> User에게 입력받음
• VARIABLE : 변수 선언
• DECLARE

v_sal NUMBER := &p_sal;   -> v_sal 변수 선언후 거기에 p_sal 값을 저장  -> ' := '임을 주의

• P14-9 Example

1. SQL> EDIT anony
2. SQL> @anony
   Enter the salary:10
   old   2: v_sal NUMBER := &p_sal;
   new   2: v_sal NUMBER := 10;
   old   4: :g_annual_sal := &p_sal*12;
   new   4: :g_annual_sal := 10*12;
3. PL/SQL procedure successfully completed.
4. 
   G_ANNUAL_SAL
   ------------
            120

Stored Program (P14-10)

• Procedure : 리턴 불가(C#의 out 같은 것 통해서 리턴처럼 처리할 수는 있음), Select안에서 사용불가
• Function : 리턴 가능, Select안에서 사용가능

Stored Procedure (P14-11)

• 기본 문법

CREATE OR REPLACE PROCEDURE name

[(Parameter, ...)]

IS

PL/SQL Block; (BEGIN~END 를 반드시 사용해야함)

• Parameter 종류

IN : value만 전달됨

OUT : C#의 out과 같음, 주소값이 넘어감

IN, OUT : C#의 ref와 같음

• 실행방법

EXEC 저장프로시저명 (매개변수 목록, ~~~);

• P14-13 Example

1. SQL> EDIT p1
2.  
3. CREATE OR REPLACE PROCEDURE change_salary(p_id IN NUMBER, p_new_sal IN NUMBER)
   IS
   BEGIN
    UPDATE s_emp
    SET salary = p_new_sal
    WHERE id = p_id;
    COMMIT;
   END change_salary;
   /
   
4.  
5. SQL> @p1
6. Procedure created.
7.  
8. SQL> EXEC change_salary(1,10000);
9. PL/SQL procedure successfully completed.

Stored Function (P14-14)

• P14-16 Example

1. SQL> EDIT f1
2.  
3. CREATE OR REPLACE FUNCTION get_salary(p_id IN NUMBER) RETURN NUMBER
   IS v_sal NUMBER;
   BEGIN
    SELECT salary INTO v_sal
    FROM s_emp WHERE id = p_id;
    RETURN v_sal;
   END get_salary;
   /
4.  
5. SQL> @f1
6. Function created.
7.  
8. SQL> VARIABLE g_sal NUMBER
   
9.  
10. SQL> EXEC :g_sal := get_salary(1);
11. PL/SQL procedure successfully completed.
12.  
13. SQL> PRINT g_sal;
14.      G_SAL
    ----------
         10000

Chapter15~~~

Scalar Data Type (P15-2)

딱 하나만을 데이터로 같는 것

%TYPE Attribute (P15-7)

데이터 타입 선언

v_last_name   s_emp.last_name%TYPE;   ->  v_last_name의 타입을 s_emp.last_name랑 같게 만듦

 예제~

1. SQL> ed a1
2.  
3. • DECLARE
      v_last_name s_emp.last_name%TYPE;
      v_first_name s_emp.first_name%TYPE;
     BEGIN
      SELECT last_name, first_name
      INTO v_last_name, v_first_name
      FROM s_emp
      WHERE id = 1;
   •  DBMS_OUTPUT.PUT_LINE(v_last_name);
      DBMS_OUTPUT.PUT_LINE(v_first_name);
     END;
     /
4.  
5.  
6. SQL> SET SERVEROUTPUT ON
7.  
8. SQL> @a1
   Velasquez
   Carmen
9. PL/SQL procedure successfully completed.

%ROWTYPE Attribute (P15-8)

v_dept   s_dept%ROWTYPE   -> s_dept 테이블에 있는 모든 컬럼만큼 각각 변수를 선언함(변수명 = 컬럼명)  -> 변수의 타입 = s_dept 테이블의 각 컬럼의 타입이랑 같게~

이 글은 스프링노트에서 작성되었습니다.

• Database User (P13-1)

1. SQL> conn system/system
   Connected.
   
2.  
3. SQL> conn sys/system
   ERROR:
   ORA-28009: connection to sys should be as sysdba or sysoper
4. 
   Warning: You are no longer connected to ORACLE.
   
5.  
6. // 현재 윈도우의 계정이 bit이고 그 상태에서 오라클을 설치하고, 해당 계정으로 로그인했기 때문에 아래 문장 가능
7. // 윈도우에 로그인시 다른 계정으로 로그인 한다면 안됨~
8. // sys 계정 접속 방법
9. SQL> conn /as sysdba
   Connected.
   
10.  
11. SQL> show user
    USER is "SYS"

Manage User (P13-2)

• system or sys 계정으로 로그인 후 사용

1. SQL> conn system/system
   Connected.
2. 
   SQL> CREATE USER user00      // ID
     2  IDENTIFIED BY user00;   // Password
3. User created.

• 생성된 User (user00)로 로그인 시도했지만 오류~
• 세션을 만들수 있는 권한이 없어서 로그인 안됨(P13-7에서 권한 주는 것 나옴)

1. SQL> conn user00/user00;
   ERROR:
   ORA-01045: user USER00 lacks CREATE SESSION privilege; logon denied
2. 
   Warning: You are no longer connected to ORACLE.

Privileges (P13-4)

• system 권한 : 세션을 만들 수 있냐? 또 다른 유저를 만들수 있냐?
• object 권한 : 특정 테이블을 업데이트 등을 할 수 있냐?

Control System Privileges (P13-6)

• 권한을 줄 때는 'GRANT'~
• 권한을 뺄 때는 'REVOKE'~

• user00계정에 create session 권한을 줌으로써 로그인 가능

1. SQL> conn system/system
   Connected.
2.  
3. SQL> GRANT create session To user00;
4. Grant succeeded.
5.  
6. SQL> conn user00/user00
   Connected.

• 테이블 생성과 관련된 리소스에 대한 권한이 없으므로 user00 계정으로 테이블 생성 안됨~

1. SQL> conn user00/user00
   Connected.
   
2.  
3. SQL> CREATE TABLE x (a NUMBER);
   CREATE TABLE x (a NUMBER)
   *
   ERROR at line 1:
   ORA-01031: insufficient privileges

• user00 계정에 리소스 등의 권한 줌~
• user00 계정으로 테이블 생성 가능

1. SQL> conn system/system
   Connected.
   
2. SQL> GRANT connect, resource TO user00;
3. Grant succeeded.
4.  
5. SQL> conn user00/user00
   Connected.
   
6.  
7. SQL> CREATE TABLE x (a NUMBER);
8. Table created.

WITH ADMIN OPTION (13-9)

Object Privileges (P13-10)

• 특정 object에 대한 권한
• ON 절에서 사용함

Role (P13-13)

• 권한의 묶음
• user들에게 권한을 줄 때 하나씩 주기 힘드니까 권한을 묶어서 한번에 적용
• 권한 관리를 효율적으로 하기위해 사용

• P13-15 예제

1. // Role를 이용해서 mgr에 권한을 묶음(select any table, update any table 권한)
2. SQL> conn system/system
   Connected.
   
3.  
4. SQL> CREATE ROLE mgr;
5. Role created.
6.  
7. SQL> GRANT select any table, update any table TO mgr;
8. Grant succeeded.
9.  
10. // 묶은 권한을 user00에 줌
11. SQL> GRANT mgr to user00;
12. Grant succeeded.
13.  
14. SQL> conn user00/user00;
    Connected.
    
15.  
16. // user00계정은 모든 테이블을 select, update 할 수 있는 권한을 받았으므로 test00계정의 s_dept 테이블을 select, update 할 수 있음
17. SQL> SELECT * FROM test00.s_dept;
18.         ID NAME                       REGION_ID
    ---------- ------------------------- ----------
            10 Finance                            1
            31 Sales                              1
            32 Sales                              2
            33 Sales                              3
            34 Sales                              4
            35 Sales                              5
            41 Operations                         1
            42 Operations                         2
            43 Operations                         3
            44 Operations                         4
            45 Operations                         5
            50 Administration                     1
           100 x                                  2
19. 13 rows selected.
20.  
21. SQL> UPDATE test00.s_emp SET salary = 0;
22. 25 rows updated.
23.  
24. SQL> ROLLBACK;
25. Rollback complete.
26.  
27. SQL> conn system/system
    Connected.
    
28.  
29. // 권한을 뺏은 뒤로는 다시 권한 없어서 작동 안함~ 
30. SQL> REVOKE mgr FROM user00;
31. Revoke succeeded.
32.  
33. SQL> conn user00/user00
    Connected.
    
34.  
35. SQL> SELECT * FROM test00.s_dept;
    SELECT * FROM test00.s_dept
                         *
    ERROR at line 1:
    ORA-00942: table or view does not exist
36.  

이 글은 스프링노트에서 작성되었습니다.

Character Functions (P3-7)

• TRIM : 첫번째 인자의 문자열에서, 두번째 인자의 문자열을 왼쪽 또는 오른쪽에서 찾아서 잘라냄(중간꺼는 패스~)
• LTRIM() : 왼쪽 끝에 대해서 수행
• RTRIM() : 오른쪽 끝에 대해서 수행

1. SQL> SELECT RTRIM('abcdeefeeeee', 'e') FROM dual;
2. RTRIM('
   -------
   abcdeef
3.  
4. SQL> SELECT LTRIM('eeabcdeefeeeee', 'e') FROM dual;
5. LTRIM('EEABC
   ------------
   abcdeefeeeee

Character Functions (P3-9)

• CHR() : 인자(아스키코드값)에 해당하는 문자 출력
• ASCII() : 인자(문자)에 해당되는 아스키코드값 출력

1. SQL> SELECT CHR(79) FROM dual;
2. C
   -
   O
3.  
4. SQL> SELECT ASCII('A') FROM dual;
5. ASCII('A')
   ----------
           65
6.  
7. SQL> SELECT ASCII('O') FROM dual;
8. ASCII('O')
   ----------
           79

• REPLACE() : 첫번째 인자의 문자열에서 두번째 문자열을 찾아서 찾은 것을 세번째 문자열로 바꾼다.

1. SQL> SELECT REPLACE('Oracle DB System', 'DB', 'Database') FROM dual;
2. REPLACE('ORACLEDBSYSTE
   ----------------------
   Oracle Database System

• SUBSTR() : 문자열 자르기 -> 1부터 인덱스 시작 -> 첫번째 인자의 문자열에서. 두번째 인자의 값을 인덱스로 시작으로 해서. 세번째 인자의 크기만큼 잘라내서 출력~

1. SQL> SELECT SUBSTR('Oracle DB System', 2, 4) FROM dual;
2. SUBS
   ----
   racl

1. // -5 : 끝에서 5번째부터 3개의 문자를 자름~
2. SQL> SELECT first_name, SUBSTR(first_name, -5, 3) FROM s_emp;
3. FIRST_NAME                SUBSTR
   ------------------------- ------
   Carmen                    arm
   LaDoris                   Dor
   Midori                    ido
   Mark
   Audry                     Aud
   Molly                     Mol
   Roberta                   ber
   Ben
   Antoinette                net
   Marta                     Mar
   Colin                     Col
   Henry                     Hen
   Yasmin                    asm
   Mai
   Andre                     And
   Elena                     Ele
   George                    eor
   Akira                     Aki
   Vikram                    ikr
   Chad
   Alexander                 and
   Eddie                     Edd
   Radha                     Rad
   Bela
   Sylvie                    ylv
4. 25 rows selected.

• TRANSLATE() : 첫번째 인자의 문자열을 대상으로 번역, 두번째 인자의 문자열을, 세번째 인자의 문자열로 바꿈 -> 전체 문자열이 아닌 2, 3번째 문자열의 각각의 문자를 맵핑~

1. A -> 1
2. B -> 2
3. C -> 3
4. D -> 4
5. SQL> SELECT TRANSLATE('Oracle DBMS', 'ABCD', '1234') FROM dual;
6. TRANSLATE('
   -----------
   Oracle 42MS

• INSTR() : 첫번째 문자열에서 두번째 문자열을 찾아서 인덱스(1부터 시작) 리턴~, 없으면 0 리턴~
        3번째 인자는 시작위치 지정 -> 검색 시작위치를 지정할 뿐 인덱스는 처음부터 시작
        네번째 인자는 몇번째에 해당되냐를 정함 -> 2로 지정은 검색 결과중 2번째에 해당되는 인덱스를 지정)

1. SQL> SELECT INSTR('Oracle DBMS', 'a') FROM dual;
2. INSTR('ORACLEDBMS','A')
   -----------------------
                         3

• LENGTH() : 문자열로 display되는 길이를 return, 날짜의 경우는 문자열로 바뀔때 해당 문자열의 길이

Date Functioins (P3-10)

• ADD_MONTHS() : 첫번째 인자의 날짜로부터 몇개월 후 또는 전

1. SQL> SELECT ADD_MONTHS(sysdate, 5), ADD_MONTHS(sysdate, -5) FROM dual;
2. ADD_MONT ADD_MONT
   -------- --------
   09/04/06 08/06/06

• LAST_DAY() : 인자로 지정된 날짜의 마지막 날

1. SQL> SELECT LAST_DAY('03/01/01'), LAST_DAY('03/02/01') FROM dual;
2. LAST_DAY LAST_DAY
   -------- --------
   03/01/31 03/02/28

• MONTHS_BETWEEN() : 첫번째와 두번째 날짜의 개월 차

1. SQL> SELECT MONTHS_BETWEEN('03/01/01', '03/07/01') FROM dual;
2. MONTHS_BETWEEN('03/01/01','03/07/01')
   -------------------------------------
                                      -6

• NEW_TIME() : 시간대 변경하는것

• NEXT_DAY() : 첫번째 인자(날짜) 기준으로 두번째 인자(요일)이 언제인지 알려줌(요일은 NLS_DATE_LANGUAGE에 따라 달라짐)

1. SQL> SELECT NEXT_DAY(sysdate, '일요일') FROM dual;
2. NEXT_DAY
   --------
   08/11/09

• ROUND() : YEAR, MONTH, DAY를 기준으로 반올림(TO_DATE() 함수를 써서 문자열형으로 된 날짜를 날짜형으로 변경)
• TRUNC() :

1. // 연도를 기준
2. SQL> SELECT ROUND(TO_DATE('03/07/16'), 'YEAR') FROM dual;
3. ROUND(TO
   --------
   04/01/01
4.  
5. // 월을 기준
6. SQL> SELECT ROUND(TO_DATE('03/07/16'), 'MONTH') FROM dual;
7. ROUND(TO
   --------
   03/08/01
8.  
9. // 요일을 기준
10. SQL> SELECT ROUND(TO_DATE('03/07/16'), 'DAY') FROM dual;
11. ROUND(TO
    --------
    03/07/13

Date Arithmetic Operations (P3-11)

• 날짜에 대한 연산
• 성이 Biri인 사원의 근무일수

1.  SQL> SELECT sysdate - start_date FROM s_emp WHERE last_name='Biri';
2. SYSDATE-START_DATE
   ------------------
           6788.61247

Conversion Functions (P3-12)

• TO_CHAR() : 문자열로 변환하는 함수 -> 인자 d=날짜, n=숫자 -> 포맷 문자를 알아야 함
• TO_DATE() : 문자열을 날짜 데이터로 변경 -> 포맷 : 해당 문자열을 어떻게 해석할지 알려줌, Y, M, D가 어디에 해당하는지 알려줌

Format Elements for Number (P3-14)

• TO_CHAR() : 숫자 or 날짜를 문자열로 변경해 줌, 포맷을 통해서 출력형식 결정

  • 첫번째 : 전체를 5자리로 잡고 자릿수 남으면 공백문자열 채움 -> 첫번째 결과인 3,000 앞에 공백문자열 있으니 LENGTH=5 (고정길이 문자열)
  • 두번째 : 전체를 6자리로 잡고 자룻시 남으면 0으로 채움
  • 세번째 : FM을 씀으로써 첫번째의 결과에서 공백문자열 대신 아예 비워둠 (가변길이 문자열)
  • 네번째 : 소수점 아래 1번째 자리 있던 없던 무조건 출력
    

1. SQL> SELECT TO_CHAR(salary, '99,999'), TO_CHAR(salary, '099,999'),
     2         TO_CHAR(salary, 'FM99,999'), TO_CHAR(salary, '99,999.0')
     3  FROM s_emp;
2. TO_CHAR TO_CHAR( TO_CHAR TO_CHAR(S
   ------- -------- ------- ---------
     3,000  003,000 3,000     3,000.0
     1,450  001,450 1,450     1,450.0
     1,400  001,400 1,400     1,400.0
     3,000  003,000 3,000     3,000.0
     3,000  003,000 3,000     3,000.0
     1,200  001,200 1,200     1,200.0
     1,250  001,250 1,250     1,250.0
     1,100  001,100 1,100     1,100.0
     1,300  001,300 1,300     1,300.0
     1,307  001,307 1,307     1,307.0
     1,400  001,400 1,400     1,400.0
     1,490  001,490 1,490     1,490.0
     1,515  001,515 1,515     1,515.0
     1,525  001,525 1,525     1,525.0
     1,450  001,450 1,450     1,450.0
     1,400  001,400 1,400     1,400.0
       940  000,940 940         940.0
     1,200  001,200 1,200     1,200.0
       795  000,795 795         795.0
       750  000,750 750         750.0
       850  000,850 850         850.0
       800  000,800 800         800.0
       795  000,795 795         795.0
       860  000,860 860         860.0
     1,100  001,100 1,100     1,100.0
3. 25 rows selected.

• 9, 0 : 자리수 표현
• $ : $표시
• L : Local Currency 표현 -> 날짜나 지역에 따라 달라지는 값 설정

1. SQL> SELECT value FROM nls_session_parameters
     2  WHERE parameter = 'NLS_CURRENCY';
2. VALUE
   --------------------------------------------------------------------------------
3. ￦
4. SQL> ALTER SESSION SET NLS_CURRENCY='\';
5. Session altered.
6.  
7. // '$' -> 그냥 $
8. // 'L' -> 현재 세션의 통화단위
9. SQL> SELECT TO_CHAR(salary, '$99,999'), TO_CHAR(salary, 'L99,999')
     2  FROM s_emp;
10. TO_CHAR( TO_CHAR(SALARY,'L
    -------- -----------------
      $3,000            \3,000
      $1,450            \1,450
      $1,400            \1,400
      $3,000            \3,000
      $3,000            \3,000
      $1,200            \1,200
      $1,250            \1,250
      $1,100            \1,100
      $1,300            \1,300
      $1,307            \1,307
      $1,400            \1,400
      $1,490            \1,490
      $1,515            \1,515
      $1,525            \1,525
      $1,450            \1,450
      $1,400            \1,400
        $940              \940
      $1,200            \1,200
        $795              \795
        $750              \750
        $850              \850
        $800              \800
        $795              \795
        $860              \860
      $1,100            \1,100
11. 25 rows selected.

• MI : 숫자가 음수면 끝에 '-'가 붙음
• PR : 숫자가 음수면 괄호가 붙음
• RN : 해당 숫자를 로마숫자로 변경 (RN을 대문자, 소문자 어떤걸 쓰냐에 따라서 결과 로마문자도 바뀜)
• S : 양수면 +, 음수면 -
• X : 16진수로 변경해서 출력 (X = 대문자, x = 소문자 -> 16진수중 A~F 값 표현시)

• TO_NUMBER() : 매개인자인 문자열을 숫자로 변경함, 숫자와 숫자앞에 +, -를 붙혀주면 별도의 포맷 없이 변환 가능

숫자외의 문자가 들어있는 경우에 포맷을 이용해서 숫자에 해당하는 부분을 표현해줘야함

Format Elements for Date (P3-15)

• 사용시기

  • TO_CHAR(날짜, 포맷) : 날짜가 어떤 형식인지 알려줌
  • TO_DATE(문자열, 포맷) : '문자열'을 'DATE'형식으로 바꿀때 '포맷' 형식으로 해석해서 변경해라~

• SS : 0~59 사이의 초 (1분을 초 단위로 나눈 것)
• SSSSS : 정오(0시)부터 현재까지 몇 초가 지났는지 알려줌
• MI : 0~59 사이의 분 (1시간을 분 단위로 나눈 것)
• HH : 0~12로 시간 표시
• H24 : 0~23으로 시간 표시
• AM, PM : 둘중 어떤 것을 쓰던간에 오전이면 AM, 오후이면 PM으로 표시됨.
• DD ; 1~31 사이의 월(한달)
• DAY : 요일(일요일, 월요일~ / SUNDAY~)
• DY : 요일의 약자(일, 욜~ / SUN~)
• D : 요일을 의미하는 숫자 (1~7)
• DDD : 1년중 오늘이 몇일인지 (1~366)

• 포맷 문자 : ' ~ ' 사이
• 일반 문자 : " ~ " 사이

• P3-15 예제

1. SQL> SELECT TO_CHAR(sysdate, 'HH24"시" MI"분" SS"초"') FROM dual;
2. TO_CHAR(SYSDAT
   --------------
   15시 28분 24초
3.  
4. // AM을 쓰던 PM을 쓰던 결과는 똑같음 (언어셋이 한글이라 '오후'로 출력됨~)
5. SQL> SELECT TO_CHAR(sysdate, 'HHAM'), TO_CHAR(sysdate, 'HHPM') FROM dual;
6. TO_CHA TO_CHA
   ------ ------
   03오후 03오후
7.  
8. // '15시 28분 24초'를 'HH24"시" MI"분" SS"초"'로 해석해서 TO_DATE 시킴~
9. // 현재 세션에서 날짜를 표시하는 방법 때문에 결과에서 '시, 분, 초' 가 안나오는 것~
10. SQL> SELECT TO_DATE('15시 28분 24초', 'HH24"시" MI"분" SS"초"') FROM dual;
11. TO_DATE(
    --------
    08/11/01

1. SQL> SELECT TO_CHAR(sysdate, 'DDD"일"') FROM dual;
2. TO_CH
   -----
   311일
3. 
   SQL> SELECT TO_CHAR(sysdate, 'DAY DY') FROM dual;
4. TO_CHAR(SYSDAT
   --------------
   목요일 목

Format Elements for Date (P3-16)

• W : 이번달의 몇번째 주 인지 알려줌 (1~5)
• WW : 올해의 몇번째 주 인지 알려줌 (1~53)
• MM : 월 (1~12)
• MON : 월의 약어(JAN, ~)
• MONTH : 월의 FullName(JANUARY, ~)
• Q : 분기 (1~4)
• RM : 월을 로마숫자로 표현 (I~XII)
• AD, BC : 기원전, 후
• Y, YY, YYY : 연도의 자릿수별 표현
• YYYY, SYYYY : 연도가 기원전인 경우 -가 붙음

1. // 1년중 45번째 주, 1주일중 5번째 일
2. SQL> SELECT TO_CHAR(sysdate, 'WW"주" D"일"') FROM dual;
3. TO_CHAR(
   --------
   45주 5일

1. SQL> SELECT TO_CHAR(start_date, 'BC YYYY Q MM DD') FROM s_emp;
2. TO_CHAR(START_DATE,
   -------------------
   서기 2090 1 03 03
   서기 1990 1 03 08
   서기 1991 2 06 17
   서기 1990 2 04 07
   서기 1990 1 03 04
   서기 1991 1 01 18
   서기 1990 2 05 14
   서기 1990 2 04 07
   서기 1992 1 02 09
   서기 1991 1 02 27
   서기 1990 2 05 14
   서기 1992 1 01 18
   서기 1991 1 02 18
   서기 1992 1 01 22
   서기 1991 4 10 09
   서기 1992 1 02 07
   서기 1990 1 03 08
   서기 1991 1 02 09
   서기 1991 3 08 06
   서기 1991 3 07 21
   서기 1991 2 05 26
   서기 1990 4 11 30
   서기 1990 4 10 17
   서기 1991 1 03 17
   서기 1991 2 05 09
3. 25 rows selected.
4.  
5. //BC -> AD, MM ->MON으로 변경후 출력~
6. SQL> SELECT TO_CHAR(start_date, 'AD YY Q MON DD') FROM s_emp;
7. TO_CHAR(START_DATE,'ADY
   -----------------------
   서기 90 1 3월  03
   서기 90 1 3월  08
   서기 91 2 6월  17
   서기 90 2 4월  07
   서기 90 1 3월  04
   서기 91 1 1월  18
   서기 90 2 5월  14
   서기 90 2 4월  07
   서기 92 1 2월  09
   서기 91 1 2월  27
   서기 90 2 5월  14
   서기 92 1 1월  18
   서기 91 1 2월  18
   서기 92 1 1월  22
   서기 91 4 10월 09
   서기 92 1 2월  07
   서기 90 1 3월  08
   서기 91 1 2월  09
   서기 91 3 8월  06
   서기 91 3 7월  21
   서기 91 2 5월  26
   서기 90 4 11월 30
   서기 90 4 10월 17
   서기 91 1 3월  17
   서기 91 2 5월  09

1. // 나머지는 올해의 현재 월로~
2. SQL> SELECT TO_DATE('03', 'YY') FROM dual;
3. TO_DATE(
   --------
   03/11/01
4.  
5. SQL> SELECT TO_DATE('03', 'MM') FROM dual;
6. TO_DATE(
   --------
   08/03/01
7.  
8. SQL> SELECT TO_DATE('03', 'DD') FROM dual;
9. TO_DATE(
   --------
   08/11/03

Format Elements for Date (P3-17)

• YEAR, SYEAR

1. SQL> SELECT TO_CHAR(sysdate, 'YEAR') FROM dual;
2. TO_CHAR(SYSDATE,'YEAR')
   ------------------------------------------
   TWO THOUSAND EIGHT
3. SQL> SELECT TO_CHAR(sysdate, 'SYEAR') FROM dual;
4. TO_CHAR(SYSDATE,'SYEAR')
   -------------------------------------------
    TWO THOUSAND EIGHT

• CC, SCC : 세기
• J : 줄리앙 데이트
• SP : 앞에 나오는 숫자를 스펠링으로 변경
• TH : 상수로 변경

1. // CC(세기)를 SP영문으로 표시
2. SQL> SELECT TO_CHAR(sysdate, 'CCSP QTH') FROM dual;
3. TO_CHAR(SYSDATE,'
   -----------------
   TWENTY-ONE 4TH

RR Format (P3-18)

• Y2K 문제를 해결하려고 나온 포맷
• 예전에는 연도를 2자리로 표시
• 시간이 지나면서 2000년을 00으로 표시하면 1900인지 2000인지 모름
• 00이 들어왔을때 포맷에 따라 1900년도인지 2000년도인지 구분함

Miscellaneous Functions (P3-19)

• GREATEST() : 매개인자로 몇개가 오던 그 중에 가장 큰것 반환
• LEAST() : 매개인자로 몇개가 오던 그 중에 가장 작은것 반환
• USER() :  로그인한 유저
• VSIZE() : 매개인자로 들어온 걸 저장하기 위해 몇바이트를 사용하는지 알려줌
• DECODE() : 매개인자로 몇개가 오던 상관 없음, 첫번째 매개변수를 대상으로 하고, 마지막은 기본값, 중간에서는 2개를 쌍으로 해서 2개의 쌍중 첫번째 값과, 첫번째 매개변수를 비교해서 맞는 것에 대해 2개의 쌍중 2번째 값을 리턴~

1. SQL> SELECT DECODE(dept_ID,
     2     10, salary*1.1,
     3     50, salary*1.2,
     4     salary)
     5  FROM s_emp;
2. DECODE(DEPT_ID,10,SALARY*1.1,50,SALARY*1.2,SALARY)
   --------------------------------------------------
                                                 3600
                                                 1450
                                                 1400
                                                 3300
                                                 3600
                                                 1200
                                                 1250
                                                 1100
                                                 1300
                                                 1307
                                                 1400
                                                 1490
                                                 1515
                                                 1525
                                                 1450
                                                 1400
                                                  940
                                                 1200
                                                  795
                                                  750
                                                  850
                                                  800
                                                  795
                                                  860
                                                 1100
3. 25 rows selected.

Miscellaneous Functions (P3-20)

• NVL() : 첫번째 매개변수가 null이면 두번째 매개변수를 리턴, null이 아니라면 첫번째 매개변수 리턴

1. // commission_pct 컬럼이 0인 경우 그냥 아무것도 출력 안됨
2. SQL> SELECT id, commission_pct FROM s_emp;
3.         ID COMMISSION_PCT
   ---------- --------------
            1
            2
            3
            4
            5
            6
            7
            8
            9
           10
           11             10
           12           12.5
           13             10
           14             15
           15           17.5
           16
           17
           18
           19
           20
           21
           22
           23
           24
           25
4. 25 rows selected.
5.  
6. // commission_pct 컬럼의 값이 null인 경우 0으로 출력~
7. SQL> SELECT id, NVL(commission_pct, 0) FROM s_emp;
8.         ID NVL(COMMISSION_PCT,0)
   ---------- ---------------------
            1                     0
            2                     0
            3                     0
            4                     0
            5                     0
            6                     0
            7                     0
            8                     0
            9                     0
           10                     0
           11                    10
           12                  12.5
           13                    10
           14                    15
           15                  17.5
           16                     0
           17                     0
           18                     0
           19                     0
           20                     0
           21                     0
           22                     0
           23                     0
           24                     0
           25                     0
9. 25 rows selected.

• NVL2() : 첫번째 매개변수가 null이 아니면 두번째 매개변수 리턴, null이면 세번째 매개변수 리턴~
• NULLIF() : 첫번째, 두번째 매개변수가 같으면 null 리턴, 그렇지 않으면 첫번째 리턴
• COALESCE() : 매개인자로 들어온 여러 컬럼의 값중 최초로 나온 null이 아닌 값을 리턴

Nesting Single-Row Functions (P3-21)

• Single-Row 함수는 함수안에 함수 쓰는 것이 가능함

이 글은 스프링노트에서 작성되었습니다.

Number Functions (P3-6)

• ABS() : 절대값
• CEIL() : 양수인 경우 무조건 올림, 매개인자로 들어온 값보다 같거나 크면서 제일 작은 정수
• FLOOR() : 무조건 내림, 매개인자로 들어온 값보다 작으면서 제일 큰 정수
• MOD() : 나머지
• POWER(m, n) :  m에 n승
• ROUND() : 반올림
• TRUNC() : 버림
• SIGN() : 부호

• 예제

1. SQL> SELECT salary, ROUND(salary, -3), TRUNC(salary, -3) FROM s_emp;
2.     SALARY ROUND(SALARY,-3) TRUNC(SALARY,-3)
   ---------- ---------------- ----------------
         3000             3000             3000
         1450             1000             1000
         1400             1000             1000
         3000             3000             3000
         3000             3000             3000
         1200             1000             1000
         1250             1000             1000
         1100             1000             1000
         1300             1000             1000
         1307             1000             1000
         1400             1000             1000
         1490             1000             1000
         1515             2000             1000
         1525             2000             1000
         1450             1000             1000
         1400             1000             1000
          940             1000                0
         1200             1000             1000
          795             1000                0
          750             1000                0
          850             1000                0
          800             1000                0
          795             1000                0
          860             1000                0
         1100             1000             1000
3. 25 rows selected.

• CEIL()

1. SQL> SELECT CEIL(5.6), CEIL(-5.6) FROM dual;
2.  CEIL(5.6) CEIL(-5.6)
   ---------- ----------
            6         -5

Character Functioins (P3-7)

• CONCAT() : 문자열 2개 붙혀서 반환
• INITCAP() : 각 단어의 첫번째 문자는 대문자로, 나머지는 소문자로
• LOWER() : 전부 소문자로
• UPPER() : 전부 대문자로
• LPAD() : 첫번째 매개변수의 문자열을 출력하는데, 두번째 매개변수의 값 만큼 공간을 잡고, 나머지는 세번째 매개변수의 값으로 채움(왼쪽부터)
• RPAD() : LPAD() + 오른쪽부터~

• LPAD() & RPAD()

1. SQL> SELECT LPAD('Oracle dbms', 13, 'x') FROM dual;
2. LPAD('ORACLED
   -------------
   xxOracle dbms
3.  
4. SQL> SELECT RPAD('Oracle dbms', 13, 'x') FROM dual;
5. RPAD('ORACLED
   -------------
   Oracle dbmsxx

이 글은 스프링노트에서 작성되었습니다.

 Types of View (P12-7)

• Simple View

기반 테이블이 하나

함수 사용안함

그룹화 안함

Update, Insert, Delete 가능

• Complex View

기반 테이블이 여러개

함수 사용

그룹화 함

Update, Insert, Delete 불가능

• 부서번호가 10인 부서의 부서이름, 부서번호를 보는 뷰 생성

1. // Simple View
2. SQL> CREATE OR REPLACE VIEW dept10
     2  AS
     3  SELECT deptno, dname
     4  FROM dept
     5  WHERE deptno=10;
3. View created.
4.  
5. SQL> SELECT * FROm dept10;
6.     DEPTNO DNAME
   ---------- --------------
           10 ACCOUNTING
7.  
8. // Simple View라서 Update 가능
9. SQL> UPDATE dept10
     2  SET dname = 'accounting'
     3  WHERE deptno=10;
10. 1 row updated.

• 부서번호별로 평균급여를 보는 뷰 생성

1. // Complex View
2. SQL> CREATE OR REPLACE VIEW avgsal
     2  AS
     3  SELECT deptno, avg(sal) as dasl
     4  FROM emp
     5  GROUP BY deptno;
3. View created.
4.  
5. SQL> SELECT * FROM avgsal;
6.     DEPTNO       DASL
   ---------- ----------
           10       5000
           20       5000
           30       5000
7.  
8. // Complex View라서 DELETE 문 안먹힘
9. SQL> DELETE FROM avgsal WHERE deptno=10;
   DELETE FROM avgsal WHERE deptno=10
               *
   ERROR at line 1:
   ORA-01732: data manipulation operation not legal on this view

• View를 통해서 INSERT 한경우 베이스테이블에 적용은 되지만 View를 통해서는 볼수 없는 경우 (P12-5 WITH CHECK OPTION 으로 해결~

1. // View를 통해서 Insert
2. SQL> INSERT INTO dept10
     2  VALUES (90, 'development');
3. 1 row created.
4.  
5. // 실제 Base Table에는 Insert 되있음
6. SQL> SELECT * FROM dept;
7.     DEPTNO DNAME          COMMENTS                       LOC
   ---------- -------------- ------------------------------ --------------------
           10 accounting
           20 RESEARCH
           30 SALES
           40 OPERATIONS
           50 Local Branch
           90 development
8. 6 rows selected.
9.  
10. SQL> CREATE OR REPLACE VIEW dept10
      2  AS
      3  SELECT deptno, dname
      4  FROM dept
      5  WHERE deptno=10;
11. View created.
12.  
13. // view로 조회해보면 추가한 내용 안보임(view 생성 조건이 deptno가 10인 경우뿐이므로 의미 없음~)
14. SQL> SELECT * FROM dept10;
15.     DEPTNO DNAME
    ---------- --------------
            10 accounting

P12-8 예제

1. SQL> conn test00/test00
   Connected.
   
2.  
3. SQL> CREATE OR REPLACE VIEW detp_emp (did, dname, eid, ename)
     2  AS SELECT d.id, d.name, e.id, e.last_name
     3  FROM s_dept d, s_emp e
     4  WHERE d.id = e.dept_id;
4. View created.
5.  
6. SQL> SELECT * FROM detp_emp;
7.        DID DNAME                            EID ENAME
   ---------- ------------------------- ---------- -------------------------
           50 Administration                     1 Velasquez
           41 Operations                         2 Ngao
           31 Sales                              3 Nagayama
           10 Finance                            4 Quick-To-See
           50 Administration                     5 Ropeburn
           41 Operations                         6 Urguhart
           42 Operations                         7 Menchu
           43 Operations                         8 Biri
           44 Operations                         9 Catchpole
           45 Operations                        10 Havel
           31 Sales                             11 Magee
           32 Sales                             12 Giljum
           33 Sales                             13 Sedeghi
           34 Sales                             14 Nguyen
           35 Sales                             15 Dumas
           41 Operations                        16 Maduro
           41 Operations                        17 Smith
           42 Operations                        18 Nozaki
           42 Operations                        19 Patel
           43 Operations                        20 Newman
           43 Operations                        21 Markarian
           44 Operations                        22 Chang
           34 Sales                             23 Patel
           45 Operations                        24 Dancs
           45 Operations                        25 Schwartz
8. 25 rows selected.
9.  
10. SQL> COL column_name FORMAT A20
    
11.  
12. SQL> SELECT column_name, updatable, insertable, deletable
      2  FROM user_updatable_columns
      3  WHERE table_name = 'DEPT_EMP';
13. COLUMN_NAME          UPD INS DEL
    -------------------- --- --- ---
    DID                  NO  NO  NO
    DNAME                NO  NO  NO
    EID                  YES YES YES
    ENAME                YES YES YES

•  부서별 최고급여 받는 사원 이름(Inline View로~, 기존에는 Select문 3개 사용했음)

1.  SQL> SELECT e.deptno, e.empno, e.ename, e.sal
     2  FROM emp e, (SELECT deptno, max(sal) msal FROM emp GROUP BY deptno) dmax
     3  WHERE e.deptno = dmax.deptno
     4  AND e.sal = dmax.msal;
2.     DEPTNO      EMPNO ENAME             SAL
   ---------- ---------- ---------- ----------
           20       7369 SMITH            5000
           30       7499 ALLEN            5000
           30       7521 WARD             5000
           20       7566 JONES            5000
           30       7654 MARTIN           5000
           30       7698 BLAKE            5000
           10       7782 CLARK            5000
           20       7788 SCOTT            5000
           10       7839 KING             5000
           30       7844 TURNER           5000
           20       7876 ADAMS            5000
           30       7900 JAMES            5000
           20       7902 FORD             5000
           10       7934 MILLER           5000
3. 14 rows selected.
4.  

INDEX (P12-14)

• 테이블 스캔 : SELECT로 검색시 인덱스 없이 처음부터 한개씩 비교하는 것 -> 속도 오래 걸림 -> Index로 해결
• Index는 data에 대한 조회 속도를 높이기 위해 사용
• Index를 위한 저장공간이 필요
• 즉, 데이터 검색 성능은 증가하지만 저장공간이 필요함
• 디스크 I/O과  줄어듬
• Index를 많이 만들수록 검색속도는 빨라지지만 Update, Delete 등의 갱신속도는 떨어짐 (데이터가 변경되면 인덱스도 함께 변경되야함)
• 테이블의 사용용도에 맞게 적절히 사용해야한다.

Types of Index (P12-15)

• Unique <-> Nonunique : 데이터에 중복된 데이터가 있다면 non~
• Automatic created <-> User create : UNIQUE, PRIMARY KEY 제약조건을 주면 자동으로 인덱스 생성됨(Automatic created)

CREATE INDEX (P12-16)

• SELECT * FROM emp WHERE sal*1.1 > 1000 : sal이 인덱스가 지정되있다고 해도 수행안됨
• SELECT * FROM emp WHERE sal > 1000 / 1.1  : 이렇게 해주면 가능, 자주 쓰인다면 해당 내용을 함수로~

Dictionary for Indexes (P12-18)

1. SQL> SELECT t.index_name, t.uniqueness, c.column_name, c.column_position
     2  FROM user_indexes t, user_ind_columns c
     3  WHERE c.index_name = t.index_name
     4  AND t.table_name = 'EMP';
2. INDEX_NAME                     UNIQUENES COLUMN_NAME          COLUMN_POSITION
   ------------------------------ --------- -------------------- ---------------
   EMP_ENAME_IND                  NONUNIQUE ENAME                              1
   PK_EMP                         UNIQUE    EMPNO                              1

Needs for the Index (P12-19)

• WHERE절에서 Join 조건에 많이 사용되는 컬럼의 경우
• 데이터가 많은 테이블에서 적은 양의 데이터를 찾는 경우
• 값이 넒은 범위를 가진 컬럼(반대 = 남/여 같은 경우)
• 자주 갱신되지 않는 테이블의 경우

Sequence (P12-20)

• 일련번호 생성에 사용
• Unique한 Integer 사용함
• 게시판의 글번호 등에 사용

Using Sequence (P12-22)

1. SQL> CREATE SEQUENCE seq_dept_no;
2. Sequence created.
3.  
4. SQL> SELECT seq_dept_no.nextval from dual;
5.    NEXTVAL
   ----------
            1
6.  
7. SQL> SELECT seq_dept_no.currval from dual;
8.    CURRVAL
   ----------
            1

• 1번부터 시작해서 2씩 증가하는 Sequence(롤백해도 취소 안됨~, insert후 롤백하면 insert 한 데이터는 롤백되겠지만 sequence 넘버는 그대로~)

1. SQL> CREATE SEQUENCE seq_dept_no
     2  START WITH 1
     3  INCREMENT BY 2;
2. Sequence created.

CREATE/DROP SYNONYM (P12-27)

1.  SQL> conn system/system
   Connected.
   SQL>
   SQL> CREATE SYNONYM emp for scott.emp;
2. Synonym created.
3. SQL> SELECT * FROM emp;
4.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
   ---------- ---------- --------- ---------- -------- ---------- ----------
       DEPTNO
   ----------
         7369 SMITH      CLERK           7902 80/12/17       5000
           20
5.       7499 ALLEN      SALESMAN        7698 81/02/20       5000        300
           30
6.       7521 WARD       SALESMAN        7698 81/02/22       5000        500
           30
7.       7566 JONES      MANAGER         7839 81/04/02       5000
           20
8.       7654 MARTIN     SALESMAN        7698 81/09/28       5000       1400
           30
9.       7698 BLAKE      MANAGER         7839 81/05/01       5000
           30
10.       7782 CLARK      MANAGER         7839 81/06/09       5000
            10
11.       7788 SCOTT      ANALYST         7566 87/04/19       5000
            20
12.       7839 KING       PRESIDENT            81/11/17       5000
            10
13.       7844 TURNER     SALESMAN        7698 81/09/08       5000          0
            30
14.       7876 ADAMS      CLERK           7788 87/05/23       5000
            20
15.       7900 JAMES      CLERK           7698 81/12/03       5000
            30
16.       7902 FORD       ANALYST         7566 81/12/03       5000
            20
17.       7934 MILLER     CLERK           7782 82/01/23       5000
            10
18. 
    14 rows selected.

이 글은 스프링노트에서 작성되었습니다.

Usages of View (P12-3)

• select, update 등은 로그인한 계정이 해당 권한을 가지고 있으므로 가능한 것.
• 컬럼 등 데이터 내용에만 권한을 줄 수 있음.
• emp20이라는 view를 만들고 deptno가 20인 사용자들에게만 권한을 줌 -> 전체 emp에 대한 권한은 없으므로 전체에 대한 조회는 안되고 deptno가 20인 사람은 emp20만 조회가능(보안 문제~) 

CREATE VIEW (P12-4)

• FORCE & NO FORCE
• NO FORCE(기본값) : VIEW를 만들 때 AS 다음의 SELECT문에 문제가 없어야 함(emp테이블을 사용해서 emp20을 view로 만들려고 하는데 emp테이블이 없다면 에러~)
• FORCE : VIEW를 먼저 만들고 BaseTable를 나중에 만들 때 사용(emp 테이블이 없어도 그것을 사용해서 만드는 emp20 view 생성가능)

1. // 기본값 : NO FORCE
2. SQL> CREATE OR REPLACE VIEW test
     2  AS
     3  SELECT empno FROM ttttt;
   SELECT empno FROM ttttt
                     *
   ERROR at line 3:
   ORA-00942: table or view does not exist
3. 
   
4. // FORCE로 설정 : 경고는 뜨지만 VIEW 생성 됨 (ttttt 테이블은 나중에 생성하면 됨~)
5. SQL> CREATE OR REPLACE FORCE VIEW test
     2  AS
     3  SELECT empno FROM ttttt;
6. Warning: View created with compilation errors.

CREATE VIEW (P12-6)

1. SQL> CREATE VIEW emp20
     2  AS SELECT empno, ename, sal
     3  FROM emp WHERE deptno=20;
2. View created.
3.  
4. SQL> DESC emp20;
    Name                                      Null?    Type
    ----------------------------------------- -------- -------------
5.  EMPNO                                     NOT NULL NUMBER(4)
    ENAME                                              VARCHAR2(10)
    SAL                                                NUMBER(7,2)
6.  
7. // emp20 이라고 쓴 부분에는 VIEW의 내용이 복사되어서 들어옴(CREATE VIEW에서 'SELECT empno, ename, sal FROM emp WHERE deptno=20;' 부분)
8. // emp20 이라는 테이블이 따로 생성되고 그 테이블에서 결과를 가져오는 것이 아님(일종의 서브쿼리 처럼~)
9. SQL> SELECT * FROM emp20;
10.      EMPNO ENAME               SAL
    ---------- ---------- ------------
          7369 SMITH            $5,000
          7566 JONES            $5,000
          7788 SCOTT            $5,000
          7876 ADAMS            $5,000
          7902 FORD             $5,000

• CREATE VIEW = VIEW 새로 만들 때 사용
• CREATE OR REPLACE VIEW = VIEW 새로 만들거나 변경할 때 사용(ALTER 처럼~)

1. // () 안의 내용은 SELECT 다음의 컬럼명으로 각각 맵핑
2. // 생략시 그냥 컬럼명 사용~
3. SQL> CREATE OR REPLACE VIEW emp20(eno, name, payroll)
     2  AS SELECT empno, ename, sal
     3  FROM emp WHERE deptno=20;
4. View created.
5.  
6. SQL> SELECT * FROM emp20;
7.        ENO NAME          PAYROLL   // 위 쿼리문에서 () 안의 내용으로 출력됨~
   ---------- ---------- ----------
         7369 SMITH            5000
         7566 JONES            5000
         7788 SCOTT            5000
         7876 ADAMS            5000
         7902 FORD             5000

• 사원이름과 부서이름을 볼 수 있는 VIEW
• 자주 쓰이는 복잡한 질의문을 VIEW로 만든 후 간단하게 사용~

1. SQL> CREATE OR REPLACE VIEW empdept
     2  AS
     3  SELECT e.ename as ename, d.dname as dname
     4  FROM emp e, dept d
     5  WHERE e.deptno = d.deptno;
2. View created.
3.  
4. SQL> SELECT * FROM empdept;
5. ENAME      DNAME
   ---------- --------------
   SMITH      RESEARCH
   ALLEN      SALES
   WARD       SALES
   JONES      RESEARCH
   MARTIN     SALES
   BLAKE      SALES
   CLARK      ACCOUNTING
   SCOTT      RESEARCH
   KING       ACCOUNTING
   TURNER     SALES
   ADAMS      RESEARCH
6. ENAME      DNAME
   ---------- --------------
   JAMES      SALES
   FORD       RESEARCH
   MILLER     ACCOUNTING
7. 14 rows selected.

• VIEW에서 BASE TABLE = VIEW를 만드는데 사용한 테이블 -> empdept에서 emp, dept 테이블이 BASE TABLE

이 글은 스프링노트에서 작성되었습니다.

Alter Tables (P11-2)

• 테이블의 구조를 변경할 때 사용
• 추가(ADD), 수정(MODIFY), 삭제(DROP)할 수 있음
• DB 운영중에 삭제를 하면 문제가 생길 수 있다. (시간 등) -> UNUSED 사용해서 없는 것처럼 해놓는다~

Add Columns (P11-4)

• 테이블에 데이터가 하나라도 있는 상태에서는 'NOT NULL' 제약조건을 사용할 수 없다.
• 기존 테이블에 있는 데이터(레코드)에도 새로운 칼럼이 추가되는데 이때 기존 레코드의 새로추가된 컬럼에는 초기값으로 null이 들어가므로 문제가 됨~(NOT NULL 제약조건을 쓴다면~)

Unused Columns (P11-7)

• Unused 시킨 컬럼은 다시 복구할 수 없다.(DROP 시킨거라고 생각하면 됨)
• Unused 컬럼 확인 방법

1. SQL> DESC user_unused_col_tabs;
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
2.  TABLE_NAME                                NOT NULL VARCHAR2(30)
    COUNT                                              NUMBER

• Unused 컬럼의 데이터는 있어도 확인방법이 없고, 같은 이름의 컬럼은 새로 만들 수 있음.

1. // loc 컬럼 unused 시킴
2. SQL> ALTER TABLE dept
     2  SET UNUSED(loc);
3.  
4. Table altered.
5.  
6. // loc 컬럼 출력안됨.
7. SQL> SELECT * FROM dept;
8.     DEPTNO DNAME          COMMENTS
   ---------- -------------- ------------------------------
           10 ACCOUNTING
           20 RESEARCH
           30 SALES
           40 OPERATIONS
           50 Local Branch
9.  
10. // unused된 loc컬럼명으로 add
11. SQL> ALTER TABLE dept
      2  ADD(loc varchar2(20));
12. Table altered.
13.  
14. // loc 컬럼 출력됨~
15. SQL> SELECT * FROM dept;
16.     DEPTNO DNAME          COMMENTS                       LOC
    ---------- -------------- ------------------------------ --------------------
            10 ACCOUNTING
            20 RESEARCH
            30 SALES
            40 OPERATIONS
            50 Local Branch

Add Constraints (11-9)

• NOT NULL 조건은 제외~
• 기본키 제약조건이랑 유니크 제약조건은 자동으로 인덱스가 생성됨.

1. SQL> ALTER TABLE emp
     2  ADD CONSTRAINT emp_mgr_fk
     3     FOREIGN KEY (mgr)
     4     REFERENCES EMP(empno);
2. Table altered.
3.  
4. SQL> SELECT CONSTRAINT_NAME
     2  FROM user_constraints
     3  WHERE table_name='EMP';
5. CONSTRAINT_NAME
   ------------------------------
   EMP_MGR_FK   // 새로 추가한 제약조건
   PK_EMP
   FK_DEPTNO
6.  
7. SQL>  SELECT CONSTRAINT_NAME, COLUMN_NAME
     2   FROM user_cons_columns
     3   WHERE TABLE_NAME = 'EMP';
8. CONSTRAINT_NAME
   ------------------------------
   COLUMN_NAME
   --------------------------------------------------------------------------------
9. FK_DEPTNO
   DEPTNO
10. PK_EMP
    EMPNO
11. EMP_MGR_FK
    MGR

Drop Constraints (P11-10)

• PK-FK 제약조건이 걸린 것은 그냥 삭제가 안되고, CASCADE를 통해서 삭제해야 된다.
• 이때 삭제된 PK에 해당되는 FK도 같이 삭제됨

1. // DEPT.DEPTNO는 그냥 삭제 안됨~
2. SQL> ALTER TABLE dept
     2  DROP PRIMARY KEY;
   ALTER TABLE dept
   *
   ERROR at line 1:
   ORA-02273: this unique/primary key is referenced by some foreign keys
3.  
4. // FK_DEPTNO -> DEPT.DEPTNO의 기본키를 참조하고 있음(삭제안되는 이유)
5. SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
     2  FROM user_constraints
     3  WHERE TABLE_NAME = 'EMP';
6.  
7. // P = 기본키 제약조건
8. // R = 외래키 제약조건
9. CONSTRAINT_NAME                C
   ------------------------------ -
   EMP_MGR_FK                     R
   PK_EMP                         P
   FK_DEPTNO                      R
10.  
11. // CASCADE를 이용해서 삭제함
12. SQL> ALTER TABLE dept
      2  DROP PRIMARY KEY CASCADE;
13. Table altered.
14.  
15. // FK_DEPTNO까지 같이 삭제됨
16. SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
      2  FROM user_constraints
      3  WHERE TABLE_NAME = 'EMP';
17. CONSTRAINT_NAME                C
    ------------------------------ -
    EMP_MGR_FK                     R
    PK_EMP                         P
18.  

Enable Constraints (P11-11)

• 테이블 복사시 많이 사용
• 기존 데이터 -> 새로 복사시 : 제약조건 확인하면서 복사함
• 기존 데이터는 정상적으로 작동되던 것이기 때문에 제약조건에 문제없음
• 잠시 Disable 시켜놓고 복사하면 제약조건 확인을 안하기 때문에 속도가 빠름
• Enable : Disable 되있는 제약조건을 다시 활성화 시킴 (데이터 상태가 만족되어야 다시 enable 가능 -> PK쪽을 disable 시킨후 데이터 추가후 다시 enable 시키려고 했는데 PK 조건이 안맞으면 에러~)

 P11-12 예제

1. // alter2 테이블 별다른 제약조건 없이 생성
2. SQL> CREATE TABLE alter2(a NUMBER, b CHAR, c DATE);
3. Table created.
4.  
5. // 데이터 2개 삽입 (a컬럼의 값이 중복되서 들어가는 것에 주의~)
6. SQL> INSERT INTO alter2 VALUES(10, 'x', sysdate);
7. 1 row created.
8. SQL> INSERT INTO alter2 VALUES(10, 'y', sysdate);
9. 1 row created.
10.  
11. // a컬럼에 PK제약조건을 추가하려고 했는데 현재 테이블내 데이터 중 a값이 중복되서 오류~(10이 중복~)
12. SQL> ALTER TABLE alter2 ADD CONSTRAINT alter2_a_pk PRIMARY KEY(a);
    ALTER TABLE alter2 ADD CONSTRAINT alter2_a_pk PRIMARY KEY(a)
                                      *
    ERROR at line 1:
    ORA-02437: cannot validate (SCOTT.ALTER2_A_PK) - primary key violated
13. 
    
14. // 중복된 것 확인~
15. SQL> SELECT * FROM alter2 ORDER BY a;
16.          A B C
    ---------- - --------
            10 x 08/11/04
            10 y 08/11/04
17.  
18. // 중복된 데이터 중 한개 제거
19. SQL> DELETE FROM alter2 WHERE a=10 AND b='y';
20. 1 row deleted.
21.  
22. // 중복데이터 제거후 PK 제약조건 추가~
23. SQL> ALTER TABLE alter2 ADD CONSTRAINT alter2_a_pk PRIMARY KEY (a);
24. Table altered.
25.  
26. // PK 제약조건이 걸려있으므로 중복데이터 추가 불가~
27. SQL> INSERT INTO alter2 VALUES(10, 'y' , sysdate);
    INSERT INTO alter2 VALUES(10, 'y' , sysdate)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.ALTER2_A_PK) violated
28. 
    
29. // PK 제약조건 비활성화(Disable)
    SQL> ALTER TABLE alter2 DISABLE CONSTRAINT alter2_a_pk;
30. Table altered.
31.  
32. // PK 제약조건이 비활성화 되있으므로 중복데이터 추가 가능~
33. SQL> INSERT INTO alter2 VALUES(10, 'y', sysdate);
34. 1 row created.
35. SQL> /
36. 1 row created.
37.  
38. // 현제 테이블에 중복데이터가 있으므로 PK제약조건 활성화(Enable) 불가
39. SQL> ALTER TABLE alter2 ENABLE CONSTRAINT alter2_a_pk;
    ALTER TABLE alter2 ENABLE CONSTRAINT alter2_a_pk
    *
    ERROR at line 1:
    ORA-02437: cannot validate (SCOTT.ALTER2_A_PK) - primary key violated
40.  
41. // 중복데이터 다시 제거~
    SQL> DELETE FROM alter2 WHERE a=10 AND b='y';
42. 2 rows deleted.
43.  
44. // 중복데이터 제거후 제약조건 Enable 가능~
45. SQL> ALTER TABLE alter2 ENABLE CONSTRAINT alter2_a_pk;
46. Table altered.

Drop Table (P11-13)

• 다른 테이블과의 제약조건(PK-FK)이 있으면 삭제안됨 (다른 테이블에서 현재 삭제할 테이블의 컬럼을 참조하고 있는 경우)
• 'CASCADE CONSTRAINT' 사용해서 삭제해야함

1. // test00계정
2. SQL> DROP TABLE 교수;
   drop table 교수
              *
   ERROR at line 1:
   ORA-02449: unique/primary keys in table referenced by foreign keys
3. 
   SQL> DROP TABLE 교수 CASCADE CONSTRAINTS;
4. Table dropped.

Rename (P11-15)

• 테이블명 변경

1. SQL> RENAME emp to employees;
2. Table renamed.
3.  
4. SQL> SELECT * FROM EMP;
   SELECT * FROM EMP
                 *
   ERROR at line 1:
   ORA-00942: table or view does not exist
5. 
   
6. // DDL 문이기 때문에 롤백이 안되므로 다시 바꾼것~
7. SQL> RENAME employees to emp;
8. Table renamed.

• 컬럼명 변경

1. SQL> ALTER TABLE emp
     2  RENAME COLUMN sal TO salary;
2. Table altered.
3.  
4. SQL> SELECT * FROM salary;
   SELECT * FROM salary
                 *
   ERROR at line 1:
   ORA-00942: table or view does not exist

Truncate (P11-17)

• DROP TABLE emp -> 데이터 모두 삭제, emp table 정의 삭제
• Delete from emp -> rollback 가능 -> rollback 정보 백업하느라 속도 느림
• Truncate table emp -> 자동커밍 -> rollback 가능 -> 속도빠름
• P11-18 예제

1. SQL> SELECT count(*) FROM alter2;
2.   COUNT(*)
   ----------
            1
3.  
4. SQL> DELETE FROM alter2;
5. 1 row deleted.
6. SQL> SELECT count(*) FROM alter2;
7.   COUNT(*)
   ----------
            0
8.  
9. SQL> ROLLBACK;
10. Rollback complete.
11.  
12. SQL> SELECT count(*) FROM alter2;
13.   COUNT(*)
    ----------
             1
14. SQL>
    SQL>
    SQL> TRUNCATE TABLE alter2;
15. Table truncated.
16.  
17. SQL> SELECT count(*) FROM alter2;
18.   COUNT(*)
    ----------
             0
19.  
20. SQL> ROLLBACK;
21. Rollback complete.
22.  
23. SQL> SELECT count(*) FROM alter2;
24.   COUNT(*)
    ----------
             0

Comment (P11-19)

• Comment를 달면 딕셔너리에서 확인가능~

1. SQL> comment on table emp is 'This table is for employee';
2. Comment created.
3.  
4. SQL> SELECT comments FROM user_tab_comments
     2  WHERE table_name = 'EMP';
5. COMMENTS
   -------------------------------------------
6. This table is for employee

• FORMAT

1. // JOB 컬럼을 30자로 출력~
2. SQL> COL JOB FORMAT a30
   SQL> select job from emp;
3. JOB
   ------------------------------
   CLERK
   SALESMAN
   SALESMAN
   MANAGER
   SALESMAN
   MANAGER
   MANAGER
   ANALYST
   PRESIDENT
   SALESMAN
   CLERK
4. JOB
   ------------------------------
   CLERK
   ANALYST
   CLERK
5. 14 rows selected.
6.  
7.  
8. // '0'갯수 기준으로 남은 공백을 0으로 채움
   SQL> COL sal FORMAT 000000
   SQL> SELECT sal FROM emp;
9.     SAL
   -------
    005000
    005000
    005000
    005000
    005000
    005000
    005000
    005000
    005000
    005000
    005000
10.     SAL
    -------
     005000
     005000
     005000
11. 14 rows selected.
12.  
13.  
14. // '9' 갯수 기준으로 남은 것을 공백으로 채움
15. SQL> COL sal FORMAT 999999
    SQL> SELECT sal FROM emp;
16.     SAL
    -------
       5000
       5000
       5000
       5000
       5000
       5000
       5000
       5000
       5000
       5000
       5000
17.     SAL
    -------
       5000
       5000
       5000
18. 14 rows selected.
19.  
20. // '$' 랑 ',' 형식 맞춰서~
21. SQL> COL sal FORMAT $99,999,999
    SQL> /
22.          SAL
    ------------
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
23.          SAL
    ------------
          $5,000
          $5,000
          $5,000
24. 14 rows selected.
25.  

이 글은 스프링노트에서 작성되었습니다.

Add Columns (P11-4)

• 테이블안에 레코드가 있는 상태에서 컬럼을 추가하는 경우에는 'NOT NULL' 제약조건을 추가할 수 없음(새로운 컬럼 추가시 기존 데이터는 NULL로 되기 때문에)
• Add Columns 예제

1. SQL> ALTER TABLE dept
     2  ADD (comments VARCHAR2(3));
2. Table altered.
3.  
4. SQL> DESC dept
    Name                                      Null?    Type
    ----------------------------------------- -------- -------------------
5.  DEPTNO                                    NOT NULL NUMBER(2)
    DNAME                                              VARCHAR2(14)
    LOC                                                VARCHAR2(13)
    COMMENTS                                           VARCHAR2(3)
6.  
7. SQL> SELECT * FROM dept;
8.     DEPTNO DNAME          LOC           COM
   ---------- -------------- ------------- ---
           10 ACCOUNTING     NEW YORK
           20 RESEARCH       DALLAS
           30 SALES          CHICAGO
           40 OPERATIONS     BOSTON
           50 Local Branck   Korea

Modify Columns (P11-5)

• 데이터 크기의 증가는 문제 없지만, 데이터 타입의 변경 또는 크기를 줄이는 등의 경우는 해당 항목의 모든 레코드가 NULL일 경우에만 가능하다.
• Modify Columns 예제

1. // 크기를 늘리는 경우는 문제 안됨
2. SQL> ALTER TABLE dept
     2  MODIFY (comments VARCHAR2(255));
3. Table altered.
4.  
5. // 크기를 줄이는 경우는 컬럼 상태에 따라 달라짐
6. // 이 예제의 경우 comments 컬럼의 모든 데이터가 null이므로 가능
7. SQL> ALTER TABLE dept
     2  MODIFY (comments VARCHAR2(100));
8. Table altered.

Drop Columns (P11-6)

• 삭제보다는 SET UNUSED 사용권장
• Drop Columns 예제

1.  SQL> ALTER TABLE dept
     2  DROP (comments);
2. Table altered.

Unused Columns (P11-7)

• Unused Columns 한 데이터는 다시 되돌릴 수 없음
• 삭제할 내용인데 시간상의 문제 등으로 임시적으로 사용못하게 한것이므로 나중에 삭제함
• Unused Columns 예제

1. SQL> DESC dept
    Name                                      Null?    Type
    ----------------------------------------- -------- -------------
2.  DEPTNO                                    NOT NULL NUMBER(2)
    DNAME                                              VARCHAR2(14)
    LOC                                                VARCHAR2(13)
    COMMENTS                                           VARCHAR2(100)
3.  
4. SQL> ALTER TABLE dept
     2  SET UNUSED(comments);
5. Table altered.
6.  
7. // SET UNUSED() 사용후 컬럼 안보임
8. SQL> DESC dept
    Name                                      Null?    Type
    ----------------------------------------- -------- -------------
9.  DEPTNO                                    NOT NULL NUMBER(2)
    DNAME                                              VARCHAR2(14)
    LOC                                                VARCHAR2(13)

이 글은 스프링노트에서 작성되었습니다.

Transaction (P10-2)

• 연관되는 연속적인 작업이 있을 때 모든 작업이 끝나던지, 중간에 문제가 생긴다면 완전 처음으로 되돌아가도록 해야함 -> 트랜잭션으로 묶으면 DB에서 알아서 해줌~
• Oracle에서는 SQLPLUS를 시작하면 트랜잭션이 자동으로 시작됨
• COMMIT, ROLLBACK을 사용하면 명시적으로 트랜잭션이 종료됨

COMMIT : 트랜잭션 시작 이후에 모든 정보 DB에 저장

ROLLBACK : 트랜잭션 시작 이후에 모든 정보 취소

• SQLPLUS에서 EXIT로 종료를 하면 COMMIT을 하고 종료되고 그냥 프로그램을 종료하면 ROLLBACK을 하고 종료된다.
• DML은 자동으로 트랙잭션 시작, DDL, DCL은 자동 COMMIT로서 ROLLBACK 안됨.

• COMMIT & ROLLBACK 예제

1. SQL> DELETE FROM emp;
2. 15 rows deleted.
3.  
4. SQL> DELETE FROM dept;
5. 6 rows deleted.
6.  
7. SQL> ROLLBACK;
8. Rollback complete.
9.  
10. SQL> SELECT count(*) FROM emp;
11.   COUNT(*)
    ----------
            15
12.  
13. SQL> SELECT count(*) FROM dept;
14.   COUNT(*)
    ----------
             6

• DDL 사용으로 인한 COMMIT

1. SQL> SELECT * FROM dept;
2.     DEPTNO DNAME          LOC
   ---------- -------------- -------------
           10 ACCOUNTING     NEW YORK
           20 RESEARCH       DALLAS
           30 SALES          CHICAGO
           40 OPERATIONS     BOSTON
           50 Local Branck   Korea
           60 HQs
3. 6 rows selected.
4.  
5. SQL> DELETE FROM dept
     2  WHERE deptno=60;
6. 1 row deleted.
7.  
8. // CREATE 문을 사용해서 이 시점에서 COMMIT이 일어남
9. SQL> CREATE TABLE t (id number);
10. Table created.
11.  
12. SQL> ROLLBACK;
13. Rollback complete.
14.  
15. // ROLLBACK을 했지만 CREATE에서 COMMIT이 일어나서 ROLLBACK이 안됨
16. SQL> SELECT * FROM dept;
17.     DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
            50 Local Branck   Korea

Tracsaction Control Statements (P10-5)

• SAVEPOINT x : x 위치에 롤백 시점 지정
• ROLLBACK TO SAVEPOINT x : x위치까지만 롤백 시킴, 트랜잭션이 종료되지는 않고 트랙잭션 시작위치는 그대로고 계속 진행됨.

• P10-6 예제

1. SQL> SELECT deptno FROM emp WHERE empno=7788;
2.     DEPTNO
   ----------
           20
3.  
4. SQL> UPDATE emp SET deptno=30 WHERE empno=7788;
5. 1 row updated.
6.  
7. SQL> SAVEPOINT a;
8. Savepoint created.
9.  
10. SQL> DELETE FROM emp;
11. 15 rows deleted.
12.  
13. SQL> ROLLBACK TO a;
14. Rollback complete.
15.  
16. // SAVEPOINT a까지만 롤백을 했기 때문에 UPDATE 이후 값으로 출력됨.
17. SQL> SELECT deptno FROM emp WHERE empno=7788;
18.     DEPTNO
    ----------
            30
19.  
20. // 트랜잭션 시작된 시점으로 롤백
21. SQL> ROLLBACK;
22. Rollback complete.
23.  
24. // UPDATE 전 값으로 롤백됨~
25. SQL> SELECT deptno FROM emp WHERE empno=7788;
26.     DEPTNO
    ----------
            20

State of the Data During Transaction (P10-7)

• SCOTT 계정으로 SQLPLUS를 2개 띄워서 각각 접속 (같은 아이디 다른 세션)
• SCOTT.A 계정으로 DML문을 사용하면 해당되는 레코드에 자동으로 LOCK이 걸림(COMMIT, ROLLBACK 되기전까지)
• SCOTT.B 계정의 락이 걸린 데이터를 변경 등 하려고하면 대기하고 있다가 SCOTT.A에서 LOCK을 풀면 그때서야 수행됨.
• SCOTT.A 계정에서 데이터를 변경하면 SCOTT.B에서 SELECT 구문을 이용해서 조회하면 SCOTT.A가 변경한 데이터는 안보이고 예전 데이터가 조회됨

• SCOTT.A

1. SQL> UPDATE emp
     2  SET sal=0
     3  WHERE deptno=10;
2. 3 rows updated.
3.  
4. SQL> ROLLBACK;
5. Rollback complete.
6.  
7. SQL>  UPDATE emp
     2   SET sal=0
     3   WHERE deptno=10;
8. 3 rows updated.
9.  
10. SQL> SELECT sal FROM emp WHERE deptno=10;
11.        SAL
    ----------
             0
             0
             0

• SCOTT.B

1.  SQL> SELECT sal
     2  FROM emp
     3  WHERE deptno=10;
2.        SAL
   ----------
         2450
         5000
         1300
3.  
4. SQL> UPDATE emp
     2  SET sal=5
     3  WHERE deptno=10;
5. 3 rows updated.   // SCOTT.A에서 롤백 등 해야 수행됨
6.  
7. SQL> ROLLBACK;
8. Rollback complete.
9.  
10. SQL> SELECT sal FROM emp WHERE deptno=10;   // SCOTT.A에서 sal=0으로 변경했지만 안보임~
11.        SAL
    ----------
          2450
          5000
          1300

State of Data After Transaction (P10-8)

SESSION #1

1. SQL> SELECT * FROM tr1;
   SELECT * FROM tr1
                 *
   ERROR at line 1:
   ORA-00942: table or view does not exist
2. 
   SQL> /
3. no rows selected
4.  
5. SQL> /
6. no rows selected
7.  
8. SQL> /
9.          A
   ----------
           10
10.  
11. SQL> UPDATE tr1 SET a=20;
12. 1 row updated.
13.  
14. SQL> SELECT * FROM tr1;
15.          A
    ----------
            20
16.  
17. SQL> COMMIT;
18. Commit complete.
19.  
20. SQL> SELECT * FROM tr1;
21.          A
    ----------
            20
22.  
23. SQL> /
24.          A
    ----------
            20
25.  
26. SQL> /
27.          A
    ----------
            30

SESSION #2

1. SQL> CREATE TABLE tr1 (a number);
2. Table created.
3.  
4. SQL> INSERT INTO tr1 VALUES(10);
5. 1 row created.
6.  
7. SQL> SELECT * FROM tr1;
8.          A
   ----------
           10
9.  
10. SQL> COMMIT;
11. Commit complete.
12.  
13. SQL> SELECT * FROM tr1;
14.          A
    ----------
            10
15.  
16. SQL> UPDATE tr1 SET a=30;
17. 1 row updated.
18.  
19. SQL> SELECT * FROM tr1;
20.          A
    ----------
            30
21.  
22. SQL> COMMIT;
23. Commit complete.

Set Transaction (P10-10)

• 읽기 일관성 : 한번 읽었던 것은 다른곳에서 내용이 변경되도 내가 변경안한거면 처음 내용 그대로 읽어지는것
  
• READ ONLY : 읽기전용

SESSION 1

1. (1)SQL> SET TRANSACTION READ ONLY;   // 읽기 일관성 제공
2. Transaction set.
3.  
4. (2)SQL> SELECT sal FROM emp WHERE deptno=20;
5.        SAL
   ----------
          800
         2975
         3000
         1100
         3000
6.  
7. // SESSION2에서 (3), (4) 수행한 결과가 안보임(READ ONLY)
8. (5)SQL> SELECT sal FROM emp WHERE deptno=20;
9.        SAL
   ----------
          800
         2975
         3000
         1100
         3000

SESSION 2

1. (3)SQL> UPDATE emp
     2  SET sal=2000
     3  WHERE deptno=20;
2. 5 rows updated.
3.  
4. (4)SQL> COMMIT;
5. Commit complete.

READ WRITE(기본값) :

SERIALIZABLE :

읽기 일관성 제공됨((P10-11 표 예제) 1~6번까지가 해당되는 내용)

자기 세션기준으로 초기값 그대로라면 수정가능(?) (다른 세션에 수정안하거나, commit 안한경우??) 

READ COMMITTED(기본값) :

읽기 일관성 제공안함 -> 다른 세션에서 COMMIT하면 변경된 내용 보임

한 세션에서 DML문장을 사용하는 경우 LOCK이 걸리고 다른 세션에서는 LOCK이 걸린 항목을 변경하려면 변경안되고 기다리고 있음(LOCK을 건 세션에서 COMMIT 등을 하기전까지~)

이 글은 스프링노트에서 작성되었습니다.