2012년 8월 8일 수요일

[oracle] grouping(), rollup, cube (그룹별 소계 구할 때 사용)

/****************************************************************
  작성자 : 삼이
  작성일 : 2004-09-30
  제  목 : Grouping(), ROLLUP, CUBE Study 정리
 ****************************************************************/

* ROLLUP 연산자
 - GROUP BY절에 있는 컬럼들을 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고,
   각 그룹에 계산함수를 적용한다.
 - GROUP BY절의 결과는 누적 계산 결과이다.
 
* CUBE 연산자
 - GROUP BY절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성한다.
 
* GROUPING 함수 - 각 결과 행이 CUBE, ROLLUP 연산자들에 의해 계산된 것인지를 알기 위해 사용된다.
 - 해당컬럼에 대해 계산되었다면 0, 그렇지 않다면(컬럼값이 NULL) 1을 반환한다.
 - GROUP BY절에 나타나는 컬럼에 적용된다.

사용 예)
  -- table생성(사원이름,급여,부서,직위,입사년도)  CREATE TABLE roll_test (
    name   VARCHAR2(10),
    sal    NUMBER,
    dept   VARCHAR2(10),
    duty   VARCHAR2(10),
    entYear NUMBER(4)
  );
INSERT INTO roll_Test VALUES('kim' , 1000, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('no' , 1500, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('choi', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('park', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('lee' , 3000, 'CC', '03', 2002);
INSERT INTO roll_Test VALUES('cho' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('lyu' , 4000, 'DD', '04', 2001);
INSERT INTO roll_Test VALUES('ham' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('kang', 7000, 'DD', '05', 2001);
COMMIT;
SELECT * FROM roll_Test;

1. 각 부서에 대한 급여 소계를 구하고, 총계를 구하라
   (하나의 Column Grouping)
-----일반-----------
SELECT dept, SUM(sal)
FROM   roll_Test
GROUP BY dept;
---------------------
----- ROLLUP ---------
SELECT dept, SUM(sal), GROUPING(dept)
FROM   roll_Test
GROUP BY ROLLUP(dept);
-----------------------
----- CUBE ----------
SELECT dept, SUM(sal), GROUPING(dept)
FROM roll_Test
GROUP BY CUBE(dept);
-----------------------
ㆍ 일반적인 GROUP BY를 사용할 경우 급여 소계만 나오고, 총계는 따로 구해야 함
ㆍ ROLLUP과 CUBE 차이점 없음

2. 각 부서별, 직위별 급여 소계를 구하고, 총계를 구하라
   (두개의Column Grouping)
-----NORMAL-----------
SELECT dept, duty, SUM(sal)
FROM   roll_Test
GROUP BY dept, duty;
-----------------------
----- ROLLUP ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM   roll_Test
GROUP BY ROLLUP(dept, duty);
-----------------------
----- CUBE ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM   roll_Test
GROUP BY CUBE(dept, duty);
-----------------------
ㆍ ROLLUP은 부서에 대한 소계 / 부서에 대한 직위별 소계만 볼 수 있고,
ㆍ CUBE는 부서에 대한 소계 / 부서에 대한 직위별 소계 / 직위별 소계를 볼 수 있음
ㆍ GROUP BY 내의 왼쪽 컬럼부터 자동으로 오름차순 정렬 됨

3. 각 부서별, 직위별, 입사년도별 급여 소계를 구하고   , 총계를 구하라
  (세개의 Column Grouping)
-----NORMAL-----------
SELECT dept, duty, entYear, SUM(sal)
FROM   roll_Test
GROUP BY dept, duty, entYear;
-----------------------
----- ROLLUP ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM   roll_Test
GROUP BY ROLLUP(dept, duty, entYear);
-----------------------
----- CUBE ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM   roll_Test
GROUP BY CUBE(dept, duty, entYear);
-----------------------

ㆍ ROLLUP 사용시 3개의 소계와 1개의 총계를 구할 수 있음
  (부서별, 부서*직위별, 부서*직위*입사년도별, 총계)
  ※ GROUP BY 내의 가장 왼쪽 컬럼을 기준으로 하여 순차적으로 하위 그룹 생성
ㆍ CUBE 사용시 7개의 소계와 1개의 총계를 구할 수 있음.
  (부서별, 직위별, 입사년도별, 부서*직위별, 부서*입사년도별, 직위*입사년도별, 부서*직위*입사년도별, 총계)
  ※ 생성 가능한 모든 경우를 그룹 생성
 

사용 예)
ㆍ 부서에 대한 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 1
AND GROUPING(entYear) = 1;
ㆍ 각 부서에 대한 직위별 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 0
AND GROUPING(entYear) = 1;


원문 URL : http://fly32.net/252
작성자: 시간: 댓글 없음:

[oracle] sql에서 연속된 숫자 발생시키는 쿼리

* connect by 절 사용

1. 사용자가 지정한 특정 수만큼 숫자 발생
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 지정한 숫자;

2. 올 해 12.31일까지 DATE 발생
SELECT TO_DATE(TO_CHAR(level, '000'), 'ddd')
FROM DUAL
CONNECT BY level<=TO_NUMBER(TO_CHAR(TO_DATE('12-31', 'MM-DD'), 'DDD'));

→ 어렵다..
작성자: 시간: 댓글 1개:

[orcle] order by 조건 사용하기


ORDER BY의 DEFAULT는 ASC!
오라클 예제는 SCOTT/TIGER와 함께..

1.  DECODE 사용하기
-- 정렬순서 : 부서명
-- 1          : OPERATION
-- 2          : SALES
-- 3          : RESEARCH
-- 4          : ACCOUNTING
SELECT DNAME
FROM DEPT
ORDER BY DECODE(DNAME, 'OPERATIONS', 1, 'SALES', 2, 'RESEARCH', 3, 'ACCOUNTING', 4);


2. NULL값 사용해서 정렬하기 (ORDER BY 저
ASC   정렬일 때 : LAST 값
DESC 정렬일 때 : FIRST 값

//COLUMN에 NULL값이 있는 게 아닐 때
-- RESEARCH를 가장 마지막값으로 정렬할 때
SELECT DNAME
FROM DEPT
ORDER BY CASE WHEN (DNAME='RESEARCH') THEN NULL
                        ELSE DNAME END;
(ORDER BY 절에 조건식 사용이 가능!)

//COLUMN에 NULL값을 가지고 있을 때
-- ASC일 때 NULL값 FIRST 값으로 정렬
ORDER BY NVL(COLUMN명, '')
--DESC일 때 NULL값 LAST 값으로 정렬
ORDER BY COLUMN명 NULLS LAST
작성자: 시간: 댓글 없음:

2012년 6월 21일 목요일

[jsp] img태그 src에 한글명 넣기(톰캣)



1. html
   <img src = "filedownload.jsp?filename=사진.jpg">




2. filedownload.jsp
-----------------------------------------------------------

<%@page contentType="text/html; charset=euc-kr" import ="java.io.*,java.text.*,java.util.*"%>


<%


String filename = new String(request.getParameter("filename").getBytes("ISO-8859-1"),"euc-kr");
String filename1 = java.net.URLEncoder.encode(filename, "UTF8");


System.out.println(filename);
System.out.println(filename1);


System.out.println("Request File Name:"+filename);
String appRoot = request.getRealPath("/");
System.out.println(appRoot);


File file = new File (appRoot+"/upload/"+filename);


System.out.println("DownLoad File:"+ file.getPath());


if(!file.exists()) {


%>


<script type="text/javascript">
alert("요청한 파일을 찾을 수 없습니다..");
history.back();
</script>
<%


  return;


}


BufferedInputStream input = null; 
BufferedOutputStream output = null;


response.reset();


String strClient = request.getHeader("User-Agent");
if (strClient.indexOf("MSIE 5.5") > -1) {  //익스  5.5
response.setContentType("application/x-msdownload"); 
response.setHeader("Accept-Ranges", "bytes");
response.setHeader("Content-Disposition", "filename=" + filename1 + ";"); 
response.setHeader("Content-Length", ""+ file.length());
   }else{           //익스 5.5이상
response.setContentType("application/x-msdownload"); 
response.setHeader("Content-Disposition", "attachment; filename=\""+filename1+"\";");
response.setHeader("Content-Transfer-Encoding", "binary;");
response.setHeader("Content-Length", ""+ file.length());
}


try {
input = new BufferedInputStream( new FileInputStream(file) ); 
byte buffer[] = new byte[1024];
int len = 0;
out.clear();
out.flush();
output = new BufferedOutputStream(response.getOutputStream());
while((len = input.read(buffer)) > 0 ){
output.write(buffer,0,len);
}
} catch(Exception e) {
e.printStackTrace();
System.out.println("File DownLoad Exception:" + e.getMessage());
} finally{
response.flushBuffer();
if(input !=null)try{input.close();}catch(Exception e){}
if(output !=null)try{output.close();}catch(Exception e){}
}
%>
-----------------------------------------------------------
작성자: 시간: 댓글 없음:

2012년 6월 19일 화요일

[oracle] BLOB, CLOB 데이터 오라클에서 직접 INSERT


1. DIRECTORY로서 경로를 지정 (직접 디스크상에 존재하는 파일을 LOB에 넣어줘야 하니까.)
    단, 디렉토리를 만들기 위해서는 권한 필요
         GRANT CREATE ANY DIRECTORY TO user

-------------------------------------------------------------
CREATE OR REPLACE DIRECTORY CLOB_DIR
AS 'c:\';
-------------------------------------------------------------





2. PROCEDURE 생성(CLOB)

-------------------------------------------------------------
CREATE OR REPLACE PROCEDURE PCD_TBL_LOB_INSERT
(V_FILENAME IN VARCHAR2)
IS    V_LOCATOR_CLOB  CLOB;
    
    V_SOURCE_DATA_FILE BFILE := BFILENAME('CLOB_DIR', V_FILENAME);    
    V_DEST_OFFSET    NUMBER := 1;
    V_SRC_OFFSET      NUMBER := 1;
    V_LANG_CONTEXT  NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
    V_WARNING           NUMBER;
    
BEGIN
         INSERT INTO TBL_LOB(NO, TXT_FILE)
         VALUES(SEQ_LOB.NEXTVAL, EMPTY_CLOB() )         RETURNING TXT_FILE INTO V_LOCATOR_CLOB;
         
         DBMS_LOB.OPEN(V_SOURCE_DATA_FILE, 
                                  DBMS_LOB.LOB_READONLY);
                                  
         DBMS_LOB.LOADCLOBFROMFILE(V_LOCATOR_CLOB,
                                                      V_SOURCE_DATA_FILE,
                                                      DBMS_LOB.GETLENGTH(V_SOURCE_DATA_FILE),
                                                      V_DEST_OFFSET,
                                                      V_SRC_OFFSET,
                                                      DBMS_LOB.DEFAULT_CSID,
                                                      V_LANG_CONTEXT,
                                                      V_WARNING);
                                                      
         DBMS_LOB.CLOSE(V_SOURCE_DATA_FILE);
         
         COMMIT;
END;
 -------------------------------------------------------------
복잡해보이지만, 굵은 글씨로 된부분을 자신에게 맞게 고처주면된다.
EXEC PCD_TBL_LOB_INSERT('test.sql')

참고로 물리적 DIRECTORY 에서 지정된 공간에서 파일이 삭제되더라도 DB에는 파일이 남아있다.





3. PROCEDURE 생성(BLOB)

 -------------------------------------------------------------
CREATE OR REPLACE PROCEDURE PCD_TBL_BLOB_INSERT
(V_FILENAME IN VARCHAR2)
IS
    V_LOCATOR_BLOB  BLOB;
    
    V_SOURCE_DATA_FILE BFILE := BFILENAME('BLOB_DIR', V_FILENAME);
    
    V_DEST_OFFSET    NUMBER := 1;
    V_SRC_OFFSET      NUMBER := 1;
    
BEGIN
         INSERT INTO TBL_LOB2(NO, IMG_FILE)
         VALUES(SEQ_LOB.NEXTVAL, EMPTY_BLOB() )
         RETURNING IMG_FILE INTO V_LOCATOR_CLOB;
         
         DBMS_LOB.OPEN(V_SOURCE_DATA_FILE, 
                                  DBMS_LOB.LOB_READONLY);
                                  
         DBMS_LOB.LOADBLOBFROMFILE(V_LOCATOR_BLOB,
                                                      V_SOURCE_DATA_FILE,
                                                      DBMS_LOB.GETLENGTH(V_SOURCE_DATA_FILE),
                                                      V_DEST_OFFSET,
                                                      V_SRC_OFFSET,
                                                      );
                                                      
         DBMS_LOB.CLOSE(V_SOURCE_DATA_FILE);
         
         COMMIT;
         
         DBMS_OUTPUT.PUT_LINE('CLOB 타입 데이터 입력 성공!!!');
         
         EXCEPTION
            WHEN OTHERS THEN 
                DBMS_OUTPUT.PUT_LINE('CLOB 타입 데이터 입력 실패!!!');
END;
 -------------------------------------------------------------
EXEC PCD_TBL_BLOB_INSERT('저장할파일이름', 'dog.jpg');

SELECT *
FROM TBL_LOB2

작성자: 시간: 댓글 없음:

2012년 6월 4일 월요일

[oracle] sysdba로 접속하기

CMD창 발췌!
----------------------------------------------------------------------
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\yj>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 화 6월 5 15:55:23 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect /as sysdba
연결되었습니다.
SQL>
----------------------------------------------------------------------
작성자: 시간: 댓글 없음:

[oracle] Trigger 중지

* alter table 테이블명 disable all triggers;


* alter trigger 트리거명 disable
작성자: 시간: 댓글 없음:
피드 구독하기: 글 (Atom)