(28)[ORACLE] 11일차 : PL/SQL - 2

 

 

프로시저란?
PL/SQL에서 가장 대표적인 구조인 프로시저는 개발자가 자주 실행해야 하는 업무 흐름(SQL)을
미리 작성하여 데이터베이스 내에 저장해 두었다가 필요할 때마다 호출하여 실행 할 수 있다.
프로시저는 특정한 로직을 처리하고 결과를 반환하지 않는다.

프로시저 특징

패키지 내에서 프로시저는 중복 정의 가능

테이블이 삭제되어도 연관된 프로시저가 삭제 되는 것은 아니다

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

프로시저 안에서 INSERT,UPDATE,DELETE문을 사용하는 경우 자동 COMMIT 되지 않으므로 COMMIT; 구문을 추가 해야된다.

 

프로시저 정보 확인

--프로시저 목록확인
SELECT * FROM user_procedures;

--의존성 확인
SELECT * FROM user_dependencies;

--소스 확인
SELECT * FROM user_source;

 

프로시저 생성

CREATE [OR REPLACE] PROCEDURE 프로시저명
(
    (parameter [IN|OUT|IN OUT] 데이터타입[:=디폴트 값])
)
IS
    변수,상수 등 선언
BEGIN   
    실행부
    [EXCEPTION 예외 처리부]
END;
/

 

• OR REPLACE
  • 프로시저가 존재하지 않으면 프로시저를 생성하며, 존재하면 기존 프로시저는 재작성(수정)한다.
• 매개변수(parameter)
  • IN 파라미터 :호출자에 의해 프로시저로 전달되는 파라미터이며, '읽기' 전용의 값으로 프로시저는 이 파라미터의 값을 변경할 수 없다. (디폴트 모드)
  • OUT 파라미터 : 프로시저에서 값을 변경할 수 있고, '쓰기' 기능으로 프로시저가 정보를 호출자에게 돌려주는 기능. OUT 파라미터는 디폴트 값을 지정할 수 없다.
  • IN OUT 파라미터 : 프로시저가 읽고 쓰는 작업을 동시에 할 수 있는 파라미터.
• 파라미터는 데이터 타입만 명시하고 크기를 지정할 수 없다. 

 

프로시저 실행 

-- 실행 형식
EXEC 프로시저명 [(매개변수값,매개변수값2...)];

-- 실행 형식 2
EXEC 프로시저명 (매개변수명1=>값1,매개변수명2=>값2...);
'=>' 기호를 사용해 해당 매개변수명과 값을 연결하는 형태로 실행할 수 있다.

 

프로시저 삭제

DORP PROCEDURE 프로시저명;

 

 

---

 

 

사용자 정의 함수란?
사용자가 직접 로직을 구현하여 구현한 함수로 내장 함수(빌트인 함수)처럼 쿼리에서 호출하거나 EXECUTE문을 통해 실행 할 수 있다.
프로시저는 특정한 로직을 처리하고 결과를 반환하지 않지만 사용자 정의 함수는 결과를 반환한다.
패키지 내에서 사용자 정의 함수는 중복 정의가 가능하다. 

함수 생성

CREATE [OR REPLACE] FUNCTION 함수명
[(parameter 데이터 타입
[,parameter 데이터 타입...]
)]
RETURN 데이터 타입
IS
	변수,상수 등 선언
BEGIN
	실행부
    
    RETURN 반환값;
    [EXCEPTION 예외 처리부]
    END;

 

• OR REPLACE : 사용자 정의 함수가 존재하지 않으면 함수를 생성하며, 존재하면 기존 함수는 재작성(수정)한다.
• 매개변수 (parameter) : 파라미터는 데이터 타입만 명시하고 크기를 지정할 수 없다.
• RETURN 데이터 타입 : 반환되는 값의 데이터 타입만 명시하고 크기를 지정할 수 없다.
• RETURN 반환값; : 함수를 실행하고 호출한 곳으로 반환할 값을 명시한다. 

 

사용자 정의 함수 호출

--매개 변수가 없는 경우
EXEC 함수명 또는 함수명()

--매개 변수가 있는 경우
EXEC 함수명(값1,값2...)

 

사용자 정의 삭제

DROP FUNCTION 함수명;

 

CREATE OR REPLACE FUNCTION fnSum
(
        n IN NUMBER
)
RETURN NUMBER
IS

    s NUMBER := 0;
BEGIN
    FOR i IN 1..N LOOP
        s:=s+i;
        END LOOP;
    RETURN s;
END;

/

--함수 목록 확인
SELECT * FROM user_procedures;

--함수 결과 확인 
SELECT fnSum(10),fnSum(100) FROM dual;