Oracle - Procedure 사용하기

 

 

Procedure 란

데이터베이스(DB) 에 일련의 작업을 정리한 절차를 

관계형 데이터베이스 관리 시스템에 저장한 것을 말한다

 

 

Procedure 생성하기

CREATE OR REPLACE PROCEDURE 프로시저명( -- 매개변수를 사용하지 않기도 한다
		-- 변수 IN 변수타입		-- IN 으로 입력만 받는다
		-- 변수 OUT 변수타입		-- OUT 으로 출력만 한다
		-- 변수 IN OUT 변수타입		-- IN OUT 입력받고 출력한다
)
	IS
		-- 지역변수 선언
	BEGIN
		-- 실행할 PL/SQL 문
	EXCEPTION
		-- 예외 처리 문
	END
		-- 프로시저명

 

 

CREATE OR REPLACE PROCEDURE TEST_01(
		NUM1 		IN 	NUMBER,
		NUM2 		IN 	NUMBER,
		SUM		OUT 	NUMBER,
		TEST_TEXT 	IN OUT	VARCHAR2
	)
	IS
	BEGIN
		SUM := NUM1 + NUM2;
		TEST_TEXT := TEST_TEXT;
	END TEST_01;

 

꼭 매개변수에는 , 

IS 와 BEGIN 은 ; 로 닫아준다

 

 

매개변수, IS, BEGIN, END 사이에

빈 여백이 없도록 한다

여백이 있으면 아래 구문이 생성되지 않는다

 

 

 

 

 

 

 

Procedure 사용하기

 

    -- 결과 출력
    DBMS_OUTPUT.PUT_LINE('TEST 문구');
    
    -- 결과를 OUTPUT 에서 확인하려면 ON 으로 설정해야한다
    SET SERVEROUTPUT ON;
    
    -- DB Tool 마다 설정 방법이 다르다
    -- DBeaver 에서는 SET SERVEROUTPUT ON; 가 필요없고
    -- Ctr + Shift + O 에 OutPut 에 표시된다

 

 

DECLARE
   TEST_RESULT NUMBER;				   -- OUT 을 출력할 변수
   TEST_TEXT VARCHAR2(100) := '테스트 중입니다';    -- IN OUT 을 출력할 변수
BEGIN
    TEST_01(10, 20, TEST_RESULT , TEST_TEXT);
    DBMS_OUTPUT.PUT_LINE('Result: ' || TEST_RESULT);
    DBMS_OUTPUT.PUT_LINE('Result: ' || TEST_TEXT);
END;

 

 

TEST_01 은 매개변수가 4개이다

IN, IN, OUT, IN OUT 

각각에 맞춰서 

TEST_01(NUM1, NUM2, 출력변수, 값을 받아서 출력하는 변수) 로 맞춰주어야 한다

 

 

---

  

  

 

-- 생성
CREATE OR REPLACE PROCEDURE TEST_05(
	TEST_NO IN NUMBER,		-- 매개변수
	RESULT_NO OUT NUMBER, 		-- 결과값 변수
	RESULT_TEXT OUT VARCHAR2	-- 결과값 변수
)
IS
BEGIN
	SELECT 
		NO, 
		TEXT 
		INTO RESULT_NO, RESULT_TEXT --결과값 변수
		FROM TEST_PRO
	WHERE NO = TEST_NO;		    -- 매개변수 		
EXCEPTION 
	WHEN OTHERS THEN
    	RAISE_APPLICATION_ERROR(-20003, '조회 에러');
END;

DECLARE
    D_RESULT_NO NUMBER;        		 -- 결과를 받을 변수
    D_RESULT_TEXT VARCHAR2(100);	 -- 결과를 받을 변수
BEGIN
    -- 프로시저 호출
    TEST_05(
        TEST_NO => 1,           			-- 조회할 NO 값
        RESULT_NO => D_RESULT_NO,
        RESULT_TEXT => D_RESULT_TEXT
    );
    DBMS_OUTPUT.PUT_LINE('NO: ' || D_RESULT_NO);
    DBMS_OUTPUT.PUT_LINE('TEXT: ' || D_RESULT_TEXT);

EXCEPTION
    -- 예외 처리
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error : ' || SQLERRM);
END;

 

 

=> 를 이용할 수 도 있다

 

 

 

 

 

 

 

INSERT 하기

-- TEST 테이블
CREATE TABLE TEST_PRO(
	NO	NUMBER,
	TEXT	VARCHAR2(20)
);



-- INSERT 하기
CREATE OR REPLACE PROCEDURE TEST_02(
		TEST_NO IN NUMBER,
		TEST_TEXT IN VARCHAR2
	)
	IS
	BEGIN
		INSERT INTO TEST_PRO(NO , TEXT) VALUES(TEST_NO, TEST_TEXT);
	END TEST_02;



-- 실행1
DECLARE
	D_NO NUMBER := 1;
	D_TEXT VARCHAR2(20) := '첫번째 텍스트';
BEGIN
	TEST_02(D_NO , D_TEXT);

	COMMIT; -- 저장
END;


-- 실행2
CALL TEST_02(2, '두번째 텍스트');

 

 

 

 

 

 

 

 

UPDATE 하기

CREATE OR REPLACE PROCEDURE TEST_03(
		TEST_NO IN NUMBER,
		TEST_TEXT IN VARCHAR2
	)
	IS
	BEGIN
		UPDATE TEST_PRO SET TEXT = TEST_TEXT WHERE NO = TEST_NO;
		COMMIT;
	EXCEPTION
		WHEN OTHERS THEN
		ROLLBACK;	-- 예외 발생 시 롤백
		DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
	END TEST_03;

EXCEPTION 을 적용시켰다

 

 

CALL TEST_03(1, '첫번째 텍스트인데 수정했음');

CALL 로 프로시저를 실행시키니 오류가 발생하였다

 

VARCHAR2(20) 인데 글자수가 넘어서 EXCEPTION 에 걸렸다

 

 

 

CALL TEST_03(1, '첫번째 수정!!!');

 

변경해서 실행시키니 정상적으로 UPDATE 되었다

 

 

 

 

 

DELETE 하기

CREATE OR REPLACE PROCEDURE TEST_04(
		TEST_NO IN NUMBER
	)
	IS
	BEGIN
		DELETE FROM TEST_PRO WHERE NO = TEST_NO;
		DBMS_OUTPUT.PUT_LINE('삭제한 번호 : ' || TEST_NO);
		COMMIT;
	EXCEPTION
		WHEN OTHERS THEN
		ROLLBACK;
		DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
	END TEST_04;
    
    
// 실행 , 당연히 DECLARE 문으로도 가능하다
CALL TEST_04(2);

 

 

데이터가 삭제되었다

 

 

 

 

 

 

 

 

Procedure 수정하기

CREATE OR REPLACE PROCEDURE 프로시저명(
...
..

CREATE OR REPLACE PROCEDURE 로 

프로시저를 생성했던 것처럼 작성하면 코드가 수정된다

 

 

 

 

DBeaver 기준으로 프로시저명에 F4 버튼을 누르거나

Procedure 폴더 안에 서서 생성된 프로시저를 더블클릭하면 

현재 프로시저의 구문을 확인할 수 있고 해당 페이지에서 수정이 가능하다

 

 

 

 

 

 

 

CURSOR 사용하기

-- FETCH 를 이용한 LOOP
DECLARE
	D_RESULT_NO NUMBER;      		-- NO
	D_RESULT_TEXT VARCHAR(20);		-- TEXT
CURSOR C_TEST IS                          	-- CURSOR 명
        SELECT NO,
                  TEXT
        FROM  TEST_PRO;
BEGIN
        OPEN C_TEST;   -- 커서 실행
        DBMS_OUTPUT.PUT_LINE('NO '||' '||' TEXT'); 
        LOOP
            FETCH C_TEST INTO D_RESULT_NO, D_RESULT_TEXT; 	--커서에서 데이터 가져오기
            EXIT WHEN C_TEST %NOTFOUND; --커서 속성
            DBMS_OUTPUT.PUT_LINE(D_RESULT_NO||' '||D_RESULT_TEXT);   
        END LOOP;   
     CLOSE C_TEST;    -- 커서 종료
END;

 

 

 

 

-- FOR 문을 이용한 LOOP
DECLARE
	D_RESULT_NO NUMBER;      		-- NO
	D_RESULT_TEXT VARCHAR(20);		-- TEXT
CURSOR C_TEST IS				-- CURSOR 명
        SELECT NO,
                  TEXT
        FROM  TEST_PRO;
BEGIN
		DBMS_OUTPUT.PUT_LINE('NO '||' '||' TEXT');
       FOR RES IN C_TEST
       LOOP
       		DBMS_OUTPUT.PUT_LINE(RES.NO || ' ' || RES.TEXT);
       END LOOP;
END;

 

 

 

둘 다 결과는 동일하며,

차이는 CURSOR 를 OPEN, CLOSE 하느냐 하지 않느냐 차이이다

 

 

 

 

 

 

 

Procedure 조회하기

SELECT 
	OWNER, 
	OBJECT_NAME, 
	PROCEDURE_NAME 
FROM ALL_PROCEDURES
WHERE OBJECT_TYPE = 'PROCEDURE'
AND OBJECT_NAME LIKE 'TEST_%'

 

 

생성한 프로시저를 조회한다

 

 

 

 

Procedure 삭제하기

DROP PROCEDURE 프로시저명;

-- DROP PROCEDURE TEST_04;

 

Table 처럼 DROP 으로 간단히 삭제한다