[데이터베이스] Ch5. SQL 고급2 - 비지니스 로직, 함수, 테이블 함수, 프로시저

비지니스 로직(Business Logic)

• 프로그램에서 비지니스 요구사항을 구현하는 부분
• e.g) 대학에는 다양한 비지니스 규칙들이 있고, 이 규칙들에 따라 데이터를 생성, 저장, 변경해야함. 이를 통틀어 비지니스 로직이라고 함.
  - 학생이 주어진 학기에 수강해야하는 과목 수는 15학점을 넘어선 안된다.
  - 교수가 1년에 가르쳐야 하는 최소 과목의 수는 12학점 이상이어야 한다.
  - 한 학생이 등록할 수 있는 전공의 최대 개수는 3개 까지 이다.
  

함수와 프로시저
function and procedure

• 비지니스 로직을 응용 프로그램에 인코딩 하는 대신, 데이터 베이스 안에 저장하고 SQL 구문에서 실행되도록 한다.
• 데이터베이스 시스템이 함수와 프로시저를 표준화 하기 전부터 각자 지원해왔기에, 대부분의 데이터베이스는 함수와 프로시저 관련 SQL 비표준 문법을 구현한다.
• SQL 의 절차형 구성 요소 또는 프로그래밍 언어에 의해 정의될 수 있음

SQL 함수 선언

create function function_name (param_list) // param_list : 인자들의 목록, 0개 이상의 인자
   returns return_type // 반환 타입
   language plpqsql // 함수가 사용하는 절차형 언어의 종류 명세
 as
$$
declare // 코드 블록 시작
-- variable declaration // 로직에서 사용할 변수 선언
begin // 로직 블록 시작
-- logic
end; // 로직 블록 끝
$$;

// 학과 이름을 입력 받아 그 학과에 소속된 교수의 수를 반환하는 함수.
// 생성 후 \df 로 확인 가능
create function dept_count ( dn varchar(20) ) // 학과 이름을 입력값으로 받고, 그 값을 dn 에 저장
returns integer
language plpqsql
as
$$
declare
   d_count integer;
begin
   select count(*) into d_count // count(*) 의 결과를 d_count 변수에 저장
   from instructor
   where instructor.dept_name = dn; // 입력값 dn 과 일치하는 instructor.dept_name
   return d_count;
end;
$$;

// 위에서 정의한 dept_count 함수는 2명 이상의 교수가 있는 모든 학과의 이름과 예산을 찾는데 사용 가능
select dept_name, budget
from department
where dept_count(dept_name) > 1

테이블 함수

• 테이블 함수: 테이블 자체를 함수의 결과로 반환할 수 있는 함수

create function function_name (param_list)
   returns table (column_list)
   language plpqsql
  as
$$
declare
-- variable eclaration
begin
-- body
end;
$$;

// 지정된 학과의 모든 교수 반환
create function instructor_of ( dn varchar(20) )
returns table (
   ID varchar(5),
   name varchar(20),
   dept_name varchar(20),
   salary numeric(8,2) )
language plpqsql
as $$
begin
  return query
   select i.ID, i.name, i.dept_name, i.salary
   from instructor i
   where i.dept_name = dn;
end;
$$;

SQL 프로시저

create procedure procedure_name (parameter_lsit) // 0개 이상의 인수
language plpqsql
// 프로시저는 returns 값이 없다!
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$;

// 학과명과 연봉 인상 금액을 입력 받아
// department 릴레이션 해당 학과의 예산을 증액 시키기
create procedure inc_dept_budget (
   dn varchar(20),
   amount numeric (12,2)
)
language plpqsql
as $$
begin
   update department
   set budget = budget + amount
   where dept_name = dn;
   commit;
end; $$;

// 호출(call) 구문을 사용하여 SQL 프로시저 또는 내장 SQL에서 호출할 수 있다.
call inc_dept_budget('Music', 10000);

외부 언어 루틴

• 앞에선 절차형 언어인 plpqsql 을 이용하여 함수와 프로시저를 선언.
• 프로그래밍 언어로 외부 언어 루틴을 정의하여 SQL 에서 사용하는 법을 배워보자
• 외부 언어 루틴은 SQL 에서 정의된 함수보다 효율적일 수 있으며, SQL에서 수행 불가능한 계산을 수행할 수도 있음.

// 외부 프로시저
create procedure dept_count_prc ( in dept_name varchar(20), out count integer )
language C
external name '/usr/avi/bin/dept_count_proc' // 외부 언어 루틴이 정의된 파일의 경로를 저장
// 외부 함수
create function dept_count ( dept_name varchar(20) )
returns integer
language C
external name '/usr/avi/bin/dept_count'

외부 언어 루틴에 대한 보안

• 외부 언어 루틴 파일은 보안에 취약할 수 있음.
  - C언어로 짜여진 프로그램은 포인터를 사용하여 메모리에 자유롭게 접근할 가능성 있음
  

• 보안 문제 처리 기술
  1. 샌드박스 기술: 질의 실행 프로세스 내의 샌드박스에서 외부 언어 루틴 실행
  - 외부 언어로 짜인 코드가 자신의 메모리 영역만 접근 가능.
  - JAVA 나 C# 과 같은 메모리 Safety 를 제공해주는 언어를 사용.
  2. 외부의 코드를 질의 실행 프로세스와는 별도의 프로세스에서 진행
  - 프로세스 간에 통신을 통하여 매개변수를 주고 받는다.

• 두 방식 모두 성능에 대한 오버헤드가 있음.