[DB] 05. 데이터베이스 프로그래밍

728x90

Section 01. 개념

DBMS에 데이터 정의, 저장된 데이터를 읽어와, 데이터를 변경하는 프로그램 작성 과정

SQL을 포함함!

SQL 전용 언어 사용 방법

SQL 자체의 기능 확장하여 변수,제어,입출력 등의 기능을 추가한 새로운 언어 사용
오라클은 저장 프로그램 언어 사용, SQL 서버는 T-SQL 언어 사용

일반 프로그래밍 언어에 SQL 삽입하여 사용 방법

자바, C, C++ 등 일반 프로그래밍 언어에 SQL 삽입
일반 프로그래밍 언어로 작성된 응용 프로그램에서 데이터베이스에 저장된 데이터를 관리, 검색
삽입된 SQL문은 DBMS의 컴파일러가 처리

웹 프로그래밍 언어에 SQL 삽입 사용 방법

호스트 언어가 JSP, ASP, PHP 등 웹 스크립트 언어인 경우

4GL (4th Generation Language)

데이터베이스 관리 기능과 비주얼 프로그래밍 기능을 갖춘 ‘GUI 기반 소프트웨어 개발 도구’를 사용하여 프로그래밍
Delphi, Power Builder, Visual Basic 등

DBMS 플랫폼과 데이터베이스 프로그래밍 유형

DBMS 종류와 특징

Section 02. 저장 프로그램 Stored Program

: 데이터베이스 응용 프로그램 작성에 사용하는 MySQL의 SQL 전용 언어

SQL문에 변수, 제어, 입출력 등의 프로그래밍 기능 추가 → SQL만으로 처리 어려운 문제 해결

저장 프로그램은 Workbench에서 바로 작성, 컴파일 후 결과 실행

프로시저

CREATE PROCEDURE문 사용

선언부 : 변수, 매개변수 선언
실행부 (BEGIN-END) : 프로그램 로직 구현

매개변수 : 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값

변수 : 저장 프로시저/트리거 내에서 사용되는 값

/* 소스코드 설명문 */

-- 설명문 한 줄이면 이중 대시

삽입 작업 하는 프로시저

복잡한 조건의 삽입 작업을 인자 값만 바꿔서 수행 o, 저장했다가 필요 시 호출하여 사용

-- Book 테이블에 한 개의 튜
delimiter //
CREATE PROCEDURE insertBook (
    IN myBookID INTEGER,
    IN myBookName VARCHAR(40),
    IN myPublisher VARCHAR(40),
    IN myPrice INTEGER)
BEGIN
		INSERT INTO book(bookid, bookname, publisher, price)
			VALUES(myBookID, mybookname, mypublisher, myprice);
END;
//
delimiter ;

CALL insertbook(15, '스포츠과학', '마당과학서적', 25000);
SELECT * FROM book;

매개변수 : myBookID, myBookName, myPublisher, myPrice

CALL 명령 : 저장 프로시저나 함수를 실행하고 그 결과를 가져올 때 사용

💡 delimiter // 종결문을 세미콜론이 아닌 //로 만듦
~~
//
delimiter ; 다시 종결문을 세미콜론으로 만듦

SQL문을 좀 더 직관적으로 작성하기 위해 사용 //나 $$ 많이 씀

제어문 사용하는 프로시저

어떤 조건에서 어떤 코드가 실행되어야 하는지 제어하기 위한 문법

절차적 언어의 구성 요소를 포함

구문 의미 문법

구문	의미	문법
delimiter	구문 종료 기호 설정	delimiter //
BEGIN-END	프로그램 문 블록화 중첩 가능	BEGIN SQL문 END
IF-ELSE	조건의 검사 결과에 따라 문장을 선택적으로 수행	IF 조건 THEN SQL문 [ELSE SQL문] END IF;
LOOP	LEAVE문 전까지 LOOP 반복	[label:] LOOP SQL문 \| LEAVE [label] END LOOP
WHILE	조건이 참일 때 WHILE문의 블록 실행	WHILE 조건 DO SQL문 \| BREAK \| CONTINUE END WHILE
REPEAT	조건이 참일 때 REPEAT문의 블록 실행	[label:] REPEAT SQL문 \| BREAK \| CONTINUE UNTIL 조건 END REPEAT [label:]
RETURN	프로시저 종료 상태값 반환 가능	RETURN [<식>]

label 없이 leave면 가장 안쪽 루프문 빠져나감

while문은 루프 진입 전 조건을 검사

repeat문은 루프 실행 후 조건 검사

-- Book 테이블에 저장된 도서의 평균가격 반환하는 프로시저
delimiter //
CREATE PROCEDURE AveragePrice (
	OUT AverageVal INTEGER )
BEGIN
	SELECT AVG(price) INTO AverageVal
	FROM book 
  WHERE price IS NOT NULL;
END;
//
delimiter ;
/* 프로시저 실행 및 테스트 */
CALL AveragePrice(@myValue);
SELECT @myValue;

커서 사용하는 프로시저

cursor는 실행 결과 테이블을 한 번에 한 행씩 처리하기 위해, 테이블의 행을 순서대로 가리키는 데 사용

키워드	역할
CURSOR 커서이름 IS 커서정의	커서 생성
OPEN 커서이름	커서 사용 시작
FETCH 커서이름 INTO 변수	행 데이터 가져옴
CLOSE 커서이름	커서 사용 끝냄

-- orders 테이블의 판매 도서에 대한 이익을 계산하는 프로시저
delimiter //
CREATE PROCEDURE interest()
BEGIN
	DECLARE myinterest INTEGER DEFAULT 0.0;
    DECLARE price INTEGER;
    DECLARE endOfRow BOOLEAN DEFAULT false;
    -- orders테이블의 판매가격 가져오는 커서 선언
    DECLARE InterestCursor CURSOR FOR
		SELECT saleprice FROM orders;
    DECLARE CONTINUE handler
		-- 찾지 못하면 endOfRow를 true로
		FOR NOT FOUND SET endOfRow=true;
	OPEN InterestCursor;
	cursor_loop : loop
		FETCH InterestCursor INTO price;
		IF endOfRow THEN LEAVE cursor_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 ;
/* 프로시저 실행 및 테스트 */
call interest();

DECLARE CONTINUE HANDLER : 내장 핸들러(handler)를 정의하는 데 사용되며, 예외를 처리하거나 특정 조건이 발생했을 때 원하는 동작을 수행하도록 지시함

트리거 trigger

데이터의 변경 (insert, delete, update) 문이 실행될 때 자동으로 따라서 실행되는 프로시저

BEFORE 트리거
AFTER 트리거

-- 새로운 도서 삽입 후 자동으로 Book log 테이블에 삽입한 내용을 기록하는 트리거
SET global log_bin_trust_function_creators=ON; /* 실습을 위해 root 계정에서 실행 */

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

delimiter //
CREATE TRIGGER AfterInsertBook
-- book 테이블에 새로운 레코드(new) 삽입된 후 실행, 각 행에 대해 실행됨 
AFTER INSERT ON Book FOR EACH ROW
BEGIN
	DECLARE average INTEGER;
	INSERT INTO Book_log
		VALUES (new.bookid, new.bookname, new.publisher, new.price);
END;
//
delimiter ;
/* 프로시저 실행 및 테스트 */
INSERT INTO Book VALUES(14, '스포츠과학 1', '이상미디어', 25000);
SELECT * FROM Book WHERE bookid=14;
SELECT * FROM book_log WHERE bookID_L='14';

💡 일반적으로 MySQL에서 사용자 정의 함수와 프로시저를 만들 때, 데이터베이스 관리자는 SUPER 권한이 있어야 합니 다. SUPER 권한이 없는 사용자도 사용자 정의 함수와 프로시저를 만들고 사용할 수 있게 됩니다.

💡new : MySQL 트리거(Trigger)에서 사용되는 특별한 키워드 트리거가 실행되면서 새로 삽입된 행(레코드)에 대한 열 값 을 참조하는 데 사용
즉, **new**는 트리거가 작동하는 동안 새로 추가된 행의 데이터에 접근할 때 사용되며, 해당 행의 열 값을 가져오는 데 활용
- AFTER INSERT 트리거: **new**를 사용하여 새로 삽입된 행의 열 값을 가져올 수 있음
- AFTER UPDATE 트리거: **new**를 사용하여 업데이트된 행의 새로운 열 값을 가져올 수 있음
- AFTER DELETE 트리거: **new**는 삭제된 행의 열 값을 참조하는 데 사용되지 않고 OLD를 사용
ex) SET deleted_book_name = OLD.bookname;

사용자 정의 함수

delimiter //
CREATE FUNCTION fnc_Interest(price INTEGER) 
	RETURNS INT
BEGIN
	DECLARE myInterest INTEGER;
    -- 가격이 3만원 이상이면 10%, 3만원 미만이면 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;

구분 프로시저 트리거 사용자 정의 함수

구분	프로시저	트리거	사용자 정의 함수
공통점	저장 프로시저
정의 방법	CREATE PROCEDURE 문	CREATE TRIGGER 문	CREATE FUNCTION 문
호출 방법	CALL 문으로 직접 호출	INSERT, DELETE, UPDATE 문 실행 시 자동 실행	SELECT 문에 포함
기능의 차이	SQL 문으로 할 수 없는 복잡한 로직 수행	기본값 제공, 데이터 제약 준수, SQL 뷰의 수정, 참조무결성 작업 등 수행	속성 값 가공하여 반환, SQL 문에서 직접 사용