[데이터베이스] 13주차 | 프로시저, 함수

https://docs.microsoft.com/ko-kr/sql/connect/jdbc/using-a-stored-procedure-with-input-parameters?view=sql-server-ver15  --> 강의 장료 참고된 곳 (microsoft 홈페이지)

입력 매개 변수가 있는 저장 프로시저 사용 - JDBC Driver for SQL Server

 

실습에 참고되는 대학교 데이터베이스 스키마 다이어그램

# 프로시저

procedural extensions and stored procedures

• 데이터베이스 안의 프로시저에 저장하여 정의
• 프로시저는 while 문, repeat 문, if-then-else 문, for 반복문, case문 사용 가능
• call 구문을 통해 호출됨
• in 은 입력 받을 값을 저장하고 있는 매개변수
• out 은 결과 값을 반환하기 위해 프로시저 안에서 값이 지정될 매개변수
• 매개변수의 수가 다르면 같은 이름을 가진 여러 개의 프로시저들을 허용
• 장점) 프로시저에 접근하는 다수의 응요 프로그램을 허용한다.
• 장점2) 응용 프로그램의 변경없이 비즈니스 규칙만 변경 가능하다
• 응용프로그램 코드는 데이터베이스릴레이션의 직접적인 변경대신 저장된 프로시저 호출 가능하다

# 함수

- 학과의 이름을 주어서 그 학과의 교수의 수를 반환하는 함수

- 변수들앞에는 @를 붙인다.

create function dept_count(@dept_name varchar(20))
returns integer
as 
begin
    declare @d_count integer;
    select @d_count = count (*)
    from instructor 
    where instructor.dept_name = @dept_name
    return(@d_count);
end

- 12명이상의 교수를 가진 모든 학과의 이름과 예산을 구하라

select dept_name, budget
from department
where dept_count (dept_name ) > 12

- jdbc로

import java.sql.*;
import java.sql.SQLException;

public class sample7{

   public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            DatabaseMetaData dbmd = conn.getMetaData(); 
            CallableStatement cstmt = null;
            try { 
            	cstmt = conn.prepareCall("{? = call dbo.dept_count (?)}");
	            cstmt.registerOutParameter(1,java.sql.Types.VARCHAR);
	            cstmt.setString(2, "Accounting");
            	cstmt.execute(); // Execute the callable statement
            	String outParam = cstmt.getString(1);
            	System.out.println("Output: "+outParam);
            } catch (Exception e) {
            	System.out.println("Exception message: "+e.getMessage());
            	}
            	conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        }
   }

}

cstmt = conn.prepareCall("{? = call dbo.dept_count (?)}");
cstmt.registerOutParameter(1,java.sql.Types.VARCHAR);
cstmt.setString(2, "Accounting");

?의 위치가 중요하다
registerOutParameter로 대입되는 변수(첫번째 물음표 자리)를 설정한다. (첫번째물음표, sql타입)
setString으로 두번째 물음표 자리에 매개변수 "Accounting"을 넣어준다.

- 함수) 매개변수로 주어진 학과명에 있는 교수의 정보

create function instructors_of(@dept_name char(20))
returns table as
return (select ID as r_id, name as r_name, dept_name as r_dept_name, salary as r_salary
             from instructor
             where instructor.dept_name = @dept_name)

함수 적용

sqlserver jdbc fuction table return 찾아서 해보기

 

- 파라미터가 없는 프로시저

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

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, output 둘 다 있는 프로시저

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

public static void executeStoredProcedure(Connection con) { 
  try { 
	CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}"); //함수호출
	cstmt.setInt(1, 5); // 첫번째 파라미터에 5 대입
	cstmt.registerOutParameter(2, java.sql.Types.INTEGER); // 두번째 파라미터는 INTEGER값이라고 설정
	cstmt.execute(); 
	System.out.println("MANAGER ID: " + cstmt.getInt(2)); // 두번째 파라미터에 2를 전달하고 출력
  } catch (Exception e) { 
	e.printStackTrace(); 
  } 
}

* 디폴트는 INPUT, sql 함수작성에서 두번째 값을 OUTPUT으로 작성한 상황

함수 매개변수 두번째값(물음표)이 OUTPUT

 

callablestatement

preparedstatement 둘의 차이

 

getupdatecount : 변경,삭제 등 갱신이 이루어진 다음에 갯수 반환해주는 메소드

 

# 오버로딩 (같은 이름, 다른 매개변수)

함수 - 파라미터 개수 다름

프로시저 - 파라미터 개수와 타입이 다름

 

## 프로시저와 함수를 위한 언어구조

sql은 범용 프로그래밍 언어같다. 

변수들은 dclare을 사용해서 선언되고 할당은 set문을 사용

begin ... end 사이에 다수의 sql구문을 포함할 수 있다.

begin atomic ... end 를 사용하게되면 모든 문장이 한번에 단일 트랜잭션으로 실행 가능

 

# while 과 repeat 문 :

declare n integer default 0; while n < 10 do set n = n + 1 end while

repeat set n = n – 1 until n = 0 end repeat

 

# 외부 언어 루틴

• 기본적인 기능조차 데이터베이스 제품들 마다 다른 문법과 의미를 가진다.
• SQL은 자바, C#, C, C++같은 프로그래밍 언어로 함수를 정의 할 수 있음
• 정의된 함수들로 SQL이 못하는 계산 수행 가능
• c#이나 자바에서 작성된 코드라면 샌드박스에서 실행하는 것 가능. 프로세스간 통시능ㄹ 피하는 것은 함수호출에 대한 비용을 많이 줄인다.

# 트리거

데이터베이스에서 일어나는 여러가지 갱신들이 적용되고 난 후에 지켜야 하는 규칙같은 것.

트리거는 데이터베이스의 수정(insert, update, delete)에 대한 부수효과로 시스템이 자동적으로 수행한는 문장이다.

무결성 제약조건을 구현하기 위해 사용될 수 있다. 데이터베이스 외부에 대한 갱신은 수행할 수 없다.

< 트리거 요구사항 >

1. 트리거가 실행될 시점을 명시해야 한다. 트리거가 검사돼야 하는 사건이나 실행을 위한 조건

2. 트리거가 실행될 때 수행해야 할 동작을 명시해야 한다.

# 트리거 after insrert  **중요**

create trigger timeslot_check1 after insert on section
referencing new row as nrow
for each row
when (nrow.time_slot_id not in (
  select time_slot_id
  from time_slot)) /* time_slot_id 는 time_slot 에  존재하면 안됨*/
begin
	rollback
end;

새로운 row를 nrow라고 정의하자.

norw의 time_slot_id가 when내부 sql문장에 없다면, insert한 것을 롤백

 

# 트리거 after delete

- 분명 삭제했는데, 다른 테이블에서 삭제한것을 여전히 참조하는 사실이 발견되면 삭제작업을 롤백

create trigger timeslot_check2 after delete on timeslot
referencing old row as orow
for each row
when (orow.time_slot_id not in (
    select time_slot_id
    from time_slot) 
  /* time slot id 에 대한 마지막 투플을  time slot 에서 삭제 */
  and orow.time_slot_id in (
  	select time_slot_id
	from section)) /* time_slot_id 는 여전히 section 에 참조됨*/
begin
	rollback
end;

# 트리거 after update

 

insert - nrow

delete - orow

update - nrow, orow