자넨 누군가? ::

« » 2012.01
일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

2008-08-23

ORACLE 10g / 2008/08/26 10:02

2-29 External Table

OS> mkdir c:\EXT_TEST

####staff.txt#####
301,KWON,29,F,740205,2598659,107
302,KIM,30,M,730715,1824451,108
303,JUNG,22,F,810922,2411057,107
304,YUN,25,F,781102,2804478,109
305,PARK,24,M,790130,1496300,108
306,LEE,32,M,711013,1695521,110
307,KAWK,34,7108100,1135620,110

SQL> show user
USER is "HR"

SQL> CREATE OR REPLACE DIRECTORY extdir AS 'C:\EXT_TEST\';
CREATE OR REPLACE DIRECTORY extdir AS 'C:\EXT_TEST\'
*
ERROR at line 1:
ORA-01031: insufficient privileges <--Error

SQL> conn system/oracle

SQL> GRANT CREATE ANY DIRECTORY, DROP ANY DIRECTORY TO hr ;

SQL> CREATE OR REPLACE DIRECTORY extdir AS 'C:\EXT_TEST';

SQL> grant read, write on directory extdir to public ;

SQL> CREATE TABLE staff_ext (
   sno    NUMBER, sname VARCHAR2(10), age NUMBER, gender CHAR(2),
   jumin1 CHAR(6), jumin2 CHAR(7), dno NUMBER)
   ORGANIZATION EXTERNAL (
TYPE    ORACLE_LOADER
DEFAULT DIRECTORY extdir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE 'staff_bad'
   LOGFILE 'staff_log'
   FIELDS TERMINATED BY ',' (
   sno char, sname CHAR,
   age char, gender CHAR,
   jumin1 char, jumin2 CHAR,
   dno char )    )
   LOCATION ('staff.txt') )
PARALLEL 5
REJECT LIMIT 200 ;

SQL> select * from staff_ext ;

   SNO SNAME AGE GE JUMIN1 JUMIN2 DNO
---------- ---------- ---------- -- ------ ------- ----------
   301 KWON 29 F 740205 2598659    107
   302 KIM    30 M 730715 1824451    108
   303 JUNG 22 F 810922 2411057    107
   304 YUN    25 F 781102 2804478    109
   305 PARK 24 M 790130 1496300    108
   306 LEE    32 M 711013 1695521    110

-- 원본 staff.txt에서 에러난 리인을 수정.

SQL> select * from staff_ext ;

7 rows selected.

SQL> desc staff_ext ;
Name    Null?    Type
----------------------------------------- -------- ----------------------------
SNO    NUMBER
SNAME    VARCHAR2(10)
AGE    NUMBER
GENDER CHAR(2)
JUMIN1 CHAR(6)
JUMIN2 CHAR(7)
DNO    NUMBER

SQL> select length(sname) from staff_ext ;

교재 2-35 와 비교

SQL> select length(sname) from staff_ext ;

LENGTH(SNAME)
-------------
4
4
4
4
4
4
4

SQL> select length(sname) from staff_ext ;

LENGTH(SNAME)
-------------
4
3
4
4
4
4
4

7 rows selected.

SQL> select * from staff_ext
where sname = 'YUN' ;

no rows selected

SQL> select * from staff_ext
where sname = 'YUN ' ;

SNO SNAME AGE GE JUMIN1 JUMIN2 DNO
---------- ---------- ---------- -- ------ ------- ----------
304 YUN 25 F 781102 2804478 109

================================================================================
#############
## 7th Day ##
#############

## Chapter 03 ##

3-12

CREATE TABLE hr.emps
(e_id NUMBER(4)
,name VARCHAR2(4) CONSTRAINT nn_name_emps NOT NULL
,address VARCHAR2(30) DEFAULT '서울'
,salary NUMBER(8)
,jumin VARCHAR2(13) NOT NULL
,deptid NUMBER(4)
,CONSTRAINT pk_e_id_emps PRIMARY KEY(e_id)
,CONSTRAINT uk_jumin_emps UNIQUE(jumin)
,CONSTRAINT ck_salary_emps CHECK(salary > 0)
,CONSTRAINT fk_emps_departments FOREIGN KEY(deptid)
REFERENCES hr.departments(department_id) ON DELETE SET NULL
);

3-15 ~ 3-26

1. UNCONDITIONAL INSERT ALL

CREATE TABLE sal_history
(EMPID number(6), HIREDATE date, SAL number);

CREATE TABLE mgr_history
(EMPID number(6), MGR NUMBER(6), SAL number);

INSERT ALL
INTO sal_history (EMPID, HIREDATE,SAL)
VALUES (employee_id, hire_date, salary)
INTO mgr_history (EMPID, MGR, SAL)
VALUES (employee_id, manager_id, salary)
SELECT employee_id, hire_date, salary, manager_id
FROM employees
WHERE employee_id >200;

SELECT employee_id , hire_date , salary , manager_id
FROM employees
WHERE employee_id > 200;

EMPLOYEE_ID HIRE_DAT SALARY MANAGER_ID
----------- -------- ---------- ----------
201 96/02/17    13000    100
202 97/08/17 6000    201
203 94/06/07 6500    101
204 94/06/07    10000    101
205 94/06/07    12000    101
206 94/06/07 8300    205

(결과)
select * from sal_history;

   EMPID HIREDATE    SAL
---------- -------- ----------
   201 96/02/17    13000
   202 97/08/17 6000
   203 94/06/07 6500
   204 94/06/07    10000
   205 94/06/07    12000
   206 94/06/07 8300

SELECT * FROM mgr_history;

   EMPID    MGR    SAL
---------- ---------- ----------
   201    100    13000
   202    201 6000
   203    101 6500
   204    101    10000
   205    101    12000
   206    205 8300

rollback ;

--------------------------------------------------------------------------------
2. CONDITIONAL INSERT ALL

rollback ;

CREATE TABLE else_table
AS
SELECT employee_id EMPID, hire_date HIREDATE
,salary SAL, manager_id MGR
FROM hr.employees
WHERE 1=2 ;

DELETE FROM hr.employees
WHERE employee_id = 900 ;

INSERT INTO hr.employees
VALUES (900, '','신상현','ks7009','',sysdate,'AC_MGR',
30000,'',205,110);

commit ;

SELECT employee_id, hire_date, salary, manager_id
FROM employees
WHERE employee_id > 200;

INSERT ALL
WHEN salary > 10000
THEN INTO sal_history (EMPID, HIREDATE, SAL)
VALUES (employee_id, hire_date, salary)
WHEN manager_id > 200
THEN INTO mgr_history(EMPID, MGR, SAL)
VALUES (employee_id, manager_id, salary)
ELSE INTO else_table(EMPID, HIREDATE, SAL, MGR)
VALUES (employee_id, hire_date,salary,manager_id)
SELECT employee_id, hire_date, salary, manager_id
FROM employees
WHERE employee_id > 200;

(결과)
SELECT * FROM sal_history;

   EMPID HIREDATE    SAL
---------- -------- ----------
   201 96/02/17    13000
   205 94/06/07    12000
   900 04/02/20    30000

SELECT * FROM mgr_history;

   EMPID    MGR    SAL
---------- ---------- ----------
   202    201 6000
   206    205 8300
   900    205    30000

SELECT * FROM else_table ;

   EMPID HIREDATE    SAL    MGR
---------- -------- ---------- ----------
   203 94/06/07 6500    101
   204 94/06/07    10000    101

rollback ;
--------------------------------------------------------------------------------
3. Conditional FIRST INSERT

rollback ;

SELECT employee_id, hire_date, salary, manager_id
FROM employees
WHERE employee_id > 200;

INSERT first
WHEN salary > 10000
THEN INTO sal_history (EMPID, HIREDATE, SAL)
VALUES (employee_id, hire_date, salary)
WHEN manager_id > 200
THEN INTO mgr_history(EMPID, MGR, SAL)
VALUES (employee_id, manager_id, salary)
ELSE INTO else_table(EMPID, HIREDATE, SAL, MGR)
VALUES (employee_id, hire_date,salary,manager_id)
SELECT employee_id, hire_date, salary, manager_id
FROM employees
WHERE employee_id > 200;

(교재 실습)

CREATE TABLE special_sal (DEPTID NUMBER(6), SAL NUMBER(8,2));
CREATE TABLE hiredate_history_00 (DEPTID NUMBER(6), HIREDATE DATE);
CREATE TABLE hiredate_history_99 (DEPTID NUMBER(6), HIREDATE DATE);
CREATE TABLE hiredate_history (DEPTID NUMBER(6), HIREDATE DATE);

SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE
FROM employees
GROUP BY department_id;

DEPTID    SAL HIREDATE
---------- ---------- --------
10 4400 87/09/17
20    19000 97/08/17
30    24900 99/08/10
40 6500 94/06/07
50 156400 00/03/08
60    28800 99/02/07
70    10000 94/06/07
80 304500 00/04/21
90    58000 93/01/13
   100    51600 99/12/07
   110    50300 04/02/20
   7000 99/05/24

INSERT FIRST
WHEN SUM_SAL > 25000
THEN INTO special_sal
VALUES(department_id, SUM_SAL)
WHEN MAXHIREDATE like ('%00%')
THEN INTO hiredate_history_00
VALUES (department_id, MAXHIREDATE)
WHEN MAXHIREDATE like ('%99%')
THEN INTO hiredate_history_99
VALUES (department_id, MAXHIREDATE)
ELSE INTO hiredate_history
VALUES (department_id, MAXHIREDATE)
   SELECT department_id, SUM(salary) SUM_SAL
   ,MAX(hire_date) MAXHIREDATE
   FROM employees
   GROUP BY department_id;

SELECT * FROM special_sal;

DEPTID    SAL
---------- ----------
50 156400
60    28800
80 304500
90    58000
   100    51600
   110    50300

SELECT * FROM hiredate_history_00;

SELECT * FROM hiredate_history_99;

DEPTID HIREDATE
---------- --------
30 99/08/10
99/05/24

SELECT * FROM hiredate_history;

DEPTID HIREDATE
---------- --------
10 87/09/17
20 97/08/17
40 94/06/07
70 94/06/07

--------------------------------------------------------------------------------
4. PIVOT 실습

1. 준비
1) NON-RELATIONAL (비정규화된 테이블) Table 생성

CREATE TABLE sales_qty_week
(WEEK_ID    NUMBER(2)
,employee_id number(2)
,prod_cd    varchar2(10)
,MON_qty    NUMBER(6)
,TUE_qty    NUMBER(6)
,WED_qty    NUMBER(6)
,THU_qty    NUMBER(6)
,FRI_qty    NUMBER(7)
,CONSTRAINT pk_sales_qty_week PRIMARY KEY (week_id, employee_id, prod_cd)
)TABLESPACE example ;

SQL> desc hr.sales_qty_week
이름    널?    유형
----------------------------------------- -------- ---------------
WEEK_ID NOT NULL NUMBER(2)
EMPLOYEE_ID NOT NULL NUMBER(2)
PROD_CD NOT NULL VARCHAR2(10)
MON_QTY    NUMBER(6)
TUE_QTY    NUMBER(6)
WED_QTY    NUMBER(6)
THU_QTY    NUMBER(6)
FRI_QTY    NUMBER(7)

2) 행입력

INSERT INTO sales_qty_week
(week_id, employee_id, prod_cd,
MON_qty, TUE_qty,WED_qty,THU_qty,FRI_qty)
VALUES (1, 10, 'A', 10, 5, 6, 10, 3) ;

INSERT INTO sales_qty_week
(week_id, employee_id, prod_cd,
MON_qty, TUE_qty,WED_qty,THU_qty,FRI_qty)
VALUES (1, 10, 'B', 1, 2, 3, 4, 5) ;

INSERT INTO sales_qty_week
(week_id, employee_id, prod_cd,
MON_qty, TUE_qty,WED_qty,THU_qty,FRI_qty)
VALUES (1, 20, 'A', 5, 5, 5, 5, 5) ;

col MON_QTY format 999
col TUE_QTY format 999
col WED_QTY format 999
col THU_QTY format 999
col FRI_QTY format 999
col PROD_CD format a2
col PROD_ID format a2
col week_id format 999
col employee_id format 999

SELECT * FROM sales_qty_week ;

WEEK_ID EMPLOYEE_ID PR MON_QTY TUE_QTY WED_QTY THU_QTY FRI_QTY
------- ----------- -- ------- ------- ------- ------- -------
1    10 A 10 5 6    10 3
1    10 B    1 2 3 4 5
1    20 A    5 5 5 5 5

지금까지 만든 NON-RELATIONAL TABLE의 데이터를 RELATIONAL TABLE에
입력해봅시다.

2. RELATIONAL TABLE 생성

CREATE TABLE sale_qty_week_re
(week_id NUMBER(2)
,EMPLOYEE_ID NUMBER(2)
,PROD_ID VARCHAR2(2)
,wd_cd NUMBER(1)
,qty number(6)
)
TABLESPACE example ;
1 월 2 화 3 수 4목 5금
desc sale_qty_week_re

이름    널?    유형
----------------------------------------- -------- ----------------------------
WEEK_ID    NUMBER(2)
EMPLOYEE_ID    NUMBER(2)
PROD_ID    VARCHAR2(2)
WD_CD    NUMBER(1)
QTY    NUMBER(6)

(참고)
desc sales_qty_week
이름    널?    유형
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(2)
PROD_CD NOT NULL VARCHAR2(10)
MON_QTY    NUMBER(6)
TUE_QTY    NUMBER(6)
WED_QTY    NUMBER(6)
THU_QTY    NUMBER(6)
FRI_QTY    NUMBER(7)
WEEK_ID NOT NULL NUMBER(2)

3. NON-RELATIONAL TABLE(sales_qty_week)의 데이터를
RELATIONAL TABLE(sale_qty_week_re)에 입력

INSERT ALL
INTO sale_qty_week_re(week_id, employee_id,prod_id, wd_cd, qty)
VALUES(week_id, employee_id, prod_cd,1,MON_qty)
INTO sale_qty_week_re(week_id, employee_id,prod_id, wd_cd, qty)
VALUES(week_id, employee_id, prod_cd,2,TUE_qty)
INTO sale_qty_week_re(week_id, employee_id,prod_id, wd_cd, qty)
VALUES(week_id, employee_id, prod_cd,3,WED_qty)
INTO sale_qty_week_re(week_id, employee_id,prod_id, wd_cd, qty)
VALUES(week_id, employee_id, prod_cd,4,THU_qty)
INTO sale_qty_week_re(week_id, employee_id,prod_id, wd_cd, qty)
VALUES(week_id, employee_id, prod_cd,5,FRI_qty)
   SELECT EMPLOYEE_ID,PROD_CD,MON_QTY,TUE_QTY,WED_QTY,THU_QTY
   ,FRI_QTY,WEEK_ID
   FROM sales_qty_week ;

(확인)

SELECT * FROM sale_qty_week_re;

WEEK_ID EMPLOYEE_ID PR    WD_CD    QTY
------- ----------- -- ---------- ----------
1    10 A 1 10
1    10 B 1    1
1    20 A 1    5
1    10 A 2    5
1    10 B 2    2
1    20 A 2    5
1    10 A 3    6
1    10 B 3    3
1    20 A 3    5
1    10 A 4 10
1    10 B 4    4
1    20 A 4    5
1    10 A 5    3
1    10 B 5    5
1    20 A 5    5

15 개의 행이 선택되었습니다.

--4. 기존 NON-relational table export 받고 drop ;

OS> exp system/oracle tables=hr.sales_qty_week file=c:\oracle\dump.dmp

SQL> drop table sales_qty_week purge;

--5. table rename

SQL> alter table sale_qty_week_re rename to sales_qty_week ;

6. 입력된 데이터 확인 후, RELATIONAL TABLE에 PK 추가

ALTER TABLE sales_qty_week
ADD CONSTRAINT pk_sale_qty_week
PRIMARY KEY (week_id, employee_id, prod_id,wd_cd) ;

SELECT *
FROM sales_qty_week
ORDER BY week_id,employee_id, prod_id,wd_cd;

WEEK_ID EMPLOYEE_ID PR    WD_CD    QTY
------- ----------- -- ---------- ----------
1    10 A 1 10
1    10 A 2    5
1    10 A 3    6
1    10 A 4 10
1    10 A 5    3
1    10 B 1    1
1    10 B 2    2
1    10 B 3    3
1    10 B 4    4
1    10 B 5    5
1    20 A 1    5
1    20 A 2    5
1    20 A 3    5
1    20 A 4    5
1    20 A 5    5

15 개의 행이 선택되었습니다.

SQL>

--------------------------------------------------------------------------------
3-27

MERGE 구문 실습

용어
목적테이블 : 삽입 및 갱신이 일어나는 테이블
사용테이블 : USING 절에 기술되는 테이블

(주의)
1. 삽입 및 갱신되는 컬럼들, 특히 ON 절에 기술되는 컬럼은 동일한 DataType 이어야 합니다.
2. ON 절에 기술된 기준이 되는 컬럼의 데이터는 UPDATE SET 시킬 수 없습니다.

MERGE 구문 시 다음 사항을 확인하시기 바랍니다.
1. 목적테이블과 대상 테이블이 꼭 같을 필요는 없습니다.
2. 삽입 및 갱신되는 컬럼들, 특히 ON 절에 기술되는 컬럼은 반드시 동일한 DataType 이어야 합니다.
3. 컬럼 이름이 동일할 필요는 없습니다.
4. UPDATE와 INSERT 부분에 기술되는 컬럼갯수들이 목적테이블과 사용테이블 어느 쪽과 같을 필요는 없습니다.
5. ON 절에 기술된 기준이 되는 컬럼의 데이터는 UPDATE SET 부분에 명시하면 않됩니다.

-------------------------------------------------------------------------------------------------------

목적 테이블과 사용테이블은 DataType은 동일하고 컬럼갯수 및 컬럼 이름이 다릅니다.
MERGE 시에 갱신은 한개 컬럼, 삽입은 3개 등 목적 테이블과 사용테이블의 일부 컬럼만을 가지고 실습합니다.

CREATE TABLE copy_emp01
(EMPNO    NUMBER(6),
   ENAME    VARCHAR2(20),
   JOB    VARCHAR2(10) DEFAULT 'NO JOB',
   MGR    NUMBER(6),
   HIREDATE DATE,
   SAL    NUMBER(8,2),
   COMM NUMBER(2,2),
   DEPTNO NUMBER(4)
   );

--copy_emp01 테이블에 데이터가 없어요..

10g only

MERGE INTO copy_emp01 c
USING employees e
ON (c.empno = e.employee_id)
WHEN NOT MATCHED THEN
INSERT (c.empno,c.ename,c.hiredate)
VALUES(e.employee_id, e.FIRST_NAME, sysdate);

## WHEN NOT MATCHED THEN 이하만 적었읍니다.
10g 에서는 정상수행, 9i 에서는 Error !!

9i, 10g

MERGE INTO copy_emp01 c
USING employees e
ON (c.empno = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.deptno = e.department_id
WHEN NOT MATCHED THEN
INSERT (c.empno,c.ename,c.hiredate)
VALUES(e.employee_id, e.FIRST_NAME, sysdate);

107 행이 병합되었습니다. <--INSERT만 됨

SQL> SELECT empno, ename, hiredate, JOB, deptno FROM copy_emp01;

(empno, ename, hiredate(sysdate 함수처리 결과), Job(DEFAULT 값인 NO JOB) 컬럼에만 값이 입력됨)
(DEPTNO 컬럼은 널값)

10g only

MERGE INTO copy_emp01 c
USING employees e
ON (c.empno = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.deptno = e.department_id ;

9i or 10g

SQL> SELECT empno, ename, hiredate, JOB, deptno FROM copy_emp01;

(DEPTNO 컬럼값이 employees 테이블의 DEPARTMENT_ID 값으로 갱신됨)

--------------------------------------------------------------------------------
3-31

### Flashback Version Query 연습 ###

c:\oracle>sqlplus hr/hr

drop table hr.emps13_01 purge ;

create table hr.emps13_01(empno, ename, sal, deptno)
as
select employee_id, last_name, salary, department_id
from hr.employees
where department_id in (10,20,90) ;

col ename format a15

SELECT * FROM hr.emps13_01 order by 1;

Update hr.emps13_01 SET ename='관우' WHERE empno=101 ;

commit ;

Update hr.emps13_01 SET sal=0 WHERE empno=101 ;

commit ;

delete from hr.emps13_01 where empno=101 ;

commit ;

SELECT ename,SAL
,versions_startscn ST_SCN
,versions_endscn END_SCN
,versions_XID TX_ID
,versions_operation OPERATION
FROM hr.emps13_01
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE empno=101;

ENAME    SAL ST_SCN    END_SCN TX_ID    O
--------------- ---------- ---------- ---------- ---------------- -
관우 17000    1002189    01001200E0010000 D
관우 17000    1002179    1002189 0A000700E6010000 U
Kochhar    17000 1002179

================================================================================
## Chapter 04 ##

4-13

(Query 1)
SELECT department_id, job_id, avg(salary) AS avgsal
FROM employees
GROUP BY department_id,job_id ;

(Query 2)
SELECT job_id, manager_id,avg(salary)
FROM employees
GROUP BY job_id,manager_id ;

이 두 쿼리의 결과를 한번에 실행하고 싶다면, . . .

(방법 1)
SELECT department_id, job_id, null AS manager_id, avg(salary) AS avgsal
FROM employees
GROUP BY department_id,job_id
UNION ALL
SELECT null , job_id, manager_id,avg(salary)
FROM employees
GROUP BY job_id,manager_id ;

(9i 버젼 이후)
SELECT department_id, job_id, manager_id,avg(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));

--------------------------------------------------------------------------------
4-8 세션2개를 연다

(세션1)
SELECT department_id, job_id, salary
FROM employees
WHERE department_id < 60
ORDER BY 1,2 ;

(세션2)
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);

(세션3)
--(Query1)
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY (department_id, job_id);

--(Query2)
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY department_id;

--(Query3)
SELECT SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ();

Query 1,2,3을 UNION ALL 처리 함.

이를 간단히 표시하면, GROUP BY rollup (A,B,C) 는 다음과 같은 결과를 보인다.

GROUP BY (A,B,C)
UNION ALL
GROUP BY (A,B,null)
UNION ALL
GROUP BY (A, null, null)
UNION ALL
GROUP BY (null, null, null)

이를 간단히 표시하면, GROUP BY CUBE (A,B,C) 는 다음과 같은 결과를 보인다.

GROUP BY (A,B,C)
UNION ALL
GROUP BY (A,B,null)
UNION ALL
GROUP BY (A, null, null)
UNION ALL
GROUP BY (null, null, null)
UNION ALL
GROUP BY (A,null,C)
UNION ALL
GROUP BY (null,B,C)
UNION ALL
GROUP BY (null,b, null)
UNION ALL
GROUP BY (null, null,c)

--------------------------------------------------------------------------------
4-10

Cube는 조합가능한 모든 그룹핑이 모두 처리됩니다.

SELECT department_id, job_id, SUM(salary),
FROM employees
WHERE department_id < 50
GROUP BY CUBE(department_id, job_id);

간단히 설명하면,

GROUP BY (department_id, job_id)
UNION ALL
GROUP BY (department_id, null)
UNION ALL
GROUP BY (null, null)
UNION ALL
GROUP BY (null, job_id)

--------------------------------------------------------------------------------
4-12

(교재 구문)

SELECT department_id DEPTID, job_id JOB, SUM(salary),
   GROUPING(department_id) GRP_DEPT,
   GROUPING(job_id) GRP_JOB
FROM employees
WHERE    department_id < 50
GROUP BY ROLLUP(department_id, job_id);

SELECT department_id, job_id, SUM(salary),
GROUPING(department_id) GRP_DEPT,
GROUPING(job_id) GRP_JOB
FROM employees
GROUP BY ROLLUP(department_id, job_id);

SELECT department_id, job_id, SUM(salary),
GROUPING(department_id) GRP_DEPT,
GROUPING(job_id) GRP_JOB
FROM employees
GROUP BY CUBE(department_id, job_id);

--------------------------------------------------------------------------------
4-17 Composite Columns

GROUP BY rollup (A,B,C,D)

GROUP BY (A,B,C,D)
GROUP BY (A,B,C)
GROUP BY (A,B)
GROUP BY (A)
GROUP BY ()

GROUP BY rollup (A,(B,C,D))

GROUP BY (A,B,C,D)
GROUP BY (A)
GROUP BY ()

--------------------------------------------------------------------------------
4-18

GROUP BY GROUPING SETS ((a),(b),(c))
= GROUP BY GROUPING SETS (a,b,c)

GROUP BY GROUPING SETS ((a,b,c))
=== GROUP BY (a,b,c)

--------------------------------------------------------------------------------
4-19 Composite Columns

(설명 수정)

그림 (1) --> (2)

(설명 수정)
1. (department_id, job_id) <-- 요기 수정
2. (department_id, job_id, manager_id)
3. (department_id)
4 ()

group by ROLLUP( department_id,(job_id, manager_id));

group by department_id,job_id, manager_id
union all
group by department_id
union all
group by ()

group by ROLLUP( department_id,job_id, manager_id);

group by department_id,job_id, manager_id
union all
group by department_id,job_id
union all
group by department_id
union all
group by ()

SELECT department_id, job_id, manager_id,SUM(salary)
,GROUPING(department_id) GRP_DEPT
,GROUPING(job_id) GRP_JOB
,GROUPING(manager_id) GRP_MGR
FROM employees
GROUP BY ROLLUP( department_id,(job_id, manager_id));

group by department_id,job_id, manager_id 2
group by department_id 3
group by () 4

--------------------------------------------------------------------------------
4-22 Concatenated Groupings

(기준)
1. 모든 그룹핑 형식이 한번씩 고려되야함.
2. 중복된 필드는 한번으로 처리
3. () (Grand Total Group)는 삭제

(내용)
GROUP BY A, rollbup (A,C), cube (C,D) 결과
   A A,C C,D A A,C C,D ACD
   A C A A,C C AC
   ()    D A A,C D AD A
   ()    A A,C

select department_id, job_id, manager_id, sum(salary)
,GROUPING(department_id) GRP_DEPT
,GROUPING(job_id) GRP_JOB
,GROUPING(manager_id) GRP_MGR
from hr.employees
GROUP BY department_id, ROLLUP(job_id), CUBE(manager_id);
department_id job_id (manager_id)
() ()

(department_id,job_id,manager_id)
(department_id,job_id,)
(department_id,,manager_id)
(department_id,)

--------------------------------------------------------------------------------
4-21

GROUP BY GROUPING SETS((a), (b)), GROUPING SETS((c), (d))
GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)

(a) (c)
(b) (d)

GROUPING SETS((a,c),(a,d),(b,c),(b,d))

--------------------------------------------------------------------------------
##########################
## ROLLUP_CUBE_보충연습 ##
##########################

## 아래의 예제는 스스로 관련 테이블을 만들고, 데이터도 입력한 다음에
실습해 보시기 바랍니다.

## 아래는 이런 정보를 조회할 수 있구나 라는 느낌을 가져 보시라고
표시한 것입니다.

스스로 분석해 보세요.. 재미있습니다.

1.대리점별 판매건수

select store_name , count(*)
from month_sales
group by store_name ;

2.대리점별 월별 판매건수

select store_name , to_char(sale_date,'MM') MON , count(*)
from month_sales
group by store_name , to_char(sale_date,'MM') ;

3.대리점별 월별 판매건수
select store_name ,
sum(decode (to_char(sale_date,'MM'),10 ,1,0)) "10월" ,
sum(decode (to_char(sale_date,'MM'),11 ,1,0)) "11월" ,
sum(decode (to_char(sale_date,'MM'),12 ,1,0)) "12월"
from month_sales
group by store_name , to_char(sale_date,'MM') ;

select store_name ,
sum(decode (to_char(sale_date,'MM'),10 ,1,0)) "10월" ,
sum(decode (to_char(sale_date,'MM'),11 ,1,0)) "11월" ,
sum(decode (to_char(sale_date,'MM'),12 ,1,0)) "12월"
from month_sales
group by store_name ;

4.대리점별 월별 판매총액
select store_name ,
sum(decode (to_char(sale_date,'MM'),10 ,sale_price,0)) "10월" ,
sum(decode (to_char(sale_date,'MM'),11 ,sale_price,0)) "11월" ,
sum(decode (to_char(sale_date,'MM'),12 ,sale_price,0)) "12월"
from month_sales
group by store_name , to_char(sale_date,'MM') ;

select store_name ,
sum(decode (to_char(sale_date,'MM'),10 ,sale_price,0)) "10월" ,
sum(decode (to_char(sale_date,'MM'),11 ,sale_price,0)) "11월" ,
sum(decode (to_char(sale_date,'MM'),12 ,sale_price,0)) "12월"
from month_sales
group by cube(store_name) ;

select nvl(store_name,'Total') ,
sum(decode (to_char(sale_date,'MM'),10 ,sale_price,0)) "10월" ,
sum(decode (to_char(sale_date,'MM'),11 ,sale_price,0)) "11월" ,
sum(decode (to_char(sale_date,'MM'),12 ,sale_price,0)) "12월" ,
sum(sale_price) "TOTAL"
from month_sales
group by cube(store_name) ;

4.대리점별 월별 판매총액
select nvl(str_nm,'Total') , sum(decode(mon,10,total,0)) "10월" ,
sum(decode(mon,11,total,0)) "11월",
sum(decode(mon,12,total,0)) "12월",
sum(decode(mon,10,total,0)) + sum(decode(mon,11,total,0)) +
sum(decode(mon,12,total,0)) "TOTAL"
from ( select store_name str_nm, extract (month from sale_date ) mon , sum(sale_price) total
from month_sales
group by cube(store_name , extract (month from sale_date ) ) )
group by str_nm ;

================================================================================
#############
## 8th Day ##
#############

## Chapter 05 ##

5-6

SELECT parameter, value FROM nls_session_parameters ;

ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS' ;
ALTER SESSION SET nls_timestamp_format='YYYY-MM-DD HH24:MI:SSXFF' ;
ALTER SESSION SET nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SSXFF TZR' ;

ALTER SESSION SET time_zone='+1:00' ;

set linesize 200
col sysdate format a19
col current_date format a19
col current_timestamp format a33
col localtimestamp format a26
col sessiontimezone format a6
col dbtimezone format a6

SELECT sysdate, current_date, current_timestamp, localtimestamp
,sessiontimezone, dbtimezone
FROM dual ;
SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP SESSIO DBTIME
------------------- ------------------- --------------------------------- -------------------------- ------ ------
2007-11-14 15:34:27 2007-11-14 07:34:27 2007-11-14 07:34:27.342000 +01:00 2007-11-14 07:34:27.342000 +01:00 +00:00

SQL> ALTER SESSION SET time_zone='+4:00' ;

sysdate

SQL> SELECT sysdate, current_date, current_timestamp, localtimestamp
,sessiontimezone, dbtimezone
FROM dual ;

SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP SESSIO DBTIME
------------------- ------------------- --------------------------------- -------------------------- ------ ------
2007-11-14 15:35:23 2007-11-14 10:35:23 2007-11-14 10:35:23.122000 +04:00 2007-11-14 10:35:23.122000 +04:00 +00:00
--------------------------------------------------------------------------------
5-9

참고) database의 time_zone 변경방법

c:\> sqlplus sys/oracle as sysdba

SQL> alter database set time_zone='+9:00' ;
alter database set time_zone='+9:00'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
LOCAL TIME ZONE columns

1행에 오류:
ORA-30079: 데이터베이스에 TIMESTAMP WITH LOCAL TIME ZONE 열이 있는 경우
데이터베이스 시간대를 변경할 수 없음

col owner format a10
col table_name format a15
col column_name format a20

SQL> select owner, table_name, column_name
from dba_tab_columns
where data_type like 'TIMESTAMP% WITH LOCAL TIME ZONE';

OWNER    TABLE_NAME    COLUMN_NAME
---------- --------------- --------------------
OE ORDERS    ORDER_DATE
HR TIMETEST    T_TSLTZ

2. export 툴을 이용해서 위의 두 table을 export 함

OS> exp system/oracle tables=(oe.orders, hr.timetest) file=c:\oracle\dump1.dmp direct=y

3. table drop

(10g)
- recyclebin에 drop 된 테이블 정보가 남기때문에

- drop 시 purge 옵션을 사용하든가
purge dba_recyclebin 명령어를 이용해서 휴지통 정보도 같이 삭제.

(9i)
- drop 만 하면됨

(~8i)
- TIMESTAMP WITH LOCAL TIME ZONE 데이터형식이 없음

SQL> drop table oe.orders cascade constraints PURGE;

SQL> drop table hr.timetest cascade constraints purge;

SQL> alter database set time_zone='+9:00' ;
alter database set time_zone='+9:00'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
LOCAL TIME ZONE columns

SQL> select original_name from dba_recyclebin ;

ORIGINAL_NAME
--------------------------------
ORD_SALES_REP_IX
ORD_CUSTOMER_IX
ORD_ORDER_DATE_IX
ORDER_PK
ORDERS
A1

SQL> purge dba_recyclebin ;

SQL> select original_name from dba_recyclebin ;

no rows selected

4. 설정 변경

SQL> alter database set time_zone='+9:00' ;

Database altered.

5. db 재 가동

SQL> conn sys/oracle as sysdba

SQL> shutdown immediate

SQL> startup

6. exported tables을 import

OS> imp system/oracle tables=timetest fromuser=hr touser=hr file=c:\oracle\dump1.dmp
OS> imp system/oracle tables=orders fromuser=oe touser=oe file=c:\oracle\dump1.dmp

--------------------------------------------------------------------------------
5-18 INTERVAL Data Type의 활용

준비과정1) 테이블 생성

drop table hr.test_interval purge ;

CREATE TABLE hr.test_interval
(t_id NUMBER(3)
,iytm INTERVAL YEAR(3) TO MONTH
,idts INTERVAL DAY(3) TO SECOND(2) );

준비과정2) 자료입력

INSERT INTO hr.test_interval VALUES (1, INTERVAL '123-2' YEAR(3) TO MONTH
,INTERVAL '4 5:12:10.21' DAY(3) TO SECOND (2)) ;

INSERT INTO hr.test_interval VALUES (2, INTERVAL '200' MONTH
,INTERVAL '4' DAY (3)) ; <--16년8개월

INSERT INTO hr.test_interval VALUES (3, INTERVAL '99' YEAR
, INTERVAL '5:12:10.22' HOUR TO SECOND(2)) ;

INSERT INTO hr.test_interval VALUES (4, INTERVAL '99' YEAR
, INTERVAL '10:30' HOUR TO MINUTE) ;

INSERT INTO hr.test_interval VALUES (5, INTERVAL '99' YEAR
, INTERVAL '240' HOUR ) ;

INSERT INTO hr.test_interval(t_id, idts)
VALUES (6, INTERVAL '18' DAY(2));

INSERT INTO hr.test_interval VALUES (7, INTERVAL '123-2' YEAR(3) TO MONTH
, INTERVAL '5 5:12' DAY TO MINUTE);

INSERT INTO hr.test_interval VALUES (8, INTERVAL '123' YEAR(3)
,INTERVAL '400' DAY(3) ) ;

INSERT INTO hr.test_interval VALUES (9, INTERVAL '123' MONTH
,INTERVAL '400' HOUR ) ;

INSERT INTO hr.test_interval VALUES (10, INTERVAL '123' MONTH
,INTERVAL '400' MINUTE ) ;

INSERT INTO hr.test_interval VALUES (11, INTERVAL '123-30' YEAR(3) MONTH
,INTERVAL '400' MINUTE ) ;
INSERT INTO hr.test_interval VALUES (12, INTERVAL '123-30' YEAR(3) MONTH
*
ERROR at line 1:
ORA-01867: the interval is invalid

INSERT INTO hr.test_interval VALUES (12, INTERVAL '123-11' YEAR(3) TO MONTH
,INTERVAL '30:400' HOUR TO MINUTE );
,INTERVAL '30:400' HOUR TO MINUTE )
*
ERROR at line 2:
ORA-01851: minutes must be between 0 and 59

INSERT INTO hr.test_interval VALUES (12, INTERVAL '123-11' YEAR(3) TO MONTH
,INTERVAL '30:59' HOUR TO MINUTE );

INSERT INTO hr.test_interval VALUES (13, '123-11'
,'400 10:10:10.22');

준비과정3) 입력자료 확인

SQL> col t_id format 999
SQL> col iytm format a10
SQL> col idts format a20
SQL>
SQL> SELECT * FROM test_interval;

## 기간 데이터형식의 활용

SQL> SELECT sysdate + IYTM, sysdate + IDTS FROM test_interval;

--------------------------------------------------------------------------------
5-25

EXTRACT : 날짜 데이터에서 형식요소에 해당하는 값을 추출해서
숫자값을 반환합니다.

SQL> SELECT sysdate, EXTRACT (YEAR FROM SYSDATE) "연도" FROM dual ;

SYSDATE 연도
-------- ----------
07/11/14 2007

SQL> SELECT sysdate, EXTRACT (YEAR FROM SYSDATE)+3000 "연도" FROM dual ;

SYSDATE 연도
------------------- ----------
08/01/18 5008

SQL> SELECT sysdate, EXTRACT (YEAR FROM SYSDATE) FROM dual ;

SYSDATE EXTRACT(YEARFROMSYSDATE)
-------- ------------------------
07/11/14 2007

SQL>
SQL> SELECT sysdate, EXTRACT (MONTH FROM SYSDATE) FROM dual ;

SYSDATE EXTRACT(MONTHFROMSYSDATE)
-------- -------------------------
07/11/14 11

SQL>
SQL> SELECT sysdate, EXTRACT (DAY FROM SYSDATE) FROM dual ;

SYSDATE EXTRACT(DAYFROMSYSDATE)
-------- -----------------------
07/11/14 14

SQL>
SQL> SELECT sysdate, EXTRACT (HOUR FROM SYSDATE) FROM dual ;
SELECT sysdate, EXTRACT (HOUR FROM SYSDATE) FROM dual
*
ERROR at line 1:
ORA-30076: invalid extract field for extract source

SQL> SELECT sysdate, EXTRACT (HOUR FROM CURRENT_DATE) FROM dual ;
SELECT sysdate, EXTRACT (HOUR FROM CURRENT_DATE) FROM dual
*
ERROR at line 1:
ORA-30076: invalid extract field for extract source

SQL> SELECT sysdate, EXTRACT (HOUR FROM CURRENT_TIMESTAMP)
,EXTRACT (HOUR FROM LOCALTIMESTAMP) FROM dual ;

SYSDATE EXTRACT(HOURFROMCURRENT_TIMESTAMP) EXTRACT(HOURFROMLOCALTIMESTAMP)
-------- ---------------------------------- -------------------------------
07/11/14 2 11

SQL>
SQL> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS' ;

SQL> ALTER SESSION SET nls_timestamp_format='YYYY-MM-DD HH24:MI:SSXFF' ;

SQL> ALTER SESSION SET nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SSXFF TZR' ;

SQL> SELECT sysdate, EXTRACT (HOUR FROM CURRENT_TIMESTAMP),
EXTRACT (HOUR FROM LOCALTIMESTAMP)
FROM dual ;

SYSDATE EXTRACT(HOURFROMCURRENT_TIMESTAMP)
------------------- ----------------------------------
EXTRACT(HOURFROMLOCALTIMESTAMP)
-------------------------------
2007-11-14 11:12:49 2
11

SQL> SELECT EXTRACT (HOUR FROM (TO_TIMESTAMP_TZ
   ('2007-11-14 10:00:00 +9:00'
   ,'YYYY-MM-DD HH24:MI:SS TZR')
)
) AS "HOUR"
   FROM    dual ;

HOUR
----------
1

SQL> SELECT sysdate, EXTRACT (MINUTE FROM CURRENT_TIMESTAMP),
EXTRACT (MINUTE FROM LOCALTIMESTAMP)
FROM dual ;

SYSDATE EXTRACT(MINUTEFROMCURRENT_TIMESTAMP)
------------------- ------------------------------------
EXTRACT(MINUTEFROMLOCALTIMESTAMP)
---------------------------------
2007-11-14 11:13:32 13
13

SQL> SELECT sysdate, EXTRACT (SECOND FROM CURRENT_TIMESTAMP),
EXTRACT (SECOND FROM LOCALTIMESTAMP)
FROM dual ;

SYSDATE EXTRACT(SECONDFROMCURRENT_TIMESTAMP)
------------------- ------------------------------------
EXTRACT(SECONDFROMLOCALTIMESTAMP)
---------------------------------
2007-11-14 11:13:47 47.433
47.433

SQL> SELECT sysdate, EXTRACT (TIMEZONE_HOUR FROM CURRENT_TIMESTAMP),
   EXTRACT (TIMEZONE_HOUR FROM LOCALTIMESTAMP)
   FROM    dual ;
   EXTRACT (TIMEZONE_HOUR FROM LOCALTIMESTAMP)
   *
ERROR at line 2:
ORA-30076: invalid extract field for extract source

SQL> SELECT sysdate, EXTRACT (TIMEZONE_HOUR FROM CURRENT_TIMESTAMP)
FROM dual ;

SYSDATE EXTRACT(TIMEZONE_HOURFROMCURRENT_TIMESTAMP)
------------------- -------------------------------------------
2007-11-14 11:14:50 9

SQL> SELECT sysdate, EXTRACT (TIMEZONE_REGION FROM CURRENT_TIMESTAMP)
FROM dual ;

SYSDATE
-------------------
EXTRACT(TIMEZONE_REGIONFROMCURRENT_TIMESTAMP)
----------------------------------------------------------------
2007-11-14 11:15:44
UNKNOWN

SELECT EXTRACT(TIMEZONE_REGION FROM FROM_TZ(TIMESTAMP '2000-03-28 08:00:00'
,'Australia/North')
)
FROM DUAL ;

select
extract ( hour from interval '4 5:12:10.222' day to second(3) ) 시,
extract ( minute from interval '4 5:12' day to minute ) 분
from dual;

SQL> create table a (a1 interval year(3) to month);
SQL> insert into a values ('100-10');
SQL> insert into a values ('10-10');
SQL> insert into a values ('10-1');

SQL> select * from a;

A1
----------------------
+100-10
+010-10
+010-01

SQL> select extract(year from a1) from a;

EXTRACT(YEARFROMA1)
-------------------
100
10
10

SQL> select extract(month from a1) from a;

EXTRACT(MONTHFROMA1)
--------------------
10
10
1

--------------------------------------------------------------------------------
5-26

SELECT distinct tzname FROM v$timezone_names
ORDER BY 1 ;

--------------------------------------------------------------------------------
5-28

SQL> SELECT FROM_TZ (timestamp '2007-11-14 09:00:00.222', '+3:00') from dual ;

FROM_TZ(TIMESTAMP'2007-11-1409:00:00.222','+3:00')
---------------------------------------------------------------------------
2007-11-14 09:00:00.222000000 +03:00

SQL> SELECT FROM_TZ (timestamp '2007-11-14 09:00:00.222', 'ASIA/SEOUL') from dual ;

FROM_TZ(TIMESTAMP'2007-11-1409:00:00.222','ASIA/SEOUL')
---------------------------------------------------------------------------
2007-11-14 09:00:00.222000000 ASIA/SEOUL

SQL> SELECT FROM_TZ (timestamp '2007-11-14 09:00:00.222', 'ASI/SEOUL') from dual ;
SELECT FROM_TZ (timestamp '2007-11-14 09:00:00.222', 'ASI/SEOUL') from dual
*
ERROR at line 1:
ORA-01882: timezone region not found

--------------------------------------------------------------------------------
5-29

SQL> conn hr/hr

create table a1
(test1 timestamp with time zone
,test2 timestamp
) ;

insert into a1 values ('2000-12-01 11:00:00 +9:00'
, '2000-12-01 11:00:00') ;

insert into a1 values ('01-DEC-07 11:00:00 +9:00'
, '01-DEC-07 11:00:00') ;

col test1 format a33
col test2 format a30

select * from a1 ;

TEST1 TEST2
--------------------------------- ------------------------------
2000-12-01 11:00:00.000000 +09:00 2000-12-01 11:00:00.000000
0001-12-07 11:00:00.000000 +09:00 0001-12-07 11:00:00.000000 <--허억 년도랑 날이 ????

insert into a1
values (TO_TIMESTAMP_TZ('01-DEC-07 11:00:00 +9:00'
   ,'DD-MON-RR HH24:MI:SS TZR')
   ,TO_TIMESTAMP('01-DEC-07 11:00:00','DD-MON-RR HH24:MI:SS')
   ) ;

select * from a1 ;

--> 처리된 것은 제대로 입력됩니다.

--------------------------------------------------------------------------------
5-30
conn hr/hr
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS' ;

ALTER SESSION SET nls_timestamp_format='YYYY-MM-DD HH24:MI:SSXFF' ;

ALTER SESSION SET nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SSXFF TZR' ;

SELECT sysdate, sysdate+TO_YMINTERVAL('1-0') from dual ;
SELECT sysdate, sysdate+TO_YMINTERVAL('0-9') from dual ;
SELECT sysdate, sysdate+TO_YMINTERVAL('1-1') from dual ;

--------------------------------------------------------------------------------
5-31

SELECT sysdate, sysdate+TO_DSINTERVAL('1 00:00:00.00') from dual ;
SELECT sysdate, sysdate+TO_DSINTERVAL('0 01:00:00.00') from dual ;
SELECT sysdate, sysdate+TO_DSINTERVAL('0 00:30:00.00') from dual ;
SELECT sysdate, sysdate+TO_DSINTERVAL('0 00:0:10.00') from dual ;
SELECT sysdate, sysdate+TO_DSINTERVAL('1 00:30:10.00') from dual ;

SQL>

--------------------------------------------------------------------------------

================================================================================

## Chapter 06 ##

6-3

SQL> select last_name
   from hr.employees
   where (employee_id, department_id) =
(select employee_id, department_id
   from hr.employees
   where last_name = 'Abel') ;

LAST_NAME
---------------
Abel

SQL> select last_name
   from hr.employees
   where (employee_id, department_id) =
(select employee_id, department_id
   from hr.employees
   where last_name = 'King') ;
   (select employee_id, department_id
*
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

--------------------------------------------------------------------------------
6-5
(Pairwise comparison)

SELECT employee_id, manager_id, department_id
FROM hr.employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
   FROM hr.employees
   WHERE employee_id IN (174, 199) )
AND    employee_id NOT IN (174,199)
order by 1,2 ;

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
174 149 80
199 124 50

manager_id = 149 and department_id = 80 또는
manager_id = 124 and department_id = 50

6-6
(Non-pairwise comparison)

SELECT employee_id, manager_id, department_id
FROM hr.employees
WHERE manager_id    IN (SELECT manager_id
   FROM hr.employees
   WHERE employee_id IN (174, 199) )
and    department_id IN (SELECT department_id
   FROM hr.employees
   WHERE employee_id IN (174, 199) )
AND    employee_id NOT IN (174,199)
order by 1,2 ;

manager_id = 149 or manager_id = 124
department_id = 80 or department_id = 50

manager_id = 149 and department_id = 80 또는
manager_id = 149 and department_id = 50 또는
manager_id = 124 and department_id = 80 또는
manager_id = 124 and department_id = 50 또는

SQL> SELECT employee_id, manager_id, department_id
2 FROM hr.employees
3 WHERE employee_id IN (174, 199) ;

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
174 149 80
199 124 50

--------------------------------------------------------------------------------
6-8

SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e INNER JOIN departments d
ON (e.department_id = d.department_id)
ORDER BY (SELECT department_name
FROM departments d1
WHERE d1.department_id = e.department_id) ;

SELECT employee_id, last_name, department_id
FROM employees e
ORDER BY (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id) ;

--------------------------------------------------------------------------------
6-12

SELECT last_name, salary, department_id
FROM employees outer
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = outer.department_id)
ORDER BY 3,1 ;

SELECT last_name, salary, department_id
FROM hr.employees
ORDER BY 3, 1 ;

(INLINE VIEW)

select e.last_name, e.salary, e.department_id, a.DEPT_AVG
from employees e INNER JOIN
(SELECT department_id
,ROUND(avg(salary),0) as dept_avg
FROM employees
GROUP BY department_id) a
ON e.department_id = a.department_id
AND e.salary > a.dept_avg
order by 3,1;

--------------------------------------------------------------------------------
6-13

SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);

SELECT * FROM job_history order by 1 ;

--------------------------------------------------------------------------------
6-15

SELECT employee_id, last_name, job_id, manager_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
FROM employees
WHERE manager_id = outer.employee_id)
order by 1;

SELECT employee_id, last_name, job_id, manager_id
FROM employees outer
WHERE employee_id IN (SELECT distinct manager_id
FROM hr.employees )
order by 1 ;

(설명)
SELECT employee_id, last_name, job_id, department_id
from hr.employees
order by 1 ;

SELECT employee_id, last_name, manager_id
from hr.employees
order by 3 ;

SELECT employee_id, last_name, job_id, manager_id
FROM employees outer
WHERE NOT EXISTS ( SELECT 'X'
FROM employees
WHERE manager_id = outer.employee_id)
order by 1 ;

--------------------------------------------------------------------------------
6-17

create table emp16
as
select employee_id, last_name, salary, department_id
from hr.employees
order by 1 ;

alter table emp16
add (department_name varchar2(30)) ;

select * from emp16 ;

select department_id, department_name
from hr.departments ;

update hr.emp16 e16
set department_name = (SELECT department_name
FROM hr.departments
WHERE department_id = e16.department_id) ;

delete from emp16
where employee_id IN (select employee_id
FROM hr.emp_history ) ;

create table emp_history
as
select * from emp16
where department_id IN ( 80 , 50 ) ;

delete from emp16 a
where employee_id = (select employee_id from emp_history
where employee_id = a.employee_id) ;

--------------------------------------------------------------------------------
6-24

WITH
dept_costs AS (
   SELECT d.department_name, SUM(e.salary) AS dept_total
   FROM employees e, departments d
   WHERE e.department_id = d.department_id
   GROUP BY d.department_name),
avg_cost AS (
   SELECT SUM(dept_total)/COUNT(*) AS dept_avg
   FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT dept_avg FROM avg_cost)
ORDER BY department_name;

SELECT *
FROM    ( SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name)
WHERE dept_total > (SELECT dept_avg FROM (
(SELECT SUM(dept_total)/COUNT(*)
AS dept_avg
   FROM (
   SELECT d.department_name, SUM(e.salary) AS dept_total
   FROM employees e, departments d
   WHERE e.department_id = d.department_id
   GROUP BY d.department_name)))
ORDER BY department_name;

================================================================================
#############
## 9th Day ##
#############

## Chapter 07 ##

7-11

SELECT LPAD(last_name, LENGTH(last_name)+(Level*4)-4, '-') AS org_chart
FROM employees
START WITH last_name = 'King'
CONNECT BY prior employee_id = manager_id;

King Level 1 4+4-4=4
Kochhar Level 2 7+8-4=7+4

LPAD(last_name, 40, '-')

--------------------------------------------------------------------------------
(문법설명)
SELECT LEVEL, employee_id, manager_id,
LPAD(last_name, LENGTH(last_name)+(Level*4)-4, '-') AS org_chart
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;

START WITH last_name = 'King' <-- 시작하는 행을 명시하는 조건

CONNECT BY PRIOR employee_id = manager_id; <--방향

CONNECT BY employee_id = PRIOR manager_id
-->CONNECT BY PRIOR manager_id = employee_id

(참고)
SQL> select employee_id, last_name, manager_id
from hr.employees
order by 1 ;

--------------------------------------------------------------------------------
7-9
(교재)
SELECT Prior last_name, 'is commander of', last_name
FROM employees
START WITH manager_id is null
CONNECT BY prior employee_id = manager_id;

SELECT last_name, 'is commander of', Prior last_name
FROM employees
START WITH manager_id is null
CONNECT BY prior employee_id = manager_id;

SELECT employee_id, last_name, manager_id, PRIOR last_name
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id
ORDER BY 1 ;

(참고)
SQL> select employee_id, last_name, manager_id
from hr.employees
order by 1 ;

SELECT employee_id, last_name, manager_id, PRIOR last_name
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR manager_id = employee_id
ORDER BY 1 desc;

--------------------------------------------------------------------------------
7-13

SELECT LPAD(last_name,
LENGTH(last_name)+(Level*4)-4, '-')
AS org_chart
FROM employees
START WITH last_name = 'Kochhar'
CONNECT BY prior employee_id = manager_id;

SELECT LPAD(last_name,
LENGTH(last_name)+(Level*4)-4, '-')
AS org_chart
FROM employees
WHERE last_name <> 'Higgins'
START WITH last_name = 'Kochhar'
CONNECT BY prior employee_id = manager_id;

SELECT LPAD(last_name,
LENGTH(last_name)+(Level*4)-4, '-')
AS org_chart
FROM employees
START WITH last_name = 'Kochhar'
CONNECT BY prior employee_id = manager_id
AND last_name <> 'Higgins';

================================================================================

##############
## 10th Day ##
##############

## Chapter 08 ##

8-9

SELECT first_name, last_name
FROM hr.employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$') ;

Steven

Stephen

8-10

SELECT street_address, REGEXP_INSTR(street_address,'[^[:alpha:]]') AS "RESULT"
FROM hr.locations
WHERE REGEXP_INSTR(street_address,'[^[:alpha:]]')>1 ;

[^[:alpha:]]

'^abc'

'^[abc]' '^(a|b|c)'
'^(abc)'
'^abc'

---------------------------------------------------------------------------
8-11

col Road format a50
set linesize 200

SELECT street_address,REGEXP_SUBSTR(street_address,' [^ ]+ ') AS "Road"
FROM locations ;

8-12

SQL> select regexp_replace (country_name, '(.)', '\1 ') "RESULT"
2 from hr.countries ;
-------------------------------------------------------------------
A r g e n t i n a
A u s t r a l i a

Argentina
Australia

select regexp_replace (country_name, '(..)', '\1 ') "RESULT"
from hr.countries ;
------------------
Ar ge nt in a
Au st ra li a

Argentina
Australia

select regexp_replace (country_name, '(.)', '\1 ') "RESULT"
from hr.countries ;

-----------------------------

A r g e n t i n a
A u s t r a l i a

Argentina
Australia

select regexp_replace (country_name, '(.)', '\1*') "RESULT"
from hr.countries ;

Posted by 아돌

트랙백 0, | 댓글 0

2008-08-16~17

ORACLE 10g / 2008/08/23 14:27

## Chapter 7 ##

{1,2,3,2}
{3,4,5,5}

합 : {1,2,3,4,5} --> UNION UNION ALL {1,2,3,2,3,4,5,5}
교 : {3} --> INTERSECT
차 : {1,2} --> MINUS

--------------------------------------------------------------------------------
SQL> SELECT employee_id, job_id
   FROM employees {1,2,3,2}
   UNION
   SELECT employee_id, job_id
   FROM job_history ; {3,4,5,5}

{1,2,2,3,4,5,5}

SQL> SELECT employee_id, job_id
   FROM employees
   UNION ALL
   SELECT employee_id, job_id
   FROM job_history
   ORDER BY 1 ;

SQL> SELECT employee_id, last_name
   FROM employees
   UNION ALL
   SELECT employee_id, job_id
   FROM job_history
   ORDER BY last_name ;

SQL> SELECT employee_id, job_id
   FROM employees
   UNION
   (SELECT employee_id, job_id
FROM job_history
   MINUS
SELECT employee_id, job_id
FROM employees
WEHRE department_id=80) ;

--------------------------------------------------------------------------------
7-19

SQL> SELECT department_id, hire_date
   FROM employees
   UNION
   SELECT department_id, location_id
   FROM departments;
SELECT department_id, hire_date
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

SQL> SELECT department_id, TO_NUMBER(null) AS location_id, hire_date
   FROM employees
   UNION
   SELECT department_id, location_id, TO_DATE(null)
   FROM departments;

SQL> SELECT department_id, hire_date, null AS location_id
   FROM employees
   UNION
   SELECT department_id, null,location_id
   FROM departments;

------------------------------------
SQL> SELECT 'B', 3 a_dummy FROM dual
   UNION
   SELECT 'A', 1 FROM dual
   UNION
   SELECT 'C', 2 FROM dual ;

'    A_DUMMY
- ----------
A    1
B    3
C    2
------------------------------------
SQL> SELECT 'B', 3 a_dummy FROM dual
   UNION
   SELECT 'A', 1 FROM dual
   UNION
   SELECT 'C', 2 FROM dual
   ORDER BY a_dummy ;
ORDER BY a_dummy
   *
ERROR at line 6:
ORA-00904: "A_DUMMY": invalid identifier
------------------------------------
SQL> SELECT 'B', 3 a_dummy FROM dual
   UNION
   SELECT 'A', 1 a_dummy FROM dual
   UNION
   SELECT 'C', 2 a_dummy FROM dual
   ORDER BY a_dummy ;

'    A_DUMMY
- ----------
A    1
C    2
B    3
------------------------------------
SQL> SELECT 'B', 3 a_dummy FROM dual
   UNION
   SELECT 'A', 1 FROM dual
   UNION
   SELECT 'C', 2 FROM dual
   ORDER BY 2 ;

'    A_DUMMY
- ----------
A    1
C    2
B    3

sqlplus, isqlplus COLUMN 명령어

column last_name HEADING 'EMPLOYEE|NAME' JUSTIFY center
column salary format L999,999,999.0 JUSTIFY LEFT
column commission_pct NULL "인센티브없음"

------------------------------------------------------
## sqlplus 출력양식 예제 ##

SET pagesize 50
SET FEEDBACK OFF
TTITLE 'Employee|Report'
BTITLE 'Confidential'
BREAK ON job_id
COLUMN job_id HEADING 'Job|Category' JUSTIFY center
COLUMN last_name HEADING 'Employee'
COLUMN salary HEADING 'Salary' FORMAT $99,999.99
REM ** Insert SELECT statement
SELECT job_id, last_name, salary
FROM employees
WHERE salary < 15000
ORDER BY job_id, last_name
/
REM clear all formatting commands ...
SET FEEDBACK ON
COLUMN job_id CLEAR
COLUMN last_name CLEAR
COLUMN salary CLEAR
CLEAR BREAK
TTITLE OFF
BTITLE OFF
SET FEEDBACK ON

================================================================================
#############
## 5th Day ##
#############

## Chapter 8 - DML ##

--------------------------------------------------------------------------------
8-11 실습테이블(sales_reps) 생성

SQL> DROP TABLE hr.sales_reps ;

SQL> CREATE TABLE hr.sales_reps
   (id NUMBER(6)    PRIMARY KEY
   ,name VARCHAR2(30) NOT NULL
   ,salary NUMBER(8,2)
   ,commission_pct NUMBER(8,2)
) ;

INSERT INTO sales_reps (id, name, salary, commission_pct)
(SELECT employee_id, last_name, salary, commission_pct
FROM hr.employees
WHERE job_id LIKE '%REP%') ;
--------------------------------------------------------------------------------
8-11 설명부분을 위한 실습테이블(copy_emp)생성

SQL> DROP TABLE hr.copy_emp ;

SQL> CREATE TABLE hr.copy_emp
   AS
   SELECT * FROM hr.employees
   WHERE 1=2;

SQL> INSERT INTO hr.copy_emp
SELECT * FROM hr.employees ;

--------------------------------------------------------------------------------
8-16 실습테이블(copy_emp) 생성

SQL> DROP TABLE hr.copy_emp ;

SQL> CREATE TABLE hr.copy_emp
AS
SELECT * FROM hr.employees ;

--------------------------------------------------------------------------------
8-21

TRUNCATE TABLE 명령어 : "DELETE TABLE" SYSTEM 권한이 필요.

1. DDL 명령어 : autocommit --> rollback (X)

2. 모든 데이터를 삭제 + 사용한 저장영역을 일부만 남겨놓고 할당해제합니다.

--------------------------------------------------------------------------------
8-22 Using a Subquery in an INSERT Statements

SQL> set linesize 200

## 실습테이블(hr.emp) 생성 :

SQL> CREATE TABLE hr.emp
as
SELECT employee_id, last_name, email, hire_date, job_id,
salary, department_id
FROM hr.employees ;

SQL> INSERT INTO (SELECT *
FROM emp
WHERE department_id = 10 )
VALUES (99999,'Taylor','DTAYLOR',
TO_DATE('07-JUN-99','DD-MON-RR'),'ST_CLERK', 5000, 10);

SQL> INSERT INTO emp
values (99999,'Taylor','DTAYLOR',
TO_DATE('07-JUN-99','DD-MON-RR','nls_date_language=AMERICAN')
,'ST_CLERK', 5000, 10) ; <-- OK

SQL> INSERT INTO (SELECT *
FROM emp
WHERE department_id = 10 WITH CHECK OPTION )
values (99999,'Taylor','DTAYLOR',
TO_DATE('07-JUN-99','DD-MON-RR','nls_date_language=AMERICAN')
,'ST_CLERK', 5000, 10) ;

SQL> INSERT INTO emp
values (99999,'Taylor','DTAYLOR',
TO_DATE('07-JUN-99','DD-MON-RR','nls_date_language=AMERICAN')
,'ST_CLERK', 5000, 20) ; <--X

SQL> INSERT INTO hr.emp (employee_id, last_name, email, hire_date
   ,job_id, salary, department_id)
   VALUES (99999,'Taylor','DTAYLOR',
   TO_DATE('07-JUN-99','DD-MON-RR'),'ST_CLERK', 5000, 50);

SQL> ROLLBACK ;

1 개의 행이 만들어졌습니다.

SQL> INSERT INTO (SELECT *
2    FROM emp
3    WHERE department_id = 10 WITH CHECK OPTION )
4 values (99999,'Taylor','DTAYLOR',
5 TO_DATE('07-JUN-99','DD-MON-RR','nls_date_language=AMERICAN')
6    ,'ST_CLERK', 5000, 20) ;
   TO_DATE('07-JUN-99','DD-MON-RR','nls_date_language=AMERICAN')
   *
5행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

SQL>

--------------------------------------------------------------------------------
8-24 세션 2개를 열고 실습하세요.

(세션1)

SQL> update hr.departments
set department_name= 'Oracle'
where department_id = 270 ;

1 row updated.

SQL> select department_id, department_name
from hr.departments ; --(1)

(세션2)

SQL> select department_id, department_name
from hr.departments ; --(2)

(1)과 (2)의 결과에 차이가 납니다.

(세션1)

SQL> commit ;

(세션2)

SQL> select department_id, department_name
from hr.departments ; --(3)

(1)과 (3)의 결과가 같지요..
--------------------------------------------------------------------------------

세션1)

SQL> update hr.departments
set department_name= 'Payroll'
where department_id = 270 ;

1 row updated.

세션2)

SQL> update hr.departments
set department_name= 'Payroll'
where department_id = 270 ; <-- 실행이 멈춤 (왜? - 세션1의 LOCK 때문에.)

세션3)

SQL> update hr.departments
set department_name= 'Payroll'
where department_id = 260 ; <--작업하는 row가 다르므로 가능.

1 row updated.

SQL>
--------------------------------------------------------------------------------
(참고하세요)

Explicit Locking 의 예 (Manual Data Locking 확인실습)
---------------------------------------------------------
(준비작업)
sqlplus scott/tiger
SQL> grant SELECT on emp to hr;
SQL> grant SELECT on dept to hr;

SQL> conn hr/hr
SQL> CREATE TABLE emp AS SELECT * FROM scott.emp;

SQL> CREATE TABLE dept AS SELECT * FROM scott.dept;
---------------------------------------------------------

세션 1 sqlplus hr/hr
SQL> SELECT * FROM dept;
---------------------------------------------------------
세션 2 sqlplus hr/hr
SQL> SELECT * FROM dept;
SQL> COMMIT;
SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE NAME 'aaa'; <- 대표적인 Explicit Locking
SQL> SELECT * FROM dept;
---------------------------------------------------------
세션 1
SQL> INSERT INTO dept values ( 91, '신상현','서울')
SQL> SELECT * FROM dept; <--입력한 결과가 보입니다.
---------------------------------------------------------
세션 2
SQL> SELECT * FROM dept; <--세션1에서 입력된 결과가 없죠..
---------------------------------------------------------
세션 1
SQL> commit;
SQL> SELECT * FROM dept; <--입력한 결과가 보입니다. <--입력한 결과 영구 저장
---------------------------------------------------------
세션 2
SQL> SELECT * FROM dept; <--헉..아직도 세션1의 입력행이 보이질 않네요..
이것이 트랜잭션에 LOCK을 걸었기 때문이죠..
SQL> commit ; <-- 이것은 수동으로 지정한 LOCK을 해제하겠다는 뜻이죠..
SQL> SELECT * FROM dept; <-- 이제는 세션1에서 입력된 결과가 보입니다.
---------------------------------------------------------

다른 수동 LOCK 명령어의 예

SQL> SET TRANSACTION NAME 'aaa';
SQL> SET TRANSACTION READ ONLY NAME 'aaa';
SQL> SET TRANSACTION READ WRITE NAME 'aaa';
SQL> SET TRANSACTION ISOLATION LEVEL READ COMMITTED NAME 'aaa';

================================================================================
================================================================================
## Chapter 9 ##

--------------------------------------------------------------------------------
9-5

CREATE TABLE hr.emps
(e_id NUMBER(4)
,name VARCHAR2(4) CONSTRAINT nn_name_emps NOT NULL
,address VARCHAR2(30) DEFAULT '서울'
,salary NUMBER(8)
,jumin VARCHAR2(13) NOT NULL
,deptid NUMBER(4)
,hire_date TIMESTAMP(3)
,CONSTRAINT pk_e_id_emps PRIMARY KEY(e_id)
,CONSTRAINT uk_jumin_emps UNIQUE(jumin)
,CONSTRAINT ck_salary_emps CHECK(salary > 0)
,CONSTRAINT fk_emps_departments FOREIGN KEY(deptid)
REFERENCES hr.departments(department_id) ON DELETE SET NULL
);
--------------------------------------------------------------------------------
9-9

SELECT employee_id, last_name, ROWID
FROM hr.employees
ORDER BY 3 ;

User Table (예,employees)에 한 행을 입력하면,
오라클은 이 row가 데이터베이스 전체에서 유일하다는 식별자 정보로
ROWID 하나를 생성해서 부여.--> 시스템데이터로 저장.

AAAMg6 AAF AAAABU AAA

obj# relative file# block# row 포인트 정보

한 행의 물리적인 위치정보를 제공.

--------------------------------------------------------------------------------
9-11

Oracle 9i 부터 다음의 날짜시간관련 Data Type이 새로이 추가되었습니다.

(1) TIMESTAMP

- 기존의 DATE 표현형태에 추가하여 초를 최대 10^-9 초까지 표현 가능
- 디폴트로 6자리의 밀리세컨드를 표현합니다.

(2) TIMESTAMP WITH TIME ZONE

- TIMESTAMP 데이터타입의 표현형식에 GMT 기준의 시차를 같이 나타냅니다.

(3) TIMESTAMP WITH LOCAL TIME ZONE

   - Database가 운영중인 서버의 시간을 기준으로 입력
   - 저장된 데이터가, 시차가 있는 다른 지역의 Client에 의하여 호출될 때,
   데이터베이스의 시간 기준이 아닌 Client 지역의 시간 기준으로 자동 변환되어 출력됩니다.

(4) INTERVAL YEAR TO MONTH : 기간을 나타내며, 년-개월까지 표현됩니다.

(5) INTERVAL DAY TO SECOND : 기간을 나타내며, 일~초까지 표현됩니다.

예01) TIMESTAMP 자료형 이용

CREATE TABLE timetest
(t_id number(3)
,t_ts timestamp(7) DEFAULT SYSDATE
,t_tstz timestamp(3) with time zone DEFAULT SYSDATE
,t_tsltz timestamp with local time zone DEFAULT SYSDATE
);

SQL> INSERT INTO timetest(t_id) VALUES (01);

set linesize 200
col t_ts format a30
col t_tstz format a31
col t_tsltz format a30
SQL>
SQL> SELECT * FROM timetest ;

T_ID T_TS T_TSTZ T_TSLTZ
---- ------------------------------ ------------------------------- ------------------------------
1 07/11/09 11:37:23.0000000 07/11/09 11:37:23.000000 +09:00 07/11/09 11:37:23.000000

SQL> SELECT parameter, value FROM nls_session_parameters ;

PARAMETER VALUE
----------------------------------- -----------------------------------
NLS_LANGUAGE    AMERICAN
NLS_TERRITORY KOREA
....
....
NLS_DATE_FORMAT RRRR/MM/DD HH24:MI:SS
NLS_DATE_LANGUAGE AMERICAN
....
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT    RR/MM/DD HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT    HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR/MM/DD HH24:MI:SSXFF TZR
....
....
....
17 rows selected.

SQL> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD HH24:MI:SSXFF' ;

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SSXFF' ;

SQL> set linesize 200
SQL> col t_ts format a30
SQL> col t_tstz format a31
SQL> col t_tsltz format a30
SQL>
SQL> SELECT * FROM timetest ;

T_ID T_TS T_TSTZ T_TSLTZ
---- ------------------------------ ------------------------------- --------------------------
1 2007/11/09 11:37:23.0000000 07/11/09 11:37:23.000000 +09:00 2007/11/09 11:37:23.000000

9-14

예02) INTERVAL Data Type의 활용

준비과정1) 테이블 생성

CREATE TABLE test_interval
(t_id NUMBER(3)
,iytm INTERVAL YEAR(3) TO MONTH
,idts INTERVAL DAY(3) TO SECOND(2) );

준비과정2) 자료입력 - 데이터입력이 좀 번잡합니다.

INSERT INTO test_interval VALUES (1, INTERVAL '123-2' YEAR(3) TO MONTH
,INTERVAL '4 5:12:10.21' DAY(3) TO SECOND (2)) ;

INSERT INTO test_interval VALUES (2, INTERVAL '200' MONTH
, INTERVAL '4' DAY (3)) ; <--16년8개월

INSERT INTO test_interval VALUES (3, INTERVAL '99' YEAR
, INTERVAL '5:12:10.22' HOUR TO SECOND(2)) ;

INSERT INTO test_interval VALUES (4, INTERVAL '99' YEAR
, INTERVAL '10:30' HOUR TO MINUTE) ;

INSERT INTO test_interval VALUES (5, INTERVAL '99' YEAR
, INTERVAL '240' HOUR ) ;

INSERT INTO test_interval(t_id, idts) VALUES (2, INTERVAL '18' DAY(2));

INSERT INTO test_interval
VALUES (3, INTERVAL '123-2' YEAR(3) TO MONTH, INTERVAL '5:12' HOUR TO MINUTE);

INSERT INTO test_interval
VALUES (4, INTERVAL '123-2' YEAR(3) TO MONTH, INTERVAL '5 5:12' DAY TO MINUTE);

준비과정3) 입력자료 확인

col t_id format 999
col iytm format a10
col idts format a20
SQL>
SQL> SELECT * FROM test_interval;

T_ID IYTM IDTS
---- ---------- --------------------
   1 +123-02    +004 05:12:10.21
   2 +016-08    +004 00:00:00.00
   3 +099-00    +000 05:12:10.22
   4 +099-00    +000 10:30:00.00
   5 +099-00    +010 00:00:00.00

## 기간 데이터형식의 활용

SQL> SELECT sysdate,sysdate + IYTM, sysdate + IDTS FROM test_interval;

SYSDATE+IYTM SYSDATE+IDTS
------------------- -------------------
2131/01/09 12:02:06 2007/11/13 17:14:16
2024/07/09 12:02:06 2007/11/13 12:02:06
2106/11/09 12:02:06 2007/11/09 17:14:16
2106/11/09 12:02:06 2007/11/09 22:32:06
2106/11/09 12:02:06 2007/11/19 12:02:06

--------------------------------------------------------------------------------
9-18

DROP TABLE hr.emps purge;

SQL> select constraint_name from user_constraints
where table_name = 'EMPS' ;

CONSTRAINT_NAME
--------------------
NN_NAME_EMPS
SYS_C005436
CK_SALARY_EMPS
PK_E_ID_EMPS
UK_JUMIN_EMPS
FK_EMPS_DEPARTMENTS

6 rows selected.

desc user_constraints
desc user_cons_columns

--------------------------------------------------------------------------------
ALTER TABLE hr.emps
MODIFY (name varchar2(30)) ;

SQL> ALTER TABLE hr.emps
2 MODIFY (name NOT NULL) ;

Table altered.

(세션1)
SQL> create table testa
(id number) ;

Table created.

SQL> insert into testa values (10) ;

1 row created.

(다른세션)
SQL> drop table hr.testa ;
drop table hr.testa
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

(세션1)
SQL> drop table testa ;

Table dropped.

SQL>

--------------------------------------------------------------------------------
9-33

CREATE TABLE hr.dept90
AS
   SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
   FROM hr.employees
   WHERE department_id = 90 ;

SQL> CREATE TABLE hr.dept80
2 AS
3 SELECT employee_id, last_name, salary*12, hire_date
4 FROM hr.employees
5 WHERE department_id = 80 ;
SELECT employee_id, last_name, salary*12, hire_date
*
ERROR at line 3:
ORA-00998: must name this expression with a column alias

SQL> CREATE TABLE hr.dept80 (empno,ename, annsal, hiredate)
2 AS
3 SELECT employee_id, last_name, salary*12, hire_date
4 FROM hr.employees
5 WHERE department_id = 80 ;

Table created.

--------------------------------------------------------------------------------
## Chapter 10 ##

SQL> create sequence hr.dept_deptid_seq1
   increment by 10
   start with 300
   maxvalue 9999
   nocache
   nocycle ;

SQL> create sequence hr.dept_deptid_seq2
   increment by 10
   start with 300
   maxvalue 9999
   cache 10
   nocycle ;

SQL> select sequence_name, last_number
from user_sequences ;

SEQUENCE_NAME    LAST_NUMBER
------------------------------ -----------
DEPT_DEPTID_SEQ2 300
DEPT_DEPTID_SEQ1 300
LOCATIONS_SEQ 3300
DEPARTMENTS_SEQ    280
EMPLOYEES_SEQ    207

(서버에서 관리) (세션에서 정의)
DEPT_DEPTID_SEQ1.NEXTVAL DEPT_DEPTID_SEQ1.CURRVAL

(의미) -서버에서 다음에 쓸값 -세션에서 사용된 마지막값

300 X

(세션1)

SQL> SELECT DEPT_DEPTID_SEQ1.CURRVAL
FROM dual ;
SELECT DEPT_DEPTID_SEQ1.CURRVAL
*
ERROR at line 1:
ORA-08002: sequence DEPT_DEPTID_SEQ1.CURRVAL is not yet defined in this session

SQL> SELECT DEPT_DEPTID_SEQ1.NEXTVAL FROM dual ;

NEXTVAL
----------
300

SQL> SELECT DEPT_DEPTID_SEQ1.CURRVAL FROM dual ;

CURRVAL
----------
300

----------------------------------------------------------
(세션2)

SQL> SELECT DEPT_DEPTID_SEQ1.CURRVAL
FROM dual ;
SELECT DEPT_DEPTID_SEQ1.CURRVAL
*
ERROR at line 1:
ORA-08002: sequence DEPT_DEPTID_SEQ1.CURRVAL is not yet defined in this session

SQL> SELECT DEPT_DEPTID_SEQ1.NEXTVAL FROM dual ;

NEXTVAL
----------
310

SQL> SELECT DEPT_DEPTID_SEQ1.CURRVAL FROM dual ;

CURRVAL
----------
310
--------------------------------------------------------------------------------
(세션1)
SQL> SELECT DEPT_DEPTID_SEQ1.NEXTVAL FROM dual ;

NEXTVAL
----------
320
SQL> SELECT DEPT_DEPTID_SEQ1.NEXTVAL FROM dual ;

NEXTVAL
----------
330
SQL> SELECT DEPT_DEPTID_SEQ1.NEXTVAL FROM dual ;

NEXTVAL
----------
340

(세션2)

SQL> SELECT DEPT_DEPTID_SEQ1.CURRVAL FROM dual ;

CURRVAL
----------
310

SQL> SELECT DEPT_DEPTID_SEQ1.NEXTVAL FROM dual ;

NEXTVAL
----------
350

## Cache 옵션으로 인한 두 시퀀스의 거동 차이

SQL> select sequence_name, last_number
from user_sequences ;

SEQUENCE_NAME    LAST_NUMBER
------------------------------ -----------
DEPT_DEPTID_SEQ2 300
DEPT_DEPTID_SEQ1 360
LOCATIONS_SEQ 3300
DEPARTMENTS_SEQ    280
EMPLOYEES_SEQ    207

(세션1)
SQL> SELECT DEPT_DEPTID_SEQ2.NEXTVAL FROM dual ;

NEXTVAL
----------
310

SQL> select sequence_name, last_number
from user_sequences ;

SEQUENCE_NAME    LAST_NUMBER
------------------------------ -----------
DEPT_DEPTID_SEQ2 400 <--여기가 320이 아니라 400 이네요.
DEPT_DEPTID_SEQ1 360
LOCATIONS_SEQ 3300
DEPARTMENTS_SEQ    280
EMPLOYEES_SEQ    207

이렇듯 메모리에 정해진 갯수를 캐쉬시키면 성능이 좋아집니다.

## 인덱스 정보

SQL> select INDEX_NAME, INDEX_TYPE,UNIQUENESS
2 from user_indexes
3 where table_name = 'EMPLOYEES' ;

INDEX_NAME INDEX_TYPE    UNIQUENES
------------------------------ --------------------------- ---------
EMP_EMAIL_UK NORMAL    UNIQUE
EMP_EMP_ID_PK    NORMAL    UNIQUE
EMP_DEPARTMENT_IX    NORMAL    NONUNIQUE
EMP_JOB_IX NORMAL    NONUNIQUE
EMP_MANAGER_IX NORMAL    NONUNIQUE
EMP_NAME_IX    NORMAL    NONUNIQUE

6 개의 행이 선택되었습니다.

SQL> col column_name forma a20
SQL> col index_name forma a25
SQL>
SQL> select index_name, column_name, column_position
2 from user_ind_columns
3 where table_name = 'EMPLOYEES' ;

INDEX_NAME    COLUMN_NAME    COLUMN_POSITION
------------------------- -------------------- ---------------
EMP_EMAIL_UK    EMAIL    1
EMP_EMP_ID_PK EMPLOYEE_ID    1
EMP_DEPARTMENT_IX DEPARTMENT_ID    1
EMP_JOB_IX    JOB_ID 1
EMP_MANAGER_IX    MANAGER_ID 1
EMP_NAME_IX LAST_NAME    1
EMP_NAME_IX FIRST_NAME 2

7 개의 행이 선택되었습니다.
================================================================================
## Chapter 11 ##

desc user_tables
desc user_tab_columns
--------------------------------------------------------------------------------
col owner format a5
col r_owner format a5
set linesize 120
SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE
,SEARCH_CONDITION,R_OWNER,R_CONSTRAINT_NAME
from user_CONSTRAINTS
where table_name = 'EMPS001';

select constraint_name,column_name, position
from user_cons_columns
where table_name = 'EMPS001' ;

select constraint_name,column_name, position
from user_cons_columns
where table_name = 'EMPS001'
order by 1,3 ;

11-11
SQL> select column_name, data_type, data_length,
data_precision, data_scale, nullable
from user_tab_columns
where table_name='EMPLOYEES' ;

COMMENT ON TABLE hr.employees
IS '사원정보테이블' ;

COMMENT ON COLUMN hr.employees.employee_id
IS '사번' ;

desc user_tab_comments ;
desc user_col_comments ;

(테이블주석확인)
SELECT COMMENTS FROM user_tab_comments
WHERE table_name = 'EMPLOYEES'

(컬럼주석확인)
SELECT comments
FROM user_col_comments
WHERE table_name = 'EMPLOYEES'
AND column_name = 'EMPLOYEE_ID' ;

(기존주석)
employees테이블 기존 주석
employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.

employees.employee_id 컬럼 기존주석
Primary key of employees table.

(주석삭제)
COMMENT ON TABLE hr.employees
IS '' ;

COMMENT ON COLUMN hr.employees.employee_id
IS '' ;

#############
## 6th Day ##
#############

## Chapter 01 ##

1-6

C:\oracle> sqlplus system/oracle 또는 SQL> conn system/oracle

SQL> create user hr1 identified by hr1 ;

SQL> create user hr2 identified by hr2 ;

--------------------------------------------------------------------------------
1-5

## 데이터베이스에 존재하는 모든 시스템권한을 확인

SQL> desc system_privilege_map

SQL> select name from system_privilege_map
order by 1 ;

SQL> create trigger (trigger)
SQL> create procedure (procedure, function, package)
--------------------------------------------------------------------------------
1-8

SQL> conn system/oracle

(시스템권한 부여)

SQL> grant create session, create table, create view
to hr1, hr2 ;

(시스템권한 철회)

SQL> revoke create view
from hr1, hr2 ;

소프트웨어 설치 시, sample db 구성하면, 생성된 DB에 대하여
다음과 같은 db 관리자 계정이 생성됩니다.

sys : 166개 시스템 권한을 모두 가지고 있습니다.
system : DBA role

SQL> select * from session_privs ; <--로그인한 계정에게 부여된 SYSTEM 권한을 알려줍니다.

--------------------------------------------------------------------------------
1-10 role의 활용방법

Step1) role 생성

SQL> create role role1 ;

Step2) role에 권한을 부여합니다.

SQL> grant create session, create table to role1 ;

Step3) role을 유저에게 부여

SQL> grant role1 to hr1, hr2 ,hr ;

## 사용자에게 부여하는 권한을 role을 통해 관리하면 쉽게 일관적인 권한 부여나 철회가 가능합니다.

SQL> revoke create table from role1 ;

## role 삭제

SQL> drop role role1 ;

SQL> select * from session_roles ; <--로그인한 계정(세션)에게 부여된 role 확인

## ORACLE database를 생성하면,

다음과 같은 predefined role이 생성되어있습니다.

DBA , connect , resource

1-16

SQL> desc SESSION_PRIVS
SQL> desc SESSION_ROLES

SQL> desc ROLE_SYS_PRIVS
SQL> desc ROLE_TAB_PRIVS
SQL> desc USER_TAB_PRIVS_MADE
SQL> desc USER_TAB_PRIVS_RECD

SQL> conn system/oracle
SQL> select role from dba_roles ;

SQL> select role, privilege from ROLE_SYS_PRIVS
where role = 'DBA' ;

SQL> SELECT role, privilege FROM role_sys_privs
WHERE role IN ('CONNECT', 'RESOURCE') ;

--------------------------------------------------------------------------------
1-14

## 객채 권한 부여

SQL> grant select on hr.employees to hr1, hr2 ;

SQL> grant update (department_name, location_id) on hr.employees
to hr1, hr2 ;

SQL> grant all on hr,employees to hr1, hr2 ;

1-15

SQL> grant select, insert on hr.departments to scott with grant option ;

SQL> grant select on hr.departments to public ;

--------------------------------------------------------------------------------
1-18 객채 권한 철회

revoke select on employees from hr1 ;

================================================================================
## Chapter 02 ##

C:\oracle> sqlplus hr/hr 또는 SQL> conn hr/hr

DROP TABLE hr.dept80 ;

CREATE TABLE dept80
AS
   SELECT employee_id, last_name, salary*12 ANNSAL
   ,hire_date
   FROM hr.employees
   WHERE department_id = 80 ;

C:\oracle> sqlplus system/oracle 또는 SQL> conn system/oracle

SQL> purge dba_recyclebin
SQL> conn hr/hr

SQL> create table emp2
as
select * from hr.employees ;

SQL> alter table emp2
2 modify employee_id primary key ;

SQL> select constraint_name, constraint_type from user_constraints
2 where table_name = 'EMP2' ;

CONSTRAINT_NAME    C
------------------------------ -
SYS_C005428    C
SYS_C005429    C
SYS_C005430    C
SYS_C005431    C
SYS_C005432    P

SQL> alter table emp2
2 rename constraint SYS_C005432 to pk_emp2 ;

SQL> select constraint_name, constraint_type from user_constraints
2 where table_name = 'EMP2' ;

CONSTRAINT_NAME    C
------------------------------ -
SYS_C005428    C
SYS_C005429    C
SYS_C005430    C
SYS_C005431    C
PK_EMP2    P

SQL> alter table emp2
2 modify email constraint uk_email_emp2 unique ;

SQL> create table emps13_01
   as
   select * from hr.employees
   where department_id = 10 ;

SQL> desc emps13_01 ;
Name    Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID    NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID    NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID    NUMBER(4)

SQL> alter table hr.emps13_01
add constraint pk_emps13_01 primary key (employee_id) ;

SQL> select constraint_name from user_cons_columns
where table_name='EMPS13_01';

SQL> alter table hr.emps13_01
drop constraint pk_emps13_01 ;

SQL> alter table hr.emps13_01
add constraint pk_emps13_01 primary key (employee_id) ;

SQL> alter table hr.emps13_01
drop primary key ;

## NOT NULL constraint

SQL> alter table hr.emps13_01
drop constraint SYS_C005464 ;

SQL> alter table hr.emps13_01
2 add constraint nn_l_name_emps13_01 NOT NULL (last_name) ;
add constraint nn_l_name_emps13_01 NOT NULL (last_name)
*
ERROR at line 2:
ORA-00904: : invalid identifier

SQL> alter table hr.emps13_01
modify (last_name constraint nn_l_name_emps13_01 NOT NULL) ;

SQL> desc hr.emps13_01 ;
Name    Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID    NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID    NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID    NUMBER(4)

SQL> alter table hr.emps13_01
modify (last_name NULL) ;

SQL> desc hr.emps13_01 ;
Name    Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID    NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME    VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID    NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID    NUMBER(4)

--------------------------------------------------------------------------------------
2-13

CONSTRAINT는 적용되는 시점이 다음의 2 시점.

1. IMMEDIATE : DML 실행 시에 검사 (Default)

2. DEFERRED : commit 시에 검사

SQL> alter table hr.emps13_01
add constraint pk_emps13_01 primary key (employee_id) ;

SQL> desc user_constraints
Name    Null?    Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE    VARCHAR2(1)
TABLE_NAME    NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER    VARCHAR2(30)
R_CONSTRAINT_NAME    VARCHAR2(30)
DELETE_RULE    VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED    VARCHAR2(13)
GENERATED    VARCHAR2(14)
BAD    VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE    DATE
INDEX_OWNER    VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID    VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)

SQL> select constraint_name, DEFERRABLE,DEFERRED
from user_constraints
where table_name = 'EMPS13_01' ;

CONSTRAINT_NAME    DEFERRABLE DEFERRED
-------------------- -------------- ---------
PK_EMPS13_01 NOT DEFERRABLE IMMEDIATE
SYS_C005465    NOT DEFERRABLE IMMEDIATE
SYS_C005466    NOT DEFERRABLE IMMEDIATE
SYS_C005467    NOT DEFERRABLE IMMEDIATE

SQL> alter table hr.emps13_01
drop primary key ;

SQL> alter table hr.emps13_01
add constraint pk_emps13_01 primary key (employee_id)
deferrable ;

SQL> select constraint_name, DEFERRABLE,DEFERRED
from user_constraints
where table_name = 'EMPS13_01' ;

CONSTRAINT_NAME    DEFERRABLE DEFERRED
-------------------- -------------- ---------
PK_EMPS13_01 DEFERRABLE IMMEDIATE
SYS_C005465    NOT DEFERRABLE IMMEDIATE
SYS_C005466    NOT DEFERRABLE IMMEDIATE
SYS_C005467    NOT DEFERRABLE IMMEDIATE

SQL> set constraint pk_emps13_01 deferred ;

Constraint set.

SQL> select constraint_name, DEFERRABLE,DEFERRED
from user_constraints
where table_name = 'EMPS13_01' ;

SQL> create table hr.emps13_02
   as
   select employee_id, last_name from hr.employees
   where department_id = 10 ;

SQL> alter table hr.emps13_02
add constraint pk_emps13_02 primary key (employee_id) deferrable ;

SQL> select constraint_name, DEFERRABLE,DEFERRED
from user_constraints
where table_name = 'EMPS13_02' ;

CONSTRAINT_NAME DEFERRABLE DEFERRED
-------------------- -------------- ---------
SYS_C005473 NOT DEFERRABLE IMMEDIATE
PK_EMPS13_02 DEFERRABLE IMMEDIATE

SQL> set constraint SYS_C005444 deferred ; <-- hr.emps13_01 의 Constraints
set constraint SYS_C005473 deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable

SQL> set constraint pk_emps13_02 deferred ;

SQL> select * from emps13_02 ;

EMPLOYEE_ID LAST_NAME
----------- ---------------
200 Whalen

SQL> insert into emps13_02 values (200, '신상현') ;

1 row created.

SQL> commit ;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HR.PK_EMPS13_02) violated

SQL> insert into emps13_02 values (200, '신상현') ; <--commit 과 함께
insert into emps13_02 values (200, '신상현') set constraint 설정은 종료.
*
ERROR at line 1:
ORA-00001: unique constraint (HR.PK_EMPS13_02) violated

SQL> alter session set constraints=deferred ;

Session altered.

SQL> insert into emps13_02 values (200, '신상현') ;

1 row created.

SQL> commit ;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HR.PK_EMPS13_02) violated

SQL> insert into emps13_02 values (200, '신상현') ; <--ALTER SESSION set 설정은
세션 종료 시까지 유효

SQL> alter table emps13_02 drop primary key ;
alter table emps13_02 drop primary key
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HR.PK_EMPS13_02) violated

SQL> commit ;

SQL> alter session set constraints=immediate ;

SQL> alter table emps13_02 drop primary key ;

SQL> alter table hr.emps13_02
add constraint pk_emps13_02 primary key (employee_id) deferrable initially deferred ;

SQL> select constraint_name, DEFERRABLE,DEFERRED
from user_constraints
where table_name = 'EMPS13_02' ;

CONSTRAINT_NAME    DEFERRABLE DEFERRED
-------------------- -------------- ---------
SYS_C005473    NOT DEFERRABLE IMMEDIATE
PK_EMPS13_02 DEFERRABLE DEFERRED

SQL> alter table emps13_02 drop primary key ;

SQL> alter table hr.emps13_02
add constraint pk_emps13_02 primary key (employee_id) deferrable ;

SQL> select constraint_name, DEFERRABLE,DEFERRED
from user_constraints
where table_name = 'EMPS13_02' ;

CONSTRAINT_NAME DEFERRABLE DEFERRED
-------------------- -------------- ---------
SYS_C005473 NOT DEFERRABLE IMMEDIATE
PK_EMPS13_02 DEFERRABLE IMMEDIATE

SQL> alter table emps13_02 drop primary key ;

SQL> alter table hr.emps13_02
add constraint pk_emps13_02 primary key (employee_id) deferrable initially deferred ;

SQL> insert into emps13_02 values (200, '신상현') ;
insert into emps13_02 values (200, '신상현')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.PK_EMPS13_02) violated

SQL> select constraint_name, DEFERRABLE,DEFERRED
from user_constraints
where table_name = 'EMPS13_02' ;

CONSTRAINT_NAME    DEFERRABLE DEFERRED
-------------------- -------------- ---------
SYS_C005473    NOT DEFERRABLE IMMEDIATE
PK_EMPS13_02 DEFERRABLE DEFERRED

SQL> select * from hr.emps13_02 ;

EMPLOYEE_ID LAST_NAME
----------- ---------------
200 Whalen

SQL> insert into emps13_02 values (200, '신상현') ;
insert into emps13_02 values (200, '신상현')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.PK_EMPS13_02) violated

SQL> set constraint PK_EMPS13_02 deferred ;

Constraint set.

SQL> insert into emps13_02 values (200, '신상현') ;

1 row created.

SQL> commit ;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HR.PK_EMPS13_02) violated

SQL> insert into emps13_02 values (200, '신상현') ;
insert into emps13_02 values (200, '신상현')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.PK_EMPS13_02) violated

SQL> alter table emps13_02 disable constraint pk_emps13_02 ;

SQL> alter table emps13_02 enable constraint pk_emps13_02 ;

SQL> alter table emps13_02 disable primary key ;

SQL> alter table emps13_02 enable primary key ;

SQL> alter table emps13_02 disable primary key ;

SQL> insert into emps13_02 values (200, '신상현') ;

SQL> commit ;

Commit complete.

SQL> select * from hr.emps13_02 ;

EMPLOYEE_ID LAST_NAME
----------- ---------------
200 Whalen
200 신상현

SQL> alter table emps13_02 enable primary key ;
alter table emps13_02 enable primary key
*
ERROR at line 1:
ORA-02437: cannot validate (HR.PK_EMPS13_02) - primary key violated

SQL> select constraint_name from user_cons_columns
where column_name = 'DEPARTMENT_ID' and table_name = 'EMPLOYEES' ;

CONSTRAINT_NAME
--------------------
EMP_DEPT_FK

SQL> alter table hr.departments disable primary key ;
alter table hr.departments disable primary key
*
ERROR at line 1:
ORA-02297: cannot disable constraint (HR.DEPT_ID_PK) - dependencies exist

SQL> alter table hr.departments disable primary key cascade ;

SQL> alter table hr.departments enable primary key cascade ;
alter table hr.departments enable primary key cascade
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> alter table hr.departments enable primary key ;

SQL> select constraint_name, status from user_constraints
2 where table_name = 'EMPLOYEES' ;

CONSTRAINT_NAME    STATUS
-------------------- --------
EMP_LAST_NAME_NN ENABLED
EMP_EMAIL_NN ENABLED
EMP_HIRE_DATE_NN ENABLED
EMP_JOB_NN ENABLED
EMP_SALARY_MIN ENABLED
EMP_EMAIL_UK ENABLED
EMP_EMP_ID_PK    ENABLED
EMP_MANAGER_FK ENABLED
EMP_JOB_FK ENABLED
EMP_DEPT_FK    DISABLED

SQL> alter table hr.employees enable constraints EMP_DEPT_FK ;

SQL> select table_name, constraint_name from user_constraints
where table_name IN ( 'NEW_EMP', 'NEW_EMP2') ;

CONSTRAINT_NAME
--------------------
SYS_C005478

SQL> select table_name, index_name, uniqueness from user_indexes
where table_name IN ( 'NEW_EMP', 'NEW_EMP2');

INDEX_NAME
-------------------------
SYS_C005478

(참고) 자동인덱스 생성거동

경우 1)
SQL> create table new_emp
   (employee_id number(6) primary key
   ,first_name varchar2(20)
   ,last_name varchar2(25)
   );

경우 2)

1) 테이블 생성

SQL> create table new_emp2
   (employee_id number(6)
   ,first_name varchar2(20)
   ,last_name varchar2(25)
   );

2) 수동으로 인덱스를 생성

SQL> create index idx_emp_id on new_emp2(employee_id)

3) constraint 추가

SQL> alter table new_emp2
add constraint pk_new_emp PRIMARY KEY(employee_id) ;

(비교)
select index_name, table_name, uniqueness from user_indexes
where table_name IN ('NEW_EMP','NEW_EMP2') ;

alter table new_emp disable primary key ;

alter table new_emp2 disable primary key ;

select index_name, table_name, uniqueness from user_indexes
where table_name IN ('NEW_EMP','NEW_EMP2') ;

alter table new_emp enable primary key ;

alter table new_emp2 enable primary key ;

select index_name, table_name, uniqueness from user_indexes
where table_name IN ('NEW_EMP','NEW_EMP2') ;

SQL> DROP table new_emp cascade constraints ;

SQL> create table new_emps
   (employee_id number(6) primary key
using index (create index emp_id_idx
   on new_emps(employee_id)
   tablespace example)
   ,first_name varchar2(20)
   ,last_name varchar2(25)
   ) tablespace users;

SQL> select constraint_name from user_constraints
where table_name = 'NEW_EMP' ;

CONSTRAINT_NAME
--------------------
SYS_C005478

SQL> select constraint_name from user_constraints
where table_name = 'NEW_EMPS' ;

CONSTRAINT_NAME
--------------------
SYS_C005479

SQL> select index_name, uniqueness from user_indexes
where table_name = 'NEW_EMP' ;

INDEX_NAME UNIQUENES
------------------------- ---------
SYS_C005478 UNIQUE

SQL> select index_name, uniqueness from user_indexes
where table_name = 'NEW_EMPS' ;

INDEX_NAME UNIQUENES
------------------------- ---------
EMP_ID_IDX NONUNIQUE

SQL> alter table new_emp disable primary key ;

SQL> alter table new_emps disable primary key ;

SQL> select index_name, uniqueness from user_indexes
2 where table_name = 'NEW_EMP' ;

no rows selected

SQL> select index_name, uniqueness from user_indexes
where table_name = 'NEW_EMPS' ;

INDEX_NAME UNIQUENES
------------------------- ---------
EMP_ID_IDX NONUNIQUE

--------------------------------------------------------------------------------
2-23 Function based index

SQL> desc new_emp
Name    Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID    NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME    VARCHAR2(25)

SQL> create index idx_l_name_new_emp on new_emp (last_name) ;

SQL> select * from hr.employees
where last_name = 'King' ;

EMPLOYEE_ID FIRST_NAME    LAST_NAME EMAIL
----------- --------------- --------------- -------------------------
PHONE_NUMBER HIRE_DATE    JOB_ID SALARY COMMISSION_PCT
-------------------- ------------ ---------- ---------- --------------
MANAGER_ID DEPARTMENT_ID
---------- -------------
156 Janette King    JKING
011.44.1345.429268 30-JAN-96    SA_REP    10000    .35
   146    80

100 Steven    King    SKING
515.123.4567 17-JUN-87    AD_PRES 24000
90

SQL> select * from hr.employees
where upper(last_name) = 'KING' ;

EMPLOYEE_ID FIRST_NAME    LAST_NAME EMAIL
----------- --------------- --------------- -------------------------
PHONE_NUMBER HIRE_DATE    JOB_ID SALARY COMMISSION_PCT
-------------------- ------------ ---------- ---------- --------------
MANAGER_ID DEPARTMENT_ID
---------- -------------
100 Steven    King    SKING
515.123.4567 17-JUN-87    AD_PRES 24000
90

156 Janette King    JKING
011.44.1345.429268 30-JAN-96    SA_REP    10000    .35
   146    80

SQL> create index idx_upper_l_name_new_emp on new_emp (upper(last_name)) ;

Index created.

(설명부분)

SQL> conn system/oracle

SQL> show parameter query_rewrite_

NAME TYPE    VALUE
------------------------------------ ----------- -----------
query_rewrite_enabled    string    TRUE
query_rewrite_integrity    string    enforced

SQL> alter system set query_rewrite_enabled=false ;

SQL> alter system set query_rewrite_enabled=true ;

(참고 9i)

SQL> show user
USER is "HR"

SQL> select name from system_privilege_map
where name like '%QUERY%' ;

NAME
----------------------------------------
GLOBAL QUERY REWRITE
QUERY REWRITE

SQL> conn system/oracle

SQL> grant QUERY REWRITE to hr ;

SQL> conn hr/hr ;

SQL> create index idx_upper_l_name_new_emp on new_emp (upper(last_name)) ;

--------------------------------------------------------------------------------
2-26

SQL> purge recyclebin ;

SQL> drop table new_emp ;

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
NEW_EMP BIN$tI2LEfTKQ5+I5R21Du+O+w==$0 TABLE 2007-11-13:11:20:12

SQL> select * from new_emp ;
select * from new_emp
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> flashback table new_emp to before drop ;

SQL> desc new_emp ;
Name    Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID    NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME    VARCHAR2(25)

SQL> desc user_recyclebin
Name    Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME    VARCHAR2(32)
OPERATION    VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME    VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN    NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE    VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT    NOT NULL NUMBER
SPACE    NUMBER

SQL> drop table new_emps purge ;

SQL> show recyclebin

SQL> drop table new_emp ;

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
NEW_EMP BIN$43qBn7BYR02hzYHEawaduA==$0 TABLE 2007-11-13:11:25:00

SQL> flashback table new_emp to before drop ;

SQL> show recyclebin
SQL> drop table new_emp purge ;

SQL> show recyclebin
SQL>

Posted by 아돌

트랙백 0, | 댓글 0

님들아~

THE ASCETiC LiFE/SIST / 2008/08/23 14:21

SIST CLASS A 학생들 열공하셈~

Posted by 아돌

트랙백 0, | 댓글 0

«이전 1 2 3 4 5 ... 21 다음»

티스토리 툴바

자넨 누군가?

카테고리

달력

공지사항

태그목록

최근에 올라온 글

2008-08-23

Trackback | http://dandyboys.tistory.com/trackback/90

2008-08-16~17

Trackback | http://dandyboys.tistory.com/trackback/89

님들아~

Trackback | http://dandyboys.tistory.com/trackback/88

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

링크