[MySQL_MariaDB] 스토어드프로시저_변수_if_case_while_에러처리_동적쿼리

* 스토어드 프로시저

> SQL을 하나로 묶어 편리하게 사용하는 기능. SQL을 묶는 기능 외에도 프로그래밍 언어와 같은 기능을 담당할 수도 있다.

> 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용된다.

> DB서버로 긴 쿼리의 텍스트 문장을 전송하는 것이 아닌, 프로시저의 이름 및 매개변수 등 몇 글자의 텍스트만 전송하면 되므로 네트워크의 부하를 줄일 수 있다.(결과적으로 MySQL의 성능향상). 다른 DBMS에서는 스토어드 프로시저가 처음 호출 시 1회만 컴파일되고 메모리에 적재되어 사용하나, MySQL 프로시저는 호출할 때마다 컴파일됨.

> 사용자테이블에 접근하려면 당연히 userTBL에 접근해야하지만, 테이블 정보없이 프로시저를 보내면 보안에 강할 수 있다.

 

매번 회원 테이블 당탕이의 정보와 제품 테이블의 냉장고의 정보를 동시에 조회한다고 가정해보자. 위처럼 매번 2번의 select문을 거쳐야 한다.

 

DELIMITER : 구분문자. 뒤에 //가 나오면 기존의 세미콜론(;)을 //로 대신한다. 이는 CREATE PROCEDURE ~~ END까지를 하나의 단락으로 묶어주는 효과를 갖는다. 그리고 제일 마지막 행에서 세미콜론(한칸띄고 ;)으로 돌려놓아야 한다.

* 단락 묶기 기호 : //, $$, &&, @@ 등 다른 것을 사용해도 된다. 또한 2개 연속이 아니라 하나만 사용해도 되지만 가능하면 다른 기호와 중복되지 않도록 2개를 연속해서 사용하는 것이 좋다.

 

삭제

수정은 alter procedure를 통해 수정 가능. 하지만 함수의 내용을 바꿀 수는 없다. 내용을 바꾸려면 drop 시킨 후에 다시 create 해야한다.

 

5. @ : 전역변수.

9. declare : 지역변수.

 

12. 셀렉트한 컬럼을 지역변수에 대입.

16,17. 오늘날짜, 날짜 차이도 지역변수에 저장이 가능함.

프로시저 내 if,else

 

프로시저 내 case

10, 14. set을 통해 지역변수에 값대입

 

case문과 if문은 select 문에서도 종종 사용된다.

 

 

* while : 1~3까지의 합(기본적인 while형태)

 

* while : 1~100까지의 합을 구하되 7의 배수는 제외, 1000이 넘으면 그만

- iterate = continue, leave = break

 

* 오류처리

9. DECLARE 액션 HANDLER FOR 오류조건 처리할_문장;

- 액션 : 오류 발생시 행동(CONTINUE와 EXIT 중 하나 사용)

 > CONTINUE : 제일 뒤의 ‘처리할_문장’ 부분이 실행된다.

- 오류조건 : 어떤 오류를 처리할 것인가를 쓴다. 오류코드 숫자가 오거나, SQLSTATE 상태코드, SQLEXCEPTION, SQLWARNING, NOT FOUND 등이 올 수 있다.

- 처리할_문장 : 처리할 문장이 하나라면 한 문장이 오며, 여러 개일 경우에는 BEGIN…END로 묶어줄 수 있다.

 

기본키 오류시 롤백 및 에러메시지 처리

8. continue : 9~13 처리 후 아래 쿼리문 실행

 

오버플로 발생시 에러처리

60. exit : 61~64 begin~end 구문처리 후 바로 종료

 

* 동적쿼리

3. prepare : select 문을 바로 실행하지 않고 myQuery에 입력

4. excute : prepare에 저장한 SQL을 실행 using을 통해 값을 전달하여 사용 가능. > 저장 후 필요에 의해 실행 > 동적SQL

5. 실행 후 deallocate prepare를 통해 해제해준다.

 

Using을 활용하여 쿼리를 실행하는 순간 값전달이 가능하다.

 

매개변수가 있는 프로시저

* 매개변수로 MySQL에서는 테이블 이름을 직접 파라미터로 던질 수 없음.

 

매개변수가 2개인 프로시저

 

34. out : 리턴값

37. 프로시저 생성시 testtbl테이블은 없었으나 생성됨. 실행만 안하면 에러는 없음.

47. @전역변수에 결과값을 담고.

48. @전역변수 셀렉트

 

현재 저장된 프로시저의 이름 및 내용을 조회할 수 있다. information_schema 데이터 베이스의ROUTINES 테이블에서. 프로시저의 내용은 확인되지만, 파라미터는 보이지 않는다.

 

프로시저 조회

 

프로시저 조회

 

1~11. 테이블 명을 파라미터로 받으면 mysql은 인식못함 에러남

14~27. 테이블명을 파라미터로 받아 실행시키려면, 동적쿼리(쿼리를 변수에 저장하여) 실행시키는 방법을 활용해야한다.

21. prepare : select 문을 바로 실행하지 않고 myQuery에 입력

22. excute : prepare에 저장한 SQL을 실행 using을 통해 값을 전달하여 사용 가능. > 저장 후 필요에 의해 실행 > 동적SQL

23. 실행 후 deallocate prepare를 통해 해제해준다.