SQL - Procedure 프로시저란?

Procedure 프로시저 란? 

여러개의 SQL을 하나의 SQL처럼 정리하여 사용하고자 하는 것.

실행결과를 반환하지 않는다. 즉, return 이 없다. 

서버에서 실행되기 때문에 속도가 빠름. 서버에 저장됨 (=stored procedure)

SQL에서 사용할 수 없다.

ex) select 프로시저()  (X) 

   call 프로시저()  (O)

Function은 주로 간단한 값 계산.

 Procedure는 복잡한 계산의 결과를 얻기 위해 계산 과정을 쪼개놓은것.

예를 들어 어떤 테이블의 데이터를 가지고 여러 테이블의 데이터를 새로 만들거나, 없는 데이터를 찾아서 자동적으로 넣어주는 경우 또는 월별 통계를 내는 경우 등등의 경우 사용된다. 

Function으로도 기능 구현은 가능하지만, 펑션은 하나의 결과값만을 리턴 하는 반면, 프로시저는 최종결과까지 계속해서 중간결과를 출력할 수 있다. (like println() in java)

예를 들어 전기요금을 구하는 기능 구현시,

펑션은 최종 전기요금만을 리턴하지만 프로시저로 구현할 경우, 내가 쓴 전력랑, 기본 요금, 전력요금, 총합계 등등의 원하는 정보를 순차적으로 출력하고 알 수 있다. 

Procedure 쿼리

예제 1) 전기요금 구하기.

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_calc_elecity_amount`(IN p_quantity INT)
BEGIN
DECLARE _basicFee INT; -- 변수선언
DECLARE _fee INT;

SET _basicFee = CASE 
WHEN p_quantity > 400 THEN 7300
WHEN p_quantity > 200 THEN 1600
ELSE 910
END ;

SELECT CASE WHEN p_quantity > 400 THEN (200*93.3)+(200*187.9)+(p_quantity-400) * 280.6
WHEN p_quantity > 200 THEN (200*93.3)+((p_quantity-200) * 187.9)
ELSE p_quantity *93.3
END INTO _fee;

SELECT 
p_quantity as '사용량',
_basicFee as '기본요금',
_fee as '전력량요금'
FROM DUAL;

END

** Procedure안에 function이 포함될 수 있다. 

예제 2) 4대보험료 구하기

[

입력)  3500000 p_salary

출력)

국민연금     건강보험     장기요양     고용보험  

150880     106080     7820     22100 

국민연금 : 9%(사업주가 50%부담) : (p_salary * 0.09) / 2

건강보험: 6.24% (사업주가 50% 부담) : (p_salary * 0.0624) / 2

장기요양: 건강보험 금액의 7.38%  ((p_salary * 0.0624) / 2)* 0.0738

고용보험: 0.65% p_salary * 0.0065 

]

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_calc_insurance`(IN p_salary INT)
BEGIN

DECLARE _nation INT; -- 국민연금
DECLARE _health INT; -- 건강
DECLARE _care INT; -- 장기요양
DECLARE _work INT; -- 고용
DECLARE _sum INT; -- 총합 

SET _nation = (p_salary * 0.09) / 2;
SET _health = (p_salary * 0.0624) / 2;
SET _care = _health* 0.0738;
SET _work = p_salary * 0.0065;
SET _sum = _nation+_health+_care+_work ;


SELECT 
p_salary as '소득',
_nation as '국민연금',
_health as '건강보험',
_care as '장기요양',
_work as '고용보험',
_sum as '4대보험 총합'

FROM DUAL;
END

예제3) 회문 검사.

[

회문 palindrome

입력된 문자역이 회문(거꾸로 읽어도 제대 로 읽는것과 같은 문장이나 낱말)

function : fn_is_palindrome

    입력: tenet, 출력: yes

    입력:abcde, 출력: no

]

STRCMP('문자열1','문자열2') 비교 시 같으면 0, 다르면 -1 

REVERSE('문자열1') > 문자열 1을 반대로 .. .

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_is_palindrome`(_input VARCHAR(10)) RETURNS varchar(11) CHARSET utf8mb4
BEGIN

DECLARE _temp VARCHAR(10);
DECLARE _result VARCHAR(10);

SET _temp = REVERSE(_input);
SET _result = IF( STRCMP(_input, _temp) = 0, 'yes', 'no');

RETURN _result;
END