Database/ORACLE

SELECT문 활용(대체 변수,함수,형변환)

타카스 류지 2012. 11. 1. 10:49

대체변수

대체변수는 사용하고자 하는 값을 입력하고자 할때 쓰는 변수이다

사용 형태는 '&변수이름'형식으로 사용한다. 자세한 사용방법은 EX)로 나타낸다

EX)와 같이 입력을 하고 실행을 시킬경우

'ENTER value for 변수이름: '식의 문장이 생인가

여기서 입력을 하게되면 &로 입력했던 문장의 자리에 입력한 값이 들어가서 실행이 되게 된다.

EX1)

SELECT employee_id, last_name, job_id, &column_name

FROM employees

WHERE &condition

ORDER BY &order_column

// 첫번재 & 값은 출력하고자 하는 컬럼의 값이고

두번쩨는 조건을 입력하는 곳이다

세번째는 정렬을 하고자 하는 기준을 입력하는 곳이다.

단일행함수

단일행 함수란 출력하는 문자에 적용되는 함수이다.

이름

사용형태

사용예)

CONCAT

두가지문자를 한곳에 출력하는 함수

CONCAT('hello','world') = helloworld

SUBSTR

문자에서 정해진 위치의 3가지 문자를 출력하는 삼수

SUBSTR('helloworld',1,5) = hello

LENGTH

문자의 길이를 출력하는 함수

LENGTH('helloworld') = 10 // 문자의 길이 수

INSTR

문자가 있는 위치를 출력하는 함수

INSR('helloworld','w') = 6 // 문자가 있는 위치

LPAD

공간을 정의하고 남은 공간에 임의의 문자를 입력하는 함수(왼쪽에)

LPAD(salary,10,'*') = *****24000

RPAD

공간을 정의하고 남은 공간에 임의의 문자를 입력하는 함수(오른쪽에)

RPAD(salary,10,'*') = 24000*****

REPLACE

임의의 문자를 지정하는 문자로 바꾸는 함수

REPLACE('JACK and JUE','J','BL') = BLACK and BLUE

TRIM

지정한 문자를 문장에서 제외하고자 하는 함수

TRIM('h' FROM 'helloworld' ) = elloworld

UPPER

컬럼이나, 문자를 모두 대문자로 나타내는 함수

UPPER('hello')=HELLO

LOWER

컬럼이나,문자를 모두 소문자로 나타내는 함수

LOWER('HELLO')=hello

Q1)employees테이블에서 last_name이 4글자인 사원의 모든 정보를 출력하시오

SELECT *

FROM employees

WHERE LENGTH(last_name)=4

Q2) employees테이블에서 last_name에 k를 포함한 사원의 풀네임을 출력하시오.(last_name+first_name)

숫자관련 함수

숫자에 관련되어 사용하는 함수

ROUND(45.926,2) = 45,93 // 반올림

TRUNC(45.926,2) = 45.92 // 버림

MOD(1600,300) = 100 // 나머지

숫자

1

2

3

.

4

5

6

반올림/버림 지정 숫자

-3

-2

-1

0

1

2

자리수

100의자리

10의자리

1의자리

소수점1자리

소수점2자리

소수점3자리

날짜관련 함수

함수이름

기능

활용방법

SYSDATE

현재의 날짜를 출력

MONTHES BETWEEN

두가지 날짜의 기간을 출력

NEXT_DAY

다음년도를 출력

LAST_DAY

전년도를 출력

ROUND

날짜를 반올림하여 나타냄

(반올림기준 : 6월이상,15일 이상)

ROUND (SYSDATE,'MONTH') =

/현재의 달를 일기준 반올림

(15일전이면 현재의 달,이후는 다음달)

ROUND ( SYSDATE,'YEAR') =

/현재의 날짜를 달도기준으로 반올림

(6월전이면 현재년도 1월1일,

이후는 내년 1월1일 출력)

TRUNC

날짜를 버림하여 나타냄

위와 비슷하지만 선자를 따름

형변환

데이터 베이스 저장 시 저장하는 데이터의 유형을 조정하는 것

함수이름

기능

to_NUMBER()

문자였던 것을 숫자로 빠꿔준다.

to_VARCHAR2()=to_CHAR()

숫자였던것을 문자로 바꿔준다.

to_DATE()

문자였던 것을 날짜로 변경한다.

NUMBER()

()안에 숫자의 크기를 입력한다(입력함수)

VARCHAR2(20)

20글자 이상은 넘지않게 입력한다.

char(20)

무조건 20글자를 채워서 입력

YYYY

Y의 갯수만큼 년도 표현

YEAR

4자리를 모두 표현

MONTH

날짜 이름 (풀네임)

MON

월을 3자리로 표현

MM

월을 숫자로 표현

DY

3자리 요일

DAY

요일 이름

DD

숫자료 표현

Q1) employees 테이블에서 입사일이 1990-12-31 이전에 입사한 사원의 last_name과 hire_date를 출력

(단, last_name = 대문자 , hire_date = 1990-12-31형식)

Q2) employees테이블에서 근무년수가 15년 이상인 사원의 성명(last_name || first_name)을 출력하세요

Q3) employees테이블에서 사번, 연봉(salary*12)을 천의자리에서 반올림한 값을(&200,000.00)형태로 출력하되 사번순으로 오름차순 하시오

Q4) employees테이블에서 last_name과first_name의 길이가 같은 사원의 department_id,last_name 길이

first_name길이, last_name 길이 + first_name길이를 1의 자리에서 반올림한 값, job_id를 department_id 순으로 내림차순 하여 정렬하여라

GENERAL FUNCTION

함수이름

활용형태

기능

NVL

NVL(컬럼값,바꿀값)

지정된 컬럼에서 null값을 지정하는 숫자로 바꾸는 함수

NVL(컬럼,변경하고자하는 숫자)

NVL2

NVL2(컬럼값,값1,값2)

컬럼이 NULL이 아니면 값1로, NULL이면 값2로 바꾸어 활용

NULLIF

NULLIF(값1,값2)

값1과 값2가 같으면 null값을 주고, 다르면 값1을 준다

COALESCE

COLESCE(값1,값2,값3...값N)

값들을 확인하여 NULL값이 안나오면 그값을 출력한다.

Q1)employees 테이블에서 employee_id, 연봉을 출력하시오(연봉 = salary*12+salary*12*commission_pct / 연봉표현방식 $1,500.00)

NVL2 활용

SELECT last_name, salary, commission_pct,
NVL2(commission_pct,'SAL+COMM','SAL') income
FROM employees
WHERE department_id IN(50,80);

NULLIF 활용

SELECT first_name,LENGTH(first_name)"expr1",
last_name, LEGTH(last_name)"expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employee

employees테이블에서 사원의 first_name,first_name의 길이,last_name,last_name의 길이와

last_name의 길이와first_name의 길이가 같으면 null을 다르면 first_name을 출력하라