스토어드 프로그램 장단점
장점
데이터베이스 보안향상
1.프로그램 단위로 실행 권한 부여가능
조합하면 칼럼단위의 권한 조절 가능
2.SQL-injection 등에 강함
? 기능의 추상화
여러 프로그램에서 사용하는 일련번호 생성기
네트워크 소요 시간 절감
절차적 기능 구현
개발 업무 구분
단점
낮은 처리 성능
문자열 조작이나 숫자 계산등의 연산을 할경우 엄청 느리다
시간당 처리 횟수의 배수
c/c++ 88.6배
java 60.9배
oracle 2.1배
mySql 1.0
애플리케이션 코드의 조각화
문법
헤더
정의부
이름
입출력값
보안
작동방식 옵션
본문
실행되는 부분
*기본설정
함수와 괄호 사이의 공백 무시
IGNORE_SPACE
thrad_stack = 512K
스토어드 프로시저
데이터를 주고 받아야 하는 여러쿼리를 하나의 묶음으로 사용하는것
*스토어드 프로시저는 반드시 독립적으로 생성
select or update문에서 프로시져 참조 불가
프로시저는 내용은 변경 못한다. drop 후 crate 해야한다.
ALTER 프로시저의 경우 속성 변경에 사용
레이블 신택스
[begin_label:] BEGIN
[statement_list]
END [end_label]
DELIMITER
프로시저의 시작과 종료를 알리기 위해 ';' 대신 사용
사용후 다시 바꿔야 한다.
EX)
DELIMITER $$
프로시저..
END$$
DELIMITER ;
ex)
DELIMITER $$
CREATE PROCEDURE `test`.`sp_num` (IN param1 Integer, IN param2 Integer, OUT param3 Integer)
BEGIN
SET param3 = param1 + param2;
END
실행
SET @result :=0;
SELECT @result;
CALL test.sp_num(1,2,@result);
SELECT @result;
*OUT, INOUT 파라미터는 세션변수를 만들어 넘겨주어야 한다.
*프로그램 언어의 경우 세션 변수 없이 받아 올수 있음
*커서는 그냥 SELECT 할 경우 CLINET 언트에 전송된다(출력된다)
1.디버깅 용으로 사용 할 수 있음
2.두개 이상의 결과 셋을 반환 할수 도 있다 .
ex1)
CREATE PROCEDURE `employees`.`sp_selectEmps` (IN in_emp_no INTEGER)
BEGIN
SELECT * FROM employees WHERE emp_no = in_emp_no;
END
ex2) 실행
SET @result := 0;
CALL sp_sum(1,2,@result);
SELECT @result;
프로시저
CREATE PROCEDURE `employees`.`sp_sum` (IN in_int1 INTEGER, IN in_int2 INTEGER, OUT out_result INTEGER)
BEGIN
SELECT '> START' AS debug_message;
SELECT CONCAT(' > P1', in_int1) AS debug_message;
SELECT CONCAT(' > P2', in_int2) AS debug_message;
SET out_result = in_int1 + in_int2;
SELECT CONCAT(' > result', out_result) AS debug_message;
SELECT '> END' AS debug_message;
END
스토어드 함수
sql문장 일부에서 참고 가능
문법
1.입력 파람은 모두 읽기 전용이라 IN, OUT 을 선언 할수 없다.
2.return 값을 선언 해야 한다.
3.함수 본문에서 return 을 해야 한다.
함수에서 사용못하는 문법
1.prepare, execute 명령을 이용한 프리페어 문
2.명시적, 묵시적 RollBack/commit
3.재귀호출
4.함수 안에서 프로시져 호출
5.결과 셋을 반환하는 SQL안됨
*SELECT 를 사용 하면 클라이언트에 결과 셋이 반영되므로 INTO 를 꼭 쓰자
ex)
CREATE FUNCTION `employees`.`sf_sum` (num1 Integer, num2 Integer)
RETURNS INTEGER
BEGIN
DECLARE result INTEGER DEFAULT 0;
SET result = num1 + num2;
RETURN result;
RETURN 1;
트리거
성능에 부하 걸림
5.16 이전 버전 슈퍼만 생성가능
5.x 하나의 이벤트에 최대 2개의 트리거
P659
이벤트(스케쥴러)
아래와 같이 하면 start 시간에 맞춰 실행된다.
DELIMITER $$
ALTER EVENT `EVENT_DAILY_ARENA_RANK_USER_INFO_C` ON SCHEDULE EVERY 1 DAY STARTS '2013-04-03 17:36:00'
DO
BEGIN
SET @o_sp_rtn := -1;
CALL USP_BatchOperation_DAILY_ARENA_RANK_USER_INFO_C( @o_sp_rtn );
END$$
DELIMITER ;
*이벤트를 inable 시켜줘야 된다.
SELECT @@event_scheduler;
SET GLOBAL event_scheduler = 1;
주어진 특성 시간에 스토어드 프로그램을 실행
스케쥴 처리 스래드를 뛰어 스래드가 처리함
파라미터 설정파일
event_scheduler = ON (or 1)
*따로 로그를 만들지 않고 가장 최근 정보만 기록함
INFORMATION_SCHEMA.EVENTS
시작시간 확인 SELECT * FROM information_schema.`EVENTS`
*실행 이력이 필요한 경우 테이블 생성 후 이벤트 처리 로직에서 기록
기록하는게 좋다
생성
반복성 이벤트
매일 01:00:00에 처리됨
CREATE EVENT daily_ranking
ON SCHEDULE EVERY 1 DAY STARTS '2013-01-22 01:00:00' ENDS '2013-01-25 12:59:59'
DO
INSERT INTO test1 VALUE(1,201);
일회성 이벤트
* 현재 시간으로 부터 1시간뒤에 처리됨
CREATE EVENT one_time_job
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO test1 VALUE(1,201);
본문
DO
단순한 SQL문 실행시
아니면
DO
BEGIN
내용..
END;;
ON COMPLETION 완정이 종료되면 삭제됨(DEFAULT)
ON COMPLETION NOT PRESERVE 로 하면 삭제 안됨
확인
등록된
INFORMATION_SCHEMA.events에서 확인가능
마지막 실행된
mysql.event
last_executed 마지막으로 실행된
*information_schema.event 와 mysql.event 는 관리되는 시간의 TIME_ZONE
이 다르기 때문에 시간이 조금 다르다. 같게 하려면 타임존 변환을 하면됨
P666확인
본문 문법
BEGIN..END 로 시작하며 BEGIN..END 가 중첩 될수 있다
mysql에서 트랜잭션 시작방법
BEGIN
START TRANSACTION
*하지만 프로시저류에서는 BEGIN 문법은 안된다.
스토어드 프로시저나 이벤트 본문에서만 사용가능 한 문법
*트랜잭션이 중일 경우 라도 프로시저에서 내부에서 COMMIT;을 하게되면 외부 트랜잭션까지 같이 종료됨
*즉 PROPERGATION 이 없음(전파 되지 않음)
트랜잭션 예제
CREATE PROCEDURE `employees`.`sp_hello` ()
BEGIN
START TRANSACTION;
INSERT INTO test value(1,2);
COMMIT;
END
*단 프로시저에서 트랜잭션을 완료 할경우 APP에서의 트랜잭션 시작도 완료
되어 버린다.:) APP에서 할지 PROCEDURE에서 할지 확실이 결정 하자
* 많은 사용자가 프로시저에서 트랜잭션을 완료 할거라고 생각하지 않는다 고려하자!!
변수 선언(BEGIN..END 에서 사용하는)
스토어드 프로그램 로컬 변수 -> 로컬 변수
프리페어 스테이트 먼트를 사용하려면 반드시 사용자 변수를 사용
*사용자 변수보다 로컬 변수가 빠르다
사용자 변수를 너무 많이 쓰면 느려질 수 있다.
선언
DECARE v_name 자료형 DEFAULT 'value'
값 넣기
SET v_name = 'value', v_email = 'value@'
SELECT emp_no, first_name, last_name INTO v_empno, v_firstname, v_lastname
FROM employees
WHERE emp_no = 10001
LIMIT 1;
SELECT..INTO 는 반드시 하나의 레코드에만 사용 할 수 있다
없다거나 두개 이상이면 에러 발생
IF..ELSE IF ..ELSE ..END IF
CREATE FUNCTION EMPLOYEES.`sf_greatest` (p_value1 INT, p_value2 INT)
RETURNS INT
BEGIN
IF p_value1 IS NULL THEN
RETURN p_value2;
ELSEIF p_value2 IS NULL THEN
RETURN p_value1;
ELSEIF p_value1 >= p_value2 THEN
RETURN p_value1;
ELSE
RETURN p_value2;
END IF;
END
CASE WHEN..TEHN..ELSE.. END CASE
동등비교 에서
CASE 변수
WHEN 비교1 THEN 처리내용1
WHEN 비교2 THEN 처리내용2
ELSE 처리내용3
END CASE
일반
CASE
WHEN 비교조건식1 THEN 처리내용1
WHEN 비교조건식2 THEN 처리내용2
ELSE 처리내용3
END CASE
*BREAK 문 필요 없음
ex)
CREATE FUNCTION `employees`.`sf_greatest1` (p_value1 INT, p_value2 INT)
RETURNS INT
BEGIN
CASE
WHEN p_value1 IS NULL THEN
RETURN p_value2;
WHEN p_value2 IS NULL THEN
RETURN p_value1;
WHEN p_value1 >= p_value2 THEN
RETURN p_value1;
ELSE
RETURN p_value2;
END CASE;
END
루프문
WHILE
LOOP
REPEAT
LOOP문 예제
CREATE FUNCTION `employees`.`sf_loop` (p_max INT)
RETURNS INTEGER
BEGIN
DECLARE v_factorial INT DEFAULT 1;
factorial_loop : LOOP
SET v_factorial = v_factorial * p_max;
SET p_max = p_max -1;
IF p_max <= 1 THEN
LEAVE factorial_loop;
END IF;
END LOOP;
RETURN v_factorial;
END
REPEAT문 예제
CREATE FUNCTION `employees`.`sf_repeat` (p_max INT)
RETURNS INTEGER
BEGIN
DECLARE v_factorial INT DEFAULT 1;
REPEAT
SET v_factorial = v_factorial * p_max;
SET p_max = p_max -1;
UNTIL p_max <= 1
END REPEAT;
RETURN v_factorial;
END
WHILE문 예제
CREATE FUNCTION `employees`.`sf_while` (p_max INT)
RETURNS INTEGER
BEGIN
DECLARE v_factorial INT DEFAULT 1;
WHILE p_max > 1 DO
SET v_factorial = v_factorial * p_max;
SET p_max = p_max -1;
END WHILE;
RETURN v_factorial;
END
핸들러와 컨디션을 통한 에러 핸들링
에러No ,sql state
에러 No
Mysql에러 번호
sql state
ansi 기준 에러 번호
*sql state 가 좀더 추상화 되어있다 그러므로 핸들링 할때 sql state 로 하는게 좋다.
P679
핸들러
문법
DECLARE handler_type HANDLER
FOR condtion_value [, condtion_value] ..
handler_statements
handler_type
CONTINUE
핸들러 구문 실행 후 프로그램의 다음 구문 실행
EXIT
핸들러 구문 실행 후 선언된 BEGIN..END 종료
*반드시 함수의 반환 타입에 맞는 코드를 반환 하는 코드가 있어야함
condtion_value
SQLSTATE
*error no 는 버젼마다 다르니 sqlstat를 사용하자
00 정상처리
01 경고
02 not found
SQLEXCEPTION
NOT FOUND 와 SQLWARRING을(00,01,02) 제외한 으로 시작하는 모든값
*00000 은 사용말자
handler_statements
핸들러로 플로우가 넘어올 경우 처리 로직 작성
컨디션
?
에러 번호는 해석이 힘드니까 에러 번호를 문자에 맵핑 하는것
문법
DECLARE condition_name CONDITION FOR condition_value
condtion_value
mysql error 번호일 경우 바로 기록
SQLSTATE 일경우 SQLSTATE를 입력하고 값을 입력
ex)
DECLARE dup_key CONDTION FOR 1062;
ex)
CREATE FUNCTION sf_testfucn()
RETURNS BIGINT
BEGIN
DECLARE dup_key CONDITION FOR 1062
DECLARE EXIT HANDLER FOR dup_key
BEGIN
RETURN -1;
END;
INSERT INTO tb_test VALUES(1);
RETURN 1;
END;;
시그널
사용자가 에러 생성 후 던짐(throw)
5.5미만 버전은 해당 문법이 없어 존재하지 않는 테이블을 일부로 셀렉트해서 에러 던지는 꽁수 사용
P685
주의 사항
1.SQLSTATE 사용
2.SQLSTATE 는 정수가 아닌 5자리 문자열이다 '' 사용하자
3.경고도 발생 시킬수 있다.
4.에러 코드
00은 정상이므로 쓰면안된다.
01은 경고 이므로 종료안시킴
그외의 값 에러 발생
5.일반적으로 유저 에러임으로 45로 시작하는 값을 사용하자
ex)p686
BEGIN
DECLARE null_divisor CONDTION FOR SQLSTATE '45000'
IF p_divisor IS NULL THEN
SIGNAL null_divisor SET MESSAGE_TEXT = 'divisor can not be null', MYSQL_ERRNO=9999;
ELSEIF p_divisor = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'divisor can not be null', MYSQL_ERRNO=9999;
ELSE
SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'divisor can not be null', MYSQL_ERRNO=9997;
RETUNR 0;
END IF;
*핸들러에서 에러를 받아 다시 집어 던지는것도 가능하다.p689
커서
JDBC의 리절트셋 과 비슷
커서 제한사항
1.스토어드 프로그램의 커서는 전방향(전진) 읽기만 가능
2.칼럼을 바로 업데이트하는것이 안됨
DBMS 커서의 종류
insensitive
레코드를 임시테이블로 복사해서 가지고 있는 형태
(복사해야 되기때문에 느리다)
2.복사했기 때문에 변경이나 삭제가 불가능하다
sensitive
레코드에 대한 포인터를 가지고 있다
변경할경우 바로 적용된다.
insensitive + sensitive = Asensitive
MySql은 두개를 복합적으로 사용함
하지만 유저는 어느 것인지 알 수 없기 때문에 커서를 사용해 레코드에 대한 변경이나 삭제는 불가능 하다.
사용 순서
1.커서 정의 후
2.오픈한면 실제로 실행되고
3.페치 명령으로 레코드를 읽을수 있으며
4.클로우즈로 닫는다.
ex)
DELIMITER ;;
CREATE FUNCTION sf_emp_count(p_dept_no VARCHAR(10))
RETURNS BIGINT
BEGIN
/* 사원 번호가 20000보다 큰 사원의 수를 누적하기 위한 변수 */
DECLARE v_total_count INT DEFAULT 0;
/* 커서에 더 읽어야 할 레코드가 남아 있는지 없는지 여부를 위한 플래그 변수 */
DECLARE v_no_more_data TINYINT DEFAULT 0;
/* 커서를 통해서 SELECT된 사원번호를 임시로 담아 둘 변수 */
DECLARE v_emp_no INTEGER;
/* 커서를 통해서 SELECT된 사원의 입사 일자를 임시로 담아 둘 변수 */
DECLARE v_from_date DATE;
/* v_emp_list 라는 이름으로 커서 정의 */
DECLARE v_emp_list CURSOR FOR
SELECT emp_no, from_date FROM dept_emp WHERE dept_no=p_dept_no;
/* 커서로부터 더 읽을 데이터가 있는지 없는지 플래그 변경을 위한 핸들러 */
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_data = 1;
/* 정의된 v_emp_list 커서를 오픈 */
OPEN v_emp_list;
REPEAT
/* 커서로부터 레코드 한 개씩 읽어서 변수에 저장 */
FETCH v_emp_list INTO v_emp_no, v_from_date;
IF v_emp_no > 20000 THEN
SET v_total_count = v_total_count + 1;
END IF;
UNTIL v_no_more_data END REPEAT;
/* v_emp_list 커서를 닫고 관련 자원을 반납 */
CLOSE v_emp_list;
RETURN v_total_count;
END ;;
DELIMITER ;
* 핸들러 사용
커서를 통해 읽다가 더이상 없으면 NOT FOUND 에러가 발생 하고
핸들러로 에서 no_more_date =1로 셋하고 continue 하기 때문에
REPEAT 문을 빠져나옴
*반드시 선언은 아래의 순서로 되어야 한다.
1.로컬변수와 컨디션
2.CURSOR
3.HANDLER
스토어드 프로그램의 권한과 옵션
DEFINDER = 소유자
SECUERTITY = DEFINDER, INVOKER
DEFINDER
해당 프로시저를 생성한 사람의 권한으로 실행
(호출자가 테이블에 권한이 없어도 돌아감)
IVCOKER
해당 프로시저를 호출 하는 자의 권한으로 실행
(호출자가 테이블에 권한 없으면 에러)
*정의 하지 않으면 SECUERTITY = DEFINDER 로 default로 설됨
스토어드 프로시저(함수) 권한
SUPER
생성, 실행 가능
CREATE ROUTINE
함수 생성 가능
ALTER ROUTINE
조회, 삭제 가능
EXECUTEE
실행 가능
*권한은 DB나 OBJECT 단위로 부여가능
DETERMINISTIC, NOT DETERMINISTIC
함수에 정의
DETERMINISTIC
입력값이 같은면 결과는 늘 같다.
NOT DETERMINISTIC
입력값이 같아도 시간에 따라 결과는 다르다.
DEFAULT NOT DETERMINISTIC
* 디펄트가 늘 변함 임으로 속도가 느려질수 있다 꼭 구분해서 사용하자
DETERMINISTIC
쿼리가 실행되면 그때 한번 호출하고 그 쿼리 안에서 상수값을 돌려줌
다시 쿼리가 호출되면 다시 실행됨
스토어드 프로그램에서 문자 깨지는 오류가 생기면
P700확인
사용자 변수
1.세션단위로 생김
2.로컬변수보다 느리다.
프리페어문이 아니라면 로컬 변수를 쓰자
*3.사용하기전에 초기화 해주자
세션 변수임으로 덮어 쓸 수 있다
4.타입이 없어서 위험 할수있다.
로컬변수
1.프로그램 실행시 생성
재귀호출 설정
max_sp_recursing_depth = 0 으로 되어있음 재귀 호출 쓸수 없다.
중첩커서 쓸때 블락안에서 블락을 만들어서 처리하자 그게 더 깔금하다
