Stored program 장종원 phobos90@naver.com.

Presentation on theme: "Stored program 장종원 phobos90@naver.com."— Presentation transcript:

1 Stored program 장종원

2 목 차 Stored Program이란 Stored Procedure Stored Function Cursor Trigger
과제

3 STORED PROGRAM Database 내에서 프로그래밍 언어와 같은 기능을 제공하는 것을 통틀어서 말한다
자주 사용하는 복잡한 쿼리의 경우 매번 입력하기보다는 하나로 묶어서 이름을 지정하여 이름만 호출하면 실행되도록 설정하면 편할 것이다 Stored Procedure, Stored Function, Trigger, Cursor 등이 있음 특징 성능 향상 네트워크 부하 감소 긴 코드를 실행하게 되면 클라이언트 → 서버로 모든 텍스트가 전송되어야 함 쿼리를 프로시저로 생성해 놓으면 서버에 저장되어 있으므로 프로시저이름/매개변수만 전송하면 됨 유지관리 간편 응용프로그램(Java Programming 등)에서 SQL문을 작성할 필요 없이 프로시저 이름만 호출하면 됨 모듈식 프로그래밍 가능 한번 생성하면 언제든 실행이 가능하다. 프로시저로 저장해 놓은 쿼리의 수정 및 삭제 등 관리가 수월해짐 보안 강화 사용자 별로 테이블에 접근 권한 주지 않고 스토어드 프로시저에만 접근 권한을 줌으로써 보안강화

4 SQL 프로그래밍 기본 구조 문장의 종료 시점에 세미콜론(;) 사용 프로시저 안에서 변수 선언 및 할당할 때에는
DECLARE 변수이름 변수타입; 변수 선언 SET 변수이름 = 값; 변수에 값 대입 단일행 주석: -- 여러행 주석: /* */

5 Stored Program Stored Procedure Stored Function

6 Stored Procedure(저장 프로시저)
생성 문법 DELIMITER $$ CREATE PROCEDURE 스토어드 프로시저이름 ( IN 또는 OUT 파라미터 ) BEGIN SQL프로그래밍 코딩.. END $$ DELIMITER ; 변수 선언은 보통 BEGIN 이후 바로 이루어진다. IN / OUT 파라미터 프로시저 생성할 때 프로시저 이름 뒤의()안에 채워지게 됨 IN 파라미터 문법 : IN 입력_매개변수_이름 데이터_형식 호출 : CALL procedure(IN_매개변수); IN_매개변수 : 데이터 형식에 맞는 값이나 값이 저장된 변수명 OUT 파라미터 문법 : OUT 출력_매개변수_이름 데이터_형식 호출 : CALL MariaDB의 종료문자는 세미콜론(;)인데 프로시저 안에서도 종료문자가 세미콜론 이므로 구별을 위해 프로시저 종료문자를 변경시킨 것이다. 결국 DELIMITER $$ ~ END $$가 프로시저가 되겠음 프로시저가 작성이 끝나면 종료문자를 다시 세미콜론(;)으로 변경해야 한다. 출력 매개변수에 값을 대입하기 위해 주로 SELECT INTO문이 사용된다

7 Stored Procedure(저장 프로시저)
프로시저 수정 ALTER PROCEDURE 프로시저_이름 [characteristic …] characteristic : { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT ‘string’ 스토어드 프로시저에서 제공하는 보안, 작동방식과 관련된 특성을 수정할 때만 사용가능 프로시저의 파라메터나 내용을 변경할 때는 프로시저를 삭제 후 재생성 해야함( 이전 버전) 프로시저 내용 변경 CREATE OR REPLACE PROCEDURE 프로시저_이름 ( 이후버전) BEGIN SQL 실행.. END 프로시저 삭제 DROP PROCEDURE 프로시저_이름; 프로시저 호출 CALL 프로시저_이름();

8 Stored Procedure(저장 프로시저)
프로시저 목록 확인 SHOW routine_name, routine_definition from information_schema.routines; 프로시저 이름, 내용을 확인할 수 있음(그러나 매개변수 내용이 안 보임) 프로시저 확인 SHOW CREATE PROCEDURE DB명.프로시저명; ‘create Procedure’ 칼럼에서 확인 매개변수까지 확인할 수 있음

9 Stored Procedure(저장 프로시저)
실습8-1 이름을 매개변수로 받아 고객 정보를 출력하는 프로시저

10 Stored Procedure(저장 프로시저)
실습8-2 특정 출판사의 일정 가격보다 높은 서적 목록을 출력하는 프로시저

11 Stored Procedure(저장 프로시저)
실습8-3 특정 금액을 입력 매개변수로 받아, 책의 가격이 그 이상인 책의 개수를 출력 매개변수에 저장하는 프로시저

12 Stored Function(사용자 정의 함수)
기본 형식 DELIMITER $$ CREATE FUNCTION 스토어드 함수이름 (파라미터 ) RETURNS 반환형식 BEGIN SQL프로그래밍 코딩.. RETURN 반환값; END $$ DELIMITER ; 호출 방법 SELECT 스토어드_함수이름(); 파라미터 : 파라미터_이름 자료형

13 Stored Function(사용자 정의 함수)
스토어드 함수 내용 확인 SHOW CREATE FUNCTION 함수명; ‘create Function’ 칼럼 확인 매개변수 및 리턴 값까지 확인할 수 있음 스토어드 함수 수정 CREATE OR REPLACE FUNCTION 함수명 스토어드 함수 삭제 DROP FUNCTION 함수명;

14 Stored Function(사용자 정의 함수)
실습8-4 출생년도를 입력하면 나이(만)가 출력되는 함수생성 후 userTbl에 적용

15 Stored Procedure VS Stored Function
서버에서 실행된다. 리턴 값이 있어도 되고 없어도 된다.(out parameter로 처리, 여러 개 가능) 파라미터로 IN, OUT 둘 다 사용할 수 있다. Select문에서 호출이 불가능하다. CALL로 호출된다. 프로시저 안에서 SELECT문을 사용할 수 있다. 프로시저는 SQL문 실행, 처리를 할 때 주로 사용된다. Function Procedure에서 호출 할 수 없다. 클라이언트에서 처리한다. 리턴 값이 필수이고 하나만 된다. 피라미터로 IN/OUT을 사용할 수 없다. 파라미터는 모두 입력 파라미터이다. Select에서 호출하여 사용한다. 함수 안에서 SELECT문을 사용할 수 없다.(SELECT INTO는 사용할 수 있음) 주로 간단한 계산, 수치 이런 결과를 나타낼 때 사용한다.

16 CURSOR 쿼리에 의해 반환되는 결과는 메모리 상에 위치하게 되는데 커서(cursor)를 이용 하여 결과집합에 접근할 수 있음.(간단하게 포인터라고 생각하면 된다.) 커서 선언 DECLARE 커서명 CUSOR FOR SQL문장; 커서 열기(open) OPEN 커서명; 패치(fetch) FETCH 커서명 INTO 변수…; 커서 닫기(close) CLOSE 커서명;

17 userTbl 내 회원들의 평균 키를 커서를 이용해 구하는 프로시저
CURSOR 실습8-5 userTbl 내 회원들의 평균 키를 커서를 이용해 구하는 프로시저 커서를 연습하기 위해 집계함수 AVG() 기능을 구현 한 것임

18 Trigger Trigger란 Trigger의 생성 Trigger의 조회 및 삭제

19 Trigger란 지정된 Table에 삽입/삭제/수정 등의 작업이 실행 되면 자동으로 실행되는 개체
스토어드 프로시저와 비슷한 모양을 가짐 View가 아닌 Table에 대해서만 정의가 가능(다른 일부 DBMS에서는 가능) 트랜잭션 제어문을 사용할 수 없음 추후에 배우게 될 commit/rollback 같은 트랜잭션 제어문은 트리거/스토어드 함수에서 사용 불가

20 Trigger AFTER/BEFORE 트리거로 나눌 수 있다 기본 문법 DELIMITER //
CREATE TRIGGER 트리거이름 trigger_time trigger_event ON 테이블이름 FOR EACH ROW [trigger_order] trigger_body trigger_time : { BEFORE | AFTER } BEFORE 쿼리가 처리되기 전에 작동 AFTER 쿼리가 처리 된 후에 작동 trigger_event : { INSERT | DELETE | UPDATE } 어떤 쿼리에 반응할지 정함. 1가지만 지정할 수 있음 각 행마다 트리거를 적용시킴 [trigger_order] : { FOLLOWS | PRECEDES } other_trigger_name 테이블에 여러 개의 트리거가 부착되어 있을 때, 다른 트리거보다 먼저/이후에 수행되는 것을 지정

21 트리거가 생성하는 임시 테이블 새 값 새 값 예전 값 예전 값 새 값 예전 값
트리거가 INSERT/UPDATE/DELETE 작업이 수행될 때 임시로 사용되는 시스템 테 이블이 2개 있는데, 이름은 NEW와 OLD이다. NEW 테이블 원래 테이블 INSERT(새 값) 새 값 새 값 원래 테이블 OLD 테이블 예전 값 예전 값 DELETE(예전 값) NEW 테이블 원래 테이블 OLD 테이블 새 값 예전 값 새 값 UPDATE(새 값, 예전 값) 예전 값

22 Trigger 생성 Update Trigger Delete Trigger
(실습 8-6) book 테이블에서 수정 및 삭제를 시도하면, 수정 및 삭제된 데이터를 별도의 테이블에 보관하도록 Trigger 생성 백업 테이블 생성 Update 및 Delete 실행 시 변경 이전 값이 select되는 것을 볼 수 있음 Update Trigger Delete Trigger

23 Trigger 조회 및 삭제 Trigger 목록 조회 방법 Trigger 생성문 확인 Trigger 수정 Trigger 삭제
SHOW TRIGGERS; Trigger 생성문 확인 SHOW CREATE TRIGGER 트리거이름; ‘SQL Original Statement’ 칼럼에서 확인가능 Trigger 수정 CREATE OR REPLACE TRIGGER 트리거이름; Trigger 삭제 DROP TRIGGER 트리거 이름

24 실습 8-7 book테이블에 UPDATE문의 실행되면 변경된 row의 책 이름과 질의문의 실행 시간 기록하는 트리거를 생성하시오 트리거 이름 : book_logTrg CREATE TABLE book_log (changed_book VARCHAR(20), updatetime VARCHAR(30)); 현재 시간 받아오는 함수 : curtime()

25 다중 / 중첩 트리거 고객이 물건 구매 다중 트리거 중첩 트리거 구매 테이블 물품 테이블 배송 테이블
앞에 Trigger 생성 문법에서의 [trigger_order]를 사용하여 한 테이블에 2개 이상의 트리거를 부착할 수 있다 중첩 트리거 트리거가 다른 트리거를 작동시키는 것을 말한다. ex) 쇼핑몰에서의 중첩 트리거 ① 고객이 물건을 구매하면 구매 기록이 구매 테이블에 INSERT됨 ② 구매 테이블에 부착된 INSERT 트리거가 작동되어 ‘물품 테이블’의 남은 개수를 구매한 개수만큼 감소시킨다. (물품 테이블을 UPDATE시킴) ③ 물품 테이블에 부착된 UPDATE 트리거가 작동되어 ‘배송 테이블’에 배송내용을 INSERT시킨다. 구매 테이블 물품 테이블 배송 테이블 고객이 물건 구매 ①INSERT ②UPDATE ③INSERT 물품 테이블에서 남은 개수를 감소시킴 배송 테이블에새 배송 건수 입력시킴 INSERT 트리거 UPDATE 트리거

26 과제 8-1 앞 장에 나온 쇼핑몰 중첩 트리거를 구현해보세요 CREATE TABLE orderTBL -- 구매 테이블
( orderNo INT AUTO_INCREMENT PRIMARY KEY, -- 구매 일련번호 userID VARCHAR(5), -- 구매한 회원 아이디 prodName VARCHAR(5), -- 구매한 물건 orderamount INT ); 구매한 개수 CREATE TABLE prodTBL -- 물품 테이블 ( prodName VARCHAR(5), -- 물건 이름 account INT ); -- 남은 물건수량 CREATE TABLE deliverTBL -- 배송 테이블 ( deliverNo INT AUTO_INCREMENT PRIMARY KEY, -- 배송 일련번호 prodName VARCHAR(5), -- 배송할 물건 account INT UNIQUE); -- 배송할 물건개수 INSERT INTO prodTBL VALUES('사과', 100); INSERT INTO prodTBL VALUES('배', 100); INSERT INTO prodTBL VALUES('귤', 100); -- 트리거 부착 (이 부분 구현하는 것이 과제) -- 구현 후 실습 INSERT INTO orderTBL VALUES (NULL, 'JOHN', '배', 5); select * from orderTBL; select * from prodTBL; select * from deliverTBL;

27 과제 8-2 구구단을 문자열로 생성하여 테이블에 입력하는 프로시저를 만드세요. 먼저 구구단을 저장할 테이블을 생성해주세요
CREATE TABLE guguTBL(txt VARCHAR(100)); 프로시저 내에서 반복문으로 구구단을 문자열로 생성해서 guguTBL에 단 별로 INSERT하도록 프로그래밍 언어에서 구구단을 만들 때와 비슷함

28 과제 8-3 userTbl, buyTbl 테이블을 이용하여 각 회원의 총 구매액에 따라 고객등급을 ‘최우 수고객’, ‘우수고객’, ‘일반고객‘, ‘유령고객’으로 나누어 userID, name, 총 구매액, 고 객등급을 출력하는 프로시저를 생성하세요 . ALTER TABLE userTBL ADD grade VARCHAR(5); -- 고객 등급 칼럼 추가 프로시저 안에서 총 구매액을 기준으로 비어 있는 고객등급 칼럼 Update 고객등급 기준(총 구매액) 1500 이상 최우수고객 1000 이상 우수고객 1 이상 일반고객 그 외 유령고객 Procedure + Cursor + 반복문 + 조건문 Cursor 실습(실습8-5) 참고 저번 7장 과제 집계함수 내용 사용 마지막에 SELECT 할 때 총구매액이 높은사람 순으로