데이터베이스 제 8강[KNU 2021-2]

👨‍💻🏫KNU 2021-2 SW & media 데이터베이스 필기노트 8

1. 삽입SQL

1. 일반/웹 프로그래밍 언어에 SQL을 삽입하는 방법
2. SQL자체의 기능을 확장시킨 변수,제어,입출력 등의 기능을 추가한 새로운 언어를 사용하는 방법(스토어드 프로시저)
3. 4GL: 비주얼 프로그래밍에서 데이터 관리기능을 사용하는 방법

과정에서는 1을 개념만 잡고 2를 중점적으로 다뤄본다.

1 의 삽입SQL은 ESQL(EmbeddedSQL)이라 불린다. 일반 명령문과 구분하기위해 ESQL앞에는 EXEC SQL을 붙인다. 프로그래밍언어에서 선언된 변수를 ESQL에서 사용할떄에는 변수명앞에 콜론을 붙여 구분한다. ESQL내에서 사용할 변수는 BEGIN DECLARE SECTION과 END DECLARE SECTION문 내에서 선언된다.

2. 스토어드 프로시저

MYSQL에서 제공하는 프로그래밍 기능으로 쿼리의 집합(모듈)으로 어떤 동작을 일괄처리하는데 쓰인다. 프로그래밍 언어의 함수 개념이며 내부에서 쓰이는 조건문과 반복문은 자체적인 문법을 사용한다.

--스토어드프로시저 정의 시작
DELIMITER $$
CREATE PROCEDURE a_proc()
BEGIN
...
END $$

--스토어드프로시저 정의 종료
DELIMITER ;

--스토어드프로시저 호출
CALL a_proc();

2.1. 스토어드 프로시저의 입출력

C계열, 파이썬 언어를 예로들면 함수의 인자(파라미터)는 IN, return값은 OUT이다.
예시를 보자면 아래에서 bar, baz 는quux, corge에 해당되며 END에서 quuz값은 @qux에 대입된다.

---생략
IN quux INT;
IN corge BOOL;
OUT quuz INT;
---생략
CALL foo(bar, baz, @qux); 

2.2. 스토어드 프로시저의 조건문

파이썬을 예로들면 if(foo==bar): … … …

의 조건 if(foo==bar):는 IF foo=bar THEN으로 나타내며 다음줄부터 실행문이 들어간다. 파이썬처럼 인덴트(들여쓰기)가 있어야할 강제성은 없다. 실행문의 종료는 END IF로 명시되기 때문이다. 유의할점은 ==이 아닌 =을 사용한다는점이다. 대입은 SET foo=1과같은 방식으로 이루어진다.

CREATE PROCEDURE testProc()
BEGIN
IN foo INT
DECLARE bar INT; --변수 정의
SET foo = 1; --변수에 값 대입

IF foo = bar THEN
SELECT '같습니다’;
ELSE
SELECT '다릅니다’;
END IF;

END $$
DELIMITER;
CALL testProc(1);

2.3. 스토어드 프로시저의 입출력

스토어드 프로시저의 반복문 WHILE문은 C계열 언어의 CONTINUE, BREAK와 같은 명령어인 ITERATE와 LEAVE를가진다. 다중 반복문을 위해 라벨을 지정 가능하다.

CREATE PROCEDURE testProc()
BEGIN
IN bar INT;
OUT foo INT;
SET foo = 1;

whileFunc: WHILE(foo<10) DO --10의 배수가 되기전까지 반복 라벨인 WhileFunc작성
IF(foo*bar<100);
SET foo=foo+1;
ITERATE whileFunc; --100미만이면 반복
ELSE
LEAVE whileFunc --100을 넘겼다면 반복 종료
END IF
END WHILE

END $$
DELIMITER;
CALL testProc(9,@result);

2.4. 동적SQL

동적 SQL은 스토어드 프로시저에 비해 간단한 리턴을 원하는 경우에 사용할수있다. 인자로는 ?을 사용한다. PREPARE문으로 정의하며 EXECUTE로 실행 DEALLOCATE PREPARE로 준비된 실행문 해제의 순서로 이루어진다.

CREATE TABLE myTable (id INT AUTO_INCREMENT PRIMARY KEY, mDate DATETIME); --id는 자동 증가로 입력할 필요가 없다, 삽입시 NULL사용
SET @curDATE = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간
PREPARE myQuery FROM 'INSERT INTO myTable VALUES(NULL, ?)'; EXECUTE myQuery USING @curDATE;
DEALLOCATE PREPARE myQuery;
SELECT * FROM myTable;

2.5. 스토어드 함수

스토어드 함수는 매개변수가 파라미터로만 사용되고 리턴문을 사용한다는점에서 프로시저에 비해 일반 프래그래밍 언어의 함수와 좀 더 유사하다. 또한 내부에서 쿼리를 사용할 수 없어 계산 처리에 적합하다.

다음 시스템변수를 1로 설정한 후 사용 가능하다. SET GLOBAL log_bin_trust_function_creators = 1;

DROP FUNCTION IF EXISTS getAgeFunc; 
DELIMITER $$
CREATE FUNCTION getAgeFunc(bYear INT)
  RETURNS INT 
BEGIN
DECLARE age INT;
SET age = YEAR(CURDATE()) - bYear; 
RETURN age;
END $$
DELIMITER;
....
SELECT getAgeFunc(1998) --프로시저와 다르게 CALL이 아닌 SELECT를 사용함에 유의
SELECT getAgeFunc(1998) INTO @age1998 --변수에 값저장

2.6. 커서

커서의 작동 순서
_
커서 선언
반복 조건 선언	테이블 끝(EOF)에서 실행할 명령 결정
커서 열기
커서로부터 데이터 로드	LOOP
데이터 처리	LOOP
커서 종료

DROP PROCEDURE IF EXISTS cursorProc;
DELIMITER $$
CREATE PROCEDURE cursorProc()
BEGIN
DECLARE foo INT;
DECLARE sums INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부(기본은 FALSE)
DECLARE userCusor CURSOR FOR SELECT bar FROM TBL; -- TBL의 bar로드
DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE; -- 행의 끝에서 endOfRow에 TRUE 대입
OPEN userCursor; -- 커서 열기
  cursor_loop: LOOP -- 루프 시작
    FETCH userCursor INTO foo; -- foo에 bar대입
      IF endOfRow THEN -- 더 이상 읽을 행이 없으면 LOOP 종료 
        LEAVE cursor_loop;
      END IF;
  SET sums = sums + foo; 
END LOOP cursor_loop;
-- var의 합계
SELECT sums;
CLOSE userCursor; -- 커서 닫기 
END $$
DELIMITER ;

...

CALL cursorProc;