9강 - DDL프로시져 / 함수 / 예외처리 / CURSOR

- USER : LEE

 

1. DDM 프로시져

a.INSERT 프로시져

 CREATE OR REPLACE PROCEDURE 프로시져명

 (값1 TYPE, 값2 TYPE…)

 IS

 BEGIN

 INSERT INTO 테이블명(컬럼명1, 컬럼명2…)

 VALUES (값1, 값2..);

 COMMIT;

 END;

 

 EXECUTE 프로시져명(값1, 값2..);

b.UPDATE 프로시져  

 CREATE OR REPLACE PROCEDURE 프로시져명

 (값1 TYPE, 값2 TYPE…)

 IS

 BEGIN

 UPDATE 테이블명 SET 컬럼명=값1,컬럼명=값2…;

 COMMIT;

 END;

 

 EXECUTE 프로시져명(값1, 값2..);

c.DELETE 프로시져

  CREATE OR REPLACE PROCEDURE 프로시져명

(값1 TYPE)

 IS

 BEGIN

 DELETE FROM 테이블명  WHERE 컬렴명=값1;

 COMMIT;

 END;

 

 EXECUTE 프로시져명(값1);

2. 함수

CREATE OR REPLACE FUNCTION 함수명

(값1 IN TYPE, 값2 IN TYPE..)

RETURN 리턴값의TYPE

IS

리턴값명 리턴값의TYPE;

 

BEGIN

서식(EX,값1:=값1+값2;)

RETURN 리턴값명;

END;

a.반환값이 있다.

 

 

3. 함수문제

a.부피를 구하는 함수

b.입력 받은 날짜의 말일 구하기

c.이름을 입력받아 성을뺀 이름만 반환

d.입사일을 입력 받아 사원명,입사일,근무기간(6년2개월)을 출력

e. 등급 구하기

 

 

4. 반복문

a. LOOP문 - do while문과 비슷

   = LOOP

  반복할 부분

  EXIT WHEN LOOP문의 조건식;

  END LOOP;

 

b. FOR문

   = FOR I IN 시작할숫자..끝나는 숫자 LOOP

   반복할 부분

   END LOOP;

 

c. WHILE문

   = WHILE 조건식

  반복할 부분

  END LOOP;

 

 

5. 예외 처리

EXCEPTION

 

WHEN TOO_MANY_ROWS THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('데이터가 너무 많습니다!!');

WHEN NO_DATA_FOUND THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('데이터가 없습니다!!');

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('데이터 중복');

WHEN OTHERS THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('기타 에러입니다!!');

6. 사용자 정의 에러

IS

예외처리명 EXCEPTION; --에러처리 변수 선언

 

BEGIN

IF 조건식                            --에러처리 조건

RAISE 에러처리명;

 

EXCEPTION

WHEN 예외처리명 THEN

RAISE_APPLICATION_ERROR(에러번호,에러메세지);  --에러처리 방법

END;

 

 

7. CURSOR

a. 여러줄의 레코드를 집어 넣을 수 있는 기능

   ** 하나의 데이터 = %TYPE

   ** 하나의 레코드 = %ROWTYPE

b. 선언 -> OPEN -> FETCH -> CLOSE

c. 원래는 - IS위에는 매개변수 선언

   - IS밑에는 BEGIN안에서 사용하는 변수 선언

   ** 커서선언할때 => 내부에 또 IS가 있다

d. 커서가 오픈되어있을때만 사용가능한 커서의 시스템변수

 

 

8. CURSOR - LOOP

= 선언 -> OPEN -> FETCH -> CLOSE

= 구조

IS

     CURSOR 커서명 --1. 선언

     IS

     SELECT 컬럼명1,컬럼명2.. FROM 테이블명

     WHERE 조건절;

 

변수1 TYPE;

변수2 TYPE;

…

 

BEGIN

OPEN 커서명; --2. 오픈

LOOP

FETCH 커서명 INTO 변수1,변수1...;

     --3. 커서의 내용을 꺼내와서 변수에 넣기

 

EXIT WHEN 커서명%NOTFOUND; --커서에 값이 없으면 LOOP 나가기

 

DBMS_OUTPUT.PUT_LINE(TO_CHAR(변수명1)||TO_CHAR(변수명2)...);

            --커서의 변수 출력

END LOOP;

 

DBMS_OUTPUT.PUT_LINE(TO_CHAR(CUR_DEPTNO%ROWCOUNT));

--ROWCOUNT = 커서의 레코드 갯수

 

CLOSE CUR_DEPTNO; --4. 닫기

 

END;

9. CURSOR - FOR

= 스스로 OPEN,FETCH,CLOSE를 다해서 따로 코딩해줄 필요 없다(선언만 코딩)

= 구조

IS

     CURSOR 커서명 --1. 선언

     IS

     SELECT 컬럼명1,컬럼명2.. FROM 테이블명

     WHERE 조건절;

 

변수1 TYPE;

변수2 TYPE;

…

 

BEGIN

 

FOR 반복변수 IN 커서명 LOOP

 

DBMS_OUTPUT.PUT_LINE(TO_CHAR(반복변수.컬럼명1)

||TO_CHAR(반복변수.컬럼명2)...);

 

레코드갯수변수 := 커서명 %ROWCOUNT;

--커서가 닫히기 전에 ROWCOUNT를 사용해야 하기 때문에 미리 넣어주기

 

END LOOP;

 

DBMS_OUTPUT.PUT_LINE('전체 데이터 갯수: '||TO_CHAR(V_CNT));

 

END;