■ SEQUENCE
- 자동일련번호를 생성하는 객체이다.
- SEQUENCE객체를 생성하려면 CREATE SEQUENCE 시스템권한이 있어야 한다.
SELECT * FROM session_privs; -- 나한테 권한이 있는가
<SYS SESSION>
GRANT CREATE SEQUENCE TO hr; -- 시스템에서 권한부여
<->
# SEQUENCE 생성
CREATE SEQUENCE id_seq;
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';
└ view
<SYS SESSION>
SELECT * FROM dba_sequences WHERE sequence_name = 'ID_SEQ';
└ 딕셔너리 테이블 뷰
SELECT * FROM seq$;
<->
CREATE TABLE hr.seq_test(id number, name varchar2(30), day date);
# sequence이름.nextval : 사용가능한 번호를 리턴하는 가상 칼럼
INSERT INTO hr.seq_test (id, name, day) VALUES (id_seq.nextval,'홍길동',sysdate);
SELECT * FROM hr.seq_test;
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';
INSERT INTO hr.seq_test (id, name, day) VALUES (id_seq.nextval,'박찬호',sysdate);
SELECT * FROM hr.seq_test;
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';
ROLLBACK;
# sequence이름.currval : 현재 사용한 번호를 리턴하는 가상컬럼
SELECT id_seq.currval FROM dual;
# sequence번호는 갭이 생길 수 있다.
INSERT INTO hr.seq_test (id, name, day) VALUES (id_seq.nextval,'홍길동',sysdate);
SELECT * FROM hr.seq_test;
SELECT id_seq.currval FROM dual;
INSERT INTO hr.seq_test (id, name, day) VALUES (id_seq.currval,'박찬호',sysdate);
SELECT * FROM hr.seq_test;
SELECT id_seq.nextval FROM dual; -- 조회만으로도 번호 생성 됨
INSERT INTO hr.seq_test (id, name, day) VALUES (id_seq.nextval,'손흥민',sysdate);
SELECT * FROM hr.seq_test;
ROLLBACK;
# SEQUENCE 삭제
DROP SEQUENCE id_seq;
# SEQUENCE 생성
CREATE SEQUENCE id_seq
START WITH 1 -- 기본값 : 1 시작번호, 생성 후 수정불가
MAXVALUE 3 -- 기본값 : 10^27 = POWER(10,27)
INCREMENT BY 1 -- 기본값 : 1 증가
NOCYCLE -- 기본값
NOCACHE -- 기본값 : CASHE 20
;
INSERT INTO hr.seq_test (id, name, day) VALUES (id_seq.nextval,'홍길동',sysdate);
INSERT INTO hr.seq_test (id, name, day) VALUES (id_seq.nextval,'박찬호',sysdate);
INSERT INTO hr.seq_test (id, name, day) VALUES (id_seq.nextval,'손흥민',sysdate);
INSERT INTO hr.seq_test (id, name, day) VALUES (id_seq.nextval,'이문세',sysdate);
SELECT * FROM hr.seq_test; -- └오류 : MAX 3 + NOCYCLE -> 4번 생성이 안됨
-- 오류 : MAXVALUE까지 수행되어서 더이상 SEQUENCE번호를 생성 할 수 없다.
COMMIT;
-- 정보 확인
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';
# SEQUENCE 수정
- START WITH는 생성 후 수정할 수 없다.
ALTER SEQUENCE id_seq
MAXVALUE 10;
-- 정보 확인
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';
INSERT INTO hr.seq_test (id, name, day) VALUES (id_seq.nextval,'이문세',sysdate);
SELECT * FROM hr.seq_test;
UPDATE hr.seq_test
set id = id_seq.nextval
WHERE id = 4; -- 4번일경우 다음 번호로 수정
COMMIT;
SELECT * FROM hr.seq_test;
■ SYNONYM(동의어)
- 긴객체 이름을 짧은 이름으로 사용하는 객체
- SYNONYM을 생성하려면 CREATE SYNONYM 시스템권한이 있어야 한다.
SELECT * FROM session_privs;
-- PRIVILEGE : CREATE SYNONYM
CREATE TABLE hr.emp_copy_2023
AS
SELECT * FROM hr.employees;
SELECT * FROM emp_copy_2023;
# SYNONYM 생성 -- 실제 사용은 흔지않다.
CREATE SYNONYM ec2 FOR hr.emp_copy_2023;
SELECT * FROM user_synonyms WHERE table_name = 'EMP_COPY_2023';
SELECT * FROM ec2;
# SYNONYM 삭제
DROP SYNONYM ec2;
SELECT * FROM user_synonyms WHERE table_name = 'EMP_COPY_2023';
DROP TABLE hr.emp PURGE;
DROP TABLE hr.dept PURGE;
GRANT SELECT ON hr.employees TO insa;
GRANT SELECT ON hr.departments TO insa;
SELECT * FROM user_tab_privs;
-- HR입장에서의 받은 권한, 부여한 권한
<INSA SESSION>
DROP TABLE emp PURGE;
SELECT * FROM user_tab_privs;
SELECT * FROM hr.employees;
SELECT * FROM hr.departments;
<->
SELECT * FROM session_privs;
<SYS SESSIOLN>
# CREATE PUBLIC SYNONYM -- 보편적.
- 모든 유저들이 사용할 수 있는 SYNONYM을 생성하는 시스템권한
- PUBLIC SYNONYM으로 생성된 SYNONYM을 사용하려면 그 SYNONYM에 연결된 객체에 대한 권한이 있어야지만
SYNONYM을 사용 할 수 있다.
GRANT CREATE PUBLIC SYNONYM TO hr;
SELECT * FROM dba_sys_privs WHERE grantee = 'HR';
<->
SELECT * FROM session_privs;
CREATE PUBLIC SYNONYM emp FOR hr.employees;
SELECT * FROM user_synonyms; -- 만들었는데 없다? PUBLIC SYNONYM은 이곳에서 안보인다.
SELECT * FROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner = 'HR';
- 특정 유저의 테이블만 검색해서 보는 습관을 가지자 ★
SELECT * FROM EMP;
<INSA SESSION>
SELECT * FROM user_tab_privs;
SELECT * FROM hr.employees;
SELECT * FROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner = 'HR';
SELECT * FROM emp; -- 동명의 테이블이 있다면 우선순위는 내소유 테이블이 먼저이다.
<->
REVOKE SELECT ON hr.employees FROM insa;
-- 권한 회수
SELECT * FROM user_tab_privs;
SELECT * FROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner = 'HR';
<INSA SESSION>
SELECT * FROM emp;
-- 오류 : emp synonym과 연결되어있는 employees 테이블 객체 권한이 없어 사용 불가
SELECT * FROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner = 'HR';
# PUBLIC SYNONYM 삭제
DROP PUBLIC SYNONYM emp; -- 오류 : insufficient privileges. 권한 불충분
- DROP PUBLIC SYNONYM 시스템 권한이 별도로 있는 유저만 사용할 수 있다.
<SYS SESSION>
GRANT DROP PUBLIC SYNONYM TO hr;
SELECT * FROM dba_sys_privs WHERE grantee = 'HR';
<->
SELECT * FROM session_privs;
-- DROP PUBLIC SYNONYM
DROP PUBLIC SYNONYM emp;
SELECT * FROM all_synonyms WHERE table_owner = 'HR';
■ 날짜 타임
SELECT
sysdate, -- SERVER 시간정보
systimestamp,
current_date, -- CLIENT 시간정보
current_timestamp,
localtimestamp
FROM dual;
# 현재 접속한 지역의 TIMEZONE 수정
ALTER SESSION SET TIME_ZONE = '+08:00';
ALTER SESSION SET TIME_ZONE = '+09:00';
DATE : 년월일 : SYSDATE, CURRENT_DATE
- 기존에 날짜 시간 초 정보는 년월일시분초.5자리
TIMESTAMP(9) : 년월일시분초.9자리 : localtimestamp
TIMESTAMP(9) WITH TIME ZONE : 년월일시분초.9자리 TIMEZONE : systimestamp, current_timestamp
TIMESTAMP(9) WITH LOCAL TIME ZONE : 년월일시분초.9자리 : 보는 지역에 따라 날짜 시간 정보를 자동으로 정규화해서 보여준다.
INTERVAL YEAR TO MONTH : 기간을 나타내는 날짜 타입, 년수, 개월수
INTERVAL DATE TO SECOND : 기간을 나타내는 날짜 타입, 일수, 시분초.9자리
CREATE TABLE hr.time_test(
a date,
b timestamp with time zone,
c date,
d timestamp with time zone,
e timestamp,
f timestamp with local time zone);
desc hr.time_test
INSERT INTO hr.time_test(a,b,c,d,e,f)
VALUES(sysdate,systimestamp,current_date,current_timestamp,localtimestamp,localtimestamp);
COMMIT;
SELECT * FROM hr.time_test;
ALTER SESSION SET TIME_ZONE = '+9:00';
SELECT * FROM hr.time_test;
# to_date
문자를 날짜형(date)으로 변환하는 함수
SELECT to_date('20231023','yyyymmdd') FROM dual;
SELECT to_date('2023-10-23 13:45:30','yyyy-mm-dd hh24:mi:ss') FROM dual;
# to_timestamp
문자를 날짜형(timestamp)으로 변환하는 함수
SELECT to_timestamp('2023-10-23 13:45:30','yyyy-mm-dd hh24:mi:ss') FROM dual;
SELECT to_timestamp('2023-10-23 13:45:30.123456','yyyy-mm-dd hh24:mi:ss.ff') FROM dual;
# to_timestamp_tz
문자를 날짜형(timestamp with time zone)으로 변환하는 함수
SELECT to_timestamp_tz('2023-10-23 13:45:30.123456 +09:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm') FROM dual;
# to_yminterval('00-00') ('년-개월')
문자를 날짜형(INTERVAL YEAR TO MONTH)으로 변환하는 함수, + - 계산 가능
SELECT to_yminterval('10-2') FROM dual;
SELECT sysdate, sysdate + to_yminterval('10-00') FROM dual; -- 10년 0개월
SELECT sysdate, sysdate - to_yminterval('10-00') FROM dual;
SELECT sysdate, sysdate + to_yminterval('00-04') FROM dual;
SELECT sysdate, sysdate - to_yminterval('00-1') FROM dual;
# to_dsinterval('00 00:00:00') ('일수 시:분:초')
문자를 날짜형(INTERVAL DAY TO SECONDS)으로 변환하는 함수, + - 계산 가능
SELECT current_timestamp, localtimestamp + to_dsinterval('100 10:00:00') FROM dual; -- 100일 10시간
SELECT current_timestamp, localtimestamp - to_dsinterval('100 10:00:00') FROM dual;
CREATE TABLE hr.time_test_2 (
a interval year(3) to month, -- 기본 2자리
b interval day(3) to second); -- 기본 2자리
INSERT INTO hr.time_test_2(a,b) VALUES (to_yminterval('10-2'), to_dsinterval('100 10:00:00'));
SELECT * FROM hr.time_test_2; -- 형변환 함수를 꼭 사용하자.
INSERT INTO hr.time_test_2(a,b) VALUES ('10-11', '50 10:30:00');
SELECT * FROM hr.time_test_2;
INSERT INTO hr.time_test_2(a,b) VALUES (to_yminterval('10-12'), to_dsinterval('100 10:00:00'));
-- 오류 : 년수 개월수를 함께 사용할 때는 11개월까지만 입력해야 한다. '0-12' -> '1-0'
ROLLBACK;
#날짜계산
날짜 + 숫자(일수) = 날짜
날짜 - 숫자(일수) = 날짜
날짜 - 날짜 = 숫자(일수)
날짜 + 시/24 = 날짜 시간
날짜 + 분/(24*60) = 날짜 시간
날짜 + 초/(24*60*60) = 날짜 시간
날짜 + 날짜 = 오류
날짜 + interval year to month = 날짜
날짜 + interval day to second = 날짜
- 시/24
- 분/(24*60) , 분/1440
- 초/(24*60*60), 초/86400
# TO_CHAR
SELECT to_char(sysdate, 'yyyy mm dd hh24 mi ss') FROM dual;
■ EXTRACT
날짜형을 숫자형으로 추출하는 날짜 함수
SELECT to_char(sysdate, 'yyyy'), extract(year from sysdate) FROM dual;
└ 문자(왼쪽정렬) └ 숫자(오른쪽정렬)
SELECT to_char(sysdate, 'mm month mon'), extract(month from sysdate) FROM dual;
SELECT to_char(sysdate, 'dd dy'), extract(day from sysdate) FROM dual;
SELECT to_char(sysdate, 'hh24 hh12'), extract(hour from localtimestamp) FROM dual;
└ sysdate에서는 시간정보가 없는걸로 인식
SELECT to_char(sysdate, 'mi'), extract(minute from localtimestamp) FROM dual;
SELECT to_char(sysdate, 'ss'), extract(second from localtimestamp) FROM dual;
SELECT to_char(current_timestamp, 'tzh'), extract(timezone_hour from current_timestamp) FROM dual;
SELECT to_char(current_timestamp, 'tzm'), extract(timezone_minute from current_timestamp) FROM dual;
[문제] 각 사원의 last_name, hire_date 및 근속 연수를 출력하는 query를 작성합니다.
사원의 근속 연수가 5년 이상인 경우 '5 years of service'를 출력합니다.
사원의 근속 연수가 5년 이상인 경우 '10 years of service'를 출력합니다.
사원의 근속 연수가 5년 이상인 경우 '15 years of service'를 출력합니다.
어떠한 조건과도 일치하지 않을 경우 'maybe next year!'를 출력합니다.
단, 근속 연수 출력은 case, to_yminterval을 사용하세요.
SELECT last_name, hire_date, trunc(months_between(sysdate,hire_date)/12) 근속연수,
CASE
WHEN sysdate - to_yminterval('15-00') >= hire_date THEN '15 years of service'
WHEN sysdate - to_yminterval('10-00') >= hire_date THEN '10 years of service'
WHEN sysdate - to_yminterval('05-00') >= hire_date THEN '5 years of service'
ELSE 'maybe next year!'
END awards
FROM hr.employees;
■ 분석함수
그룹함수(칼럼) OVER(옵션: partition by 칼럼 order by 칼럼)
그룹함수의 값을 각 행에 표시
SELECT trunc(avg(salary))
FROM hr.employees; -- 값 1건
SELECT employee_id, salary, trunc(avg(salary))
FROM hr.employees; -- 오류 : GROUP BY 사용필요
SELECT employee_id, salary,
sum(salary) over() 합 -- 전체합 : sum() over()
avg(salary) over() 평균, -- 전체평균 : avg() over()
max(salary) over() 최대값, -- 최대값 : max() over()
min(salary) over() 최소값, -- 최소값 : min() over()
median(salary) over() 중앙값, -- 중앙값 : median() over()
variance(salary) over() 분산, -- 분산 : variance() over()
stddev(salary) over() 표준편차, -- 중앙값 : stddev() over()
count (*) over() 개수, -- 개수 : count () over()
FROM hr.employees;
SELECT employee_id, salary, department_id,
sum(salary) over() 전체합,
sum(salary) over(order by employee_id) 누적합 -- order by
sum(salary) over(partition by department_id) 부서별합, -- partition by
sum(salary) over(partition by department_id order by employee_id) 부서별누적합 -- partition by + order by
FROM hr.employees;
[문제] 자신의 부서평균급여보다 더 많이 받는 사원의 employee_id, salary, department_name 출력해주세요.
-- 1. 조인(107건) 후 값(38건) 가져오기
SELECT e.employee_id, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
AND e.salary > (SELECT avg(salary)
FROM hr.employees
WHERE department_id = e.department_id);
↓ 성능업
-- 2.조인 줄이기 // 값(38건)가져오고 해당 부서이름만 가져오기
SELECT e.employee_id, e.salary, (SELECT department_name
FROM hr.departments
WHERE department_id = e.department_id) dept_name
FROM hr.employees e
WHERE e.salary > (SELECT avg(salary)
FROM hr.employees
WHERE department_id = e.department_id);
↓ 성능업
-- 3. 인라인뷰 // 부서아이디별 평균 >> 사원과 비교(38건) >> 해당 부서이름 가져오기
단점 employees 테이블을 두번이나 가져옴 >> 분석함수 사용하자
SELECT e2.employee_id, e2.salary, (SELECT department_name
FROM hr.departments
WHERE department_id = e2.department_id) dept_name -- 38건에 대한 부서이름
FROM (SELECT department_id, avg(salary) avg_sal
FROM hr.employees
GROUP BY department_id) e1, hr.employees e2
WHERE e1.department_id = e2.department_id
AND e2.salary > e1.avg_sal;
-- 4. <최종> 사원정보 테이블 안에서 CASE비교하여 True가져오기 >> 부서이름 가져오기
SELECT employee_id, salary, (SELECT department_name
FROM departments
WHERE department_id = e.department_id) dept_name
FROM (SELECT employee_id, salary, department_id,
CASE WHEN salary > avg(salary) over(partition by department_id) THEN 1 END case_sal
FROM hr.employees) e
WHERE e.case_sal = 1;
-- ME
SELECT e.employee_id, e.salary, d.department_name
FROM (SELECT employee_id, salary, department_id, trunc(avg(salary) over(partition by department_id)) dept_avg
FROM hr.employees) e, hr.departments d
WHERE e.department_id = d.department_id
AND e.salary > e.dept_avg;| ★비교는 CASE 밖에 없다 ★ SELECT employee_id, salary, (SELECT department_name FROM departments WHERE department_id = e.department_id ) dept_name FROM (SELECT employee_id, salary, department_id, CASE WHEN salary > avg(salary) over(partition by department_id) THEN 1 END case_sal FROM hr.employees ) e WHERE e.case_sal = 1; |
■ TOP-N
예) 최고급여자중에 10위까지만 출력?
1) 내림차순
SELECT employee_id, salary
FROM hr.employees
ORDER BY salary desc;
2) 정렬한 결과집합을 이용해서 10위까지 출력
# rownum : fetch 번호를 리턴하는 '가상'칼럼.
-- salary 칼럼의 값이 중복성이 없을 경우에는 사용 가능
-- 중복성이 있을 경우는 절대 사용하면 안된다.
SELECT rownum, employee_id, salary
FROM (SELECT employee_id, salary
FROM hr.employees
ORDER BY salary desc)
WHERE rownum <= 10;
-- 아래 코드로는 절대 작성하면 안된다.
-->랜덤하게 데이터 10건만 추출해서 정렬한다.
SELECT rownum, employee_id, salary
FROM hr.employees
WHERE rownum <= 10
ORDER BY salary desc;
★ RANK() : 순위를 구하는 함수, 동일한 순위가 있을경우 다음 순위의 갭이 생긴다.
★ DENSE_RANK() : 순위를 구하는 함수, 동일한 순위가 있더라도 연이은 순위를 구한다.
SELECT
employee_id,
salary,
rank() over(order by salary desc) rank,
dense_rank() over(order by salary desc) dense_rank
FROM hr.employees;
SELECT *
FROM (SELECT
employee_id,
salary,
rank() over(order by salary desc) rank1
FROM hr.employees)
WHERE rank1 <= 10;
SELECT *
FROM (SELECT
employee_id,
salary,
dense_rank() over(order by salary desc) rank2
FROM hr.employees)
WHERE rank2 <= 10;
SELECT
employee_id,
salary,
department_id,
rank() over(partition by department_id order by salary desc) rank,
dense_rank() over(partition by department_id order by salary desc) dense_rank
FROM hr.employees;
# SEQUENCE
- CREATE SEQUENCE 이름 : 시스템권한
- sequence이름.nextval : 사용가능한 번호를 리턴하는 가상 칼럼
- sequence이름.currval : 현재 사용한 번호를 리턴하는 가상컬럼
- 조회만으로도 번호 생성되기에 갭이 발생할 수 있다.
# SEQUENCE 삭제
DROP SEQUENCE id_seq;
# SEQUENCE 생성
CREATE SEQUENCE id_seq
START WITH 1 -- 기본값 : 1 시작번호, 생성 후 수정불가
MAXVALUE 3 -- 기본값 : 10^27 = POWER(10,27)
INCREMENT BY 1 -- 기본값 : 1 증가
NOCYCLE -- 기본값
NOCACHE -- 기본값 : CASHE 20
# SEQUENCE 수정
- START WITH는 생성 후 수정할 수 없다.
ALTER SEQUENCE id_seq
MAXVALUE 10;
# 정보확인
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';
# 날짜 타임
sysdate, -- SERVER 시간정보
systimestamp,
current_date, -- CLIENT 시간정보
current_timestamp,
localtimestamp
DATE : 년월일 : SYSDATE, CURRENT_DATE
- 기존에 날짜 시간 초 정보는 년월일시분초.5자리
TIMESTAMP(9) : 년월일시분초.9자리 : localtimestamp
TIMESTAMP(9) WITH TIME ZONE : 년월일시분초.9자리 TIMEZONE : systimestamp, current_timestamp
TIMESTAMP(9) WITH LOCAL TIME ZONE : 년월일시분초.9자리 : 보는 지역에 따라 날짜 시간 정보를 자동으로 정규화해서 보여준다.
INTERVAL YEAR TO MONTH : 기간을 나타내는 날짜 타입, 년수, 개월수
INTERVAL DATE TO SECOND : 기간을 나타내는 날짜 타입, 일수, 시분초.9자리
to_date('20231023','yyyymmdd')
문자를 날짜형(date)으로 변환하는 함수
to_timestamp('2023-10-23 13:45:30','yyyy-mm-dd hh24:mi:ss')
문자를 날짜형(timestamp)으로 변환하는 함수
to_timestamp_tz('2023-10-23 13:45:30.123456 +09:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm')
문자를 날짜형(timestamp with time zone)으로 변환하는 함수
to_yminterval('10-02')
문자를 날짜형(INTERVAL YEAR TO MONTH)으로 변환하는 함수, + - 계산 가능. ('년-개월')
to_dsinterval('100 10:00:00')
문자를 날짜형(INTERVAL DAY TO SECONDS)으로 변환하는 함수, + - 계산 가능. ('일수 시:분:초')
# EXTRACT
날짜형을 숫자형으로 추출하는 날짜 함수
extract(year from sysdate)
extract(month from sysdate)
extract(day from sysdate)
extract(hour from localtimestamp)
extract(minute from localtimestamp)
extract(second from localtimestamp)
extract(timezone_hour from current_timestamp)
extract(timezone_minute from current_timestamp)
# 분석함수
그룹함수(칼럼) OVER(옵션: partition by 칼럼 order by 칼럼)
전체합 : sum() over()
전체평균 : avg() over()
최대값 : max() over()
최소값 : min() over()
중앙값 : median() over()
분산 : variance() over()
중앙값 : stddev() over()
개수 : count () over()
# SQL에서 비교는 CASE 문 사용
# 순위
RANK() : 순위를 구하는 함수, 동일한 순위가 있을경우 다음 순위의 갭이 생긴다.
DENSE_RANK() : 순위를 구하는 함수, 동일한 순위가 있더라도 연이은 순위를 구한다.
'Oracle SQL' 카테고리의 다른 글
| 14 PL/SQL Variable, CASE, LOOP (0) | 2023.10.25 |
|---|---|
| 13-1 SAVEPOINT (0) | 2023.10.24 |
| 11 Unique, Check, Not Null, Rename, Flashback, Truncate, Comment, View (0) | 2023.10.20 |
| 10 Subquery, Merge, Primary Key, Foreign Key (0) | 2023.10.19 |
| 09-2 권한, 테이블, INSERT, UPDATE, DELETE (0) | 2023.10.18 |