2021.07.07 수업일지

- 쇼핑몰 만들기 review

쇼핑몰  가장 기본적인틀

 

- VIEW : 논리적인 가상 테이블

※ 뷰 특징

1. 사용자에게 접근이 허용된 자료만 제한적으로 보여주기 위해, 하나 이상의 기본 테이블로 유도된, 이름을 가지는 가상의 테이블 2. 임시적인 작업(데이터 보정작업, 처리과정 시험)을 위한 용도로 활용 3. 뷰는 기본테이블과 같은 형태의 구조 사용, 조작도 거의 같다. 4. 가상의 테이블이기 떄문에, 저장장치 내에 물리적으로 존재되어 있지 않다. 5. 데이터의 논리적 독립성 제공 6. 필요한 데이터만 뷰로 정의해서 처리할 수 있기 떄문에, 관리가 용이하며, 뷰를 생성한 후에 쿼리를 간단하게 쓸 수 있다. 7. 뷰를 통해서만 데이터에 접근하게 되면, 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있다. 8. 기본 테이블의 기본키를 포함한 속성집합으로 뷰를 구성해야만 삽입, 삭제, 갱신 연산 가능 9. 일단 정의된 뷰는 다른 뷰의 정의에 기초될 수 있다. 10. 뷰가 정의된 기본테이블이나, 뷰를 삭제하면, 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동 삭제 11. 조인문 사용 최소화로 사용상의 편의성 최대화

 

EX)

SELECT E.ENAME, E.SAL*12 ANNUAL
FROM (SELECT ENAME,SAL FROM EMP) E;

ㄴ 위의 예처럼 SELECT FROM 절에서 테이블 대신에 서브쿼리를 이용해서 가상의 테이블을 만든다. 이런 것을 VIEW라고 명명할 수 있다.

CREATE VIEW EMP_VIEW30
AS
SELECT EMPNO, ENAME, DEPTNO --이하 서브쿼리
FROM EMP
WHERE DEPTNO=30;

ㄴ 셀렉트 쿼리문을 EMP_VIEW30이라는 이름의 가상 테이블로 생성한다.
ㄴ 뷰는 중요한 헤더, 팀장급들에게 권한을 준다.
ㄴ 그렇기에 SYS(1인자 계정)에서 ICIA(말단 계정)에게  생성 권한(CREATE VIEW)을 줄 필요가 있다.

 

-- 권한 확인
SELECT * FROM role_sys_privs -- 시스템에 역할을 정의한 테이블
WHERE ROLE='CONNECT'; -- CREATE SESSION의 권한이 들어있다.

SELECT * FROM role_SYS_privs
WHERE ROLE = 'RESOURCE';

-- ICIA에게 VIEW 생성 권한 부여
GRANT CREATE VIEW TO ICIA;

 

- 권한을 설정하고 뷰를 만들면

-- VIEW 확인
SELECT * FROM EMP_VIEW30;

SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO=30;

ㄴ 동일한 가상의 테이블을 뷰로 정의하여 볼 수 있다.
ㄴ 생성한 뷰는 접속란의 ICIA 계정안에서 VIEW SECTION을 통해서 볼 수 있다.
ㄴ 자주 사용하는 SELECT절은 뷰를 설정해서 쉽게 이용할 수 있다.

 

- 생성한 뷰를 삭제하고 싶다면

DROP VIEW EMP_VIEW30;

ㄴ 하지만 매번 삭제하고 설정하는 것은 좋지 않다. 그렇기에 애초에 생성할때 수정권한도 같이 주는게 좋다.

 

- VIEW 생성 및 수정 권한 설정 + 컬럼 이름설정

CREATE OR REPLACE VIEW EMP_VIEW20(ENO,NAME,DNO)
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO=20;

ㄴ 뷰 생성 및 수정은 CREATE OR REPLACE VIEW라고 기억하면 된다. CREATE는 생성, REPLACE는 수정인데 CREATE OF REPLACE라고 명시해줬을경우 타겟 뷰이름이 없을때 CREATE를 하고 있는 경우 REPLACE한다. ?

 

- 사번, 이름, 연봉계산식을 넣은 뷰 만들기

CREATE OR REPLACE VIEW V_EMP(EMPNO, ENAME, ANNUAL)
AS
SELECT EMPNO, ENAME, SAL*12+NVL(COMM,0) --사번, 이름, 연봉계산식
FROM EMP;

ㄴ 확인

SELECT * FROM V_EMP;

 

- 뷰라는 가상의 테이블에도 INSERT 가능하다. (많이 쓰지는 않는다)

CREATE OR REPLACE VIEW V_EMP
AS
SELECT EMPNO, ENAME, SAL
FROM EMP;

ㄴ VIEW를 수정하고 데이터를 넣어보자.

INSERT INTO V_EMP
VALUES(111,'CHA',1000);

SELECT * FROM V_EMP;

ㄴ 넣은 데이터를 확인해보자.

ㄴ 삽입, 수정, 삭제도 가능하지만 VIEW의 역할인 검색(SELECT)용으로만 사용하는 것이 좋다.

 

- VIEW 의 장점, 단점

장점	단점
- 논리적 데이터 독립성 제공 - 동일 데이터에 대해 동시에 여러 사용자의 다른 방식의 응용이나 요구를 지원해준다. - 사용자의 데이터 관리가 간단해진다. 사용자 데이터 관리가 용이하다. - 접근제어를 통한 자동보안이 제공된다. - 데이터 보관이 용이하다.	- 독립적인 인덱스를 가질 수 없다. - 뷰의 정의를 변경할 수 없다. - 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신, 연산에 제약이 따른다.

 

- 뷰를 이용해서 뷰를 생성

CREATE OR REPLACE VIEW VV_EMP
AS
SELECT ENAME, SAL*12 ANNUAL
FROM V_EMP;

ㄴ 생성된다. 물리적인 테이블은 아니지만 논리적인 테이블도 그 기능은 비슷하다. 다만 위에서 말한것처럼 검색(SELECT)용으로만 이용하는 것을 추천한다.

 

- Q. 부서별(GROUP BY) 최대(MAX) 급여를 받는 사원 조회(이름, 부서명, 최대급여)

1) 뷰를 사용하지 않을때

SELECT E.ENAME, D.DNAME, M.SAL
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
JOIN (SELECT DEPTNO, MAX(SAL) SAL
    FROM EMP
    GROUP BY DEPTNO) M
ON E.DEPTNO=M.DEPTNO
WHERE M.SAL=E.SAL;

 

2) 뷰를 사용할때

- 뷰 생성

CREATE VIEW MAX_SAL
AS
SELECT DEPTNO, MAX(SAL) SAL
FROM EMP
GROUP BY DEPTNO;

 

- 적용

SELECT E.ENAME, D.DNAME, M.SAL
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
JOIN MAX_SAL M
ON E.DEPTNO=M.DEPTNO
WHERE M.SAL=E.SAL;

 

3) 문제에서 생성된 쿼리를 자주 쓸것 같으면 또 뷰를 만들 수 있다.

CREATE OR REPLACE VIEW V_RESULT
AS
SELECT E.ENAME, D.DNAME, M.SAL
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
JOIN MAX_SAL M
ON E.DEPTNO=M.DEPTNO
WHERE M.SAL=E.SAL;

 

- 서브쿼리에서 했었던 급여 순위 매기기에도 적용할 수 있다.

-- Q. 6~10위만 검색할 수 있도록 서브쿼리 작성
SELECT * FROM V_RN
WHERE RANK BETWEEN 6 AND 10;

CREATE OR REPLACE VIEW V_RN
AS
SELECT ROWNUM RANK, EMPNO, ENAME, SAL
FROM (SELECT * FROM EMP ORDER BY SAL DESC);

 

- 잠깐!) 기사 시험용
- WITH CHECK OPTION : VIEW 조건에 맞지 않는 UPDATE, INSERT를 불가하게 만듦

CREATE OR REPLACE VIEW EMP_VIEW20
AS
SELECT EMPNO, ENAME, DEPTNO FROM EMP
WHERE DEPTNO = 20;

SELECT * FROM EMP_VIEW20;

UPDATE EMP_VIEW20 SET DEPTNO=40
WHERE EMPNO=7369; -- 행이 업데이트 되었다고 나오지만 삭제되었다.

ㄴ DEPTNO = 40인 경우는 해당 VIEW에 존재하지 않는다. 위의 UPDATE가 실행되었지만 EMPNO=7369인 경우의 ROW는 삭제 되었다. 이런 경우가 싫다면 아래와 같이 한다.

CREATE OR REPLACE VIEW EMP_VIEW20
AS
SELECT EMPNO, ENAME, DEPTNO FROM EMP
WHERE DEPTNO = 20 WITH CHECK OPTION;

UPDATE EMP_VIEW20 SET DEPTNO=40
WHERE EMPNO=7566;

명령의 1,776 행에서 시작하는 중 오류 발생 -
UPDATE EMP_VIEW20 SET DEPTNO=40
WHERE EMPNO=7566
오류 보고 -
ORA-01402: view WITH CHECK OPTION where-clause violation

 

- 잠깐!) 기사 시험용
- WITH READ ONLY : DML(데이터 조작어; INSERT, DELETE, UPDATE) 불가

CREATE OR REPLACE VIEW EMP_VIEW20
AS
SELECT EMPNO, ENAME, DEPTNO FROM EMP
WHERE DEPTNO = 20 WITH READ ONLY;

UPDATE EMP_VIEW20 SET ENAME='CHA'
WHERE EMPNO=7566;

명령의 1,785 행에서 시작하는 중 오류 발생 -
UPDATE EMP_VIEW20 SET ENAME='CHA'
WHERE EMPNO=7566
오류 발생 명령행: 1,785 열: 23
오류 보고 -
SQL 오류: ORA-42399: cannot perform a DML operation on a read-only view
42399.0000 - "cannot perform a DML operation on a read-only view"

 

- ROWNUM : 행번호 출력
- 행번호가 중요한 이유는 나중에 게시판 같은 것을 만들때

Q2. 가장 급여를 많이 받은 사원 3명만 검색

SELECT RANK, ENAME, SAL
FROM
(SELECT ROWNUM RANK, ENAME, SAL
FROM (SELECT ENAME, SAL
    FROM EMP
    ORDER BY SAL DESC))
WHERE RANK BETWEEN 1 AND 3;

 

선생님)

SELECT ROWNUM RANK, E.*
FROM (SELECT ENAME, SAL
    FROM EMP
    ORDER BY SAL DESC) E
WHERE ROWNUM<=3;

 

Q3. 가장 최근에 입사한 직원 5명 검색

SELECT RANK, ENAME, HIREDATE
FROM
(SELECT ROWNUM RANK, ENAME, HIREDATE
FROM (SELECT ENAME, HIREDATE
    FROM EMP
    ORDER BY HIREDATE DESC))
WHERE RANK BETWEEN 1 AND 5;

선생님)

SELECT ROWNUM RANK, E.*
FROM (SELECT ENAME, HIREDATE
    FROM EMP
    ORDER BY HIREDATE DESC) E
WHERE ROWNUM<=5;

- 결과 값을 하나만 찾고, 찾는 순간 멈추고 싶을때

SELECT * FROM EMP
WHERE ENAME='JONES' AND ROWNUM=1;

 

※ 참고

https://coding-factory.tistory.com/417

[Oracle] 오라클 뷰(View) 사용법 총정리 (생성,조회,수정,삭제)

 

- 시퀀스(SEQUENCE) : 자동 번호 발생기, 오라클 전용

- 은행 번호표기 같은 것
- 자동으로 상품번호 기입, 자동으로 LOT번호 기입 등의 활용이 가능하지 않을까?

CREATE SEQUENCE EMPNO_SQ
  INCREMENT BY 1 -- 1씩 증가
  START WITH 1000 -- 1000부터 시작
  MAXVALUE 9999 -- 최대값은 9999
  MINVALUE 0 -- 최소값은 0
  NOCYCLE -- 최대값에 도다르면 멈춤
  -- CYCLE -- 최대값에 도다르면 다시 1000으로 돌아감

ㄴ INCREMENT 이하는 옵션이다.

CREATE SEQUENCE EMPNO_SQ;

ㄴ INCEREMENT 이하는 생략하고 생성해보자.

 

- NEXTVAL, CURRVAL : 무조건 NEXTVAL을 먼저 한 후에 CURRVAL이 가능하다.

SELECT EMPNO_SEQ.NEXTVAL FROM DUAL; -- 카운팅(increments the sequence and returns the next value)

 

SELECT EMPNO_SEQ.CURRVAL FROM DUAL; -- 현재값(current value)

ORA-08002: sequence EMPNO_SEQ.CURRVAL is not yet defined in this session
08002. 00000 -  "sequence %s.CURRVAL is not yet defined in this session"
*Cause:    sequence CURRVAL has been selected before sequence NEXTVAL
*Action:   select NEXTVAL from the sequence before selecting CURRVAL

ㄴ 카운팅을 한번도 안하면 문제가 생긴다. CURRVAL 이전에 NEXTVAL을 먼저 실행해 준다.
ㄴ DB에 접속 후 되돌아오면 세션이 초기화 되어서 매번 NEXTVAL, CURRVAL을 실행해줘야한다.

 

- 사원번호를 자동으로 카운팅 하게 하고 싶다면?

테이블 및 컬럼 생성)

CREATE TABLE EMP6(
    EMPNO NUMBER, --PK
    ENAME NVARCHAR2(10),
    SAL NUMBER(9,0),
    CONSTRAINTS PK_EMPNO_EMP6 PRIMARY KEY(EMPNO)
);

 

데이터 입력)

-- DUMMY DATA
INSERT INTO EMP6 VALUES(1,'KIM',1000);
INSERT INTO EMP6 VALUES(2,'LEE',2000);
INSERT INTO EMP6 VALUES(EMPNO_SEQ.NEXTVAL,'CHA',3000);

 

확인)

SELECT * FROM EMP6;

1	KIM	1000
2	LEE	2000
4	CHA	3000
5	CHA	3000
6	CHA	3000

 

※ 참고) 시퀀스 편법 초기화(권장하지 않음)

SELECT EMPNO_SEQ.CURRVAL FROM DUAL; --현재 카운터 확인 6
ALTER SEQUENCE EMPNO_SEQ INCREMENT BY -5; -- 최소값은 1이니 -5
SELECT EMPNO_SEQ.NEXTVAL FROM DUAL;
ALTER SEQUENCE EMPNO_SEQ INCREMENT BY 1;

COMMIT;
SELECT EMPNO_SEQ.NEXTVAL FROM DUAL;

ㄴ 카운팅은 무조건 전진 ROLLBACK되지 않음 

 

- INDEX(인덱스) : 검색 속도를 향하기 위한 객체

- 도서관의 라벨 같은 역할
- PK, UNIQUE 제약조건의 컬럼은 자동으로 인덱스 생성

- 인덱스를 사용해야 되는 경우
1. ROW의 수가 많을 때
2. WHERE절에 해당 COLUMN을 사용할 때
3. 검색 결과가 전체 데이터의 2 ~ 4% 일때
4. JOIN에서 자주 사용라는 COLUMN일때

- 테이블을 삭제하지 않고 외래키만 삭제한다면?
-- DROP TABLE CART; -- 자식 테이블 삭제

DROP TABLE MEMBER CASCADE CONSTRAINTS;

-- 자식 테이블에서 참조하고 있기 때문에 자식을 우선 삭제한다.
-- 자식 테이블을 삭제하고 싶지 않다면 자식 테이블의 외래키만 삭제할 수 있다. 

SELECT * FROM CART; -- 자식 테이블 확인
SELECT * FROM USER_CONSTRAINTS;
CREATE TABLE MEMBER(
    NAME NVARCHAR2(10),
    TEL NCHAR(5),
    GENDER NCHAR(2),
    CONSTRAINT PK_NAME_TEL PRIMARY KEY(NAME, TEL)
);

INSERT INTO MEMBER VALUES('KIM', '11111','남자');
INSERT INTO MEMBER VALUES('LEE', '22222','여자');
INSERT INTO MEMBER VALUES('PARK', '33333','남자');

 

SELECT * FROM MEMBER
WHERE TEL='33333';

PK의 인덱스가 작동했다. 조합키중 하나인 NAME만 사용해도 가능하다.

PK의 인덱스가 작동했다.

순서를 뒤바꿔도 인덱스가 작동했다.

PK의 인덱스가 자동으로 설정되있지만 조합키의 경우 후자(TEL)만 선택할 경우 인덱스가 발동되지 않는다.

ㄴ PK에 설정된 인덱스는 예로 위처럼 PK의 첫번째인 이름만 사용하던가 둘다 사용하던가, 뒤바뀐 순서를 사용할 수 있지만 전화번호만 단독으로 사용한다면 인덱스가 적용되지 않는다.

 

- 사용자 관리

- 테이블스페이스 보기

https://becomefullstackdev.tistory.com/20

2021.07.01 수업일지

 

- 시스템 권한

1. 관리자가 가지는 권한
 1) CREATE USER : 사용자 생성 권한
 2) DROP USER : 사용자 삭제 권한

2. 일반 사용자에게 관리자가 부여하는 권한
 1) CREATE SESSION : 접속할 수 있는 권한
 2) CREATE TABLE : 테이블을 만들 수 있는 권한
 3) CREATE VIEW : 
 4) CREATE SEQUENCE :
 5) CREATE PROCEDURE : 프로시저(함수)를 만들 수 있는 권한

-- 관리자가 ICIA에게 VIEW 생성 권한 부여
GRANT CONNECT, RESOURCE, CREATE VIEW TO ICIA;

 

3. 일반 사용자의 시스템 권한 확인

SELECT * FROM USER_SYS_PRIVS;

-- 관리자에서 유저생성
-- 관리자가 ICIA2에게 권한 부여
-- 권한 부여 및 ICIA2 외에 다른 일반 사용자가 권한을 사용할 수 있게함
DROP USER ICIA2 CASCADE;
CREATE USER ICIA2 IDENTIFIED BY 1111;
GRANT CONNECT, RESOURCE, CREATE VIEW TO ICIA2 WITH ADMIN OPTION;
-- WITH ADMIN OPTION으로 관리자가 아닌 다른 사용자에게 관리자의 역할을 줄수 있다.

ALTER USER ICIA2 DEFAULT TABLESPACE USERS;
ALTER USER ICIA2 TEMPORARY TABLESPACE TEMP;

DROP USER ICIA2 CASCADE; -- 객체, 테이블 등 작성했던 내용들을 단계적으로 지울 수 있다.

ㄴ 11인자나 2인자 권한이 있는 SYSTEM에서 해야한다.

 

- 객체(TABLE, VIEW) 권한

1. ICIA2 생성

DROP USER ICIA2 CASCADE;
CREATE USER ICIA2 IDENTIFIED BY 1111;
GRANT CONNECT, RESOURCE, CREATE VIEW TO ICIA2 WITH ADMIN OPTION;

 

2. ICIA2에서 EMP TABLE으로 보고싶지만 권한이 없다.

-- ICIA2
SELECT * FROM EMP;

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
2행, 15열에서 오류 발생

 

3. ICIA에서 ICIA2에게 권한을 줘보자.

-- ICIA
-- 객체(TABLE, VIEW) 권한
SELECT * FROM ICIA.EMP;
GRANT SELECT ON EMP TO ICIA2;

 

4. ICIA2에서 검색이 가능한지 확인해보자.

-- ICIA2
SELECT * FROM ICIA.EMP; --검색할때 테이블의 소속을 명시해야한다.

ㄴ 오직 SELECT만 가능하다.

 

- 현재 USER가 다른 USER에게 부여해준 권한 확인

SELECT * FROM SYS.user_tab_privs_made;

 

- 현재 USER가 다른 USER에게 부여받은 권한 검색

-- ICIA2
-- 현재 USER가 다른 USER에게 부여받은 권한 검색
SELECT * FROM SYS.user_tab_privs_recd;

ICIA	EMP	ICIA	SELECT	NO	NO

ㄴ 권한을 추가해보자.

-- 객체(TABLE, VIEW) 권한
SELECT * FROM ICIA.EMP;
GRANT SELECT,INSERT,UPDATE ON EMP TO ICIA2;

ICIA	EMP	ICIA	UPDATE	NO	NO
ICIA	EMP	ICIA	INSERT	NO	NO
ICIA	EMP	ICIA	SELECT	NO	NO

ㄴ 확인 가능하다.

 

- REVOKE : 권환 회수

REVOKE INSERT, UPDATE ON EMP FROM ICIA2;

ICIA	EMP	ICIA	SELECT	NO	NO

 

- DB의 ROLE 권한 제어
- CONNECT, RESOURCE(일반 계정), DBA(관리자 계정 / 모든권한부여)

- 관리자로 부터 부여받은 롤 검색

SELECT * FROM SYS.USER_ROLE_PRIVS;

 

- 사용자가 롤 정의

CREATE ROLE MYROLE;
GRANT SELECT, INSERT, UPDATE,DELETE ON EMP TO MYROLE;

 

- 다른 사용자에게 롤(객체, 권한의 집합) 부여

GRANT MYROLE TO ICIA2;

- 부여한 롤의 권한 확인

SELECT * FROM SYS.ROLE_TAB_PRIVS;

- 부여된 ROLE확인

SELECT * FROM SYS.USER_ROLE_PRIVS;

- 부여된 ROLE 권한 회수

REVOKE MYROLE FROM ICIA2;

 

- 동의어(SYNONYM) : TABLE, VIEW 이런 객체들에 대한 별명

https://coding-factory.tistory.com/421

[Oracle] 오라클 시노님(Synonym) 사용법 총정리 (생성, 조회, 권한, 삭제)

 

-- 시노님 생성
CREATE SYNONYM E FOR EMP;

ㄴ EMP을 E로 명명하곘다.

-- SYS 에서 권한부여
GRANT CREATE SYNONYM TO ICIA;

 

SELECT *
FROM E
WHERE E.SAL>=2000;

ㄴ 이렇게 짧게 쓸 수 있게 된다.

DROP SYNONYM E;
DROP SYNONYM D;

ㄴ 삭제도 할 수 있다.

 

- PL/SQL : 트리거, 프로시져, 평션 ETC 中
- 트리거(TRIGGER)

CREATE OR REPLACE TRIGGER TRG
AFTER INSERT
ON EMP
BEGIN
    UPDATE EMP SET SAL=50;
END; -- 마우스 포인터는 이쪽에 올려 놓는다.
/ -- 슬래시는 커맨드에서 에디트 할때 메모장에 /로 마무리해야하기때문에 쓰는것이다.
-- 여기서는 트리거 생성 다음 쿼리와의 구분자 역할을 한다.

INSERT INTO EMP(EMPNO, ENAME) VALUES(9999,'CHA');
SELECT * FROM EMP;

-- 트리거 삭제
DROP TRIGGER TRG;
SELECT * FROM EMP;