6장. PL/SQL Sub program

- 자주 사용되는 PL/SQL 블록에 저장해 두었다가 필요할 경우에 호출해서 사용할수 있도록 하는 프로그램.

- 프로시저(Procedure), 함수(Function), 패키지(Package), 트리거(Trigger)

- 익명블록

이름이 지정되지 않은 PL/SQL 블록

매번 사용시마다 컴파일

데이터베이스에 저장되지 않음

다른 응용프로그램에서 호출 불가능

값을 반환하지 않음

파라미터를 사용할수 없음

- 서브프로그램

이름이 지정된 PL/SQL 블록

최초 실행될때 한번만 컴파일됨

데이터베이스에 저장됨

다른 응용프로그램에서 호출 가능

함수일 경우 값을 반환

파라미터 사용 가능

---

1. 프로시저 (PROCEDURE)

- 특정 처리를 실행하는 서브프로그램의 한 유형

- 단독으로 실행되거나 다른 프로시저나, 툴 또는 다른 환경에서 호출되어 실행

- 처음 생성후 컴파일 할때 오브젝트로서 데이터베이스내에 저장

- 반복적으로 호출되어 실행될때 별도의 컴파일 없이 생성된 p-code로 바로 실행 가능

 

* 스크립트와 다른점

- @ 기호로 호출

- 실행 할때마다 컴파일한다 >> 부하가 걸릴 가능성이 있음

<프로시저 문법>

① OR REPLACE : 동일 프로시저명이 존재할 경우, 기존의 내용을 현재의 내용으로 수정 (삭제 후 다시 생성)

② 프로시저명 : 스키마 내에서 유일해야 한다.

③ Parameter 

호출환경과 프로시저간의 주고받을때 사용되는 매개변수, 데이터형 명시(크기는 X)

형식 파라미터 (Formal Parameter) : 선언부에 선언된 파라미터

실행 파라미터 (Actual Parameter) : 형식 파라미터에 실제 값이나 변수를 할당/대응 할때 사용하는 파라미터  

  EX) vempno IN emp.empno%TYPE --vempno 선언

④ 파라미터 모드 

IN : 사용자로부터 값을 입력받아 프로시저로 전달해주는 모드, 기본값, 생략가능

OUT : 다른 프로시저 등으로 전달하는 역할, 프로시저 내에서는 읽을수 없고 값을 저장하는 지역변수처럼 사용됨

IN OUT : 호출관경과 프로시저 간에 값을 주고받는 지역변수로 사용되며 읽기, 쓰기 모두 가능

IN	OUT	IN OUT
기본모드  값이 서브프로그램에 전달됨  형식 파라미터가 상수로 동작  리터럴, 표현식, 상수, 초기화된 변수로 사용가능  기본값을 할당할수 있음	명시적으로 지정  값이 호출환경에 반환  초기화 되지 않은 변수  변수만 사용가능  기본값 할당 불가	명시적으로 지정  값이 호출환경에 반환 + 서브프로그램에 전달  초기화된 변수  변수만 사용가능  기본값 할당 불가

⑤ USER_SOURCE : 프로시저 내용 확인 딕셔너리

⑥ SHOW ERRORS : 에러발생시 내용을 자세하게 바로 보여주지 않으므로 명령어 수행해야 함

실습1. emp테이블에서 부서번호가 20번인 사람들의 job을 'CLERK'으로 변경하는 프로시저

step1. emp 테이블에서 부서번호가 20번인 사람들 조회

step2. 부서번호가 20번인 사람들의 job을 'CLERK'으로 바꾸는 프로시저 생성후 실행

>> 부서번호가 20번인 사람들의 job이 모두 'CLERK'으로 바뀐것을 확인

실습2. 사번을 입력받아 급여를 인상하는 프로시저

(사용자로부터 값을 입력받아야 하므로 매개변수 사용)

step1. empno가 7902번인 사원 조회

>> sal : 3000

step2. 사원번호를 입력받을 변수를 선언한후 sal을 5000으로 변경하는 프로시저 작성

 >> 사원번호를 입력하여 프로시저 실행

step3. 프로시저 실행 후 입력받은 7902번 사원의 sal값이 변경되었는지 확인

실습3. 사번을 입력받아 그 사원의 이름과 급여를 출력하는 프로시저

실습4. OUT 모드 파라미터 사용 예

---

2. 함수 (Function)

---

3. 패키지 ( ORACLE Package )

---

4. 트리거 (Trigger)