SQL, 저장 프로시저(Stored Procedure)
저장 프로시저(Stored Procedure)란,
SQL Server에서 제공되는 프로그래밍 기능으로,
어떠한 동작을 일괄 처리하기 위한 용도로 사용하는 쿼리문의 집합
프로시저의 생성은 CREATE, 수정은ALTER, 삭제는 DROP, 사용은 EXEC를 사용한다.
CREATE PROCEDURE procedure_name
AS
실행문;
GO
EXEC procedure_name;
※ 매개변수의 사용
//정의
@입력매개변수 dataType [=default 값]
@출력매개변수 dataType OUTPUT
//실행
EXECUTE procedure_name[전달 값]
EXECUTE procedure_name @변수명 OUTPUT
RETURN문을 사용해서 성공 실패 여부를 확인할 수 있다.
저장 프로시저 내 오류처리는 @@ERROR 함수 및 TRY/CATCH문을 이용하여 가능하다.
임시 저장 프로시저를 사용할 때에는 # 또는 ## 을 붙인 형태로 사용하며 TEMPDB에 저장된다.
사용 예)
CREATE PROCEDURE usp_users2
@userBirth INT,
@userHeight INT
AS
SELECT * FROM userTbl WHERE birthYear > @userBirth AND height > @userHeight;
GO
EXEC usp_users2 1970, 178;
저장 프로시저의 특징
- SQL Server의 성능을 향상시킬 수 있다.
동일한 저장 프로시저가 자주 사용될 경우에는 일반 쿼리를 반복해서 실행하는 것 보다 SQL Server의 성능이 크게 향상될 수 있다.
- 모듈식 프로그래밍이 가능하다.
저장 프로시저를 생성해 놓으면, 언제든지 실행이 가능하다.
- 보안을 강화할 수 있다.
사용자 별로 테이블에 접근 권한을 주지 않고, 저장 프로시저에 접근 권한을 줌으로써 좀 더 보안을 강화한다.
- 네트워크 전송량을 감소시킨다.
저장 프로시저 이름 및 매개변수 등 몇 글자의 텍스트만 전송하면 되기 때문에 네트워크 부하를 줄일 수 있다.
저장 프로시저는 사용자 정의 프로시저와 확장 저장 프로시저로 크게 나눠볼 수 있다.
우리가 일반적으로 사용하는 사용자 정의 프로시저는 T-SQL 프로시저 이다.
※ 시스템 저장 프로시저란,
시스템을 관리하기 위해서 SQL Server가 제공해주는 저장 프로시저로, SQL Server의 관리와 관련된 작업을 위해서 주로 사용한다.
주로 'sp_' 접두어를 사용하여 작성되어있다.
저장 프로시저의 작동 방식
저장 프로시저 1회 실행 시 작동은 다음과 같이 이루어진다.
만약 동일한 프로시저를 다시 실행시키면 메모리에 남아있던 실행계획을 확인하여 바로 실행하게 되어 소요시간이 훨씬 짧아진다.
WITH RECOMPILE 옵션과 문제점
※ 저장 프로시저의 문제점과 해결책
처음 수행 시 최적화가 이루어지기 때문에, 두 번째 수행 시에는 그 최적화가 더 안 좋은 성능을 내더라도 이미 컴파일 된 저장 프로시저를 계속 사용하게 된다.
따라서 이에 대한 해결책으로는 재컴파일하는 방법이 있다.
※ 재 컴파일 방법
실행 시에 WITH RECOMPILE 옵션을 사용
실행 시마다 자동으로 다시 컴파일 되도록 프로시저를 생성
'sp_recompile 테이블_이름' 시스템 저장 프로시저를 사용한다.
'DBCC FREEPROCCACHE'를 사용한다.
CREATE PROC usp_id
@id INT
WITH RECOMPILE
AS
SELECT * FROM spTbl WHRER CustomerID < @id;
GO
사용자 정의 함수
저장 프로시저와 조금 비슷해보이지만, 일반적인 프로그래밍 언어에서 사용되는 함수와 같이 복잡한 프로그래밍이 가능
함수는 RETURN 문에 의해서 특정 값을 반환함.
저장 프로시저는 EXEC에 의해서 실행되지만, 함수는 주로 SELECT문에 포함되어 실행됨 (예외도 있음)
CREATE FUNCTION ufn_getAge(@byear INT) -- 매개 변수를 정수로 받음
RETURNS INT -- 리턴값은 정수형
AS
BEGIN
DECLARE @age INT
SET @age = YEAR(GETDATE()) - @byear
RETURN(@age)
END
GO
SELECT dbo.ufn_getAge(1979); -- 호출 시 스키마명을 붙여줘야 함
※ 함수의 종류
기본 제공 함수
: SQL Server가 제공해주는 시스템 함수
사용자 정의 스칼라 함수
: Return문에 의해서 하나의 값을 돌려주는 함수
사용자 정의 테이블 반환 함수
: 리턴하는 값이 테이블인 함수
1) 인라인 테이블 함수 - 간단한 테이블을 리턴하는 함수로 뷰(view)와 비슷
CREATE FUNCTION funcName(매개변수)
RETURNS TABLE
AS
RETURN (단일 SELECT문;)
2) 다중문 테이블 함수 - BEGIN~END로 정의되며 내부에 일련의 T-SQL을 이용해서 반환될 테이블에 행 값을 INSERT하는 형식
CREATE FUNCTION funcName(매개변수)
RETURNS @TABLE_variable TABLE
(column dataType 정의)
AS
BEGIN
헤더에서 정의한 테이블에 행을 INSERT하는 SQL문.. ;
RETURN;
END
스키마 바운드 함수
: 함수에서 참조하는 테이블, 뷰 등이 수정되지 못하도록 설정한 함수
스키마 바운드 함수 생성은 옵션에 'WITH SCHEMABINDING'을 사용한다.
테이블 변수
: 일반적인 변수의 선언처럼 테이블 변수도 선언해서 사용 가능
테이블 변수의 용도는 주로 임시테이블의 용도와 비슷하다
사용자 정의 함수의 제약사항
: 사용자 정의 함수 내부에 TRY~CATCH문을 사용할 수 없다.
사용자 정의 함수 내부에 CREATE/ALTER/DROP은 사용할 수 없다.
오류가 발생하면 즉시 함수의 실행이 멈추고 값을 반환하지 않는다.
참고
: https://blog.naver.com/leejjoo112/222138998780
SQL Stored Procedures for SQL Server
What is a Stored Procedure?
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
Stored Procedure Syntax
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
Execute a Stored Procedure
EXEC procedure_name;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
| 1 |
Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 |
Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
Stored Procedure Example
The following SQL statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table:
Example
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllCustomers;
Stored Procedure With One Parameter
The following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table:
Example
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllCustomers @City = 'London';
Stored Procedure With Multiple Parameters
Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.
The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table:
Example
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';
참고