이 문서는 MySQL 저장 프로시저에 대한 기본적인 설명을 제공합니다.
개요
일반적으로 SQL 문은 실행되기 전에 컴파일되어야 하며, 이후 실행됩니다. 반면 저장 프로시저는 특정 작업을 수행하기 위한 SQL 문들의 집합으로, 미리 컴파일되어 데이터베이스 내에 저장됩니다. 사용자는 저장 프로시저의 이름과 필요한 매개변수를 지정하여 이를 호출하고 실행할 수 있습니다.
저장 프로시저는 재사용 가능한 함수로, 데이터베이스 내부에 생성되고 저장됩니다. 이는 SQL 문과 특수 제어 구조들을 포함할 수 있으며, 동일한 기능을 다양한 애플리케이션이나 플랫폼에서 필요할 때 유용합니다. 데이터베이스 내의 저장 프로시저는 객체지향 프로그래밍의 개념을 시뮬레이션하며, 데이터 접근 방식을 제어할 수 있습니다. MySQL 5.0 이전 버전에서는 저장 프로시저를 지원하지 않았습니다.
저장 프로시저 생성
1. 종결자 변경
SQL 문은 세미콜론(;)으로 끝나기 때문에, 저장 프로시저를 생성하기 위해 먼저 종결자를 변경해야 합니다:
delimiter // select * from tablename //
위 코드는 종결자를 "//"로 변경합니다.
2. 저장 프로시저 생성
이제 저장 프로시저를 생성할 수 있습니다:
create procedure getUserInfo (out userName varchar(255))reads sql databeginselect name into userName from userinfo where id=1; end//
여기서 procedure는 프로시저를 의미하며, getUserInfo는 저장 프로시저의 이름입니다. 괄호 안의 내용은 매개변수 정의이며, out은 출력 매개변수, in은 입력 매개변수를 나타냅니다.
MySQL 저장 프로시저의 매개변수는 다음과 같은 세 가지 유형이 있습니다: IN, OUT, INOUT. 형식은 다음과 같습니다: CREATE PROCEDURE([[IN |OUT |INOUT ] 매개변수명 데이터타입...])
- IN 입력 매개변수: 호출 시 반드시 값을 지정해야 하며, 저장 프로시저 내에서 변경된 값은 반환되지 않습니다. 기본값입니다.
- OUT 출력 매개변수: 저장 프로시저 내에서 변경 가능하며 반환됩니다.
- INOUT 입력/출력 매개변수: 호출 시 지정되며, 변경 및 반환 모두 가능합니다.
userName은 매개변수 이름이고 varchar(255)는 데이터 타입입니다. 사용 가능한 데이터 타입에는 int, float, date, varchar(length) 등이 있습니다.
reads sql data는 저장 프로시저가 데이터를 읽기 위해 SQL을 사용한다는 것을 나타냅니다. begin과 end는 시작과 끝을 의미합니다.
3. 저장 프로시저 호출
call getUserInfo(@result)//select @result//
첫 번째 명령어를 실행하면 성공 메시지를 확인할 수 있으며, 두 번째 명령어를 실행하면 결과를 얻을 수 있습니다. 여기서 @result는 세션 변수로, MySQL에서는 선언 없이 바로 사용할 수 있으며, 전체 세션 동안 유효합니다. 저장 프로시저를 호출하여 결과를 세션 변수에 저장하고, 해당 변수를 조회함으로써 결과를 확인할 수 있습니다.
4. 저장 프로시저 조회
- 데이터베이스 내의 모든 저장 프로시저 목록 조회:
select name from mysql.proc where db='database_name';
select routine_name from information_schema.routines where routine_schema='database_name';
show procedure status where db='database_name';
- 저장 프로시저 상세 정보 조회:
show create procedure database_name.procedure_name;
5. 저장 프로시저 수정
ALTER PROCEDURE
CREATE PROCEDURE로 생성된 저장 프로시저를 변경하며, 관련된 다른 프로시저나 기능에는 영향을 주지 않습니다.
6. 저장 프로시저 삭제
저장 프로시저 삭제는 테이블 삭제와 유사하게 간단합니다:
DROP PROCEDURE
MySQL 테이블에서 하나 이상의 저장 프로시저를 삭제합니다.