Oracle 8일차

1.제약조건

PRIMARY KEY

▶COLUMN LEVEL

CREATE TABLE CUSTOMER

(ID NUMBER(4) CONSTRAINT CUSTOMER_ID_PK PRIMARY KEY,

NAME VARCHAR2(10),

NO NUMBER(4));

▶TABLE LEVEL

CREATE TABLE ORDERS

(NO NUMBER(4),

SNO NUMBER(4),

NAME VARCHAR2(10),

NCOUNT NUMBER(7),

CONSTRAINT ORDERS_NO_PK PRIMARY KEY(NO));

FOREIGN KEY

▶CUSTOMER의 NO가 ORDERS의 NO 참조

ALTER TABLE CUSTOMER

ADD CONSTRAINT CUSTOMER_NO_FK FOREIGN KEY(NO)

REFERENCES ORDERS(NO);

▶CATALOG1의 NO가 ORDERS의 NO 참조

CREATE TABLE CATALOG1

(CATALOGNO NUMBER(4) CONSTRAINT CATALOG1_CATALOGNO_PK PRIMARY KEY,

NAME VARCHAR2(10),

NO NUMBER(4) CONSTRAINT CATALOG1_NO_FK REFERENCES ORDERS(NO));

UNIQUE KEY : 중복값X.NULL값 O

ALTER TABLE ORDERS

ADD CONSTRAINT ORDERS_SNO_UK UNIQUE(SNO);

CREATE TABLE TEST

(ID NUMBER,

NAME VARCHAR2(10),

JUMIN VARCHAR2(14) CONSTRAINT TEST_JUMIN_UK UNIQUE);

CHECK

ALTER TABLE ORDERS

ADD CONSTRAINT ORDERS_SNO_CK CHECK(SNO BETWEEN 10 AND 50);

NOT NULL

CREATE TABLE TEST1

(NO NUMBER(4) CONSTRAINT TEST1_NO_NN NOT NULL);

ALTER TABLE ORDERS

ADD CONSTRAINT ORDERS_NAME_NN CHECK(NAME IS NOT NULL);

ALTER TABLE ORDERS

MODIFY NAME NOT NULL;

제약조건 추가,삭제만 가능(수정은 없다)

ALTER TABLE STUDENT

DROP PRIMARY KEY;

ALTER TABLE STUDENT

DROP CONSTRAINT STUDENT_NO_PK;

ALTER TABLE ORDERS

DROP PRIMARY KEY CASCADE;

제약조건 비활성화 : 삭제X. 잠깐 사용을 안함

ALTER TABLE DIVISION

DISABLE PRIMARY KEY CASCADE;

▶활성화

ALTER  TABLE DIVISION

ENABLE PRIMARY KEY;

ALTER TABLE PERSONNEL

ENABLE CONSTRAINT PERSONNEL_DNO_FK;

2.DATA DICTIONARY

▶USER_ : USER 소유의 OBJECT 정보

▶ALL_  : USER에게 ACCESS가 허용된 OBJECT 정보  

▶DBA_  : DBA 권한을 가진 USER 소유의 OBJECT 정보

▶V$_   : SERVER의 성능에 관련된 정보

SELECT * FROM V$VERSION;

SELECT * FROM DICTIONARY;

SELECT * FROM USER_CONS_COLUMNS;

SELECT * FROM ALL_CONS_COLUMNS;

SELECT * FROM SYSTEM_PRIVILEGE_MAP;

▶SYS에서만 가능

SELECT * FROM V$INSTANCE;

SELECT * FROM DBA_CONSTRAINTS;

3.VIEW

▶가상테이블

▶보안을 위해 사용

SIMPLE VIEW : 하나의 테이블에서 데이터를 SELECT하는 VIEW

CREATE VIEW 뷰이름

AS

SELECT 컬럼 FROM 테이블명 WHERE 조건;

CREATE VIEW PER10_V

AS

SELECT * FROM PERSONNEL WHERE DNO=10;

CREATE VIEW PER20_V

AS

SELECT * FROM PERSONNEL WHERE DNO=20;

CREATE VIEW PER_AVG

AS

SELECT DNO,AVG(PAY) 평균, SUM(PAY) 합계

FROM PERSONNEL

GROUP BY DNO;

▶SELECT

SELECT * FROM PER_AVG WHERE 합계>=8000;

▶INSERT,CREATE,DELETE 다 가능

INSERT INTO PER20_V VALUES(1234,'HHHH',1001,2000,10);  

UPDATE PER20_V SET PNAME='AAAA'

WHERE PNO=1234;

DELETE PER20_V WHERE PNO=1234;

▶INSERT에러

CREATE VIEW PER

AS

SELECT PNAME,JOB,PAY FROM PERSONNEL;

INSERT INTO PER VALUES('I_WANT','GO_HOME',20000);

→PRIMARY KEY에 넣을 값이 없음!

▶VIEW 수정

일반적 : CREATE ▶ ALTER

VIEW : CREATE OR REPLACE VIEW : 없으면 만들고, 있으면 수정

CREATE OR REPLACE VIEW PER20_V

(번호,이름,직업,부서번호)

AS SELECT PNO,PNAME,JOB,DNO FROM PERSONNEL

WHERE DNO=20;

CREATE OR REPLACE VIEW AAA

AS

SELECT * FROM PERSONNEL

WHERE DNO=10;

▶VIEW 삭제

DROP VIEW AAA;

DROP VIEW PER_AVG;

COMPLEX VIEW : JOIN문으로 만든 VIEW

▶SELECT가능. INSERT,UPDATE,DELETE불가

CREATE TABLE 고객정보

(고객번호 CHAR(10),

이름 CHAR(10));

CREATE TABLE 회사정보

(고객번호 CHAR(10),

회사명 CHAR(10));

INSERT INTO 고객정보 VALUES('A001','배수지');

INSERT INTO  고객정보 VALUES('A002','홍길동');

INSERT INTO  회사정보 VALUES('A001','LG');

INSERT INTO  회사정보 VALUES('A002','HYUNDAI');

◀고객정보

◀회사정보

CREATE VIEW 정보

AS

SELECT A.고객번호,이름,회사명

FROM 고객정보 A, 회사정보 B

WHERE A.고객번호=B.고객번호;

◀정보

▶INSERT, UPDATE, DELETE 실행 불가

INSERT INTO  정보 VALUES('A003', '전지현','SAMSUNG');

INSERT INTO  정보 (고객번호,이름) VALUES('A003','전지현');

INSERT INTO  정보 (회사명) VALUES('SAMSUNG')

UPDATE 정보 SET 이름='이수지' WHERE 고객번호='A001';

DELETE 정보 WHERE 고객번호='A001';

WITH CHECK OPTION

CREATE OR REPLACE VIEW PER10_V

AS

SELECT * FROM PERSONNEL WHERE DNO=10

WITH CHECK OPTION CONSTRAINT PER10_V_CHECK;

UPDATE PER10_V SET DNO=20 WHERE PNO=1001;

→뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

WITH READ ONLY

CREATE OR REPLACE VIEW PER10_V

AS

SELECT PNO,PNAME,JOB FROM PERSONNEL

WHERE DNO=10

WITH READ ONLY;

DELETE PER10_V WHERE PNO=1118;

→읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

UPDATE PER10_V SET JOB='AAA' WHERE PNO=1119;

→읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

INSERT INTO PER10_V VALUES(1111,'KIM','SALESMAN');

→읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

4.TOP-N

가장 최근에 입사한 5명의 사원의 정보

SELECT ROWNUM AS FASTDATE, PNAME, STARTDATE

FROM (SELECT  PNAME,STARTDATE FROM PERSONNEL

ORDER BY STARTDATE DESC)

WHERE ROWNUM<=5;

SELECT FASTDATE,PNAME,STARTDATE FROM

(SELECT  ROWNUM FASTDATE,PNAME,STARTDATE FROM

(SELECT  PNAME,STARTDATE FROM PERSONNEL

ORDER BY STARTDATE DESC))

WHERE FASTDATE>=2 AND FASTDATE<=4;

▶MS-SQL

SELECT TOP 5 PNAME,STARTDATE FROM PERSONNEL ORDER BY STARTDATE DESC;

SELECT TOP 5 PRECENT PNAME,STARTDATE FROM PERSONNEL ORDER BY STARTDATE DESC;

5.SYNONYM(동의어)

동의어 생성

CREATE SYNONYM SALARY

FOR PAYGRADE;

CREATE SYNONYM INSA

FOR PERSONNEL;

동의어 삭제

DROP SYNONYM SALARY;

공용동의어

▶생성

CREATE PUBLIC SYNONYM PUB_DIV

FOR KIM.DIVISION;

GRANT SELECT ON KIM.DIVISION TO PUBLIC;

▶삭제

DROP PUBLIC SYNONYM PUB_DIV;