Database 기본9 Squence, View, Index, PL/SQL

----------------------- 2021_03_15
 
-- DROP TABLE 테이블명 --> 참조되고 있는 테이블은 함부로 지울 수 없다.
CREATE TABLE REF_TEST(
    EMP_ID VARCHAR2(20) REFERENCES MEMBER(USER_ID),
    EMP_NAME VARCHAR2(20)
);
 
DROP TABLE MEMBER; --참조중인 테이블이라 삭제 불가
-- DROP TABLE 테이블명 CASCADE CONSTRAINTS; -- 제약조건 삭제하고 테이블 삭제
 
-- DCL : DATA에 대한 접근권한 설정. 권한 회수/트랜잭션 처리
-- GRANT. REVOKE : 권한부여, 회수
-- GRANT 권한명||ROLE명, 권한명,권한명,..... TO 사용자명
-- GRANT 권한명 ON 테이블 TO 사용자명 / 다른사용자 소유의 테이블 접근 가능하게 만들 수 있음
 
CREATE USER QWER IDENTIFIED BY QWER;
GRANT CREATE SESSION TO QWER;
GRANT CREATE TABLE TO QWER;
ALTER USER QWER DEFAULT TABLESPACE USERS;
ALTER USER QWER QUOTA UNLIMITED ON USERS;
 
-- 권한 확인하기
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE='QWER';
 
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE='KH';
 
SELECT * FROM USER_ROLE_PRIVS
WHERE USERNAME='KH';
 
--ROLE에 부여된 권한 확인
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE='RESOURCE';
 
CREATE ROLE MYROLE;
GRANT CREATE TABLE, CREATE SESSION TO MYROLE;
 
-- QWER 계정에 권한 부여해서 KH소유 테이블에 접근하게 하기
SELECT * FROM EMPLOYEE; -- 다른 계정 테이블 접속하는건 계정명.테이블명 으로 적어야 한다. 
SELECT * FROM KH.EMPLOYEE;  -- 이렇게
 
GRANT SELECT ON KH.EMPLOYEE TO QWER;
 
GRANT INSERT ON KH.EMPLOYEE TO QWER; -- KH.EMPLOYEE에 삽입할 권한을 QWER에게 준다~ 이말이다
INSERT INTO KH.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,JOB_CODE,SAL_LEVEL) VALUES('888','GOM','999999-9999999','J5','S5');
 
-- 한번에 여러 권한 부여
GRANT SELECT, INSERT, UPDATE ON KH.DEPARTMENT TO QWER;
 
-- 부여된 권한 회수
REVOKE SELECT ON KH.EMPLOYEE FROM QWER;
 
CREATE TABLE TBL_USER(NAME VARCHAR2(20));
 
-- 트랜잭션 : 작업단위
SELECT * FROM TBL_USER;
GRANT SELECT ON KH.TBL_USER TO QWER;
 
-- VIEW : 가상의 테이블로, SELECT문 사용해서 실제 테이블의 데이터를 이용
-- VIEW도 하나의 ORACLE OBJECT 이라서 DDL로 생성 수정 삭제 가능
-- CREATE VIEW 뷰이름 AS SELECT문
CREATE VIEW  V_EMPALL AS SELECT * FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
JOIN JOB USING(JOB_CODE); -- VIEW에 대한 생성권한이 없어서 안됨.
 
GRANT CREATE VIEW TO KH; -- 권한부여 하면 위에 쿼리문 가능
SELECT*FROM V_EMPALL;
 
SELECT EMP_NAME, DEPT_TITLE, JOB_NAME, SALARY
FROM V_EMPALL;
 
UPDATE EMPLOYEE SET DEPT_CODE='D1' WHERE EMP_NAME='송종기'; -- 실제 테이블 수정시 뷰에도 반영됨.
 
CREATE VIEW V_EMP_AVG
AS SELECT DEPT_CODE, FLOOR(AVG(SALARY)) AS 평균
FROM EMPLOYEE
GROUP BY DEPT_CODE
UNION
SELECT JOB_CODE, FLOOR(AVG(SALARY)) AS 평균
FROM EMPLOYEE
GROUP BY JOB_CODE
UNION
SELECT '총평균', FLOOR(AVG(SALARY)) AS 평균
FROM EMPLOYEE;
 
-- VIEW 삭제
DROP VIEW V_EMPALL;
 
SELECT * FROM V_EMPALL;
 
-- OR REPLACE : 이미 그 이름의 VIEW가 있으면 덮어쓰기
CREATE OR REPLACE VIEW V_EMP
AS SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID;
 
-- FORCE/NOFORCE : 실제테이블이 없어도 VIEW를 생성할 수 있게 함
CREATE FORCE VIEW V_TEMP
AS SELECT * FROM TT;
 
-- WITH CHECK OPTION : SELECT문에서 WHERE절에 사용한 컬럼은 수정못하게 하는 옵션
CREATE OR REPLACE VIEW V_EMP_SAL
AS SELECT EMP_NAME, DEPT_CODE, SALARY, SALARY*12 AS 연봉
FROM EMPLOYEE WHERE DEPT_CODE='D5' WITH CHECK OPTION; -- UPDATE로 수정 안됨.
 
-- SEQUENCE : 순차적으로 정수값을 자동으로 생성하는 객체
-- CREATE SEQUENCE 시퀀스명[옵션]
-- 시퀀스작동 예약어 : 시퀀스명.NEXTVAL / 시퀀스명.CURRVAL
CREATE SEQUENCE SEQ_BASIC;
SELECT SEQ_BASIC.NEXTVAL FROM DUAL;
SELECT SEQ_BASIC.CURRVAL FROM DUAL;
 
CREATE SEQUENCE SEQ_OPTION
START WITH 100
INCREMENT BY 10;
 
SELECT SEQ_OPTION.NEXTVAL FROM DUAL;
 
-- 조회할때마다 6이 늘어난다. 최대 500, 60시작
CREATE SEQUENCE SEQ_OPTION2
START WITH 60
INCREMENT BY 6
MAXVALUE 500
MINVALUE 0
CYCLE
NOCACHE;
 
SELECT SEQ_OPTION2.NEXTVAL FROM DUAL;
 
-- PK 값으로 설정하기
CREATE TABLE BOARD_SEQ(
BOARD_NO NUMBER PRIMARY KEY,
BOARD_TITLE VARCHAR2(200) NOT NULL,
BOARD_CONTENT VARCHAR2(800),
BOARD_WRITER VARCHAR2(20) REFERENCES EMPLOYEE(EMP_ID),
BOARD_DATE DATE
);
 
CREATE SEQUENCE SEQ_BOADE;
INSERT INTO BOARD_SEQ VALUES(SEQ_BOARD.NEXTVAL, '제목1', '내용1', '200', SYSDATE);
SELECT * FROM BOARD_SEQ;
 
-- SEQUNCE 를 사용할 수 없는 구문
 
-- INDEX : 전체적인 DBMS 성능향상을 위해 설정
-- CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명, 컬럼명||함수계산식);
SELECT * FROM USER_INDEXES;
 
CREATE INDEX IND_EMPLOYEE ON EMPLOYEE(EMP_NAME);
 
DROP INDEX IND_EMPLOYEE;
 
-- 여러 컬럼에 인덱스 부여
CREATE INDEX IND_EMPLOYEE_COM ON EMPLOYEE(EMP_NAME, DEPT_CODE);
 
SET SERVEROUTPUT ON;
BEGIN
    DBMS_OUTPUT.PUT_LINE('첫 PL/SQL');
END;
/
 
-- 기본 익명블록
DECLARE
    V_ID VARCHAR2(20);
BEGIN
    SELECT EMP_ID
    INTO V_ID
    FROM EMPLOYEE
    WHERE EMP_NAME='선동일';
    
    DBMS_OUTPUT.PUT_LINE(V_ID);
END;
/
 
-- 변수 이용
-- 선언 : 변수명 자료형(길이);
-- 변수에 값 넣기 -> 변수명:=값
DECLARE
    V_EMPNO NUMBER;
    V_EMPNAME VARCHAR2(20);
    TEST_NUM NUMBER := 10+20; -- 선언과 동시에 초기화
BEGIN
    V_EMPNO := 300;
    V_EMPNAME := 'GOM';
    DBMS_OUTPUT.PUT_LINE(V_EMPNO);
    DBMS_OUTPUT.PUT_LINE(V_EMPNAME);
    DBMS_OUTPUT.PUT_LINE(TEST_NUM);
    END;
    /
    
-- 변수 자료형 알아보기
-- 기본자료형 : NUMBER, VARCHAR2, DATE, BOOLEAN, BINARY_INTEGER
-- 복합자료형 : 레코드, 커서, 컬렉션
 
-- 참조형 변수
-- %TYPE : 기존테이블에 있는 자료형을 참조해서 가져와 사용하는 것
DECLARE
    V_EMP_ID EMPLOYEE.EMP_ID%TYPE;
    V_SALARY EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT EMP_ID, SALARY
    INTO V_EMP_ID, V_SALARY
    FROM EMPLOYEE
    WHERE EMP_ID='&사원명';
    DBMS_OUTPUT.PUT_LINE(V_EMP_ID||V_SALARY);
END;
/
 
-- 레코드 : 클래스
DECLARE
    TYPE MY_RECORD IS RECORD(
        V_ID EMPLOYEE.EMP_ID%TYPE,
        V_NAME EMPLOYEE.EMP_NAME%TYPE,
        V_DEPTTITLE DEPARTMENT.DEPT_TITLE%TYPE
    );
    MY_VAR MY_RECORD;
BEGIN
    SELECT EMP_ID, EMP_NAME, DEPT_TITLE
    INTO MY_VAR
    FROM EMPLOYEE
    JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
    WHERE EMP_ID='&사원번호';
 
    DBMS_OUTPUT.PUT_LINE(MY_VAR.V_ID||' '||MY_VAR.V_NAME||' '||MY_VAR.V_DEPTTITLE);
END;
/