프로시저

# 프로시저

- 데이터베이스 안의 프로시저에 저장하여 정의

- 프로시저는 while 문, repeat 문, if-then-else 문, for 반복문, case문 사용 가능

- call 구문을 통해 호출됨

- in 은 입력 받을 값을 저장하고 있는 매개변수

- out 은 결과 값을 반환하기 위해 프로시저 안에서 값이 지정될 매개변수

- 매개변수의 수가 다르면 같은 이름을 가진 여러 개의 프로시저들을 허용

 

 

# SQL 프로시저

- dept_count 함수를 프로시저로

  create procedure dept_count_proc (in dept_name varchar(20),
                                      out d_count integer)
  begin
     select count(*) into d_count  
     from instructor  
     where instructor.dept_name = dept_count_proc.dept_name
  end

 

- 프로시저 사용

  call 구문을 통해 호출됨

      declare d_count integer;

      call dept_count_proc( ‘Physics’, d_count);

- JDBC와 같은 동적 SQL로부터 호출 가능

 

 

# 파라미터가 없는 프로시저 – Statement 객체

1. SQL

CREATE PROCEDURE GetContactFormalNames AS
BEGIN
       SELECT TOP 10 Title + ' ' + FirstName + ' ' + LastName AS FormalName
       FROM Person.Contact
END

2. JDBC

public static void executeSprocNoParams(Connection con) { 
  try { 
	Statement stmt = con.createStatement(); 
	ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}"); 
	while (rs.next()) { 
		System.out.println(rs.getString("FormalName")); } 
	rs.close(); 
	stmt.close(); 
  } 
  catch (Exception e) { 
	e.printStackTrace(); 
  } 
}

 

 

# input 파라미터만 있는 프로시저 – preparedStatement 객체

1. SQL

CREATE PROCEDURE dbo.usp.GetEmployeeManagers
      @EmployeeID INT
AS
BEGIN
:
END

2. JDBC

public static void executeSprocInParams(Connection con) { 
  try { 
	PreparedStatement pstmt = con.prepareStatement("{call 				dbo.usp.GetEmployeeManagers(?)}"); 
	pstmt.setInt(1, 50); 
	ResultSet rs = pstmt.executeQuery(); 
	while (rs.next()) { 
	  System.out.println("EMPLOYEE:"); 
	  System.out.println(rs.getString("LastName") + ", " + 
	  rs.getString("FirstName")); 
	  System.out.println("MANAGER:"); 
	  System.out.println(rs.getString("ManagerLastName") + ",
	  " + rs.getString("ManagerFirstName")); 
	  System.out.println(); 
	} 
	rs.close(); 
	pstmt.close(); 
  } 
  catch (Exception e) { 
	e.printStackTrace(); 
  } 
}

 

 

# input, output 파라미터 둘 다 있는 프로시저 – callableStatement 객체

1. SQL

CREATE PROCEDURE GetImmediateManager 
	@employeeID INT, @managerID INT OUTPUT 
AS 
BEGIN 
	SELECT @managerID = ManagerID 
	FROM HumanResources.Employee 
	WHERE EmployeeID = @employeeID 
END

2. JDBC

public static void executeStoredProcedure(Connection con) { 
  try { 
	CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}"); 
	cstmt.setInt(1, 5); 
	cstmt.registerOutParameter(2, java.sql.Types.INTEGER); 
	cstmt.execute(); 
	System.out.println("MANAGER ID: " + cstmt.getInt(2)); 
  } catch (Exception e) { 
	e.printStackTrace(); 
  } 
}