반응형
여기 나온 문제는 테이블이 있어야 하기에 같이 테이블 생성 코드도 올렸습니다.
아래는 테이블 생성 예제입니다.
drop database if exists madang;
create database madang;
use madang;
create table book
(
bookid int auto_increment not null primary key,
bookname varchar(20) not null,
publisher varchar(10) not null,
price int not null
);
insert into book values(null, '축구의 역사', '굿스포츠', 7000);
insert into book values(null, '축구아는 여자', '나무수', 13000);
insert into book values(null, '축구의 이해', '대한 미디어', 7000);
insert into book values(null, '골프 바이블', '대한 미디어', 35000);
insert into book values(null, '피겨 교본', '굿스포츠', 8000);
insert into book values(null, '역도 단계별 기술', '굿스포츠', 6000);
insert into book values(null, '야구의 추억', '이상 미디어', 20000);
insert into book values(null, '야구를 부탁해', '이상 미디어', 13000);
insert into book values(null, '올림픽 이야기', '삼성당', 7500);
insert into book values(null, 'Olympic Champions', 'Pearson', 13000);
create table customer
(
custid int auto_increment not null primary key,
name varchar(10) not null,
address varchar(20) not null,
phone char(13)
);
insert into customer values (null, '박지성','영국 맨체스타', '000-5000-0001');
insert into customer values (null, '김연아','대한민국 서울', '000-6000-0001');
insert into customer values (null, '장미란','대한민국 강원도', '000-7000-0001');
insert into customer values (null, '추신수','미국 클리브랜드', '000-8000-0001');
insert into customer values (null, '박세리','대한민국 대전', null);
create table orders
(
orderid int auto_increment not null primary key,
custid int not null,
bookid int not null,
saleprice int not null,
orderdate date not null,
foreign key(custid) references customer(custid),
foreign key(bookid) references book(bookid)
);
insert into orders values(null, 1,1, 6000, '2014-07-01');
insert into orders values(null, 1,3, 21000, '2014-07-03');
insert into orders values(null, 2,5, 8000, '2014-07-03');
insert into orders values(null, 3,6, 6000, '2014-07-04');
insert into orders values(null, 4,7, 20000, '2014-07-05');
insert into orders values(null, 1,2, 12000, '2014-07-07');
insert into orders values(null, 4,8, 13000, '2014-07-07');
insert into orders values(null, 3,10, 12000, '2014-07-08');
insert into orders values(null, 2,10, 7000, '2014-07-09');
insert into orders values(null, 3,8, 13000, '2014-07-10');
아래는 테이블 활용 예제입니다.
순차적으로 하나씩 읽어가면서 진행해보시면 됩니다.
-- 주문 테이블에 주문 수량을 의미하는 필드를 새로 생성합니다. 또한 이는 기본값이 있습니다.
alter table orderstbl add column ordercount int not null default 0;
-- 북 테이블에 재고를 의미하는 필드를 만들어줍니다.
Alter table booktbl add column count int not null default 100;
-- 고객 테이블에 구매실적에 따른 등급을 저장할 필드를 생성합니다.
Alter table customertbl add column vip_grade varchar(10) not null default 'bronze';
-- 고객 테이블에서 등급의 변경이 있을 시 관련 고객의 정보와 등급에 관련된 정보를 저장할 테이블을 생성합니다.
drop table if exists vip_history;
CREATE TABLE vip_history (
no INT AUTO_INCREMENT PRIMARY KEY,
cusid INT NOT NULL,
update_date DATE,
past_vip_grade VARCHAR(10),
now_vip_grade VARCHAR(10),
FOREIGN KEY (cusid)
REFERENCES customertbl (cusid)
);
-- 고객의 등급을 변경시키는 프로시저입니다. 이것은 길기에 각 줄로 주석을 달겠습니다.
-- 만약 프로시저가 존재하면 삭제시키는 것을 진행합니다.
DROP PROCEDURE IF EXISTS vip_change_proc;
delimiter $$
-- 프로시저를 생성합니다.
CREATE PROCEDURE vip_change_proc()
begin
-- 커서가 반복문을 돌면서 레코드에서 가져오는 값을 저장하기 위한 변수 두개입니다.
declare cus_id int;
declare price INT;
-- 반복문을 돌면서 그 안에 조건문을 통해 받은 값을 저장할 변수입니다.
declare ch_vip_grade varchar(10);
-- 커서가 반복문을 빠져나오기 위한 조건문에서 사용할 값입니다.
declare endOfRow Boolean default false;
-- 커서를 생성하고 cusid를 기준으로 그룹핑한 주문테이블에서 cusid, sum(집계함수) 필드를 표시하는 테이블이랑 연결해줍니다.
declare userCursor CURSOR FOR select cusid,sum(saleprice * ordercount) from orderstbl group by cusid;
-- 만약 커서가 반복문을 돌다가 not found 에러를 발견시 endofrow를 true로 바꿔줍니다.
declare continue handler for not found set endOfRow = true;
-- 커서를 사용할 준비를 해줍니다.
OPEN userCursor;
-- 반복문이고 vip_loop라벨이 붙어있습니다.
vip_loop : LOOP
-- 커서가 본인이 부착된 테이블의 레코드를 반복문에 따라서 순차적으로 돌게 되는데, 그때 into 다음에 나오는 변수에 값을 지정해줍니다.
FETCH userCursor INTO cus_id,price;
-- 커서가 돌다가 not found 에러가 나오면 endofrow가 true가 되면서 leave문이 실행이됩니다.
if endOfRow then
LEAVE vip_loop;
END IF;
-- java 스위치 케이스문과 똑같습니다. 해당 조건에 맞는 쿼리를 실행합니다.
-- 커서가 돌면서 받은 price 값으로 조건에 맞는 값을 등급 변수에 넣어줍니다.
CASE
WHEN (price >= 30000) then set ch_vip_grade = 'vip';
WHEN (price < 30000 and price >= 20000) then set ch_vip_grade = 'gold';
WHEN (price < 20000 and price >= 10000) then set ch_vip_grade = 'silver';
else set ch_vip_grade = 'bronze';
END CASE;
-- customer 테이블에서의 등급 컬럼을 수정해주는 과정입니다.
UPDATE customertbl
SET
vip_grade = ch_vip_grade
WHERE
-- 커서가 주문테이블을 반복문으로 돌면서 받아오는 cusid를 cus_id로 저장해줬으며, 그 값을 다시 고객테이블에 cusid와 비교해주는 값입니다.
-- 그리고 그 조건에 해당하는 레코드(1개만 나올 것이다)의 등급 컬럼을 수정해줍니다.
cusid = cus_id;
-- 여기까지 반복문이 실행됩니다.
END LOOP vip_loop;
-- cursor에서 더이상 값이 출력될 것이 없으면 반복문을 빠져나와서 커서를 닫아줄 것입니다.
CLOSE userCursor;
end $$ DELIMITER ;
CALL vip_change_proc();
-- 고객등급에서 수정이 일어나게 되면 실행되는 트리거입니다.
-- 예를 들어 위의 프로시저가 실행하면 결국 커서를 반복시켜서 가져온 값들로 비교하고, 등급컬럼을 수정. 하게 되는데 이때 이 트리거도 자연스럽게 실행이 됩니다.
DROP TRIGGER IF EXISTS vip_change_Trg;
delimiter $$
create trigger vip_change_Trg
-- 고객 테이블에서 수정이 발생될 시 트리거가 작동됩니다.
after update
on customertbl
for each row
begin
-- 트리거의 실행문입니다. 간단히 말해서 고객테이블의 등급이 변경시, 고객등급이 변경되었을 때의 정보들을 표시하는 테이블에 insert를 해줍니다.
INSERT INTO vip_history values (null,cusid,curdate(),old.vip_grade,new.vip_grade);
end $$
delimiter ;
-- 북테이블의 서적 개수를 변경시켜주는 트리거입니다.
-- 주문 테이블에 값을 insert 해주면 이 트리거가 작동하게 됩니다.
DROP TRIGGER IF EXISTS bookCntTrg;
DELIMITER //
CREATE TRIGGER bookCntTrg
AFTER insert
ON orderstbl
FOR EACH ROW
BEGIN
-- 북 테이블의 카운트 값은 기존 카운트(재고수량)에 주문테이블에 insert 하면서 받아온 값을 빼준 것이다.
-- 그리고 그 값을,
-- 북테이블의 아이디와 주문테이블에 insert하면서 받는 주문한 책의아이디(북아이디)랑 같은 레코드에서 수정합니다.
update booktbl set count = count - new.ordercount where bookid = new.bookid;
END //
DELIMITER ;
-- 주문테이블에서 insert시 북테이블의 재고 수량이 변경되는 트리거가 발생합니다.
INSERT INTO orderstbl values (null,1,1,6000,curdate(),5);
-- 주문테이블의 실적이 생기면 다시 고객등급을 업데이트 하는 트리거입니다.
-- 고객등급을 변경시켜주는 프로시저랑 비슷합니다.
DROP TRIGGER IF EXISTS updateVipTrg;
DELIMITER //
CREATE TRIGGER updateVipTrg
AFTER insert
ON orderstbl
FOR EACH ROW
BEGIN
-- 위의 고객등급 변경 프로시저를 참고해주시기 바랍니다.
declare cus_id int;
declare price INT;
declare ch_vip_grade varchar(10);
declare endofrow boolean default false;
declare userCursor CURSOR FOR select cusid,sum(saleprice * ordercount) from orders group by custid;
declare continue handler for not found set endOfRow = true;
OPEN userCursor;
vip_loop : LOOP
FETCH userCursor INTO cus_id,price;
if endOfRow then
LEAVE vip_loop;
END IF;
CASE
WHEN (price >= 30000) then set ch_vip_grade = 'vip';
WHEN (price < 30000 and price >= 20000) then set ch_vip_grade = 'gold';
WHEN (price < 20000 and price >= 10000) then set ch_vip_grade = 'silver';
else set ch_vip_grade = 'bronze';
END CASE;
update customertbl set vip_grade = ch_vip_grade where custid = cust_id;
END LOOP vip_loop;
CLOSE userCursor;
END //
DELIMITER ;
-- 주문테이블에서 insert 시 고객등급을 변경시켜주는 트리거가 실행됩니다.
-- 그리고 위에서 북테이블 재고수량 변경 트리거도 같이 발생합니다.
INSERT INTO orderstbl values (null,5,1,6000,curdate(),3);
select * from orderstbl;
select * from booktbl;
SELECT * FROM customertbl;
select * from vip_history;반응형