[오라클 프로시져(Stored Procedure)]

//*****************************************

-- 프로시져(Stored Procedure)

   - PL/SQL에서 가장 대표적인 구조인 스토어드 프로시져는 개발자가 자주 실행해야 하는 업무 흐름(sql)을 미리 작성하여 데이터베이스 내에 저장해 두었다가 필요할 때마다 호출하여 실행할 수 있다.

   - 패키지 내에서 프로시져는 중복 정의가 가능하다.

---------------------------------------------

-- 사용자에게 프로시져를 만들 수 있는 권한이 없는 경우 권한 설정(RESOURCE 롤에 기본적으로 프로시져를 만들수있는 권한 있음)

-- SYS 계정

-- 사용자에게 프로시져를 만들 수 있는 권한이 없는 경우 부여

GRANT CREATE PROCEDURE TO 사용자명;

-- 사용자 계정

-- 부여된 권한 확인

SELECT * FROM USER_SYS_PRIVS;

//*****************************************

-- 프로시져 작성

CREATE [OR REPLACE] PROCEDURE 프로시져이름

[(

   매개변수 IN 자료형,   -- 입력용, 인수는 자료형크기를 지정할 수 없다.

   매개변수 OUT 자료형, -- 출력용

   매개변수 IN OUT 자료형 -- 입/출력용

)]

IS 

   [변수의 선언]

BEGIN

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

   [EXCEPTION]

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

END;

 IN 파라미터 : 호출자에 의해 프로시져로 전달되는 파라미터이며, '읽기' 전용의 값으로 프로시져는 이 파라미터의 값을 변경할 수 없다.(디폴트 모드)

 OUT 파라미터 : 프로시져에서 값을 변경할 수 있고, '쓰기' 기능으로 프로시져가 정보를 호출자에게 돌려주는 기능이다. OUT 파라미터는 디폴트값을 지정할 수 없다.

 IN OUT 파라미터 : 프로시져가 읽고 쓰는 작업을 동시에 할 수 있는 파라미터이다.

  -- 주의

    1) 프로시져 안에서는 INSERT, UPDATE, DELETE문을 사용하는 경우 자동 커밋이 되지 않으므로 COMMIT; 구문을 추가 해야 한다.

    2) 프로시져에서 INSERT 문을 2개 이상 사용하여 데이터를 추가하는 경우 첫번째 INSERT문은 성공하고 두번째 INSERT 에서 추가를 실패하면 첫번째 INSERT는 자동으로 ROLLBACK 된다.

//*****************************************

-- 테이블의 프로시져, 함수, 패키지 등 상호 참조되는 관계 확인

 SELECT * FROM user_dependencies;

   -- 테이블이 삭제된다고 연관된 프로시져가 삭제 되는것은 아니다.

       테이블이 삭제된 상태에서 연관된 프로시져를 실행 하면 오류가 발생 한다.

//*****************************************

-- 소스확인

SELECT text FROM user_source;

//*****************************************

-- 프로시져 목록 확인

SELECT object_name FROM user_procedures;

//*****************************************

-- 프로시져 실행

 1. EXECUTE

    EXEC[UTE] 프로시져이름[(인수, 인수)];

 2. CALL

    CALL 프로시져이름([인수, 인수]);

   - CALL에서 사용되는 인수를 지정하는 세가지

     CALL my_procedure(3, 4)

     CALL my_procedure(arg1 =>3, arg2 =>4)

     CALL my_procedure(3, arg2 => 4) 

//*****************************************

-- 프로시져 삭제

DROP PROCEDURE 프로시져이름;

//*****************************************

 - IN 파라미터 : 호출자에 의해 프로시져로 전달되는 파라미터이며, '읽기' 전용의 값으로 프로시져는 이 파라미터의 값을 변경할 수 없다.(디폴트 모드)

 - OUT 파라미터 : 프로시져에서 값을 변경할 수 있고, '쓰기' 기능으로 프로시져가 정보를 호출자에게 돌려주는 기능이다. OUT 파라미터는 디폴트값을 지정할 수 없다.

 - IN OUT 파라미터 : 프로시져가 읽고 쓰는 작업을 동시에 할 수 있는 파라미터이다.

-----------------------------------------------

//*****************************************

-- 예외 처리

-- RAISE_APPLICATION_ERROR(error_number, error_message);

  - 표준화되지 않은 에러 코드와 에러 메시지를 리턴하기 위해 RAISE_APPLICATION_ERROR() 프로시져 사용

  - RAISE_APPLICATION_ERROR를 만나면 처리를 중단하며, 자바등의 어플리케이션에는 에러를 던져주어 어플리케이션에서는 SQLException을 이용해서 에러를 확인할 수가 있다.

  - error_message : 출력할 메시지

  - error_number : -20999~-20000사이의 수 사용