[DB/Oracle] Procedure, Curosr , 사용자 정의 함수

 

 

Oracle 에서 Procedure를 실행 할때 ORACLE에서 출력 값을 확인 하기 위해서는

SET SERVEROUTPUT ON;        <==== 을 실행 한 후에 실행시켜야 출력이 된다.

 

우선 프로시저의 경우 시작부가

CREATE OR REPLACE PROCEDURE (프로시저명)   // DECLARE   이렇게나뉜것을 쉽게 볼수 있는데 

이것은 CREATE 하여 프로시저를 만들면 지속적으로 사용할수 있으나 // DECLARE를 하면 일회용 한번 쓰고 

끝나는 프로시저가 된다.

 

프로시저의 경우는 

DECLARE // CREATE OR REPLACE PROCEDURE 프로시저명(    ) 으로 시작되며

 

IS -- 변수를 선언할떄 사용하는 키워드 

   변수들 ~~~ 

   p_name employee.mem_name%type  <== p_name 변수를 선언하고, employee 테이블의

                                                           mem_name 속성과 타입을 통일하게 선언한 것이다.

   CUROSR   커서명(매개변수) IS    select문   <== 이것은 select 문의 결과값이 저장된 메모리 주소를 커서명에 저장함

BEGIN

   OPEN 커서명(매개변수)    <= 커서를 사용하려면 OPEN 명령어와 함께 사용하며, 해당 커서가 실행되어 값이 커서명에 저장됨

   FETCH 커서명 INTO p_name   <=== CUROSR에서 정의한 Select 결과를 p_name에 저장하는것 

                                                    --만약에 데이터가 하나가 아니라면 LOOP문을 통해 반복적으로 넣어줌,

   CLOSE 커서명;    ==> 커서를 종료함

END;

 

 

CREATE OR REPLACE PROCEDURE name_view(emp_no IN INT, emp_name OUT CHAR)

==> 매개변수로 들어오는것은  emp_no에 인트형이고 ,, 반환할것은 emp_name에 저장하고 char이다.

 

 

프로시저를 선언할때 들어가는 매개 변수를 프로시저명 ( 괄호안에 넣어준다. )

이때 외부에서 넣어주는 값은 IN 으로 선언 하고,  외부로 리턴할떄는 OUT으로 선언한다.  

 

IS 를 통해서 지역변수를 선언해주고, 커서의 정의 또한 IS 안에서 해준다

BEGIN 을 통해서 정의한 내용이 실행이된다.  이때 커서를 사용할땐 OPEN 커서명을 통해서 커서에서 정의한 select 값을 저장한 메모리 주소를 salaryCur 에 저장한다. 이때 커서에 저장된 값이 여러개일 경우 LOOP를 통해서 한개씩 꺼냄

 

LOOP 문안에서  FETCH salaryCur INTO salaryVar; 를 통해서 루프문을 돌면서 salaryCur에 저장된 값들을 하나씩 salaryVar에 저장한다. 

 

EXIT WHEN salaryCur%NOTFOUND;    의 경우는        salaryCur 에 더이상 select의 결과값이 없을 경우 종료된다. 

 * salaryCur%Found  --> FETCH된 튜플수가 1개 이상이면 TRUE 

 * salaryCur%ROWCOUNT --> 쿼리 수행결과로 패치된 튜플수를 반환한다.(LOOP문안에 넣을경우 1씩 증가됨을 확인할 수 있음)

 * salaryCur%NOTFOUND --> 쿼리 수행의 결과로 패치된 튜플 수가 0개 이면 TRUE 

 

 

선언한 프로시저 사용방법

프로시저의 매개변수가 없거나 // IN 매개 변수만 있을경우는

EXEC(=EXECUTE, CALL) 프로시저명 // EXEC(매개변수) 를통해서 실행 할 수 있습니다.

 

프로시저의 매개변수가 IN/OUT 모두 있을경우는  프로시저 안에서 사용할 수 있다.

아래를 보면 위에서 선언한 프로시저를 사용하고 있는데

GROUP_AVG()안에 IN,OUT 의 매개 변수가 들어가는데 

IN 안에는 char의 변수를 넣어주고,  OUT에는 FLOAT의 변수를 넣어준다. 이때

OUT은 해당 프로시저의 결과값을 받아야 하므로 해당 변수를 선언하고 선언한 변수를 넣어준다.

해당 변수를 출력하여 결과 값을 확인할 수 있다. 

 

프로시저 제거 

DROP PROCEDURE 프로시저명

 

 

 

 

 

 

사용자 정의 함수

CREATE OR REPLACE FUNCTION 만들함수명(파라미터)

[지역변수 선언]

BEGIN

   사용자 정의 함수 BODY;

   RETURN 반환값

END;

 

사용자 정의 함수의 경우는 OUT 매개 변수가 필요 없다 .  

RETURN 자료형을 선언하기 떄문이다.   

BEGIN에서 select 의 결과값을  저장할 변수를 IS 에 선언하고, select의 결과값을 into 를 통해서 변수에 집어 넣고

해당 변수를 RETURN 한다.     

 * 그렇다면 SQL의 결과값을 저장할 변수는 RETURN 할 변수명과 동일하게 하면 될듯,

 

만약에 IF 문을 통해   IF dept_cd = 1 THEN  RETURN '회계부' ELSE --- END IF; 이렇게 되어있다면

바로 RETURN 할수 있기 떄문에 결과값을 저장할 telss 변수를 선언할 필요가 없다.

 

DROP FUNCTION 사용자 정의 함수명

 

 

조건문 

IF 조건 THEN

 실행할 문장 1;

 실행할 문장 2;

END IF;

 

IF 조건 THEN

  실행할 문장1;

ELSE 

  실행할 문장2;

END IF:

 

LOOP문

LOOP

   실행할 문장;

    EXIT WHEN 조건;  // 조건이 참일경우 LOOP 탈출

END LOOP;