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. 실행!

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

오늘은 여기까지 정리!