[데이터베이스] 8. ESQL(Embedded SQL)와 프로시저(Procedure)

ESQL

응용프로그램에 삽입된 SQL문을 의미한다. 응용프로그램에서 작성된 SQL문을 통해 DBMS에 접근하여 조회/갱신 등이 가능하다. 이때, 결과를 받기 위해 '커서'를 활용한다.

커서는 SQL문의 결과를 접근하기 위한 포인터이다.

void computeGPA{
EXEC SQL BEGIN DECLARE SECTION;
float oldgpa, newgpa; char id[9];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE SECTION;
EXEC SQL DECLARE gpaCursor CURSOR FOR
	SELECT id, gpa FROM student FOR UPDATE;
EXEC SQL OPEN gpaCursor; //학생 테이블 접근시작

while(1){
    EXEC SQL FETCH gpaCursor INTO :id, :oldgpa; //id와 oldgpa값을 가져옴
    if(NO_MORE_TUPLES)
    	break;
    EXEC SQL SELECT avg(grade) INTO :newgpa
    	FROM course_taken WHERE sid=:id;
    EXEC SQL UPDATE student
    SET gpa=:newgpa
    WHERE CURRENT OF gpaCursor;
    }
};

---

SQL에서의 변수

조회된 값의 저장, 조건에 사용 등을 위해 사용한다. 일반적인 C, C++과 비슷한 변수의 특성을 가진다.

SET @counter=100;

---

프로시저(Procedure)

프로시저란 어떠한 일련된 작업 집합으로 서버의 일부에 저장하여 필요 시 호출하여 사용한다. 유저로 하여금 DB 내부 정보에 대한 추상화가 가능하고, 네트워크 트래픽 감소, 질의 처리 향상, 보안과 같은 측면에서 장점이다.

 

#예제1. UNTIL의 조건을 만족할 때 까지 반복하는 프로시저

DELIMITER //
CREATE PROCEDURE doRepeat(p1 INT)
BEGIN
	SET @x = 0;
    REPEAT SET @x=@x+1;
    UNTIL @X>p1 END REPEAT;
END;//
DELIMITER ;

CALL doRepeat(1000);
SELECT @X;

위의 결과

IN. OUT 매개변수를 활용한 프로시저

# IN 타입 매개변수: 매개변수의 값을 전달
DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
BEGIN
	SELECT * FROM offices WHERE country=countryName;
END //
DELIMITER ;

CALL GetOfficeByCountry('France');



# OUT 타입 매개변수: 결과 값이 변수에 저장
DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(IN orderStatus VARCHAR(25), OUT total INT)
BEGIN
	SELECT count(orderNumber) into total FROM orders WHERE status = orderStatus;
END$$
DELIMITER ;

CALL CountOrderByStatus('Shipped', @total);

SELECT @total;

---

제어문

① CASE

DELIMITER /
CREATE PROCEDURE GetCustomerShipping(IN p_customerNumber INT(11), OUT p_shipping VARCHAR(30))
BEGIN
DECLARE customerCountry VARCHAR(30); #지역변수
SELECT country into customerCountry FROM customers
	WHERE customerNumber=p_customerNumber;
CASE customerCountry
	WHEN 'USA' THEN SET p_shipping ='2-day shipping';
   	WHEN 'CANADA' THEN SET p_shipping ='3-day shipping';
    ESLE SET p_shipping='5-day shipping';
END CASE;
END/
DELIMITER ;

② LEAVE/ ITERATE (C언어의 break, continue)

DELIMITER /
CREATE PROCEDURE test_mysql_loop()
BEGIN
DECLARE x INT; DECALRE str VARCHAR(255); #지역변수
SET x = 1; SET str = '';

loop_label: LOOP
    IF x>10 THEN LEAVE loop_label; END IF;
    SET x=x+1;
    IF (x mod 2) THEN ITERATE loop_label;
    ELSE SET str=CONCAT(str,x,',');
    END IF;
END LOOP;
SELECT str;
END/
DELIMITER ;

③ WHILE

DELIMITER /
CREATE PROCEDURE test_mysql_loop()
BEGIN
DECLARE x INT; DECALRE str VARCHAR(255); #지역변수
SET x = 1; SET str = '';

WHILE x<=5 DO
	SET str = CONCAT(str,x,',');
	SET x=x+1;
END WHILE;

SELECT str;
END/
DELIMITER ;

---

MySQL 커서

MySQL에서의 커서는 오직 읽기만 가능하며 쓰기/갱신은 불가능하다. 접근은 순차적으로 해야한다. 커서를 이용한 결과가 없을 경우를 대비하여 not found 핸들러 처리가 필요하다.

SET @email_list="";
DELIMITER /

CREATE PROCEDURE build_email_list(INOUT eamil_list VARCHAR(5000))
BEGIN
DECLARE v_finished INTERGER DEFAULT 0;
DECLARE v_email VARCHAR(100) DEFAULT "";
DECLARE eamil_cursor CURSOR FOR SELECT eamil FROM employees; #커서 정의
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished =1; #not found 핸들러

OPEN eamil_cursor;
get_email:LOOP
FETCH email_cursor INTO v_email;
IF v_finished=1 THEN LEAVE get_email; END IF;
SET eamil_list=CONCAT(v_email,';'.email_list);
END LOOP get_email;
CLOSE eamil_cursor;
END /
DELIMITER ;

CALL build_email_list(@email_list);
SELECT @email_list;