Oracle 요약 3

▒ 데이터 베이스 객체

: 데이터를 관리하는 논리적 단위

 

- 테이블 : Data 저장

- 뷰

- 시퀀스

- 인덱스

- 동의어

==> 위의 모두를 총괄적으로 관리하는 단위 : 스키마 (유저라고 생각하면 됨.)

 

 

유저를 만든다는것은 위에 5가지를 관리할 집합체를 만드는것이라 생각.

스키마끼리 서로 알수 없다. 서로 접근을 허용을 하지 않았다면 접근할수 없다.

select * from 스키마 employees; 원래는 스키마(유저)가 들어감.

 

sysdba가 오라클의 최고 사용자와 같음. 

sysdba는 다른 스키마도 볼수 있다.

해당 스키마는 저장공간을 명시해 줄수 있다. 파일로 저장되어 관리됨.(저장매체로 접근하는것은 실제로 OS가 하기 때문에)

 

 

① 테이블 : DATA저장 ->검색, 수정, 삭제, 제약조건..

컬럼, row을 만듬.                                                               

                               

컬럼이름 데이터형식제약조건
한글도지원 number  
(128개국어지원)  date 
 char(정해진 문자길이) 
 varchar2(가변적늘어남) 
 long(최대2G까지가변문자) 
 clob(최대4G가지문자데이터)  
 blob(가변 길이 이진 데이터(1~4Gbyte))

 

 

----------테이블 생성------------------------------------------------------------------------------

create table 테이블명(

컬럼 형식 제약조건

); 

저장공간 명시

관리기법 명시

----------테이블의 구조 변경-----------------------------------------------------------------------

alter table 테이블명

modify (수정) 컬럼 형식 제약조건

add (추가) 컬럼 형식 제약조건

drop (삭제) 컬럼 형식 제약조건

 

- 기존의 데이터가 변경할 형식과 다르면 문제가 생김.

 

ex) alter table employees add yearsal number(15) : 컬럼추가

ex) update employees set yearsal=12*salary+nvl(salary*COMMISSION_PCT,0) ; yearsal에 데이터 추가

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

create table 사원연봉 as 서브쿼리 : 서브쿼리 내용을 새로운 테이블로 생성!!

ex) create table 사원연봉 as select 12*salary+nvl(salary*COMMISSION_PCT,0) as "연봉" from employees;

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

cf)  unused : 필요없는 컬럼을 사용하지 못하게 만듬. 해당컬럼의 데이터가 많고 데이터베이스가 바쁠때 

                   drop하기 전에 임시방편. 

형식 : alter table 테이블명 set unused (컬럼) : unused 설정

형식 : alter table 테이블명 drop unused (컬럼) : unused 된 컬럼을 삭제

 

--컬럼 UNUSED 하기
    ALTER TABLE SAWON SET UNUSED(EMAIL)
    ALTER TABLE SAWON SET UNUSED COLUMN EMAIL 

 

--UNUSED상태에 있는 컬럼명 조회 
    SELECT * FROM USER_UNUSED_COL_TABS

 

--실제로 UNUSED 상태의 컬럼을 삭제하기
    ALTER TABLE SAWON DROP UNUSED COLUMNS

----------------------------------------------------------------------------------------------------
delete : 삭제 (ROLLBACK 가능)

Truncate : 절단 (속도는 delete보다 빠름, ROLLBACK 불가능)

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

 

② 뷰 (view)

: 테이블의 논리적인 부분 집합

- 많이 검색하는 쿼리, 컬럼들.. 미리 만들어 둘수 있다.

- DML작업이 많이 없는 것들

저장공간을 사용하지 않는다. 즉, 테이블이 아니다??

 
형식 :
create view view명 
as
select e.employee_id,e.last_name,d.department_name
from employees e
join departments d
on e.department_id=d.department_id
 
 
 
 
ex) 
- create view emp_dept_join as select e.employee_id,e.last_name,d.department_name from employees e join departments d on e.department_id=d.department_id (emp_dept_join 뷰 생성)
 
- select employee_id, department_id from emp_dept_join
 
 
<뷰 정보확인>
select * from user_views;
select * from dba_views;
 
cf) create or replace view ~
 
     같은 이름의 뷰가 있을때 덮어쓰고 싶을때 
     or replace를 사용.
 
 
<뷰 삭제>
Drop view view명
 
 
 
- 단순 뷰 : 테이블을 하나 써서 만듬
- 복합 뷰 : 테이블을 여러개 써서 만듬
- 인라인 뷰
 
cf) 대개 뷰는 검색작업을 위해 많이 쓰지만, DML작업도 할수 있다.검색을 위한 목적이라면 단순뷰나 복합뷰든 상관없지만, DML작업을 할 때는 단순뷰는 하나의 테이블을 사용하기때문에 잘되지만, 복합뷰(group by를 써서 사용한 경우등..)는 여러 테이블을 사용하기 때문에 원본에 문제가 생길수 있기때문에 잘 쓰지 않는 것이 좋음. 
 

cf) Top-N 분석 ----------------------------------------------------------------

rownum : row에 대한 number를 붙여줌.

- select rownum,last_name,salary from employees

ROWNUM LAST_NAME SALARY 
King 30000 
Kochhar 17000 
Dehaan 17000 
Hunold 9000 
Ernst 6000 
Lorentz 4200 
Mourgos 5800 
Rajs 3500 
Davies 3100 
10 Matos 2600 
11 Vargas 2500 
12 Zlotkey 10500 
13 Abel 11000 
14 Taylor 8600 
15 Grant 7000 
16 Whaien 4400 
17 Hartstein 13000 
18 Fay 6000 
19 Higgins 12000 
20 Gietz 8300 

 

 

- select rownum,last_name,salary from employees order by salary desc;

(먼저 rownum,last_name,salary 를 뽑아 놓고 정렬을 하기때문에, rownum이 뒤죽박죽ㅋㅋ)

ROWNUM LAST_NAME SALARY 
King 30000 
Kochhar 17000 
Dehaan 17000 
17 Hartstein 13000 
19 Higgins 12000 
13 Abel 11000 
12 Zlotkey 10500 
Hunold 9000 
14 Taylor 8600 
20 Gietz 8300 
15 Grant 7000 
Ernst 6000 
18 Fay 6000 
Mourgos 5800 
16 Whaien 4400 
Lorentz 4200 
Rajs 3500 
Davies 3100 
10 Matos 2600 
11 Vargas 2500 

 

 

- select rownum,last_name,salary from (select last_name,salary from employees order by salary desc);

ROWNUM LAST_NAME SALARY 
King 30000 
Kochhar 17000 
Dehaan 17000 
Hartstein 13000 
Higgins 12000 
Abel 11000 
Zlotkey 10500 
Hunold 9000 
Taylor 8600 
10 Gietz 8300 
11 Grant 7000 
12 Ernst 6000 
13 Fay 6000 
14 Mourgos 5800 
15 Whaien 4400 
16 Lorentz 4200 
17 Rajs 3500 
18 Davies 3100 
19 Matos 2600 
20 Vargas 2500 

 

- select rownum,last_name,salary from (select last_name,salary from employees order by salary desc) where rownum<6;

(1~5까지 뽑아보자!!ㅋㅋ)

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

③ 시퀀스

: 연속적으로 값을 할당하는 의미

ex) Max(employee_id)+1 

=> but max는 group 함수, 따라서 row가 많을 수록 시간이 오래 걸림.

이런 단점을 보완해서 나온것이~ 시쿼스라네요~ㅋ

 

 

 

 

 

 

 

 

 

 

 

- 형식 : create sequence 시퀀스명 start with 처음부여할번호 increment by 간격 maxvalue 최대값 nocycle nocache ;

ex) - create sequence dept_id_deq start with 200 increment by 10 maxvalue 1000 nocycle nocache;

     - select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYC ORD CACHE_SIZE LAST_NUMBER 
DEPT_ID_DEQ 1000 10 200

 

     

      -  insert into departments values(dept_id_deq.nextval,'KBS',100,1400);

       (nextval : last_number의 값을 넣어줌)

      

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID 
200 KBS 100 1400

 

 

200 -> currval

210 -> nextval

하나를 더 부여하면,

210 -> currval

220 -> nextval 이 됨.

 

      -  insert into departments values(dept_id_deq.nextval,'MBS',600,1100);

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID 
200 KBS 100 1400 
210 MBS 600 1100

 

 

cf) 중간에 오류가 나더라도 건너뛰고 자동으로 부여된다.

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

④ 동의어 (synonym)

긴 이름들을 단순화 할때 쓰면 편리함.

- 형식 : create synonym newname for oldname

 

 

참고> 상대방 오라클 접속 설정

1. # xhost + localhost

2. $HOME/bin로 이동 해서 

3. $./netca 실행