- 프로그래밍 언어: 절차적, 변수, 제어문, 반복문, 예외처리 가능
- SQL 언어: 선언적 언어, 결과 중심 언어, 변수 사용 불가, 제어문, 반복문, 예외 처리 가능.
- PLSQL: SQL을 절차적 언어로 처리한다.
- SELECT에서 제어, 반복, 복잡한 연산, 포맷 변경 등을 수행해주는 함수를 사용하면 더 강력하게 SQL을 사용할 수 있다.
predefine함수: user, sysdate,
plsql로 작성하는 사용자 정의 함수: create or replace function is begin return ….
DB의 함수 특성은 반드시 결과 하나를 리턴한다.
- Single Row Function : 1ROW에 대해서 함수를 적용하면 결과 1 리턴
- Multiple Row Function (Group Function) : 1개 이상의 그룹핑된 ROW들에 함수가 적용되어 결과 1 리턴
Character Function
Number Function
Date Function
Conversion Function
General Function
함수안에 함수를 nested(중첩)해서 사용 가능하다.
- 함수의 단순한 결과나 단순 연산의 결과를 DB로부터 얻고자 할 때
From절에 dual 테이블 사용, sys소유의 dual테이블은 public 으로 모든 DB유저가 사용할 수 있도록 만들어 놓은 테이블
Column 구별 à describe DUAL , desc dual
SELECT CHR(67)||CHR(65)||CHR(84) "Dog" //(""는 alias 값)
FROM DUAL;
SELECT lower ('hello world'), upper('hello world') // 소문자, 대문자 변경
FROM dual;
SELECT late_name lower(late_name), upper(late_name) //
FROM employees;
SELECT initcap('hello world'), initcap('HELLO WORLD')
from dual;
select length('대한민국'), lengthb('대한민국')
from dual;
SELECT instr('hello world','o'), instr('Hello world','o',6) // STR, 찾는문자, X번 위치부터 검색
from dual;
SELECT SUBSTR('hello world',2,8), SUBSTR('Hello world',-5,3), SUBSTR('Hello world',6) // 2~8번째 까지 문자, -는 뒤에서부터 앞으로 검색.
from dual;
SELECT ltrim('xyxyhello', 'xy') // 결과 hello , 왼쪽 xy문자 전부 제거
from dual;
SELECT rtrim('Oracle Serverkkkkkk', 'k') // 결과 oracle server, 중간에 있는 값 삭제는 못함, 뒤에 k만 전부 삭제.
from dual;
SELECT length(' Oracle '), length(trim(' oracle ')) 결과 12, 6, 공백을 전부 삭제한 결과를 보인다.
from dual;
SELECT lpad(1000,10,'*'), rpad(30000, 10, '#') 결과 ******1000 (숫자입력, 길이 공간, 공간에 적용된만큼 * 가 들어간다, 왼쪽/오른쪽)
FROM dual;
select concat('oracle', 'Server') // 두개의 값을 추가하는 것.
from dual;
select replace('jack and jue','j','bl') // 문자열의 j를 bl로 바꾼다.
from dual;
select round(42.795,2), round(42.795,0), round(42.795), round(42.795,-1) // 2,3는 그 뒷자리에서 반올림, 자릿수 공백이면 정수(integer), -1은 첫째자리에서 내림
from dual;
select ceil(42.795) // 올림(소수이하 삭제,정수리턴) select floor(42.795) // 내림(소수이하 삭제, 정수리턴), select mod(1000, 700) // 나머지
- 날짜함수
Select sysdate from dual; è os의 현재 날짜, 시간
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; è 날짜 시간 세션 변경 // 결과 : 13/07/03 -> 2013-07-03 10:27:31
날짜 + 정수(number of day)
날짜 – 정수
날짜 + 정수/24
날짜 – 날짜
Select sysdate, sysdate+1
from dual;
Select sysdate, sysdate-1
from dual;
Select sysdate, sysdate+1/24
from dual;
Select sysdate-hire_date // 근무근속 시간 나타낸다.
from employees;
current_date, current_timmestamp è 세션의 timezone을 기준으로 시간을 리턴
select sessiontimezone from dual;
alter session set time_zone = '+3:00'; // 3시간추가
Select sysdate, current_date, current_timestamp
from dual;
è 결과 : 2013-07-03 10:35:08 2013-07-03 04:35:08 13/07/03 04:35:08.000000000 +03:00
alter session set time_zone = '+9:00'; // 9시간추가
2013-07-03 10:35:08 2013-07-03 10:35:08 13/07/03 10:35:08.000000000 +09:00
select add_months(sysdate,3) // sysdate에서 3개월 뒤.
from dual;
select months_between(sysdate, hire_date) // HIRE_DAT값과 비교 (간격)
from employees;
alter session set nls_date_format='RRRR/MM/DD';
select last_day('2002/02/01'), last_day('2004/02/15') // 2월 마지막날 확인
from dual;
select NEXT_DAY(sysdate,'일') "NEXT DAY" // 해당 날짜 확인하기
from dual;
select round (to_date('13/7/20'),'DAY') // 해당날을 반올림 7/21 (day는 수요일 기준, 나라마다 기준이 다르다)
from dual;
select trunc (to_date('13/7/20'),'MONTH') // 절삭. (내림) 결과 7/01
from dual
- Conversation 함수
암시적 형 변환 : 오라클 서버가 자동 변환 (할당)
명시적 형 변환 : TO_XXXX 함수를 사용해서 변환
select '10'+'10' // ORACLE에서는 문자를 자동 형변환, 결과 20
select '10'||'10' // 결과 1010
from dual;
to_Date ('날짜 구성 문자열', '날짜 포맷형식') : 문자열 à 날짜
to_number('숫자구성문자열','숫자 포맷형식') : 문자열 à 숫자
to_char('날짜|숫자') : 날짜 또는 숫자 à 문자열
select to_date('13-7월-1', 'RR-MM"월"-DD') // 결과 2013/07/01
from dual;
select to_number('$123,456,000', '$999,999,999') // 숫자변환
from dual
select hire_date, to_char(hire_date,'MM"월"DD"일"') //해당 날 리턴
from employees;
- General Function
Null 관련 함수
Select empno, ename, sal, comm, (sal+comm)*12 // null값을 제외해서 계산한다.
From emp;
Null 컬럼은 산술 연산이 불가하므로 null값을 null이 아닌 값으로 변환해줘야 한다.
Nvl(컬럼, 값);
If 컬럼 is null then return 값
Else if 컬럼 is not null then return 컬럼
Select empno, ename, sal, comm, (sal+nvl(comm,0))*12 // null값을 포함해 계산한다.
From emp;
Nvl2(컬럼, 값1, 값2) : 값1, 값2는 동일한 타입이어야 합니다.
If 컬럼 is null then return 값2
Else if 컬럼 is not null then return 값1
Select empno, ename, sal, comm, nvl2(comm, 'o', 'x') "is Comm" // null확인
From emp;
Select nullif('ABC', 'ABC'), nullif('ABC', 'DBC') // Nullif(expr1, expr2) : exp1과 exp2이 동일하면 null 리턴 동일하지 않으면 expr1 리턴
From dual;
Coalesce (Expr1, expr2, expr3, expr4, ….., exprN) // NULL을 순차적으로 하나씩 체크하는데 NULL이 아니면 값 리턴.
Select Coalesce (null, null, 1 , null, null), Coalesce (null, null, null, null), Coalesce (null, null, null, null, 'A'), Coalesce (null, 100, null, null, 'A')
From dual;
- 조건 처리 함수 : decode
emp 테이블에서 deptno컬럼이 10이면 월급 5%인상,20이면 월급 10%인상,30이면 월급 15%인상하고 다른 deptno컬럼값은 월급 인상 없습니다.
현재 월급과 인상된 월급을 검색 결과로 생성
Select empno, ename, deptno, sal,
decode(deptno, 10, sal*1.05, 20, sal*1.1, 30, sal*1.5) "INCREASE" // 위 조건을 넣은 부분
From Emp
order by deptno;
<SQL3 표준 구문과 같다>
Select empno, ename, deptno, sal,
case deptno when 10 then sal*1.05
when 30 then sal*1.1
when 30 then sal*1.5
else sal end "INCREASE"
from Emp
order by deptno;
문> emp 테이블에서 sal컬럼값으로 기준으로 TAX를 계산합니다.
sal 컬럼값이 1000미만이면 세금 0
sal 컬럼값이 2000미만이면 세금 sal의 5%
sal 컬럼값이 3000미만이면 세금 sal의 10%
sal 컬럼값이 4000미만이면 세금 sal의 15%
sal 컬럼값이 4000이상이면 세금 sal의 20%
(empno, ename, sal, TAX)
select distinct empno, ename, sal,
decode(ceil(sal/1000), 0, sal, 1, sal*0.05, 2, sal*0.10, 3, sal*0.15, sal*0.2) "TAX"
from emp
order by sal;
select empno, ename, deptno, sal,
case when sal < 1000 then 0
when sal < 2000 then sal*0.05
when sal < 3000 then sal*0.1
when sal < 4000 then sal * 0.15
else sal*0.2 end "TAX"
from emp
order by sal;
연습문제
1번 select sysdate "DATE" from dual;
2번 select employee_id, last_name, salary,
ceil(salary),salary*1.15 "New Salary"
from employees;
4번 select employee_id, last_name, salary,
ceil(salary),salary*1.15 "NewSalary",
ceil(increase),salary*1.15-salary "increase"
from employees;
7번
select last_name || 'earns ' || to_char(salary, '$999,999.00')
|| 'monthly but wants' || to_char(salary*3, '$999,999.00') "DREAM"
from employees;
- multiple row function
사원 테이블에서 전체 사원의 월급의 평균, 합계, 최대값 , 최소값
sum, avg, min, max, count, variance(분산), stddev(표준편차)
select sum(salary), avg(salary), min(salary), max(salary), variance(salary), stddev(salary) // 결과 합, 평균, 최소, 최대,분산,표준편차
from employees;
** sum, avg, variance, stddev à number 컬럼 대상
select min(last_name), max(last_name)
from employees;
** min, max à 모든 컬럼 대상
Select count(*), count(commission_pct), count(distinct department_id)
From employees;
// * 전체는 row수, commission _PCT는 NULL이 아닌 값, 중복되지 않은 값 만
** Count à 모든 컬럼 대상
Select avg(commission_pct), sum(commission_pct)/count(*)
From employees;
è 그룹함수는 NULL값을 무시한다. (함수적용X)
Select avg(nvl(commission_pct,0)), sum(commission_pct)/count(*)
è 널값을 0으로 지정하여 계산한다.
-- 각 부서별로 월급의 합계와 평균을 검색
select department_id, sum(salary), avg(salary)
from employees
group by department_id;
select department_id, sum(salary), avg(salary)
from employees; è 오류
***select절에서 그룹함수를 적용하지 않은 컬럼은 반드시 group by절에 선언해주어야 합니다.
-- 각 부서별로 그룹핑하고 부서내에 직무별로 그룹핑해서 월급의 합계와 평균을 검색하시오.
select nvl(department_id,0), job_id, sum(salary), avg(salary)
from employees
group by department_id, job_id
order by job_id;
-- 각 부서별 평균 월급이 8000 이상인 부서와 평균 월급만
select department_id, avg(salary)
from employess
where avg(salary)>8000
group by department_id;
è 오류난다!! where절에는 필터 조건 (대상 레코드만 선택하는 조건)
********그룹함수에 대한 조건은 having절에 선언합니다
(수정)-- 각 부서별 평균 월급이 8000 이상인 부서와 평균 월급만
select department_id, avg(salary)
from employees
group by department_id
having avg(salary)>8000;
(page 262)
-- 4번 ~ 12번까지
select max(salary) "MAXIMUM", min(salary) "MINIMUM", sum(salary) "SUM", avg(salary) "Average"
from employees;
-- 5번
select job_id, max(salary) "MAXIMUM", min(salary) "MINIMUM", sum(salary) "SUM", avg(salary) "Average"
from employees
group by job_id
order by 1;
-- 6번
select job_id, count(job_id)
from employees
group by job_id;
--7번
select count(distinct manager_id) "NUMBER OF MANAGERS"
from employees;
--8번
select max(salary)-min(salary) "DIFFERENCE"
from employees;
--9번 관리자 번호 및
select manager_id, min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary)>6000;
-- 10번
--select department_name, location_id, count(department_name), salary
--from departements
-- 11번
select count(employee_id) "TOTAL",
-- sum(decode(to_char(hir_date,'yyyy'),'1995',1)) "1995"
sum(decode(substr(hire_date,1,4),'1995',1)) "1995",
sum(decode(substr(hire_date,1,4),'1996',1)) "1996",
sum(decode(substr(hire_date,1,4),'1997',1)) "1997",
sum(decode(substr(hire_date,1,4),'1998',1)) "1998"
from employees;
/*
sum(case to_char(hire_date,'yyyy') when '1995' then 1 end) "1995"
sum(case to_char(hire_date,'yyyy') when '1996' then 1 end)) "1996",
위와 동일하다.
*/
-- 12번
select job_id,
sum(decode(department_id,'20',salary)) "dept20",
sum(decode(department_id,'50',salary)) "dept50",
sum(decode(department_id,'80',salary)) "dept80",
sum(decode(department_id,'90',salary)) "dept80",
sum(salary) "Total"
from employees
group by job_id
order by 1;
select employee_id, last_name, department_id, department_name
from employees, departments;
è 조인 결과는 카티션 프로덕트
select employee_id, last_name, employee_id, employees.department_id, department_name
from employees, departments;
à 조인시에 공통 컬럼에 대해서는 소유자인 테이블 명을 반드시 선언해야 한다.
전부다 소유자를 써줘야 효율이 좋다.
select s.employee_id, s.last_name, s.employee_id, s.department_id, b.department_name
from employees s, departments b;
원인 : 조인조건을 생략, 조인 조건을 잘못 선언, 시뮬레이션 하기 위해서.
조인 조건 : 오라클 문법은 where 절에 선언.
- 표준SQL(1999문법)에서는 FROM절에 JOIN 키워드를 사용해서 조인 구문 추가.
select s.employee_id, s.last_name, s.employee_id, s.department_id, b.department_name
from employees s, departments b
where a.department_id = b.department_id;
select s.employee_id, s.last_name, s.employee_id, s.department_id, b.department_name
from employees s join departments b on (s.department_id = b.department_id); // 위와 같다.
