http://loveu.oranc.co.kr/bbs/zboard.php?id=skill&page=2&sn1=&divpage=1&category=2&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=48 오라클 데이터베이스 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 WITH와 CONNECT 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으로 표현하기가 어렵 습니다. 아래 강좌에서 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문으로 쉽게 가져 올수가 있죠.. |
'oracle'에 해당되는 글 8건
- 2010.10.06 oracle
- 2010.10.06 START WITH와 CONNECT BY절을 이용해서 계층 구조로 쿼리
- 2010.10.06 oracle 행열,열행 변환
- 2010.10.06 Oracle 내부함수
- 2010.10.06 오라클 ROLLUP - 소계, 합계 구할때 사용 SQL
- 2010.05.31 ORA-00054 : 자원이 사용중이고, NOWAIT가 지정되어 있습니다 SQL_튜닝
- 2010.03.03 racle startup 시 발생하는 ORA-01157,ORA-01110 오류 해결법
- 2010.01.21 오라클 Connect By 절 응용 쿼리 - 콤마(,)로 구분된 스트링을 레코드로 만드
START WITH와 CONNECT BY절을 이용해서 계층 구조로 쿼리
http://loveu.oranc.co.kr/bbs/zboard.php?id=skill&page=2&sn1=&divpage=1&category=2&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=49
http://loveu.oranc.co.kr/bbs/zboard.php?id=skill&page=2&sn1=&divpage=1&category=2&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=49
아래 강좌에서 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문으로 쉽게 가져 올수가 있죠.. |
oracle 행열,열행 변환
[오라클]행->열, 열->행 변환 /*--------------------------------------------------- * ROW => COLUMN의 변환 * COLUMN => ROW의 변환 ----------------------------------------------------*/ --------------- DEPTNO EMPNO --------------- 10 7782 10 7839 10 7934 20 7369 20 7566 20 7788 30 7499 30 7521 30 7654 ------------------------------ DEPTNO EMP1 EMP2 EMP3 ------------------------------ 10 7782 7839 7934 20 7369 7566 7788 30 7499 7521 7654 /* COLUMN => ROW 시작 */ SELECT A.DEPTNO, DECODE(C.NO, 1, A.EMP1, 2, A.EMP2, 3, A.EMP3) EMPNO FROM ( /* ROW => COLUMN 시작 */ SELECT DEPTNO, MAX(DECODE(RID, 1, EMPNO)) EMP1, MAX(DECODE(RID, 2, EMPNO)) EMP2, MAX(DECODE(RID, 3, EMPNO)) EMP3 FROM ( SELECT DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID, EMPNO FROM EMP ) GROUP BY DEPTNO /* ROW => COLUMN 종료 */ ) A, COPY_T C WHERE C.NO <= 3 /* COLUMN => ROW 종료 */ /*--------------------------------------------------- * CROSSTAB에서 열을 행으로 행을 열로 변환 * ROW => COLUMN, COLUMN => ROW을 한꺼번에 구현 ----------------------------------------------------*/ ------------------------------ DEPTNO EMP1 EMP2 EMP3 ------------------------------ 10 7782 7839 7934 20 7369 7566 7788 30 7499 7521 7654 ------------------------------ EMP DEPT_10 DEPT_20 DEPT_30 ------------------------------ EMP1 7782 7369 7499 EMP2 7839 7566 7521 EMP3 7934 7788 7654 SELECT DECODE(C.NO, 1, 'EMP1', 2, 'EMP2', 3, 'EMP3') EMP, MAX(DECODE(A.DEPTNO||C.NO2, '1001', A.EMP1, '1002', A.EMP2, '1003', A.EMP3)) DEPT_10, MAX(DECODE(A.DEPTNO||C.NO2, '2001', A.EMP1, '2002', A.EMP2, '2003', A.EMP3)) DEPT_20, MAX(DECODE(A.DEPTNO||C.NO2, '3001', A.EMP1, '3002', A.EMP2, '3003', A.EMP3)) DEPT_30 FROM ( /* 원래의 ROW, COLUMN구조 시작 */ SELECT DEPTNO, MAX(DECODE(RID, 1, EMPNO)) EMP1, MAX(DECODE(RID, 2, EMPNO)) EMP2, MAX(DECODE(RID, 3, EMPNO)) EMP3 FROM ( SELECT DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID, EMPNO FROM EMP ) GROUP BY DEPTNO /* 원래의 ROW, COLUMN구조 종료 */ ) A, COPY_T C WHERE C.NO <= 3 GROUP BY DECODE(C.NO, 1, 'EMP1', 2, 'EMP2', 3, 'EMP3') /*--------------------------------------------------- * 참고) COPY_T 의 생성 ----------------------------------------------------*/ CREATE TABLE COPY_T AS SELECT ROWNUM NO ,TO_CHAR(ROWNUM, 'FM00') NO2 FROM ALL_OBJECTS WHERE ROWNUM <= 31 CREATE UNIQUE INDEX COPY_T_IDX1 ON COPY_T(NO) CREATE UNIQUE INDEX COPY_T_IDX2 ON COPY_T(NO2) /*--------------------------------------------------- * ROW_NUMBER() 함수의 기능을 구현 => 테이블을 두번 읽기 * ROWNUM이 지원되지 않는 DBMS에서 ROWNUM 구현도 유사 ----------------------------------------------------*/ SELECT DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID, EMPNO FROM EMP SELECT A.DEPTNO, COUNT(*) RID, A.EMPNO FROM EMP A, EMP B WHERE A.DEPTNO = B.DEPTNO /* PARTITION BY 기능 */ AND A.EMPNO >= B.EMPNO /* ORDER BY 기능 => 반드시 UNIQUE 해야함 */ GROUP BY A.DEPTNO, A.EMPNO /* PARTITION BY,ORDER BY 기능 */ ORDER BY A.DEPTNO, A.EMPNO /* PARTITION BY,ORDER BY 기능 */ /*--------------------------------------------------- * COPY_T 테이블이 없을때 COPY_T 기능 구현방법 ----------------------------------------------------*/ SELECT NO, NO2 FROM COPY_T WHERE NO <= 5 /*------------------------------------------ * 1.USER_OBJECTS 테이블의 이용 * 최대한 가벼운 테이블 이용 * USER_OBJECTS가 가벼운지는 검증할 문제임 -------------------------------------------*/ SELECT ROWNUM NO, TO_CHAR(ROWNUM, 'FM00') NO2 FROM USER_OBJECTS WHERE ROWNUM <= 5 /*------------------------------------------ * 2.DUAL 테이블의 이용 * 복사갯수가 적을때 이용(2~3개) -------------------------------------------*/ SELECT NO, TO_CHAR(NO, 'FM00') NO2 FROM ( SELECT 1 NO FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 3 FROM DUAL UNION ALL SELECT 4 FROM DUAL UNION ALL SELECT 5 FROM DUAL ) /*--------------------------------------------------- * 참고) DUAL 테이블이 없는 경우의 구현(SQL SERVER) ----------------------------------------------------*/ CREATE VIEW DUAL AS SELECT 'X' DUMMY_COL SELECT GETDATE() FROM DUAL |
http://loveu.oranc.co.kr/bbs/zboard.php?id=skill&page=2&sn1=&divpage=1&category=2&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=90
oracle
오라클 버젼확인 : select * from product_component_version;
산술 함수
ABS(n) : 절대값 |n|
sin() cos() tan() sinH() cosH() tanH() : radian값을 이용
ex> 30' 일 경우 sin(30* (3.14.../180));
exp(n) : 지수값 e를 밑으로 하는 e의 n승 값;
LN(n):자연 log값 밑수 e 지수 n
SQRT(n): 루트n
power(m,n): m의 n승
MOD(m.n) : m을 n 으로 나눈 나머지
floor(n): n보다 작은 정수값들중에서 최대값
ceil(n): n보다 큰 정수값들중에서 최소값
ex> floor(3.5) --> 3 , ceil (3.5) --> 4
floor(-3.2) --> -4 , ceil (-3.5) --> -3
greatest(m,n): 두 수중에서 큰것
least(m,n);두 수중에서 작은 것
TO_NUMBER('숫자형 문자열'):문자를 숫자로 변환
round(m,n):반올림
trunc(m,n):절삭한다 즉 반올림을 허용하지 않는다.
ex> Round(2.4678, 2) --> 2.47 , trunc(2.4687, 2) --> 2.46
Round(3654.26, -2) --> 3700 , trunc (3654.26, -2 ) --> 3600
NVL(col명,값): col의 값이 null를 가질때 값을 가진다.
ex> 100+NULL은 NULL이 되기 때문에 이러한 문제점을 해결할 때 사용한다.
select ename,sal,comm,sal+nvl(comm,0) from emp;
-->sal값과 comm값을 더하는데 comm값이 null값이면 0값을 취한다...
문자열 함수
lower():소문자로 변환시켜주는 함수
upper():대문자로 변환시켜주는 함수
Initcap():단어의 첫자만 대문자로 나머지는 소문자로
Initcap('i LOVE you') ----> I Love You
length():문자열의 길이
substr(문자열,위치,갯수): 해당위치에서 갯수만큼의 문자열을 추출한다.
substr ('KOREA', 3, 2) ----> 'RE'
-- 3번째인 R부터 2글자 출력
instr(문자열,찾을 문자열[,위치,찾을위치])
:문자열에서 이 문자를 찾아라. return값은 찾은 위치
Instr("abcdabkdoerabjdlfjdg","ab") -----> 1
-- 1번째에서 시작해서 처음 ab가 나타나는 1출력
Instr("abcdabkdoerabjdlfjdg","ab",4,2) ----> 12
-- 4번째부터 시작해서 두번째 나타나는 ab 출력
user:현재 접속된 사용자를 보여준다.
decode(col명,비교값,취할값,비교값,취할값,....기본값)
:col값이 비교값이면 취할값으로 대체.
decode(deptno,10,'총무부',20,'영업부',30,'전산부','관리부'):나머지는 관리부
-- deptno가 10이면 총무부 , 20이면 영업부, 30이면 전산부 나머지 는 관리부 출력
trim : 좌우 공백 제거
날짜 함수
TO_CHAR(날짜,'포맷'): 날짜를 문자열로 바꾼다.
TO_DATE(문자열,'포맷'):문자열을 날짜로 ex> TO_DATE('97-10-14',' -----')
ADD_MONTHS(날짜,n):현재 날짜에 n개월수를 더한다.
MONTHS_BETWEEN(날짜1,날짜2):두 날짜의 개월수의 차를 구한다.
LAST_DAY(날짜):그 달의 마지막 날짜를 구한다.
NEXT_DAY(날짜,'요일'):날짜로 부터 다음에 나오는 요일은 며칠인가
next_day(sysdate,'금요일')
날짜 + n : 날짜에다 n 일을 더한다.
포맷:
yy : 년도를 2자리로
yyyy : 년도를 4자리로
year : 영어 철자로 표시
mm :월을 표시(01-12)
mon :월을 표시(DEC....):약자로 나온다.
month :월을 표시(1월,2월,....혹은 ,DECEMBER)
d :일을 표시(주에 대한 일)
dd :일을 표시 (월에 대한 일)
ddd :일을 표시 (년에 대한 일)
Q : 분기를 구한다.
DAY :요일 (월요일)
DY :요일(월)
HH & HH12 :시간을 12시간제로
HH24 :24시간제로
MI:분
SS:초
AM & PM & A.M & P.M:12시간제 일때 오전 오후를 표시
접미사로 사용하는 포맷
TH:서수로 표시 ex> 4 -> DD ->4
4-> DDTH -> 4TH
SP:철자로 표시 ex> 4 ->DDSP -> FOUR
SPTH & THSP ex> 4 -> DDSPTH -> FOURTH
근무한 년월 수 ex> column "근무년수" Format a 14;
그룹 함수
NULL값은 계산되지 않는다.
여러개의 데이타에 대한 결과 이므로 그룹함수라 한다
단일 필드와 함께 사용할 수 없다.
만약 단일 필드와 사용되면 group by절에 반드시 나와야 한다.
sum() : 합
avg() : 평균
count() : 갯수 count(*): 리턴되는 레코드의 수를 계산
ex> select deptno,sum(sapay) from sawon; 에러
-->단위 필드이므로 따라서 마지막에 group by deptno;라고 지정해주면 사용가능
max() : 최대값
min() : 최소값
stddev : 표준편차
variance : 분산
:: 예제 ::
오늘 날짜에 2틀씩 뺀것
select sysdate+2, sysdate-2 from dual;
오늘날짜에 3시간을 더한것과 20분을 더한것
select sysdate+3/24, sysdate+20/1440 from dual;
오늘날짜에 며칠을 뺀것
alter session
set nls_date_format = 'yyyy-mm-dd;
select sysdate-to_date('2005-03-02') from dual;
위에서 세션을 변경해준것은 2005-03-02가 문자로 인식되기 때문에 바꾸어 준것이과 기존에 되어 있던 날짜 입력형태가 시간까지 들어가 있기 때문에 그런것이다.
오늘날짜에 3개월을 더하고 3개월을 뺀것
select add_months(sysdate,3), add_months(sysdate,-3) from dual;
오늘을 기준으로 가장 가까운 토요일, 가장가까운 일요일
select next_day(sysdate,'토'),next_day(sysdate,'월'),next_day(sysdate,'금') from dual;
여기에서 next_day의 형식은 날짜 다음 요일이라는 것이 오라클 사에서 지정이 되어 있기 때문에 '토'가 문자라 인싟하지 않는 것이다
오늘부터 1월1일까지 몇개월 지났을까
select months_between(sysdate,'2005-01-01) from personel;
[형변환]
To_Number : 숫자로 바꾸어라
T0_date : 날짜로 바꾸어라
세션변경---------------------
alter session
set nls_date_format = 'yyyy-mm-dd day hh24:mi:ss BC';
변경후 위에 것을 다시 실행해 보세요
날짜에 숫자를 연산했을 경우에는 날짜가 나옴
data + number = date
data - number = date
date - date = Number of days
date + number/24 = Date
oracle
오라클 버젼확인 : select * from product_component_version;
산술 함수
ABS(n) : 절대값 |n|
sin() cos() tan() sinH() cosH() tanH() : radian값을 이용
ex> 30' 일 경우 sin(30* (3.14.../180));
exp(n) : 지수값 e를 밑으로 하는 e의 n승 값;
LN(n):자연 log값 밑수 e 지수 n
SQRT(n): 루트n
power(m,n): m의 n승
MOD(m.n) : m을 n 으로 나눈 나머지
floor(n): n보다 작은 정수값들중에서 최대값
ceil(n): n보다 큰 정수값들중에서 최소값
ex> floor(3.5) --> 3 , ceil (3.5) --> 4
floor(-3.2) --> -4 , ceil (-3.5) --> -3
greatest(m,n): 두 수중에서 큰것
least(m,n);두 수중에서 작은 것
TO_NUMBER('숫자형 문자열'):문자를 숫자로 변환
round(m,n):반올림
trunc(m,n):절삭한다 즉 반올림을 허용하지 않는다.
ex> Round(2.4678, 2) --> 2.47 , trunc(2.4687, 2) --> 2.46
Round(3654.26, -2) --> 3700 , trunc (3654.26, -2 ) --> 3600
NVL(col명,값): col의 값이 null를 가질때 값을 가진다.
ex> 100+NULL은 NULL이 되기 때문에 이러한 문제점을 해결할 때 사용한다.
select ename,sal,comm,sal+nvl(comm,0) from emp;
-->sal값과 comm값을 더하는데 comm값이 null값이면 0값을 취한다...
문자열 함수
lower():소문자로 변환시켜주는 함수
upper():대문자로 변환시켜주는 함수
Initcap():단어의 첫자만 대문자로 나머지는 소문자로
Initcap('i LOVE you') ----> I Love You
length():문자열의 길이
substr(문자열,위치,갯수): 해당위치에서 갯수만큼의 문자열을 추출한다.
substr ('KOREA', 3, 2) ----> 'RE'
-- 3번째인 R부터 2글자 출력
instr(문자열,찾을 문자열[,위치,찾을위치])
:문자열에서 이 문자를 찾아라. return값은 찾은 위치
Instr("abcdabkdoerabjdlfjdg","ab") -----> 1
-- 1번째에서 시작해서 처음 ab가 나타나는 1출력
Instr("abcdabkdoerabjdlfjdg","ab",4,2) ----> 12
-- 4번째부터 시작해서 두번째 나타나는 ab 출력
user:현재 접속된 사용자를 보여준다.
decode(col명,비교값,취할값,비교값,취할값,....기본값)
:col값이 비교값이면 취할값으로 대체.
decode(deptno,10,'총무부',20,'영업부',30,'전산부','관리부'):나머지는 관리부
-- deptno가 10이면 총무부 , 20이면 영업부, 30이면 전산부 나머지 는 관리부 출력
trim : 좌우 공백 제거
날짜 함수
TO_CHAR(날짜,'포맷'): 날짜를 문자열로 바꾼다.
TO_DATE(문자열,'포맷'):문자열을 날짜로 ex> TO_DATE('97-10-14',' -----')
ADD_MONTHS(날짜,n):현재 날짜에 n개월수를 더한다.
MONTHS_BETWEEN(날짜1,날짜2):두 날짜의 개월수의 차를 구한다.
LAST_DAY(날짜):그 달의 마지막 날짜를 구한다.
NEXT_DAY(날짜,'요일'):날짜로 부터 다음에 나오는 요일은 며칠인가
next_day(sysdate,'금요일')
날짜 + n : 날짜에다 n 일을 더한다.
포맷:
yy : 년도를 2자리로
yyyy : 년도를 4자리로
year : 영어 철자로 표시
mm :월을 표시(01-12)
mon :월을 표시(DEC....):약자로 나온다.
month :월을 표시(1월,2월,....혹은 ,DECEMBER)
d :일을 표시(주에 대한 일)
dd :일을 표시 (월에 대한 일)
ddd :일을 표시 (년에 대한 일)
Q : 분기를 구한다.
DAY :요일 (월요일)
DY :요일(월)
HH & HH12 :시간을 12시간제로
HH24 :24시간제로
MI:분
SS:초
AM & PM & A.M & P.M:12시간제 일때 오전 오후를 표시
접미사로 사용하는 포맷
TH:서수로 표시 ex> 4 -> DD ->4
4-> DDTH -> 4TH
SP:철자로 표시 ex> 4 ->DDSP -> FOUR
SPTH & THSP ex> 4 -> DDSPTH -> FOURTH
근무한 년월 수 ex> column "근무년수" Format a 14;
그룹 함수
NULL값은 계산되지 않는다.
여러개의 데이타에 대한 결과 이므로 그룹함수라 한다
단일 필드와 함께 사용할 수 없다.
만약 단일 필드와 사용되면 group by절에 반드시 나와야 한다.
sum() : 합
avg() : 평균
count() : 갯수 count(*): 리턴되는 레코드의 수를 계산
ex> select deptno,sum(sapay) from sawon; 에러
-->단위 필드이므로 따라서 마지막에 group by deptno;라고 지정해주면 사용가능
max() : 최대값
min() : 최소값
stddev : 표준편차
variance : 분산
:: 예제 ::
오늘 날짜에 2틀씩 뺀것
select sysdate+2, sysdate-2 from dual;
오늘날짜에 3시간을 더한것과 20분을 더한것
select sysdate+3/24, sysdate+20/1440 from dual;
오늘날짜에 며칠을 뺀것
alter session
set nls_date_format = 'yyyy-mm-dd;
select sysdate-to_date('2005-03-02') from dual;
위에서 세션을 변경해준것은 2005-03-02가 문자로 인식되기 때문에 바꾸어 준것이과 기존에 되어 있던 날짜 입력형태가 시간까지 들어가 있기 때문에 그런것이다.
오늘날짜에 3개월을 더하고 3개월을 뺀것
select add_months(sysdate,3), add_months(sysdate,-3) from dual;
오늘을 기준으로 가장 가까운 토요일, 가장가까운 일요일
select next_day(sysdate,'토'),next_day(sysdate,'월'),next_day(sysdate,'금') from dual;
여기에서 next_day의 형식은 날짜 다음 요일이라는 것이 오라클 사에서 지정이 되어 있기 때문에 '토'가 문자라 인싟하지 않는 것이다
오늘부터 1월1일까지 몇개월 지났을까
select months_between(sysdate,'2005-01-01) from personel;
[형변환]
To_Number : 숫자로 바꾸어라
T0_date : 날짜로 바꾸어라
세션변경---------------------
alter session
set nls_date_format = 'yyyy-mm-dd day hh24:mi:ss BC';
변경후 위에 것을 다시 실행해 보세요
날짜에 숫자를 연산했을 경우에는 날짜가 나옴
data + number = date
data - number = date
date - date = Number of days
date + number/24 = Date
http://loveu.oranc.co.kr/bbs/zboard.php?id=skill&page=1&sn1=&divpage=1&category=2&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=100
1. ROLLUP의 단계적 이해
: 소계, 합계를 같이 구할 때 ROLLUP 사용한다.
a. 전체 합
SELECT SUM(salary)
FROM employees
WHERE department_id < 50;
SUM(SALARY)
-----------
54800
b. 부서별/직급별 합
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY department_id, job_id;
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
30 PU_MAN 11000
30 PU_CLERK 13900
40 HR_REP 6500
c. 부서별 합
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY department_id;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
10 4400
20 19000
30 24900
40 6500
d. a,b,c 의 결과를 union all한 결과와 비교
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400
10 4400 -->> DEPARTMENT_ID별 SUM
20 MK_MAN 13000
20 MK_REP 6000 -->> DEPARTMENT_ID별 SUM
20 19000
30 PU_MAN 11000
30 PU_CLERK 13900
30 24900 -->> DEPARTMENT_ID별 SUM
40 HR_REP 6500
40 6500 -->> DEPARTMENT_ID별 SUM
54800 -->> DEPARTMENT_ID별 총계
2. 컬럼의 구분은 어떻게 ?
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
DEPTNO JOB SUM(SAL)
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 ---->> DEPTNO별 SUM
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875 ---->> DEPTNO별 SUM
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 ---->> DEPTNO별 SUM
29025 ---->> 총계
소계, 합계를 구분하고자 할때 GROUPING(컬럼) 또는 GROUPING_ID(컬럼1, 컬럼2)
를 사용한다.GROUPING() 함수는 해당 컬럼이 집합에 참여하지 않으면 1을 반환, 집합계산에 참여하면 0을 반환
SELECT DEPTNO, JOB, SUM(SAL),
GROUPING(DEPTNO) A,
GROUPING(JOB) B,
GROUPING_ID(DEPTNO,JOB) C
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
DEPTNO JOB SUM(SAL) A B C
10 CLERK 1300 0 0 0
10 MANAGER 2450 0 0 0
10 PRESIDENT 5000 0 0 0
10 8750 0 1 1 ---->> DEPTNO별 SUM
20 CLERK 1900 0 0 0
20 ANALYST 6000 0 0 0
20 MANAGER 2975 0 0 0
20 10875 0 1 1 ---->> DEPTNO별 SUM
30 CLERK 950 0 0 0
30 MANAGER 2850 0 0 0
30 SALESMAN 5600 0 0 0
30 9400 0 1 1 ---->> DEPTNO별 SUM
29025 1 1 3 ---->> 총계
1. ROLLUP의 단계적 이해
: 소계, 합계를 같이 구할 때 ROLLUP 사용한다.
a. 전체 합
SELECT SUM(salary)
FROM employees
WHERE department_id < 50;
SUM(SALARY)
-----------
54800
b. 부서별/직급별 합
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY department_id, job_id;
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
30 PU_MAN 11000
30 PU_CLERK 13900
40 HR_REP 6500
c. 부서별 합
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY department_id;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
10 4400
20 19000
30 24900
40 6500
d. a,b,c 의 결과를 union all한 결과와 비교
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400
10 4400 -->> DEPARTMENT_ID별 SUM
20 MK_MAN 13000
20 MK_REP 6000 -->> DEPARTMENT_ID별 SUM
20 19000
30 PU_MAN 11000
30 PU_CLERK 13900
30 24900 -->> DEPARTMENT_ID별 SUM
40 HR_REP 6500
40 6500 -->> DEPARTMENT_ID별 SUM
54800 -->> DEPARTMENT_ID별 총계
2. 컬럼의 구분은 어떻게 ?
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
DEPTNO JOB SUM(SAL)
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 ---->> DEPTNO별 SUM
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875 ---->> DEPTNO별 SUM
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 ---->> DEPTNO별 SUM
29025 ---->> 총계
소계, 합계를 구분하고자 할때 GROUPING(컬럼) 또는 GROUPING_ID(컬럼1, 컬럼2)
를 사용한다.GROUPING() 함수는 해당 컬럼이 집합에 참여하지 않으면 1을 반환, 집합계산에 참여하면 0을 반환
SELECT DEPTNO, JOB, SUM(SAL),
GROUPING(DEPTNO) A,
GROUPING(JOB) B,
GROUPING_ID(DEPTNO,JOB) C
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
DEPTNO JOB SUM(SAL) A B C
10 CLERK 1300 0 0 0
10 MANAGER 2450 0 0 0
10 PRESIDENT 5000 0 0 0
10 8750 0 1 1 ---->> DEPTNO별 SUM
20 CLERK 1900 0 0 0
20 ANALYST 6000 0 0 0
20 MANAGER 2975 0 0 0
20 10875 0 1 1 ---->> DEPTNO별 SUM
30 CLERK 950 0 0 0
30 MANAGER 2850 0 0 0
30 SALESMAN 5600 0 0 0
30 9400 0 1 1 ---->> DEPTNO별 SUM
29025 1 1 3 ---->> 총계
2006/12/19 18:42 |
-- 특정 테이블을 access 하고 있는 것을 조회
SELECT *
FROM v$access
WHERE OBJECT = '오브젝트 이름'
;
-- 위의 결과 중 SID 로 세션 조회
SELECT *
FROM v$session
WHERE SID = 718
;
-- 위의 결과 중 SID, SERIAL# 로 세션 KILL
ALTER SYSTEM KILL SESSION '718,1014'
;
Orange ==> Session Monitor ==> ACTIVE 상태의 세션을 클릭하여 SQL 확인하고 해당 Process ID를 KILL 한다
오라클의 startup 시에 발생하는 ORA-01157,ORA-01110 오류는 OS상에 해당 테이블스페이스가 없거나 다른 이유에 의해서 열수 없는 경우입니다.
=========================================================================================================================
SQL>STARTUP
....
....
....
....
Database mounted
ORA-01157 : cannot identify/lock data file 12 - see DBWR trace file
ORA-01110 : data file 12: ' 파일 경로 '
이렇게 오류가 발생하면 밑의 쿼리를 실행하면 해결이 됩니다.
SQL> ALTER DATABASE DATAFILE ' 파일 경로 ' OFFLINE DROP;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered
오라클 Connect By 절 응용 쿼리 - 콤마(,)로 구분된 스트링을 레코드로 만드
원 제목: 오라클 Connect By 절 응용 쿼리 - 콤마(,)로 구분된 스트링을 레코드로 만드는 쿼리.
예를 들어 :Bind1 = 123,232,345,2353,54747,74432,457547 라는 변수가 있다고 하면
원 제목: 오라클 Connect By 절 응용 쿼리 - 콤마(,)로 구분된 스트링을 레코드로 만드는 쿼리.
예를 들어 :Bind1 = 123,232,345,2353,54747,74432,457547 라는 변수가 있다고 하면
이 스트링값의 변수를
==============
SUB
==============
123
232
345
2353
54747
74432
457547
==============
와 같이 변환 해 주는 쿼리
======================================================================================
SELECT
SUBSTR(
CODE,
INSTR (CODE, ',', 1, LEVEL) + 1,
INSTR (CODE, ',', 1, LEVEL + 1) - INSTR (CODE, ',', 1, LEVEL)
- 1
) SUB
FROM DUAL A
CROSS JOIN (SELECT ','|| :Bind1 ||',' AS CODE FROM DUAL) B
CONNECT BY LEVEL<= LENGTH(CODE) - LENGTH(REPLACE(CODE, ',')) - 1;
SUBSTR(
CODE,
INSTR (CODE, ',', 1, LEVEL) + 1,
INSTR (CODE, ',', 1, LEVEL + 1) - INSTR (CODE, ',', 1, LEVEL)
- 1
) SUB
FROM DUAL A
CROSS JOIN (SELECT ','|| :Bind1 ||',' AS CODE FROM DUAL) B
CONNECT BY LEVEL<= LENGTH(CODE) - LENGTH(REPLACE(CODE, ',')) - 1;
======================================================================================
선택된 시퀀스 들로 부터 무엇인가 공통된 작업을 진행하고자 할때 프로그램으로 부터 넘어온
배열을 그대로 사용가능하게 해주는 쿼리......