프로시저(Procedure) 의 개념
- 프로시저는 절차형 SQL을 활용하여 특정 기능을 수행할 수 있는 트랜잭션 언어입니다.
- 프로시저는 호출을 통해 실행됩니다.
- 일련의 SQL 작업을 포함하는 데이터 조작어(DML, DATA MANIPULATE Language)를 수행합니다.
※ DML(Data Manipulate Language) 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회를 하는 언어로 SELECT, INSERT, UPDATE, DELETE 명령이 존재합니다.
CREATE PROCEDURE 프로시저_명 (파라미터_명 MODE 데이터 타입) IS 변수 선언 CREATE OR REPLACE PROCEDURE 프로시저_명(파라미터_명 MODE 데이터_타입) AS 변수 선언
- CREATE : DBMS 내에 객체(트리거, 함수, 프로시저)를 생성
, OR REPLACE 는 기존 프로시저 존재시 현재 컴파일 하는 내용으로 덮어씀 (같은 이름의 프로시저가 존재하는 경우 OR REPLACE가 없으면 에러 발생) - MODE : 변수의 입출력을 구분
IN(프로시저로 값 전달)
OUT(프로시저에서 처리된 결과)
INOUT(IN과 OUT의 두가지 기능을 모두 수행)
3가지로 구성 - 데이터 타입 : 파라미터에 대한 데이터 타입
- IS/AS : PL/SQL 의 블록의 시작 IS또는 AS를 작성
- 시작/종료부(BEGIN/END) : 프로시저의 실행 시작과 종료를 알려주는 부분
프로시저 호출문
EXCUTE 프로시저_명(파라미터_명1, 파라미터_명2)
CREATE OR REPLACE PROCEDUTE TEST_PROCEDURE ( V_DATE IN CHAR(8) --파라미터 ) IS V_TOT_CNT NUMBER :=0 -- 내부에서 사용할 변수 BEGIN -- 프로시저 시작 -- 제어부 IF V_DATE < "20210215" THEN SET V_DATE = "20210201"; END IF; --SQL SELCT SUM(C_CNT) INTO V_TOT_CNT FROM TEST_TABLE WHERE C_DATE = V_DATE; -- SELCT 문에서 나온 결과는 INTO 문에 선언된 변수에 값이 전달 - (SELECT 에서 조회된 결과가 없을 경우에는 NO_DATA_FOUND라는 결과가 발생 -예외부 EXCEPTION WHEN NO_DATA_FOUND THEN SET V_TOT_CNT= 0; -- INSERT INTO TEST_TABLE(C_DATE, C_CNT) VALUES(V_DATE, V_TOT_CNT); COMMIT; //트랜잭션 완료 END; //프로시저 종료실행 방법
- IN 변수만 있는 프로시저 EXECUTE TEST_PROCEDURE('20210205'); CALL TEST_PROCEDURE('20210205'); EXECUTE TEST_PROCEDURE('20210205');
CREATE OR REPLACE PROCEDURE TEST_PROC ( -- 프로시저 실행 시 받을 매개변수는 CREATE 안에 작성 param1 IN NUMBER , param2 IN VARCHAR2 ) -- 프로시저에서 선언할 변수는 IS 뒤에 작성 IS param3 IN VARCHAR2(20) := "StudyingAzae"; -- 실행 내용은 BEGIN 뒤에 작성 BEGIN -- 단순 INSERT 문 INSERT INTO TEST_TABLE (NO, NAME, SITE) VALUE (param1, param2, param3) ; END TEST_PROC;
- 실행
- IN 변수만 있는 프로시저
EXEC TEST_PROC();
EXEC TEST_PROC(213, "DDDL");- OUT 변수만 있는 프로시저
DECLARE
param1 varchar2(200);
param2 varchar2(200);
BEGIN
TEST_PROC('123', param1, param2);
END;
- 프로시저 문법 종류
- IF 조건1 THEN 처리
IF 조건2 THEN 처리
ELSE
END IF;
