프로시저 / 함수 / 커서 - 페치 / 예외처리

프로시저

처리 후 반환 값이 여러 개

 

프로시저 생성

Create [Or Replace] Procedure 프로시저 명

(매개변수 명 [모드] 자료형,

.........................................)

IS

  자체변수 명 - 자료형,

...................................

BEGIN

- 실행부

EXCEPTION

- 예외처리

END;

 

프로시저 삭제

Drop Procedure 프로시저 명

 

ex) 학년을 압력으로 학생의 이름을 검색

 

CREATE OR REPLACE PROCEDEURE p_1(

v_stu_no IN student.stu_no%TYPE,

v_stu_name OUT student.stu_name%TYPE)

IS

BEGIN

  Select stu_name

    into v_stu_name

    from student

    where stu_no=v_stu_no;

END p_1;

 

프로시저  실행

declare

variable d_stu_name varchar2(12);

begin

execute p_1(20213075, :d_stu_name); :d_stu_name = bind 변수

end;

print d_stu_name;

 

---

함수

처리 후 반환 값이 하나.

 

함수 생성

Create [Or Replace] Finction 함수 명

(매개변수 명 [모드] 자료형,

...........................................)

RETURN 자료형

is

  자체 변수 - 자료형

BEGIN

  - 실행부

  return 변수

EXCEPTION

  - 예외처리

END;

 

함수 삭제

Drop Function 함수 명

 

ex) 성적 환산 함수

CREATE OR REPLACE FUNCTION f_1(

v_enr_grade in number)

RETURN CHAR

IS

  enr_score char;

BEGIN

  IF v_enr_grade >= 90 THEN enr_score := 'A';

  ELSIF v_enr_grade >= 80 THEN enr_score := 'B';

  ELSIF v_enr_grade >= 70 THEN enr_score := 'C';

  ELSIF v_enr_grade >= 60 THEN enr_score := 'D';

  ELSE enr_score := 'F';

  END OF;

  RETURN (enr_socre);

END f_1;

 

execute :d_score := f_1(95);

 

---

커서, 페치

select 문의 실행 결과가 복수행을 가질 경우 사용함.

 

커서의 생성

CURSOR 커서 명

IS 

SELECT 질의

[FOR UPDATE];

 

페치

커서에서 행을 하나씩 가져올때 사용.

 

ex) 101번 과목을 수강하는 학생의 학번과 성적을 확인

 

CREATE OR REPLACE PROCEDURE p_2

IS 

  v_stu_no enrol.stu_no%TYPE;

  v_stu_no enrol.sub_no%TYPE;

  v_enr_grade enrol.enr_grade%TYPE;

  CURSOR t_cursor IS

    SELECT stu_no, sub_no, enr_grade

    FROM enrol

    WHERE sub_no = 101;

BEGIN

  OPEN t_cursor;

  LOOP

    FETCH t_cursor INTO v_stu_no, v_sub_no, v_enr_grade;

    EXIT WHEN t_cursor%NOTFOUND;

    dbms_output.put_line(v_stu_no||'='||v_sub_no||'='||v_enr_grade);

  END LOOP;

  CLOSE t_cursor;

END p_2;

 

프로시저 실행

execute p_2;

다른 프로시저를 실행하느라 과목번호가 501이 되어버렸다...

 

커서의 속성

• %ROWCOUNT 속성
  • dbms_output.put_line('현재 '|| %ROWCOUNT ||'번째 행을 처리합니다.');
• %FOUND 속성
  • if %FOUND then
    dbms_output.put_line('데이터를 읽었습니다.’);
    end if;
• %NOTFOUND 속성
  • if %NOTFOUND then
    dbms_output.put_line(＇더 이상 읽을 데이터가 없습니다.’);
    end if;
• %ISOPEN 속성
  • if %ISOPEN then
    fetch t_cursor into v_stu_no, v_sub_no, v_cour_grade;
    else
    open t_cursor;
    end if

---

예외처리

에러발생, 예외적인 상황 발생

처리 조건명	설 명
NO_DATA_FOUND	검색문 사용 후, 결과가 있는지 여부 판단
NOT_LOGGED_ON	데이터 베이스에 연결 상태를 판단
TOO_MANY_ROWS	select 문에 into절을 사용한 경우 select 절의 결과가 복수 행일 경우
VALUE_ERROR	변수의 길이보다 큰 값을 저장하는 경우
ZERO_DEVIDE	열의 값을 0 값으로 나누는 경우
INVALID_CURSOR	커서 선언의 select 문에 대한 연산이 부적절한 겨웅
DUP_VAL_ON_INDEX	UNIQUE INDEX가 설정된 열에 중복 값이 입력하는 경우

 

 

ex) 학번에 의한 학생 이름 검색

CREATE OR REPLACE PROCEDURE p_3

(v_stu_no IN student.stu_no%TYPE)

IS

  v_stu_name student,stu_name%TYPE;

BEGIN

  Select stu_name

    Into v_stu_name

    From student

    Where stu_no = v_stu_no;

dbms_output.put_line(v_stu_name);

EXCEPTION

  When NO_DATA_FOUND Then

    dbms_output.put_line('해당 데이터가 없습니다.');

END p_3;

 

 

프로시저 실행

execute p_3(20213088);

execute p_3(20213089);

 

 

변수를 통한 예외처리

........

IS

  예외조건명 EXCEPTION;

.....

  IF 조건 then

    RAISE 예외조건명;

.....

  EXCEPTION

    When 예외조건명 Then

.....

END;