[MySQL] 함수, 프로시저, 세션변수 쿼리문 연습문제

1. Data Table

customer 테이블

book 테이블

orders 테이블

 

2. 프로시저 연습문제

 

/*프로시져1. 고객을 새로 등록*/
use jin;
drop procedure if exists newCustomer;
delimiter //
create procedure newCustomer(
newCustID int,
newName varchar(40),
newAddress varchar(40),
newPhone varchar(20))
BEGIN
	declare myCount int;
    select count(*) into myCount from customer where custID like newCustID;
    if myCount != 0 then
		update customer set name = newName, address = newAddress, phone = newPhone where custID like newCustID;
	else
		insert into customer(custID, name, address, phone) values (newCustID, newName, newAddress, newPhone);
	end if;
END;
//
delimiter ;

call newcustomer(17, '김준표', '미국 뉴멕시코', '010-3333-8282');
call newcustomer(16, '차준표', '미국 뉴올리언스', '010-3334-8484');
select * from customer;


/*프로시져2. 같은 책이 있을 때, 새 가격이 높을 때만 업데이트*/
drop procedure if exists changePrice;
delimiter //
create procedure changePrice(
tmpBookName varchar(40),
tmpPrice int)
BEGIN
	declare myCount int;
    declare myPrice int;
	select count(*) into myCount from book where bookName like tmpBookName;
    select price into myPrice from book where bookName like tmpBookName;
    if myCount != 0 then
		if tmpPrice > myPrice then
			update book set price = tmpPrice where bookName like tmpBookName;
		end if;
	end if;
END;
//
delimiter ;

select * from book;
call changePrice('축구아는 여자', 15000);

/*프로시져3. 출판사별 도서 판매 총액*/
DROP PROCEDURE 	if exists totalPrice;
delimiter //
create procedure totalPrice()
BEGIN
	select b.publisher, sum(o.saleprice) '도서 판매 총액' 
    from orders o
    left join book b on o.bookID = b.bookID
    group by b.publisher;
END;
//
delimiter ;

call totalPrice();


/*프로시져4. 각 출판사별 도서 평균 금액과 평균 금액을 초과한 도서 목록 
	#1 함수와 프로시저를 같이 사용*/
drop function if exists fnc_bookList;
delimiter //
create function fnc_avgPrice(myPublisher varchar(40)) returns int
BEGIN
	DECLARE avgPrice int;
    select avg(price) into avgPrice
    from book
    where publisher = myPublisher;
	RETURN avgPrice;
END;
//
delimiter ;


drop procedure if exists myBookList;
delimiter //
create procedure myBookList()
BEGIN
	select distinct publisher, fnc_avgPrice(publisher) as '도서 평균 금액', price, bookName as '평균 금액을 초과한 책 이름'
    from book
    where price > fnc_avgPrice(publisher)
    order by publisher;
END;
//
delimiter ;

call mybooklist();

/*프로시져4. 각 출판사별 도서 평균 금액과 평균 금액을 초과한 도서 목록 
	#2 프로시저로 tempTable을 만들어 값을 비교해 출력*/
DROP PROCEDURE IF EXISTS myBookList;
DELIMITER //
CREATE PROCEDURE myBookList()
BEGIN
    -- 출판사별 평균 가격을 임시 테이블에 저장
    CREATE TEMPORARY TABLE IF NOT EXISTS tmpAvgPrices AS
        SELECT publisher, round(AVG(price),1) AS avgPrice
        FROM book
        GROUP BY publisher;
    
    -- 각 출판사의 평균 가격 출력
    SELECT * FROM tmpAvgPrices;
    
    -- 각 출판사별 평균 가격을 초과하는 도서 목록 출력
    SELECT b.publisher, b.bookName, b.price, a.avgPrice
    FROM book b
    JOIN tmpAvgPrices a ON b.publisher = a.publisher
    WHERE b.price > a.avgPrice
    order by b.publisher;

    -- 임시 테이블 삭제
    DROP TEMPORARY TABLE IF EXISTS tmpAvgPrices;
END //
DELIMITER ;

call mybooklist();

 

 

3. 함수 연습문제

 

/*함수1. 고객의 주문 총액이 20,000원 이상이면 '우수' 미만이면 '보통' 반환*/
drop function if exists fnc_prize;
delimiter //
create function fnc_prize(totalPrice int) returns varchar(10)
BEGIN
	declare prize varchar(10);
	if totalPrice >= 20000 then
		set prize = '우수';
	else
		set prize = '보통';
	end if;
    return prize;
END;
// 
delimiter ;

select c.name, sum(o.saleprice) as '주문 총액', fnc_prize(sum(o.saleprice)) as '평점'
from orders o
right join customer c on o.custID = c.custID
group by c.name;

/*함수2. 주소를 확인해서 '해외 거주', '국내 거주' 반환 받기*/
drop function if exists fnc_residence;
delimiter //
create function fnc_residence(address varchar(40)) returns varchar(20)
BEGIN
	declare residence varchar(20);
    if address like '대한민국%' then
		set residence = '국내 거주';
	else
		set residence = '해외 거주';
	end if;
    return residence;
END;
//
delimiter ;

select custID, name, address, fnc_residence(address), phone
from customer;

 

4. 세션 변수 연습문제

 

/*피라미드 별찍기 연습*/


-- @num 변수를 21로 설정합니다. 이 변수는 피라미드의 총 높이를 결정합니다.
SET @num := 21;

-- CONCAT 함수를 사용해 각 행마다 공백 문자열과 별표 문자열을 연결하여 피라미드 모양을 생성합니다.
SELECT CONCAT(REPEAT('  ', @num - n), REPEAT('* ', n)) AS pyramid
FROM (
    -- 서브쿼리 내에서, information_schema.tables의 각 행마다 @row 변수를 1씩 증가시키며 n에 할당합니다.
    -- 이 구조는 특정 행의 개수를 생성하기 위해 사용됩니다 (@num 값까지).
    SELECT @row := @row + 1 AS n
    FROM information_schema.tables,
    -- @row 변수를 0으로 초기화합니다. 이 부분은 서브쿼리 실행이 시작될 때 한 번만 수행됩니다.
    (SELECT @row := 0) AS r
    -- @row 값이 @num 값보다 작은 동안 WHERE 조건을 만족하는 행만을 선택합니다.
    -- 즉, @num 값까지의 숫자를 생성합니다.
    WHERE @row < @num
) AS derived;