PL/SQL : PROCEDURE

PROCEDURE : 리턴형이 없는 함수 → 기능 수행만 한다

프로시저는 함수와 달리 리턴형이 없다.

대신, 매개변수를 통해 값을 받아 온다!

 

매개변수(parameter) : 프로시저가 호출될 때 그 프로시저에 전달되는 값

변수(variable) : 프로시저나 트리거 내에서 사용되는 값

→ 이 때, 매개변수를 IN/OUT 으로 구분하여 연산에 필요한 값인지, 값을 받아오는 데 필요한지 등을 구분한다.

→IN : SQL 처리에 필요한 변수

→OUT: 값을 받아오는 변수

 

 

기본적인 형식은 아래와 같다.

CREATE [OR REPLACE] PROCEDURE proc_name(
매개변수
name IN VARCHAR2(34), --일반 변수(Call by Value)
addr OUT VARCHAR2(100), --메모리 주소(Call by Reference)
no INOUT NUMBER --
)
IS
--변수 설정
BEGIN
--구현부
END;
/

PROCEDURE를 실행할 때는 EXECUTE 라는 명령을 사용해 주어야 하고,

사용시 발생한 오류를 확인하려면 show error; 를 사용한다.

--PROCEDURE 실행할 때는 EXECUTE 사용
EXECUTE proc_name(매개변수);
--'컴파일 오류와 함께 프로시저가 생성되었습니다' 일 때 어떤 에러인지 확인
show error;

 

 

 

예시

--데이터 첨부 = IN
CREATE OR REPLACE PROCEDURE studentInsert(
 pName IN student.name%TYPE,
 pKor IN student.kor%TYPE,
 pEng IN student.eng%TYPE,
 pMath IN student.math%TYPE
)
IS
BEGIN
 INSERT INTO student VALUES(
  std_sno_seq.nextval,
  pName, pKor, pEng, pMath
 );
 COMMIT;
END;
/

→ pName, pKor, pEng, pMath 라는 변수를 선언하고,

각 변수에 부여된 값들을 이용해서 student 테이블에 데이터를 INSERT해 준다.

즉,  execute studentInsert('kim', 100, 100, 90); 이라는 SQL문을 작성하여 실행하면 

INSERT INTO student VALUES(...) 와 같은 긴 SQL문을 작성할 필요가 없어 간편하다.

 

 

--데이터 삭제 = IN
CREATE OR REPLACE PROCEDURE studentDelete(
 pSno student.sno%TYPE
)
IS
BEGIN
 DELETE FROM student
 WHERE sno = pSno;
 COMMIT;
END;
/

→ pSno라는 학생번호 변수를 선언하고, 해당 학생번호와 일치하는 학생의 데이터를 삭제한다.

execute studentDelete(103); 을 실행하면 학생번호가 103번인 학생의 데이터를 삭제해 준다. (마찬가지로 간편)

 

 

--데이터 수정 = IN
CREATE OR REPLACE PROCEDURE studentUpdate(
 pSno IN student.sno%TYPE,
 pName IN student.name%TYPE,
 pKor IN student.kor%TYPE,
 pEng IN student.eng%TYPE,
 pMath IN student.math%TYPE
 )
IS
BEGIN
 UPDATE student SET
	name=pName,
	kor=pKor,
	eng=pEng,
	math=pMath
	WHERE sno=pSno;
 COMMIT;
END;
/

→ 마찬가지로 pSno와 학생번호가 일치하는 학생의 pName, pKor,.. 등의 값을 간편하게 변경할 수 있다.

 

 

--데이터 읽기-상세보기 = OUT
--학번을 넣어주고 나머지 값들을 받아와라
CREATE OR REPLACE PROCEDURE studentDetailData(
 pSno IN student.sno%TYPE,
 pName OUT student.name%TYPE,
 pKor OUT student.kor%TYPE,
 pEng OUT student.eng%TYPE,
 pMath OUT student.math%TYPE,
 pSum OUT NUMBER,
 pAvg OUT NUMBER
 )
IS
BEGIN
 SELECT name, kor, eng, math, (kor+eng+math),
	ROUND((kor+eng+math)/3.0,2) 
 INTO pName, pKor, pEng, pMath, pSum, pAvg
 FROM student WHERE sno=pSno;
END;
/

→ 학생번호 pSno를 받아(IN), 나머지 데이터를 내보내(OUT) 받아오는 프로시저이다.

단, 이 때는 아래와 같이 별도로 데이터를 받아 올 변수를 선언해 주어야 한다.

 

 

--값을 받아오려면 아래 처리가 필요
VARIABLE pName VARCHAR2(34)
VARIABLE pKor NUMBER
VARIABLE pEng NUMBER
VARIABLE pMath NUMBER
VARIABLE pSum NUMBER
VARIABLE pAvg NUMBER
execute studentDetailData(4,:pName,:pKor,:pEng,:pMath,:pSum,:pAvg);
PRINT pName;

 

 

 

응용(참고)

--데이터 전체보기 = OUT
CREATE OR REPLACE PROCEDURE studentListData(
 pResult OUT SYS_REFCURSOR
)
IS
BEGIN
 OPEN pResult FOR
  SELECT * FROM student
  ORDER BY sno ASC;
END;
/

 

package dao;

public class StudentVO {
	private int sno,kor,eng,math,total;
	private double avg;
	private String name;
	public int getSno() {
		return sno;
	}
	public void setSno(int sno) {
		this.sno = sno;
	}
	public int getKor() {
		return kor;
	}
	public void setKor(int kor) {
		this.kor = kor;
	}
	public int getEng() {
		return eng;
	}
	public void setEng(int eng) {
		this.eng = eng;
	}
	public int getMath() {
		return math;
	}
	public void setMath(int math) {
		this.math = math;
	}
	public int getTotal() {
		return total;
	}
	public void setTotal(int total) {
		this.total = total;
	}
	public double getAvg() {
		return avg;
	}
	public void setAvg(double avg) {
		this.avg = avg;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
}

프로시저를 실행할 때는 PreparedStatement가 아니라 CallableStatement 를 선언한다.

 

package dao;
//오라클에 있는 데이터 읽기 -> 웹
import java.util.*;

import oracle.jdbc.OracleTypes;

import java.sql.*;
public class StudentDAO {
	private Connection conn;
	private CallableStatement cs;
	private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
	public StudentDAO() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch(Exception ex) {}
	}
	
	public void getConnection() {
		try {
			conn = DriverManager.getConnection(URL,"hr","happy");
		}catch(Exception ex) {}
	}
	public void disConnection() {
		try {
			if(cs!=null) cs.close();
			if(conn!=null) conn.close();
		}catch(Exception ex) {}
	}
	
	public List<StudentVO> stdListData(){
		List<StudentVO> list = new ArrayList<StudentVO>();
		try {
			getConnection();
			//procedure는 함수호출->SQL문이 필요없음
			String sql = "{CALL studentListData(?)}";
			cs = conn.prepareCall(sql);
			cs.registerOutParameter(1, OracleTypes.CURSOR);
			cs.executeQuery();//함수가 호출되어 값이 다 채워짐
			ResultSet rs = (ResultSet)cs.getObject(1);
			
			while(rs.next()) {
				StudentVO vo = new StudentVO();
				vo.setSno(rs.getInt(1));
				vo.setName(rs.getString(2));
				vo.setKor(rs.getInt(3));
				vo.setEng(rs.getInt(4));
				vo.setMath(rs.getInt(5));
				
				list.add(vo);
			}
		}catch(Exception ex) {
			ex.printStackTrace();
		}finally {
			disConnection();
		}
		return list;
	}
}