2010.05.04 14:41

집합 연산자를 사용시 집합을 구성할 컬럼의 데이터 타입이 동일해야 한다.

  • - UNION : 합집합
  • - UNION ALL : 중복 데이터를 다 포함하는 합집합
  • - INTERSECT : 교집합
  • - MINUS : 차집합

UNION

UNION은 두 테이블의 결합을 나타내며, 결합시키는 두 테이블의 중복되지 않은 값들을 반환 한다.

 
SQL> SELECT deptno FROM emp
     UNION
     SELECT deptno FROM dept;
 
    DEPTNO
----------
        10
        20
        30
        40
    

UNION ALL

UNION과 같으나 두 테이블의 중복되는 값 까지 반환 한다.

 
SQL> SELECT deptno FROM emp
     UNION ALL
     SELECT deptno FROM dept;
 
   DEPTNO
---------
       20
       30
       30
       20
       30
       30
       10
       20
       10
       30
    

INTERSECT

INTERSECT는 두 행의 집합중 공통된 행을 반환 한다.

 
SQL> SELECT deptno FROM emp
     INTERSECT
     SELECT deptno FROM dept;
    
    DEPTNO
----------
        10
        20
        30
    

MINUS

MINUS는 첫 번째 SELECT문에 의해 반환되는 행 중에서 두 번째 SELECT문에 의해 반환되는 행에 존재하지 않는 행들을 반환 한다.

 
SQL> SELECT deptno FROM dept
     MINUS
     SELECT deptno FROM emp;
 
    DEPTNO
----------
        40
    
Posted by 박쥐소년
2010.03.29 13:51

select to_date('20070312000000','yyyymmddhh24miss') from dual; -- 문자를 날짜로

select to_char(sysdate, 'yyyymmddhh24miss') from dual;  -- 날짜를 문자로.

 

select sysdate + 1 from dual;   -- 내일

select sysdate + 30 from dual ; -- 30일 후...

select sysdate + 1/24 from dual; -- 한시간 후.

select sysdate + 1/24/60 from dual; -- 1분 후

.......

 

select sysdate - 1 from dual; -- 어제

select sysdate - 30 from dual ; -- 30일 전...

select sysdate - 1/24 from dual; -- 한시간 전.

select sysdate - 1/24/60 from dual; -- 1분 전.

Posted by 박쥐소년
2010.03.29 11:30

# 날짜계산
select months_between(sysdate,to_date('2002-12-22','yyyy-mm-
dd'))
-- months_between(A,B) = A-B/30
--select add_months(sysdate,4) -- 특정일의 달수 더한 날
--select next_day(sysdate,'friday') -- 특정일의 다음주 요일
--select last_day(sysdate) -- 특정일의 해당 월의 마지막 날
--select round(sysdate,'dd') -- 특정일의 반올림(오후면 다음날..)
--select trunc(sysdate,'ww') -- 특정일의 전주 토요일(해당 전주의 마지막 날)에해당하는 날짜
--select trunc(sysdate,'D') -- 특정일의 주 일요일(해당 주의 첫째 날)에해당하는 날짜

from dual
 

/* 어제 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE-1)+0.99999421
/* 오늘 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 0.99999421
/* 내일 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+1) AND TRUNC(SYSDATE+1)+0.99999421
/* 금주 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'D')
                        AND TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'D')+6.99999421
/* 차주 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+8)-TO_CHAR(SYSDATE, 'D')
                        AND TRUNC(TRUNC(SYSDATE)+14.99999421)-TO_CHAR(SYSDATE, 'D')
/* 금월 */ 날짜칼럼 BETWEEN TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'DD')
                        AND TRUNC(LAST_DAY(SYSDATE))+0.99999421
/* 전월 */ 날짜칼럼 BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-1)+1)-TO_CHAR(SYSDATE,'DD')
                        AND TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))+0.99999421
/* 차월 */ 날짜칼럼 BETWEEN ADD_MONTHS(TRUNC(SYSDATE),1)-TO_CHAR(SYSDATE,'DD')+1
                        AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE),1)+0.99999421)


 

# 특정일 까지의 간격을 년, 개월, 일로 표현하기

SELECT
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD'))/12) "년",
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD')) -
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD'))/12) * 12) "개월",
TRUNC((MONTHS_BETWEEN(SYSDATE,TO_DATE('19970101', 'YYYYMMDD')) -
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('19970101', 'YYYYMMDD')))) * 30.5) "일"
FROM DUAL;



# 당월의 주차 구하기..
SELECT
'20040511' as "날짜"
, ceil((to_number(substrb('20040511', -2, 2)) + 7 - to_number(TO_CHAR(TO_DATE('20040511','YYYYMMDD'),'D')))/7) as "월별 주차"
from dual;


 

# 시간 계산 SQL
SELECT TRUNC(TO_DATE('20010502223443','YYYYMMDDHH24MISS')-TO_DATE('20010501213344','YYYYMMDDHH24MISS')) || ' day ' ||
       TRUNC(MOD((TO_DATE('20010502223443','YYYYMMDDHH24MISS')-TO_DATE('20010501213344','YYYYMMDDHH24MISS')),1)*24) || ' hour ' ||
       TRUNC(MOD((TO_DATE('20010502223443','YYYYMMDDHH24MISS')-TO_DATE('20010501213344','YYYYMMDDHH24MISS'))*24,1)*60) || ' minute ' ||
       TRUNC(ROUND(MOD((TO_DATE('20010502223443','YYYYMMDDHH24MISS')-TO_DATE('20010501213344','YYYYMMDDHH24MISS'))*24*60,1)*60)) || ' sec '
       " Time Interval "
FROM DUAL ;


Posted by 박쥐소년
2010.03.16 14:09

DBMS에 따른 날짜포맷 변환

Oracle - MS SQL - DB2 UDB 의 서로 다른 날짜 형식을 맞추기위한 SQL문


DBMS 별 시간, 날짜 조회 쿼리

Oracle

select sysdate from dual; 날짜+시분초 까지 조회가능

select current_timestamp from dual;  날짜+밀리초+시간존 까지 조회

MS SQL

 

select getdate()    날짜 + 밀리초 단위까지 조회가능

 

DB2 UDB

select current timestamp from sysibm.sysdummy1  날짜+밀리초까지 조회 가능

select current date from sysibm.sysdummy1    날짜만 조회

select current time from sysibm.sysdummy1     밀리초 단위의 시간만 조회

 

DBMS 별 default date format

Oracle

YY/MM/DD  (한글)

DD-MON-YYYY  (영어)  

MS SQL

YYY/MM/DD HH:MI:SS   (한글)

MM-DD-YYYY HH:MI:SS   (영어)

DB2 UDB

YYYY-MM-DD-HH:MI:SS.MMMMMM (TIMESTAMP 타입)

YYYY-MM-DD (DATE 타입)

HH:MI:SS.MMMMMM (TIME 타입)

 

날짜 포맷 변환표

형식

RDBMS

변환 문법

 

Oracle

TO_CHAR(date_exp, 'YYYY.MM.DD')

'YYYY.MM.DD'

MSSQL

CONVERT(VARCHAR, date_exp, 102)

 

DB2

REPLACE(CHAR(DATE(date_exp),ISO), '-', '.')

 

Oracle

TO_CHAR(date_exp, 'HH:MI:SS')

'HH:MI:SS'

MSSQL

CONVERT(VARCHAR, date_exp, 108)

 

DB2

CHAR(TIME(date_exp) , JIS )

 

Oracle

TO_CHAR(date_exp, 'YYYY/MM/DD')

'YYYY/MM/DD'

MSSQL

CONVERT(VARCHAR, date_exp, 111)

 

DB2

REPLACE(CHAR(DATE(date_exp), ISO), '-', '/')

 

Oracle

TO_CHAR(date_exp, 'YYYYMMDD')

'YYYYMMDD'

MSSQL

CONVERT(VARCHAR, date_exp, 112)

 

DB2

CHAR(DATE(date_exp))

 

Oracle

TO_CHAR(date_exp, 'HH24:MI:SS')

'HH24:MI:SS'

MSSQL

CONVERT(VARCHAR(8), date_exp, 114)

 

DB2

CHAR(TIME(date_exp) )

 

Oracle

TO_CHAR(date_exp, 'YYYY.MM.DD HH24:MI')

'YYYY.MM.DD HH24:MI'

MSSQL

CONVERT(VARCHAR, date_exp, 102) + ' ' + CONVERT(VARCHAR(5), date_exp, 114)

 

DB2

REPLACE(CHAR(DATE(date_exp), ISO), '-', '.') || CAST( TIME(date_exp) AS CHAR(5))

 

Oracle

TO_CHAR(date_exp, 'YYYY/MM/DD HH24:MI:SS')

'YYYY/MM/DD HH24:MI:SS'

MSSQL

CONVERT(VARCHAR, date_exp, 111) + ' ' + CONVERT(VARCHAR(8), date_exp, 114)

 

DB2

REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') || CAST( TIME(date_exp))


Posted by 박쥐소년
2010.02.08 10:35

테이블이 사용중인 블록 크기를 계산해주는 SQL

/*
** Table이 사용하는 블럭 크기를 구하는 스크립트... <<박제용>>
**
** 사용법 : 1) DBA 권한으로 로그인한다.
** 2) SQL> @tab_block [table명]
**
** Notice : sum(blocks)는 사용하는 블럭의 갯수이며 사이즈는 db_block_size를
** 곱하여 얻을 수 있다.
*/
SELECT OWNER, TABLESPACE_NAME, SEGMENT_NAME, SUM(BLOCKS)
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = UPPER('&1')
GROUP BY OWNER, TABLESPACE_NAME, SEGMENT_NAME
/

이미 컴파일된 프로시져소스를 보고싶을 때 사용하는 스크립트

/*
** PL/SQL 소스를 보기위한 스크립트.. <박제용>
**
** 사용법 : find_plsql [프로시져명칭]
**
**
*/
select text
from user_source
where name = upper('&1')
order by line;

테이블을 복사해주는 스크립트 (v8.0 only)

/*
** table을 다른 스키마 혹은 table로 복사 <<박제용>>
**
** Notice) 1. Oracle 8.0 이상에서만 지원.
** 2. sql*net 이 설정되어 있어야만 한다.
** 3. 테이블과 PK만 복사하고 인덱스는 모두 다시 생성해주어야 한다.
** 따라서 테이블을 생성해 주고 입력하는것이 좋다.
** 4. sql*plus 에서만 실행된다.
** 사용법) @tab_copy scott/tiger@link source_table_name target_table_name
**
*/
copy from &1 create &3 using select * from &2

/* 다른 DB로 복사할때는
copy from &1 to &2 create &4 using select * from &3
*/

/* 미리 만들어진 table에 입력할때는
copy from &1 insert &3 using select * from &2
*/

Table Data Size를 정확히 계산해주는 스크립트

/*
** Table Data Size를 정확히 계산해주는 스크립트. <<박제용>>
**
** 사용법 : @tab_size [table_name]
**
*/
analyze table &1 delete statistics;
analyze table &1 compute statistics;

SELECT GREATEST(4, ceil(NUM_ROWS/
( (round(((1958-(INI_TRANS*23))*((100-PCT_FREE)/100))/AVG_ROW_LEN)))) * 2048) TableSize_Kbytes
FROM user_tables
WHERE table_name = upper('&1'); 

dead lock이 발생했을때 발생시킨 유저와 SQL문을 찾아주는 SQL

/*  
**  
**  사용법   :SQL> @find_deadlock
**  Description : 데드락이 발생할 경우 locking 된 유저와 sql문을 보여준다.
**  
**  데드락이 발생한 유저를 kill 하려면.
** Alter system kill session '{serial#},{SID}';
**
*/
Select a.serial#, a.sid, a.username, b.id1, c.sql_text
from v$session a, v$lock b, v$sqltext c
where b.id1 in( select distinct e.id1 from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and c.hash_value = a.sql_hash_value
and b.request = 0;

딕셔너리에서 해당 키워드에 관한 뷰, 테이블을 찾아주는 SQL

/*
** 딕셔너리로부터 입력한 키워드에 관한 테이블명을 조회한다. <<박제용>>
**
** 사용법 : SQL> @dic_find [키워드(대소문자가림)]
**
*/
col TABLE_NAME format a15
col COMMENTS format a100

select * from dictionary
where COMMENTS like ('%&1%')
/

컬럼명만 가지고 테이블과 설정상태를 찾아주는 SQL

/*
** 컬럼의 스펙과, 소속 테이블을 찾는다. <<박제용>>
**
** 사용법 : SQL> @col_find [컬럼명]
**
*/
col CNAME format a20
col COLTYPE format a10
col NULLS format a5
col DEFAULTVAL format a10

select TNAME, COLNO, CNAME, COLTYPE, WIDTH, NULLS, DEFAULTVAL
from col
where CNAME = UPPER('&1')
/

Constraint 이름으로 해당 테이블과 컬럼찾는 SQL

/*
**=============================================
** CONSTRAINT 이름으로 사용 테이블 찾기
**=============================================
**
** Usage : @Show_Columns Constraint_Name
** Description : Shows The Columns Bound By A Constraint
** 사용예 : SQL> @show_Columns PK_EMPNO
*/
SET VERIFY OFF
CLEAR BREAK
BREAK ON CONSTRAINT_NAME ON TABLES

SELECT SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME = UPPER('&1');

컬럼에 걸려있는 constraint 를 보여주는 SQL

/*
**=======================================
** 해당 COLUMN에 걸려 있는 CONSTRAINT확인
**=======================================
**
** Usage : @Show_Constraints Table_Name Column_Name
**
** Description : 해당 Table의 Column에 걸려 있는 Constraint를 보여준다.
**
** < 실행 예 >
** SQL> @SHOW_CONSTRAINTS WIDGETS LENGTH
**
*/

SET VERIFY OFF
CLEAR BREAK
BREAK ON TABLES ON COL_NAME
SELECT SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&1')
AND COLUMN_NAME = UPPER('&2');

PK와 FK간의 연관관계를 찾아 보여주는 SQL

/*  
**  
**  사용법     :> @Show_Positions  Parent_Table  Child_Table  
**  Description  :  Shows Primary And Foreign Key Positions  
**  
**  WARNING   :  이 문장은 해당 Table의 Constraint생성시 Naming   
**          Convention을 따른 경우에 적용되도록 되어 있다.  
**
*/
SET VERIFY OFF  
CLEAR BREAK  
BREAK ON CONSTRAINT_NAME ON TABLES
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&1') AND CONSTRAINT_NAME LIKE 'PK%'

UNION
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,25) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&2') AND CONSTRAINT_NAME LIKE 'FK%'
ORDER BY 1 DESC,4 ASC;

테이블의 특정 컬럼에 중복된 값을 찾는 SQL

/*
**=============================================
** 중복된 값 있는지 찾기
**=============================================
** Usage : @중복찾기.sql [테이블명] [중복을조사할컬럼명]
**
** Warning : 똑같은값이 2개 이상있을때 처음값은 출력 않되고 2번째 값부터 출력됨. <>
*/

select * from &1 A
where rowid >
(SELECT min(rowid) FROM &1 B
WHERE B.&2 = A.&2)
order by &2; 


 

 
Posted by 박쥐소년
2010.01.26 23:17

set pagesize n : 현재 화면의 PageSize를 표시, 페이지마다 컬럼의 헤더가 표시된다.

SCOTT>set pagesize 10
SCOTT>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80/12/17       2800         96         20
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         10
      7566 JONES      MANAGER         7839 81/04/02       2975                    20
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7782 CLARK      MANAGER         7839 81/06/09       2450                    10

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
      7839 KING       PRESIDENT            81/11/17                               10
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
      7876 ADAMS      CLERK           7788 87/05/23       1100                    20
      7900 JAMES      CLERK           7698 81/12/03        950                    30
      7902 FORD       ANALYST         7566 81/12/03       3000                    20
      7934 MILLER     CLERK           7782 82/01/23       1300                    10

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      5555 tatata                                         9000

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

SCOTT>set pagesize 20
SCOTT>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80/12/17       2800         96         20
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         10
      7566 JONES      MANAGER         7839 81/04/02       2975                    20
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7782 CLARK      MANAGER         7839 81/06/09       2450                    10
      7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
      7839 KING       PRESIDENT            81/11/17                               10
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
      7876 ADAMS      CLERK           7788 87/05/23       1100                    20
      7900 JAMES      CLERK           7698 81/12/03        950                    30
      7902 FORD       ANALYST         7566 81/12/03       3000                    20
      7934 MILLER     CLERK           7782 82/01/23       1300                    10
      5555 tatata                                         9000



15 개의 행이 선택되었습니다.
Posted by 박쥐소년
2010.01.26 23:14

Product Edition


Oracle 10g : Product Edition

오라클 세미나 자료를 참고 했습니다...

Oracle 10g의 Product Edition을 살펴 보면 아래와 같습니다.

Enterprise Edition : 최고의 성능과 확장성, OLTP상의 안정성, 의사결정지원 기능, Processor수는 제한이 없으며 Min User수는 25, RAC는 옵션 입니다. Processor당 $40,000 정도, USER당 $800 정도 입니다. DB를 운용하는 규모에는 제한이 없습니다.

Standard Edition : 클러스터링을 지원하는 4 Processor미만의 서버를 위한 것이며 Min User수는 5이며 RAC(Real Application Clusters)가 포함 되어 있습니다. 1000명 미만의 사업장에 적당한데 기가바이트 DB사이즈 볼륨의 업무에 적당하며 SQL Server 2000 Enterprise Edition에 대응 됩니다.

Standard Edition One : Entry Level 서버를 위한 2 Processor미만, Min User는 5이며 RAC(Real Application Clusters)는 지원되지 않습니다. 400명 미만의 사용자가 있는 회사나 조직에 적합하며 대기업인 경우 단일 업무용 이나 독립업무 서버로 적당 하여 수 GB 미만의 DB볼륨에 적당 합니다. SQL Server 2000 SE와 대응 됩니다.

Personal Edition : 모든 Oracle Database 제품군과 호환이 가능하며 모든 제품의 특징이 포함된 개인 사용자용
Posted by 박쥐소년
2009.11.10 14:41

Posted by 박쥐소년