[DB] 데이터베이스 설계 및 관리 2

박소미·2024년 6월 22일

DB

목록 보기

14/14

두 개 이상 테이블에서 SQL 질의

SQL은 여러 개의 테이블을 이용하여 질의하는 두 가지 방법을 제공한다. 조인(join)과 부속질의(subquery)다.

조인_2개의 테이블을 합체
조인은 한 테이블의 행을 다른 테이블의 행에 연결하여 두 개 이상의 테이블을 결합하는 연산이다.

Q1. 고객과 고객의 주문에 관한 데이터를 모두 보이시오.
A1. SELECT *
	FROM Customer, Orders
    WHERE Customer.custid=Orders.custid;

Q2. 고객과 고객의 주문에 관한 데이터를 고객별로 정렬하여 보이시오
A2. SELECT *
	FROM Customer, Orders
    WHERE Customer.custid=Orders.custid
    ORDER BY Cusomer.custid;

여러 개의 테이블을 연결하여 하나의 테이블을 만드는 과정을 테이블 조인이라고 한다. 특히 위의 예시처럼 동등조건에 의하여 테이블을 조인하는 것을 동등조인이라고 한다.

조인을 사용하면 아래의 예시 처럼 두 개 이상의 테이블에서 일부 데이터만 얻을 수 있다.

Q1. 고객의 이름과 고객이 주문한 도서의 판매 가격을 검색하시오
A1. SELECT name, saleprice
	FROM Customer, Orders
    WHERE Customer.custid=Orders.custid;

Q2. 고객별로 주문한 모든 도서의 총 판매액을 구하고 고객별로 정렬하시오.
A2. SELECT name, SUM(saleprice)
	FROM Customer, Orders
    WHERE Customer.custid=Orders.custid
    GROUP BY Customer.name
    ORDER BY Cusomer.name;

세 개 이상의 테이블을 조인하고 싶으면 세 개의 테이블을 한꺼번에 조인해야 한다.

Q1. 고객의 이름과 고객이 주문한 도서의 이름을 구하시오.
A1. SELECT Customer.name, Book.bookname
	FROM Customer, Orders, Book
    WHERE Customer.custid=Orders.custid 
    AND Orders.bookid=Book.bookid;
    
Q2. 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.
A2. SELECT Customer.name, Book.bookname
	FROM Customer, Orders, Book
    WHERE Customer.custid=Orders.custid 
    AND Orders.bookid=Book.bookid 
    AND Book.price=20000;

외부조인 (outer join)
조인 연산의 특별한 경우로 외부조인이 있다. 고객의 이름과 고객이 주문한 도서의 판매 가격을 구하는 동등조인의 예에서 도서를 주문하지 않은 고객 '홍길동'은 결과에 포함되지 않는다. 만약 도서를 구매하지 않은 '홍길동'까지 포함하여 고객의 이름과 고객이 주문한 도서의 가격을 구하려면 외부조인을 사용하면 된다.

SELECT Customer.name, saleprice
FROM Customer LEFT OUTER JOIN Orders
			ON Customer.custid=Orders.custid;  
            //왼쪽에 있는 거 다 쓰기 즉, 왼쪽에 있는 것이 주인

부속질의_SQL문 내에 또 다른 SQL 문을 작성

Q1. 가장 비싼 도서의 이름을 보이시오
A1. SELECT bookname
	FROM Book
    WHERE price = (SELECT MAX(price)
    			   FROM Book);
//결과로 단일행 - 단일열 (1X1) 반환

SQL 문의 결과는 테이블이며 테이블의 결과는 다음 네 가지 중 하나이다.

단일행 - 단일열 (1 X 1)
다중행 - 단일열 (n X 1)
단일행 - 다중열 (1 X n)
다중행 - 다중열 (n X n)

다중행 - 다중열과 같이 여러 개의 값을 반환하면 IN 키워드를 사용하면 된다. ex) 도서를 구매한 적이 있는 고객 -> 도서를 구매한 적이 있는 고객은 여러명이므로 다중행 - 단일열 (n X 1)이 된다.

Q1. 도서를 구매한 적이 있는 고객의 이름을 검색하시오.
A1. SELECT name
	FROM Customer
    WHERE custid IN (SELECT custid
    			     FROM Orders);

IN과 OR은 같은 역할을 하는데 에러 발생을 줄이기 위해 IN을 더 많이 쓴다. 서브쿼리에서는 IN을 쓰는 것이 좋다.

세 개 이상의 중첩된 부속질의도 가능하다.

Q1. 대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오.
A1. SELECT name
	FROM Customer
    WHERE custid IN (SELECT custid
    				 FROM Orders
                     WHERE bookid IN(SELECT bookid
                     				 FROM Book
                                     WHERE publisher = '대한미디어'));
                                     
A2. SELECT name
	FROM book, customer,orders
	WHERE orders.bookid = book.bookid
	AND customer.custid = orders.custid
	AND publisher = '대한미디어';
    
A3. SELECT cs.name
	FROM Customer cs, Orders ord, Book b
    WHERE cs.custid = ord.custid
    AND b.bookid = ord.bookid
    AND b.publisher = '대한미디어';

A4. SELECT c.name
	FROM Customer c
	JOIN Orders o ON c.custid = o.custid
	JOIN Book b ON o.bookid = b.bookid
	WHERE b.publisher = '대한미디어';

부속질의 간에는 상하 관계가 있으며 실행 순서는 하위 부속질의를 먼저 실행하고 그 결과를 이용하여 상위 부속질의를 실행한다.

반면 상관 부속질의는 상위 부속질의의 튜플을 이용하여 하위 부속질의를 계산한다. 즉 상위 부속질의와 하위 부속질의가 독립적이지 않고 서로 관련을 맺고 있다.

Q1. 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오.
A1. SELECT b1.bookname
	FROM Book b1
    WHERE b1.price > (SELECT avg(b2.price)
    				  FROM Book b2
                      WHERE b2.publisher = b1.publisher);

이 문제는 평균을 구한 후 평균보다 비싼 도서를 구해야 하기 때문에 부속질의 없이 단독 SQL문으로 작성이 어렵다.

하위 부속질의는 상위 부속질의에서 도서와 출판사가 주어지면 종속적으로 출판사의 도서 평균을 구한다. 상위 부속질의는 이 도서가 평균보다 비싼지 비교한다.

📍정리
부속질의와 조인은 여러 테이블을 하나의 SQL 문에서 다루는 점은 같지만 차이점이 있다.

부속질의는 SELECT문에 나오는 결과 속성을 FROM 절의 테이블에서만 얻을 수 있고 조인은 조인한 모든 테이블에서 결과 속성을 얻을 수 있다.

조인은 부속질의가 할 수 있는 모든 것을 할 수 있다. 그러나 부속질의를 조인을 이용해서 작성해보면 부속질의 만의 편리함을 알 수 있고 한 개의 테이블에서만 결과를 얻는 여러 테이블 질의는 조인보다 부속질의로 작성하는 것이 편하다.

집합 연산_고객 이름의 합집합을 구하기
SQL에서 집합 연산 중 합집합을 UNION으로 나타낸다. MYSQL은 다른 DBMS와 달리 MINUS, INTERSECT 집합 연산이 없다. MINUS 연산은 NOT IN, INTERSECT 연산은 IN 연산자를 이용하여 구현한다.

하지만 합집합 UNION은 안 쓰는 게 좋다. 차라리 서브쿼리를 만드는게 좋고 UNION은 별로

Q1. 대한민국에서 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 보이시오.
A1. SELECT name
	FROM Customer
    WHERE address LIKE '대한민국%'
    UNION
    SELECT name 
    FROM Customer
    WHERE custid IN (SELECT custid
    				 FROM Orders);
                     
// UNION ALL을 사용하면 중복된 결과를 모두 포함하여 보여준다.

EXISTS

EXISTS는 상관 부속질의문 형식이다. EXISTS는 조건에 맞는 튜플이 존재하면 결과에 포함시킨다. 즉 부속질의문의 어떤 행이 조건에 만족하면 참이다.

NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참이다.

Q1. 주문이 있는 고객의 이름과 주소를 보이시오.
A1. SELECT name, address
	FROM Customer cs
    WHERE EXISTS (SELECT *
    			  FROM Orders od
                  WHERE cs.custid = od.custid);

연습문제 2-(8) 주문하지 않은 고객의 이름(부속질의 사용)

A1. SELECT name 
    FROM Customer
	WHERE name NOT IN (SELECT name
    			       FROM Orders, Customer
    				   WHERE Orders.custid = Customer.custid);
                       
A2. SELECT name
	FROM Customer
	WHERE custid NOT IN (SELECT custid
    					 FROM Orders);         

A3. SELECT c.name
	FROM Customer c
	LEFT JOIN Orders o ON c.custid = o.custid
	WHERE o.custid IS NULL;

A4. SELECT c.name
	FROM Customer c
	WHERE NOT EXISTS (SELECT *
    				  FROM Orders o
    				  WHERE o.custid = c.custid);

ch4 SQL 고급

SQL 내장함수

SQL 내장 함수는 상수나 속성 이름을 입력 값으로 받아 단일 값으로 결과를 반환한다.

수학함수

Q1. -78과 +78의 절댓값을 구하시오.
A1. SELECT ABS(-78), ABS(+78);

Q2. 4.875를 소수 첫째 자리까지 반올림한 값을 구하시오.
A2. SELECT ROUND(4.875,1);

문자 함수

Q1. 도서제목에 야구가 포함된 도서를 농구로 변경한 후 도서 목록을 보이시오.
A1. SELECT bookid, REPLACE(bookname, '야구','농구') bookname, publisher, price
FROM Book;


Q2. 같은 성을 가진 사람이 몇 명인지 성별 인원수를 구하시오.
A2. SELECT SUBSTR(name,1,1) '성', COUNT(*) '인원'
	FROM Customer
    GROUP BY SUBSTR(name,1,1);

NULL 값 처리
NULL값은 0, 빈 문자, 공백과는 다른 특별한 값이다. NULL은 비교 연산자로 비교가 불가능하다. NULL값의 연산을 수행하면 결과는 NULL이 나온다.

NULL값을 찾을 때는 = 연산자가 아닌 IS NULL을 사용하고 NULL이 아닌 값을 찾을 때는 IS NOT NULL을 사용한다.

IFNULL 함수
NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력하는 함수이다.

IFNULL(속성, 값) //속성 값이 NULL이면 '값'으로 대치한다.

SELECT name '이름', IFNULL(phone, '연락처없음')'전화번호'
FROM Customer;

행번호 출력
결과로 나오는 행에 번호를 붙이거나 행번호에 따라 결과의 개수를 조절하는 방법은 변수를 처리하면 된다. MYSQL에서 변수는 이름 앞에 @ 기호를 붙이면 치환문에는 SET과 := 기호를 사용한다.

Q1. 고객 목록에서 고객번호, 이름, 전화번호를 앞의 두 명만 보이시오.
A1. SET @seq:=0;  //변수를 0으로 초기화
    SELECT (@seq:=@seq+1) '순번', custid, name, phone
    FROM Customer
    WHERE @seq < 2;

부속질의

부속질의는 하나의 SQL문 안에 다른 SQL문이 중첩된 질의를 말한다.

조인을 사용할 경우: Customer 테이블과 Orders 테이블의 고객번호로 조인한 후 필요한 데이터를 추출한다.

부속질의를 사용할 경우: Customer 테이블에서 박지성 고객의 고객번호를 찾고 찾은 고객번호를 바탕으로 Orders 테이블에서 확인한다.

1. 스칼라 부속질의 - SELECT 부속질의
스칼라 부속질의는 SELECT절에서 사용되는 부속질의로 부속질의의 결과 값을 단일행, 단일 열의 스칼라 값으로 반환한다.

Q1. 마당서점의 고객별 판매액을 보이시오.(고객이름과 고객별 판매액 출력).
A1. SELECT (SELECT name
		    FROM Customer cs
       	    WHERE cs.custid=od.custid) 'name', SUM(saleprice) 'total'
    FROM Orders od
	GROUP BY od.custid;

스칼라 부속질의는 SELECT문과 함께 UPDATE문에서도 사용 가능하다.

Q1. Orders 테이블에 각 주문에 맞는 도서이름을 입력하시오.
A1. UPDATE Orders
	SET bname = (SELECT bookname
    			 FROM Book
                 WHERE Book.bookid=Orders.bookid);

2. 인라인 뷰 - FROM 부속질의
인라인 뷰(inline view)는 FROM 절에서 사용되는 부속질의를 말한다. 뷰는 기존 테이블로부터 일시적으로 만들어진 가상 테이블을 말한다.

FROM절에는 테이블 이름이 위치하는데 여기에 테이블 이름 대신 인라인 뷰 부속질의를 사용하면 된다.

Q1. 고객번호가 2 이하인 고객의 판매액을 보이시오(고객이름과 고객별 판매액 출력)
A1. SELECT cs.name, SUM(od.saleprice) 'total'
	FROM (SELECT custid, name
    	  FROM Customer
          WHERE custid < = 2) cs, Orders od
    WHERE cs.custid = od.custid
    GROUP BY cs.name;
    
//참고로 SUM 같은 집계함수 사용할 때는 GROUP BY 써줘야 함

3. 중첩질의 - WHERE 부속질의
중첩질의는 WHERE 절에서 사용되는 부속질의를 말한다.

비교 연산자

질의 4-15 평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오.

SELECT orderid, saleprice
FROM Orders
WHERE saleprice <= (SELECT AVG(saleprice)
				   FROM Orders);

// AVG 같은 집계함수는 무조건 SELECT를 써줘야 한다. 


질의 4-16 각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호, 금액을 보이시오. 
SELECT orderid, custid, saleprice
FROM Orders md
WHERE saleprice > (SELECT AVG(saleprice)
				   FROM Orders so
                   WHERE md.custid = so.custid);
                   
/**4-16 질의에서 "각 고객" 이라는 말에 집중해야 한다. 각 고객의 평균 주문 금액인 것에 주목

만약 각 고객이 아닌 고객의 평균 주문 금액이면 
SELECT orderid, custid, saleprice
FROM Orders
WHERE saleprice > (SELECT AVG(saleprice) FROM Orders);
이런 쿼리가 작성된다. 
**/

알아야 할 3가지
1.where절에 서브쿼리 가능
2.집계함수는 SELECT와 함께
3. 마지막 WHERE에 같다고 놓기 (cs.custid = od.custid)

IN, NOT IN
IN 연산자는 주질의 속성 값이 부속질의에서 제공한 결과 집합에 있는지 확인하는 역할이다.
NOT IN은 반대로 값이 존재하지 않으면 참이 된다.

질의 4-17 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하시오.

SELECT SUM(saleprice) 'total'
FROM Orders
WHERE custid IN(SELECT custid
				FROM Customer
                WHERE address LIKE '%대한민국%');

아래는 잘못된 예시이다.


SELECT * 
FROM customer 
WHERE custid IN (
    SELECT custid, count(*)
    FROM orders
    GROUP BY custid
    HAVING count(*) > 3
)

위의 예시는 서브쿼리에 SELECT 문에서 두 개의 열(custid, count(*))을 선택했기 때문에 오류가 발생한다. IN 절은 하나의 열만 받아야 하는데, 두 개의 열이 반환되었기 때문에 문제가 생긴 것이다.

에러를 고치려면 서브쿼리에 count(*)를 제거하면 된다.

ALL, SOME(ANY)
ALL은 모든, SOME은 어떠한(최소한 하나라도)이라는 의미를 가진다.

예를들어 금액 > SOME (SELECT 단가 FROM 상품) 이라고 하면 금액이 상품 테이블에 있는 어떠한 (SOME) 단가보다 큰(>) 경우 참이 되어 해당 행의 데이터를 출력한다.

ALL의 경우 부속질의의 결과 집합 전체를 대상으로 하므로 결과 집합의 최대값(MAX)과 같다고 볼 수 있고 SOME은 부속질의 결과 집합 중 어떠한 값을 의미하므로 최솟값(MIN)과 같다고 볼 수 있다.

질의 4-18 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 판매금액을 보이시오.

SELECT orderid, saleprice
FROM Orders
WHERE saleprice > ALL (SELECT saleprice
					   FROM Orders
                       WHERE custid='3');

EXISTS, NOT EXISTS
EXISTS와 NOT EXISTS는 데이터의 존재 유무를 확인하는 연산자이다.

WHERE [NOT] EXISTS (부속질의)

EXISTS 연산자는 다른 연산자와 달리 왼쪽에 스칼라 값이나 열을 명시하지 않기 때문에 반드시 부속질의에 주질의의 열 이름이 제공되어야 한다.

질의 4-19 EXISTS 연산자를 사용하여 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하시오.

SELECT SUM(saleprice) 'total'
FROM Orders od
WHERE EXISTS (SELECT * 
			  FROM Customer cs
              WHERE address LIKE '%대한민국%'
              AND cs.custid = od.custid);

뷰 (VIEW)

뷰는 하나 이상의 테이블을 합하여 만든 가상테이블이다.

뷰의 정의만 DBMS에 저장된다.
뷰는 실제로 데이터를 복사하지 않는다. 데이터를 저장하지 않는다.
Data query 시 데이터를 가져온다.
원본 데이터 값에 따라 같이 변한다.
독립적인 인덱스 생성이 어렵다.
삽입, 삭제, 갱신 연산에 제약이 따른다.

뷰의 장점

편리성 및 재사용성: 미리 정의된 뷰를 일반 테이블처럼 사용할 수 있기 때문에 편리하고 사용자가 필요한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있다.
->복잡한 질의를 간단히 작성
보안성: 각 사용자별로 보안이 필요한 데이터를 제외하여 선별하여 보여줄 수 있다.
->개인정보(주민번호)나 급여, 건강 같은 민감한 정보를 제외한 테이블을 만들어 사용한다.
독립성: 논리 데이터베이스의 원본 테이블의 구조가 변해도 응용 프로그램에 영향을 주지 않도록 하는 논리적 독립성을 제공한다.

1. 뷰의 생성

//뷰 생성 문법
CREATE VIEW 뷰이름 [(열이름[,...n])]
AS SELECT 문

위 문법에서 '뷰이름'은 생성할 뷰의 이름을 말하며 '열이름'은 뷰에서 사용할 열의 이름을 말한다.

질의 4-20 주소에 '대한민국'을 포함하는 고객들로 구성된 뷰를 만들고 조회하시오. 뷰의 이름은 vw_Customer로 설정하시오.

CREATE VIEW vw_Customer
AS SELECT *
   FROM Customer
   WHERE address LIKE '%대한민국%';
   
결과를 확인하려면 
SELECT *
FROM vw_Customer;

질의 4-21 Orders 테이블에서 고객이름과 도서이름을 바로 확인할 수 있는 뷰를 생성한 후 
'김연아' 고객이 구입한 도서의 주문번호, 도서이름, 주문액을 보이시오.

CREATE VIEW vw_Orders(orderid, custid, name, bookid, bookname, saleprice, orderdate)
AS SELECT od.orderid, od.custid, cs.name, od.bookid, bk.bookname, od.saleprice, od.orderdate
FROM Orders od, Customer cs, Book bk
WHERE od.custid = cs.custid AND od.bookid = bk.bookid;

결과를 확인하려면
SELECT orderid, booknamem saleprice
FROM vw_Orders
WHERE name = '김연아';

2. 뷰의 수정
뷰의 수정은 CREATE VIEW문에 OR REPLACE 명령을 더하여 작성한다.

CREATE OR REPLACE VIEW 뷰이름 [(열이름 [,... n])]
AS SELECT 문

질의 4-22 [질의 4-20]에서 생성한 뷰는 주소가 대한민국인 고객을 보여준다. 
이 뷰를 영국을 주소로 가진 고객으로 변경하시오. phone 속성은 필요 없으므로 포함시키지 마시오. 

CREATE OR REPLACE VIEW vw_Customer (custid, name, address)
AS SELECT custid, name, address
   FROM Customer
   WHERE address LIKE '%영국%' ;
   
결과를 확인하려면
SELECT *
FROM vw_Customer;

UPDATE SET 에러 발생 쿼리

UPDATE V_orders
SET custid = custid*10; 

// PK를 수정하면 안 되기 때문에 에러 발생

3. 뷰의 삭제
DROP VIEW 뷰이름

질의 4-23 앞서 생성한 뷰 vw_Customer를 삭제하시오.
DROP VIEW vw_Customer;

ch 5 데이터베이스 프로그래밍

데이터베이스 프로그래밍의 개념

SQL 전용 언어를 사용하는 방법

SQL 자체의 기능을 확장하여
변수, 제어, 입출력 등의 기능을 추가한 새로운 언어를 사용
MY SQL: Stored Program
SQL Server: T-SQL
Oracle: PL/SQL

저장 프로그램 (트리거, 프로시저)

1. 저장 프로그램
저장 프로그램은 프로그램 로직을 프로시저로 구현하여 객체 형태로 사용한다. 함수와 비슷한 개념으로 작업 순서가 정해진 독립된 프로그램의 수행 단위를 말하며 정의된 다음 MYSQL에 저장되므로 저장 프로그램이라고 한다.

저장 프로그램은 저장 루틴, 트리거, 이벤트로 구성되며 저장 루틴은 프로시저와 함수로 나눈다.

✅트리거란 데이터의 변경(INSERT, DELETE, UPDATE)문이 실행될 때 자동으로 같이 실행되는 프로시저를 말한다.

프로시저를 정의하려면 CREATE PROCEDURE 문을 사용한다.

프로시저는 선언부와 실행부 (BEGIN-END)로 구성된다. 선언부에서는 변수와 매개변수를 선언하고 실행부에서는 프로그램 로직을 구현한다.
매개변수는 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값이다.
변수는 저장 프로시저나 트리거 내에서 사용되는 값이다.
소스코드에 대한 설명문은 /* */안에 적고 한 줄이라면 -- 에 쓰면 된다.

삽입 작업을 하는 프로시저
프로시저를 사용하지 않고 INSERT 문을 사용해도 된다. 하지만 프로시저로 작성해두면 좀 더 복잡한 조건의 삽입 작업을 인자 값만 바꾸어 수행할 수도 있고 저장해두었다가 필요할 때마다 호출하여 사용할 수도 있다.

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;
/* 프로시저 InsertBook을 테스트하는 부분 */
CALL InsertBook(13,'스포츠과학', '마당과학서적', 25000);
SELECT * FROM Book;

생성된 InsertBook 프로시저를 삭제하는 문장은 DROP PROCEDURE InsertBook;

제어문을 사용하는 프로시저

delimiter //

CREATE PROCEDURE BookInsertOrUpdate(
  myBookID INTEGER,
  myBookName VARCHAR(40),
  myPublisher VARCHAR(40),
  myPrice INTEGER)
BEGIN
  DECLARE mycount INTEGER;
  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);
  END IF;
END;
//

delimiter ;
---------------------------------------------------------------------
-- BookInsertOrUpdate 프로시저를 실행하여 테스트하는 부분
CALL BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 25000);

SELECT * FROM Book; -- 15번 도서 삽입 결과 확인

-- BookInsertOrUpdate 프로시저를 실행하여 테스트하는 부분
CALL BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 20000);

SELECT * FROM Book; -- 15번 도서 가격 변경 확인

결과를 반환하는 프로시저
저장 프로시저는 주어진 작업을 수행하고 작업을 완료하기도 하지만 함수와 같이 계산된 결과를 반환할 수도 있다.

예를들어 테이블에 저장된 도서의 평균 가격을 반환하는 프로시저를 작성하면 반환하는 방법은 프로시저를 선언할 때 인자의 타입을 OUT으로 설정한 후 인자 변수에 값을 주면 된다.

delimiter//
CREATE PROCEDURE AveragePrice(
 OUT AverageVal INTEGER)
BEGIN
 SELECT AVG(price) INTO AverageVal
 FROM Book WHERE price IS NOT NULL;
END;
//
delimiter;
------------------------------------------------------
/*프로시저 AveragePrice를 테스트하는 부분*/
CALL AveragePrice(@myValue);
SELECT @myValue;

커서를 사용하는 프로시저

커서 사용 이유:
SQL문의 실행 결과가 다중행 또는 다중열일 경우 프로그램에서는 한 행씩 처리한다. 커서는 실행 결과 테이블을 한 번에 한 행씩 처리하기 위해 테이블의 행을 순서대로 가리키는 데 사용한다.

ex) Orders 테이블의 판매 도서에 대한 이익을 계산하는 프로시저

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;
    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;
--------------------------------------------------------------
/*Interest 프로시저를 실행하여 판매된 도서에 대한 이익금을 계산 */
CALL Interest();

아웃풋 커서 작성

2. 트리거
트리거는 데이터의 변경(INSERT, DELETE, UPDATE) 문이 실행될 때 자동으로 같이 실행되는 프로시저를 말한다.

트리거는 데이터의 변경문이 처리되는 세 가지 시점, 즉 실행 전 BEFORE, 대신하여 INSTEAD OF, 실행 후 AFTER 에 동작한다.

트리거는 데이터의 변경(삽입, 삭제, 수정)이 일어날 때 부수적으로 필요한 작업인 데이터의 기본값 제공, 데이터 제약 준수, SQL 뷰의 수정, 참조무결성 작업 등을 수행한다.

예를들어 새로운 도서를 삽입할 때 삽입한 내용을 백업하기 위해 다른 테이블에 삽입을 기록한다고 하자. INSERT문을 수행하면서 같이 실행할 수도 있지만 사용자 입장에서는 번거롭고 보안상 백업을 감추어야할 경우도 있다. 이 때 트리거를 사용하면 편하다.

트리거 작동을 위해서는 다음 문장을 실행해야 한다.
SET global log_bin_trust_function_creators=ON;

ex) 신규 도서를 삽인한 후 자동으로 Book_log 테이블에 삽입한 내용을 기록하는 트리거

ex) 신규 도서를 삽인한 후 자동으로 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
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' ; -- 결과 확인

에러가 발생하는 TRIGGER를 생성했을 때는 에러가 안 나지만 이런 TRIGGER를 INSERT하면 에러가 발생한다.

3. 사용자 정의 함수
사용자 정의 함수는 수학의 함수와 마찬가지로 입력된 값을 가공하여 결과 값을 되돌려 준다. 프로시저와 비슷해 보이지만 프로시저는 CALL 명령에 의해 실행되는 독립된 프로그램이고 사용자 정의 함수는 SELECT문이나 프로시저 내에서 호출되어 SQL문이나 프로시저 내에서 호출되어 SQL 문이나 프로시저에 그 값을 제공한다.

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 ;
-------------------------------------------------------------------------
-- Orders 테이블에서 각 주문에 대한 이익을 출력
SELECT custid, orderid, saleprice, fnc_Interest(saleprice) AS interest
FROM Orders;