Oracle 문법 정리 - 스키마 객체

◆스키마 객체 관리

 

테이블 컬럼 삭제

alter table dept80 drop column job_id;

 

테이블 컬럼 변경

alter table dept80 modify last_name varchar2(30);

 

테이블 컬럼 추가

alter table dept80 add job_id varchar(9);

 

unused

: 데이터를 사용하지 않겠다고 설정.

: 데이터가 사라지는 것은 아님. 데이터가 엄청 많을 때 지우는 시간동안 서비스가 제대로 이루어 지지 않기 때문에 나중에 삭제하려고 unused체크

alter table dept80 set unused (last_name);

 

unused 체크한 것 삭제하기

alter table dept80 drop unused columns;

 

테이블 제약 조건 변경

ALTER TABLE emp2 MODIFY employee_id PRIMARY KEY;

 

 

테이블 제약 조건 추가

ALTER TABLE emp2 ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(manager_id) REFERENCES emp2(employee_id);

 

on delete

: 외래키에서 상위 키가 삭제 될 때 하위 행의 행동을 지정

 

1)연쇄 삭제 : 참조하고 있는 행이 삭제되면 참조하던 행도 삭제

ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk FOREIGN KEY (Department_id) REFERENCES departments(department_id) ON DELETE CASCADE;

 

2)null로 만들기 : 참조하고 있는 행이 삭제되면 참조하던 행을 null로 고침

ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk FOREIGN KEY (Department_id) REFERENCES departments(department_id) ON DELETE SET NULL;

 

제약 조건 지연

 

1)     생성 시 제약 조건 지연(트랜잭션이 종료될 때까지 제약 조건 검사를 기다림)

ALTER TABLE dept2 ADD CONSTRAINT dept2_id_pk PRIMARY KEY (department_id) DEFERRABLE INITIALLY DEFERRED;

 

2)     (명령문 실행이 완료되면 제약 조건을 바로 검사)

CREATE TABLE emp_new_sal (salary NUMBER CONSTRAINT sal_ck CHECK (salary > 100) DEFERRABLE INITIALLY IMMEDIATE;

 

3)     특정 제약 조건 속성 변경

SET CONSTRAINTS dept2_id_pk IMMEDIATE;

 

4)     세션의 모든 제약 조건 변경

ALTER SESSION SET CONSTRAINTS= IMMEDIATE;

 

제약 조건 삭제

ALTER TABLE emp2 DROP CONSTRAINT emp_mgr_fk;

 

ALTER TABLE dept2 DROP PRIMARY KEY CASCADE; -- 연관된 foreign key 조건도 삭제

 

rename column

CREATE TABLE marketing (team_id NUMBER(10), target VARCHAR2(50), CONSTRAINT mktg_pk PRIMARY KEY(team_id));     ALTER TABLE marketing RENAME COLUMN team_id TO id;     ALTER TABLE marketing RENAME CONSTRAINT mktg_pk TO new_mktg_pk;

 

 

임시 테이블

CREATE GLOBAL TEMPORARY TABLE today_sales ON COMMIT PRESERVE ROWS AS SELECT * FROM employees;   select * from today_sales;   commit;  -- 세션이후(접속 끊었다 다시 켜기)에는 today_sales 테이블에 정보가 없어져 있을 것이다.

 

CREATE GLOBAL TEMPORARY TABLE today_sales3 ON COMMIT delete ROWS AS SELECT last_name, salary FROM employees;   insert into today_sales3 values ('test',10000);   select * from today_sales3;   commit; -- 커밋 후에는 정보 삭제

 

 

External Table

: 외부 파일(csv,excel)등을 오라클에 집어넣고 싶을 때 사용

1)External Table 디렉토리 생성

--먼저 sys로부터 권한을 받는다. grant create any directory to n1; revoke create any directory from n1;   create or replace directory emp_dir as ‘c:/dev’; -- 폴더 지정

2)csv 만들기

다음과 같이 작성 후 c:/dev(방금전 지정한 폴더)에 csv 형태로 넣는다.

 

 

3)External Table 생성

CREATE TABLE oldemp3 (fname char(25), lname CHAR(25), team  char(20)) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir  -- 지정폴더 ACCESS PARAMETERS (FIELDS TERMINATED BY ',' (fname,lname,team))--csv는 쉼표단위 LOCATION ('testdb.csv')--2에서 저장한 데이터 ) PARALLEL 5 REJECT LIMIT 200;   select * from oldemp3;