10.명령어

[2008.06.02] 오라클 내장함수^#

숫자 함수(number function)^#

① ROUND(45.926, 2) --> 45.93 : 소수점 두자리수까지 보여주면서 반올림한다.
② TRUNC(45.926, 2) --> 45.92 : 소수점 두자리까지만 보여주고 나머지는 버린다.
③ MOD(1600,300) --> 100 : 1600을 300으로 나누고 나머지를 리턴한다.
* ROUND예제(WHOLE NUMBER:정수)
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL
==> 45.92 46 50

* TRUNC예제
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL
==> 45.92 45(n이 생략된면 일의 자리까지 남기고 버린다.) 40

* SYSTEM 날짜를 갖고 오는 방법.
SELECT sysdate FROM dual

/** 함수 - Number Function **/
--무조건 올림
SELECT CEIL(13.11) FROM DUAL;
SELECT CEIL(13.001) FROM DUAL;

--나머지 구함
SELECT MOD(23, 5) FROM DUAL;
SELECT MOD(57, 145) FROM DUAL;

-- 제곱승
SELECT POWER(3, 2), POWER(3, -2) FROM DUAL;
SELECT POWER(2, 10) FROM DUAL;

--자릿수 지정
SELECT ROUND(345.123, 0) FROM DUAL;
SELECT ROUND(345.123, 2), ROUND(345.123, -1) FROM DUAL;

--지정된 자리까지 잘라내기
SELECT TRUNC(345.123, 1), TRUNC(345.123, 0), TRUNC(345.123, -1) FROM DUAL;
SELECT TRUNC( 345.123 + 0.09, 1 ) FROM DUAL;
SELECT TRUNC( 345.123 + 0.9, 0 ) FROM DUAL;
SELECT TRUNC( 345.123 + 9, -1 ) FROM DUAL;

SELECT SIGN(5.989), SIGN(0), SIGN(-999.098) FROM DUAL;

문자 함수(character function)^#

select ascii('B') from dual;

=> 66

①LOWER( column|expression )

LOWER('String') --> string : 소문자로 변환

②UPPER( column|expression )
UPPER('String') --> STRING : 대문자로 변환

③INITCAP( column|expression )
INITCAP('string') --> String : 첫글자만 대문자이고 나머지글자는 소문자로 변환

④CONCAT( column1|expression1 ,column2|expression2 )
CONCAT('Good','String') --> GoodString : ||와 같은 역할을 한다.

⑤SUBSTR(column|expression, m [,n]) : m값이 음수면 문자값의 끝부터..)
SUBSTR('String',1,3) --> Str : 1번째부터 3개의 문자를 리턴한다.

⑥LENGTH( column|expression )
LENGTH('String') --> 6 : 문자열의 길이를 리턴한다.

⑦INSTR( column|expression, )
INSTR('String','r') --> 3 : 문자열에 'r'이 몇번째 위치하고 있나를 리턴한다.

⑧LPAD( column|expression,n,'string' ) : n 은 전체 길이
LPAD('String',10,'*') --> ****String
: 10자리수중에 모자란 자리수를 '*'로 왼쪽에 채운다.(문자,숫자 가능!!!)

⑨ RPAD('String',10,'*') --> String****
: 10자리수중에 모자란 자리수를 '*'로 오른쪽에 채운다.(문자,숫자 가능!!!)

⑩ LTRIM(' String') --> 'String' : 문자열의 왼쪽 공백을 버린다.

⑪ RTRIM('String ') --> 'String' : 문자령의 오른쪽 공백을 버린다.
* TRIM(leading/tailing/both, trim_character FROM trim_source )

TRIM( 'S' FROM 'SSMITH') --> MITH

/** 함수 - Character Function **/
--특정Character의 아스키값 구하기
SELECT CHR(65) "CHR", ASCII('A') "ASCII" FROM DUAL;
SELECT ASCII( CHR(65) ) FROM DUAL;

SELECT LOWER('My name is LKM') "LOWER", UPPER('My name is LKM') "UPPER" FROM DUAL;

SELECT LPAD('LKM', 10, '*') "LPAD", RPAD('LKM', 10, '*') "RPAD" FROM DUAL;
SELECT LPAD('1234567890', 20, '+') || RPAD('1234567890', 20, '^') "12345678901234567890"
FROM DUAL;
SELECT LPAD('1,234,567', 30, ' ') "LPAD사용으로 30자리 맞춤",
'1,234,567' "단순문자 사용",
1234567 "단순숫자 사용"
FROM DUAL;

SELECT LTRIM(' AAA ') "LTRIM", RTRIM(' AAA ') "RTRIM" FROM DUAL;
SELECT LTRIM( RTRIM( ' A A A ' ) ) "TRIM" FROM DUAL;

SELECT REPLACE('ORACLE', 'A', 'BBB') "REPLACE" FROM DUAL;
SELECT EMP_NAME, REPLACE(EMP_NAME, '이', '박') "이->박"
FROM PERSONNEL
WHERE EMP_NAME LIKE '이%';

SELECT SUBSTR('ORACLE PROJECT', 1, 3) SUBSTR1,
SUBSTR('ORACLE PROJECT', 4, 5) SUBSTR2,
SUBSTR('ORACLE PROJECT', 10) SUBSTR3 FROM DUAL ;
SELECT SUBSTRB('ORACLE PROJECT', 1, 3) SUBSTRB1,
SUBSTRB('ORACLE PROJECT', 4, 5) SUBSTRB2,
SUBSTRB('ORACLE PROJECT', 10) SUBSTRB3 FROM DUAL ;
SELECT SUBSTR('오라클 PROJECT', 1, 3) SUBSTR1,
SUBSTR('오라클 PROJECT', 4, 5) SUBSTR2,
SUBSTR('오라클 PROJECT', 10) SUBSTR3 FROM DUAL ;
SELECT SUBSTRB('오라클 PROJECT', 1, 3) SUBSTRB1,
SUBSTRB('오라클 PROJECT', 4, 5) SUBSTRB2,
SUBSTRB('오라클 PROJECT', 10) SUBSTRB3 FROM DUAL ;

SELECT LENGTH ('ORACLE PROJECT') "LENGTH",
LENGTHB('ORACLE PROJECT') "LENGTHB",
FROM DUAL;
SELECT EMPNO, LENGTH(EMPNO), LENGTHB(EMPNO),
EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME)
FROM PERSONNEL
WHERE EMPNO > '98102';
SELECT HOBBY, LENGTH(HOBBY), LENGTHB(HOBBY)
FROM PERSONNEL
WHERE EMPNO > '98102';

SELECT INSTR ('ORACLE PROJECT', 'R', 1, 1) INSTR1,
INSTR ('ORACLE PROJECT', 'R', 1, 2) INSTR2,
INSTR ('ORACLE PROJECT', 'R', 1, 3) INSTR3
FROM DUAL;
SELECT INSTR ('CORPORATE FLOOR','OR', 3, 2) INSTR,
INSTRB ('CORPORATE FLOOR','OR', 3, 2) INSTRB
FROM DUAL;
SELECT HOBBY,
INSTR (HOBBY, ')', 1, 1) INSTR,
INSTRB(HOBBY, ')', 1, 1) INSTRB
FROM PERSONNEL

WHERE EMPNO > '98102';

날짜와 날짜 처리함수(date, datetime function)^#

date + number : date에 number만큼 후의 날자를 보여준다.
date - number : date에 number만큼 전의 날자를 보여준다.
date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.( date1+date2는 X )
date1 + 숫자/24 : date1에서 시간을 더해 날짜를 보여준다.

NUMTODSINTERVAL

NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
 char_expr은 다음 중의 하나이다.
  ‘DAY’
  ‘HOUR’
  ‘MINUTE’
  ‘SECOND’

MONTHS_BETWEEN('01-SEP-95','11-JAN-94') --> 19.6774194
; 두날짜 사이의 달수를 보여준다.

ADD_MONTHS('11-JAN-94', 6) --> 11-JUL-94
; 날짜에 6개월을 더한 날자를 보여준다.

NEXT_DAY('01-SEP-95','FRIDAY') --> '08-SEP-95'
; 해당일 다음에 오는 FRIDAY의 일자를 보여준다.
('SUNDAY'는 1, 'MONDAY'는 2...이런식으로 숫자를 써줘도 된다.)

LAST_DAY('01-SEP-95') --> '30-SEP-95'
; 해당월의 마지막날자를 보여준다.

ROUND('25-JUL-95','MONTH')--> 01-AUG-95 ROUND('25-JUL-95','YEAR')--> 01-JAN-96
TRUNC('25-JUL-95','MONTH') --> 01-JUL-95 TRUNC('25-JUL-95','YEAR') --> 01-JAN-95

/** 함수 - Date Function **/
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "SYSDATE" FROM DUAL;

SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') "LAST_DAY" FROM DUAL;

SELECT MONTHS_BETWEEN( '2002/01/13', '2002/05/13' ) "MONTHS_BETWEEN (-)",
MONTHS_BETWEEN( '2002/01/13', '2001/11/13' ) "MONTHS_BETWEEN (+)"
FROM DUAL ;
SELECT MONTHS_BETWEEN( '2002/01/13', '2002/01/30' ) "MONTHS_BETWEEN (-)",
MONTHS_BETWEEN( '2002/01/13', '2002/01/01' ) "MONTHS_BETWEEN (+)"
FROM DUAL ;

SELECT ADD_MONTHS(SYSDATE, 1) "ADD_MONTHS (+)",
ADD_MONTHS(SYSDATE, -1) "ADD_MONTHS (-)"
FROM DUAL ;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT ADD_MONTHS( '2002/02/28', 12 ) "1년후",
ADD_MONTHS( '2002/02/28', 24 ) "2년후",
ADD_MONTHS( '2002/02/28', 36 ) "3년후"
FROM DUAL ;

SELECT SYSDATE,
NEXT_DAY(SYSDATE, '일요일') "NEXT_DAY 1",
NEXT_DAY(SYSDATE, 1 ) "NEXT_DAY 2"
FROM DUAL;
SELECT SYSDATE,
NEXT_DAY(SYSDATE, '수요일') "NEXT_DAY 1",
NEXT_DAY(SYSDATE, 4 ) "NEXT_DAY 2"
FROM DUAL;

데이터 형변환 함수(conversion function)^#

nlsparams : 십진수, 그룹구분자, 지역 통화 기호, 국제 통화 기호

TO_CHAR(date,['format'],[nlsparams]) : date를 format에 맞게 문자열로 변환한다.

- Date Format Elements
YYYY --> 1999 (년출력) , YEAR --> nineteen ninety-nine (년출력)
MM --> 12 (월출력) , MONTH --> DECEMBER (월출력), MON --> DEC
D --> 요일을 숫자로 리턴한다.(일요일은 1, 월요일은 2...)
DD --> 07 (달의 일출력)
DDD --> 200 (연의 일출력)그 해의 총 몇 일째인가를 리턴한다.
DAY --> MONDAY (요일출력) DY-->MON
CC --> 20 (몇 세기인지를 보여준다.)
WW --> 그 해의 몇 번째 주인가를 리턴한다.
W --> 그 달의 몇 번째 주인가를 리턴한다.

=>  흔히 사용하는 위의 형태는 일상 생활에서 사용되는 몇주 개념적용이 아닌

7일 단위로 끊은 주 개념입니다.

예를 들어 2005년 1월 2일의 경우 일요일로서 일상 생활에서는 2005년의 2주차 입니다.

하지만 to_char('20050102','ww') 의 경우 '2005'년도의 2일째 이기에 1주 로 계산 합니다.

(2006년의 경우 '20060101'이 정확히 일요일이기 때문에 to_char(sysdate,'ww')의 값을 오해 할 수 있습니다. 2005년 또는 2007년 경우를 확인 해 보시면 이해에 도움이 될 것입니다.)

* Element들을 소문자로 쓰면 소문자로 나오고 대문자로 쓰면 대문자로 출력된다.
HH or HH12 or HH24 / MI(0-59분) / SS(0-59초)


* 문자열은 " " 묶어 추가한다 DD " of " MONTH --> 12 of DECEMBER

*숫자 접미어는 숫자를 문자로 표기. TH(4->4TH)/ SP(4->FOUR)/ SPTH or THSP(4->FOURTH)

ddspth : 14-> fothteenth

* / . , : 구두점은 결과에 그대로 출력한다. * 공백, 선행제로를 제거하는 fm요소가 있다.

TO_CHAR(number,'format',[nlsparams]) : number를 format에 맞게 문자열로 변환한다.

- Number Format Elements
9 : 999,999 --> 1,234 $: 부동 달러 기호 $99999 -> $1234
0 : 099999 --> 001234 99.999EEEE -> 1.234E+03 B: 0값을 공백으로
L : L99,999 --> FF1,234 (NLS_CURRENCY에 설정되어있는 값이 L로 보여진다.)

TO_NUMBER(char,['format'],[nlsparams]) : 숫자형태의 문자열을 숫자로 변한한다.
TO_DATE(char,['format'],[nlsparams]):날자형태의 문자열을 format에 맞게 날자형식으로 변환 한다.

/** 함수 - Conversion Function **/
SELECT TO_CHAR(1234567.891) "TO_CHAR1",
TO_CHAR(1234567.891, '999') "TO_CHAR2",
TO_CHAR(1234567.891, '9,999,999') "TO_CHAR3",
TO_CHAR(1234567.891, '0.0000') "TO_CHAR5",
TO_CHAR(1234567.891, '9,999,999.0000') "TO_CHAR6",
TO_CHAR(123, '9,999.00') "TO_CHAR7",
TO_CHAR(123, '9,999.99') "TO_CHAR8"
FROM DUAL ;
SELECT TO_CHAR(1234567.891, '9G999G999') "TO_CHAR3",
TO_CHAR(1234567.891, '0D0000') "TO_CHAR5",
TO_CHAR(1234567.891, '9G999G999D0000') "TO_CHAR6",
TO_CHAR(123, '9G999D00') "TO_CHAR7",
TO_CHAR(123, '9G999D99') "TO_CHAR8"
FROM DUAL ;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "TO_CHAR1",
TO_CHAR(SYSDATE, 'YYYY/MM') "TO_CHAR2",
TO_CHAR(SYSDATE, 'YYYY') "TO_CHAR3",
TO_CHAR(SYSDATE, 'DD') "TO_CHAR4",
TO_CHAR(SYSDATE, 'DAY') "TO_CHAR5",
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') "TO_CHAR6",
TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYY-MM-DD') "TO_CHAR7",
TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYYMMDD HHMISS') "TO_CHAR8"
FROM DUAL ;

SELECT TO_NUMBER('123456.9') "TO_NUMBER1",
TO_NUMBER('1234567') "TO_NUMBER2"
FROM DUAL ;
SELECT TO_NUMBER('123,456.9', '999,999.9') "TO_NUMBER1",
TO_NUMBER('1,234,567', '9G999G999') "TO_NUMBER2"
FROM DUAL ;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT TO_DATE('20020824') "TO_DATE1",
TO_DATE('2002-08-24') "TO_DATE2",
TO_DATE('200208') "TO_DATE3"
FROM DUAL ;
SELECT TO_DATE('20020824', 'YYYYMMDD') "TO_DATE1",
TO_DATE('2002-08-24', 'YYYY-MM-DD') "TO_DATE2",
TO_DATE('200208', 'YYYYMM') "TO_DATE3"
FROM DUAL ;
SELECT TO_DATE('2002/08/24 08:14:06', 'YYYY/MM/DD HH24:MI:SS') "TO_DATE1",
TO_DATE('2002/08/24 08:14:06 오후', 'YYYY/MM/DD HH:MI:SS AM') "TO_DATE2"
FROM DUAL ;

기타함수(miscellaneous single row function)^#

NVL Funcion : 값이 null일 때 설정값을 보여준다.
NVL(number_column, 0) : null일 때 0을 보여준다.
NVL(date_column, '01-JAN-95') : null일 때 '01-JAN-95'를 보여준다.
NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.
* column Type과 표현식의 type이 반드시 일치해야 한다.

DECODE Function : CASE or IF-THEN-ELSE 형식과 비슷하다.
*DECODE(col/expression, search1, result1 [,search2,result2,…] [,default])
F1 (F2 (F3 (col,arg1),arg2),arg3)

이중 Decode나 Nvl 그리고, To_데이터형은 너무나도 많이 쓰여서 잘 안 까먹지만....
숫자나 문자관련 함수 잘 안쓰면 찾기가 너무 힘들다는..... '그 뭐시기냐 문자열의 위치 찾아내는거
그 함수 뭐냐?' 라면 '그게 뭔데?'라는 반문이-_- 차라리 instr이 뭐하는거지가 더 쉬운 질문이다-_-
대전 있을때 자료전환건이 있어서... 처리를 하려고 보니 특정칼럼 하나에 잘 쓰이지 않는
두개의 필드값을 탭구분자로 나눠서 때려넣어논 꼴을 본적이 있다. instr, chr, replace로 간단히 해결가능할걸 instr과 chr를 잘 몰라 통째로 엑셀로 받아 수작업을 했다라는....

/** 함수 - ETC **/
SELECT EMPNO, EMP_NAME, HOBBY 취미, WELL 특기
FROM PERSONNEL
WHERE EMPNO BETWEEN '98001' AND '98005';
SELECT EMPNO, EMP_NAME, HOBBY 취미, NVL(WELL, '(없다)') 특기
FROM PERSONNEL
WHERE EMPNO BETWEEN '98001' AND '98005';

SELECT DECODE( '나', '나', '맞다', '아니다' ) "나가 나면 맞다, 아니면 아니다"
FROM DUAL ;
SELECT DECODE( '나', '대명', '아니다',
'너' , '아니다',
'그' , '아니다',
'나' , '맞다',
'모르겠다' ) FROM DUAL ;
SELECT EMPNO,
EMP_NAME,
DECODE (HT_CODE, '1', '현재원', '2', '휴직', '퇴사') HT_CODE
FROM PERSONNEL
WHERE EMPNO BETWEEN '98071' AND '98080';

SELECT GREATEST (132, 33, 45, 90, 60.77) GREATEST,
LEAST (132, 33, 45, 90, 60.77) LEAST
FROM DUAL;
SELECT GREATEST ('이공명', '이대명', '최수미') GREATEST,
LEAST ('이공명', '이대명', '최수미') LEAST
FROM DUAL;

SELECT USERENV('LANGUAGE') "LANGUAGE",
USERENV('TERMINAL') "TERMINAL",
USERENV('SESSIONID') "SESSIONID"
FROM DUAL;

SELECT UID, USER FROM DUAL;

그룹함수 - 집계(Aggregate) 함수^#

/** 함수 - Group Function **/
SELECT AVG(HEIGHT), AVG(WEIGHT) FROM PERSONNEL;

SELECT MAX(EMPNO), MAX(EMP_NAME), MIN(EMPNO), MIN(EMP_NAME) FROM PERSONNEL;
SELECT MAX(HEIGHT), MIN(HEIGHT) FROM PERSONNEL;

SELECT SUM(WEIGHT) FROM PERSONNEL;

SELECT COUNT(*), COUNT(EMPNO), COUNT(JIKCH_CODE) FROM PERSONNEL;

그룹함수 -  분석(Analytic) 함수^#

윈도우(windowing) 분석 함수^#

윈도우 분석함수는 전체 결과 집합 중에서 연속선상에 있는 부분 집합을 대상으로 적용하는 함수의 집합을 말한다.
윈도우 분석함수의 종류는 AVG, COUNT, MAX, MIN,STDDEV,SUM,VARIANCE,FIRST_VALUE,LAST_VALUE 등이 있다.
부분 집합을 결정하기 위한 범위는 CURRENTROW, UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING 등을 지정할 수 있다.

【형식】
   (SUM | AVG | MAX | MIN | COUNT | STDDEV | VARIANCE | FIRST_VALUE | LAST_VALUE)
   ({ | * }) OVER
   ([PARTITION BY [,...]]
   ORDER BY  [collate clause]
   [ASC | DESC] [NULLS FIRST | NULLS LAST] [,...]
   ROWS | RANGE
   {{UNBOUNDED PRECEDING |  PRECEDING} | BETWEEN
   {UNBOUNDED PRECEDING |  PRECEDING}
   AND {CURRENT ROW |  FOLLOWING}}

PseudoColumn을 의미하는 것^#

LPAD, RPAD : 문자열 채우기^#

문법 :

lpad ('string', n [, 'string_pad')
rpad ('string', n [, 'string_pad')

예제 :

select lpad('*', 5, '%') lpad, rpad('%', 5, '*') rpad from dual

결과 :

설명 :

VSIZE : 문자의 길이를 구한다.^#

예제 :

select vsize('가나다'), '가나다' from dual

결과 :

출처 : http://ebizdocs.springnote.com/pages/1250050?print=1

오라클 clob 예제

by kkaok
2003-06-27

Lob type이란?

lob(Large Object) 타입은 대용량 데이터를 저장하기위한 데이터 타입으로 오라클 8버전부터 지원된다.

Lob 타입의 특징

이전에 대용량 처리는 long 타입으로 처리를 할 수 밖에 없었다. 그런데 이 칼럼은 한테이블에 하나만 사용할 수 있으며 최대 크기가 2GB였다. 더군다나 검색을 구현하기가 어려워 포기를 해야 했다. 그래서 이런 문제를 보안하기 위 나온 것이 Lob 타입이다. Lob 타입은 테이블에 여러개의 컬럼을 생성할 수 있으며 최대 크기가 4GB이다. 또한 long 타입보다 훨씬 쉬운 검색기능을 제공한다.

Lob type의 종류

이전에 대용량 처리는 long 타입으로 처리를 할 수 밖에 없었다. 그런데 이 칼럼은 한테이블에 하나만 사용할 수 있으며 최대 크기가 2GB였다. 더군다나 검색을 구현하기가 어려워 포기를 해야 했다. 그래서 이런 문제를 보안하기 위 나온 것이 Lob 타입이다. Lob 타입은 테이블에 여러개의 컬럼을 생성할 수 있으며 최대 크기가 4GB이다. 또한 long 타입보다 훨씬 쉬운 검색기능을 제공한다.

Clob에 저장하기

  1: import java.sql.*;
  2: import java.io.*;
  3: import oracle.sql.*;
  4: import oracle.jdbc.driver.*;
  5: ...
  6: 
  7: public void insertQuery(UploadBoard up,int re_step,int re_level) 
         throws Exception  
  8: {
  9:   ResultSet rs   = null;
 10:   PreparedStatement pstmt = null;
 11:   Connection conn = null;
 12:   String query = "insert into "+up.getTableName()+" (seq,re_step,
           re_level,name,title,pwd,email,readnum,writeday,ip,relativeCnt,
               homepage,imgInfo,content,tag) 
                   values(?,?,?,?,?,?,?,0,sysdate,?,0,?,?,empty_clob(),?)";
오라클 명령어 empty_clob()을 이용해 공간을 확보한다.
 13:   try{
 14:     conn = DBManager.getClobConnection();
 15:     conn.setAutoCommit(false);
CLOB column을 업데이트 하는동안 다른 process의 접근을 막기위해 
setAutoCommit(false)를 반드시 설정해야 한다. 이부분이 가장 중요하다.
 16:     pstmt = conn.prepareStatement(query);
 17:     pstmt.setInt(1,up.getSeq());
 18:     pstmt.setInt(2,re_step);
 19:     pstmt.setInt(3,re_level);
 20:     pstmt.setString(4,up.getName());
 21:     pstmt.setString(5,up.getTitle());
 22:     pstmt.setString(6,up.getPwd());
 23:     pstmt.setString(7,up.getEmail());
 24:     pstmt.setString(8,up.getIp());
 25:     pstmt.setString(9,up.getHomepage());
 26:     pstmt.setString(10,up.getImgInfo());
 27:     pstmt.setString(11,up.getTag());
 28:     pstmt.executeUpdate();
 29:     pstmt.close();
 30:     String query2 = " select /*+ index_desc("+up.getTableName()+
             " "+up.getTableName()+"_indx) */ content from "+
                 up.getTableName()+" where seq = ? for update ";
for update를 이용해 CLOB column을 lock한다.
 31:     pstmt = conn.prepareStatement(query2);
 32:     pstmt.setInt(1,up.getSeq());
 33:     rs = pstmt.executeQuery();
 34:     if(rs.next()) {
 35:       CLOB clob = ((OracleResultSet)rs).getCLOB(1);        
 36:       Writer writer = clob.getCharacterOutputStream();
 37:       Reader src = new CharArrayReader(up.getContent().toCharArray());
 38:       char[] buffer = new char[1024];
 39:       int read = 0;
 40:       while ( (read = src.read(buffer,0,1024)) != -1) {
 41:         writer.write(buffer, 0, read); // write clob.
 42:       }
 43:       src.close();        
 44:       writer.close();
 45:     }
 46:     conn.commit();
 47:     conn.setAutoCommit(true);
CLOB column에 데이터을 저장하였다면 commit()을 실행시키고 
conn.setAutoCommit(true)로 다시 설정한다.

 48:   }finally{
 49:     DBManager.close(rs,pstmt,conn);
 50:   }
 51: }

Clob 불러오기

     ...
  1: public BoardTable getViewData(String tableName,int seq) throws Exception
  2: {
  3:   BoardTable bTable = new BoardTable();
  4:   String query = " select * from "+tableName+" where seq = ? ";
  5:   ResultSet rs = null;
  6:   PreparedStatement pstmt = null;
  7:   Connection conn = null;
  8:   try{
  9:     conn = DBManager.getConnection();
 10:     pstmt = conn.prepareStatement(query);
 11:     pstmt.setInt(1,seq);
 12:     rs = pstmt.executeQuery();
 13:     if (rs.next()) {
 14:       ...
 15:       ...      
 16:       StringBuffer output = new StringBuffer();
 17:       Reader input = rs.getCharacterStream("content");
 18:       char[] buffer = new char[1024];
 19:       int byteRead;
 20:       while((byteRead=input.read(buffer,0,1024))!=-1){
 21:         output.append(buffer,0,byteRead);
 22:       }
 23:       input.close();
 24:       bTable.setContent(output.toString()); 
CLOB 데이터를 불러오기 위해서는 위에서처럼 
rs.getCharacterStream("content")로 불러서 StringBuffer에 담아야 한다.
 25:       ...
 26:       ...
 27:     }
 28:   } finally {
 29:       DBManager.close(rs,pstmt,conn);
 30:   }
 31:   return bTable;
 32: }


 ================================================ 
    * Oracle Community OracleClub.com 
    * http://www.oracleclub.com 
    * http://www.oramaster.net 
    * 운영자 : 김정식 (oramaster _at_ empal.com)
  ================================================ 

oracle 공백 제거

홍 길 동  -> 홍길동

홍   길동 -> 홍길동

SELECT RTRIM(REPLACE(필드명' ','')) FROM 테이블명;

디비는 SQL문의 응답을 빠른 시간안에 내야하죠...

대부분의 SQL문은 select입니다.
검색이 가장 빨라야 하는 거죠.

인덱스라고 하는 것은 검색시의 빠른 응답을 위해서 마련해 놓은 자료구조입니다.
정확히는 B+트리라는 자료구조를 이용하는데요.

테이블의 데이터들은 데이터파일내에 위치하게 되고, 이를 검색하기 위해서는 많은 수의 레코드들을 비교해야 합니다.
그래서 특정 필드들을 이용해서 B+트리를 구성해 놓는 것을 인덱스하고 보시면 되겠습니다.
자료구조에서 트리의 경우는 검색이 빠르다는 장점을 가지고 있죠.

결국 테이블의 데이터를 가지고 인덱스를 만들어서 검색속도를 향상시키는 것입니다.

대부분의 DB에서 기본적으로 primary key는 인덱스를 자동으로 생성하는 경우가 많습니다.

물론 이 인덱스의 종류는 다양합니다. 데이터의 종류나 성향에 따라서 검색속도를 향상시키는 몇가지의 방법이 존재하는 것이죠.

이 인덱스는 검색 쿼리문의 조건절에 명시된 조건절을 따르게 됩니다.
조건이 많은 경우는 디비내의 옵티마이져가 선택한 인덱스를 이용해서 검색을 하게 됩니다.(이는 디비마다의 알고리즘이 다릅니다.)
그래서 오라클의 경우는 옵티마이져가 정한 인덱스가 아닌, 개발자/DBA가 정한 인덱스를 타도록 지정하는 힌트라는 옵션도 있습니다.

결론적으로 인덱스는
1. 검색속도 향상을 위한 것이다.
2. 내부적으로 B+ 트리를 이용한다.
3. 데이터의 성향에 따라서 다양한 인덱스가 존재한다.
4. INSERT/UPDATE/DELETE시는 인덱스가 성능을 약화시킨다.
5. 인덱스가 다수 존재하는 경우 조건절에 따라서 타는 인덱스가 달라진다.

이정도의 특성이 있겠습니다.

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

CREATE [ UNIQUE | BITMAP ] INDEX index_name ON table_name(column_name)

[TABLESPACE tablespace_name];

UNIQUE : UNIQUE Index를 생성한다.

BITMAP : BITMAP Index를 생성한다.

index_name : 생성하고자 하는 인덱스 이름

table_name : 인덱스를 생성하고자 하는 테이블 이름

column_name : 인덱스로 생성하고자 하는 컬럼 이름

tablespace_name : 인덱스가 위치할(생성될) 테이블 스페이스 이름

예) CREATE INDEX idx_emp ON tb_emp (empno);

tb_emp 테이블에 empno 컬럼을 이용하여 idx_emp를 생성한다.

범례)

대문자 : Reserved Word

소문자 : User Define

[ ] : Option, 지정하지 않아도 되거나 생략시 기본 설정값으로 대체됨.

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

1.       oracle 다운로드 및 압축해제

2.       oracle 유저생성 및 권한부여

3.       pre-install RPM

4.       jdk 설치

5.       공유메모리 및 세마포 설정

6.       오라클 사용자 초기화 파일 구성

7.       오라클 셋업

8.       Database 시작과 종료 TIP & TECH

1. oracle 다운로드 및 압축해제

오라클을 설치하시려면 오라클 파일 혹은 시디가 있어야 겠죠

시디면 상관없지만 파일을 받아서 설치 할 경우 아래를 따라하세요

zcat ship_lnx_920_disk1.cpio.gz | cpio –idmv

zcat ship_lnx_920_disk2.cpio.gz | cpio -idmv

zcat ship_lnx_920_disk3.cpio.gz | cpio -idmv

disk1, disk2, disk3이 생기고 생긴 파일들은 폴더를 하나 생성해서 이동시킨다

여기서는 /home/oracle로 이동

시디의 경우에는 바탕화면에 아이콘이 생성됩니다.

2. oracle 유저생성 및 권한부여

아래의 명령어로 oracle 유저생성 및 권한부여를 하셔야 됩니다

groupadd dba

useradd oracle –g dba

chown –R oracle.dba /home/oracle

chmod 777 / home

chmod 777 /home/oracle

3. pre-install RPM

오라클 설치시에 반드시 필요한 패키지들로 보통 레드햇 9.0을 기준으로

풀패키지 설치시 대부분 설치가 되어있습니다.

그러나 빨간 글씨의 패키지는 오라클 메타링크에서 다운을 받으셔야 되는데

메타링크 계정을 만드는 방법이 어렵습니다.

문의주시면 파일을 보내드릴께요

* Required OS Components

            - compat-db-4.1.25-9

            - compat-gcc-32-3.2.3-47.3

            - compat-gcc-32-c++-3.2.3-47.3

            - compat-oracle-rhel4-1.0-3

            - compat-libcwait-2.0-1

            - compat-libgcc-296-2.96-132.7.2

            - compat-libstdc++-296-2.96-132.7.2

            - compat-libstdc++-33-3.2.3-47.3

            - gcc-3.4.3-9.EL4

            - gcc-c++-3.4.3-9.EL4

            - gnome-libs-1.4.1.2.90-44

            - gnome-libs-devel-1.4.1.2.90-44

            - libaio-devel-0.3.102-1

            - libaio-0.3.102-1

            - make-3.80-5

            - openmotif21-2.1.30-11

            - xorg-x11-deprecated-libs-devel-6.8.1-23.EL

            - xorg-x11-deprecated-libs-6.8.1-23.EL

The compat-oracle-rhel4-1.0-3 and compat-libcwait-2.0-1 packages are available

from Oracle Metalink

While installing the patch you might receive the warning. It is a normal behaviour.

단. 의존성이 있는 RPM들은 같이 설치하여야 합니다.

보통 풀패키지로 설치 했을 때 대부분의 패키지들이 깔리지만 오라클의 메타링크에서 제공하는 패키지는 다운을 받아서 설치해야 합니다.

레드햇 9에서는

xorg-x11-deprecated-libs-devel-6.8.1-23.EL

compat-libgcc-296-2.96-132.7.2

openmotif21-2.1.30-11
위 패키지를 설치 안해도 잘 깔린다고 합니다.

4. jdk설치

오라클 설치시 반드시는 아니지만 여러 오류를 피해가시고 싶으면 jdk를 설치하시면 됩니다

다운로드 :

RPM in self-extracting file(j2sdk-1_4_2_08-linux-i586-rpm.bin, 33.64M)

chmod +x를 주고 설치

rpm –Uvh로 설치

/usr/java/j2sdk-1_4_2_08에서 /usr/local/j2sdk-1_4_2_08로 이동후

$/usr/local/에서 ln –s j2sdk-1_4_2_08 java

/etc/profile에서 다음 내용을 설정

# For java

export JAVA_HOME=/usr/local/java

export PATH=$JAVA_HOME/bin:.:$PATH

5. 공유메모리 및 세마포 설정

오라클사가 제공한 커널 파라미터 값에 대한 권고값

** 공유 메모리와 세마포어의 개수 알아보기**

원래 공유메모리와 세마포어를 변경한 값을 적용하여 커널을 재컴파일 해야하지만

여건이 되지 않는 경우 소프트웨어적으로 공유메모리와 세마포어를 변경할 수 있다.

[root@ora9 kernel]# vi /etc/sysctl.conf

가장 마지막에 다음을 추가합니다.

kernel.shmmax = 2147483648

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range=1024 65000

시스템 리소스를 제한하는 설정파일에 다음을 추가한다.

[root@ora9 kernel]# vi /etc/security/limits.conf

가장 마지막에 다음을 추가합니다.

oracle soft nofile 65536

oracle hard nofile 65536

oracle soft nproc 16384

oracle hard nproc 16384

이 후 재부팅을 권장합니다.

6. 오라클 사용자 초기화 파일구성

oracle$ vi .bash_profile

# for ORACLE

export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export ORACLE_OWNER=oracle

export ORACLE_SID=ORA92
export ORACLE_TERM=xterm
export NLS_LANG=AMERICAN_AMERICA.KO16KSC5601
export TNS_ADMIN=$ORACLE_HOME/network/admin

export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export LD_ASSUME_KERNEL=2.4.1

export THREADS_FLAG=native

export TEMPDIR=/tmp

export EDITOR=vi
export PATH=$PATH:$ORACLE_HOME/bin
umask 022

# for DBA/User

alias oh='cd $ORACLE_HOME'

alias ob='cd $ORACLE_BASE'

alias cls='clear'

alias ls=’ls -F’

alias rm=’rm –i’

umask 022

# for java

export JAVA_HOME="/usr/jdk"

CLASSPATH=".:$JAVA_HOME/lib/tools.jar"

CLASSPATH="$CLASSPATH:$ORACLE_HOME/jdbc/lib/classes12.jar"

CLASSPATH="$CLASSPATH:$ORACLE_HOME jdbc/lib/nls_charset12.jar"

CLASSPATH="$CLASSPATH:$ORACLE_HOME/rdbms/jlib/xsu12.jar"

CLASSPATH="$CLASSPATH:$ORACLE_HOME/lib/xmlparserv2.jar"

CLASSPATH="$CLASSPATH:$ORACLE_HOME/lib/classgen.jar"

CLASSPATH="$CLASSPATH:$ORACLE_HOME/lib/oraclexsql.jar"

CLASSPATH="$CLASSPATH:$ORACLE_HOME/lib/xmlplsql.jar"

CLASSPATH="$CLASSPATH:$ORACLE_HOME/lib/xschema.jar"

export CLASSPATH

오라클 환경 설정 항목과 그에 대한 설명

7. 오라클 셋업

x-windows에 oracle계정으로 로그인

oracle에서 ./runinstaller

언어가 깨지거나 실행이 안될 경우에는 다음명령을 실행

unset LANG

Unix Group Name-> oinstall 또는 dba

sid-> 일반적으로 ora9 혹은 ora92

Global Database Name -> ora9.도메인

문자셋은 [6] 사용자 초기화파일에서 설정한 것과 같아야 한다.

export NLS_LANG=AMERICAN_AMERICA.KO16KSC5601

설치 중간에 orainstRoot.sh 팝업이 나올시

새창에서

su –root

cd /tmp

./orainstRoot.sh실행

완료 후 진행

설치 진행 100% 후에

*/oracle/ora92/root.sh 실행 대화상자 팝업

$su – root

#cd / oracle/ora92

#./root.sh

시스템 기본 디렉토리를 물으면

/usr/bin으로 설정

8. Database 시작과 종료

①                    glibc 관련 rpm 패키지 원상복구하기

[root@ora9 /]# cd /usr/local/src/

[root@ora9 src]# rpm -Uvh --nodeps glibc-common-2.3.2-11.9.i386.rpm

[root@ora9 src]# rpm -Uvh --nodeps glibc-devel-2.3.2-11.9.i386.rpm

[root@ora9 src]# rpm -Uvh --nodeps glibc-2.3.2-11.9.i386.rpm

②                    오라클 데이터베이스 시작하기

Database 의 시작과 종료는 반드시!! Oracle 계정으로 수행해야 합니다.

[root@ora9 src]# su oracle

[oracle@ora9 src]$ sqlplus /nolog

SQL> connect / as sysdba

SQL> startup

오라클 데이터베이스를 시작하고 종료하기 위해서는 OS에서의 인증과 암호 파일을 생성하는

툴인 orapwd를 통해야 한다. 그리고 sys 스키마의 권한인 sysdba 권한과 public 스키마

권한인 sysoper 권한의 특별한 시스템 권한을 소유한 사용자이어야 한다.

· sysdba : 데이터베이스 시작/종료, 아카이브 및 복구 작업, ALTER DATABASE OPEN,

MOUNT, BACKUP, CHANGE, CHARACHER SET 절의 명령어 실행

· sysoper : 데이터베이스 시작/종료, 아카이브 및 복구 작업, ALTER DATABASE OPEN,

MOUNT, BACKUP 절의 명령어 실행

SQL> SELECT * FROM v$version;

현재의 오라클 데이터베이스 인스턴스의 버전 확인하기

③                    오라클 데이터베이스 종료하기

[oracle@ora9 src]$ sqlplus /nolog

SQL> connect / as sysdba

SQL> shutdown immediate

SQL> exit

④                    oratab 파일 편집하기

오라클 데이터베이스를 /etc/rc.d/ini.d에 스크립트로 설정하여 자동으로 실행하게 하여봅시다.

[root@ora9 src]# vi /etc/oratab

다음 부분을 수정 ([SID], [ORACLE_HOME], [자동실행/종료 플래그]로 구성되어 있습니다.)

ora9:/opt/oracle/product/9.2.0.1.0:N è ora9:/opt/oracle/product/9.2.0.1.0:Y

⑤                    Parameter 파일 링크

xxxxxxxxxxxx은 일정치 않은 숫자 입니다.

[root @ora9 /]# cp /opt/oracle/admin/ora9/pfile/initora9.ora.xxxxxxxxxxxx \

/opt/oracle/product/9.2.0.1.0/dbs/initora9.ora

⑥                    s/etc/rc.d/init.d 에 등록하기

oracle9i 스크립트를 /etc/rc.d/init.d 에 복사합니다.

[root@ora9 src]# cp /usr/local/src/oracle-9.2.0.1.0 /etc/rc.d/init.d/oracle9i

oracle9i에 실행권한을 부여합니다.

[root@ora9 src]# chmod 755 /etc/rc.d/init.d/oracle9i

시스템에 oarcle9i 데몬을 등록한다.

[root@ora9 src]# chkconfig --add oracle9i

[root@ora9 src]# chkconfig --level 2345 oracle9i on

Oracle Database를 재시작 시켜본 후, LISTENER 데몬이 띄워져 잇는지 확인합니다.

[root@ora9 src]# /etc/rc.d/init.d/oracle9i start

[root@ora9 src]# ps ax | grep LISTENER

설치 로그보기

tail –f $ORA_HOME/app/oracle/product/orainventory/logs/installactions.log

Oracle 삭제

$ORACLE_HOME 디렉토리에 있는 설치파일들 전부삭제

/etc 밑에 orainst.loc, oratab 삭제

/usr/local/bin/oraenv 파일 삭제

/tmp 디렉토리에서 관련파일 삭제

# rm /etc/oratab /etc/emtab

이후 다시 재설치 하시면 됩니다.

출처가 어디인지는 잊어버렸습니다...

원작자분께 죄송하단 말씀을..^^;;

기존 내용에 제가 이해하기 쉽도록 내용을 추가하였습니다.

그리고 초보가 한번에 따라하기는 어렵습니다.

나름대로의 시행착오를 겪어보셔야 될 것입니다.

^^ 잘되시길 바랍니다.