자바블로그

join 조건 사용시 on 절에 조인 조건의 쿼리문이 긴 경우 using를 이용해서 간략하게 작성 할 수 있습니다.

select t.region_name, t.team_name, t.stadium_id, s.stadium_name

from team t inner join stadium s on (t.stadium_id = s.stadium_id)

라는 문구를 using를 사용하는 경우 아래와 같이 작성 할 수 있습니다.

select t.region_name, t.team_name, t.stadium_id, s.stadium_name

from team t inner join stadium s using (stadium_id)

오라클 재귀쿼리문 작업시 사용하는 connect by 쿼리문 안에서 특정 컬럼 기준으로 정렬을 해야 하는 경우

strt with dept_id = '0'

connect by dept_id = parent_dept_id

order siblings by view_order asc

형식으로 작성 해줘야 합니다.

oracle key 생성시 특정한 길이를 맞춰야 하는경우 lpad+시퀀스 를 이용해서 데이터를 생성 할 수 있습니다.

select 'D'||lpad(시퀀스,7,'0') from dual
-> D0000001

lpad(데이터, 자리수, 채움문자) 형식으로 지정 해주면 특정 자리수를 유지하는 문자 코드를 만들어 낼 수 있습니다.

rpad는 오른쪽 부터 채우기 때문에
D1000000 와 같이 출력 되게 됩니다.

ORACLE에서 테이블의 데이터 개수 및 용량을 확인 하기 위한 쿼리문

--개수(NUM_ROWS=통계테이블에 있는 정보, num_rows2=COUNT결과)

SELECT TABLE_NAME, NUM_ROWS,

         TO_NUMBER(dbms_xmlgen.getxmltype('SELECT COUNT(*) c FROM '||TABLE_NAME).Extract('//text()') num_rows2

FROM USER_TABLES

ORDER BY TABLE_NAME ASC

--용량(MB기준)

SELECT SEGMENT_NAME, BYTES/1024/1024||'MB'

FROM USER_SEGMENTS

WHERE SEGMENT_TYPE='TABLE'

ORDER BY SEGMENT_NAME ASC

.

to_char 이용해서 숫자를 한글로 변환시 fm을 이용해서 원하는 정보를 정의 할 수 있습니다.

select to_char(0001.11, 'fm0000.00') from dual; => 0001.11

select to_char(0001.11, 'fm9999.00') from dual; => 1.11

select to_char(1001.11, 'fm9999.00') from dual; => 1001.11

select to_char(1001.00, 'fm9999.99') from dual; => 1001.

9는 해당 자리의 데이터가 있는경우 출력 없는경우 출력 하지 않는다

0은 해당 자리의 데이터를 무조건 출력

fm 뒤에 나오는 자릿수는 출력 하려는 데이터의 자릿수 보다 커야 한다

ex> to_char(10011.00, 'fm9999.99') 에러발생

Clob 컬럼의 가지고 올때 데이터를 자르는 경우 사용하는 해당 함수의 경우 최대 글자수가 있어서 에러가 발생 합니다.

1. dbms_output.put_line has a documented limit of 255 characters/line.  you have exceeded that.

2. Furthermore, SQL is limited to returning 4000 characters from VARCHAR types.  

3. For fixed-width n-byte CLOBs,

    if the input amount for SUBSTRis greater than (32767/n),

    then SUBSTRreturns a character buffer of length (32767/n), or the length of the CLOB, 

   whichever is lesser. For CLOBs in a varying-width character set, nis the maximum byte-width used for characters in the CLOB.

   ==> 32767 Byte 까지 지원됨..

프로젝트 진행 중 함수안에 인자 값을 '111','222','3333' 같은 내용을 보내서 IN 함수의 인자 값으로 사용 하고자 했으나 varchar2형식으로 넘어 가기 때문에 에러가 발생 되어 검색 중 아래와 같은 쿼리문으로 문자여을 쪼개는 작업을 이용해서 해결

SELECT REGEXP_SUBSTR('111,222,333,444,555,666,777','[^,]+',1,LEVEL)

  FROM DUAL

  CONNECT BY LEVEL <= LENGTH('111,222,333,444,555,666,777') - LENGTH(REPLACE('111,222,333,444,555,666,777',',','')) + 1

'111,222,333,444,555,666' 이렇게 정보를 보내주면

111

222

333

444

555

666

이런 식으로 전달을 해줍니다. IN함숨에 select문으로 인자값을 보내주는 방식 처리

오라클 sys비밀번호가 생각 나지 않을때 console접속은 된다는 가정하에

아래의 명령어를 실행 하여 다시 변경 할 수 있습니다. 콘솔 접속시 해당 서버에 직접 접속해서 실행 해야 합니다.

sqlplus /nolog

connect sys/manager as sysdba

alter user sys identified by new_password

alter user system identified by new_password

new_password 부분에 새로운 비밀번호 입력 

툴을 통한 디비 작업이 불가능한 경우 여러 쿼리문을 한꺼번에 동작 시키기 위해서 .sql문을 생성한뒤

DB서버의 업로드 가능한(실행권한 있는 폴더) 폴더에 업로드 후 

sqlplus접속 상태에서 해당 쿼리를 실행하고자 하는 계정으로 로그인 후 

@/폴더경로1/폴더경로2/xxx.sql 처럼 해당 파일의 절대 경로를 지정해서 실행 할 수 있습니다.