웹프로그램 및 데이타베이스 관심 북마크

TRUNC (date)

2009-11-27T10:33:51Z

TRUNC (date)

Syntax

trunc_date::=

Purpose

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, then date is truncated to the nearest day.

See Also:

"ROUND and TRUNC Date Functions" for the permitted format models to use in fmt

Examples

The following example truncates a date:

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')  "New Year"

  FROM DUAL;

New Year

---------

01-JAN-92

ROUND and TRUNC Date Functions

Table 6-4 lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.

Table 6-4 Date Format Models for the ROUND and TRUNC Date Functions

Format Model	Rounding or Truncating Unit
CC SCC	One greater than the first two digits of a four-digit year
SYYYY YYYY YEAR SYEAR YYY YY Y	Year (rounds up on July 1)
IYYY IY IY I	ISO Year
Q	Quarter (rounds up on the sixteenth day of the second month of the quarter)
MONTH MON MM RM	Month (rounds up on the sixteenth day)
WW	Same day of the week as the first day of the year
IW	Same day of the week as the first day of the ISO year
W	Same day of the week as the first day of the month
DDD DD J	Day
DAY DY D	Starting day of the week
HH HH12 HH24	Hour
MI	Minute

The starting day of the week used by the format models DAY, DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY.

See Also:

Oracle9i Database Reference and Oracle9i Database Globalization Support Guide for information on this parameter

-- 전년도의 12월31일
SELECT TRUNC(SYSDATE, 'IY')
FROM DUAL

-- 같은년도의 01월01일
SELECT TRUNC(SYSDATE, 'YY')
FROM DUAL

-- 분기의 첫월01일
SELECT TRUNC(SYSDATE, 'Q')
FROM DUAL

-- YYYY년 MM월 01일
SELECT TRUNC(SYSDATE, 'MM')
FROM DUAL

-- YYYY년 MM월 DD일
SELECT TRUNC(SYSDATE, 'DD')
FROM DUAL

-- SYSDATE가 속하는 주에서 년도의 1월1일의 요일과 같은 일자
SELECT TRUNC(SYSDATE, 'WW')
FROM DUAL

-- SYSDATE가 속하는 주에서 월요일의 일자
SELECT TRUNC(SYSDATE, 'IW')
FROM DUAL

-- SYSDATE가 속하는 주에서 월의 1일의 요일과 같은 일자
SELECT TRUNC(SYSDATE, 'W')
FROM DUAL

-- YYYY년 MM월 DD일 HH:00:00
SELECT TRUNC(SYSDATE, 'HH') HH,
       TRUNC(SYSDATE, 'HH12') HH12,
       TRUNC(SYSDATE, 'HH24') HH24
FROM DUAL

-- YYYY년 MM월 DD일 HH:MI:00
SELECT TRUNC(SYSDATE, 'MI') MI
FROM DUAL

-- 분기산출 Sample 1

SELECT TRUNC(TO_DATE('20080310', 'YYYYMMDD'), 'Q') "1분기",
       TRUNC(TO_DATE('20080425', 'YYYYMMDD'), 'Q') "2분기",
       TRUNC(TO_DATE('20080802', 'YYYYMMDD'), 'Q') "3분기",
       TRUNC(TO_DATE('20081130', 'YYYYMMDD'), 'Q') "4분기"
FROM DUAL

-- 분기산출 Sample 2

SELECT YMD,
       Q_YMD,
       DECODE(MM, '01', '1분기', '04', '2분기', '07', '3분기', '10', '4분기') "분기"
FROM (
        SELECT ADD_MONTHS(TO_DATE('20080125', 'YYYYMMDD'), LEVEL-1) YMD,
               TRUNC(ADD_MONTHS(TO_DATE('20080110', 'YYYYMMDD'), LEVEL-1), 'Q') Q_YMD,
               TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE('20080110', 'YYYYMMDD'), LEVEL-1), 'Q'), 'MM') MM
          FROM DUAL
        CONNECT BY TO_CHAR(ADD_MONTHS(TO_DATE('20080110', 'YYYYMMDD'), LEVEL-1), 'YYYYMMDD') <= '20081231'
       )

출처 : Tong - 병조님의 만개의 SQL 통

tag : TRUNC

오라클 Hash Functions

2009-10-23T19:43:26Z

참조 : http://www.psoug.org/reference/dbms_crypto.html

Hash Functions 은 고정 길이 결과값을 리턴합니다. 전 문자열의 비교를 위해 사용합니다.

위 참조 URL을 보시면 다른 형태의 Hash와 암호화 Algorithms들이 있습니다.

set serveroutput on

DECLARE
l_credit_card_no VARCHAR2(19) := '1234-5678-9012-3456';
l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no);
l_encrypted_raw RAW(2048);
BEGIN
dbms_output.put_line('CC: ' || l_ccn_raw);

l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, 1);
dbms_output.put_line('MD4: ' || l_encrypted_raw);

l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, 2);
dbms_output.put_line('MD5: ' || l_encrypted_raw);

l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, 3);
dbms_output.put_line('SH1: ' || l_encrypted_raw);
END;
/

tag : MD5, Hash, SH1

JFReeChart 한글 깨짐 현상 해결

2009-08-21T18:28:18Z

한글때문에 여러글을 봤는데 chart java소스를 수정해서 재컴파일해서 적용하는가 하면

한글이 들어간 부분의 Font를 전부 변경한다거나 여러 방법을 쓰시고 계시더군요.

제가 해결한 방법을 간단히 소스로 안내해 드립니다. 참고하세요.

전체적인 소스는 저도 참고한 것이고 한글 부분만 제가 추가하였으며 이렇게 처리되기 위해서는

createPieChart 메서드 인자 사용에 주의해야 합니다. boolean legend 이 부분을 true로...

JFreeChart chart = ChartFactory.createPieChart(
                    title, //java.lang.String title
                    dataset, //PieDataset dataset
                    true, //boolean legend
                    true, //boolean tooltips
                    false); //java.util.Locale locale

혹, 텍스트에 대한 한글 처리를 더 해야될 부분이 있는지는 저도 더 경험해 봐야...

그럼... ^^

--------------------------------------------------------------------------

<%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR"%>
<%@ page import="java.io.*" %>
<%@ page import="java.awt.Font" %>

<%@ page import="org.jfree.data.general.DefaultPieDataset"%>
<%@ page import="org.jfree.chart.JFreeChart"%>
<%@ page import="org.jfree.chart.plot.PiePlot"%>
<%@ page import="org.jfree.chart.ChartRenderingInfo"%>
<%@ page import="org.jfree.chart.servlet.ServletUtilities"%>
<%@ page import="org.jfree.chart.entity.StandardEntityCollection"%>
<%@ page import="org.jfree.chart.ChartFactory"%>
<%@ page import="org.jfree.chart.ChartUtilities"%>

http://www.w3.org/TR/html4/loose.dtd">

JChart 연습하기

<%

  DefaultPieDataset ds = new DefaultPieDataset();
  ds.setValue("홍길동", new Double(40.0));
  ds.setValue("홍길숙", new Double(25.0));
  ds.setValue("심원보", new Double(35.0));
  ds.setValue("NPS", new Double(15.0));
  ds.setValue("기타", new Double(15.0));

  JFreeChart chart = ChartFactory.createPieChart("득점분포", ds, true, true, false);
  chart.setBackgroundPaint(java.awt.Color.white);
  chart.setTitle("JChart 연습하기");

  chart.getTitle().setFont(new Font("돋움", Font.BOLD, 20));
  chart.getLegend().setItemFont(new Font("돋움", Font.PLAIN, 10));

  PiePlot plot = (PiePlot) chart.getPlot();
  plot.setLabelFont(new Font("돋움", Font.PLAIN, 12));

ChartRenderingInfo info = new ChartRenderingInfo(new StandardEntityCollection());

       //JPG 파일명을 만들어내기
        String fileName = ServletUtilities.saveChartAsJPEG(chart, 600, 300, info, session);

        System.out.println("파일명 : " +fileName);

       //특정 임시 폴더에 이미지 파일을 만들어냄.
        String dir = application.getRealPath("/upload/"); // 파일경로 지정
        dir = "D:\\" + dir.substring(3) + "/";
        String filepath = dir + fileName;
        System.out.println("File path = "+ filepath);

        FileOutputStream fos = new FileOutputStream(new File(filepath));
        File f = new File(filepath);
        ChartUtilities.writeChartAsPNG(fos, chart, 600, 300);

        String graphURL = request.getContextPath() + "/upload/" + fileName;
        //System.out.println(graphURL);
       %>
       "/>

tag : 한글, JFreeChart

AHP 계층적 분석기법

2009-07-14T12:27:05Z

AHP

Analytic Hierarchy Process의 머리말로써 '계층적 분석 과정/방법'이라고 해석될 수 있습니다. 즉 의사결정의 전 과정을 여러 단계로 나눈 후 이를 단계별로 분석 해결함으로써 최적인 의사결정에 이르는 방법이라고 할 수 있습니다.

AHP의 활용단계
-
가. 브레인스토밍(Brainstorming)
브레인스토밍은 계층구조를 설정하기 위하여 평가의 목표를 명확히 하고 평가에 중요한 요인들을 도출하기 위하여 머리속에 떠오르는 모든 관련된 항목과 대안을 열거하는 과정입니다.

-
나. 계층구조의 설정
계층구조의 설정이란 Brainstorming을 통하여 찾아낸 의사결정에 영향을 미치는 요소들 사이의 종속관계를 찾아내고 군 집화하여 최상의 계층에는 문제의 궁극적인 목표를 나타내고 제1수준에는 최종목표에 영향을 미치는 평가기준을, 그 다음 단계로서 제2수준에는 제1수준에 영향을 미치는 세부 평가 기준을 나타내고, 최하위 수준에는 평가 대안들을 위치시키는 과정입니다. 이러한 과정을 통하여 복잡한 사안들을 계층화, 시각화함으로서 논리적인 판단을 통한 보다 올바른 의사결정을 할 수 있도록 하는 단계입니다.
-
다. 쌍대비교 및 상대적 중요도의 설정
이러한 계층적인 분석을 통하여 얻어낸 각각의 요소 및 대안간의 쌍대비교(1:1 비교)를 통하여 각각의 요소 및 대안들이 상위요소 및 기준에 대하여 얼마나 많은 영향을 미치는지 또는 중요성을 갖는지 등을 찾아내는 과정이며 이러한 과정을 전 계층에 대하여 수행하고, 찾아낸 비교 값을 고유치계산 방법을 이용하여 풀면 최종적으로 각 요소들 간의 중요도 및 대안의 선호도를 찾아낼 수 있습니다.

-
라. 논리적 일관성의 유지
분석적사고의 다음단계는 의사결정자나 설문에 응한 사람의 판단이 얼마나 논리적 일관성 을 유지하는 가를 판단하는 것입니다. 일관성이 결여된 판단은 잘못된 정보나, 개념으로 인한 계층구조의 모순일 경우도 있지만 의사결정자의 판단상의 오류에서 기인하는 것이 대부분입니다. AHP모델에서는 이러한 비일관성의 정도를 측정하는 비일관성 비율을 산출해 줌으로써 결과의 수용여부를 결정할 수 있도록 하며 이러한 수치가 일정수준 이상으로 나타날 때에는 정확한 판단이 얻어 질 때까지 반복 작업을 수행합니다.
-
마. 피드백(Feedback)
아무리 잘 된 모델 및 그를 통하여 도출된 요소별 가중치라고 하더라도 모든 상황에서 타당성을 갖는 것은 아닙니다. 상황의 변화에 따라 융통성 있게 조정 및 개선되어야 합니다. 예를 들어 중동에서의 전쟁의 발발, 세계적 폭설로 인하 유가의 폭등 등 의사결정 당시에는 예상하지 못했던 매우 중요한 상황의 변화가 발생할 수 있는 것이 현실입니다. 따라서 주기적으로 상황변화에 따른 모델의 타당성, 가중치의 적정성 등을 점검하여야 합니다. AHP는 이러한 시장상황의 변화와 이에 따른 새로운 기준의 추가 또는 삭제, 모델의 재설정 등의 Feedback을 쉽게 할 수 있도록 지원하는 융통성 있는 의사결정방법입니다. 이는 의사결정의 시간과 질을 향상시키는 데 크게 공헌을 하게 될 것입니다.

평가의 일관성 파악 및 개선 AHP의 또 다른 특징은 AHP를 활용하여 의사 결정하는 과정에서 의사결정자의 논리적 일관성의 유지 여부를 확인할 수 있도록 지원한다는 점입니다.

AHP에서 이용하고 있는 고유치계산 방법을 수행하는 과정에서 필연적으로 비일관성지수(Inconsistency Index)가 산출되며 이를 통하여 의사결정자의 논리적 일관성 유지 여부와 개선을 위한 기준을 제시합니다. 의사결정 참가자의 의견 통합 및 집단의사결정 도출 AHP 개발의 동기가 되었던 점이 바로 의사결정자의 의견을 합리적, 논리적으로 통합하여 집단의 의사결정을 도출하는 것이었습니다. 통상적인 경우 의사결정참여자의 다수 의견을 취하거나 평균을 내어 집단의 의사결정으로 삼는 것이 대부분일 것입니다. 그러나 이런 방법을 취할 경우에는 소수의 의견이 완전히 무시되거나 의사결정참여자의 정확한 요소별 또는 대안별 선호도 차이의 반영이 불가능하게 됩니다. AHP는 의사결정참여자 모두의 의견을 반영시킬 뿐만 아니라 각자의 요소별 또는 대안별 선호도의 차이를 100% 반영할 수 있게 되어 보다 합리적인 집단의사결정의 결과가 도출되도록 지원합니다.

이 지식은 SERI포럼의 열린지식존 > 지식SOS에 올라온 회원 답변글입니다.

tag : AHP

Oracle Session 죽이기

2009-06-12T18:01:33Z

Unix System은 다중 사용자가 터미널을 바탕으로 동시에 접속하는 개념으로 생성 되었다.. 그러다 보나디 이들이 실해하는 프로그램을 관리하기 위해 Process라는 것이 생겨 났고 이런 Process와 사용자간의 통신을 위해서 Session이라는 것이 생겨났다..

이런 여러 Session중에서 Oracle이 사용하는 Session은 v$session이라는 곳에 저장되어 있으며 이를 위해 해당 Session을 Kill(죽이다) 할 수 있다..

SQL*PLUS상에서 현재 Session 정보로 Kill 하기

select username,sid,serial#,terminal from v$session 을 실행하면 위와 같은 결과를 볼 수 있다.. 이는 현재 Oracle Databae에 접속되어 있는 Session을 확인 할 수 있다. 이때 해당 Session을 Kill 하기 위해서는 alter system kill session ‘SID, SERIAL#’;을 실행 하면 된다.

SQL>alter system kill session '151,8702' immediate;

OS Kill 명령어 이용하기(Unix,Linux)

SELECT nvl(S.OSUSER,S.type) OS_User, S.USERNAME Ora_User, S.sid SID, S.serial# Serial#, P.spid OS_Pid, S.program Program FROM V$SESSION S, V$PROCESS P WHERE s.paddr = p.addr 을 실행하면 위와 같는 결과를 볼 수 있다.

위 결과에서 OS_USER는 해당 세션을 유지하는 Unix OS User이거나 아니면 Oracle Client에서 접속한 컴퓨터의 사용자 이림 일수도 있다. OS_PID는 SID와 대응하는 OS Process ID 이다.

이것을 확인 후 PL/SQL Developer 접속자의 Session을 Kill을 한다고 하면 kill -9 형태로 OS 명령어를 실행 하면 된다.

$kill -9 917628

orakill 명렁어 이용하기(Windows)

Windows에서는 Kill이라는 명렁어 자체가 없다. 그래서 인지 Windows용 Oracle Database는 Unix의 Kill 명령어와 같은 기능을 하는 orakill($ORACLE_HOME/bin)이라는 명령어가 존제 하고 있다.

orakill의 사용법은 orakill 형태의 명령문을 DOS환경에서 실행 하면 된다.

C:\>orakill 154 917628

Kill oracle session

2009-06-12T17:46:34Z

ALTER SYSTEM KILL Session Marked for Killed Forever

PURPOSE
This document briefly describes how to suppress sessions marked killed in v$session.
이 문서는 v$session에 killed로 표시된 세션을 정리<억압이라고 해석하긴 싫다-_-;> 하는 법에 대해 간단히 설명한다.

SCOPE & APPLICATION
Useful for DBAs. : DBA에게 유용합니다. 아~예~예~ ㅋㅋ

ALTER SYSTEM KILL Session Marked for Killed Forever:

====================================================

You have a session that you have killed, but it seems as though it will not go away:
당신은 한 세션을 kill 시켰으나 그 세션은 여전히 정리되지 않은 채로 보인다.

alter system kill session 명령에 sid, serial을 파라미터로 주고, 실행시켜 보자.
SQL> alter system kill session 'sid, serial#';

해당 세션의 상태를 조회한다.
SQL> select status, username from v$session;

status killed
username username

You have issued this several times and it seems it still is marked as killed, but has a lock on the existing table.

당신은 이 명령을 여러 번이나 수행했으나 (여러 번 kill 시키려 했으나) 해당 세셔은 여전히 killed라고 표시되어 있으나 해당 테이블은 lock 을 잡고 있다.

In order to determine which process to kill:
어떠한 프로세스를 kill 시킬지 결정하기 위해 하위의 Query를 실행시킨다.

a) On a Unix platform: < Unix 상에서 > 이 query를 통해 spid를 확인한 뒤, unix상에서 kill 명령어로 프로세스를 kill 시키면 된다.

SQL> SELECT spid
                 FROM v$process
                 WHERE NOT EXISTS ( SELECT 1
                                    FROM v$session
                                    WHERE paddr = addr);

% kill

b) On a Windows platform: < Windows 상에서 > Unix와는 달리 Windows에서는 Orakill을 사용한다.

SQL> SELECT spid, osuser, s.program
FROM v$process p, v$session s
WHERE p.addr=s.paddr;

Then use the orakill utility at the DOS prompt:

c:\> orakill

where = the Oracle instance name (ORACLE_SID)
= the thread id of the thread to kill

Explanation:
============

PMON has not yet cleaned up the process and this may not happen for several hours.
PMON은 해당 프로세스를 아직 정리하지 않았고 그것은 몇 시간에 걸쳐 정리되지 않을 수도 있다.

By finding the spid you can then force the process to be killed instead of
waiting for PMON to wake up and kill it.
spid를 알아냄으로써 당신은 PMON이 그 세션을 wake up 한 후 kill 시키는 것을 기다리는 대신, 해당 프로세스를 강제로 종료할 수 있다.

When issuing the 'kill' command be sure that you kill "DEDICATED SERVER PROCESSES", those called:
kill 명령어를 수행할 때는 아래와 같이 불리는 "DEDICATED SERVER PROCESSES" 를 삭제하는지 확인해야 한다.

oracle (local=NO)

where is the ORACLE_SID.

Be sure you do not kill processes such as:
아래와 같이 시작하는 프로세스들을 삭제하는 건 아닌지 확인해라.
< 잘못하면 DB Crash ;;; >

    ora_d000_
    ora_s000_
    ora_pmon_

참조 : http://blog.naver.com/makin?Redirect=Log&logNo=41778981

JAVA Eclipse 자바 이클립스 자료 모음

2009-05-26T13:43:02Z

참조 : http://withhun.tistory.com/37?srchid=BR1http%3A%2F%2Fwithhun.tistory.com%2F37

JAVA Eclipse 자바 이클립스 자료 모음

JDK 5.0 한글 API : http://xrath.com/devdoc/jdk1.5/ko/api/index.html
JDK 5.0 한글 도움말 : http://xrath.com/devdoc/jdk1.5/ko/
JDK 1.4 한글 API(forcert) : http://www.forcert.com/programming/api/j2se/1.4.0k/index.html
JDK 1.4 한글 도움말(javastudy) : http://www.javastudy.co.kr/api/api1.4/

J2EE 한글 도움말 : http://xrath.com/devdoc/j2ee-1.3.1/ko/api/index.html
JavaServlet API v2.3 : http://jakarta.apache.org/tomcat/tomcat-4.1-doc/servletapi/index.html
JavaServlet API v2.4 : http://jakarta.apache.org/tomcat/tomcat-5.0-doc/servletapi/index.html
JSP API 2.0 : http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jspapi/index.html

자바 싸이트

자바스터디 : http://www.javastudy.co.kr/ (강좌 정리)
자바누리 : http://www.javanuri.com/
자바랜드 : http://www.javaland.co.kr/
소설같은 자바 : http://www.jabook.org/ (책 한권)
자바서비스넷 : http://www.javaservice.net/ (중고급 정보)
김상욱의 개발자side : http://50001.com/ (소스 등)
종스클럽닷컴: http://www.jongsclub.com/
JLAB : http://www.jlab.net/
OKJSP : http://www.okjsp.pe.kr/
제이스톰 : www.jstorm.pe.kr/
자바 튜토리알(영어) : http://java.sun.com/docs/books/tutorial/index.html
스윙 컴포넌트 예제(영어) : http://java.sun.com/docs/books/tutorial/uiswing/components/components.html
MS SQL2000 JDBC 드라이버 다운로드 : http://www.microsoft.com/korea/download/server.asp
자카르타 프로젝트 한글화 : http://jakarta.apache-korea.org/
자바지기 : http://wiki.javajigi.net/
자바지기 eclipse 강좌 : http://wiki.javajigi.net/display/IDE/Eclipse
자바지기 struts 강좌 : http://wiki.javajigi.net/display/FRAMEWORK/Home
이클립시안 : http://eclipsians.net/

JSP 싸이트

JSP 스쿨 : http://www.jspschool.com/ (정리 잘됨, 자료실 Good)
OKJSP : http://www.okjsp.pe.kr/ (종류별로 정리잘된 싸이트)
JSP스터디 : http://www.jspstudy.co.kr/ (오른쪽 메뉴중 lecture list 강좌보면 좋다)
AboutJSP : http://aboutjsp.com/ (강좌, 팁, QnA 등)
자북 (소설같은 JSP) : http://www.jabook.org/ (책 소설같은 JSP의 1부, 2부 있음)
까오기 : http://www.kkaok.pe.kr/ (여러 유영한 팁들)
더블 아이큐 : http://www.doubleiq.pe.kr/ (정리된 강좌와 QnA 등 )

기타 관련 다운로드 싸이트(JDK, MySQL, 톰캣 등)

---------------------------------------------------------------------
JDK 1.4.x 자바 Development Kit http://java.sun.com
---------------------------------------------------------------------
MySQL MySQL 데이터베이스 http://mysql.com
---------------------------------------------------------------------
MySQL 직접 다운로드 아래 링크 참조
3.23.58 : http://downloads.mysql.com/archives/mysql-3.23/mysql-3.23.58-win.zip
MySQL Connector/J (JDBC Driver for MySQL) http://dev.mysql.com/downloads/connector/
직접 다운로드 DBC 드라이버 ( 3.1.10 ) : http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-3.1.10.zip/from/http://mysql.holywar.net/
MySQLCC : http://mysql.mirror.anlx.net/Downloads/MySQLCC/
MySQL-Front : http://www.mysqlfront.de/download.html
---------------------------------------------------------------------
톰캣 4.1.31 웹 애플리케이션 서버 http://jakarta.apache.org/tomcat
---------------------------------------------------------------------
직접 다운로드 : http://ftp.apache-kr.org/tomcat/tomcat-4/v4.1.31/bin/jakarta-tomcat-4.1.31.exe
JDK 1.4에 최적화된 버젼 : http://ftp.apache-kr.org/tomcat/tomcat-4/v4.1.31/bin/jakarta-tomcat-4.1.31-LE-jdk14.exe
---------------------------------------------------------------------

이클립스 eclipse 3.0.2 다운로드 정리

한국의 미러 싸이트 : http://eclipse.areum.biz/downloads/
이클립스 3.0.2 : http://eclipse.areum.biz/downloads/drops/R-3.0.2-200503110845/eclipse-SDK-3.0.2-win32.zip
언어팩 : http://eclipse.areum.biz/downloads/drops/L-3.0.1_Translations-200409161125/NLpack-eclipse-SDK-3.0.x-win32.zip
톰캣 플러그인 : http://www.sysdeo.com/sysdeo/content/download/350/4721/file/tomcatPluginV31beta.zip
롬보즈 : http://download.fr2.forge.objectweb.org/lomboz/org.objectweb.lomboz_3.0.1.N20050106.zip
롬보즈 실행을 위한 도구 : http://download.fr2.forge.objectweb.org/lomboz/emf-sdo-runtime-2.0.0.zip (롬보즈를 쓰려면 같이 다운받아서 압축 풀어 쓴다.)

이클립스 eclipse 3.1.1 다운로드 정리

한국의 미러 싸이트 : http://eclipse.areum.biz/downloads/
이클립스 3.1.1 : http://eclipse.areum.biz/downloads/drops/R-3.1.1-200509290840/download.php?dropFile=eclipse-SDK-3.1.1-win32.zip
언어팩 : ftp://ftp.jaist.ac.jp/pub/eclipse/eclipse/downloads/drops/L-3.1.1_Language_Packs-200510051300/NLpack1-eclipse-SDK-3.1.1a-win32.zip
톰캣 플러그인 : http://www.sysdeo.com/eclipse/tomcatPlugin.html
직접 다운 : http://www.sysdeo.com/sysdeo/content/download/350/4721/file/tomcatPluginV31beta.zip
롬보즈 플러그인 : http://forge.objectweb.org/projects/lomboz
http://download.fr2.forge.objectweb.org/lomboz/
lomboz-emf-gef-jem-3.1RC2 : http://download.fr2.forge.objectweb.org/lomboz/lomboz-emf-gef-jem-3.1RC2.zip
WTP all : http://ftp.jaist.ac.jp/pub/eclipse/webtools/downloads/drops/R-0.7-200507290654/wtp-all-in-one-0.7-win32.zip

기타 플러그인 정리

롬보즈3.1RC2 : http://download.fr2.forge.objectweb.org/lomboz/lomboz-3.1RC2.zip
lomboz/lomboz-eclipse-emf-gef-jem-3.1RC2 : http://download.fr2.forge.objectweb.org/lomboz/lomboz-eclipse-emf-gef-jem-3.1RC2.zip
EMF : ftp://ftp.jaist.ac.jp/pub/eclipse/tools/emf/downloads/drops/2.1.1/R200509281310/emf-sdo-xsd-SDK-2.1.1.zip
GEF : ftp://ftp.jaist.ac.jp/pub/eclipse/tools/gef/downloads/drops/R-3.1.1-200509301327/GEF-ALL-3.1.1.zip
VE : ftp://ftp.jaist.ac.jp/pub/eclipse/tools/ve/downloads/drops/R-1.1.0.1-200509071822/VE-SDK-1.1.0.1.zip
UML2 : ftp://ftp.jaist.ac.jp/pub/eclipse/tools/uml2/downloads/drops/1.1.1/R200509282050/uml2-SDK-1.1.1.zip
JEM : ftp://ftp.jaist.ac.jp/pub/eclipse/tools/ve/downloads/drops/R-1.1-200507221721/JEM-SDK-1.1.zip
WTP : ftp://ftp.jaist.ac.jp/pub/eclipse/webtools/downloads/drops/R-0.7-200507290654/wtp-all-in-one-0.7-win32.zip

eclipseUML : http://www.eclipsedownload.com/eclipseUML_freeEdition_2.1.0/eclipseUML_E310_freeEdition_2.1.0.20050927.jar

DBEdit : http://sourceforge.net/projects/dbedit/
SQLExplorer : http://sourceforge.net/projects/eclipsesql/

자바스크립트 에디터 : http://sourceforge.net/projects/jseditor
CSS 에디터 : http://www.vasanthdharmaraj.com/Software.aspx?=csseditor

(J2EE 개발 및 JSP 개발시 유용한 플러그인)

1. 다운로드 주소
롬보즈 다운로드 링크 : http://forge.objectweb.org/projects/lomboz

2. 직접 다운
미국 : http://download.us.forge.objectweb.org/lomboz/lomboz.213.zip
프랑스 : http://download.fr2.forge.objectweb.org/lomboz/lomboz.213.zip
중국 : http://download.zh.forge.objectweb.org/lomboz/lomboz.213.zip

c:\eclipse라는 폴더에 압축을 해제.

(톰캣 개발시 유용한 플러그인)

1. 다운로드 주소
톰캣 플러그인 : http://www.eclipsetotale.com/tomcatPlugin.html
tomcatPluginV3.zip 다운 받아서 압축 해제해서
C:\eclipse\plugins 에 복사

2.톰캣 글자깨짐방지: server.xml 에서 URIEncodiog 추가.

(스트럿츠 개발시 유용한 플러그인)

EasyStruts : eclipse 에서 스트럿츠 개발시 사용하는 플러그인.
1. 다운로드 주소 : http://sourceforge.net/projects/easystruts
2. 직접 다운로드 : http://prdownloads.sourceforge.net/easystruts/org.easystruts.eclipse_0.6.4.zip?download
3. SVN설정 : http://wiki.javajigi.net/pages/viewpage.action?pageId=175&showComments=false

UNIX에서 .sql 실행하기

2009-05-07T20:40:09Z

#test.sql

-- test.sql 내용

----------------------------------------------------------------

sqlplus -s user/password <<-!

set feed off

set timing off

set pagesize 0

set linesize 100

INSERT INTO demo.DEMO_TABLE@DEMO

(SEQ, TITLE, CONTENT, DATE)

SELECT * FROM SOURCE_TABLE WHERE ERR_CODE is null;

COMMIT;

----------------------------------------------------------------

암호화 MD5 사용하기 (JSP/ASP)

2009-04-29T16:02:48Z

/******************************** JSP */

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

/**
* 사용자 비밀번호를 MD5 방식으로 암호화
*/
public class DataEncrypt {
MessageDigest md;
String strSRCData = "";
String strENCData = "";

public DataEncrypt() {}

public String encrypt(String strData) { // 암호화 시킬 데이터
try {
MessageDigest md = MessageDigest.getInstance("MD5"); // "MD5 형식으로 암호화"

   byte[] bytData = strData.getBytes();
   md.update(bytData);
   byte[] digest = md.digest(); //배열로 저장을 한다.
   for (int i = 0; i < digest.length; i++) {
    strENCData = strENCData + Integer.toHexString(digest[i] & 0xFF).toUpperCase();
   }
  } catch (NoSuchAlgorithmException e) {
   System.out.print("암호화 에러" + e.toString());
  }
  return strENCData; // 암호화된 데이터를 리턴...
}
} // end class

참고사이트 :

http://blog.naver.com/jhkim5481?Redirect=Log&logNo=60064334851

http://blog.naver.com/kojaedoo?Redirect=Log&logNo=100045343583

http://blog.naver.com/wishkjh?Redirect=Log&logNo=80059131302

자바스크립트에서 암호화시키기

http://pajhome.org.uk/crypt/md5/md5src.html

[출처] 자바 비밀번호를 암호화 해보자~(MD5)|작성자 Spade

----------------------------------------------------------------------------------------------

/******************************** ASP */

첨부 파일을 다운로드 후 인클루드 시킨다. 사용은 MD5('AAA')

참조 : http://blog.naver.com/wishkjh?Redirect=Log&logNo=80059131302

md5-wishkjh.asp

md5-wishkjh.js

----------------------------------------------------------------------------------------------

MD5 크랙

참조 : http://cafe.naver.com/dietdoc/13643

tag : 암호화, MD5

JDBC에서 Connection, Statement,ResultSet의 close

2009-04-21T14:38:25Z

오래전에 봤던 글인데 항상 다시 봐도 도움이 됩니다. 가장 기초이면서도 지키기 어려운 부분들이죠.

검색하다 잘 정리해 둔 곳이 있어 실어봅니다.

참조 : http://benelog.egloos.com/1898928

이미 많이 알려진 내용이지만, 아직도 문제를 많이 일으키는 주제입니다. 그래서 보다 이 주제를 검색엔진에서 쉽게 찾을 수 있었으면 하는 마음에서 이 글을 정리해봤습니다.

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null; // <---- !!!
try{
     conn = ......;
     pstmt = conn.prepareStatement("select .....");
     rs = pstmt.executeQuery(); // <----- !!!
     while(rs.next()){
       ......
     }
} catch(Exception e){
     ....
} finally {
     if ( rs != null ) try{rs.close();}catch(Exception e){}
     if ( pstmt != null ) try{pstmt.close();}catch(Exception e){}
     if ( conn != null ) try{conn.close();}catch(Exception e){}

}

이것이 JDBC API 사용시에 권장되는 코딩방식입니다. 코드는 참조자료에 있는 이원영님의 글에서 인용했습니다.

JDBC 스펙을 찾아보면 Statement가 닫힐 때 ResultSet은 닫히고, Connection이 닫히면 Statement도 닫힌다고 되어 있습니다. 하지만 Staement close 시에 Exception이 발생한다면 이것이 따로 Exception을 catch되지 않고서는 뒤에 Connection을 닫는 코드가 실행되지 않습니다. 그리고 Connection pool에서 얻어온 Connection객체는 connection.close()로 처리하는 것이 pool로의 반환을 의미하는 것이지 실제로 connetion을 close하는 것이 아니기 때문에 Statement까지 닫아준다고 장담할 수 없습니다. ResultSet의 경우도 WAS에도 제공하는 Statement cache 기능 때문에 명시적으로 close해주는 것이 확실한 자원해제를 보장할 수 있습니다.

DBMS에서 "maximum open cursor exceed !" 나 "Limit on number of statements exceeded " 에러를 내고 있다면 위와 같이 코딩했는지 한번 확인해보시기 바랍니다.

각 벤더별 드라이버의 구현이나 WAS의 Connection Pool의 구현등에 따라서 저 정도까지 안 해도 문제가 안 생길 수도 있습니다. 그리고 독립적으로 돌아가는 배치프로그램이나 커넥션풀을 쓰지 않는 경우에는 보다 덜 엄격해도 될 때도 있습니다. 그래도 어떠한 경우에도 안심하고 있을만한 코드는 위와 같은 구조입니다.

javaservice.net에서 이원영님이 처음에 이 문제에 대한 글을 쓰신것이 2000년 9월입니다. 그래서 많은 분들이 알고 계시지만 그래도 정말 반복적으로 만나게 되는 문제입니다. 저의 경험이 편향된지도 모르겠지만, 지금까지 제가 만났던 JDBC AP를 그대로 쓰는 개발팀은 세 팀이였었는데, 모두 이렇게 코딩하지 않을 경우 문제가 생길 가능성이 있다는 것을 모르고 있었습니다. 결국 그 중 한 팀은 시스템 전체를 몇 일동안 매시간마다 재부팅시키게 만들게 했었습니다.

미국의 모 대형항공사의 예약시스템을 3시간동안 멈춘 코드도 위와 같은 방식을 따르지 않았었습니다. finally절이 다음과 같았다고 합니다.

}finally{

if (stmt!=null) stmt.close();

if (conn!=null) conn.close();

}

그 예약 시스템은 이중화된 DB로 구성되어 있었고, 그 DB들은 가상IP주소로 어플리케이션과 연결되어 있었습니다. 정기 점검을 위해 DB중 하나를 수동 fail-over 시키는 순간 내려간 DB의 JDBC연결에서 나온 statement객체의 close문장은 Exception을 일으켰습니다. 이 문장은 별도로 catch 되지 않았기 때문에 그 다음의 conn.close()는 실행되지 않았습니다. 결국 이 때문에 반환되지 않은 Connection 자원들로 인해 리소스 풀은 곧 바닥이 났습니다. 그 후에 새로 Connection을 얻고자 하는 다른 프로그램들은 블록되어서 전체 시스템을 멈추었습니다.

아마도 JDBC API를 쓰는 곳에는 언제나 생길 수 있는 문제일 것입니다. 좋은 API는 문서를 안 보고 자연스럽게 써도 사용하기 쉽고 문제를 안 일으키는 것일텐데, JDBC는 제대로 사용하기가 오히려 더 어려운 API입니다. 위의 항공사 사건 같이 전 세계에서 JDBC로 인해 야기된 장애,생산성 저하를 다 따져본다면, 가히 이 API가 인류에게 끼친 해악이 엄청나다는 생각까지도 듭니다. 요즘은 Framework 기반 개발로 JDBC를 직접 안 쓰는 것이 이런 점에서는 다행입니다.

JDBC API에서 대표적으로 지적받는 문제점은 Checked Exception을 남발했다는 것입니다. catch 절에서 아무 것도 하지 않는 것은 바람직하지 않은 코딩이지만 JDBC API에서는 정말 할 것이 없습니다. 그래서 이런 문제점을 알고서 그 후에 나온 JDBC를 활용한 API들, Spring의 JdbcTemplet, Hibernate의 Query 인터페이스, JPA의 Query 인터페이스, JDO의 Query 인터페이스에서는 Checked Exception인 SqlException을 볼 수 없게 설계되어 있습니다.

그리고 JDBC에서는 접속에러, 쿼리에러, 제약조건 에러 등 다양한 원인으로 생기는 Exception을 SqlException 1개로 다 때우는 문제도 있습니다. Spring에서는 이것을 더 섬세하게 구분한 Exception들을 정의를 하고 있습니다. DataAccessException의 하위 클래스를 보면 CleanupFailureDataAccessException, DataIntegrityViolationException, DataRetrievalFailureException 등이 보입니다.

Java의 Checked Exception 처리 문제에 관한 글은 따로 정리했습니다.

미국 항공사 장애 사건 관련 참고자료

Release It 릴리스 잇 : 성공적인 출시를 위한 소프트웨어 설계와 배치 (마이클 나이가드 저/신승환,정태중 역, 위키북스)

2장 사례연구 : 항공사를 정지시킨 예외(Exception) 사건

JDBC 관련 참고자료

Javaservice.net에 이원영님이 올린 글

같은 내용으로 zdnet에 올라온 글

tag : JDBC

대용량 데이터 INSERT시 참조할 URL

2009-04-20T14:17:03Z

관련 정보의 링크만 간단히 소개합니다.

- INSERT /*+ APPEND */ VS CTAS

http://tong.nate.com/sonamu92/37311915

- 오라클 - Oracle Batch Update model - batch speedup (JAVA)

http://blog.daum.net/rqzisfyq/3962013?srchid=BR1http%3A%2F%2Fblog.daum.net%2Frqzisfyq%2F3962013

- 대용량 insert/update/delete || 배치작업 (JAVA)

http://blog.naver.com/ssyang21?Redirect=Log&logNo=40007170222

- 대용량 데이터에 유용한 저장 구조

http://tistory.izyou.net/2460914?srchid=BR1http%3A%2F%2Ftistory.izyou.net%2F2460914

EmEditor - 추천 에디터

2009-04-02T09:40:05Z

- 다운로드 : http://www.emeditor.com/download.htm

- 한글 패치 : 휄마의 잡다한 지식들 (http://jgh0721.egloos.com/)

- 관련 정보 : 클릭

- 간단 설명 : 편리한 소스보기, 칼럼편집모드...

tag : EmEditor

오라클 CLOB 타입에 DATA INSERT - PL/SQL DBMS_LOB 패키지 사용

2008-12-15T23:56:46Z

아래 내용은 완전 정리본은 아니지만 . 실행에는 이상없습니다.

혹시, 문제되는 부분있으면 지적해 주세요. 그럼.. ^^

DECLARE
    v_clob_selected CLOB;
    v_write_amt     NUMBER;
    v_write_offset INTEGER := 1;
    v_buf           LONG;
    -- CLOB 컬럼에 한번에 입력시킬 크기(오라클 Buffer 설정과 연관된 것 같음. Buffer 사이즈에 따라 크기 조정)
    v_buf_value     INTEGER := 20000;
    v_clob_length   NUMBER;
    loop_index      INTEGER;
    v_CLOB_CTNT CLOB;

    BEGIN
        v_CLOB_CTNT := '///////////AAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAD///////////////8AAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAA///CAAAAAkAAAAKAAAADAAAAP7///8NAAAADgAAAP7/
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
/////////////////////////NkBAAAAAAAAUAByAHYASQBtAGEA
ZwBlAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABIA
AgH/////BwAAAP////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAAA
qAQAAAAAAABQAHIAdgBUAGUAeAB0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAA////wAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAACQAAAAOAAAAAAAAAEQAZQBmAGEAdQBsAHQASgBTAGMA
cgBpAHAAdAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAeAAIA////////
////////AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIwAAABEAAAAAAAAA
XwB
////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////xjCCa6MrVXW
eMcgAA+8IAAEyLDAhccluCAAmMysuVjHIAAByBXIMcENAAoAAAAAAAAAAAAAAAAAAAAAAAAA
AABHSUY4OWGxAPoA9wAAAAAAgAAAAIAAgIAAAACAgACAAICAwMDA/v7+8fHx5OTk1tbWycnJ
eMcgAA+8IAAEyLDAhccluCAAmMysuVjHIAAByBXIMcENAAoAAAAAAAAAAAAAAAAAAAAAAAAA
AABHSUY4OWGxAPoA9wAAAAAAgAAAAIAAgIAAAACAgACAAICAwMDA/v7+8fHx5OTk1tbWycnJ
q1atYs2rdyrWr169gw4odS7as
SQBmWQJYi5atW7QD4a4V2FYu3bp04+Kdm5biW7b/AMO9G1hu28KEAyNObLev48eQI0ueTLmy
5cuYM2vezADEAMgB8xyAACK6UxnzH
IAAkxgTIIAAxADAAOgAzADIAOW1lbnQgRmlsZQAAAAAA
q1atYs2rdyrWr169gw4odS7as
SQBmWQJYi5atW7QD4a4V2FYu3bp04+Kdm5biW7b/AMO9G1hu28KEAyNObLev48eQI0ueTLmy
5cuYM2vezADEAMgB8xyAACK6UxnzH
IAAkxgTIIAAxADAAOgAzADIAOW1lbnQgRmlsZQAAAAAA
AAAAAAAAAAAAAAcBAAUhAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAUwBlAGMAdABpAG8A
bgAwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABIA
AgH///////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAuAAAA
3AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////wAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAA////////////////
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAD///////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABjYGBAAf+BAABu
u27RFAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Y2RgAAMA99+IqQgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAcwBlAHIAXABMAE8AQwBBAEwAUwB+ADEAXABUAGUAbQBwAFwAcAByAHYA
MAAwADAAMAAwzYEB4KbQWUfFEhNHxoKiwKAAAAAAiQJgsAAC8M
KAAAAABQKwsAEAAAGDuOCwwAjgsEAAAAABCOC9wAAACpDQAA1ldVAAAAFADFipR81tuJWbUL
AAAQJwAACwAAAAdGAQAFAAAAwBwSAAdGAgCoHRIAGO6TfHAFlHz/////bQWUfGIZlHyTGZR8
gMCafHAZlHxo3jUHSN41B5RfjXoBAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
3AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////wAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAA////////////////
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAD///////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
3AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////wAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAA////////////////
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAD///////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
3AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////wAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAA////////////////
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAD///////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
3AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////wAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAA////////////////
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAD///////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';

        -- 새로운 ROW일 경우 offset 초기화
        v_write_offset := 1;

        -- CLOB 초기화
        UPDATE TABLECLOB SET CLOB_CTNT = EMPTY_CLOB()
        WHERE SN = 3;

        SELECT CLOB_CTNT INTO v_clob_selected FROM TABLECLOB
        WHERE SN = 3
        FOR UPDATE;

        -- 컬럼 DATA가 존재할 경우만
        IF v_CLOB_CTNT is not null THEN
            v_clob_length := DBMS_LOB.GETLENGTH (v_CLOB_CTNT);

            -- CLOB 컬럼에 한번에 입력시킬 수 있는 크기보다 큰 경우 LOOP로 처리
            IF v_clob_length > v_buf_value THEN
                -- DATA 전체 크기를 v_buf_value 크기로 나눈 값만큼 LOOP
                FOR loop_index IN 1 .. TRUNC(v_clob_length / v_buf_value) - 1
                LOOP
                    -- 처음엔 DBMS_LOB.WRITE로 처리
                    IF loop_index = 1 THEN
                      DBMS_OUTPUT.PUT_LINE('4. loop_index --------' || cast(v_buf_value as varchar2));
                      v_buf := DBMS_LOB.SUBSTR(v_CLOB_CTNT, v_buf_value, 1);
                      --v_write_amt := length(v_buf);
                      v_write_amt := DBMS_LOB.GETLENGTH (v_buf);
                      DBMS_LOB.WRITE(v_clob_selected, v_write_amt, v_write_offset, v_buf);
                    -- 두번째부턴 DBMS_LOB.WRITEAPPEND로 처리
                    ELSE
                       DBMS_OUTPUT.PUT_LINE('4. loop_index --------' || cast((v_buf_value * loop_index) + 1 as varchar2));
                       v_buf := DBMS_LOB.SUBSTR(v_CLOB_CTNT, v_buf_value, (v_buf_value * loop_index) + 1);
                       v_write_amt := DBMS_LOB.GETLENGTH (v_buf);
                      DBMS_LOB.WRITEAPPEND(v_clob_selected, v_write_amt, v_buf);
                    END IF;
                END LOOP;


                DBMS_OUTPUT.PUT_LINE('5. MOD --------' || MOD(v_clob_length, v_buf_value));
                DBMS_OUTPUT.PUT_LINE('6. MOD loop_index --------' || cast((v_clob_length - MOD(v_clob_length, v_buf_value)) + 1 as varchar2));

                -- 나머지 DATA 입력
                IF MOD(v_clob_length, v_buf_value) <> 0 THEN
                    v_buf := DBMS_LOB.SUBSTR(v_CLOB_CTNT, MOD(v_clob_length, v_buf_value), (v_clob_length - MOD(v_clob_length, v_buf_value)) + 1);
                    --v_write_amt := length(v_buf);
                    v_write_amt := DBMS_LOB.GETLENGTH (v_buf);
                    DBMS_LOB.WRITEAPPEND(v_clob_selected, v_write_amt, v_buf);
                END IF;
            -- CLOB 컬럼에 한번에 입력시킬 수 있는 크기보다 작은 경우의 처리
            ELSE
                v_buf := v_CLOB_CTNT;
                --v_write_amt := length(v_buf);
                v_write_amt := DBMS_LOB.GETLENGTH (v_buf);
                DBMS_LOB.WRITE(v_clob_selected, v_write_amt, v_write_offset, v_buf);
            END IF;
        END IF;

        COMMIT;
    END;

tag : DBMS_LOB, CLOB

날짜 함수

2008-08-12T19:43:49Z

# 날짜계산
select months_between(sysdate,to_date('2006-12-22','yyyy-mm-dd')) from dual;

-- '2006-12-22' 과 당일 사이의 달의 수를 NUMBER형 타입으로 반환
-- months_between(A,B) = A-B/30
select add_months(sysdate,4) from dual;

-- 특정일의 달수 더한 날
select next_day(sysdate,'friday') from dual;

-- 특정일의 다음주 요일
select last_day(sysdate) from dual;

-- 특정일의 해당 월의 마지막 날
select round(sysdate,'dd') from dual;

-- 특정일의 반올림(오후면 다음날..)
select trunc(sysdate,'ww') from dual;

-- 특정일의 전주 토요일(해당 전주의 마지막 날)에해당하는 날짜
select trunc(sysdate,'D') from dual;

-- 특정일의 주 일요일(해당 주의 첫째 날)에해당하는 날짜

where 조건에 날짜칼럼을 기준으로 함.
/* 어제 */ 날짜칼럼

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('20060101', 'YYYYMMDD'))/12) "년",
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20060101', 'YYYYMMDD')) -
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20060101', 'YYYYMMDD'))/12) * 12) "개월",
TRUNC((MONTHS_BETWEEN(SYSDATE,TO_DATE('20060101', 'YYYYMMDD')) -
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20060101', '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('20060502223443','YYYYMMDDHH24MISS')-TO_DATE('20060501213344','YYYYMMDDHH24MISS')) || ' day ' ||
   TRUNC(MOD((TO_DATE('20060502223443','YYYYMMDDHH24MISS')-TO_DATE('20060501213344','YYYYMMDDHH24MISS')),1)*24) || ' hour ' ||
   TRUNC(MOD((TO_DATE('20060502223443','YYYYMMDDHH24MISS')-TO_DATE('20060501213344','YYYYMMDDHH24MISS'))*24,1)*60) || ' minute ' ||
   TRUNC(ROUND(MOD((TO_DATE('20060502223443','YYYYMMDDHH24MISS')-TO_DATE('20060501213344','YYYYMMDDHH24MISS'))*24*60,1)*60)) || ' sec '
   " Time Interval "
FROM DUAL ;

[쿼리] 월별 매상을 4분기별로 집계하는 함수 비결

2008-08-12T15:21:30Z

본 자료는 일본 @IT(http://www.atmarkit.co.jp/fdb/index/index-db.html#sqlclinic)에 株式会社インサイトテクノロジー의 大道隆久씨가 연재한 SQLクリニック를 번역 재구성한 것입니다.

■ 월별 통계치 구하기

이번 연제는 집계시 사용하면 편리한 함수는

AVG ― 평균치 구하기
MAX ― 최대치 구하기
MIN ― 최소치 구하기
SUM ― 합계치 구하기

등이 있습니다.

예를 들면 리스트 1의 샘플 매상표로부터 월별의 매상 집계를 구해 봅시다.


SQL> desc sales
名前                                      型
----------------------------------------- -------------------
PRODUCT_CODE                              NUMBER
MONTH                                     NUMBER
SAL                                       NUMBER

리스트 1　샘플 매상표


SQL> select month,sum(sal) from sales group by month order by 1;

     MONTH   SUM(SAL)
---------- ----------
         1      50000
         2     107200
         3     160800
         4      80400
         5     100500
         6     120600
         7      93800
         8     107200
         9     120600
        10     134000
        11     147400
        12     160800

리스트 2　월별의 집계치를 구한다.

간단하네요.그럼 4분기별의 매상 집계를 구하려면 , 어떻게 하면 좋을 것입니다.

■ 4분기별의 집계치를 구한다(UNION ALL편)

먼저 제일 간단한 것은“UNION ALL”로 연결해 버리는 방법이 아닐까요.

UNION ALL는 2개 이상의 쿼리의 결과를 연결하는 집합 연산자입니다.


SQL> select '1Q' quarter,sum(sal) from sales
      where month between 1 and 3
     union all
     select '2Q' quarter,sum(sal) from sales
      where month between 4 and 6
     union all
     select '3Q' quarter,sum(sal) from sales
      where month between 7 and 9
     union all
     select '4Q' quarter,sum(sal) from sales
      where month between 10 and 12;

QUARTER   SUM(SAL)
------- ----------
1Q          318000
2Q          301500
3Q          321600
4Q          442200

리스트 3　UNION ALL로 4분기별의 집계치를 구한다

다만 이 경우에는 동일한 SALES테이블에 4회 액세스 하므로 효율이 나쁜 SQL이네요.. 퍼포먼스상 문제가 될지도 모릅니다.

■ 4분기별의 집계치를 구하기 (DECODE편)

“DECODE”는 인수와 판정치를 비교해 동일한 경우에 결과치를 돌려주는 함수입니다.

構文	select decode(인수,판정치1,결과치1,판정치2,결과치2,..) from


SQL> select decode(deptno,10,'ACCOUNTING',
                          20,'RESEARCH',
                          30,'SALES',
                          40,'OPERATIONS') 部署名 from emp;

部署名
----------
RESEARCH
SALES
SALES
RESEARCH
SALES
SALES
ACCOUNTING
ACCOUNTING
SALES
SALES
RESEARCH
ACCOUNTING

리스트 4　DECODE 함수의 간단한 사용예

친숙한 EMP 테이블에서 DEPTNO를 인수로서 부서명을 돌려주고 있습니다.

그럼 리스트 1의 SALES테이블에서 DECODE 함수를 사용해 4분기별의 매상 집계치를 구해 봅시다.


SQL> select decode(month,1,'1Q',2 ,'1Q',3 ,'1Q',
                         4,'2Q',5 ,'2Q',6 ,'2Q',
                         7,'3Q',8 ,'3Q',9 ,'3Q',
                        10,'4Q',11,'4Q',12,'4Q') quarter,
            sum(sal)
      from sales
     group by decode(month,1,'1Q',2 ,'1Q',3 ,'1Q',
                           4,'2Q',5 ,'2Q',6 ,'2Q',
                           7,'3Q',8 ,'3Q',9 ,'3Q',
                          10,'4Q',11,'4Q',12,'4Q');

QUARTER   SUM(SAL)
------- ----------
1Q          318000
2Q          301500
3Q          321600
4Q          442200

리스트 5　DECODE 함수를 사용한 4분기별의 매상 집계치

8~11행째의 DECODE 함수에서 month열의 판정을 이하와 같이 가고 있습니다.

1~3의 경우　 ― 1Q를 결과로 돌려줌
4~6의 경우　 ― 2Q를 결과로 돌려줌
7~9의 경우　 ― 3Q를 결과로 돌려줌
10~12의 경우 ― 4Q를 결과로 돌려줌
group by구로 QUARTER(4분기)별로 집계

UNION ALL를 사용했을 경우와 달리, SALES표에의 액세스는 1회로 끝납니다.

■ 4분기별의 집계치를 구하기 (SIGN편)

“SIGN”은

인수가 < 0 → -1
인수가 = 0 → 0
인수가 > 0 → 1

를 돌려주는 함수입니다.


SQL> select sign(5-10),sign(5-5),sign(5-1) from dual;

SIGN(5-10)  SIGN(5-5)  SIGN(5-1)
---------- ---------- ----------
        -1          0          1

리스트 6　SIGN 함수의 간단한 사용예

이 함수를 사용하고, 4분기별의 집계치를 구할 수도 있습니다.


SQL> select decode(sign(month- 4),-1,'1Q',
            decode(sign(month- 7),-1,'2Q',
            decode(sign(month-10),-1,'3Q','4Q'))) quarter,
            sum(sal)
      from sales
      group by decode(sign(month- 4),-1,'1Q',
               decode(sign(month- 7),-1,'2Q',
               decode(sign(month-10),-1,'3Q','4Q')));

QUARTER   SUM(SAL)
------- ----------
1Q          318000
2Q          301500
3Q          321600
4Q          442200

리스트 7　SIGN 함수를 사용한 4분기별의 매상 집계치

DECODE 만으로도 �찬은거 아녀.....란 생각이 들지도 모르지만 SIGN 함수의 큰 메리트는 이하와 같은 경우에 그 힘을 발휘합니다.


SQL> select decode(sign(product_code-100),-1,'0___',
            decode(sign(product_code-200),-1,'1___',
            decode(sign(product_code-300),-1,'2___',
            decode(sign(product_code-400),-1,'3___',
            decode(sign(product_code-500),-1,'4___'))))) Code,
            sum(sal)
      from sales
      group by decode(sign(product_code-100),-1,'0___',
               decode(sign(product_code-200),-1,'1___',
               decode(sign(product_code-300),-1,'2___',
               decode(sign(product_code-400),-1,'3___',
               decode(sign(product_code-500),-1,'4___')))))
      order by 1;

CODE   SUM(SAL)
---- ----------
0___     207000
1___     227700
2___     211000
3___     385700
4___     351900

리스트 8　상품 코드의 1 자리수별로 집계한다

상기의 예를 DECODE 함수만으로 실행한다면 모든 상품 코드를 DECODE 함수의 인수로서 나열 해야 하므로 큰 일이지요.

■ 분기별의 집계치를 구하기 (CASE식 편)

Oracle의 버젼이 「9 i」이상이면, “CASE”식을 사용할 수 있습니다.CASE식을 사용하면 SQL문으로 「IF...THEN...ELSE」논리를 사용할 수 있습니다.


SQL> select
     case
        when month between 1  and 3  then '1Q'
        when month between 4  and 6  then '2Q'
        when month between 7  and 9  then '3Q'
        when month between 10 and 12 then '4Q'
     end CODE,
     sum(sal)
     from sales
     group by
     case
        when month between 1  and 3  then '1Q'
        when month between 4  and 6  then '2Q'
        when month between 7  and 9  then '3Q'
        when month between 10 and 12 then '4Q'
     end;

CODE   SUM(SAL)
---- ----------
1Q       318000
2Q       301500
3Q       321600
4Q       442200

리스트 9　CASE식을 사용한 4분기별의 매상 집계치

CASE식으로 하면, 매우 알기 쉽고 SQL문을 코딩 할 수 있습니다. 그렇지만 더 심플한 쓰는 법이 있습니다.다음의 CEIL 함수를 사용한 쓰는 법입니다.

■ 4분기별의 집계치를 구하기(CEIL편)

“CEIL”는 인수를 정수로 끝맺을 때에 사용하는 함수입니다.


SQL> select ceil(1.5) from dual;

 CEIL(1.5)
----------
         2

리스트 10　CEIL 함수의 간단한 사용예

1.5를 정수로 끝맺을 수 있으므로 반환값으로서 「2」를 얻을 수 있었습니다.통상이라면 별로 빛을 못 보는 함수입니다만 이하와 같이 4분기별의 집계치를 요구할 때 대활약합니다.


SQL> select ceil(month/3)||'Q' quarter,sum(sal)
       from sales
      group by ceil(month/3);

QUARTER   SUM(SAL)
------- ----------
1Q          318000
2Q          301500
3Q          321600
4Q          442200

리스트 11　CEIL 함수를 사용한 4분기별의 매상 집계치

이상과 같이 함수를 구사한다면 SQL문은 보다 파워풀합니다. 심플한 SQL문을 구사하면 유지보수도 용이하고 경우에 따라서는 퍼포먼스도 향상됩니다. 여러분도 여러가지를 궁리해서 SQL을 날려주삼....

[출처] 【제5회】월별 매상을 4분기별로 집계하는 함수 비결|작성자 hirokorea

tag : 쿼리