기록하고 기억하는 블로그

*프로시져 ( PROCEDURE) : 

 ->PL/SQL 문을 저장하는 객체

 -> 필요할 떄마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출해 결과를 얻을 수 있음

 -> 특정 로직을 처리하기만 하고 결과값을 반환하지 않음 (FUNCTION 과의 차이)

<프로시져 생성 방법>

 CREATE OR REPLACE PROCEDURE 프로시저명

    ( 매개변수명1 (IN / OUT / IN OUT) 데이터타입 [ := DEFAULT값],

        ....

     )

  IS

      선언부

 BEGIN

      실행부

  [EXCEPTION ~]

 END [프로시저명] ;

<프로시져 실행>

 EXECUTE/EXEC  `프로시져명` ;

<EX 매개변수 없는 프로시져>

CREATE OR REPLACE PROCEDURE TEST_P
IS
BEGIN
	DELETE FROM TABLE_A;
    DELETE FROM TABLE_B;
    COMMIT;
END;
/

EXECUTE TEST_P;

SELECT * FROM USER_SOURCE; --프로시져 작성 구문 저장 출력

<EX 매개변수 있는 프로시져>

CREATE OR REPLACE PROCEDURE TEST_P2(
	P2_VAR TABLE_A.A_NAME%TYPE;   
)
IS
BEGIN
	DELETE FROM TABLE_A
    WHERE A_NAME = P2_VAR;
    COMMIT;
END;
/

EXECUTE TEST_P2('MYNAME');  --지정 입력

EXEC TEST_P2('&NAME');  -- 입력창 입력

<EX IN, OUT 매개변수 있는 프로시져>

* IN 매개변수 

    -> 프로시져 내부에서 사용될 변수

* OUT 매개변수

    -> 프로시저 외부(호출해서) 에서 사용될 변수

CREATE OR REPLACE PROCEDURE TEST_P3(
	P3_ID IN TABLE_A.A_ID%TYPE,
    P3_NAME OUT TABLE_A.A_NAME,
    P3_PHONE OUT TABLE_A.A_PHONE,
    P3_ADDRESS OUT TABLE_A.A_ADDRESS
)
IS
BEGIN
	SELECT A_NAME, A_PHONE, A_ADDRESS
    INTO P3_NAME, P3_PHONE, P3_ADDRESS
    FROM TABLE_A
    WHERE A_ID = P3_ID;
END;
/

/*
프로시져에서 OUT으로 저장되어있는 매개변수를 호출해서
외부에서 받아낸 변수가 필요하다
--> 바인딩 변수
*/

VAR RESULT_NAME VARCHAR2(30);
VAR RESULT_PHONE VARCHAR2(15);
VAR RESULT_ADDRESS VARCHAR2(200);
EXECUTE TEST_P3('&ID', :RESULT_NAME, :RESULT_PHONE, :RESULT_ADDRESS);

PRINT RESULT_NAME;
PRINT RESULT_PHONE;
PRINT RESULT_ADDRESS;  --PRINT -> 변수 출력 명령어

SET AUTOPRINT ON; -- 바인드 변수값 자동 출력 설정

*FUNCTION : 프로시져와 사용 용도가 거의 비슷하지만 프로시져와 다르게 

                   OUT 매개변수를 사용하지 않아도 결과를 되돌려 받을 수 있다 (RETURN)

 <FUNCTION 생성 방법>

 CREATE OR REPLACE FUNCTION 함수명(매개변수1 매개변수타입 , ...)

 RETURN 데이터타입

 IS

    선언부

 BEGIN

    실행부

  RETURN 반환값;

[EXCEPTION~ ]

END [함수명];

<EX>

CREATE OR REPLACE FUNCTION P_CALCUL(
	P_ID IN TABLE_A.A_ID%TYPE   --받는 매개변수
 )
 RETURN NUMBER  --리턴 타입
 IS 
 	P_INCOME TABLE_A.A_SALARY%TYPE;  -값 저장 변수 선언
    P_COMSUME TABLE_A.A_USE%TYPE;
    MONTH_NET NUMBER;
 
 BEGIN
 	SELECT A_SALARY, A_USE
    INTO P_INCOME, P_CONSUME
    FROM TABLE_A
    WHERE A_ID = P_ID;
 	
    MONTH_NET := P_INCOME - p_CONSUME ;
    RETURN MONTH_NET;
 END;
 /
 
 --사용법
 
 VAR CAL NUMBER;
 EXECUTE :CAL := P_CALCUL('&ID');
 
 --OR SELECT문에 사용
 
 SELECT A_ID, A_NAME, P_CALCUL(A_ID) 순수입
 FROM TABLE_A
 WHERE P_CALCUL(A_ID) > 300000;

* 커서(CURSOR) 

-> SELECT문 처리 결과(RESULT SET)를 담고 있는 메모리 공간에 대한 포인터

-> 커서 사용 시 여러 ROW로 나타난 처리 결과에 순차적으로 접근 가능

->  묵시적 커서:  오라클에서 자동으로 생성되어 사용되는 커서

    (묵시적 커서 속성 정보 참조 시 커서명 = SQL)
    - 커서명%ROWCOUNT : SQL 처리 결과로 얻어온 ROW 수
                       0 시작, FETCH 시 마다 1씩 증가
    - 커서명%FOUND    : 커서 영역의 ROW 수가 한 개 이상일 경우 TRUE 아님 FALSE
    - 커서명%NOTFOUND : 커서 영역의 ROW 수가 없으면 TRUE, 아님 FALSE
    - 커서명%ISOPEN   : 커서가 OPEN 상태인 경우 TRUE(묵시적 커서는 항상 FALSE)

     (EX)
     --SELECT 하면 오라클이 알아서 묵시적 커서를 생성하고 조건을 만족하는 행을 조회
     --한 행씩 이동하며 수정하고 이때 이동한 행의 개수 카운트(ROWCOUNT)
     --최종적으로 화면에 ROWCOUNT 출력

->  명시적 커서: 사용자가 직접 선언해서 사용할 수 있는 커서

            <작성법>

            1) CURSOR --커서 선언

            2) OPEN -- 커서 오픈

            3) FETCH --커서에서 데이터 추출

            4) CLOSE --커서 닫기

<EX>

DECLARE
	CURSOR C1 IS 
    	SELECT A_ID, A_NAME, A_SALARY
        FROM TABLE_A
        WHERE A_SALARY>=300000;
    ID TABLE_A.A_ID%TYPE;
    NAME TABLE_A.A_NAME%TYPE;
    SAL TABLE_A.A_SALARY%TYPE;
BEGIN
	OPEN C1:
    LOOP
    	FETCH C1 INTO ID, NAME, SAL
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(ID || '/' || NAME || '/' || SAL);
    END LOOP;
    CLOSE C1;
END;
/

<EX2 FOR +CURSOR>

-> FOR문을 이용한 커서 =  커서의 OPEN, FETCH, CLOSE 를 자동으로 해준다(선언만 하면 됨)

DECLARE
	CUSOR C2 IS
    	SELECT A_ID, A_NAME, A_SALARY
    	FROM TABLE_A
   		WHERE A_SALARY>=300000;
     P_C2 TABLE_A%ROWTYPE;
BEGIN
	FOR DEPT IN C2
    	LOOP
        	DBMS_OUTPUT.PUT_LINE(
            'ID : ' ||  P_C2.A_ID||
            'NAME : ' || P_C2.A_NAME ||
            'SALARY : '|| P2_C2.A_SALARY);
        END LOOP;
END;
/