[본문스크랩] PL/SQL (10) - 프로시저 (Procedure)

###################################################################################################

#   source site : blog.naver.com/tangamjaelt

#   master : 강용운

#   email : tangamjaelt@korea.com

#   last release : 2007.06.03

###################################################################################################

 

프로시저 (procedure) 와 패키지 (package)

- 프로그램 유지 보수와 실행을 보다 쉽게 하기 위해서 프로그램 코드를 논리적으로 구성한 것.

- 1개 패키지 내에 여러개의 프로시저가 존재한다.

 

스토어드 프로시저 (stored procedure)

- 데이터베이스 내에 컴파일되어 저장된 프로시저.

- 데이터베이스에 저장된 프로시저는 오브젝트로 관리된다.

 

프로시저를 사용하는 이유

- 특정 문제나 작업을 해결하기 위해서 사용한다.

- 특정 기능을 담당하는 모듈(module) 단위로 되어 있다.

- 재사용이 가능하다.

- 스토어드 프로시저를 통해서만 데이터를 액세스할 수 있어 보안이 향상된다.

- 프로시저는 공유된 메모리 자원을 이용한다.

 

프로시저와 함수

- 둘은 출력의 형식이 다르다.

- 프로시저 : 많은 양의 정보를 처리한다.

- 함수 : 하나의 값을 Return 한다.

 

프로시저 생성

- 프로시저를 생성할 때 오라클은 컴파일하는 동안 접근하는 오브젝트(Table,View 등)가 존재하는지 검사하고, 접근 권한이 있는지를 검사한다.

********************************************************************************************************************

CREATE OR REPLACE PROCEDURE 프로시저이름 (IN/OUT/INOUT)

AS

     PL/SQL 본문코드

END 프로시저이름

********************************************************************************************************************

- OR REPLACE : 프로시저가 이미 존재할 경우 덮어 쓴다. 항상 사용하는 것이 좋다.

- IN : 호출되는 프로시저에 값을 전달한다는 것을 지정

- OUT : 프로시저를 호출한 프로그램에게 값을 반환한다는 것을 지정

- INOUT : IN + OUT. 프로시저에 값을 전달하고 결과를 프로그램에게 반환한다는 것을 지정.

 

RETURN 문

- 프로시저가 끝나기 전에 마칠 경우에만 사용한다.

 

컴파일 / 재컴파일

- 스토어드 프로시저를 명시적으로 재컴파일 하려면 ALTER PROCEDURE 명령을 사용한다.

- ALTER PROCEDURE 는 패키지 내에 있는 프로시저가 아닌 독립된 프로시저에서만 사용 가능하다.

- 프로시저가 컴파일되면, 다음 실행시 컴파일 할 필요가 없어 부하를 줄일 수 있고, 컴파일 오류를 제거할 수 있다.

- 오라클은 부모 오브젝트가 명시적으로 다시 컴파일될 때 종속된 모든 오브젝트를 자동으로 재컴파일 한다. 따라서 부모 오브젝트를 컴파일 할 경우 관련된 모든 오브젝트가 모두 다시 컴파일이 일어나므로 시스템에 부하를 줄 수 있다.

 

프로시저 실행

- execute 명령으로 스토어드 프로시저를 실행할 수 있다.

 

[예제1]  사용자가 지정한 나이를 가진 사람의 나이를 100 으로 변경하는 스토어드 프로시저

********************************************************************************************************************

CREATE OR REPLACE PROCEDURE change_ages (i_age IN integer)
AS
BEGIN
        UPDATE member
        SET AGE=100
        WHERE age = i_age;
END change_ages;
/
********************************************************************************************************************

 

 

- 프로시저 실행

 

 

- 결과 확인 (62  -> 100 살로 변경)

 

 

 

파라미터

- 프로시저는 파라미터를 사용하여 정보를 전달한다.

- 프로시저를 호출할 때는 프로시저의 파라미터에 대해 값을 전달해야 한다.

- Actual Parameter : 프로시저에 전달된 파라미터 (실제 파라미터)

- Internal or Formal Parameter : 프로시저 내부에 선언된 파라미터 (내부 또는 형식 파라미터)

- Actual Parameter 의 데이터형과 Internal Parameter 의 데이터형이 같아야 한다.

 

 

스토어드 프로시저 확인

- 스토어드 프로시저에 관한 정보를 제공하는 데이터 딕셔너리 뷰

 

ALL_ERRORS : 현재 사용자가 액세스 가능한 모든 오브젝트에 대한 오류 목록

ALL_SOURCE : 현재 사용자가 액세스 가능한 모든 오브젝트의 텍스트 소스

 

USER_OBJECTS : 현재 사용자가 액세스 가능한 모든 오브젝토 목록

USER_ERRORS : 현재 사용자에게 속한 오브젝트의 오류 목록

USER_OBJECT_SIZE : 현재 사용자의 모든 PL/SQL 오브젝트

USER_SOURCE : 현재 사용자에게 속한 모든 오브젝트의 텍스트 소스

 

DBA_OBJECTS : 데이터베이스에 저장된 모든 오브젝트 목록

DBA_ERRORS : 데이터베이스에 저장된 모든 오브젝트에 대한 오류 목록

DBA_OBJECT_SIZE : 데이터베이스에 있는 모든 PL/SQL 오브젝트

DBA_SOURCE : 데이터베이스 전체에 대한 정보

 

- 어떤 오브젝트가 INVALID 상태인지 확인하고 다시 컴파일 할 필요가 있는지 확인할 수 있다.

- INVALID 인 함수나 프로시저는 잘못된 구문이 있을 수 있다.

 

SQL> select object_name, object_type, status from user_objects where status = 'INVALID';

 

 

 

프로시저 삭제

DROP PROCEDURE 프로시저이름;

 

프로시저 오버로딩

- 한 패키지에서 동일한 이름의 프로시저에 각각 다른 인수를 사용해서 호출할 수 있다.

- 서로 다른 데이터형을 갖고 있는 인수를 가지고 동일한 프로시저를 여러 번 실행해야할 경우 유용하다.

 

재귀 프로시저

- 자기 자신을 호출하는 프로시저