07-1 스토어드 프로시저 사용 방법

• 스토어드 프로시저 : SQL+프로그래밍

  # 스토어드 프로시저 생성
  DELIMITER $$
  CREATE PROCEDURE 프로시저명()
  BEGIN
  	코드
  END $$
  DELIMITER ;
    
  CALL 프로시저명();
  
  # 스토어드 프로시저 삭제
  DROP PROCEDURE 프로시저명;
  

• 매개변수의 사용 : 입력,출력 매개변수 지정가능

  # 입력 매개변수 지정
  IN 매개변수명 데이터형식
  CALL 프로시저명(전달값);
  
  # 출력 매개변수 지정
  OUT 매개변수명 데이터형식
  CALL 프로시저명(@변수명);
  SELECT @변수명;
  

  # 입력
  DELIMITER $$
  CREATE PROCEDURE user_proc(
  	IN user_num INT,
    IN user_height INT
  )
  BEGIN
  	SELECT * FROM member WHERE mem_number < user_num and height > user_height;
  END $$
  DELIMITER ;
    
  CALL user_proc(6, 163);
  
  # 출력
  USE market_db;
  DROP PROCEDURE IF EXISTS user_proc;
  
  CREATE TABLE noTable(
  	id INT AUTO_INCREMENT PRIMARY KEY,
    txt CHAR(10));
      
  DELIMITER $$
  CREATE PROCEDURE user_proc(
  	IN txt CHAR(10),
    OUT outvalue INT
  )
  BEGIN
  	INSERT INTO noTable VALUES(NULL, txt);
  	SELECT MAX(id) INTO outvalue FROM noTable;
  END $$
  DELIMITER ;
    
  CALL user_proc('테스트', @myvalue);
  SELECT CONCAT('입력된 값의 ID -> ', @myvalue);
  

• 동적 SQL : PREPARE문과 EXECUTE문을 사용해 다이나믹하게 SQL 생성, 변경 후 실행

  DELIMITER $$
  CREATE PROCEDURE user_proc(
  	  IN table_name VARCHAR(10)
  )
  BEGIN
  	SET @sqlquery = CONCAT('SELECT * FROM ', tablename);
  	PREPARE myquery FROM @sqlquery;
  	EXECUTE myquery;
  	DEALLOCATE PREPARE myquery;
  END $$
  DELIMITER ;
    
  CALL user_proc('member');
  

07-2 스토어드 함수와 커서

• 스토어드 함수 : 사용자가 직접 만들어서 사용하는 함수. 반드시 하나의 returns값을 가진다.

• 매개변수는 전부 입력 매개변수이다.

• 스토어드 프로시저와 다르게 함수 안에서 SELECT문을 사용할 수 없다.

• 스토어드 프로시저는 CALL로 호출하지만 스토어드 함수는 SELECT문 안에서 호출한다.

  DELIMITER $$
  CREATE FUNCTION 스토어드함수명(매개변수)
  	RETURNS 반환형식
  BEGIN
  	코드;
  	RETURN 반환값
  END $$
  DELIMITER ;
  
  SELECT 함수명();
  
  # 함수값을 변수에 저장 후 사용
  SELECT calcfunc(2000) INTO @debut2000;
  SELECT calcfunc(2003) INTO @debut2003;
  SELECT @debut2000 -  @debut2003 AS '결과';
  

• 커서 : 행 단위로 한 행씩 접근해서 처리한다. [작동방식] 커서 선언 → 반복조건 선언 → 커서 열기 → 반복(데이터 가져오기 → 데이터 처리) → 커서 닫기

  • 커서 선언

    DECLARE 커서명 CURCOR FOR 
    	SELECT 열이름 FROM 테이블명;
    

  • 반복조건 선언

    DECLARE CONTINUE HANDLER
    	FOR NOT FOUND SET endOfRow = TRUE;
    

  • 커서 열기

    OPEN 커서명;
    

  • 반복

    반복루프명 : LOOP
    	FETCH 커서명 INTO 변수명
    	반복 코드
    	-> 빠져나갈 조건이 필수
    	IF endOfRow THEN
    		LEAVE 반복루프명;
    	END IF;
    END LOOP 반복루프명
    

  • 커서 닫기

    CLOSE 커서명;
    

07-3 자동으로 실행되는 트리거

• 트리거 : 테이블에서 DML이벤트가 발생할 떄 작동하는 프로그램 코드. 테이블에 입력, 수정, 삭제되는 정보들을 백업하는 용도로 사용

• 스토어드 프로시저와 문법이 유사하지만 CALL문으로 실행 불가.

• IN, OUT 매개변수 사용불가

• TRUNCATE문(모든 행 데이터 삭제)은 DELETE 트리거가 작동하지 않음.

  DELIMITER $$
  CREATE TRIGGER 트리거명
  	ALTER INSERT/UPDATE/DELETE
  	ON 테이블명
  	FOR EACH ROW
  BEGIN
  	트리거가 눌리면 작동되는 코드;
  END $$
  DELIMETER ;