혼자 공부하는 SQL 7,8장 - 스토어드 프로시저 & SQL과 파이썬 연결

7장 스토어드 프로시저

 

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

 

<스토어드 프로시저>

- SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 내도록 만든 기능

[필수적인 형식]

DELIMETER $$
CREATE PROCEDURE 스토어드_프로시저_이름( IN 또는 OUT 매개변수 )
BEGIN
	
    이 부분에 SQL 프로그래밍 코드를 작성

END $$
DELIMETER ;

- $$는 1개만 사용해도 되지만 명확하게 표시하기 위해 2개를 사용한다. ##,%%,&&,// 등으로 바꿔도 된다.

- IN은 입력 매개변수, OUT은 출력 매개변수를 뜻한다.

[호출 형식]

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

EX)

DELIMETER $$
CREATE PROCEDURE user_proc()
BEGIN
	
    SELECT * FROM member;

END $$
DELIMETER ;

CALL user_proc();

다이나믹하게 SQL이 변경되는 동적 SQL로도 활용할 수 있다. 아래는 그 예시이다.

DELIMETER $$
CREATE PROCEDURE dynamic_proc(
	IN tablename VARCHAR(20)
)
BEGIN
	SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
    PREPARE myQuery FROM @sqlQuery;
    EXECUTE myQuery;
    DEALLOCATE PREPARE myQuery;
END $$
DELIMETER;

CALL dynamic_proc('member');

 

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

 

<스토어드 함수>

- MySQL에서 제공하는 내장함수외에 직접 함수를 만드는 기능 제공

- RETURNS 예약어를 통해서 하나의 값을 반환해야 하는 특징을 갖는다.

- 문법은 조금 복잡해 보일수 있지만 형태가 대부분 비슷하게 고정되어 있어 한번 익혀놓으면 다음에는 어렵지 않게 사용할 수 있다.

[필수적인 형식]

DELIMETER $$
CREATE FUCTION 스토어드_함수_이름( 매개변수 )
	RETURNS 반환형식
BEGIN
	
    이 부분에 프로그래밍 코딩
    RETURN 반환값;

END $$
DELIMETER ;
SELECT 스토어드_함수_이름();

EX)

DELIMETER $$
CREATE FUCTION sumFunc( number1 INT,number2 INT )
	RETURNS INT
BEGIN
	RETURN number1 + number2;
END $$
DELIMETER ;

SELECT sumFunc(100,200) AS '합계';

 

<커서로 한행씩 처리하기>

- 커서 : 테이블에서 한 행씩 처리하기 위한 방식.

- 다음과 같은 순서로 처리된다.

   커서 선언하기 -> 반복 조건 선언 하기 -> 커서 열기 -> 데이터 가져오기 -> 데이터 처리하기 -> 커서 닫기

 

1) 사용할 변수 준비하기

- 사용할 변수들은 아래와 같이 선언한다. 이때 초기값이 있는 경우 DEFAULT를 사용해준다.

DECLARE memNumber INT; -- 회원의 인원수
DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
DECLARE totNumber INT DEFAULT 0; -- 인원의 합계

- 이외에도 행의 끝을 파악하기 위한 변수를 넣어준다.

 DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부(기본을 FALSE)

2) 커서 선언하기

- 커서는 결국 SELECT 문이다.

DECLARE memberCuror CURSOR FOR-- 커서 선언
        SELECT mem_number FROM member;

3) 반복 조건 설정하기

DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입 
        FOR NOT FOUND SET endOfRow = TRUE;

4) 커서열기

OPEN memberCuror;  -- 커서 열기

5) 행 반복하기

[기본 형식]

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

EX)

cursor_loop: LOOP
        FETCH  memberCuror INTO memNumber; 

        IF endOfRow THEN 
            LEAVE cursor_loop;
        END IF;

        SET cnt = cnt + 1;
        SET totNumber = totNumber + memNumber;        
END LOOP cursor_loop;

6) 커서 닫기

 CLOSE memberCuror;

 

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

 

<트리거>

- 자동으로 수행하여 사용자가 추가작업을 잊어버리는 실수를 방지해주는 기능

- INSERT,UPDATE,DELETE 연산 수행시에 작동이 된다.

- 데이터에 오류가 발생하는 것을 막을 수 있다 => 대이터의 무결성을 지킨다.

EX)

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

- 트리거에서 기존 테이블은 OLD 테이블에, 새로운 데이터는 NEW 테이블에 잠깐 저장된다. OLD와 NEW 테이블은 MySQL이 내부적으로 관리한다.

 

8장 SQL과 파이썬 연결

 

<파이썬에서 데이터 입력하기>

- MySQL 연결하기 -> 커서 생성하기 -> 테이블 만들기 -> 데이터 입력하기 -> 입력한 데이터 저장하기 -> MySQL연결 종료하기

 

1) MySQL 연결하기

[기본 형식]

pymysql.connect(host=서버IP주소, user=사용자,password=암호,db=데이터베이스,charset=문자세트)

[사용예시]

 import pymysql
 conn = pymysql.connect(host='127.0.0.1',user='root',password='0000',db='soloDB',charset='utf8')

- 한글이 문제되지 않도록 utf8을 사용한다.

 

2) 커서 생성하기

cur = conn.cursor()

3) 테이블 만들기

cur.execute("CREATE TABLE userTable (id char(4), userName char(15), email char(20), birthYear int)")

4) 데이터 입력하기

- execute 구문을 활용해 원하는 만큼 데이터를 입력한다.

cur.execute("INSERT INTO userTable VALUES('hong','홍지윤','hong@naver.com',1996)")
cur.execute("INSERT INTO userTable VALUES('Kim','김철수','kim1@naver.com',1996)")

5) 입력한 데이터 저장하기

- 4번까지의 과정은 데이터를 임시로 저장하는 형태다. 확실하게 저장하려면 커밋(commit)을 해야한다.

conn.commit()

6) MySQL연결 종료하기

conn.close()

 

<파이썬에서 데이터 조회하기>

-  MySQL 연결하기 -> 커서 생성하기 -> 데이터 조회하기 -> 조회한 데이터 출력하기 -> MySQL연결 종료하기

-  MySQL 연결하기, 커서 생성하기,  MySQL연결 종료하기는 위와 동일하여 생략함

 

3) 데이터 조회하기

cur.execute("SELECT * FROM userTable")

4) 조회한 데이터 출력하기

- fetchone()은 조회결과를 한 행씩 추출하는 함수이다.

while (True):
	row = cur.fetchone()
    if row == None :
    	break
    data1 = row[0]
    data2 = row[1]
    data3 = row[2]
    print('%s %s %s %s' % (data1,data2,data3))