MySQL - Stored Procedure

김법우·2021년 12월 10일
Databasemysql
0

Database

목록 보기
6/10
post-thumbnail

Stored Procedure

SQL 문장들을 여러개 모아 하나의 함수처럼 실행하기 위한 것.

Create Procedure 를 사용하여 새로운 Stored Procedure 를 기술한다.

Stored Procedure 의 정의

  1. MySQL Workbench 의 Schemas 도구를 통해 Create 화면으로 진입

  2. 작성 이후 Apply 를 누른다.

  • DELIMITER $$ 는 입력된 Procedure 의 시작임을 나타낸다.
  • DELIMETER; 는 입력된 Procedure 의 끝을 나타낸다.

Procedural 구문

기본적인 구문

DELIMITER $$
USE 'datamodel' $$
CREATE DEFINER='root'@'localhost' PROCEDURE 'getTest'()

BEGIN
	declare ratio int;
	set ratio = 15;
	if ratio = 10 then
		select * from s where status > 20 order by city desc;
	else
		select sname from s where status < 20;
	end if;
END$$

DELIMETER;
  1. declare 구문 을 통해 procedure 내부에서 사용할 지역 변수를 선언 할 수 있다.
  2. set 구문을 통해 declare 구문으로 선언한 지역 변수의 값을 할당 할 수 있다.
  3. if ~ else ~ end if 구문을 통해 조건에 따른 분기 처리가 가능하다.

함수 인자의 처리

Procedure 를 호출 할 때 

call getTest();

이러한 형태로 함수 호출을 수행하는데 그렇다면 procedure 의 input 인자는 어떻게 처리하는가?

DELIMITER $$
USE 'datamodel' $$
CREATE DEFINER='root'@'localhost' PROCEDURE 'getTest'(
	**in cname varchar(10),
	out sumStatus int**
)

BEGIN

	**select sum(status)
	into sumStatus
	from s
	where city=cname;**

END$$

DELIMETER;

-------------------------------------------

call getTest('London', @total);
select @total;
  1. procedure 명 뒤의 괄호안에 in, out 구문을 사용해 함수의 input 과 output 을 정의한다.
    1. in 변수명 자료형
    2. out 변수명 자료형
  2. select ... into ... from 구문을 통해 변수에 값을 assign 할 수 있따.
DELIMITER $$
USE 'datamodel' $$
CREATE DEFINER='root'@'localhost' PROCEDURE 'getTest'(
	in cname varchar(10),
	out sumStatus int
)

BEGIN
	declare amount int;
	set amount = 0;
	
	**case 
		when cname='London' then
			set amount=amount+10;
		when cname='London' then
			set amount=amount+10;
		when cname='London' then
			set amount=amount+10;
		when cname='London' then
			set amount=amount+10;
		else
			set amount=3;
	end case;**
	
	select sum(status)
	into sumStatus
	from s
	where status < amount;

END$$

DELIMETER;
  1. case 문은 if else ... endif 구문을 대신하여 사용한다.

예외처리

stored Procedure 가 실행되는 도중 발생하는 오류를 감지하고 해당 예외를 처리 할 수 있는 방법이 필요하다.

**declare handler_action handler for condition_value [,condition_value]
	... statement**
  • handler_action 에 가능한 구문
    • continue
      • begin ... end block 을 실행하고 stored procedure 를 실행한다.
    • exist
      • begin ... end block 을 실행하고 stored procdure 를 실행하지 않고 빠져 나온다.
  • condition_value 에 가능한 구문
    • mysql-error_code
      • sql error code 를 기재한다.
    • SQLSTATE [value] sqlstate_value
      • 5자리 문자열로 기쉴하낟.
    • condition_name
    • SQLWARNING
      • '01' 로 시작, error 가 아닌 경고를 내보냄
    • NOT found
    • SQLEXCEPTION
DELIMITER $$
USE 'datamodel' $$
CREATE DEFINER='root'@'localhost' PROCEDURE 'exception_handle'(
	**in tbl varchar(10)**
)

BEGIN

	declare continue handler for 1146
		select 'NO table of', tbl as 'error message';
		select * from tbl;

END$$

DELIMETER;

동적 SQL

동적 SQL 이란?

SQL 문을 prepare 문으로 저장한 뒤 execute 로 실행하는 것을 동적 SQL 이라고 한다.

주의 !

  1. declare 문으로 변수 선언시 @를 사용해서는 안된다.
  2. set @stmt='select * from emp'; 에서는 @를 사용해야 한다.
  3. prepare st from @stmt; 에서는 변수명에 @를 사용해야 한다.
CREATE DEFINER='root'@'localhost' PROCEDURE 'simplePrepare'()
BEGIN

1. 	declare stmt varchar(40);
2. 	set @stmp = 'select * from emp'; 
3. 	prepare st from @stmp;
4.  execute st;
5. 	deallocate prepare st;

END
  1. 동적 구문을 적용할 변수명을 declare 를 통해 선언하는데, 이때는 @를 사용하지 않는다.
  2. 동적 구문을 적용한다.
  3. 동적 구문을 prepare 문으로 st 로 저장한다.
  4. 저장한 st 를 excute 문으로 실행한다.
  5. 마지막으로 저장햇던 st 를 제거한다.

테이블명 동적 SQL

CREATE DEFINER='root'@'localhost' PROCEDURE 'simplePrepare'(
	in tbl varchar(10), 
	in val int
)
BEGIN

1. 	declare stmt varchar(40);
2.  declare num int;
3. 	set @stmp = concat('select * from ', tbl, ' where qty < ?');
4.  set @num = val;
5.  select @stmt as 'statement';  
6. 	prepare st from @stmp;
7.  execute st using @num;
8. 	deallocate prepare st;

END

  1. concat 을 통해 procedure 의 in 으로 받은 tbl(테이블명)을 전달 한다. tbl 뒤에 이어진 where 문에 동적으로 적용하고자 하는 비교 값을 ? 로 정의

  2. excute 를 @num 을 통해 수행함으로서 where 절 물음표에 num 의 값을 삽입 후 실행

Stored Function

Stored Function 이란?

MySQL 에서 제공하는 함수 이외에 사용자 정의 함수를 만들어 사용 할 수 있다.

Stored Function 의 정의

  1. MySQL Workbench Functions 에서 생성

Stored Function 구문

CREATE FUNCTION `new_function` (
1. money int
) 
2. RETURNS INTEGER

BEGIN
	3. declare bl varchar(10);
	4. if money < 20 then set bl = 'SLiver';
	5. elseif money < 40 then set bl = 'Gold';
	6. else set bl = 'Platinum';
	end if;
7. RETURN bl;

END
  1. 함수의 인자를 {변수명 자료형} 형태로 정의
  2. 함수의 반환값의 타입을 정의
  3. declare 구문을 통해 지역 변수 선언
  4. if ... else if ... else ... end if 구문을 통해 지역 변수에 값 할당
  5. ...
  6. ...
  7. 타입에 맞는 값 반환.

Stored Function 을 Stored Procedure 에서 사용하기

CREATE PROCEDURE
1. 'getBugdetLevel'(
2. 	in deptName varchar(4),
3. 	out deptLevel varchar(10)
)

BEGIN

	4. declare dmoney int;
	
  5. 
	select budget 
	into dmoney
	from dept
	where dname=deptName;
	
	6. 
	select **bLevel(**dmoney**)**;
	into deptLevel;

END
  1. procedure 명 선언
  2. procedure 의 인자로 in 을 통해 input 을 정의
  3. procedure 의 출력으로 out 을 통해 output 정의
  4. procedure 내 지역변수 dmoney
  5. select ... into ... 구문을 통해 dmoney 에 select 결과 저장.
  6. select ... into ... 구문을 통해 deptLevel 에 bLevel( ) → Stored Function 결과 저장.

Cursor (잘모르겟다 ..)

cursor 는 테이블의 각 row 를 하나씩 접근하는 방법을 제공한다.

(테이블 각 레코드 접근하는 것을 cursor)

  • cursor 가 가리키는 select 문은 open 으로 실행되고 cursor 는 이를 가리킨다.

    • cursor 는 select 문을 값으로 갖고 있다.

    • 테이블 결과의 각 row 를 가리키는 pointer 역할을 한다.

      → Struct 로 간주.

  • Fetch 문은 open 으로 실행된 결과 set 의 각 row 를 fetch 할 때 마다 다음 레코드를 가져온다.
profile
김법우
개발을 사랑하는 개발자. 끝없이 꼬리를 물며 답하고 찾는 과정에서 공부하는 개발자 입니다. 잘못된 내용 혹은 더해주시고 싶은 이야기가 있다면 부디 가르침을 주세요!
이전 포스트

MySQL - Views

다음 포스트

MySQL - Functional Dependency, Normal Form

0개의 댓글