[펌] 유용한 오라클 쿼리 정리

칼럼 갯수 가져오기 쿼리

select count(*) from table이름;

테이블의 엔티티의 유형 가져옴
desc table이름;

값 삽입하기
insert into customer values('111-11','박종훈','서울');

테이블 내의 모든 addr 엔티티의 값을 바꿈
update customer set addr='room';

특정 엔티티의 값만 바꾸기(primary key 는 update 안됨)
update customer set addr='룸싸롱' where ssm='333-333';

특정 컬럼 삭제하기(delete 는 커밋을 안찍어 주면 지워도 다시 살아난다)
delete from customer where name='정용민';

테이블내의 모든 값 삭제할때
delete from customer;

디비에서 커밋만 찍지 않으면 살아나므로 살아나게 할때 rollback해줌
rollback;

droptable 엔터치면 무조건 롤백 안되고 다 날아감...주의...

자바에서 jdbc 연결
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcle817","scott","tiger");

디비 연결 후 select * from customer;실행 시 Statement에 반듯이 sql 문을 Statement 로 감싸서 작업을 한다
Statement 은 어플리케이션과 어플리케이션 연결 되는 세션 개념....

excuteQuery 는 select 에만 사용한다
st.executeQuery("select * from customer");

ResultSet 은 테이블의 값들을 가리킴....가리키는 곳은 가장 첫번째의 위를 가리킴
Iterator 과 유사..

JDBC 에서 ResultSet 에서 next 로 돌면서 검사시 getString(1) 부터 가져옴..

오라클 드라이버를 메모리에 올리는 역할을 한다.
Class.forName("oracle.jdbc.driver.OracleDriver");

Connection, Statement, ResultSet 는 인터페이스다....객체 생성 안됨...
실행하려면 이걸 impement 하여 구현한 subclass 의 객체 만들어서 실행됨
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcle817","scott","tiger");
Statement st= con.createStatement();
ResultSet rs = st.executeQuery("select * from customer");

처음 프로그램 실행 시 실행되는 순서
static 변수 메모리 올라감
static 초기화구문 실행 - 오라클 드라이버 객체 생성,
static 메소드 메모리에 올라감
JVM 이 메인실행

close() 는 finally 에 모두 구현한다...(try, catch, finally)

try catch 안하고 throws 해서 그냥 던지는 이유...try catch 에 적어줄 대체 로직이
없을 경우.

-- 무조건 올림
SELECT CEIL(13.11) FROM DUAL;

 

-- 반올림 
SELECT ROUND(345.123, 0), ROUND(345.123,2), ROUND(345.123, -1) FROM DUAL;

 

-- 나머지 
SELECT MOD(23,5) FROM DUAL;

 

-- 승수값
SELECT POWER(3,2), POWER(3, -2) FROM DUAL;

 

-- 버림값
SELECT TRUNC(345.123, 1), TRUNC(345.123, 0), TRUNC(345.123, -1) FROM DUAL;

 

-- IF + THEN 1 ELSEIF 0 THEN 0 ELSE -1
SELECT SIGN(5.342), SIGN(0), SIGN(-2334) FROM DUAL;
SELECT DECODE( SIGN(POWER(2,10)-1000), 1, '2의 10승이 1000보다 크다', 
-1, '2의 10승이 1000보다 작다', '2의 10승이 1000이다') 비교답 FROM DUAL;

 

-- ASCII <--> CHAR
SELECT CHR(65) "CHR", ASCII('A') "ASCII" FROM DUAL;

 

-- 대소문자 변환
SELECT LOWER('My name is KIMJINDOO') "LOWER", UPPER('My name is kimjindoo') "UPPER" FROM DUAL;

 

-- 자릿수 맞춰 채우기
SELECT LPAD('DALMA', 10, '*') "LPAD", RPAD('DALMA', LENGTH('DALMA')+11, '@dalcom.net') "RPAD" FROM DUAL;
SELECT LPAD('1234567890', 20, '+') || RPAD('1234567890', 20, '^') "1234567890" FROM DUAL;
SELECT LPAD('1,234,567', 30, ' ') "LPAD 사용으로 30자리 맞춤", '1,234,567' "단순문자 사용", 
1234567 "단순숫자 사용" FROM DUAL;

 

-- 공백 제거 
SELECT REPLACE(LTRIM('    AAA    '), ' ', '0') "LTRIM", 
REPLACE(RTRIM('    AAA    '), ' ', '0') "RTRIM" FROM DUAL;

 

-- 문자열 치환 
SELECT REPLACE('ORACLE', 'A', 'a') "REPLACE" FROM DUAL;
SELECT EMP_NAME, REPLACE(EMP_NAME, '이', '박') "이->박" FROM PERSONNEL WHERE EMP_NAME LIKE '이%';

 

-- 문자열의 일부만을 취하기 
SELECT SUBSTR('ORACLE 프로젝트', 1, 3) SUBSTR1, SUBSTR('오라클 프로젝트', 4, 5) SUBSTR2,
SUBSTR('오라클 PROJECT', 10) SUBSTR3 FROM DUAL;
SELECT SUBSTRB('ORACLE 프로젝트', 2, 3) SUBSTRB1, SUBSTRB('오라클 프로젝트', 4, 5) SUBSTRB2,
SUBSTRB('오라클 PROJECT', 10) SUBSTRB3 FROM DUAL;

-- 문자열의 길이 
SELECT EMPNO, LENGTH(EMPNO), LENGTHB(EMPNO), EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME) FROM 
PERSONNEL WHERE EMPNO>'98102'
SELECT LENGTH('가나다') "LENGTH", LENGTHB('가나다') "LENGTHB" FROM DUAL;

-- LANGUAGE : KOREAN_KOREA.KO16KSC5601
SELECT LENGTH('학교') FROM DUAL;   RESULT: 2
SELECT LENGTHB('학교') FROM DUAL;  RESULT: 4

-- LANGUAGE : AMERICAN_AMERICA.US7ASCII
SELECT LENGTH('학교') FROM DUAL;   RESULT: 4

-- 문자열에서 특정 문자나 문자열의 위치 INSTR[B](string1, [char||string], start_position, n_counted)
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 INSTR('하늘 아래 하늘이 또 있겠는가.', '하늘', 1, 2) 하늘1,INSTRB('하늘 아래 하늘이 또 있겠는가.', '하늘', 1, 2) 하늘2 FROM DUAL;

 

-- 문자열 병합 (||)

다음 쿼리문은 SP_GDS, SP_COMP_CODE, MALL_DESC를 '|'를 구분자로 하여 병합한다.

SELECT DISTINCT(SP_GDS) || '|' || SP_COMP_CODE || '|' || MALL_DESC
FROM TCA_SP_ACMP
WHERE SP_TRD_DTIME BETWEEN TO_DATE('20070101', 'YYYYMMDD') AND 
TO_DATE('20070131', 'YYYYMMDD') AND SP_COMP_CODE='0210';

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

 

-- 주어진 날짜가 속하는 월의 마지막 날짜
SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') "LAST_DAY" FROM DUAL;

 

-- 주어진 두 날짜간의 기간을 월 단위로 계산  
-- MONTHS_BETWEEN(date1, date2)  : IF date1 > date2  THEN +
SELECT MONTHS_BETWEEN('2006/12/26', '2007/01/25') "MONTHS_BETWEEN(-)",
MONTHS_BETWEEN('2006/01/13', '2005/11/13') "MONTHS_BETWEEN(+)" FROM DUAL;
SELECT ROUND(MONTHS_BETWEEN('2006/12/28', '2006/12/01'), 1) FROM DUAL;

 

-- 월단위 계산
SELECT ADD_MONTHS(SYSDATE, 12) "ADD_MONTHS(+)", ADD_MONTHS(SYSDATE, -12) "ADD_MONTHS(-1)" FROM DUAL;

 

-- 주어진 날짜를 기준으로 주어진 요일이 처음 오는 날짜
-- 일요일:1 ~ 토요일:7
SELECT SYSDATE, NEXT_DAY(SYSDATE, '일요일') "NEXT_DAY 1", NEXT_DAY(SYSDATE, 1) "NEXT_DAY 2" FROM DUAL;

-- NUMBER와 DATE를 문자타입으로 변환
SELECT TO_CHAR(123456789) "NUMBER", TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') "DATE" FROM DUAL;

 

-- 문자를 숫자로 변환
SELECT TO_NUMBER('123456789') "TO_NUMBER1",
TO_NUMBER('123,456.9', '999,999.9') "TO_NUMBER2",
TO_NUMBER('1,234,567', '9G999G999') "TO_NUMBER3" FROM DUAL;

 

-- 문자형 데이타를 DATE형으로 변환
SELECT TO_DATE('20020824', 'YYYYMMDD') "TO_DATE1",
TO_DATE('2002-08-24', 'YYYY-MM-DD') "TO_DATE2", 
TO_DATE('200208', 'YYYYMM') FROM DUAL;

 

-- 평균값 구하기
SELECT AVG(HEIGHT), AVG(WEIGHT) FROM PERSONNEL;

 

-- 최대값 최소값
SELECT MAX(EMPNO), MAX(EMP_NAME), MIN(EMPNO), MIN(EMP_NAME) FROM PERSONNEL;

 

-- 합계 구하기
SELECT SUM(WEIGHT) FROM PERSONNEL;

 

-- 조회 범위의 조회 건수
SELECT COUNT(*), COUNT(EMPNO), COUNT(JIKCH_CODE) FROM PERSONNEL;

 

-- NULL 값 치환
SELECT EMPNO, EMP_NAME, HOBBY 취미, NVL(WELL, '없음') 특기 
FROM PERSONNEL WHERE EMPNO BETWEEN '98001' AND '98005';

 

-- 연속 조건문
-- DECODE(a, b, c, d)  a가 b면 c고, 아니면 d
-- DECODE(a, b, c, d, e, f, g, h ....) a가 b면 c고, d면 e고, f면 g고 h면 ...
-- DECODE(a, b, c, DECODE(e, f, g, h)) 
SELECT EMPNO, EMP_NAME, DECODE(HT_CODE, '1', '현재원', '2', '휴직원', '퇴사') HT_CODE
FROM PERSONNEL WHERE EMPNO BETWEEN '98071' AND '98080';

 

-- 오라클 환경변수 값 구하기
SELECT USERENV('LANGUAGE') "LANGUAGE", USERENV('TERMINAL') "TERMINAL", 
USERENV('SESSIONID') "SESSIONID" FROM DUAL;

 

-- 주어진 데이타중 최대값 최소값 구하기
SELECT GREATEST(132,33 ,45,99,22, 32, 77, 12) GREATEST, LEAST(132,33 ,45,99,22, 32, 77, 12) FROM DUAL;
SELECT GREATEST('가', '나', '다', '라', '마') GREATEST1, GREATEST('가', '나', '다', '라', '마', '마마') GREATEST2, 
LEAST('가', '나', '다', '라', '마') FROM DUAL;

 

-- UID, USER
SELECT UID, USER FROM DUAL;
 
오라클 SQL - DATATYPES 오라클  
2006/09/23 00:15

http://blog.naver.com/besttoys/130009102326
 
SQL은 대부분의 DATABASE management System에서 쓰는 언어다. 기본적으로 "하나나 여러개의 Table에서 이거 저거 조건을 만족하는 값을 찾아라" (우리 회사에서 7만불 이상 벌면서 토론토에 사는 인간을 찾아라) 뭐 이런데 쓰이는 거다. (찾기만 하는것이 아니고 쓰기도 한다) SQL도 여러 가지가 있으나, 하나 확실한건 나같은 머리 나쁜 사람도 쓰기 편하다는 것이다. 왜냐면 다른 언어같이 복잡한 loop이나 뭐 그런게 없어서 어찌하면 빨리 돌아갈까 하는 걱정을 안해도 되기 때문이다. (쫌 복잡해 지면 걱정 해야도긴 한다) 사실 어떤 DATABASE system 이냐에 따라 SQL이 좀 틀린데 
알다시피 DATABASE 란 DATA를 가지고 있는 테이블들의 모임이다. 그리고 그테이블 들은 행과 열의 모임이다. (Row, Column) 그리고 하나의 데이타는 제각기 그나름데로의 성격을 가지고 있다. 어떤 데이타는 숫자고 어떤 데이타는 Character다. 대부분 한 열은 같은 성질의 데이타를 가지고 있다. 오늘은 데이타 타입에 대해 알아볼까 한다.

 

CATEGORY            Datatypes

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

Character          CHAR, NCHAR, VARCHAR2, NVARCHAR2

Number             NUMBER

Long and Raw     LONG, LONG RAW, RAW

Date and Time     DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE,

                            TIMESTAMP WITH LOCAL TIME ZONE, INTERNAL YEAR TO MONTH,

                             INTERVAL DAY TO SECONDS

Large object        CLOB, NCLOB, BCLOB, BFILE

Row ID                 ROWID, UROWID

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

뭐 다 설명하긴 너무 길고 한꺼번에 이해하기도 헤깔리니까 대강 필요하다 싶은거만 하나씩 보자....

 

CHAR

Fixed-length alphanumeric string이다. 뭔소리냐, 길이가 정해져 있는 숫자와 문자란 말이다. 최소 1 byte 부터 최고 2000 bytes 까지 넣을수 있고 그리고 정해진 길이가 안되면 space로 알아서 채워진다. CHAR의 default size는 1이다.

VCHAR2

Variable-length alphanumeric string이다. 뭐 대강 감 잡았듯이 길이가 정해져 있지 않단 말이다. 4000 bytes 까지 가능하고 default size가 없어서 항상 size를 지정해 주어야 한다. 나머지는 CHAR 하고 별다른게 없다.

 

여기서 잠깐, 두개를 예들 통해 비교해 보자.

CHAR datatype:     'YO' = 'YO     '   두번쨰 데이타 뒤에있는 space가 무시된다.

VCHAR2 datatype: 'YO' < 'YO    '  두번째 데이타 뒤에있는 space가 인정되 사이즈가 더크다.

 

NUMBER (p,s)

NUMBER는 숫자로만 된 데이타다. 그옆에 있는 p 는 presicion의 약자고 s 는 scale의 약자다. (이게 없으면 최대값이 자동으로 지정된다. p는 데이타 전체 크기, 1부터 38까지고, s는 소숫점 이후 크기, -84부터 127까지다. 설명하면 너무 길고 복잡하니 예를 들어보자.

 

NUMBER (5,2) - 이 열에 들어갈수 있는 수는 -999.99 부터 999.99다. 소숫점 이후 최대숫자길이가 2, 전체 길이가 5. 그러무로 정수부분의 길이는 3이다. (5-2) 만약 이 열에는 정수만 들어 간다면 999가 최대값이고 -999가 최소값이 될것이다.

NUMBER (4,2) - 여기에 들어갈수 있는 최대최소의 수는 99.99 에서 -99.99지만 만약 소숫점 이하의 자릿수가 크면 주어진 싸이즈에 맞춰 반올림을 한다. 예를 들어 12.125 를 집어 넣으면 12.13 이라고 입력되는 것이다. 그런데 수가 너무 커 p값을 넘어가면 에러가 난다. 예를 들어 123.1을 넣으려 하면 이 열의 최대최소값의 범위를 벗어남으로 에러가 난다는 말이다. 기억나는가 바로 위 문단에서 말했던  p - s = 정수자릿수!!!!! 아님면 소숫점 이하 0의 갯수

말이 너무 많다. 예제나 보자.

 

Value           Datatype            StoredValue              

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

123.2564           NUMBER                   123.2564
1234.9876          NUMBER(6,2)            1234.99
12345.12345       NUMBER(6,2)               Error
123456              NUMBER(6,2)               Error
1234.9876          NUMBER(6)                 1235
123456.1            NUMBER(6)                123456

12345.345          NUMBER(5,-2)            12300

1234567             NUMBER(5,-2)            1234600
12345678           NUMBER(5,-2)             Error
123456789          NUMBER(5,-4)            123460000
1234567890         NUMBER(5,-4)            Error
12345.58             NUMBER(*, 1)            12345.6           -  * 은 기본값 38이다
0.1                    NUMBER(4,5)             Error
0.01234567          NUMBER(4,5)             0.01235
0.09999              NUMBER(4,5)             0.09999
0.099996             NUMBER(4,5)             Error

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

아하!!! 좀 머리가 깨끗해 진다. 역시 예제가 최고다!!!! 혹시 잘 이해가 안되신다면 p-s는 정수자릿수 아님 소숫점 이하 0의 갯수만 생각하면서 다시 한번 천천히 해보시길...

 

DATE

말그데로 날짜를 나타네는 데이타타입이다. 날짜에는 년도, 달, 날짜, 시간, 분 그리고 초가 포함된다. 오라클에서는 보통 순서가 DD-MON-YY 00:00:00이다.

원문 : pixyman.tistory.com/226