[SK고용디딤돌] MySQL (5/7) - 4주차

[ 실습파일 ]

0725_sql_example.sql

0725_stored_procedure_example.sql

* last_insert_id()

- 마지막 insert id를 알아낼 수 있다.

* flow of control (제어의 흐름)

- conditional statement 조건문

- loop statement 반복문

* Stored Procedure

- SQL + 3GL(Third Generation Language, e.g,  C, C++, JAVA, Fortran)

1) Stored Procedure

2) Stored Function

3) Stored Trigger ...

* Dummy 데이터를 만드는 방법

1) insert 문 반복

2) tool 이용

3) store procedure 이용

4) 개발 언어 + SQL

* import data

- db -> table 오른쪽 클릭 -> import wizard -> .csv를 import 하면 쉽게 데이터를 넣을 수 있다 (특징, AI 값을 지정 가능)

* join

- join 의 "on" 은 PK와 FK의 컬럼의 비교문장이 오게 됨

- N개의 TABLE을 JOIN 하려면, N-1개의 JOIN 구문이 필요

* view

- view는 실행할 query를 가지고 있는 객체 -> 재사용성/ 편의성 증가

- view를 쓰는 이유? 생산성 증가

- 테이블은 물리적인 구조, 뷰는 논리적인 결과이므로 뷰는 보안성을 증가시킨다. (뷰만 바라봐야 함) -> 뷰는 조회 용도

- 조건을 만족하는 조인을 inner join(↔ outer join) 이다.

- Inner join 중 대표적인 것은 equi join

SELECT *
FROM 사원 a JOIN 부서 b ON ( a.부서번호 = b.부서번호);

[ join & view 예제 ]

[ substring 예제 ]

1) 1개의 값 : =

2) 2개 이상의 값 : IN()

3) 결과에서 중복 제거 : distinct

<cross join>

- cross join은 cartesion product의 연산을 뜻함 모든 경우의 수를 다 곱해 줌

<inline view>

- 인라인 뷰는 반드시 alias 지정이 필요하다

select *

from (

select *

from db

) a;

<date>

- 오늘을 기준으로 -0 ~ -999까지 데이터 만들기

SELECT now() - INTERVAL (tbl1.col1 + (10 * tbl2.col1) + (100 * tbl3.col1)) DAY order_date

<명령어>

- DELIMITER 여기 까지 하나의 명령어로 본다. 

  (예) DELIMITER ;; 으로 하나의 구분자 처리

- DECLARE 변수 선언을 위한 명령어,

- CURSOR는 셀렉트문에 의해 만들어진 것

1) 셀렉트 문을 실행하면 자동으로 IMPLICIT CURSOR 가 만들어진다. 

2) 이 커서 객체를 CURSOR 프로그램에서 사용하기 위해 지정할 수 있다.

3) 셀렉트를 수행하는 커서를 직접 만드는 것이다. 그 결과를 루프를 돈다.

4) 결과 하나하나 꺼내서 처리할 수 있다. 커서는 처리단위가 행 단위이다. ( next.. next.. next.. )

- 값을 할당할 때 := 을 사용, (=는 비교연산자)

- @ : 세션변수를 선언할 때 골뱅이를 붙여주자

- db객체로 프로시저를 만든다. 프로시저는 db 서버에 존재하는 프로그램이다.

- 프로시저는 만드는방법, 문법 등 DB마다 다 다르다.

- call : 프로시저 호출

- 프로시저에는 파라미터, 지역변수도 있음

- sql 클라이언트에 연결을 하면 sql 클라이언트에서 사용할수 있는 변수를 세션변수라고 한다. (세션이 살아있는 동안만 있는 변수)

- 프로시저는 보통 프로그래머가 만든다.

- 함수는 보통 제공된다. 함수도 만들수도 있지만 까다로움

[ Explicit Procedure 예제 ]

DELIMITER ;;

CREATE PROCEDURE sp_select_branch_id(IN p_branch_name VARCHAR(45), OUT p_branch_id INT) 

BEGIN

DECLARE v_no_more_rows BOOLEAN DEFAULT FALSE; // 디폴트 값 지정

DECLARE v_cursor_branch CURSOR FOR // 커서 설정

SELECT id 

FROM branch

WHERE name = p_branch_name;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_rows := TRUE; // 없을 때 true 설정

OPEN v_cursor_branch; // 커서 열기

loop_branch : LOOP // 루프의 이름

FETCH v_cursor_branch INTO p_branch_id; // 가져오는 것 fetch(당기는 것) - 없을 때 까지

IF v_no_more_rows THEN  // 패치하다가 없으면 not found가 발생

SET v_no_more_rows := FALSE; // false 로 재설정

CLOSE v_cursor_branch; // 커서제거

LEAVE loop_branch; // 루프 빠져 나오기

END IF;

END LOOP loop_branch; // 루프의 이름

    

END;;

[ REPEAT .. UNTIL 예제 ]

SET @X := 0;

REPEAT @X  = @X: + 1;

UNTIL @X > p1;

END REPEAT;

REPEAT 식

UNTIL 조건식
END REPEAT

[ Stored Procedure(=SP) 예제 ]

- 변수 선언 → 커서 선언 → 대입 처리순

- 1건일 때는 cursor 안쓰고 그냥 하는게 깔끔 함

[특정일자에 주문 건수를 구하는 SP - implicit]

[특정일자에 주문 건수를 구하는 SP - explicit]

[특정일자에 특정지점의 주문 건수를 구하는 SP - implicit]

[특정일자에 특정지점의 주문 건수를 구하는 SP - explicit]

[특정일자의 특정지점의 총 매출을 구하는 SP - implicit]

[특정일자의 특정지점의 총 매출을 구하는 SP - 선생님 정답 : concat을 활용하여 OUT을 하나만 해도 됨]

[ SQL 예제 ]