DanStory

1. 사용법

 1) IF 조건 THEN

 2) ELSIF 조건 THEN

 3) ELSE 조건

 4) END IF;

2. 예시

DECLARE 

   v_ANSWER        NUMBER(5); 

BEGIN 


   SELECT COUNT(1) 
   INTO v_ANSWER 
   FROM DUAL; 
    
   IF v_ANSWER > 0 THEN 
      DBMS_OUTPUT.PUT_LINE('0보다 큼 v_ANSWER = '||v_ANSWER);  
   ELSIF v_ANSWER = 0 THEN 
      DBMS_OUTPUT.PUT_LINE('0 v_ANSWER = '||v_ANSWER); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE('0보다 작음 v_ANSWER = '||v_ANSWER); 
   END IF; 
    
END;

1. 오라클 컬럼명으로 테이블 찾기 -  ALL_TAB_COLUMNS 활용

SELECT *
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME = {컬럼명 입력};

2. 일부 소스로 프로시저명 찾기

SELECT *
FROM ALL_SOURCE
WHERE TEXT LIKE '%{일부 소스}%';

3. OBJECT 검색

SELECT *
FROM ALL_OBJECTS;

SELECT *
FROM USER_OBJECTS;

1. 프로시저를 보면 프로시저 상단에 변수설정을 확인
2. 해당 변수를 파라미터를 넣어서 실행
참고) 아래 예제 커서 내 변수 I_로 시작하면 insert parameter / O_로 시작하면 프로시저 실행 후 return값

실행 예제1) BATCHTEST_C_01 프로시저 실행하기

------------------------------------------------------------------------------- 
프로시저 : BATCHTEST_C_01
------------------------------------------------------------------------------- 
CREATE OR REPLACE PROCEDURE test01."BATCHTEST_C_01" ( 
   I_WRK_DT IN VARCHAR2 --작업일자    
) IS
BEGIN
...
END;
------------------------------------------------------------------------------- 

-> 이 경우 i_wrk_dt라는 varchar 파라미터 1개가 필요

DECLARE    


BEGIN 

     DBMS_OUTPUT.PUT_LINE('START');   --로그찍기

      test01.BATCHTEST_C_01('20200406'); 
      
     DBMS_OUTPUT.PUT_LINE('END');    --로그찍기 

END; 

-> 이렇게 SQL에 두고 실행 후 메세지 뜨는거 보고, 에러 메세지 없으면 정상실행한것.


실행예제2) return값에 cursor types가 있는경우

-------------------------------------------------------------------------------  
프로시저 : RETRIEVE_R_01
-------------------------------------------------------------------------------  

CREATE OR REPLACE PROCEDURE test01."RETRIEVE_R_01" ( 
    I_USER_ID               IN VARCHAR2, 
    O_RESULT              OUT TYPES.CURSORTYPE, 
    O_RTNCD               OUT VARCHAR2, 
    O_RTNMSG             OUT VARCHAR2 
) IS
BEGIN
...
END;

CURSORTYPE 변수 지정 후 실행

DECLARE    
rtn_cd1  varchar2(10); 
rtn_msg1 varchar2(1000); 
O_RESULT TYPES.CURSORTYPE; 

BEGIN 

     DBMS_OUTPUT.PUT_LINE('START!');   
      
     test01.RETRIEVE_R_01('test01',O_RESULT,rtn_cd1,rtn_msg1); 
      
     DBMS_OUTPUT.PUT_LINE('END!');    
END; 

아래 테이블이 있다고 가정

1. FETCH 사용

-------------------------------------------------------------------------------------------------
DECLARE

v_userId T_USER_INF.USER_ID%TYPE;      -- 사용자 id
v_userNm T_USER_INF.USER_NM%TYPE;  -- 사용자 이름

-- 테이블 내 선언된 데이터타입 맞춰주어야 함
-- v_id varchar2(10);     
-- v_name varchar2(10);

CURSOR C_TMP1 IS

        SELECT USER_ID,
                  USER_NM
        FROM  T_USER_INF;

BEGIN

        OPEN C_TMP1;   -- 1. 커서 오픈
        DBMS_OUTPUT.PUT_LINE('USER_ID '||' '||' USER_NM');  -- 디어깅용   
        
        LOOP
            FETCH C_TMP1 INTO v_id, v_name; --커서에서 데이터 가져오기
            EXIT WHEN C_TMP1 %NOTFOUND; --커서 속성
            DBMS_OUTPUT.PUT_LINE(v_camp_no||' '||v_cus_no);   -- 디버깅용 출력

        END LOOP;   
        
     CLOSE C_TMP1;    

END;
-------------------------------------------------------------------------------------------------

2. for문 사용

-------------------------------------------------------------------------------------------------
DECLARE

v_userId T_USER_INF.USER_ID%TYPE;      -- 사용자 id
v_userNm T_USER_INF.USER_NM%TYPE;  -- 사용자 이름

-- 테이블 내 선언된 데이터타입 맞춰주어야 함
-- v_id varchar2(10);     
-- v_name varchar2(10);

CURSOR C_TMP1 IS

        SELECT USER_ID,
                  USER_NM
        FROM  T_USER_INF;

BEGIN

        FOR REC IN C_TMP1
        LOOP
            DBMS_OUTPUT.PUT_LINE(REC.USER_ID);               
        END LOOP;

END;
-------------------------------------------------------------------------------------------------

차이는 FOR문의 경우 OPEN, CLOSE 작성 안해도 됨

3. CURSOR WHERE조건을 파라미터로 주고싶을때

* 테이블 내용이 아래와 같다고 가정.

-------------------------------------------------------------------------------------------------
DECLARE

v_userNm T_USER_INF.USER_NM%TYPE;  -- 사용자 이름

-- 테이블 내 선언된 데이터타입 맞춰주어야 함
-- v_id varchar2(10);     
-- v_name varchar2(10);

CURSOR C_TMP1(v_userId T_USER_INF.USER_ID%TYPE)

 IS
        SELECT USER_ID,
                  USER_NM
        FROM  T_USER_INF
        WHERE USER_ID = v_userId;

BEGIN

        FOR REC IN C_TMP1('test01')   --()안에 변수 입력
        LOOP 
            DBMS_OUTPUT.PUT_LINE(REC.USER_NM);               
        END LOOP;

END;
-------------------------------------------------------------------------------------------------

3예제는 WHERE USER_ID = 'test01'로 조회된 값 '테스트'가 나옴

오렌지에서 실행할경우 디버깅값 'DBMS_OUTPUT.PUT_LINE'는  Server OutPut에서 확인 가능

☆○@!! 프로시저로 돌리는게 아닌, DECLARE 선어해서 돌릴 경우 쿼리 내에 'COMMIT'을 꼭 해줘야 한다.
(프로시저의 경우 CREATE로 선언해주는 DDL이기 때문에 따로 COMMIT필요없음)

오라클 테스트환경 구축하기

※  아래 다운로드 설명은 개인적으로 오라클 공부하기 위한 용도이기 때문에 Express를 사용합니다.
Toad는 기호에 따라 설치 해도되고 안해도 상관없음

모든지 처음 환경구축하는게 제일 귀찮지만, 한번 설치 해두고 자유롭게 공부하자구요!
그럼 시작!!

1. 설치 전 준비

- 오라클 계정 : 오라클 설치를 위해선 오라클 계정이 필요하다.

2. 설치 시작

https://www.oracle.com/kr/downloads 접속 -> 데이터베이스 선택

3. 버전 선택 

현재 (글작성 기준은 2020.02.23) 18c ExpressEd

3.1. 최신버전은 부담부담 ㅎㅎ 그래서 이전 버전으로 선택함

4. 각자 컴퓨터에 맞는거 선택해서 다운로드

여기서 다운로드하려면 로그인 해야합니다. 그래서 사전 준비로 오라클 계정이 필요한겁니다!

5. 다운로드 완료 후 설치

설치는 알아서 하면 됩니다.  원하는곳에 알집풀기 - setup.exe 실행

※주의※
그리고 설치할때 비밀번호를 설정하는 부분이 있을텐데, admin으로 최초 로그인시 필요하기때문에 설정 후 잊어버리지 않도록 조심합시다!

6. 설치 완료 후 설정

설치가 완료되면 (Next만 눌러서 설치했다면) 아래 URl에서 "Run SQL Command line.exe" 실행

설치 항목이 바탕화면에 없다면 아래 URL 찾아보기C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Oracle Database 11g Express Edition

7. 계정 생성

아까 설치할때 설정한 비밀번호로 입력
SQL > connect system/{아까 설치할때 설정한 비밀번호}

* 테스트로 사용할 유저 생성 및 권한 부여

이제 Oracle은 설치 끝!

그런데, Command보다는 GUI를 통해 관리하는 것이 더 쉽다.
그래서 Toad라는 프로그램을 설치해서 연동 후 실습을 진행할 생각이다.

(Orange 등 다른 DB관리 툴 사용할거면 하고싶은대로 하면 됨)

8. Toad 설치

Toad는 유료지만, 학습을 위한 Freeware가 있는데 이걸 사용할 것이다.
홈페이지 접속 : www.toadworld.com

9. download -> Toad for Oracle -> Select 클릭

10. FreeWare 선택

9번을 실행하면 사진 10이 나오는데, 30일간 무료체험과 FreeWare 중 선택하는 화면이 나옴
하고싶은거 하면 됨. 물론 FreeWare는 기능이 제한되는것이 있다. 하지만 실습용으로는 그렇게 많이 필요치않으니 전 FreeWare를 선택

11. FreeWare를 설치하면, 개인정보를 입력하는 페이지가 나온다.

필수 정보를 입력하고나면 설치가 가능해진다.

12. Toad - Oracle 연동

.msi 파일을 눌러 설치하고, 실행 (FreeWare로 선택했는데 왜 30일 License가 나오지..? 나중에 확인 후 수정)
사진 6,7에서 생성한 계정 정보를 입력하고 TNS에는 XE를 선택 후 Connet를 눌러 접속되는지 확인한다.

13. 실행!

이렇게 접속 완료헀으면 사용하면 된다.

오늘은 여기까지 정리!

면접을 보고왔는데...

DB쪽 이론은 "그냥 말하면 되지 뭐"하는 상당히 멍청한 생각을 하고 준비를 안해서 당황했네요 ㅋㅋㅋ

복습을 안하니 전부 다 아는 내용인데 입안에서 단어만 맴돌다 헛소리만 하다 오는것 같아 멘탈이 가루가 되더군요

(=> 핑계임ㅎ 급 질문에 대답 못하면 '모른다'는 것이지요)

지난날의 과오를 반성하며 꼼꼼하게 공부하겠습니다 ㅋㅋㅋ

일단 오늘은 면접 가다가 지하철에서 읽어 볼 수 있도록 DB 용어만 간단히 정리하겠습니다.

Q. DB 정규화를 하는 이유는 ?

A. 데이터의 중복 및 이상 현상을 방지하기 위해서 정규화를 진행 합니다.

Q. DB 이상의 종류는 ?

A. 삽입이상, 삭제이상, 수정이상 이렇게 총 3가지 이상이 있습니다.

'삽입 이상'이란 원하지 않는 자료가 삽입되거나, 삽입을 하는데 자료가 부족해 정상적으로 삽입이 되지 않는 현상입니다.

'삭제 이상'이란 하나의 자료를 삭제하려고 하는데 그와 관련된 튜플 전체가 삭제되는 현상 입니다.

'수정(갱신) 이상'이란 자료를 수정하는데 관련된 다른 테이블의 내용이 수정되지 않아 정보가 모호해지는 현상 입니다.

Q. PK란 무엇입니까?

A. 테이블 내에서 튜플을 완벽하게 구분할 수 있는 유일한 속성(Attribute) 입니다.

Q. 다른 Key 종류 설명

A. Key는 총 5가지 종류가 있습니다.

후보키(Candinate Key)란 테이블 내에서 튜플을 완벽하게 구분할 수 있는 유일한 속성(Attribute)들의 부분 집합입니다. 유일성과 최소성을 만족해야 합니다. 

기본키(Prime Key)란 후보키 중 선택된 주 키 이며 Null 값을 가질 수 없습니다.

대체키(Alternate Key)란 후보키들 중에서 주 키가 아닌 나머지를 지칭 합니다.

슈퍼키(Super key) 테이블 내에서 하나의 튜플을 완벽하게 구분하기 위해 두개 이상으로 조합된 키 입니다. 유일성은 만족하지만 최소성은 만족시키지 못합니다. 

외래키 관계가 있는 R1,R2 테이블이 있고 R1에서 R2를 참조하려 할때 R1의 기본키와 대응되어 R2의 튜플을 구분할 수 있는 속성을 외래키라고 합니다.

/*이것은 개념 이해가 필요할 것 같아 따로 정리하기*/

Q. index Cluster가 무엇이고 이것의 중요성 좀 설명해주세요

A. Cluster index란 물리적으로 테이블의 데이터를 정렬한 것입니다.

Join과 같이 자주 사용되는 내용들을 클러스터 인덱스로 지정해 두면 데이 탐색 시간을 단축할 수 있습니다.

머릿속으로 말고 말로하는 연습하기!

DB 개념들 꼼꼼하게 정리하고, 할 수 있다면 간단한 예제도 같이 외워두기!

/*생각나는대로 내용들 지속적으로 추가하기*/

DB를 공부하다 보면 가장 이해하기 어려웠던 부분이 정규화 였습니다.

- 도메인 원자값 (1NF)

- 부분적 함수 종속 제거 (2NF)

- 이행적 함수 종속 제거 (3NF)

- 결정자이면서 후보키가 아닌 것 제거 (BCNF)

- 다치 종속 제거 (4NF)

- 조인 종속성 이용 (5NF)

정보처리기사를 공부하면서 암기했던 도부이결다조를 외웠지만 사실 이렇게 외우면 시간지나면 까먹기 마련입니다.

또 헷깔려서 여기저기 내용을 찾아보면 각 정규화 마다 다른 테이블로 진행하여 이해는 되지만 뭔가 찝찝한(?)기 분이들어

한개의 테이블을 정규화를 통해 변화해 가는 모습을 통해 쉽게 이해하고자 합니다.

그럼 시작!

* 제 1 정규화 (비정규형 -> 1NF)

그림1) 비정규형 테이블

제 1정규형을 갖추려면 도메인 원자값 이라는 조건이 만족해야 합니다.

도메인 원자값을 만족시킨다는 것은 아래와 조건과 같습니다.

1) 반복 그룹이 존재하면 안된다.

- Tom이라는 학생에 대한 정보인 이름,메일,대학,전공은 초기 설정하면 이후 계속 따라오게 됩니다.

그렇기 때문에 해당 정보는 수강 내역을 확인하기 위해서 계속 반복되기에 테이블을 분리합니다.

2) 모든 행은 식별자로 완전하게 구분되어야 한다.

그렇다면 이 비정규형 테이블 예제를 1정규화 시켜 보도록 정리해 보도록 하겠습니다.

1. 반복되는 부분을 체크합니다. -> 학번, 이름, 메일 ,대학, 전공 (Prime key : 학번)

2. 반복되는 부분과 그렇지 않은 부분을 분리합니다. (단, prime key는 그대로 가져갑니다.)

3. 테이블을 나눈 후 반복되는 행은 삭제합니다.

이 과정을 거치면 아래와 같은 테이블로 변경이 됩니다.

그림2)  제 1정규화 이후 테이블1

그런데, '학생' 테이블은 2번 조건인 "모든 행은 식별자로 완전하게 구분되어야 한다." 라는 조건이 만족하는 반면

수강내역은 prime key인 '학번'만으로는 모든 행이 구분되지 않습니다. 

그렇기 때문에 추가적으로 key를 설정 해 주어야 합니다.

그래서  '수강 내역' 테이블의 '학번'+'수강학기'+'과목명'을 복합하여 식별자로 두면서 문제를 해결하면 제 1 정규화는 마무리 됩니다.

* 제 1 정규형 테이블

그림3)  제 1정규화 이후 테이블2

* 제 2 정규화 - 부분함수 종속 제거

제 2정규형 조건을 만족하는 '학생' 테이블을 확인해보져

그림7) 제3 정규화 대상인 '학생' 테이블

위에서 보게되면 non key 컬럼인 '대학'과 '전공'을 보게되면 key가 아닌 컬럼인데도 불구하고

종속 관계가 성립이 됩니다.

'대학'의 경우 '전공'을 포함하기 때문입니다. 

즉, 생물학과면 무조건 이과대학에 포함되고, 컴퓨터 학과의 경우 IT대학의 부분으로 포함이 되지요.

정규화가 잘 된 테이블은 갑-을 관계만 있는 것 입니다.

그런데 '학생'테이블의 경우 갑(key:학번)-을(non-key:대학)-병(non-key : 전공) 관계가 있기 때문에  이 관계를 깔끔하게 정리 해주어야 합니다.

학번/이름/메일/전공  -  대학/전공으로 테이블이 나뉘게 됩니다.

그림8) 제3 정규화로 인해 생성된 '대학정보' 테이블

'대학정보' 테이블의 경우 대학이 전공을 포함합니다. 하지만 key는 테이블의 튜플을 구분해 낼 수 있어야 합니다.

그래서튜플을 구분하기 위해서는 '전공'이 key 값이 되겠지요.

'대학' 정보를 참조하기 위해서는 학생 테이블의 '전공'을 외래키(foreign key)로 설정하여 '대학정보' 테이블을 참조 할 수 있게 해야 합니다.

그래서 제 3정규화를 마친 '학생' 테이블은 다음과 같이 분리 하게 됩니다.

그림9) 제3 정규화를 마친 '학생' 테이블

* 결론!! 테이블 정리

그래서 비정규형 테이블이였던 '학생' 테이블을 제 3정규화까지 마치게 될 경우 아래와 같이 정리가 됩니다.

- 비정규형 테이블

그림10) 비정규형

- 제 1정규형 테이블 (제1 정규화) 

- 제 2정규화 테이블 (제2 정규화 이후)