SQL 99 About 1 2 3 4 Multiple

SQL 99

About … 1. 2. 3. 4. Multiple Insert(Insert All…) MERGE 문 연산자 IS NAN 과 IS INFINITE INTERVAL Data Type

Multiple Insert SQL 92 SQL 99 INSERT Table 1 INSERT Table 2 Table 3 INSERT Table 3

Multiple Insert • Unconditional Insert • Conditional All Insert • Conditional First Insert • Pivoting Insert

UNCONDITIONAL INSERT Unconditional Insert Result of Sub Query 묻지도 따지지도 말고. . Table 1 Table 2 Table 3 Table 4

UNCONDITIONAL INSERT ALL INTO table 1 VALUES ( col 1, col 2, col 3 ) INTO table 2 VALUES ( col 2, col 4 ) INTO table 3 VALUES ( col 1, col 2, col 3, col 4 ) INTO table 4 VALUES ( col 2, col 3, col 4 ) SELECT col 1, col 2, col 3, col 4, col 5 Sub. Query임을 증명함. FROM source_table

UNCONDITIONAL INSERT 쿼리 SCOTT> create table sal_history (empno number(4), hiredate, sal number(7, 2)) SCOTT>create table mgr_history (empno number(4), mgr number(4), sal number(7, 2)); SCOTT>insert all into sal_history values (empno, hiredate, sal) into mgr_history values (empno, mgr, sal) select empno, hiredate, sal, mgr from emp where rownum < 5; 8 개의 행이 만들어졌습니다. 결과 SCOTT: SQL>select * from sal_history; =================== EMPNO HIREDATE SAL ---------7369 80/12/17 200 7499 81/02/20 1600 7521 81/02/22 1250 7566 81/04/02 2975 =================== 경 과: 00: 00. 03 SCOTT: SQL>select * from mgr_history; =================== EMPNO MGR SAL ----------7369 7902 200 7499 7698 1600 7521 7698 1250 7566 7839 2975 ===================

CONDITIONAL ALL INSERT Conditional All Insert Result of Sub Query YES NO YES Table 1 Table 2 Table 3 Table 4

CONDITIONAL ALL INSERT ALL WHEN condition 1 THEN INTO table 1 VALUES ( col 1, col 2, col 3 ) WHEN condition 2 THEN INTO table 2 VALUES ( col 2, col 4 ) WHEN condition 3 THEN INTO table 3 VALUES ( col 1, col 2, col 3, col 4 ) WHEN condition 4 THEN INTO table 4 VALUES ( col 2, col 3, col 4 ) SELECT col 1, col 2, col 3, col 4, col 5 Sub. Query임을 증명함. FROM source_table

CONDITIONAL ALL INSERT 쿼리 SCOTT>truncate table sal_history; SCOTT>truncate table mgr_history; SCOTT>insert all when sal > 1000 then into sal_history values (empno, hiredate, sal) when mgr > 7500 then into mgr_history values (empno, mgr, sal) select empno, hiredate, sal, mgr from emp where empno > 7400; 결과 SCOTT: SQL>select * from sal_history; =================== EMPNO HIREDATE SAL ---------7499 81/02/20 1600 7521 81/02/22 1250 7566 81/04/02 2975 …생략… =================== 12 개의 행이 선택되었습니다. SCOTT: SQL>select * from mgr_history; =================== EMPNO MGR SAL ----------7499 7698 1600 7521 7698 1250 7566 7839 2975 7654 7698 1250 7698 7839 2850 …생략… =================== 12 개의 행이 선택되었습니다.

CONDITIONAL FIRST INSERT Conditional First Insert Result of Sub Query NO NO YES NO Table 1 Table 2 Table 3 Table 4

CONDITIONAL FIRST INSERT FIRST WHEN condition 1 THEN INTO table 1 VALUES ( col 1, col 2, col 3 ) WHEN condition 2 THEN INTO table 2 VALUES ( col 2, col 4 ) WHEN condition 3 THEN INTO table 3 VALUES ( col 1, col 2, col 3, col 4 ) WHEN condition 4 THEN INTO table 4 VALUES ( col 2, col 3, col 4 ) ELSE INTO table 4 VALUES ( col 2, col 3, col 4 ) SELECT col 1, col 2, col 3, col 4, col 5 Sub. Query임을 증명함. FROM source_table

CONDITIONAL FIRST INSERT 쿼리 SCOTT>create table special_sal (deptno number(2), sal number(7, 2)) SCOTT>create table hiredate_history (deptno number(2), hiredate) SCOTT>insert first when sal > 10000 then into special_sal values (deptno, sal) when hiredate like ('%00%') then into hiredate_history values (deptno, hiredate) when hiredate like ('%99%') then into hiredate_history values (deptno, hiredate) else into hiredate_history values(deptno, hiredate) select deptno, sum(sal) sal, max(hiredate) hiredate from emp group by deptno; 결과 SCOTT: SQL>select * from special_sal; ===================== DEPTNO SAL -----20 10275 ===================== 경 과: 00: 00. 01 SCOTT: SQL>select * from hiredate_history; ===================== DEPTNO HIREDATE -------30 81/12/03 10 82/01/23 =====================

PIVOTING INSERT Pivoting Insert Table A col 1 col 2 col 3 col 4 col 5 val 1 val 2 val 3 val 4 val 5 Pivoting Table B col 1 col 2 col 3 val 1 val 2 val 4 val 1 val 2 val 5

PIVOTING INSERT ALL INTO table 1 VALUES ( col 1, col 2, col 3 ) INTO table 1 VALUES ( col 1, col 2, col 4 ) INTO table 1 VALUES ( col 1, col 2, col 5 ) SELECT col 1, col 2, col 3, col 4, col 5 FROM source_table

PIVOTING INSERT 쿼리 SCOTT> create table year_sales( empno number(4), years number(4), first number(8, 2), second number(8, 2), third number(8, 2), fourth number(8, 2) ); SCOTT> insert into year_sales values( 1101, 2009, 150, 80, 60, 120); 1 개의 행이 만들어졌습니다. SCOTT> insert into year_sales values( 1102, 2010, 300, 230, 120); 1 개의 행이 만들어졌습니다. SCOTT> create table year_sales_data( empno number(4), years number(4), sales number(8, 2) ); SCOTT> insert all into year_sales_data values(empno, years, first) into year_sales_data values(empno, years, second) into year_sales_data values(empno, years, third) into year_sales_data values(empno, years, fourth) select empno, years, first, second, third, fourth from year_sales; 8 개의 행이 만들어졌습니다. 결과 SCOTT> select * from year_sales; EMPNO YEARS FIRST SECOND THIRD FOURTH -----------------------1101 2009 150 80 60 120 1102 2010 300 230 120 2 개의 행이 선택되었습니다. SCOTT> select * from year_sales_data; EMPNO YEARS SALES ----------1101 2009 150 1102 2010 300 1101 2009 80 1102 2010 300 1101 2009 60 1102 2010 230 1101 2009 120 1102 2010 120 8 개의 행이 선택되었습니다.

MERGE Table A Data 1 Data 2 Data 3 Table B INSERT UPDATE Table B Data 1 Inserted Data 1 Data 2 Inserted Data 2 Data 3 Updated Data 3 Data 4 Data 5

MERGE SQL 92 SQL 99 INSERT Data 1 INSERT Data 2 Duplicated Error UPDATE Data 2 INSERT Data 3 MERGE INSERT Data 1 UPDATE Data 2 INSERT Data 3

MERGE INTO table. B USING table. A Table A, B의 Join 조건. ON table. A. col 1 = table. B. col 2 Matched/ Not Matched의 판단 조건 WHEN MATCHED THEN UPDATE 대상 Table명이 없음. SET col 1 = val 1. . . WHEN NOT MATCHED THEN INSERT (col 1, col 2, . . ) INTO Table명이 없음. VALUES (val 1, val 2, . . )

MERGE 쿼리 SCOTT> create table c_dept as select * from dept where 1 = 0; SCOTT> merge into c_dept c using dept d on(c. deptno = d. deptno) when matched then update set c. dname = d. dname, c. loc = d. loc when not matched then insert values(d. deptno, d. dname, d. loc); 결과 SCOTT> select * from c_dept; DEPTNO DNAME LOC --------------20 RESEARCH DALLAS 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 40 OPERATIONS BOSTON

IS NAN & IS INFINITE • Nan : In computing, Na. N (Not a Number) is a value of numeric data type representing an undefined or unrepresentable value, especially in floating-point calculations. • INFINITE : 무한대(∞) 출처 : wikipedia

IS NAN & IS INFINITE • Na. N SELECT SELECT SELECT 0 d/0 FROM DUAL; (1 D/0)/(1 D/0) FROM DUAL; --INFINITY/INFINITY (1 D/0)*0 FROM DUAL; --INFINITY*0 POWER(1, (1 D/0)) FROM DUAL; --1^INFINITY (1 D/0)-(1 D/0) FROM DUAL; --INFINITY +(-INFINITY) BINARY_FLOAT_NAN FROM DUAL; BINARY_DOUBLE_NAN FROM DUAL; TO_BINARY_FLOAT('NAN') FROM DUAL; TO_BINARY_DOUBLE('NAN') FROM DUAL; IS NAN

IS NAN & IS INFINITE • INFINITE SELECT SELECT 1 d/0 FROM DUAL; BINARY_FLOAT_INFINITY FROM DUAL; BINARY_DOUBLE_INFINITY FROM DUAL; TO_BINARY_FLOAT('INF') FROM DUAL; TO_BINARY_DOUBLE('INF') FROM DUAL; IS INFINITE

INTERVAL Data Type Ø INTERVAL : 시간의 길이를 나타내는 단위 Ø INTERVAL Data Type 1) INTERVAL YEAR TO MONTH : 월이나 년의 기간을 저장 INTERVAL YEAR [ ( year_precision ) ] TO MONTH * year_precision : interval 값의 YEAR 필드에 포함될 수 있는 숫자의 길이 (0~9, default : 2) 2) INTERVAL DAY TO SECOND : 날짜와 시간, 분, 초를 저장 INTERVAL DAY [ ( day_precision ) ] TO SECOND [ ( seconds_precision ) ] * day_precision : Day 필드에 포함될 수 있는 숫자의 길이 (0~9, default : 2) * seconds_precision : second 필드에 포함될 수 있는 숫자의 길이 (0~9, default : 6)

INTERVAL 쿼리 SCOTT> create table emp_breaks( empno number, reason varchar 2(20), times interval day(1) to second(2) ); SCOTT> insert into emp_breaks values( 100, 'Tea Time', timestamp '2010 -06 -01 10: 45: 00. 000000' timestamp '2010 -06 -01 11: 00. 000000'); 1 개의 행이 만들어졌습니다. 결과 SCOTT> select * from emp_breaks; EMPNO REASON TIMES --------------100 Tea Time -0 00: 15: 00. 00