Stored Procedure

Transact-SQL문의 집합

장점

1. 서버/클라이언트 네트워크 트래픽 감소

• 이는 프로시저를 실행하기 위한 호출만 네트워크를 통해서 전송되기 때문에
• 만약 프로시저로 캡슐화를 하지 않으면 모든 코드의 개별 라인이 네트워크를 통과해야된다.

2. 강한 보안

• 여러 사용자와 클라이언트 프로그램은 사용자와 프로그램이 해당 기본 개체에 대한 직접적인 사용 권한을 가지고 있지 않더라도 프로시저를 통해 기본 데이터베이스 개체에 대한 작업을 수행할 수 있다.
• 이 절차는 수행되는 프로세스와 활동을 제어하고 기본 데이터베이스 개체를 보호한다. 따라서 개별 개체 수준에서 사용 권한을 부여할 필요가 없어지고 보안 계층이 단순화됨
• 프로시저 파라미터를 사용하면 SQL 주입 공격으로부터 보호할 수 있다. 매개변수 입력은 실행 코드가 아닌 리터럴 값으로 취급되기 때문에 공격자가 절차 내부의 Transact-SQL 문에 명령을 삽입하여 보안을 손상시키는 것이 더 어렵다.
• 프로시저는 암호화가 가능해서 소스 코드를 난독화할 수 있다.

3. 코드 재사용

4. 손쉬운 유지 관리

클라이언트 애플리케이션이 절차를 호출하고 데이터베이스 운영을 데이터 계층에 유지할 때, 기본 데이터베이스의 변경에 대해 프로시저만 업데이트해야 한다. 애플리케이션 계층은 별도로 유지되며 데이터베이스 레이아웃, 관계 또는 프로세스의 변경에 대해 알 필요가 없다.

5. 성능 향상

• 기본적으로 프로시저는 처음 실행될 때 컴파일하고 후속 실행 시 재사용되는 실행 계획(execution)을 생성한다.

저장 프로시저의 유형

Temporary Stored Procedure

두가지 종류 local, global
local은 #을 사용
global은 ##을 사용

System Stored Procedure

• 중요 Database Engine(SQL Server Database Engine의 일반적인 유지 관리에 사용됨)

exec sp_databases --이런식으로 사용해볼 수 있음

Extended User-Defined Stored Procedure

C와 같은 프로그래밍 언어로 외부 루틴을 만들 수 있음

User-Defined Stored Procedure

사용자 지정 절차는 사용자가 지정한 데이터베이스 또는 자원 데이터베이스를 제외한 모든 시스템 데이터베이스에 작성될 수 있다.

프로시저 만들기

syntax

CREATE [ OR ALTER ] { PROC | PROCEDURE }
[schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

 <procedure_option> ::=
 [ ENCRYPTION ]
 [ RECOMPILE ]
 [ EXECUTE AS Clause ]

in,out이랑 같이 사용해보기(중요)

create procedure uspSumProduct @p1 int,
                               @p2 int,
                               @p3 int,
                               @sum int out,
                               @product int out
as
begin
set @sum = @p1 + @p2 + @p3
set @product = @p1 * @p2 * @p3
end
go 
------------
declare @v1 int, @v2 int;

exec uspSumProduct 3,5,7, @v1 out,@v2 out
print @v2
print @v1

Best Practices

Best Practices

• Use the SET NOCOUNT ON statement as the first statement in the body of the procedure.
• Use schema names when creating or referencing database objects in the procedure.
• Avoid wrapping functions around columns specified in the WHERE and JOIN clauses.
• Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.
• Avoid the use of SELECT *. Instead, specify the required column names.
• Avoid processing or returning too much data. Narrow the results as early as possible in the procedure code
• Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible.
• Use the Transact-SQL TRY...CATCH feature for error handling inside a procedure.
• Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQL statements in the body of the procedure. This prevents passing NULL to columns that do not allow null values.
• Use NULL or NOT NULL for each column in a temporary table.
• Use modification statements that convert nulls and include logic that eliminates rows with null values from queries.
• Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values.

• SET NOCAUNT ON 문을 절차 본문의 첫 번째 문장으로 사용한다.
• 절차에서 데이터베이스 개체를 만들거나 참조할 때 스키마 이름을 사용하십시오.
• WHERE 및 JOIN 절에 지정된 열 주위에 함수를 감지 마십시오.
• 많은 데이터 행을 반환하는 SELECT 문에서 스칼라 함수를 사용하지 마십시오. 스칼라 함수를 모든 행에 적용해야 하기 때문에 결과 동작은 행 기반 처리와 같으며 성능을 저하시킨다.
• SELECT *를 사용하지 마십시오. 대신 필요한 열 이름을 지정하십시오.
• 너무 많은 데이터를 처리하거나 반환하지 마십시오. 가능한 빨리 절차 코드에서 결과의 범위를 좁히십시오.
• BEGIN/COMIT TRANCE를 사용하여 명시적 트랜잭션을 사용하고 가능한 한 짧게 유지하십시오.
• Transact-SQL TRY 사용...절차 내에서 오류 처리를 위한 CATCH 기능.
• 절차 본문의 CREATE TABLE 또는 ALTER TABLE Transact-SQL 문장이 참조하는 모든 테이블 열에 기본 키워드를 사용하십시오. 이렇게 하면 NULL 값을 허용하지 않는 열에 NULL이 전달되지 않는다.
• 임시 테이블의 각 열에 NULL 또는 NOT NULL을 사용하십시오.
• null을 변환하고 null 값이 있는 행을 쿼리에서 제거하는 논리를 포함하는 수정문을 사용하십시오.
• 구별되는 값에 대한 특별한 필요성이 없는 한 유니온 또는 수술실 운영자 대신 유니온 올 오퍼레이터를 사용하십시오.

Limitations and Restrictions

<중요>
A procedure can reference tables that do not yet exist. At creation time, only syntax checking is performed. The procedure is not compiled until it is executed for the first time. Only during compilation are all objects referenced in the procedure resolved.
절차는 아직 존재하지 않는 표를 참조할 수 있다. 생성 시 구문 확인만 수행한다. 이 절차는 처음 실행될 때까지 컴파일되지 않는다. 컴파일 중에만 모든 객체가 해결된 절차에서 참조된다.