개요

프로시저: 쿼리로 처리하기가 복잡한 것들을(절차적) 미리 정의를 해놓는것.

프로시저 vs 함수

1) 함수와 달리 리턴값이 없다는 것
2) 독립적인 수행 가능

call 하면 정의한 시퀀스대로 순차대로 sql문이 시행됨. 함수는 독립적 실행 x select 문에 포함되어 실행.

쿼리 내 결과가 복수의 열이 나와서 처리를 해야한다면, 파일 포인터가 커서의 역할을 한다.

Wait..

What is file pointer?

파일 포인터란 특정 파일을 오픈했을때 현재의 커서 위치라고 생각하시면 됩니다.
메모장으로 텍스트 파일을 오픈할경우 커서가 텍스트 파일 제일좌측상단에 위치하게 되죠. 이때의 파일 포인터는 0입니다. 키보드 방향키를 사용하여 커서위치를 옮기면 파일포인터는 커서의 위치로 이동하게 되는것입니다.
프로그램에서 파일을 오픈했을 경우 파일포인터는 0 이지만 fread, fgets함수
등을 사용하여 파일을 읽을 경우 파일포인터는 읽은 글자수만큼 이동하게
되는것입니다. 한번 파일포인터가 이동 했을경우 뒤로 읽기는 불가능하기
때문에 fseek함수를 사용하여 파일포인터를 뒤로 돌리면됩니다.

차이점이 있다면, DELIMITER 사용
프로시저 정의문이 끝나는 곳이 어디야?를 mysql에게 알려준다.
EX. // OR $$ 등의 세미콜론(;)을 제외한 특수문자를 이용

프로시저에 들어오는 INPUT 파라미터 를 기준으로, 프로시저를 CALL해서 사용

프로시저 안 다양한 제어문 사용가능.
프로그래밍 언어의 제어문에서의 BREAK와 비슷한 역할인 RETURN!(프로시저 종료)

실습을 해서
=> 나만의 코드 자산(라이브러리 등)을 만들자~

저장 프로그램의 제어문 구글링해서 실습해보기

what is 저장프로그램 in mysql?

MySQL Stored Program

ㅇ 나중에 절차적으로 실행할 수 있도록 서버 측에 저장되는 프로그램들을 총칭

 - Stored Function  : 수식에 사용되며 계산결과 값 만을 반환
 - Stored Procedure : 결과 세트를 생성 및 반환
 - Trigger          : 테이블이 INSERT,DELETE,UPDATE 될 때에 만 실행됨
 - Event            : 스케줄러에 저장되고 시간에 의해 실행됨

ㅇ 모두 동일한 문법으로 작성 처리됨

ㅇ 실행 방법
- 저장 함수 실행 : SELECT 문 내에서 호출 실행
- 저장 프로시저 실행 : CALL 프로시저명;으로 호출 실행

LABEL은 여러개를 사용할 때 이름을 지어주는 것.

예제 5-2 동일한 도서가 있는지 점검한 후 삽입하는 프로시저
동일한 도서가 있으면 BOOK의 가격만을 업데이트.

@ => 전역변수의 역할 수행

SELECT count(*) INTO mycount FROM Book
WHERE bookname LIKE myBookName;

중복되는 책의 개수를 센다.

IF mycount!=0 THEN # 중복되는 책 있음
SET SQL_SAFE_UPDATES=0; /* DELETE, UPDATE 연산에 필요한 설정 문 */
UPDATE Book SET price = myPrice
WHERE bookname LIKE myBookName;
ELSE # 중복되는 책 없을때
INSERT INTO Book(bookid, bookname, publisher, price)
VALUES(myBookID, myBookName, myPublisher, myPrice);

커서

파일포인터가 null일때 빠져나오듯이, 커서의 값이 null일때 빠져나온다.
파일을 open하듯이 open하고, 한줄 한줄 읽어오는것이 fetch.

끝날때까지 반복(loop) 후 끝나면 loop 종료

각각의 row을 읽고, 한 line line씩 읽어 처리.

ㅇ CURSOR
- 1 이상의 결과 set를 return 하기 위해 사용
- DECLARE cname CURSOR FOR SELECT ...
. OPEN : 커서를 사용하기 위해 fetch 전에 반드시 처리
. FETCH : 커서가 다음 row 로 이동
. CLOSE : 커서를 닫아줌

delimiter //
CREATE PROCEDURE Interest()
BEGIN
DECLARE myInterest INTEGER DEFAULT 0.0; # 필요한 변수들을 선언함.
DECLARE Price INTEGER;
DECLARE endOfRow BOOLEAN DEFAULT FALSE;
DECLARE InterestCursor CURSOR FOR
SELECT saleprice FROM Orders;
DECLARE CONTINUE handler 
FOR NOT FOUND SET endOfRow=TRUE; # 데이터가 탐색되지 않으면 loop에서 빠져나옴
OPEN InterestCursor;
cursor_loop: LOOP
FETCH InterestCursor INTO Price;
IF endOfRow THEN LEAVE cursor_loop; # loop 빠져나옴
END IF; # 끝이 아니라면 
IF Price >= 30000 THEN # 하나하나씩 비교
SET myInterest = myInterest + Price * 0.1;
ELSE
SET myInterest = myInterest + Price * 0.05;
END IF;
END LOOP cursor_loop;
CLOSE InterestCursor;
SELECT CONCAT(' 전체 이익 금액 = ', myInterest);
END;
//
delimiter ;

문법

ㅇ 주석 (comment) : /* */, #, -- (3 가지)

ㅇ 제어문

   -- IF 문
   IF expression THEN commands
		[ELSEIF expression THEN commands 		....]
		[ELSE commands]
	END IF;
	

   - CASE 문
     CASE expression
      WHEN value THEN statements
      [WHEN value THEN statements]
      [WHEN value THEN statements]
      .
      .
      [ELSE statements]
  	END CASE;

ㅇ 반복문
- LOOP ~ END (빠져나가기 : LEAVE, 처음으로가기 : ITERATE)
- REPEAT ~ UNTIL
- WHILE

트리거(trigger)

하드웨어 인터럽트 처리때 주로 사용하는 단어인 트리거
신호 => 이벤트 발생 => 어떤 action?
액션 처리.

트리거(Trigger)?

Table에 어떤 신호가 가해졌을 때 미리 정해진 활동이 자동으로 실행되는 것.

구체적으로 특정 테이블에 INSERT, DELETE, UPDATE 같은 "DML"문이 수행될 때, 데이터베이스에서 자동으로 동작.

사용자호출 필요없음, 자동으로 동작!

출처: https://ansan-survivor.tistory.com/1156

DML (데이터 조작 언어 : Data Manipulation Language-데이터 검색, 등록, 삭제, 수정) 문에 사용된다.

예제 5-5
새로운 도서를 삽입한 후 자동으로 Book_log 테이블에 삽입한 내용을 기록하는 트리거

5가지 항목들을 BACK-UP(새로운 테이블을 만듦) => 삭제 후 => 삭제된 데이터 백업(언제 지워졌는지? 누가 지웠는지 추가)

SET global log_bin_trust_function_creators=ON; /* 실습을 위해 root 계정에서 실행

아무 사용자나 접근하지 못하도록 => root 계정

위에 쿼리를 작성하지 않으면 madang db에서 트리거 생성 불가.

저장프로그램이라 프로시저와 유사.

/* madang 계정에서 실습을 위한 Book_log 테이블 생성해준다. */
# Book_log 테이블 만들어준다.

CREATE TABLE Book_log(
bookid_l INTEGER,
bookname_l VARCHAR(40),
publisher_l VARCHAR(40),
price_l INTEGER);

delimiter //
CREATE TRIGGER AfterInsertBook
AFTER INSERT ON Book FOR EACH ROW # INSERT 후 시행. Book 테이블의 ROW에 붙여라~(조건없는데, 조건을 달고 싶으면 IF문으로 달 수 있음)
BEGIN 
DECLARE average INTEGER; # 변수 설정.
INSERT INTO Book_log
VALUES(new.bookid, new.bookname, new.publisher, new.price); # new vs old: 삭제가 된 다음에 아예 사라지는 것이 아닌 old정보로 가져올 수 있음. 지금 등록되는 데이터라 new임. 이전 데이터는 old임)
END;
//
delimiter ;

입력파라미터 x

사용자 정의 함수

input, output이 없는 대신 return이 있다.

예제 5-6
판매된 도서에 대한 이익을 계산하는 함수

함수는 거의 스칼라 값을 이용

delimiter //
CREATE FUNCTION fnc_Interest(
Price INTEGER) RETURNS INT
BEGIN
DECLARE myInterest INTEGER;
-- 가격이 30,000원 이상이면 10%, 30,000원 미만이면 5%
IF Price >= 30000 THEN SET myInterest = Price * 0.1;
ELSE SET myInterest := Price * 0.05;
END IF;
RETURN myInterest;
END; //
delimiter ;

프로시저 vs 함수

1. return 값 유무
2. 독립적 사용 유무(프로시저는 call만 하면 절차대로 수행 후 실행, 함수는 그렇지 않음)

5장 연습문제 과제는 다음주에 미리 풀어보자

작성한 sql 전문

USE madang;

set global myvalue = 0; # global은 db전체에 대한 쿼리임.
-- 함수 내에서만 사용하려면 @만 사용해서 지역변수로 이용가능

-- 테이블 생성
CREATE TABLE Book_log(
bookid_l INTEGER,
bookname_l VARCHAR(40),
publisher_l VARCHAR(40),
price_l INTEGER);
# 단순 기록인 로그라 pk 필요 x

DROP TRIGGER IF EXISTS AfterInsertBook;
DELIMITER $$
CREATE TRIGGER AfterInsertBook
	AFTER INSERT
    ON Book
    FOR EACH ROW # INSERT 이벤트 후 붙인다. 어디에? Book 테이블에. 각각의 로우에!
BEGIN
INSERT INTO Book_log
VALUES(new.bookdid, new.bookname, new.publisher, new.price);
END; $$
DELIMITER ;



# 트리거는 ALTER 명령어로 수정 불가
# 수정을 하려면
# DROP TRIGGER IF EXISTS AfterInsertBook
# 으로 삭제 후 다시 작성 ^^;

-- 삽입한 내용을 기록하는 트리거 확인
INSERT INTO Book VALUES(16, '스포츠 과학 1', '이상미디어', 25000);
SELECT * FROM Book WHERE BOOKID=16;
SELECT * FROM Book_log WHERE BOOKID_L = '16'; -- 결과확인
 
 SELECT * FROM Book;
 
SELECT * FROM book_log;

CREATE TABLE Order_log( # 삭제 되기 전 데이터 저장)
	order_id INTEGER,
    book_id INTEGER,
    cust_id INTEGER,
    saleprice INTEGER,
    orderdate DATE
);


DROP TRIGGER IF EXISTS AfterDeleteOrder;
DELIMITER $$
CREATE TRIGGER AfterDeleteOrder
	AFTER DELETE
    ON orders
    FOR EACH ROW
    # DELETE 이벤트 후 붙인다. 어디에? orders 테이블에. 각각의 로우에!
BEGIN
		SET @myValue = 'Delete event!';
-- 		INERT INTO Order_log values(OLD.orderid, OLD.bookid, OLD.custid, OLD.saleprice, OLD.orderdate, curdate(), );
        
    
-- INSERT INTO Book_log
-- VALUES(new.bookdid, new.bookname, new.publisher, new.price);
END; $$
DELIMITER ;

SET @myValue = '';
DELETE FROM orders where orderid=1;

DELIMITER //
CREATE FUNCTION fnc_Interest(Price INTEGER)
RETURNS INT
BEGIN
DECLARE myInterest INTEGER;
-- 가격이 30000원 이상이면 10%, 30000원 미만이면 5%
IF Price >= 30000 THEN SET myInterest = Price * 0.1;
ELSE SET myInterest := Price * 0.05;
END IF;
RETURN myInterest;
END; //
DELIMITER ;

SELECT custid, orderid, saleprice, fnc_Interest(saleprice) interest
FROM Orders;

출처: https://scotthan.tistory.com/entry/fread-파일-포인터-커서 [Now or Never]