44일차 - PL/SQL 프로시저/생성/호출

 

PL/SQL

 

Procedural Language/SQL

기존의 단순한 SQL이 확장된 언어입니다.

프로그래밍언어처럼 변수, 제어문(if문, 반복문)을 사용하여 SQL 실행의 흐름을 제어합니다.

 

프로시저

 

쿼리문의 집합으로 볼 수 있으며, 여러 명령을 일괄 처리하기 위한 용도로 사용합니다.

프로시저 내부에는 주로 DML 명령문들을 작성합니다.

 → 트랜잭션 : 함께 실행해야할 여러 SQL

 

프로시저 기본형식

 

DECLARE

    1) 변수 선언

BEGIN

    2) 기능 구현(SQL문 작성)

    3) 예외 처리(생략 가능)

END;

 

 

1) 변수 선언

 

프로시저 내에서 사용할 변수를 선언합니다.

 → 변수명 데이터타입;

 → 변수명 테이블명.컬럼명%TYPE;

 → (테이블명.컬럼명의 데이터 타입과 같게 합니다.)

 

2) 기능 구현

 

SELECT한 결과를 INTO문의 변수에 저장합니다.

 → vcustomid = custom_id

 → vage = age

 

dbms_output.put_line( ) 으로 출력문, 변수 값 등을 콘솔에 출력합니다.

 → ( ) 내에서는 연결부호로 + 대신 | | 을 사용합니다.

 

3) 예외 처리

 

EXCEPTION WHEN 예외이름 THEN 예외발생시 처리명령;

 → 예외이름에 OTHERS를 작성하면 모든 예외 시 처리합니다.

 

emp EMPLOYEES %rowtype

 → 변수 emp에 EMPLOYEES 테이블에서 조회된 행(모든 컬럼)을 저장합니다.

 

SELECT문의 결과를 변수에 저장하여 서브쿼리 대신 사용할 수 있습니다.

 

출력할 때 emp.컬럼명으로 행의 데이터를 추출합니다.

 

 

프로시저 생성 및 호출 / 매개변수 사용

 

프로시저를 오라클 객체로 생성하면 호출해서 다시 실행할 수 있습니다.

 

생성 형식

 

CREATE OR REPLACE PROCEDURE 프로시저명(

    1) 입력, 출력 매개변수 선언

)

IS

    2) 변수 선언

BEGIN

    3) 기능 구현

END;

 

CREATE : 프로시저가 없으면 생성합니다.

REPLACE : 프로시저가 이미 있으면 대체합니다.

 

 

1) 입력, 출력 매개변수 선언

 

입력 매개변수

 → 매개변수명 IN 데이터타입;

 → 매개변수명 IN 테이블명.컬럼명%TYPE;

ex) 데이터를 조회할 매개변수(부서명 등)를 입력받아 조회

 

출력 매개변수

 → 매개변수명 OUT 데이터타입;

 → 매개변수명 OUT 테이블명.컬럼명%TYPE;

ex) 조회한 데이터를 매개변수에 저장하여 출력

 

2) 변수 선언

 

프로시저 내에서 사용할 변수를 선언합니다.

 → 변수명 데이터타입;

 → 변수명 테이블명.컬럼명%TYPE;

 

3) 기능 구현

 

값을 입력할 자리에 입력 매개변수를 사용합니다.

출력 매개변수에 조회한 데이터를 저장합니다.

 

호출(실행) 형식

 

DECLARE

    1) 출력 매개변수 선언

BEGIN

    2) 실행할 프로시저명

    3) 출력문

END;

 

또는

EXECUTE 프로시저명;

EXEC 프로시저명;

단, 디비버에서는 EXECUTE, EXEC 호출이 안됩니다.

 

1) 출력 매개변수 선언

 

생성할 때와 달리 OUT 키워드를 사용하지 않습니다.

 → 매개변수명 데이터타입;

 → 매개변수명 테이블명.컬럼명%TYPE;

 

2) 실행할 프로시저명

 

생성할 때 선언한 매개변수를 순서대로 입력합니다.

 → ex) 프로시저명(입력 매개변수값, 출력 매개변수명,...);

 

3) 출력문

 

dbms_output.put_line( ) 에 출력 매개변수명을 입력합니다.