18. 스토어드(STORED) 루틴

---

18. 스토어드(STORED) 루틴

• 스토어드 루틴의 정의 • 저장 프로시져와 저장 함수의 차이 • 스토어드 루틴의 생성 • 스토어드 루틴의 실행 • 존재하는 스토어드 루틴 검사 • 존재하는 스토어드 루틴 삭제 • 변경할 수 있는 스토어드 루틴의 특징 설명 • 변경할 수 없는 스토어드 루틴의 특징 설명 • 복합적인 구문을 가진 스토어드 루틴의 생성 • 스토어드 루틴에 변수 할당 • 플로우 컨트럴 구문 생성 • 선언(Declare)와 핸들러 사용 • 커서(Cursor 사용과 한계

18-1. 스토어드 루틴이란?

• 서버에 저장할 수 있는 SQL 구문 설정. • 타입 - 저장 프로시져 • 프로시져는 CALL 구문을 사용하여 실행되고 변수 출력을 사용하여 값을 뒤로 넘길 수만 있음 - 저장 함수 • 함수는 구문에 포함하여 사용할 수 있고 스칼라 (scalar) 값을 되돌릴 수 있음

18-2. 스토어드 루틴 사용

• 어플리케이션 클라이언트. - 하나의 어플리케이션 - 하나의 프로그램 언어 • 보안 - 최소 데이터 억세스 - 한 위치 프로세싱 • 성능 • 함수 라이브러리 18-3. 스토어드 루틴의 이슈

• 증가된 서버 로드. • 제한된 개발 툴 • 제한된 함수적 언어와 속도 • 제한된 디버깅/프로파일링 능력 18-4. 스토어드 루틴의 생성

• 프로시져 생성 - 형식 CREATE PROCEDURE 프로시져명 프로시져_구문 - 싱글 구문 mysql> CREATE PROCEDUREworld_record_count () -> BEGIN SELECT 'country count ', COUNT(*) FROM Country; -> END//• 함수 생성 - 형식 CREATE FUNCTION 함수명 RETURNS 리턴_타입 함수_구문 - 싱글 파라미터 mysql> CREATE FUNCTIONThankYou (s CHAR(20)) RETURNS CHAR(50) -> RETURNCONCAT('Thank You, ',s,'!');

18-5. 혼합된 구문

• DELIMITER • BEGIN ... END mysql> DELIMITER //mysql> CREATE PROCEDURE world_record_count () -> BEGIN -> SELECT 'country count ', COUNT(*) FROM country; -> SELECT 'city count ', COUNT(*) FROM city; -> SELECT 'countrylanguage count', COUNT(*) FROM countryLanguage; -> END//mysql> DELIMITER ;

18-6. 변수 할당

• DECLARE – Declaring – Scope mysql> DELIMITER //mysql> CREATE FUNCTION add_tax (total_charge FLOAT(9,2)) -> RETURNS FLOAT(10,2) -> BEGIN -> DECLARE tax_rate FLOAT (3,2) DEFAULT 0.07; -> RETURN total_charge + total_charge * tax_rate; -> END//mysql> DELIMITER ;

18-7. 변수 할당(SELECT ... INTO)

• SELECT ... INTO - Global 변수 mysql> SELECT SUM(population) FROM country INTO@WorldPop; ... is equivalent to ... mysql> SELECT SUM(population) INTO@WorldPop FROM country; - Local 변수(DECLARE 구문 추천) mysql> SELECT COUNT(*) FROM city INTOTotal_Cities; ... is equivalent to ... mysql> SELECT COUNT(*) INTO Total_Cities FROM city; 18-8. 변수 할당(SET)

• SET - SET 구문은 연산자로서 = 혹은 := 를 사용하여 사용자 가 정의한 변수를 위해 사용자가 정한 값에 따름 mysql> DELIMITER //mysql> CREATE FUNCTION final_bill -> (total_charge FLOAT(9,2), tax_rate FLOAT (3,2)) -> RETURNS FLOAT(10,2) -> BEGIN -> DECLARE Fbill FLOAT(10,2); -> SET Fbill=total_charge + total_charge * tax_rate; -> RETURN Fbill; -> END//mysql> DELIMITER ; 18-9. 변수 영역

• Local 변수. • 루틴 파라미터 • 내부 블럭에서의 Local 변수 • 외부 블럭에서의 Local 변수 mysql> DELIMITER //mysql> CREATE PROCEDURE precedence (opalINT) -> BEGIN -> DECLARE opal INT DEFAULT 0; -> SELECT opal FROM test_table; -> BEGIN -> DECLARE opal INT DEFAULT 1; -> SELECT opal FROM test_table; -> END; -> END//mysql> DELIMITER ; 18-10. 파라미터 선언

• 저장 프로시져. - IN(기본) • 프로시져를 콜러(caller)로부터 지나 입력 파라미터 를 가리킴. - OUT • 프로시져에 의해 놓여지고 프로시져 종결된 이후 콜 러(caller)로부터 지나 출력 파라미터를 가리킴. - INOUT • IN과 OUT 파라미터로 동작할 수 있는 파라미터를 가리킴. • 저장 함수 - 저장 함수를 위한 단지 하나의 가능한 프로시져 파마리 터 선언: IN, 그리고 이와 같이 기본으로 설정되고 그 파라미터 값을 앞서가기 위해 필수적이지 않음.

18-11. 스토어드 루틴 실행

•프로시져 실행. • 함수 실행 • 함축된 데이터베이스의 조합 - 데이터베이스 사용 - 한정된 이름 - 데이터베이스가 삭제되면 루틴도 삭제됨 • SELECT 구문 - 오직 저장 프로시져 - 결과는 직접적으로 클라이언트에 보내 설정 18-12. 수정할 수 있는 스토어드 루틴의 특징

• ALTER PROCEDURE / FUNCTION. – SQL SECURITY • DEFINER • INVOKER – Data Use (SQL) • CONTAINS SQL • NO SQL • READS SQL DATA • MODIFIES SQL DATA – COMMENT 18-13. 수정될 수 없는 스토어드 루틴의 특징

• 수정 할 수 없음. – DETERMINISTIC • 파라미터 값을 입력했 놓았을 때 루틴(routine) 항상 같은 결과를 생성함. – NOT DETERMINISTIC (Default) • 파라미터 값을 입력해 놓았을 때 루틴(routine) 결과 는 가능한 변경될 수 있음. – LANGUAGE • 파싱된 스토어드 루틴이 생성될 때 사용하기 위한 프로그래밍 언어를 가지고 파서(parser)를 공급함. 18-14. 스토어드 프로시져 보기

• SHOW CREATE PROCEDURE / FUNCTION - 특정 MySQL - 정확한 코드 문자열 리턴 • SHOW PROCEDURE / FUNCTION STATUS - 특정 MySQL - 특성의 루틴 리턴 • INFORMATION_SCHEMA.ROUTINES - 표준 SQL - SHOW 명령어와 결합된 리턴

18-15. 스토어드 루틴 삭제

• DROP PROCEDURE - 문법 DROP PROCEDURE [IF EXISTS] 프로시져명 mysql> DROP PROCEDURE proc_1; • DROP FUNCTION. - 문법 DROP FUNCTION[IF EXISTS] 함수명 mysql> DROP FUNCTIONIF EXISTS func_1; 18-16. 흐름 제어 구문

• 제어 명령 동작의 실행 구문과 컨스트럭트(Construct) • 일반 흐름 제어 – 선택 • IF와 CASE – Loops • REPEAT, WHILE와 LOOP 18-17. IF

- 모든 가장 기본적인 선택은 흐름 제어 혹은 조건 컨스 트럭트(construct) IF(조건) THEN ... ELSEIF(조건) THEN ... ELSE ... END IF 18-18. CASE

• CASE는 개발의 복잡한 제어 컨스트럭트(construct)의 수단을 제공. • CASE는 특정 일정하고 우선적으로 일정하게 연결된 동 작의 주어진 값을 비교의 원리로 동작함. CASE 케이스_값 WHEN 값 THEN .... ELSE .... END CASE

CASE WHEN 조건 THEN .... ELSE .... END CASE

18-19. REPEAT

• REPEAT 구문은 조건이 참이 될까지 SQL 문을 반복. • REPEAT 구문은 항상 최소 한번은 실행 • 레벨 – Begin – End begin_label : REPEAT .... UNTIL 조건 END REPEAT end_label 18-20. WHILE

• WHILE는 조건이 참이 되는 동안 SQL 문을 반복 실행 • 구문 리스트는 하나 혹은 그이상의 구문으로 구성할 수 있음 begin_label: WHILE 조건 DO ... END WHILE end_label 18-21. LOOP

• 루프 안에 구문은 그 루프가 존재 할때 까지 반복됨; 흔 히 LEAVE 구문으로 완성됨 begin_label: LOOP ... LEAVE 레벨; END LOOP end_label 18-22. 다른 레벨 흐름 제어 컨스트럭트(Construct)

• LEAVE. - 이 구문은 LOOP, REPEAT 혹은 WHILE 구문으로 어 떤 레벨 흐름 제어 컨스트럭트(construct)를 벗어나서 사용됨 - LEAVE는 레벨 안에서 BEGIN ... END 복함 구을 사용 할 수 있음 • ITERATE - 구문은 간단한 의미이다 “do the LOOP(or REPEATor WHILE) again”

18-23. DECLARE 구문 문법

• 루틴(Routine)을 위한 로컬(Local) DECLARE 구문 정 의 항목 – Local 변수 – 조건과 핸들러 – 커서(Cursor) • DECLARE는 단지 내부에 BEGIN ... END를 따름 • 선언(Declaration)은 틀림없이 특정 명령이 따름 – 변수(Variable) – 조건(Condition) – 커서(Cursor) – 핸들러(Handler)

18-24. DECLARE CONDITION

• DECLARE CONDITION. - 형식 DECLARE 조건_명 CONDITION FOR 조건_값; - SQLSTATE 조건 값 DECLARE null_not_allowed CONDITION FOR SQLSTATE ‘23000’; - MySQL 에러 코드 조건 값 DECLARE null_not_allowed CONDITION FOR 1048;

18-25. DECLARE HANDLER

• DECLARE CONTINUE HANDLER DECLARE CONTINUE HANDLERFOR SQLSTATE ‘23000’SET @x = 1; • DECLARE EXIT HANDLER • 추가 조건 값 – Declared Conditions – SQL WARNING – NOT FOUND – SQL EXCEPTION

18-26. 커서(Cursors)

• Stored Routinesfor Record Retrieval! 안에 제어 구조 임. – 한번에 한열 • CURrent Set Of Records을 짧게 커서(Cursor)함 • 대부분 Fetch와 Process Rows의 루프(Loop)를 사용 함 • Asensitive • Read-Only • Non-Scrolling

18-27. 커서 선언

• 커서는 핸들러 선언 전에 선언되어야 함. - 형식 DECLARE 커서명 CURSOR FOR select_구문; • OPEN - 미리 선언된 커서를 열음 • FETCH - 특정 열려진 커서를 사용한 다음 열은 얻고 커서 포인 트를 전진 시킴 - 다음 열이 없으면, 에러 • CLOSE - 미리 열려진 커서는 닫음 - 명확하게 닫혀있지 않다면, 커서는 선언 되어진 복합 구문 끝에 닫아야 함

18-28. 커서 한계

• 주목할 만한 한계. – Read-only – 업데이트 할 수 있는 커서(cursors)는 지원되지 않음. – Asensitive – Non-scrollable – Not named – 단지 커서(cursor) 하나의 프리페어드(prepared) 구 문임. – 구문의 결과는 틀림없이 프리페어드(prepared) 모드 mode여야 함 – 기본 열에서 동작 – 열을 스킵(skip) 할수 없음

---