TIL_저장 프로시저_241123

💡 저장 프로시저(Stored Procedure)

일련의 SQL문장을 선언해서 MySQL에 저장하고, 해당 SQL문을 함수처럼 사용하는 것으로 만들어 두기만 하면 함수처럼 호출하여 편하게 사용할 수 있다.

사용 이유

• 저장 프로시저는 사용자들에게 데이터에 대한 제한적인 접근을 허용케하는 전통적인 수단이다.
• 쿼리 분석기와 같은 도구를 이용할 경우 원하는 어떠한 작업도 수행가능하므로, 사용자들은 SELECT, INSERT, UPDATE 혹은 DELETE 같은 문장을 직접 실행할 수 있는 권한을 가져서는 안된다.
• 성능을 향상시키기 위해서이다. 저장 프로시저가 최초로 실행 되면, SQL 서버는 해당 프로시저에 대한 실행계획을 생성, 이 실행 계획이 캐쉬에 저장된다.
• 50줄 이상의 복잡한 Select 문장이 있으며, 각 실행시 마다 Where 절 조건문이 조금씩 변한다고 가정해 보자. 이 문장을 저장 프로시저에 넣을 경우, 네트워크를 통해 전달되는 데이터 소통량이 상당히 감소하게 되며, 해당 프로시저가 자주 실행될 수록 성능향상 효과가 증대된다.
• 단일 행을 반환하는 SQL 문장을 실행시키고 싶을 경우에, SQL 문만 사용 하면 결과 집합을 레코드셋으로 반환 받아야 한다. 하지만, 저장 프로시저를 사용할 경우에는 성능이 월등한 출력매개변수의 사용이 가능하다.
• 단일 쿼리에 대한 실행시간의 차이는 무시할 수 있겠지만, 신규 사용자 등록 같은 단순 insert 작업을 SQL 서버에 수만번 해야한다면, 결과 집합으로 값을 받는 것에 비해 @key를 출력매개변수로 반환하는 경우의 이점은 엄청나게 커지게 된다.

기본 문법

• 생성
  • 파라미터 선언은 프로시저명() 안에서 선언하고 SQL문과 변수 선언은 BEGIN~END 사이에 작성
  • SELECT 사용 시에는 조회한 컬럼(데이터)을 반드시 INTO로 변수 안에 넣어줘야 함
  • 프로시저 내부에서 사용하는 SQL문은 일반 SQL문 이기 때문에 세미콜론(;)으로 문장을 끝내야함
  📌 DELIMITER
• 프로시저 작성이 완료되지 않았음에도 SQL문이 실행되는 것을 막기 위해 사용
• $$ DELIMITER; CREATE PROCEDURE `proc_user_insert`( -- 파라미터 선언 param_id varchar(255) param_name varchar(255) param_campus varchar(255) param_class varchar(255) param_gi varchar(255) ) BEGIN -- 변수 선언 (현재는 없음) -- 쿼리문 insert into ssafy_user(id, name, campus, class, gi) values(param_id , param_name, param_campus, param_class, param_gi); END $$ DELIMITER;
• 호출
  • CALL 프로시저명(파라미터); 로 손쉽게 사용
• CALL proc_user_insert('1', '2', '3', '4', '5');