2008.06.25 10:56
SELECT *
  FROM USER_TRIGGERS
신고
Posted by 커피한잔의여유
2008.06.25 10:05
SELECT TRUNC (MONTHS_BETWEEN (SYSDATE, TO_DATE (:HAPPYDAY, 'YYYYMMDD')) / 12) "년"
     , TRUNC (  MONTHS_BETWEEN (SYSDATE, TO_DATE (:HAPPYDAY, 'YYYYMMDD'))
              - TRUNC (MONTHS_BETWEEN (SYSDATE, TO_DATE (:HAPPYDAY, 'YYYYMMDD')) / 12) * 12
             ) "개월"
     , TRUNC (  (  MONTHS_BETWEEN (SYSDATE, TO_DATE (:HAPPYDAY, 'YYYYMMDD'))
                 - TRUNC (MONTHS_BETWEEN (SYSDATE, TO_DATE (:HAPPYDAY, 'YYYYMMDD')))
                )
              * 30.5
             ) "일"
  FROM DUAL;
신고
Posted by 커피한잔의여유
2008.06.24 10:05
SELECT     SUM (DECODE (TO_CHAR (TO_DATE ('200806' || LPAD (LEVEL, 2, '0'), 'YYYYMMDD'), 'D')
                      , '1', 1
                      , 0
                       )
               ) DATE#
      FROM DUAL
CONNECT BY TO_DATE ('200806', 'YYYY-MM') + LEVEL - 1 <= LAST_DAY (TO_DATE ('200806', 'YYYY-MM'))
신고
Posted by 커피한잔의여유
2008.06.24 09:35
한글과, 영어로 조합된 컬럼을 Order by 할때 한글부터 정렬되게~
ORDER BY CONVERT ( 컬럼, 'US8ICL' )

레코드에 영어가 없다면
ORDER BY CONVERT ( 컬럼, 'ISO2022-KR' )
신고
Posted by 커피한잔의여유
2008.06.18 14:52
TABLE 구조 보기

SQL>DESC TABLE_NAME
 

테이블 존재 여부 알아보기

SQL>SELECT TABLE_NAME
        FROM USER_TABLES
        WHERE TABLE_NAME = ’찾을 테이블 이름’
        -->찾을 테이블 이름은 대문자여야 됩니다.
 

모든 유저 보기

SQL>SELECT usernam FROM ALL_USERS
 

테이블 제약조건의 확인

SQL>SELECT table_name, constraint_name, constraint_type
        FROM   USER_CONSTRAINTS
       
WHERE TABLE_NAME =’STORM_CONTENT’;
 

선택한 Row만큼만 보여주기

SQL>SELECT name
        FROM storm_board
        WHERE rownum <= 10
---> 이렇게 하면 데이터가 1000건이 있더라도, 1~10건만 보여주게 된다.
 
 
LIKE’ 연산자를 사용하여 case insensitive search를 할 수 있는 방법
 
필드에 ’UPPER’ 함수를 사용하시면 됩니다
SQL>SELECT name
        FROM storm_board
        WHERE UPPER(name) like ’%K%’
 

잠시 host상태로 나가고 싶을 때.

SQL>host
다시 sqlplus로 들어오려면 exit명령어로 들어오시면 됩니다.
 

테이블에 새로운 컬럼의 추가

SQL>ALTER TABLE table_name ADD ( column_name DATATYPE );
 
ex)SQL>ALTER TABLE storm_board ADD(ip_addr VARCAHR2(15) not null)


테이블 컬럼의 삭제

SQL>ALTER TABLE table_name DROP COLUMN column_name

ex)SQL>ALTER TABLE storm_board DROP COLUMN ip_addr
-->컬럼의 삭제는 오라클 8i버전부터 되는것으로 알고 있습니다.  
 
 
SQLPLUS에서 SQL문의 실행 속도 알아보기
 
SQL>SET TIMING ON

을 한 후 SELECT문을 수행하면 경과 시간이 나옵니다.
 
SQL> SELECT COUNT(name) FROM storm_board;
 
COUNT(NAME)
-----------
        286
 
경   과: 00:00:00.10


SQLPLUS에서 SELECT문 사용시 데이타만 출력되고 HEADER는 안나오게 하는 방법
 
SQL>SET HEADING OFF
한후 SELECT문을 수행하면 아래와 같은 결과가 나옵니다.
 
SQL> SELECT empno, ename, mgr
         FROM emp
         WHERE rownum < 6;

7369  SMITH            7902
7499  ALLEN            7698
7521  WARD             7698
7566  JONES            7839
7654  MARTIN           7698

 
e


  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================
신고
Posted by 커피한잔의여유
2008.06.18 14:51

oln.oracle.co.kr 참고
 
SQL과 PL/SQL, SQL*Plus의 차이점이 궁금합니다...
 
 
SQL
관계형 데이터베이스에 저장된 데이터에 Access하기 위하여 사용하는 표준언어 입니다.
 
PL/SQL
SQL문을 사용하여 프로그램을 작성할 수 있도록 확장해 놓은 오라클의 Procedural Language입니다.
 
SQL*Plus
SQL 및 PL/SQL 문장을 실행할 수 있는 환경을 제공하는 오라클의 Tool입니다. 



  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================

신고
Posted by 커피한잔의여유
2008.06.18 14:49

NVL2라는 함수 DECODE랑 조금 비슷한 놈 같기도 하고요..
참 편한놈이네용..

문법
NVL2(expr,expr1,expr2);


expr의 값이 null이 아닐 경우에는  expr1의 값을 반환 하고요 null일 경우에는 expr2의 값을 반환 합니다.



예제)

-- 보통 SQL문을 실행 했을 경우
SQL>  SELECT ename, comm FROM emp;

ENAME                      COMM
---------------- ----------
SMITH              
ALLEN                        300
WARD                        500
JONES              
MARTIN                     1400
BLAKE               
CLARK               
SCOTT              
KING                
TURNER                        0
ADAMS            



-- NVL함수를 사용 했을 경우

SQL>SELECT ename, NVL(comm, 0) comm  FROM emp;

ENAME                      COMM
---------------- ----------
SMITH                           0
ALLEN                        300
WARD                         500
JONES                           0
MARTIN                     1400
BLAKE                            0
CLARK                            0
SCOTT                           0
KING                               0
TURNER                         0
ADAMS                           0



-- NVL2함수를 사용 했을 경우

SQL>SELECT ename, NVL2(comm, 1, 0) FROM emp;


ENAME                      COMM
--------------- ----------
SMITH                          0
ALLEN                          1
WARD                          1
JONES                          0
MARTIN                        1
BLAKE                          0
CLARK                          0
SCOTT                         0
KING                             0
TURNER                        1
ADAMS                         0



  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================

신고
Posted by 커피한잔의여유
2008.06.18 14:45
아래 강좌에서 START WITH와 CONNECT BY절을 이용해서 계층 구조로 쿼리를 해오는것을 살펴봤습니다.

이번에는 계층구조를 이용해서 역순으로 쿼리를 해오는것을 보겠습니다.
 
SQLPLUS scott/tiger
-- 조금 깔끔하게 보기 위해서.. 셋팅 먼저 하고요..
SQL> SET LINESIZE 100
SQL> SET PAGESIZE 100
SQL> COL ename FORMAT A20

SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
        FROM emp
        START WITH EMPNO=7839
        CONNECT BY PRIOR empno=mgr;
ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                         7839                            PRESIDENT
    JONES                  7566          7839           MANAGER
        SCOTT              7788          7566          ANALYST
            ADAMS          7876          7788          CLERK
        FORD               7902         7566           ANALYST
            SMITH          7369          7902           CLERK
       
           
위 SQL문은 EMPNO가 7839인 것을 기준으로 EMPNO와 MGR컬럼을 가지고 계층구조로 비교해서 결과값을 반환 합니다.

위 SQL문의 CONNECT BY PRIOR empno=mgr 이 부분을..한번 바꺼 볼까요..

아래처럼.. CONNECT BY empno=PRIOR mgr 이렇게 바꺼서 한번 실행해 보세요..
그럼..역순으로.. 쿼리를 해서 결과값을 반환 합니다

           
SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
        FROM emp
        START WITH EMPNO=7369
       CONNECT BY empno=PRIOR mgr;

 
 
ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
SMITH                      7369         7902   CLERK
    FORD                   7902         7566   ANALYST
        JONES              7566         7839   MANAGER
            KING            7839                    PRESIDENT
           

위에 결과값과 비교해 보세요..
 
이 SQL문은 실제로..실무에서 카테고리의 Depth를 표현할때 아주 많이 사용 합니다.
 
컴퓨터/소프트웨어>모니터>일반CRT모니터
 
이런 카테고리 구조를 하나의 SQL문으로 쉽게 가져 올수가 있죠..



 ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================

신고
Posted by 커피한잔의여유
2008.06.18 14:42

오라클 데이터베이스  scott유저의 emp테이블을 보면 empno와 mgr컬럼이 있습니다.
 

mgr 컬럼의 데이터는 해당 사원의 관리자의 empno를 의미 합니다.
 

예를 들어서 아래의 데이터를 보면은..
 

  EMPNO ENAME         SAL        MGR
 ------ --------- ------- ----------
   7369     SMITH          800       7902
   7902     FORD          3000       7566


empno 7369사원의  관리자는 7902의 empno를 가진 사원이며
empno 7902사원의  관리자는 7566의 empno를 가진 사원입니다.
 
 
이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITHCONNECT BY를 이용해서
쉽게 가져올 수 있습니다.
 
상품의 카테고리(대분류,중분류,소분류...)를 조회 할때 START WITH와 CONNECT BY를 이용하면
트리 구조로 편리하게 조회 할 수 있습니다.


게시판에서의 일반글과 답변글 과의 관계에서도 사용 할 수 있습니다.


 ⊙ START WITH와 CONNECT BY를 이용해 데이터를 계층적인 순서로  조회할 수 있습니다.
 
 
START WITH
 
 - 계층 질의의 루트(부모행)로 사용될 행을 지정 합니다..
 - 서브쿼리를 사용할 수도 있습니다.
 
 
CONNECT BY
 
 - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 합니다.
 - 보통 PRIOR 연산자를 많이 사용 합니다..
 - 서브쿼리를 사용할 수 없습니다..
 
 
 
CONNECT BY의 실행순서는 다음과 같습니다.
 
 - 첫째 START WITH절
 - 둘째 CONNECT BY 절
 - 세째 WHERE 절 순서로 풀리게 되어있습니다.
 


-- 테스트를 위해서 scott유저로 접속을 합니다.
SQLPLUS scott/tiger
 
 
예제1)상위계층과 하위 계층 관계의 순서대로 쿼리해 옴
 
 
-- LEVEL 예약어를 사용하여  depth를 표현 할 수 있습니다.
-- 직업이 PRESIDENT을 기준으로 계층 구조로 조회합니다.
 
SQL>SELECT LEVEL,empno,ename, mgr
        FROM  emp
        START WITH job = ’PRESIDENT’   -- 직업이 PRESIDENT를 기준으로
        CONNECT BY PRIOR  empno = mgr; -- 사원(empno)과 관리자(mgr)의  관계를 계층 구조로 조회
   
    LEVEL      EMPNO ENAME                       MGR
--------- ---------- -------------------- ----------
        1       7839      KING
        2       7566      JONES                      7839
        3       7788      SCOTT                     7566 
        4       7876      ADAMS                     7788
        3       7902      FORD                       7566
        4       7369      SMITH                       7902
        2       7698      BLAKE                       7839
        3       7499      ALLEN                       7698
        3       7521      WARD                       7698
        3       7654      MARTIN                     7698
        3       7844      TURNER                     7698
        3       7900      JAMES                       7698
        2       7782      CLARK                       7839
        3       7934      MILLER                      7782


--  LEVEL컬럼은 depth를 나타냅니다.
-- JONES의 관리자는 KING을 나타냅니다.
--  SCOTT의 관리자는 JONES를 나타냅니다.
--  예제와 같이 상/하의 계층 구조를 쉽게 조회 할 수 있습니다.


예제2) 사원성명을 계층 구조로 보여 줌


SQL>SET LINESIZE 100
SQL>SET PAGESIZE 100
SQL>COL ename FORMAT A20

 -- 예제1의 결과를 가지고 LPAD함수를 이용해서 ename왼쪽에 공백을 추가 했습니다.
SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
       FROM emp
       START WITH job=’PRESIDENT’
       CONNECT BY PRIOR empno=mgr;

 ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                        7839                   PRESIDENT
    JONES                  7566       7839      MANAGER
        SCOTT             7788       7566      ANALYST
            ADAMS         7876       7788      CLERK
        FORD               7902       7566      ANALYST
            SMITH          7369       7902      CLERK
    BLAKE                  7698       7839      MANAGER
        ALLEN               7499       7698     SALESMAN
        WARD               7521       7698     SALESMAN
        MARTIN             7654       7698     SALESMAN
        TURNER            7844       7698     SALESMAN
        JAMES              7900       7698     CLERK
    CLARK                  7782       7839     MANAGER
        MILLER              7934       7782     CLERK


예제3) 레벨이 2까지만 쿼리해서 가져오는 예제
   
SQL>SELECT LPAD(’ ’, 4*(LEVEL-1)) || ename ename, empno, mgr, job
        FROM emp
        START WITH job=’PRESIDENT’
        CONNECT BY PRIOR empno=mgr
        AND LEVEL <=2 ;
   
ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                        7839                   PRESIDENT
    JONES                  7566       7839      MANAGER
    BLAKE                  7698       7839      MANAGER
    CLARK                  7782       7839      MANAGER  


예제4) 각 label별로 급여의 합과 인원수를 구하는 예제
 
SQL> SELECT LEVEL, SUM(sal) total,COUNT(empno) cnt
        FROM emp
        START WITH job=’PRESIDENT’
        CONNECT BY PRIOR empno=mgr      
        GROUP BY LEVEL
        ORDER BY LEVEL;
 
     LEVEL      TOTAL        CNT
---------- ---------- ----------
         1       5000          1
         2       8275          3
         3      13850          8
         4       1900          2


데이터가 많아질 경우....
 
 -
첫째로 풀리는 START WITH job=’PRESIDENT’ job 컬럼에 index가 생성되어 있지 않는다면
    속도를 보장할 수 없습니다.
 
 - 그리고 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 컬럼값이 상수가
   되기 때문에 MGR컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.
   
 - 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로
   표현하기가 어렵 습니다.


참고..
 
http://dblab.changwon.ac.kr/oracle/sqltest/hierarchical.html/
http://www.en-core.com/
  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================
신고
Posted by 커피한잔의여유
2008.06.18 14:39

우리가 일반적으로 점수의 순위는 ODRDER BY desc를 해서 볼수가 있습니다.

하지만 똑같은 점수를 가진 사람들이 많이 나올때..
예를 들면.100, 90, 90, 80 이렇게 점수가 나오면..
순위를 1,2,2,4 이렇게 부여 해야 합니다
 
이거 SQL문으로 할려고 하니 어렵더라구요.. 내 머리가 나쁜건지..

암튼 오라클 8.1.6 버전부터는 아주 쉽게 순위를 부여 할 수 있습니다..

아래 내용을 참고해 보세요...


오라클에서는 RANK Function을 사용해서 순위를 간편하게 부여할 수 있습니다.
 
RANK Function는 oracle 8i(8.1.6) 부터 가능합니다.
8.1.6 이전 버전에서는 사용 할 수 없습니다. ORA-923 error 가 발생 합니다.
 
plsql 내에서는 oracle 9i 부터 가능합니다.  8.1.6에서는 ORA-900 error가 발생 합니다.
 
-- scott유저로 접속을 합니다.
SQLPLUS scott/tiger
 

-- RANK() 함수를 사용하여 급여 순으로 순위를 부여한 예제 입니다. 
-- RK의 출력값을 보면 급여가 같으면 같은 순위로 부여가 됩니다.

SQL>SELECT empno, ename, sal,
        RANK() OVER (ORDER BY sal DESC ) as rk
        FROM emp;


    EMPNO ENAME             SAL         RK
--------- ---------- ---------- ----------
     7839 KING               5000          1
     7788 SCOTT            3000         2
     7902 FORD              3000          2
     7566 JONES            2975          4
     7698 BLAKE            2850          5
     7782 CLARK            2450          6
     7499 ALLEN            1600          7
     7844 TURNER          1500          8
     7934 MILLER           1300          9
     7521 WARD             1250         10
     7654 MARTIN            1250         10
     7876 ADAMS            1100         12
     7900 JAMES             950         13
     7369 SMITH              800         14



☞ 그룹별로 순위를 부여 하는 법

-- 위 예제는 deptno를 파티션으로 나누어서 부서별로 순위를 부여 합니다.
-- 특정한 그룹별로 순위를 부여하고 싶을때 사용 하면 편합니다.

SQL>SELECT deptno, ename, sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal DESC ) as rk
       FROM emp ;
 
 DEPTNO ENAME             SAL        RK
------- ---------- ---------- ---------
     10 KING              5000          1
     10 CLARK            2450         2
     10 MILLER           1300         3
     20 SCOTT            3000         1
     20 FORD             3000          1
     20 JONES            2975         3
     20 ADAMS            1100         4
     20 SMITH             800           5
     30 BLAKE            2850          1
     30 ALLEN            1600          2
     30 TURNER           1500        3
     30 WARD             1250          4
     30 MARTIN           1250         4
     30 JAMES             950          6
 


☞ DENSE_RANK() 함수

 DENSE_RANK( ) - 중복 RANK의 수와 무관하게 numbering을 합니다.

-- 1등, 2등, 2등 이렇게 2등이 중복되었는데 4등이 아니라 3등이 부여 됩니다.

SQL>SELECT empno, ename, sal, 
       DENSE_RANK() OVER (ORDER BY sal DESC ) as rk
       FROM emp;    
   
    EMPNO ENAME             SAL        RK
--------- ---------- ---------- ---------
     7839 KING               5000         1
     7788 SCOTT            3000         2
     7902 FORD              3000         2
     7566 JONES            2975         3
     7698 BLAKE             2850         4
     7782 CLARK             2450         5
     7499 ALLEN             1600         6
     7844 TURNER          1500         7
     7934 MILLER            1300         8
     7521 WARD             1250         9
     7654 MARTIN           1250         9
     7876 ADAMS            1100        10
     7900 JAMES             950        11
     7369 SMITH             800        12
  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================
신고
Posted by 커피한잔의여유

티스토리 툴바