[MySQL] 프로시저 생성 예제 (declare, join 사용)

student 테이블의 st_point(이수학점)를 업데이트 하세요

• st_point = 이수학점
• attend / student 테이블 사용
• attend테이블의 at_repetition = 'y'  인 경우 학점을 받음
• 학점을 주는 조건 at_reprtition = 'n' 이면 학점을 얻을 수 있음
• attend 테이블의 at_co_num가 어느과목인지 먼저 확인
• subject 테이블에서 과목의 su_point 체크 후
• sum(su_point) 하여 student 테이블의 st_point에 update
• call update_stpoint(학번) ;

mysql> drop procedure if exists update_stpoint ;
 
mysql> delimiter //
 
mysql> create procedure update_stpoint(
    -> in in_st_num varchar(45)
    -> )
    -> begin
    -> declare _hap int;
    ->
    -> select sum(su.su_point) into _hap from attend a  //set _hap = (select sum(su.su_point) from attend a
    -> join course c on a.at_co_num = c.co_num
    -> join subject su on c.co_su_num = su.su_num
    -> join student s on s.st_num = a.at_st_num
    -> where in_st_num = st_num and a.at_repetition = 'n';
    ->
    -> update student set st_point = _hap where in_st_num = st_num;
    ->
    -> select * from student;
    -> end//
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
 
-- 넣어보기
mysql> call update_stpoint('2020123001');
+------------+---------+---------+----------+
| st_num     | st_name | st_term | st_point |
+------------+---------+---------+----------+
| 2020123001 | 김영철  |       2 |       13 |
| 2020123002 | 나영희  |       2 |        8 |
| 2020160001 | 강철수  |       2 |        5 |
| 2020160002 | 박철수  |       2 |        7 |
| 2020456001 | 강군    |       2 |        3 |
+------------+---------+---------+----------+
5 rows in set (0.00 sec)
 
Query OK, 0 rows affected (0.01 sec)
 
mysql> call update_stpoint('2020123002');
+------------+---------+---------+----------+
| st_num     | st_name | st_term | st_point |
+------------+---------+---------+----------+
| 2020123001 | 김영철  |       2 |       13 |
| 2020123002 | 나영희  |       2 |        8 |
| 2020160001 | 강철수  |       2 |        5 |
| 2020160002 | 박철수  |       2 |        7 |
| 2020456001 | 강군    |       2 |        3 |
+------------+---------+---------+----------+
5 rows in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> call update_stpoint('2020160001');
+------------+---------+---------+----------+
| st_num     | st_name | st_term | st_point |
+------------+---------+---------+----------+
| 2020123001 | 김영철  |       2 |       13 |
| 2020123002 | 나영희  |       2 |        8 |
| 2020160001 | 강철수  |       2 |        5 |
| 2020160002 | 박철수  |       2 |        7 |
| 2020456001 | 강군    |       2 |        3 |
+------------+---------+---------+----------+
5 rows in set (0.00 sec)
 
Query OK, 0 rows affected (0.01 sec)
 
mysql> call update_stpoint('2020160002');
+------------+---------+---------+----------+
| st_num     | st_name | st_term | st_point |
+------------+---------+---------+----------+
| 2020123001 | 김영철  |       2 |       13 |
| 2020123002 | 나영희  |       2 |        8 |
| 2020160001 | 강철수  |       2 |        5 |
| 2020160002 | 박철수  |       2 |        7 |
| 2020456001 | 강군    |       2 |        3 |
+------------+---------+---------+----------+
5 rows in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> call update_stpoint('2020456001');
+------------+---------+---------+----------+
| st_num     | st_name | st_term | st_point |
+------------+---------+---------+----------+
| 2020123001 | 김영철  |       2 |       13 |
| 2020123002 | 나영희  |       2 |        8 |
| 2020160001 | 강철수  |       2 |        5 |
| 2020160002 | 박철수  |       2 |        7 |
| 2020456001 | 강군    |       2 |        3 |
+------------+---------+---------+----------+
5 rows in set (0.00 sec)
 
Query OK, 0 rows affected (0.01 sec)
 
-- 전체 출력
mysql> select*from student;
+------------+---------+---------+----------+
| st_num     | st_name | st_term | st_point |
+------------+---------+---------+----------+
| 2020123001 | 김영철  |       2 |        2 |
| 2020123002 | 나영희  |       2 |        3 |
| 2020160001 | 강철수  |       2 |       10 |
| 2020160002 | 박철수  |       2 |        8 |
| 2020456001 | 강군    |       2 |       10 |
+------------+---------+---------+----------+
5 rows in set (0.00 sec)

 

mysql> delimiter //

mysql> create procedure update_stpoint(
    -> in in_st_num varchar(45)
    -> )
    -> begin
    -> declare _hap int;
    ->
    -> set _hap = (select sum(su.su_point) from attend a
    -> join course c on a.at_co_num = c.co_num
    -> join subject su on c.co_su_num = su.su_num
    -> join student s on s.st_num = a.at_st_num
    -> where in_st_num = st_num and a.at_reprtition = 'y');
    ->
    -> update student set st_point = _hap where in_st_num = st_num;
    ->
    -> select * from student;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;