[배운내용정리] 1228 자바 국비교육 _ Oracle

2020년 12월 28일 9시 ~ 15시 30분 zoom으로 수업 진행

 

---

 

SEQUENCE (시퀀스)

 1,2,3,4, 5... 숫자 데이터를 자동으로 카운트 하는 객체

 

 사용방법

CREATE SEQUENCE 시퀀스명 [INCREMENT BY 숫자] : 다음 값에 대한 증감 수치 , 생략하면 1씩 증가   --INCREMENT BY 5 : 5씩 증가   --INCREMENT BY -5 : 5씩 감소 [START WITH 숫자] : 시작 값, 생략하면 1부터 [MAXVALUE 숫자]   : 최대값 설정 [MINVALUE 숫자]   : 최소값 설정 [CYCLE|NOCYCLE]  : 값의 순환 여부(1 ~ 100 ~ ... 1 ~ 100) [CACHE 바이트크기|NOCACHE] : 값을 미리 구해 놓고 다음 값을 반영할 때 활용하는 설정  --기본 값 20Byte | 최소값 2Byte

시퀀스는 초기값을 설정했을 경우 변경할 수 없다. 지웠다가 다시 만들어야 한다.

 

 

INDEX (인덱스)

 조회처리속도를 향상시키기 위한 객체

장점

 검색 속도가 향상된다

단점

 테이블의 내용이 자주 변경되는 테이블이라면 인덱스를 매번 다시 계산해야 하기 때문에 오히려 성능의 저하를 유발할 수 있다.   인덱스를 저장하기 위한 별도의 공간을 할당해야 한다.

 

 

인덱스 생성 방법

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명[, 컬럼명 , ...])

 

 

인덱스 종류

1. 고유 인덱스 (UNIQUE INDEX)

2. 비고유 인덱스 (NOUNIQUE INDEX)

3. 단일 인덱스 (SINGLE INDEX)

4. 결합 인덱스 (COMPOSITE INDEX)

5. 함수기반 인덱스 (FUNCTION BASED INDEX)

 

고유 인덱스 (UNIQUE INDEX)

 인덱스 생성 시 고유값을 기준으로 생성하는 인덱스

 오라클에서 기본키로 제약조건을 사용하면 자동으로 생성되는 인덱스

 

비고유 인덱스 (NON_UNIQUE INDEX)

 내가 자주 사용하는 컬럼을 인덱스로 구성하여 검색속도를 향상시키기 위한 인덱스

 

함수기반 인덱스 (FUNCTION BASED INDEX)

 조회시에 자주 사용하는 함수식이 있다면 해당 함수식을 인덱스에 반영하여 생성

 

 

 

PL/SQL

 PROCEDURAL LANGUAGE EXTENSION TO SQL

 SQL에서 확장된 형태의 언어

 오라클 자체에 내장된 언어

 기존 SQL의 단점을 극복하기 위해 탄생~

 기능 : 번수선언, 비교처리, 반복처리

 

PL/SQL의 구조

선언부, 실행부, 예외처리부 로 구성

 선언부 : DECLARE

  변수를 선언하는 부분이다.

 실행부 : BEGIN

  제어문 , 반복문 , 함수 정의 등을 작성하는 부분

 예외처리부 : EXCEPTION

  예외 발생 시 처리할 내용을 작성하는 부분

 

뷰

 SELECT 문을 저장해서 필요할 때마다 사용

 

프로시저

 PL/SQL 문을 저장해서 사용

 

PL/SQL의 변수

 일반 변수

SQL에서 테이블 컬럼 선언할 때처럼 선언하는 방식

 

 레퍼런스 변수

%TYPE

 한 컬럼의 자료형을 받아올 때 사용하는 자료형 타입

%ROWTYPE

 행 전체의 데이터 타입 받아온다.

 

변수에 값 대입

오라클에서 = 는 같다라고 비교해주는 친구라 값을 담을 때는 := 를 사용한다.

 

---

 

--SEQUENCE : 시퀀스

CREATE SEQUENCE SEQ_EMPID
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;

SELECT SEQ_EMPID.NEXTVAL FROM DUAL;
--처음 실행하면 300
--두번 실행하면 305
--세번 실행하면 310나온다
--한번 더 실행하면 에러난다

--현재 시퀀스 값을 가져온다
SELECT SEQ_EMPID.CURRVAL FROM DUAL;

--시퀀스 정보가 들어있는 데이터 사전
SELECT * FROM USER_SEQUENCES;


--
CREATE SEQUENCE SEQ_EID
START WITH 300
INCREMENT BY 1
MAXVALUE 10000
NOCYCLE
NOCACHE;

SELECT * FROM USER_SEQUENCES;

SELECT * FROM EMPLOYEE;

INSERT INTO EMPLOYEE VALUES(SEQ_EID.NEXTVAL, '햄서터', '121203-1234567', 'HAM@EXAMPLE.COM', '01019921204', 'D2', 'J7', 'S1', 5000000, 0.1, 200, SYSDATE, NULL, DEFAULT);


--실습1.
--D9 부서에 J7 직급의 사원 4명을 추가
--EMP_ID는 시퀀스를 활용하고 나머지 컬럼 값은 임의로 작성하여 추가

--위에꺼 쓰려했는데 그냥 보기 편하라고 시퀀스 하나 더 만든것임;
CREATE SEQUENCE SEQ_EID_E1
START WITH 301
INCREMENT BY 1
MAXVALUE 10000
NOCYCLE
NOCACHE;

INSERT INTO EMPLOYEE VALUES(SEQ_EID_E1.NEXTVAL, '고앵이', '991212-2222222', 'CAT@EXAMPLE.COM', '01011111111', 'D9', 'J7', 'S1', 5000000, 0.1, 200, SYSDATE, NULL, DEFAULT);
INSERT INTO EMPLOYEE VALUES(SEQ_EID_E1.NEXTVAL, '갱얼쥐', '990218-1111111', 'DOG@EXAMPLE.COM', '01012121212', 'D9', 'J7', 'S1', 5000000, 0.1, 200, SYSDATE, NULL, DEFAULT);
INSERT INTO EMPLOYEE VALUES(SEQ_EID_E1.NEXTVAL, '애옹이', '960305-1111111', 'CAAAT@EXAMPLE.COM', '01011111111', 'D9', 'J7', 'S1', 5000000, 0.1, 200, SYSDATE, NULL, DEFAULT);
INSERT INTO EMPLOYEE VALUES(SEQ_EID_E1.NEXTVAL, '멍뭉이', '931222-3333333', 'DOOOG@EXAMPLE.COM', '01011111111', 'D9', 'J7', 'S1', 5000000, 0.1, 200, SYSDATE, NULL, DEFAULT);

SELECT * FROM EMPLOYEE;


--CYCLE : 시퀀스 값이 최소값 / 최대값에 도달했을 때 다시 반대의 값부터 시작하는 옵션
CREATE SEQUENCE SEQ_CYCLE
START WITH 200
INCREMENT BY 10
MAXVALUE 230
MINVALUE 15
CYCLE
NOCACHE;

SELECT SEQ_CYCLE.NEXTVAL FROM DUAL; --230 넘어가면 15가 된다 15에서 또 10씩 증가함


--CACHE / NOCACHE
--CACHE : 다음 값에 대한 연산들을 그때그때 수행하지 않고 미리 계산해 놓은 것
--NOCACHE : 수행값을 그때그때 처리하는 것
CREATE SEQUENCE SEQ_CACHE
START WITH 100
INCREMENT BY 1
CACHE 20
NOCYCLE;
--MAXVALUE를 지정하지 않으면 시퀀스가 가질 수 있는 최대값까지 설정된다.

SELECT SEQ_CACHE.NEXTVAL FROM DUAL;


--인덱스 생성
--CREATE [UNIQUE] INDEX 인덱스명
--ON 테이블명(컬럼명[, 컬럼명 , ...])

SELECT * FROM USER_IND_COLUMNS;

SELECT ROWID, EMP_ID, EMP_NAME FROM EMPLOYEE;


--고유 인덱스
--인덱스 생성 시 고유값을 기준으로 생성하는 인덱스
--오라클에서 기본키로 제약조건을 사용하면 자동으로 생성되는 인덱스

CREATE UNIQUE INDEX IND_EMP_NO
ON EMPLOYEE(EMP_NO);

CREATE UNIQUE INDEX IND_EMP_ID
ON EMPLOYEE(EMP_ID);
--에러난다
--ORA-01408: such column list already indexed
--만약 생성하고자 하는 인덱스가 이미 존재한다면 생성 불가

SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME='EMPLOYEE';


--cannot CREATE UNIQUE INDEX; duplicate keys found
--테이블에서 사용하는 컬럼 값이 중복 값이 존재할 경우
--UNIQUE INDEX로 사용할 수 없다.
CREATE UNIQUE INDEX IND_DEPT_CODE
ON EMPLOYEE(DEPT_CODE);


--NON_UNIQUE INDEX
--내가 자주 사용하는 컬럼을 인덱스로 구성하여
--검색속도를 향상시키기 위한 인덱스
CREATE INDEX IDX_DEPT_CODE
ON EMPLOYEE(DEPT_CODE);

SELECT EMP_NAME, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE DEPT_CODE='D6';


--결합 인덱스
CREATE INDEX IDX_DEPT
ON DEPARTMENT(DEPT_ID, DEPT_TITLE);

--0.003초 -> 0.001초로 속도가 줄어든다
SELECT DEPT_ID, DEPT_TITLE FROM DEPARTMENT
WHERE DEPT_ID != 'D1' AND DEPT_TITLE != '기술지원부';


--함수 기반 인덱스
--조회시에 자주 사용하는 함수식이 있다면
--해당 함수식을 인덱스에 반영하여 생성
CREATE INDEX IDX_EMP_SAL_CAL
ON EMPLOYEE( (SALARY * NVL(BONUS, 0) + SALARY)*12 );


SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE (SALARY * NVL(BONUS, 0) + SALARY)*12 > 10000000;


--DML 작업 수행 후에는 
--해당 INDEX를 다시 생성해야한다

--인덱스 새로고침
ALTER INDEX IDX_DEPT REBUILD;

 

---

PL/SQL 시작

--PL/SQL

--실행부를 사용해서 간단한 문장 출력
BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
END;
/

--기본 값 OFF
--접속 종료하면 다시 초기화 된다.
SET SERVEROUTPUT ON;


--변수의 선언과 초기화 , 변수값 출력
--[1] 일반 변수
DECLARE
    vid NUMBER;
BEGIN
    SELECT EMP_ID
    INTO vid --변수에 조회한 값을 담는다. 
    FROM EMPLOYEE
    WHERE EMP_NAME='선동일1';
    
    DBMS_OUTPUT.PUT_LINE('ID='||vid);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA!');
END;
/


DECLARE
    v_empno NUMBER(4);
    v_empname varchar2(10);
    test_num NUMBER(10) := 10*20;
BEGIN
    --변수에 값 대입
    --오라클에서 = 는 같다라고 비교해주는 친구라 값을 담을 때는 := 
    v_empno := 1001;
    v_empname := '유관순';
    
    DBMS_OUTPUT.PUT_LINE('id = ' || v_empno || ' name = ' || v_empname);
    DBMS_OUTPUT.PUT_LINE('test_num = ' || test_num);
END;
/


--[2] 레퍼런스 변수
--특정 테이블 컬럼의 타입을 참조해서 변수를 만들겠다!
--(1) %TYPE : 한 컬럼의 자료형을 받아올 때 사용하는 자료형 타입
DECLARE
    EMP_ID EMPLOYEE.EMP_ID%TYPE;
    EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY
    INTO EMP_ID, EMP_NAME, SALARY
    FROM EMPLOYEE
    WHERE EMP_NAME = '선동일';
    
    DBMS_OUTPUT.PUT_LINE('id = ' || EMP_ID || ' name = ' || EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('SALARY = ' || LTRIM(TO_CHAR(SALARY, 'L99,999,999')));
END;
/

--(2) %ROWTYPE : 행 전체의 데이터 타입을 받아온다
DECLARE
    myrow EMPLOYEE%ROWTYPE;

BEGIN
    SELECT EMP_ID, EMP_NAME
    INTO myrow.EMP_ID, myrow.EMP_NAME
    FROM EMPLOYEE
    WHERE EMP_NAME='선동일';
    
    DBMS_OUTPUT.PUT_LINE('id = ' ||  myrow.EMP_ID || ' name = ' || myrow.EMP_NAME);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA!');
END;
/


--* 와 ROWTYPE 변수를 이용해서 한 행을 통째로 담기

DECLARE
    myrow EMPLOYEE%ROWTYPE;

BEGIN
    SELECT *
    INTO myrow
    FROM EMPLOYEE
    WHERE EMP_NAME='&EMP_NAME'; --EMP_NAME이란 변수에 사용자가 입력한 데이터를 담겠다는 의미 , 선동일 입력 시 밑에 코드와 동일한 결과 나옴
    --WHERE EMP_NAME='선동일';
    
    DBMS_OUTPUT.PUT_LINE('id = ' ||  myrow.EMP_ID || ' name = ' || myrow.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('DEPT_CODE = ' ||  myrow.DEPT_CODE);
EXCEPTION
    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA!');
END;
/