데이터베이스 20180824 (학원11)

DDL, DML, DCL, DQL, TCL

OBJECT

VIEW, SEQUENCE, INDEX, SYNONYM

PL/SQL

TRIGGER

PROCEDURE, FUNCTION, CURSOR, PACKAGE

@ 데이터 딕셔너리

 -> 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블

 -> 사용자가 테이블 생성하거, 사용자를 변경 등의작업을 할 때 데이터베이스 서버에 의해서 

자동으로 갱신

 -> 사용자들은 딕셔너리를 직접 수정하거나 삭제할 수 없음

 -> 데이터딕셔너리안에 중요정보가 많이 있다.

 -> 정보를 활용하기 위해서 데이터 딕셔너리 뷰를 사용

뷰 -> 가상의 테이블

※ VIEW : 가상의 테이블

@ 3개의 데이터 딕셔너리 뷰

1. DBA_XXXXXX : 데이터베이스 관리자만 접근이 가능한 객체등의 정보 조회

2. ALL_XXXXXX : 자신의 계정이 소유하거나 권한을 부여받은 객체등의 관한 정보 조회

3. USER_XXXXX : 자신의 계정이 소유한 객체 등에 관한 정보 조회

@ VIEW

 -> 하나 이상의 테이블에서 원하는 데이터를 선택하여 새로운 가상테이블을 만들어 주는 것

 -> 다른 테이블에 있는 데이터를 보여줄 뿐이며, 데이터 자체를 포함하고 있는것은 아님

 - 저장장치 내에 물리적으로 존재하지 않고 가상 테이블로 만들어짐

 - 물리적인 실제 테이블에 링크 개념

 -> 뷰를 사용하면 특정 사용자가 원본테이블에 접근해서 모든 데이터를 보는것을 방지 할 수 

있음

 -> 뷰를 만들기위해서는 권한이 필요함

GRANT CREATE VIEW TO kh;

--kh계정한테 뷰를 크리에이트할권한을 줄 수 있다.

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

VIEW에서도 DML(INSERT, UPDATE, DELETE) 사용이가능

 -> 뷰테이블내용이 변경되면 원본 테이블도 변경(링크)

# DML로 조작이 불가능한경우

1. 뷰 정의에 포함되지 않은 컬럼을 조작할 때

2. 뷰에 포함되지 않은 컬럼중에 테이블 컬럼이 NOT NULL 제약조건이 지정된 경우

3. 산술표현식을 정의된 경우

4. JOIN을 이용해서 여러 테이블을 연결한 경우

5. DISTINCT를 포함한 경우

6. GROUP BY 절이나 그룹함수를 포함한 경우

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

VIEW생성 옵션

1. OR REPLACE

 -> 뷰를 생성할때 뷰가 존재하면, 갱신

CREATE VIEW TEST_VIEW

CREATE OR REPLACE VIEW TEST_VIEW

2. FORCE/NOFORCE

 -> FORCE 옵션은 기본테이블 존재하지 않더라도 뷰를 생성

--일반적으로 사용되지 않음

 -> 기본값은 NOFORCE -> 테이블이 없으면 생성되지 않음

3. WITH CHECK OPTION

 -> 설정한 컬럼의 값 수정시 수정을 못하게함

 -> WHERE절에 맞춰서 값을 넣어야함

4. WITH READ ONLY

 -> 조회만 가능, 삽입, 수정, 삭제 불가능

CHECK OPTION, READ ONLY의차이

 -> 특정 컬럼에 대한 것인지, 뷰 전체를 수정하지 못하게 하는지

READ ONLY는 전체를 다 못하게 하는거고

CHECK OPTION은 부분만 못하게 하는 것이다.

@ SQUENCE

 -> 순차적으로 정수 값을 자동으로 생성하는 객체

 -> 자동 번호 발생기

CREATE SEQUENCE [시퀀스명]

[START WITH 숫자] -> 처음 발생시킬 시작 값(기본값 1)

[INCREMENT BY 숫자] -> 다음값에 대한 증가치(기본값 1)

[MAXVALUE 숫자 | NOMAXVALUE] -> 발생시킬 최대값(10^27 -1)

[MINVALUE 숫자 | NOMINVALUE] -> 발생시킬 최소값(-10^26)

[CYCLE | NOCYCLE]

 -> 최대값 증가완료된경우

 -> CYCLE -> START WITH 설정값

 -> NOCYCLE -> 에러발생

[CACHE | NOCACHE]

 -> CACHE는 메모리상에서 시퀀스 값을 관리, 기본값 20

  - 실제 값은 다 증가하지만 메모리의 효율을 위해 사용됨

CURRVAL : 현재 시퀀스 값 반환

NEXTVAL : 현재 시퀀스값의 다음값을 반환

# NEXTVAL, CURRVAL를 사용할수 있는경우

 -> 서브쿼리가 아닌 SELECT문

 -> INSERT문의 SELECT절

 -> INSERT문의 VALUES 절

 -> UPDATE문의 SET 절

# NEXTVAL, CURRVAL를 사용할수 없는경우

 -> VIEW의 SELECT절

 -> DISTINCT키워드가 있는 SELECT절

 -> GROUP BY, HAVING, ORDER BY 절이 있는 SELECT문

 -> SELECT, DELETE, UPDATE의 서브쿼리

 -> CREATE TABLE, ALTER TABLE 명령의 DEFAULT

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

--20180824

/*

@ 데이터 딕셔너리

 -> 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블

 -> 사용자가 테이블 생성하거, 사용자를 변경 등의작업을 할 때 데이터베이스 서버에 의해서 

자동으로 갱신

 -> 사용자들은 딕셔너리를 직접 수정하거나 삭제할 수 없음

 -> 데이터딕셔너리안에 중요정보가 많이 있다.

 -> 정보를 활용하기 위해서 데이터 딕셔너리 뷰를 사용

뷰 -> 가상의 테이블

※ VIEW : 가상의 테이블

@ 3개의 데이터 딕셔너리 뷰

1. DBA_XXXXXX : 데이터베이스 관리자만 접근이 가능한 객체등의 정보 조회

2. ALL_XXXXXX : 자신의 계정이 소유하거나 권한을 부여받은 객체등의 관한 정보 조회

3. USER_XXXXX : 자신의 계정이 소유한 객체 등에 관한 정보 조회

@ VIEW

 -> 하나 이상의 테이블에서 원하는 데이터를 선택하여 새로운 가상테이블을 만들어 주는 것

 -> 다른 테이블에 있는 데이터를 보여줄 뿐이며, 데이터 자체를 포함하고 있는것은 아님

 - 저장장치 내에 물리적으로 존재하지 않고 가상 테이블로 만들어짐

 - 물리적인 실제 테이블에 링크 개념

 -> 뷰를 사용하면 특정 사용자가 원본테이블에 접근해서 모든 데이터를 보는것을 방지 할 수 

있음

 -> 뷰를 만들기위해서는 권한이 필요함

*/

--뷰 만들기

/*

CREATE VIEW EMP_VIEW(

컬럼명 데이터타입 제약조건

)

*/

select * from USER_VIEWS;

--> 아직 만들어진게 없어서 안뜬다.

--뷰 만들기 --테이블만드는거랑 같다.

create view emp_view

as

select emp_no, emp_name, email, phone

from employee;

--에러가 뜬다. 권한을 줘야 한다.

/*

GRANT CREATE VIEW TO kh;

--kh계정한테 뷰를 크리에이트할권한을 줄 수 있다.

*/

create view emp_view

as

select emp_no, emp_name, email, phone

from employee;

--뷰 조회

select * from emp_view;

--뷰에들어가있는 것이다.->데이터 딕셔너리 조회한 것이다.

select * from USER_VIEWs;

--문제 test01계정에서 EMP_VIEW 검색할 수 있게 해주라.

-- employee테이블에있는 이름을 하동운 -> 하동훈 으로바꿔라

select * from employee;

update employee SET EMP_NAME='하동훈' where EMP_NAME='하동운';

commit;

update emp_view SET EMP_NAME='하동운' where EMP_NAME='하동훈';

select * from EMP_VIEW;

commit;

--뷰날리기

select * from USER_VIEWs;

DROP VIEW EMP_VIEW;

select * from USER_VIEWs;

--여러테이블 조인해서 뷰를 만들 수 있다.

CREATE VIEW VIEW_TEST1

AS

SELECT EMP_NO, EMP_NAME, JOB_CODE, JOB_NAME

FROM EMPLOYEE

JOIN JOB USING(JOB_CODE);

--뷰 조회할 수 있다.

SELECT * FROM VIEW_TEST1;

--선생님문제

--VIEW_TEST2, 사원명, 보너스를 포함한 연봉

CREATE VIEW VIEW_TEST2

AS

SELECT EMP_NAME 이름, (salary*12+salary*12*NVL(BONUS,0)) 연봉

FROM EMPLOYEE;

select * from VIEW_TEST2;

/*

VIEW생성 옵션

1. OR REPLACE

 -> 뷰를 생성할때 뷰가 존재하면, 갱신

CREATE VIEW TEST_VIEW

CREATE OR REPLACE VIEW TEST_VIEW

2. FORCE/NOFORCE

 -> FORCE 옵션은 기본테이블 존재하지 않더라도 뷰를 생성

--일반적으로 사용되지 않음

 -> 기본값은 NOFORCE -> 테이블이 없으면 생성되지 않음

*/

CREATE VIEW VIEW_TEST3

AS

SELECT EMP_NO, EMP_NAME, JOB_CODE

FROM eee; --테이블이 없으므로 안만들어진다.

CREATE FORCE VIEW VIEW_TEST3

AS

SELECT EMP_NO, EMP_NAME, JOB_CODE

FROM eee; --테이블이 없으므로 안만들어진다.

CREATE FORCE VIEW VIEW_TEST3

AS

SELECT EMP_NO, EMP_NAME, JOB_CODE

FROM eee; --테이블이 없으므로 안만들어진다.

/*

3. WITH CHECK OPTION

 -> 설정한 컬럼의 값 수정시 수정을 못하게함

 -> WHERE절에 맞춰서 값을 넣어야함

*/

--뷰 테이블 생성

CREATE VIEW TEST_VIEW4

AS

SELECT EMP_NAME, DEPT_CODE, SALARY

FROM EMPLOYEE WHERE SALARY > 3000000 WITH CHECK OPTION;

select * from TEST_VIEW4;

select * from USER_VIEWS;

--신봉선이라는 분의 월급을 250만으로 수정하는걸 해보아라

DROP VIEW TEST_VIEW4;

--안된다. 수정을 못하게 막아놓았기 때문이다.

--값이 3백만보다 작기 때문에 안된다.

UPDATE TEST_VIEW$ SALARY=2500000 where EMP_NAME='심봉선';

/*

4. WITH READ ONLY

 -> 조회만 가능, 삽입, 수정, 삭제 불가능

*/

--이름이 겹쳐도 다시 생성된다.

CREATE OR REPLACE VIEW TEST_VIEW4

AS

SELECT EMP_NAME, DEPT_CODE, SALARY

FROM EMPLOYEE WHERE SALARY > 3000000 WITH READ ONLY;

--조건이 맞을텐데 안된다. 왜냐하면 조건이 READ ONLY이기 때문에 조회용 뷰이다.

UPDATE TEST_VIEW4 SET SALARY=4400000 WHERE EMP_NAME='유재식';

select * from TEST_VIEW4;

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

/*

@ SQUENCE

 -> 순차적으로 정수 값을 자동으로 생성하는 객체

 -> 자동 번호 발생기

CREATE SEQUENCE [시퀀스명]

[START WITH 숫자] -> 처음 발생시킬 시작 값(기본값 1)

[INCREMENT BY 숫자] -> 다음값에 대한 증가치(기본값 1)

[MAXVALUE 숫자 | NOMAXVALUE] -> 발생시킬 최대값(10^27 -1)

[MINVALUE 숫자 | NOMINVALUE] -> 발생시킬 최소값(-10^26)

[CYCLE | NOCYCLE]

 -> 최대값 증가완료된경우

 -> CYCLE -> START WITH 설정값

 -> NOCYCLE -> 에러발생

[CACHE | NOCACHE]

 -> CACHE는 메모리상에서 시퀀스 값을 관리, 기본값 20

  - 실제 값은 다 증가하지만 메모리의 효율을 위해 사용됨

*/

--시퀀스 생성

CREATE SEQUENCE SEQ_EMPID

START WITH 1

INCREMENT BY 1

MAXVALUE 100

--MININUM써줄 필요가 없다.

NOCYCLE

NOCACHE;

SELECT * FROM USER_SEQUENCES;

--시작값을 먼저 보는 것이다.처음엔 NEXTVAL을 무조건 써줘야 한다.

SELECT SEQ_EMPID.NEXTVAL FROM DUAL;

SELECT SEQ_EMPID.CURRVAL FROM DUAL;

--이렇게 증가를 하는 것이다.

SELECT SEQ_EMPID.NEXTVAL FROM DUAL;

SELECT SEQ_EMPID.CURRVAL FROM DUAL;

/*

# NEXTVAL, CURRVAL를 사용할수 있는경우

 -> 서브쿼리가 아닌 SELECT문

 -> INSERT문의 SELECT절

 -> INSERT문의 VALUES 절

 -> UPDATE문의 SET 절

# NEXTVAL, CURRVAL를 사용할수 없는경우

 -> VIEW의 SELECT절

 -> DISTINCT키워드가 있는 SELECT절

 -> GROUP BY, HAVING, ORDER BY 절이 있는 SELECT문

 -> SELECT, DELETE, UPDATE의 서브쿼리

 -> CREATE TABLE, ALTER TABLE 명령의 DEFAULT

*/

--테이블 만들기

CREATE TABLE SEQ_TEST(

NO NUMBER PRIMARY KEY,

NAME VARCHAR2(20) NOT NULL,

AGE NUMBER NOT NULL

);

--시퀀스 만들기

CREATE SEQUENCE SEQ_TEST_NO

START WITH 1

INCREMENT BY 1

MAXVALUE 100

NOCYCLE

NOCACHE;

--데이터 삽입

INSERT INTO SEQ_TEST VALUES(SEQ_TEST_NO.NEXTVAL,'이윤수',20);

INSERT INTO SEQ_TEST VALUES(SEQ_TEST_NO.NEXTVAL,'이윤수',20);

INSERT INTO SEQ_TEST VALUES(SEQ_TEST_NO.NEXTVAL,'이윤수',20);

INSERT INTO SEQ_TEST VALUES(SEQ_TEST_NO.NEXTVAL,'이윤수',20);

--NO가 우리가 지정한 시퀀스로 들어간것이다.

select * from SEQ_TEST;

--변경하기

ALTER SEQUENCE SEQ_EMPID

INCREMENT BY 10

MAXVALUE 400

NOCYCLE

NOCACHE;

--START WITH는 변경이 안된다. 바꿀려면 시퀀스 새로만들어야 한다.

select SEQ_EMPID.CURRVAL FROM DUAL;

select SEQ_EMPID.NEXTVAL FROM DUAL;

--시퀀스 지우기

DROP SEQUENCE SEQ_EMPID;

/*

문제

시퀀스 예제

KH_MEMBER테이블

컬럼

MEMBER_ID NUMBER

MEMBER_NAME VARCHAR2(20)

MEMBER_AGE NUMBER

MEMBER_JOIN_COM NUMBER

INSERT 4~5개정도,->이름나이만 입력해라대충

MEMBER_ID, MEMBER_JOIN_COM 시퀀스로 입력

MEMBER_ID -> 500번부터 시작 10씩 증가하게

MEMBER_JOIN_COM -> 1번부터 시작 1씩 증가하게 

(둘다 MAX -> 10000)

*/

CREATE SEQUENCE MEMBER_ID

START WITH 500

INCREMENT BY 10

MAXVALUE 10000

--MININUM써줄 필요가 없다.

NOCYCLE

NOCACHE;

CREATE SEQUENCE MEMBER_JOIN_COM

START WITH 1

INCREMENT BY 1

MAXVALUE 10000

NOCYCLE

NOCACHE;

CREATE TABLE KH_MEMBER(

MEMBER_ID NUMBER,

MEMBER_NAME VARCHAR2 (20),

MEMBER_AGE NUMBER,

MEMBER_JOIN_COM NUMBER

);

select * from kh_member;

INSERT INTO KH_MEMBER VALUES(MEMBER_ID.NEXTVAL,'김종규',26,MEMBER_JOIN_COM.NEXTVAL);

INSERT INTO KH_MEMBER VALUES(MEMBER_ID.NEXTVAL,'김종규',26,MEMBER_JOIN_COM.NEXTVAL);

INSERT INTO KH_MEMBER VALUES(MEMBER_ID.NEXTVAL,'김종규',26,MEMBER_JOIN_COM.NEXTVAL);

select * from kh_member;