PL /SQL) PROCEDURE(프로시저)

<procedure>

-- procedure

-- 내가 위에서 만든 커서를 영속적으로 저장 (객체)

-- 객체 형태로 저장 해놓으면 그 다음번에 코딩하지 않고 불러 사용

-- Oracle : subprogram(= procedure)

-- Ms-sql : procedure

-- 자주 사용되는 쿼리를 모듈화 시켜서 객체로 저장하고

-- 필요한 시점에 불러(호출) 해서 사용하겠다

-- procedure는 자동 커밋, 롤백이 되지 않으므로 사용자가 제어해줘야 한다

--기존 : APP(emp.java > select .... 구문) ->네트워크 > DB연결 > selet... > DB에

--지금 : APP(emp.java > usp_emplist 구문) ->네트워크 > DB연결 > usp_emplist > DB에

-- 장점 1) 네트워크 트래픽 감소(=시간 단축)(프로시져명만 서버에 보내면 되기 때문)

-- 장점 2) 보안 (네트워크 상에서 패킷 가로채도 쿼리문을 알 수 없기 때문)

<procedure 생성과 사용>

-- 프로시져 생성

-- create or replace인 이유는

-- 프로시져를 고치고 싶어도 쿼리를 수정한 뒤

-- 이 구문 그대로 실행해주면 되기 때문이다

create or replace procedure usp_emplist

is

BEGIN

update emp

set job = 'TTT'

where deptno=30;

END;

-- 실행방법

execute usp_emplist;

-- parameter 사용가능

-- 종류 : INPUT , OUTPUT

create or replace procedure usp_update_emp

(vempno emp.empno%TYPE)

is

BEGIN

update emp

set sal = 0

where empno = vempno;

END;

--실행방법

exec usp_update_emp(7788);

select * from emp where empno = 7788;

rollback;

create or replace procedure usp_getemplist

(vempno emp.empno%TYPE)

is

--내부에서 사용하는 변수

vname emp.ename%TYPE;

vsal emp.sal%TYPE;

BEGIN

select ename, sal

into vname , vsal

from emp

where empno=vempno;

DBMS_OUTPUT.put_line('이름은 : ' || vname);

DBMS_OUTPUT.put_line('급여는 : ' || vsal);

END;

exec usp_getemplist(7902);

-- procedure는 parameter 종류 2가지

--1. input paramter : 사용시 반드시 입력 (IN : 생략하는 default)

--2. output parmater : 사용시 입력값을 받지 않아요 (OUT)

create or replace procedure app_get_emplist

(

vempno IN emp.empno%TYPE,

vename OUT emp.ename%TYPE,

vsal OUT emp.sal%TYPE

)

is

BEGIN

select ename, sal

into vename , vsal

from emp

where empno=vempno;

END;

--오라클 실행 테스트

DECLARE

out_ename emp.ename%TYPE;

out_sal emp.sal%TYPE;

BEGIN

app_get_emplist(7902,out_ename,out_sal);

DBMS_OUTPUT.put_line('출력값 : ' || out_ename || '-' || out_sal);

END;

create or replace function f_max_sal

(s_deptno emp.deptno%TYPE) -- 사용할 변수 선언

return number -- 리턴할 타입 선언

is

max_sal emp.sal%TYPE; -- 리턴할 변수 선언

BEGIN

select max(sal)

into max_sal -- 위에서 선언한 is 뒤 변수

from emp

where deptno = s_deptno;

return max_sal; -- 위에서 선언한 is 뒤 변수

END;

create or replace function f_callname

(vempno emp.empno%TYPE)

return varchar2

is

v_name emp.ename%TYPE;

BEGIN

select ename || '님'

into v_name

from emp

where empno=vempno;

return v_name;

END;

--parmater 사번을 입력받아서 사번에 해당되는 부서이름을 리턴하는 함수

create or replace function f_get_dname

(vempno emp.empno%TYPE)

return varchar2

is

v_dname dept.dname%TYPE;

BEGIN

select dname

into v_dname

from dept

where deptno = (select deptno from emp where empno=vempno);

return v_dname;

END;

select empno , ename , f_get_dname(empno)

from emp

where empno=7788;

<.java에서의 프로시져 실행- 예시 1>

/*

CREATE OR REPLACE PROCEDURE usp_EmpList

(

p_sal IN number,

p_cursor OUT SYS_REFCURSOR --APP 사용하기 위한 타입

)

IS

BEGIN

OPEN p_cursor

FOR

SELECT empno, ename, sal FROM EMP WHERE sal > p_sal;

END;

*/

public class Ex08_Oracle_Procedure_Select {

    public static void main(String[] args) {

         Connection conn = null;

         //명령객체

         CallableStatement cstmt= null; //변경 (procedure 처리하는 객체)

         ResultSet rs = null;

         try{

         Class.forName("oracle.jdbc.OracleDriver");

         conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",

"bituser", "1004");

        

         //p_sal IN number,

         //p_cursor OUT SYS_REFCURSOR --APP 사용하기 위한 타입

         String sql = "{call usp_EmpList(?,?)}";

         cstmt = conn.prepareCall(sql);

        

         //usp_EmpList(?,?) input , output

         cstmt.setInt(1, 3000);

// 두번째 파라미터 : 아웃풋에 관한 지정

         cstmt.registerOutParameter(2, OracleTypes.CURSOR);

         cstmt.execute(); // 실행

        

         rs = (ResultSet)cstmt.getObject(2); //두번째 ? 에 처리 ....

        

         while(rs.next()){

         System.out.println(rs.getInt(1) +"/" + rs.getString(2) +"/" + rs.getInt(3));

         }

        

         }catch(Exception e){

        

         }finally{

         if(rs != null){try{rs.close();}catch(Exception e){}};

         if(cstmt != null){try{cstmt.close();}catch(Exception e){}};

         if(conn != null){try{conn.close();}catch(Exception e){}};

         }

    }

}

<.java에서의 프로시져 실행- 예시 2>

/*

create table usp_emp

as

select * from emp;

alter table usp_emp

add constraint pk_usp_emp_empno primary key(empno);

select * from SYS.USER_CONSTRAINTS where table_name='USP_EMP';

CREATE OR REPLACE PROCEDURE usp_insert_emp

(

vempno IN emp.empno%TYPE,

vename IN emp.ename%TYPE,

vjob IN emp.job%TYPE,

p_outmsg OUT VARCHAR2

)

IS

BEGIN

INSERT INTO USP_EMP(empno , ename, job) VALUES(vempno ,vename , vjob);

COMMIT;

p_outmsg := 'success';

EXCEPTION WHEN OTHERS THEN

p_outmsg := SQLERRM;

ROLLBACK;

END;

제약에러 확인을 위해서 EMP 제약 추가

alter table emp

add constraint pk_emp_empno primary key(empno);

*/

public class Ex09_Oracle_Procedure_DML {

    public static void main(String[] args) {

         Connection conn = null;

         //명령객체

         CallableStatement cstmt= null; //변경

         try{

         Class.forName("oracle.jdbc.OracleDriver");

         conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",

"bituser", "1004");

        

         String sql = "{call usp_insert_emp(?,?,?,?)}";

         cstmt = conn.prepareCall(sql);

        

         //usp_insert_emp(?,?) input , output

         cstmt.setInt(1, 9000);

         cstmt.setString(2, "hong");

         cstmt.setString(3, "IT");

         cstmt.registerOutParameter(4, Types.VARCHAR);

        

         cstmt.execute();

        

         String Oracle_msg = (String)cstmt.getObject(4); //네번째 ? 표 output

        

         System.out.println("Oracle_Msg : " + Oracle_msg);

        

        

         }catch(Exception e){

        

         }finally{

         if(cstmt != null){try{cstmt.close();}catch(Exception e){}};

         if(conn != null){try{conn.close();}catch(Exception e){}};

         }

    }

}

<.java에서의 프로시져 실행- 예시 3>

emp 실습 쿼리.txt

Emp_Spring_Boot_Tiles_Mybatis_1Team.zip

<프로시저 구현 부분>

1) index.html 페이지 들어가면 뜨는 전체 직원 조회

2) 직원 추가

<사용 프로시저>

1.

CREATE OR REPLACE PROCEDURE jk_EmpList

(p_list OUT SYS_REFCURSOR )

is

 BEGIN

   OPEN p_list

     FOR

        SELECT *  FROM emp;

   END;

2.

create or replace PROCEDURE jk_insert_emp

(

-- EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

vempno IN emp.empno%TYPE,

vename IN emp.ename%TYPE,

vjob   IN emp.job%TYPE,

vmgr  IN emp.mgr%TYPE, --

vhiredate in emp.hiredate%TYPE,

vsal in emp.sal%TYPE,

vcomm in emp.comm%TYPE,

vdeptno in emp.deptno%TYPE

)

IS

  BEGIN

     INSERT INTO EMP(empno , ename, job, mgr, hiredate, sal, comm, deptno) VALUES(vempno ,vename , vjob, vmgr, vhiredate, vsal, vcomm, vdeptno);

       COMMIT;

   END;

1. EmpDao.java에서 getEmpList함수 변경

void getEmpList(EmpDto dto);

2. EmpDto.java에서 아래 추가

private List<EmpDto> list;

    

public List<EmpDto> getList() {

return list;

}

public void setList(List<EmpDto> list) {

this.list = list;

}

3. Emp.xml 변경

<resultMap id="emplist" type="kr.or.bit.dto.EmpDto"/>

    

<select id="getEmpList" statementType="CALLABLE">

{ call jk_EmpList(

#{list, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=emplist}

)}

</select>

<insert id="insertEmp" statementType="CALLABLE"

parameterType="kr.or.bit.dto.EmpDto">

{ call jk_insert_emp(

#{EMPNO, mode=IN, jdbcType=INTEGER},

#{ENAME, mode=IN, jdbcType=VARCHAR},

#{JOB, mode=IN, jdbcType=VARCHAR},

#{MGR, mode=IN, jdbcType=INTEGER},

#{HIREDATE, mode=IN, jdbcType=DATE},

#{SAL, mode=IN, jdbcType=INTEGER},

#{COMM, mode=IN, jdbcType=INTEGER},

#{DEPTNO, mode=IN, jdbcType=INTEGER}

)}

</insert>

4. MasterController.java 에서 메소드 변경

@RequestMapping("showallemp.htm")

    public String showAllEmp(Model model, EmpDto dto) {

        

        EmpDao dao = sqlsession.getMapper(EmpDao.class);

        dao.getEmpList(dto);

        

        model.addAttribute("list", dto.getList());

        

        return "emp.showAllEmp";

    }