CREATE SEQUENCE sequence_name
[START WITH n]
[INCREMENT by n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
create sequence depts_seq
start with 91
increment by 1
maxvalue 100
nocycle
nocache;
2. 시퀀스 확인
- USER_SEQUENCES 데이터 사전 뷰에서 시퀀스 값을 검사
- LAST_NUMBER 열은 다음 이용 가능한 시퀀스 번호 출력
select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;
3. NEXTVAL과 CURRVAL
- NEXTVAL은 다음 사용 가느한 시퀀스 값을 반환
- 시퀀스가 참조될 때마다 사용자에게 유일한 값을 반환
- CURRVAL이 현재 시퀀스 값 반환
- CURRVAL이 참조되기 전에 NEXTVAL 이 먼저 이용되어야 함
4. 시퀀스 사용
insert into depts(deptno, dname, loc)
values (depts_seq.NEXTVAL, 'MARKETING', 'SAN DIEGO');
select * from depts;
select depts_seq.currval
from dual;
5. 시퀀스 수정
ALTER SEQUENCE sequence_name
[INCREMENT by n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
alter sequence depts_seq
maxvalue 99999;
6. 시퀀스 삭제
- DROP SEQUENCE 문장을 사용하여 데이터 사전에서 시퀀스 제거
- 한 번 제거되었다면 시퀀스는 더는 참조될 수 없음.
drop sequence sequence_name;
인덱스
- 인덱스는 테이블이나 클러스터에서 쓰이는 선택적인 객체
- DB 테이블에서 원하는 레코드를 빨리 찾아갈 수 있음
1. 인덱스 생성
- 자동 : 유일인덱스는 테이블 정의 primary key 또는 unique 제약조건 정의 시 자동 생성
- 수동 : 사용자는 행에 대한 엑세스 시간을 줄이기 위해 열에서 유일하지 않은 인덱스 생성 가능
CREATE [UNIQUE | BITMAP] INDEX index_name
ON table_name(col1, col2);
create index emps_first_name_idx
on emps(first_name);
2. 인덱스 삭제
DROP INDEX emps_first_name_idx;
동의어
- 객체를 위한 대체 이름 제공
CREATE [PUBLIC] SYNONYM [schema.]synonym
FOR [schema.]object;
- 같은 데이터를 다른 뷰로 나타낼 수 있으므로 같은 데이터에 대해 동시에 여러 사용자의 다른 응용프로그램 요구를 지원해줌.
2. 단순 뷰와 복합 뷰
특징
단순 뷰
복합 뷰
테이블 수
하나
둘 이상
함수 포함
없음
있음
데이터 그룹 포함
없음
있음
뷰를 통한 DML
있음
없음
*뷰에 관한 정보
SELECT * FROM USER_VIEWS;
뷰 생성, 데이터 검색, 수정, 삭제
1. 뷰 생성 권한
select * from user_role_privs; --현재 사용자의 롤
select * from user_sys_privs; -- 현재 사용자에게 주어진 권한
2. 뷰 생성
- CREATE VIEW 문장 내에서 서브쿼리 작성
- 서브쿼리는 조인 구문 포함 가능
- CREATE VIEW 절에서 열 별칭은 서브쿼리에서의 열과 같은 순서로 나열됨.
CREATE [OR REPLACE]
[FORCE | NOFORCE]
VIEW view_name[(alias[, alias ...])]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY]
1) 뷰 생성
create view emp_view_dept60
as select employee_id, first_name, last_name, job_id, salary
from employees
where department_id=60;
select * from emp_view_dept60;
2) 뷰 생성 서브쿼리에서 별칭 사용
create view emp_dept60_salary
as select
employee_id as empno,
first_name || ' ' || last_name as anme,
salary as monthly_salary
from employees
where department_id=60;
create view emp_dept60_salary (empno, name, monthly_salary)
as select
employee_id,
first_name || ' ' || last_name,
salary
from employees
where department_id=60;
select * from emp_dept60_salary;
3. 뷰 질의
select * from user_views;
4. 뷰 수정
- CREATE OR REPLACE VIEW 절을 사용하여 뷰를 수정
- ALTER 사용X
CREATE OR REPLACE VIEW veiw_name...
create or replace view emp_dept60_salary
as select
employee_id as empno,
first_name||' '||last_name as name,
job_id job,
salary
from employees
where department_id=60;
desc emp_dept60_salary;
5. 복합 뷰 생성
- 두 개 이상 테이블로부터 값을 출력하는 뷰를 생성
create view emp_view
as select
e.employee_id as id,
e.first_name as name,
d.department_name as department,
j.job_title as job
from employees e
left join departments d on e.department_id=d.department_id
join jobs j on e.job_id=j.job_id;
select * from emp_view;
6. 뷰 삭제
- 뷰는 데이터베이스에서 기본 테이블을 기반으로 하므로 데이터 손실 없이 삭제 가능
DROP VIEW view_name;
뷰를 이용한 DML 연산
1. 뷰에서 DML 연산 실행 규칙
- 단순 뷰에서 DML 연산 수행가능
- 뷰가 다음을 포함한다면 행 제거 불가능(그룹함수, GROUP BY, DISTINCT)
- 뷰가 다음을 포함한다면 데이터 수정 불가능(행제거불가표현, 표현식, ROWNUM)
- 뷰가 다음을 포함한다면 데이터 추가 불가능(행제거불가조건, 수정불가조건, NOTNULL)
create table emps as select * from employees;
1) 데이터 조작이 가능한 경우
- 단순 뷰에서 DML 연산수행 가능
create or replace view emp_dept60
as select * from emps where department_id=60;
delete from emp_dept60 where employee_id=104;
select * from emps where employee_id=104;
2) 행 제거가 안 되는 경우
create or replace view emp_dept60
as select distinct * from emps where department_id=60;
delete from emp_dept60 where employee_id=60;
3) 데이터 수정이 안 되는 경우
create or replace view emp_dept60
as select
employee_id,
first_name||' '||last_name as name,
salary*12 as annual_Salary
from emps where department_id=60;
update emp_dept60 set annual_salary=annual_salary*1.1
where employee_id=106;
4) 데이터 입력이 안 되는 경우
create or replace view emp_dept60
as select
employee_id,
first_name,
last_name,
email,
salary
from emps where department_id=60;
insert into demp_dept60
values(500,'JinKyoung','Heo', 'HEOJK',8000);
2. WITH CHECK POINT
- 뷰에 대한 DML연산이 뷰의 조건을 만족할 때만 수행하게 함
create or replace view emp_dept60
as select employee_id, first_name, hire_Date, salary, department_id
from emps
where department_id=60
with check option;
update emp_dept60
set department_id=10
where employee_id=105;
3. WITH READ ONLY
- DML 연산을 수행할 수 없게 함
- DML 수행 시 에러 발생
create or replace view emp_dept60
as select employee_id, first_name, hire_Date, salary, department_id
from emps
where department_id=60
with read only;
delete from emp_dept60
where employee_id=105;
인라인 뷰(Inline View)
- SQL 구문에서 사용 가능한 별칭(상관 이름)을 사용하는 서브 쿼리
- 기본질의의 FROM 절에 명명된 서브쿼리를 사용하는 것과 유사
- 객체가 아님.
select row_number, first_name, salary
from ( select first_name,salary,
row_number() over(order by salary desc) as row_number
from employees)
where row_number between 1 and 10;
- commit/rollback으로 명시적으로 종료하거나 DDL/DCL 로 자동커밋되어 종료될 수 있음
문장
설명
COMMIT
미결정 데이터를 영구적으로 변경. 현재 트랜잭션 종료
SAVEPOINT savepoint_name
현재 트랜잭션 내에 savepoint 표시
ROLLBACK [[TO [SAVEPOIT]]] savepoint_name]
모든 미결정 데이터 변경을 버림. 현재의 트랜잭션 종료.
1. 암시적 트랜잭션 종료
* 자동 커밋
- DDL 문장 완료
- DCL 문장 완료
- 명시적 COMMIT / ROLLBACK 없이 SQL plus정상 종료할 때
* 자동 롤백
- SQL plus 비정상적 종료시
- 시스템 실패 시
create table emp_temp as select * from employees;
delete emp_temp where department_id=20;
savepoint svpnt_del_20;
delete emp_temp where department_id=50;
savepoint svpnt_del_50;
delete emp_temp where department_id=60;
rollback to savepoint svpnt_del_50;
CREATE TABLE emp1 AS SELECT * FROM employees;
SELECT count(*) from emp1;
create table emp2 as select * from employees where 1=2;
select count(*) from emp2;
INSERT
INSERT INtO ( col1, col2,...)
VALUES (val1, val2, ...)
- col과 val은 순서를 맞춰야 함.
- 열 이름이 생략될 경우 모든 값을 다 넣어야 함.
- 값을 넣고 싶지 않은 부분은 null을 넣음
1. 테이블 구조 확인
DESC tablename;
2. 새로운 행 삽입
- 각각의 열에 대한 값을 포함하는 새로운 행 삽입
- 테이블에 있는 열의 티폴트 순서로 값 나열
- INSERT 절에서 열을 선택적으로 나열
- 문자와 날짜 값은 단일 따옴표 내에 둠.
INSERT INTO departments
VALUES(280, 'Data Analytics', null, 1700);
=>실행을 취소하려면 ROLLBACK; 사용(커밋했을 때로)
3. 다른 테이블로부터 행 복사
- 서브쿼리로 INSERT 문장 작성
- VALUES 절 사용X
- 서브쿼리의 열 수와 INSERT절의 열 수는 일치해야 함.
INSERT INTO table(col1, col2, ...)
select_sub_query;
CREATE TABLE MANAGERS As
select * from employees where 1=2;
insert into managers
select * from employees where job_id like '%MAN';
UPDATE
- update 문장으로 기존의 행 갱신
- 하나 이상의 행을 갱신 가능
create table emps as select * from employees;
alter table emps
add(constraint emps_emp_id_ppk primary key(employee_id),
constraint emps_manager_id_fk foreign key(manager_id)
references emps(employee_id));
1. 테이블 행 갱신
select employee_id, first_name, salary
from emps
where employee_id=103;
update emps
set salary=salary*1.1
where employee_id=103;
2. 서브쿼리로 다중 열 갱신
UPDATE table
SET (col1, col2, ...) =
(SELECT col1, col2, ...
FROM table
WHERE condition)
WHERE condition;
update emps
set(job_id, salary, manager_id) =
(select job_id, salary, manager_id
from emps
where employee_id = 108)
where employee_id=109;
DELETE
DELETE [FROM] table
[WHERE condition];
1. 행 삭제
delete from emps
where employee_id = 104;
delete from emps
where emplyee_id=103;
-- 104번 사원의 매니저는 103이므로 제약조건때문에 삭제되지 않음.
2. 다른 테이블을 이용한 행 삭제
create table depts as
select * from departments;
delete from emps
where department_id=
(select department_id
from depts
where department_name='Shipping');
--45개 행 이(가) 삭제되었습니다.
3. RETURNING
- DML 조작 시 특정 값을 임시로 저장할 수 잇음
- Oracle 10g에 추가
variable emp_name varchar2(50);
variable emp_sal number;
variable;
delete emps
where employee_id=109
returning first_name, salary into :emp_name, :emp_sal;
select * from emps where employee_id=109;
print emp_name;
print emp_sal;
MERGE
- INSERT 문과 UPDATE 문을 동시에 사용할 수 있습니다.
- Oracle 9i에 추가되었습니다.
MERGE INTO table[alias]
USING (target | view | subquery) [alias]
ON(join_condition)
WHEN MATCHED THEN
UPDATE SET col1=val1
WHEN NOT MATCHED THEN
INSERT(column_lists) VALUES(value_lists);
create table emps_it as select * from employees where 1=2;
merge into emps_it a
using (select * from employees where job_id='IT_PROG') b
on (a.employee_id = b.employee_id)
when matched then
update set
a.phone_number =b.phone_number,
a.hire_date=hire_date,
a.job_id = b.job_id,
a.salary=b.salary,
a.commission_pct = b.commission_pct,
a.manager_id = b.manager_id,
a.department_id = b.department_id
when not matched then
insert values
(b.employee_id, b.first_name, b.last_name, b.email,
b.phone_number, b.hire_date, b.job_id, b.salary,
b.commission_pct, b.manager_id, b.department_id);
Multiple INSERT
INSERT [ALL | FISRT]
[[WHEN condition1 THEN]
INTO [table1] VALUES(col1, col2, ...)]
[[WHEN condition2 THEN]
INTO [table2] VALUES(col1, col2, ...)]
[ELSE
INTO [table3] VALUES (col1, col2)]
1. UNCONDITIONAL INSERT ALL
- 조건과 상관없이 기술된 여러 개의 테이블에 데이터 입력
insert all
into emp1
values (300, 'Kildong', 'Hong', 'KHONG', '011.624.7902',
TO_DATE('2015-05-11','yyyy-mm-dd'), 'IT_PROG', 6000, null, 100, 90)
into emp2
values (400, 'Kilseo', 'Hong', 'KSHONG', '011.324.7952',
TO_DATE('2015-06-20','yyyy-mm-dd'), 'IT_PROG', 5500, null, 100, 90)
SELECT * FROM dual;
create table emp_salary as
select employee_id, first_name, salary, commission_pct
from employees
where 1=2;
create table emp_hire_date as
select employee_id, first_name, hire_Date, department_id
from employees
where 1=2;
insert all
into emp_salary values
(employee_id, first_name, salary, commission_pct)
into emp_hire_date values
(employee_id, first_name, hire_date, department_id)
select * from employees;
--218개 행 이(가) 삽입되었습니다.
2. CONDITIONAL INSERT ALL
- 특정 조건들을 기술하여 그 조건에 맞는 행들을 원하는 테이블에 난어 삽입
create table emp_10 as select * from employees where 1=2;
create table emp_20 as select * from employees where 1=2;
insert all
when department_id=10 then
into emp_10 values
(employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id)
when department_id=20 then
into emp_20 values
(employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id)
select * from employees;
--3개 행 이(가) 삽입되었습니다.
select * from emp_10;
select * from emp_20;
3. CONDITIONAL INSERT FIRST
- 첫번째 when 절에서 조건을 만족하는 경우 다음의 when절은 수행하지 않음.
create table emp_sal5000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sa10000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sal15000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sal20000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sal25000 as
select employee_id, first_name, salary from employees where 1=2;
insert first
when salary <=5000 then
into emp_sal5000 values(employee_id, first_name, salary)
when salary <=10000 then
into emp_sal10000 values(employee_id, first_name, salary)
when salary <=15000 then
into emp_sal15000 values(employee_id, first_name, salary)
when salary <=20000 then
into emp_sal20000 values(employee_id, first_name, salary)
when salary <=25000 then
into emp_sal25000 values(employee_id, first_name, salary)
select employee_id, first_name, salary from employees;
--109개 행 이(가) 삽입되었습니다.
select count(*) from emp_sal5000; --49
select count(*) from emp_sal10000; --45
select count(*) from emp_sal15000; --12
select count(*) from emp_sal20000; --2
select count(*) from emp_sal25000; --1
4. PIVOTING INSERT
- 여러개의 into절을 사용하지만 그 뒤에 오는 테이블은 모두 동일
- 비관계형DB를 관계형DB로 바꿀 때 사용
insert all
into sales_log
values(employee_id, week_id, 'SALES_MON', sales_mon)
into sales_log
values(employee_id, week_id, 'SALES_TUE', sales_tue)
into sales_log
values(employee_id, week_id, 'SALES_WED', sales_wed)
into sales_log
values(employee_id, week_id, 'SALES_THU', sales_thu)
into sales_log
values(employee_id, week_id, 'SALES_FRI', sales_fri)
select employee_id, week_id, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri
from sales;
--10개 행 이(가) 삽입되었습니다.
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
* 서브쿼리 사용시 규칙
- 서브쿼리는 괄호로 둘러싸여야 함(CTAS 제외)
- 비교 연산자의 오른쪽에 위치
- 두 종류의 비교연산자 사용(단일행, 다중행)
select first_name, salary
from employees
where salary > (select salary from employees where first_name='Nancy');
단일행 서브쿼리
- 내부 select 문장으로부터 하나의 행을 반환하는 질의
연산자
설명
=
같다
>
보다 크다
>=
보다 크거나 같다
<
작다
<=
작거나 같다
<>, !=
같지 않다
select first_name, job_id, hire_date
from employees
where job_id = (select job_id from employees where employee_id=103);
다중행 서브쿼리
- 서브쿼리의 결과가 2개 행 이상일 경우
연산자
설명
IN
목록의 어떤 값과 같은지 확인
EXISTS
값이 있는지 확인. + 서브쿼리만 가능
ANY, SOME
비교 연산자. 하나만 만족
ALL
비교 연산자. 모든 값과 비교해서 만족
ALL / ANY 차이점
< ANY : 가장 큰 값보다 작으면 됨 > ANY : 가장 작은 값보다 크면 됨 < ALL : 가장 작은 값보다 작으면 됨 > ALL : 가장 큰 값보다 크면 됨 = ANY : IN과 같은 역할
select first_name, salary
from employees
where salary > (select salary from employees where first_name='David');
ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action
select first_name, salary
from employees
where salary > any (select salary from employees where first_name='David');
select first_name, department_id, job_id
from employees
where department_id in (select department_id
from employees
where first_name = 'David');
--in(60,80)과 같음
상호연관 서브쿼리
- 한 개의 행을 비교할 때마다 결과가 메인쿼리로 반환됨
- 한 행을 처리할 때마다 서브쿼리로 주고 값을 처리한 후 결과를 다시 메인쿼리로 전달=> 성능 저하
- 메인쿼리와 서브쿼리 사이에 결과를 교한하기 위해 서브쿼리의 where 조건절에서 메인쿼리 테이블과 연결
select first_name, salary
from employees a
where salary > (select avg(salary)
from employees b
where b.department_id = a.department_id);
스칼라 서브쿼리
- SELECT 절에 사용하는 서브쿼리
select first_name, (select department_name from departments d where d.department_id = e.department_id) department_name
from employees e
order by first_name;
select first_name, department_name
from employees e
join departments d on (e.department_id = d.department_id)
order by first_name;
인라인 뷰
- FROM절에 서브쿼리가 온 것
select row_number, first_name, salary
from (select first_name, salary,
row_number() over(order by salary desc) as row_number
from employees)
where row_number between 1 and 10;
3중 쿼리와 Top-N 쿼리
- 서브쿼리 안에 또 다른 서브쿼리를 갖는 것
select rownum, first_name, salary
from (select first_name, salary from employees
order by salary desc)
where rownum between 1 and 10;
계층형 쿼리
- 수직적 관게를 맺고 있는 행들의 계층형 정보를 조회할 때 사용
SELECT select_list
FROM table
WHERE conditions
START WITH top_level_cindition
CONNECT BY [NOCYCLE] [PRIOR] connect_condition
ORDER SIBLING BY order_condition;
select employee_id,
lpad(' ',3*(level-1)) || first_name || ' ' ||last_name,
level
from employees
start with manager_id is null
connect by prior employee_id = manager_id;
select employee_id,
lpad(' ',3*(level-1)) || first_name || ' ' ||last_name,
level
from employees
start with employee_id=113
connect by prior manager_id=employee_id ;
