단단이아빠의 세상

본문

출처 : http://oracleebs.co.kr/?p=41

STX_OUT_TEMP 테이블의 ATTRIBUTE1 컬럼의 값들이 아래 그림에서와 같습니다.

즉, VARCHAR2 형식으로 정의된 컬럼에 저장된 값은 숫자형, 문자형, 기호 이렇게 아무렇게나 저장이 되어 있습니다.
이 컬럼의 값들 중에 숫자형식으로 나타낼 수 있는 값들만을 쿼리하고 싶다면,

		SELECT ATTRIBUTE1
  FROM STX_OUT_TEMP
 WHERE TRANSLATE(ATTRIBUTE1,'A1234567890','A') IS NULL;

과 같은 SQL 문을 생각할 수 있습니다.

TRANSLATE() 함수를 이용하여 숫자 혹은 문자를 특정문자(위의 SQL에서는 NULL로 바뀜)로 변환하는 방법입니다. TRANSLATE() 함수에 대한 설명은 레퍼런스를 찾아보시면 될 듯 하구요. 간단히 예를 들어

		SELECT TRANSLATE(ATTRIBUTE1,'A1234567890','A'),                                                    -- 숫자
       TRANSLATE(ATTRIBUTE1,'0abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','0'),          -- 문자
       TRANSLATE(ATTRIBUTE1,'@1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','@') -- 특수문자
  FROM STX_OUT_TEMP;

위의 SQL을 실행해보면, TRANSLATE() 함수로 문자 혹은 숫자 중 골라내고 싶은 값을 NULL 문자로 치환하여 WHERE 문에 IS NULL 을 조건으로 주면 해당 RECORD를 SELECT 할 수 있습니다.

다시 말해, 컬럼의 값들이 숫자로만 되어 있는 지, 아니면 문자로 되어있는지 이렇게 알고 싶을 때가 있는데 이때 쉽게 사용할 수 있는 함수가 ORACLE 에는 없는 것으로 알고 있습니다. 그래서 위와 같은 SQL문을 생각하게 되는 것입니다.

그러나, MS-SQL 에서는 ISNUMERIC() 이라는 함수가 있습니다.  
MS-SQL의 이 함수처럼 ORACLE 에서도 테이블의 특정 컬럼에 문자와 숫자가 병행되어 입력되는 상황에서 이 컬럼중에서 숫자 형식으로만 된 값과 아니면 문자(정확히 말하면, 숫자로 변환할 수 없는)값으로 된 레코드만을 알고자 할 때가 필요합니다.

그래서, 오라클에서도 MS-SQL에서와 동일한 기능을 하는 함수를 만들어 사용하면 될 듯 하여 아래와 같은 함수를 만들었습니다.

		CREATE OR REPLACE FUNCTION ISNUMERIC(i_str VARCHAR2) RETURN NUMBER
IS
 v_ret NUMBER;
BEGIN
  IF i_str IS NULL OR LENGTH(TRIM(i_str)) = 0 THEN
    RETURN 0;
  END IF;
 
  V_RET := TO_NUMBER(I_STR);
  RETURN 1;
 
EXCEPTION WHEN OTHERS THEN
  RETURN 0;
END ;

함수의 내용을 보면 입력 값이 올바른 숫자 데이터 형식으로 변환되면 1을 반환하고, 그렇지 않으면 0을 반환합니다.

이렇게 작성된 함수를 활용하여, 처음 소개한 SQL 문을 변경하여 보면

		SELECT ATTRIBUTE1
  FROM STX_OUT_TEMP
 WHERE ISNUMERIC(ATTRIBUTE1) = 1;

로 변경할 수 있습니다.

이와 같이 공통적으로 활용될 수 있는 함수는 미리 정의하여 프로젝트 팀원간에 공용으로 활용하면 좋을 듯 합니다.

작성 : amagrammer / 편집 : amagrammer

본문

 시퀀스 사용

시퀀스.NextVal

select 시퀀스.CurrVal from dual;

본문

시작

[oracle@db ~]$  lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-APR-2010 00:15:13

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start

sql>startup nomount

sql>startup force

sql>startup mount;

sql>alter database open test; 

# OracleInstanceShutdown

+ Shutdown normal 
- 새로운 사용자 연결 불허
- 모든 사용자가 데이터베이스 접속을 끊을 때까지 대기
- 이미 연결된 사용자는 계속 작업 가능
- 모든 사용자의 접속이 끊기면 데이터베이스를 닫고 인스턴스를 디스마운트한 후에 인스턴스 종료

+ Shutdown immediate
- 새로운 사용자 연결 불허
- 모든 사용자의 데이터베이스 접속 종료
- 커밋되지 않은 트랜잭션들은 롤백
- 데이터베이스를 닫고, 인스턴스 디스마운트, 인스턴스 중료 순

+ Shutdown transactional
- 새로운 사용자 연결 불허
- 새로운 트랜잭션 불허. 사용자가 새로운 트랜잭션을 시도할 경우 세션 종료
- 사용자의 롤백하거나 커밋하지 않은 트랜잭션이 커밋될 때까지 대기
- 모든 트랜잭션이 완료되면, 데이터베이스를 닫고, 인스턴스 디스마운트, 인스턴스 종료 순

$ sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

본문

sql>define_editor='vi'
 

본문

window server 2003 + msssql 2000 에서 서버의 Administrator 암호를 바꾸고 난 다음에 sql server 를 실행시키면 "서비스가 로그온 실패로 인해 시작되지 않았습니다" 와 같은 에러가 나면서 실행이 안되는 경우가 있다.

시작->관리도구->서비스->MSSQLSERVER

속성탭에서 로그인을 보면 계정지정에서 암호를 바꾼 암호로 수정해주면 에러가 해결된다.

본문

update  /*+  bypass_ujvc  */
(
select
addr.ADDRCODE1  addrADDRCODE1,
addr.ADDRCODE2  addrADDRCODE2,
addr.ADDRCITY  addrADDRCITY,
kd.ADDRCODE1  kdADDRCODE1,
kd.ADDRCODE2  kdADDRCODE2,
kd.ADDRCITY  kdADDRCITY

from KAPA_APPR_DATA kd,ADDRESS_INFO addr
where
kd.ADDRGU=addr.ADDRGU and kd.ADDRDONG=addr.ADDRDONG and kd.ADDRRI is null and addr.ADDRRI is null

)
set
kdADDRCODE1=addrADDRCODE1,
kdADDRCODE2=addrADDRCODE2,
kdADDRCITY=addrADDRCITY

본문

편리한 기능이다.

using 뒤에는 테이블이나 select 한 값들이 올 수 있다.

merge into APART_LAND_INFO_TB
using dual
on (APTSN=:APTSN)
when matched then
 update set
 LANDCATEGORY=:LANDCATEGORY,
 LANDAREA=:LANDAREA,
 LANDUSE=:LANDUSE,
 LANDUSESTATE=:LANDUSESTATE,
 LANDRNF=:LANDRNF,
 LANDSHAPE=:LANDSHAPE,
 LANDDIRECTION=:LANDDIRECTION,
 ROADCONTACT=:ROADCONTACT
when NOT matched then 
  INSERT
  (APTSN,LANDCATEGORY,LANDAREA,LANDUSE,LANDUSESTATE,LANDRNF,LANDSHAPE,LANDDIRECTION,ROADCONTACT) VALUES
  (:APTSN,:LANDCATEGORY,:LANDAREA,:LANDUSE,:LANDUSESTATE,:LANDRNF,:LANDSHAPE,:LANDDIRECTION,:ROADCONTACT)

본문

이전에 mysql에서 작업하던 버릇인지 계속해서 날짜를 unixtime으로 다룬다.

유닉스 타임으로 된 날짜 컬럼은

convert(varchar(10), DATEADD(hh, 9, DATEADD(ss, 유닉스타임으로 된 날짜컬럼, '1970-1-1')), 120)

와 같이 사용하면 '2007-01-01' 의 형태로 반환되어 사용할 수 있다. group by 나 where 절에

요즘은 이렇게 적어놓지 않음 잊어먹는다.. 나이가 들어가는겐가...