2013년 12월 29일 일요일

1_mysql 스토어 기본 문법

스토어드 프로그램 장단점
 장점
  데이터베이스 보안향상
   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 으로 되어있음 재귀 호출 쓸수 없다.

중첩커서 쓸때 블락안에서 블락을 만들어서 처리하자 그게 더 깔금하다