22.03.15- 프로시저, 예외처리, 커서문, 패치문,loop,트리거

1.PL/SQL

---

1) 개념

– PL/SQL 블럭내에서 SQL의 DML 문과 쿼리문, 그리고 절차형 언어(IF, LOOP) 등을 사용하여 절차적으로 프로그래밍을 가능하게 하는 강력한 트랜잭션 언어이다.

​

2)장점

1.모듈화

2.변수선언

3.절차형 언어의 사용

4.에러처리

5.이식성

6.성능향상

​

3) 기본 블럭 구조

1.선언부(Declare)-변수,상수,cursor,exception 등 선언 (옵션)

2.실행부(Begin)-데이터 처리할 PL/SQL문 기술 (필수)

3.에러처리부(Exception)-에러 발생시 수행될 문장 기술 (옵션)

​

4)종류

1.SUBPROGRAM

- FUNCTION(함수) -반드시 RETURN 값 정의

- PROCEDURE(프로시저)

2. 자동실행 PROGRAM

-TRIGGER

3.복합 프로그램

-PACKAGE

​

이 중, 오늘 배운 내용인 FUCTION, PROCEDURE, TRIGGER에 대해 다뤄보고자 한다.

​

​

1.FUNCTION(함수)

---

구조

- IS와 BEGIN 사이 변수 선언(주로 v_로 표현)

-BEGIN과 END 사이 로직 구성 이때 =이 아닌 := 사용

-한문장 종료마다 ; 사용, IS/BEGIN/END 문장에 ; 사용

CREATE OR REPLACE FUNCTION 함수명
( 파라미터 타입    파라미터 데이터타입) -->생략가능
RETURN 데이터타입                     --> 반드시 기술
IS
 변수선언;
BEGIN
 RETURN(값);
EXCEPTION                            --> 생략가능
END;

ex)특정한 수에 세금을 7%로 계산하는 Function을 작성하기

함수 호출

- 일반 SELECT문과 동일하게 사용하여 확인한다.

-또는 함수 -> 해당 함수 우클릭 -> 실행 -> 값입력으로 확인

​

​

​

2.PROCEDURE(프로시저)

---

구조

-함수와 달리 RETURN 없음

CREATE OR REPLACE PROCEDURE 프로시저명
 (파라미터타입     파라미터 데이터타입 ) 
IS
 변수선언;
BEGIN
EXCEPTION                         -->생략가능
END ;

ex) dept 테이블에 Insert Procedure 생성하기

--여기서 dept.deptno%type: dept테이블에서 deptno의 data_type이 어떻든 똑같이 해줘라, 라는 뜻이다

--타입 같게 하는 이유: 레이아웃을 바꾸는 순간 해당 프로시저를 참조한 모든것에서 오류가 발생한다. 이때 이런식으로 선언하면 어떻게 바뀌든 유동적으로 자동으로 변하기 때문에 일일히 변경해줄 필요가 없어진다.

결과 확인

프로시저-> 해당 프로시저 우클릭 -> 실행 -> 값입력으로 확인

​

​

​

3.TRIGGER(트리거)

---

정의

어떤 사건이 발생 시 내부적으로 실행되도록 Db에 저장된 프로시저로, 이벤트 발생시 자동으로 실행된다.

– Oracle의 DML 문인 INSERT, DELETE, UPDATE이 실행되면 자동으로 실행된다.

​

ex) dept 테이블에서 지역명 변경 전후 보기

CREATE OR REPLACE TRIGGER trigger_test
BEFORE
UPDATE ON dept
FOR EACH ROW -- old, new 사용하기 위해
BEGIN
    DBMS_OUTPUT.ENABLE;
    DBMS_OUTPUT.PUT_LINE('변경 전 컬럼 값 : ' || :old.dname);
    DBMS_OUTPUT.PUT_LINE('변경 후 컬럼 값 : ' || :new.dname);
END;

UPDATE dept
SET loc = '신촌3'
WHERE deptno = 41;

결과 확인

상단 메뉴-> 보기 -> DBMS 출력 -> DBMS 출력 창에서 + 에서 scott선택하고 update 실행.

​