[실습] Stored Procedure (저장 프로시저)

1. Stored Procedure란?

- 쿼리문의 집합으로 어떤 동작을 일괄 처리하기 위한 용도로 사용

- 함수와 유사한 역할

- 모듈화시켜서 호출만 해서 실행 가능

 

 

 

2. 실습

1. procedure 생성 및 호출

use academicDB;

drop procedure IF EXISTS studentProc;
DELIMITER $$
 create procedure studentProc()
 BEGIN
 select * from student; 
 END $$
DELIMITER ;

call studentProc();/*호출*/

 

+) 참고 : procedure의 더 많은 사용법으로 궁금하지 않으면, 바로 다음 단계로 가도 된다.

1) 입력 매개변수 2개인 경우

drop procedure IF EXISTS studentProc2;
DELIMITER $$
 create procedure studentProc2
 (IN userName varchar(10), IN deptName varchar(20) )
 BEGIN
 select * from student where 
 sname = userName and sdept = deptName; 
 END $$
DELIMITER ;
call studentProc2('김철수', '컴퓨터');

 

2) 출력 매개변수 지정하기

drop procedure IF EXISTS testProc;
DELIMITER $$
 create procedure testProc(IN txtValue varchar(10), OUT outValue int )
 BEGIN
 insert into testTBL values(null, txtValue);
 select max(id) into outValue from testTBL; 
 END $$
DELIMITER ;
create table IF NOT EXISTS testTBL (
 id int auto_increment primary key,
 txt char(10)
);

call testProc('테스트값', @myValue);
select concat('현재 입력된 ID 값 ==>', @myValue);

 

3) 조건문 사용

drop procedure IF EXISTS ifelseProc;
DELIMITER $$
 create procedure ifelseProc(IN number int )
 BEGIN
 DECLARE eScore int;
 select score into eScore from enroll where sno = number;
 IF (eScore >= 90) THEN select 'A 학점';
 ELSE select 'B 학점';
 END IF;
 END $$
DELIMITER ;
call ifelseProc (20183654);

 

4) 커서 사용 = 다수의 행 질의 후 한 행씩 처리

drop procedure IF EXISTS cursorProc;
DELIMITER $$
 create procedure cursorProc()
 BEGIN
 DECLARE eScore int; -- score 변수
 DECLARE count int DEFAULT 0; -- 읽은 행 수
 DECLARE total int DEFAULT 0; -- 합계 
 DECLARE endOfRow boolean default FALSE; -- 행의 끝 여부
 DECLARE cur CURSOR FOR select score from enroll; -- 커서 선언
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET 
 endOfRow = TRUE; -- 행의 끝이면 endOfRow 변수에 TRUE를 대입
 OPEN cur; -- 커서 열기
 cursor_loop: LOOP
 FETCH cur INTO eScore;
 IF endOfRow THEN 
 LEAVE cursor_loop; -- 행의 끝이면 loop 종료
 END IF;
 SET count = count + 1;
 SET total = total + eScore;
 END LOOP cursor_loop; 
 select concat('수강생 평균 점수 ==> ', (total/count));
 CLOSE cur; -- 커서 닫기
 END $$
DELIMITER ;
CALL cursorProc();

/*학생별 평균 점수가 95점 이상이면 "전액장학금", 90점 이상이면 "반액 장학금"을 입력*/
use academicdb;
alter table student add scholarship varchar(20); /*테이블에 장학금열 추가*/

drop procedure IF EXISTS scholarProc;
DELIMITER $$
    create procedure scholarProc()
    BEGIN
    DECLARE number int;
    DECLARE avgScore double;
    DECLARE scholar varchar(20);
    
    DECLARE endOfRow boolean default FALSE;
    DECLARE cur CURSOR FOR select S.sno, avg(score) from enroll E inner join student S on E.sno = S.sno group by E.sno;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE;
    OPEN cur;
    cursor_loop: LOOP
    FETCH cur INTO number, avgScore;
    IF endOfRow THEN 
    LEAVE cursor_loop;
    END IF;
    CASE
    when (avgScore >= 95.0) then set scholar = "전액 장학금";
    when (avgScore >= 90.0) then set scholar = "반액 장학금";
    else set scholar = null;
    END CASE;
    update student set scholarship = scholar where sno = number;
    END LOOP cursor_loop; 
    CLOSE cur;
    select * from student;
    END $$
DELIMITER ;

CALL scholarProc();

 

 

2. JDBC에서 사용

- CallableStatement stmt = null;

- stmt = conn.prepareCall("call studentProc()");

- rs = stmt.executeQuery();

위 세 문장만 주의해서 쓰면 된다.

package test;

import java.sql.*;
public class JDBCtest_01 {
	public static void main (String[] args)
	{ 
		 try {
			 Class.forName("com.mysql.cj.jdbc.Driver");
			 System.out.println("OK!");
		 } 
		 catch(ClassNotFoundException ee) {
			 System.exit(0);
		 }
		 Connection conn = null;
		 String url = "jdbc:mysql://localhost/academicDB?serverTimezone=UTC";
		 //academicDB 사용
		 String id = "root"; 
		 String pass = "root";
		 CallableStatement stmt = null;//수정1
		 ResultSet rs = null;
		 try {
			 conn = DriverManager.getConnection(url, id, pass);
			 stmt = conn.prepareCall("call studentProc()");//수정2
			 rs = stmt.executeQuery();//수정3
			 while(rs.next()) {
				 System.out.println(rs.getInt(1)+", "+rs.getString(2)+", "+rs.getString(3)+", "+rs.getString(4));
			 }
			 rs.close();
			 stmt.close();
			 conn.close();
		 } 
		 catch(SQLException ee) {
			 System.err.println("error = " + ee.toString());
		 }
	}
}