[혼공단9기] 혼공학습단 9기 6주차 - SQL 스터어드 프로시저, 파이썬 연결(feat. 혼자 공부하는 SQL, 혼공S, 혼공스)

[혼공단9기] 혼공학습단 9기 6주차 - SQL 스터어드 프로시저, 파이썬 연결(feat. 혼자 공부하는 SQL, 혼공S, 혼공스)

 

 

이번 주 스터디 내용 요약

 

07-1. 스토어드 프로시저 사용 방법

• MySQL의 스토어드 프로시저(stored procedure)는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있다.

 

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름( IN 또는 OUT 매개변수)
BEGIN

    이 부분에 SQL 프로그래밍 코드를 작성
    
END $$
DELIMITER ;

 

 

스토어드 프로시저 호출

CALL 스토어드_프로시저_이름();

 

 

스토어드 프로시저 삭제

DROP PROCEDURE user_proc;

 

 

입력 매개변수 지정

IN 입력_매개변수_이름 데이터_형식

 

입력 매개변수가 있는 스토어드 프로시저 실행

CALL 프로시저_이름(전달_값);

 

 

출력 매개변수 지정

OUT 출력_매개변수_이름 데이터_형식

CALL 프로시저_이름(@변수명);
SELECT @변수명;

---

 

07-2. 스토어드 함수와 커서

• 스토어드 함수는 MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공한다.
• RETURNS 예약어를 통해서 하나의 값을 반환해야 하는 특징이 있다.
• 커서는 스토어드 프로시저 안에서 한 행씩 처리할 때 사용하는 프로그래밍 방식이다.

 

스토어드 함수

스토어드 함수의 형식

DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
    RETURNS 반환형식
BEGIN

    이 부분에 프로그래밍 코딩
    RETURN 반환값;
    
END $$
DELIMITER ;
SELECT 스토어드_함수_이름();

 

스토어드 함수의 사용

- 함수 생성 권한 부여

SET GLOBAL log_bin_trust_function_creators = 1;

---

커서로 한 행씩 처리하기

1. 사용할 변수 준비하기(예시)

DECLARE memNumber INT;
DECLARE cnt INT DEFAULT 0;
DECLARE totNumber INT DEFAULT 0;

 

DECLARE endOfRow BOOLEAN DEFAULT FALSE;

 

2. 커서 선언하기(예시)

DECLARE memberCuror CURSOR FOR
    SELECT mem_number FROM member;

 

3. 반복 조건 선언하기(예시)

DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET endOfRow = TRUE;

 

4. 커서 열기(예시)

OPEN memberCuror;

 

5. 행 반복하기

 cursor_loop: LOOP
     이 부분을 반복
 END LOOP cursor_loop

 

6. 커서 닫기

CLOSE memberCuror;

 

 

 

07-3. 자동 실행되는 트리거

• 트리거(trigger)는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다.
• 트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있는데, 이런 것을 데이터의 무결성이라고 부르기도 한다.
• 트리거란 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생하면 실행되는 코드이다.

예시)

DELIMITER $$
CREATE TRIGGER myTrigger
    AFTER DELETE
    ON trigger_table
    FOR EACH ROW
BEGIN
    SET @msg = '삭제됨' ; -- 트리거 실행 시 작동되는 코드들
END $$
DELIMITER ;

---

이번 주 미션은 다음과 같습니다.

#	진도	기본 미션	선택 미션
6주차 (2/13 ~ 2/19)	Chapter 07 ~ 08	p. 363 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거 작성하고 인증하기	p. 402 GUI 응용 프로그램 만들고 인증하기

---

기본 미션

p. 363 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거 작성하고 인증하기

 

1. 아이디, 이름, 인원, 주소 4개의 열로 구성된 가수 테이블(singer) 준비하기

USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

2. 백업 테이블 준비하기

DROP TABLE IF EXISTS backup_singer;
CREATE TABLE backup_singer
( mem_id        CHAR(8) NOT NULL , 
  mem_name      VARCHAR(10) NOT NULL, 
  mem_number    INT NOT NULL, 
  addr          CHAR(2) NOT NULL,
  modType       CHAR(2), -- 변경된 타입. '수정' 또는 '삭제'
  modDate       DATE, -- 변경된 날짜
  modUser       VARCHAR(30) -- 변경한 사용자
);

 

3. 변경(UPDATE)이 발생했을 때 작동하는 singer_updateTrg 트리거 만들기

DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg  -- 트리거 이름
    AFTER UPDATE -- 변경 후에 작동하도록 지정
    ON singer -- 트리거를 부착할 테이블
    FOR EACH ROW 
BEGIN
    INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name, 
        OLD.mem_number, OLD.addr, '수정', CURDATE(), CURRENT_USER() );
END $$ 
DELIMITER ;

 

 

4. 데이터 변경해보기

UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';

 

앗.. 그런데... 에러가 발생!

 

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

 

인터넷에서 찾아보니 원인은 "테이블에서 키값을 이용한 update나 delete만을 허용하도록 되어 있는데, 그렇지 않게 좀더 넓은 범위의 sql을 적용하려고 할때 workbench에서 경고를 주는 것" 이라고 한다.

 

Workbench Preferences에서 안전모드(Safe mode)를 해제하고 다시 실행해보자.

 

 

성공!

 

확인해보자!

 

SELECT * FROM backup_singer;

 

 

미션 완료!

---

선택 미션

p. 402 GUI 응용 프로그램 만들고 인증하기

 

이번 미션은 파이썬으로 연동해서 GUI 응용프로그램을 만드는 건데... 개인적으로 파이썬 연동보다 PHP 연동에 더 관심이 많아서 살짝 다른 스터디를 진행했다.

 

올해에 개교하는 대안학교(초등과정)에서 곧 근무를 시작할 예정인데, 자그마한 학교라 LMS(Learing Management System 같은 것도 없고...  학생 관리 프로그램 같은 것도 없다... (뭐, 구글 스프레드 시트 정도 사용할 수는 있겠지만 현실적으로 Education 버전 지원 같은 것도 어렵다...)

 

그래서 간단히 PHP와 SQL을 연동하여 뭔가를 만들어보고 싶다는 생각을 했다(1년 정도 공부해보면 뭐가 나오려나?). 그게 이번에 SQL을 공부하게 된 계기이도 하고...

 

그래서 살짝 다른 과제를 수행해보았다.

 

먼저 간단히 학생 테이블(studentTBL)을 만들고 정보를 입력했다.

 

 

PHP로 MySQL에 접속을 시도해았다.

 <?php
    $con=mysqli_connect("localhost", "root", "0000", "vlms") or die("MySQL 접속 실패");
    $sql = "
        SELECT * FROM studenttbl
    ";

    $ret = mysqli_query($con, $sql);

    if($ret) {
        echo mysqli_num_rows($ret), "건이 조회됨. <br/><br/>";
    }
 ?>

 

오호라.. 잘 출력이 되었다.

 

이제 데이터를 출력해보아야 겠다.

 

<?php
    while($row = mysqli_fetch_array($ret)) {
        echo "<tr>";
        echo "<td>", $row['stdName'], "</td>";
        echo "<td>", $row['stdSex'], "</td>";
        echo "<td>", $row['stdBirth'], "</td>";
        echo "<td>", $row['stdGrade'], "</td>";
        echo "<td>", $row['stdClass'], "</td>";
        echo "</tr>";  
    }
?>

 

이런 식으로 HTML 테이블 TD 태그 안에 데이터를 넣었다.

 

그리고 디자인은 부트스트랩 무료 템플릿을 활용했다.

 

그 결과...

 

PHP 연동하여 출력하는 것 까지 성공!

 

아직 입력, 수정, 삭제 기능은 없다. 하나 하나 만들어가면서 업그레이드 해봐야겠다!!

---

 

https://www.youtube.com/watch?v=UctTSze77Wc 

 

 

 

이번에도 구독과 좋아요, 알림설정은 부탁 안해요...ㅎㅎ 구걸해요...

 

 

 

http://bit.ly/3GlGmdm

혼자 공부하는 SQL - YES24