Oracle Stored Procedure

- 프로시저 기본 구문

create or replace procedure 프로시저이름

is

프로시저에서 사용할 로컬변수

begin

수행 로직

end;

/

- 프로시저 실행

execute 프로시저이름;

- tbl_support와 tbl_confidence 테이블에 저장돼있는 지지도와 신뢰도, 규칙을 이용하여

apriori알고리즘의 lift(향상도)를 계산하는 Procedure

- 프로시저 실행

- 작성한 오라클 저장 프로시저를 스프링-MyBatis 연동한 프로젝트의 mapper.xml 파일에 작성하는 방법.

아래와 같이 <select> 에 statementType을 CALLABLE로 준다음 CALL 프로시저명() 해주면 된다.

- 코드 상세 설명

-- 저장 프로시저를 sql Developer나 웹에서 사용할 때는 미리 이렇게 설정해주어야 화면에 출력 값 확인가능

SET SERVEROUTPUT ON 

-- apriori_lift라는 이름으로 프로시저 생성

create or replace procedure apriori_lift

-- 로컬변수 선언

is

  -- '변수명 변수타입' 

  lift TBL_CONFIDENCE.CONFIDENCE%type; 

  vsupport TBL_SUPPORT.SUPPORT%type;

  --tbl_confidence 테이블의 한 행에 해당하는 타입(즉, 해당 테이블의 각 컬럼들에접근 할 수 있다.)으로 선언할 수     있다.

  vconf tbl_confidence%rowtype;   vcnt number;

  vrcnt number;

  

 -- 커서 선언( tbl_confidence테이블의 모든 내용을 조회하는 select문의 결과를 얻어오기위해 cursor를 선언한다.)

-- 질의 수행 후 검색 조건을 충족하는 모든 행으로 구성된 결과 set이 생성된다.

  CURSOR confidencecursor 

  is

  select *  

  from tbl_confidence

  order by confidence desc;

  

BEGIN 

  vcnt := 0;

  vrcnt := 0;

-- 커서를 open하면 결과 set에서 첫 번째 행을 가리키게된다.

  open confidencecursor;

  

  loop

-- fetch문은 현재행에 대한 정보를 얻어온 뒤 into 뒤에 기술한 변수에 저장한 후 다음 행으로 이동한다.

    fetch confidencecursor into vconf.startdata, vconf.enddata, vconf.confidence;

    -- 커서가 행을 못찾으면( 모든 행을 다 조회하면) loop를 exit한다.

    exit when confidencecursor%NOTFOUND;

    

    select support into vsupport

    from tbl_support

    where FREQDATA = vconf.enddata;

  

    lift := vconf.confidence / vsupport;

    

    if lift > 1 then

     DBMS_OUTPUT.put_line('넘는다' ||'  '||lift);

     vrcnt := vrcnt + 1;

     DBMS_OUTPUT.put_line('저 값 ' ||vrcnt);

    else

      delete from tbl_confidence 

      where startdata=vconf.startdata and enddata=vconf.enddata;

      DBMS_OUTPUT.put_line('넘지 못한다.');

    end if; 

  end loop;

  -- cursor다 쓴 뒤 닫아준다.

  close confidencecursor;

end;

/ 

commit;

 

-- 현재 db에 저장되어있는 프로시저를 조회할 때 사용하는 쿼리

select object_name, object_type

from user_objects

where object_type='PROCEDURE'; -- 대문자로 작성해야한다.

-- 특정 프로시저의 코드를 확인할 때 사용하는 쿼리

select text

from user_source

where name = 'APRIORI_LIFT';