데이터베이스 10주차 정리

저저번주에 본 시험 점수는 86점이고 평균은 70점 정도이다.

---

저장 프로그램

프로시저

---

저장 프로그램 (Stored Program)

- 저장 프로그램(Stored Program) : 데이터베이스 응용 프로그램을 작성하는데 사용하는 MySQL의 SQL 전용 언어

 

- SQL 문에 변수, 제어, 입출력 등의 프로그래밍 기능을 추가하여 SQL 만으로 처리하기 어려운 문제를 해결함

 

- 저장 프로그램은 Workbench에서 바로 작성하고 컴파일한 후 결과를 실행함

 

- 프로그램 로직을 프로시저 (Procedure)로 구현하여 사용한다.

 

Procedure (프로시저)

- 프로시저를 정의하려면 CREATE PROCEDURE 문을 사용함

 

정의 방법

- 프로시저는 선언부와 실행부(BEGIN-END)로 구성됨

- 선언부에서는 변수와 매개변수를 선언하고 실행부에서는 프로그램 로직을 구현함

- 매개변수(parameter)는 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값

- 변수(variable)는 저장 프로시저나 트리거 내에서 사용되는 값

- 소스코드에 대한 설명문은 /*와 */ 사이에 기술

- 설명문이 한 줄이면 이중 대시(--) 기호 다음에 기술해도 됨

삽입 작업을 하는 프로시저

프로시저로 데이터 삽입 작업을 하면 좀 더 복잡한 조건의 삽입 작업을 인자 값만 바꾸어 수행할 수도 있고,

저장해 두었다가 필요할 때마다 호출하여 사용할 수도 있음

 

products 테이블에 한 개의 레코드를 삽입하는 프로시저를 만들어라

새로 만든 프로시저를 이용해 (가디건, 톰브라운, 100000)을 product 테이블에 추가해라

※ 프로시저는 CALL 명령어를 이용한다.

 

CALL InsertProduct('가디건', '톰브라운', 100000);

SELECT * FROM products;

제어문을 사용하는 프로시저

저장 프로그램의 제어문은 어떤 조건에서 어떤 코드가 실행되어야 하는지를 제어하기 위한 문법으로,

절차적 언어의 구성요소를 포함함

 

데이터를 입력하기 전, 동일한 상품이 있는지 확인하고 삽입하는 프로시저를 만들어라

결과를 변환하는 프로시저

함수와 같이 계산된 결과를 반환해주는 프로시저이다.

 

저장 프로시저 매개변수에는 3가지 모드가 있다

- IN

프로시저에 값을 전달하며, 프로시저 내부에서 값을 수정할 수는 있지만 프로시저가 반환되고나서 호출자가 수정할 수는 없다

 

- OUT

프로시저의 값을 호출자에게 다시 전달한다.
초기값은 프로시저 내에서 NULL값이며 프로시저가 반환될 때 새로운 값이 호출자에게 리턴된다 (FUNCTION의 RETURNS ***와 비슷하다)

 

- INOUT

호출자에 의해 하나의 변수가 초기화되고 프로시저에 의해 수정된다. IN+OUT의 결합이다

 

products 테이블에 저장된 상품들의 평균 가격을 반환하는 프로시저를 작성해라

사용자가 설정하는 변수 (User Defined Variable)은 @value_name으로 설정한다.

 

커서를 사용하는 프로시저

- 커서(cursor)는 실행 결과 테이블을 한 번에 한 행씩 처리하기 위하여 테이블의 행을 순서대로 가리키는 데 사용함

 

- 커서에 사용되는 키워드

 

orders 테이블에서 판매 상품에 대한 이익을 계산하는 프로시저를 만들어라

30,000원 이상이면 10%, 미만이면 5%의 이익을 계산해라

---

트리거

---

트리거

트리거(trigger) : 데이터의 변경(INSERT, DELETE, UPDATE) 문이 실행될 때 자동으로 따라서 실행되는 프로시저

 

부수적으로 필요한 작업인 데이터 기본값 제공, 데이터 제약 준수, SQL 뷰의 수정 등을 실행

 

※Workbench에서 트리거 작동을 위해 다음 문장 실행

SET global log_bin_trust_function_creators = ON;

 

새로운 상품을 삽입한 후 자동으로 product_log 테이블에 삽입한 내용을 기록하는 트리거 생성