Microsoft SQL Server
저장 프로 시저

소개

통사론

• CREATE {PROCEDURE | PROC} [schema_name.] 프로 시저 이름
• [@parameter [type_schema_name.] 데이터 유형
• [VARYING] [= default] [OUT | 출력 | READONLY]
• , @parameter [type_schema_name.] 데이터 유형
• [VARYING] [= default] [OUT | 출력 | READONLY]]
• [WITH {암호화 | RECOMPILE | EXECUTE AS 조항}]
• [복제 용]
• 같이
• 시작
• [선언 _ 섹션]
• executable_section
• 종료;

기본 저장 프로 시저 만들기 및 실행

라이브러리 데이터베이스 에서 Authors 테이블 사용

CREATE PROCEDURE GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

몇 가지 다른 구문을 사용하여 프로 시저를 실행할 수 있습니다. 먼저 EXECUTE 또는 EXEC 사용할 수 있습니다.

EXECUTE GetName @id = 1
EXEC Getname @name = 'Ernest Hemingway'

또한 EXEC 명령을 생략 할 수 있습니다. 또한 모든 매개 변수를 전달할 때 전달할 매개 변수를 지정할 필요가 없습니다.

GetName NULL, 'Ernest Hemingway'

프로 시저에서 선언 된 것과 다른 순서로 입력 매개 변수를 지정하려면 매개 변수 이름을 지정하고 값을 지정할 수 있습니다. 예를 들어

 CREATE PROCEDURE dbo.sProcTemp 
 (
    @Param1 INT,
    @Param2 INT
)
AS
BEGIN

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

이 절차를 수행하는 일반적인 순서는 @ Param1 값을 먼저 지정한 다음 @ Param2 초 값을 지정하는 것입니다. 그러면 다음과 같이 보일 것입니다.

  EXEC dbo.sProcTemp @Param1 = 0,@Param2=1

그러나 다음을 사용할 수도 있습니다.

  EXEC dbo.sProcTemp @Param2 = 0,@Param1=1

이 경우, @ param2와 @ Param1의 값을 먼저 지정합니다. 즉, 절차에서 선언 된 것과 동일한 순서를 유지할 필요는 없지만 원하는대로 주문할 수 있습니다. 값을 설정하는 매개 변수를 지정해야합니다.

모든 데이터베이스에서 저장 프로 시저에 액세스

또한 접두사 sp_ 있는 프로 시저를 만들 수 있습니다. 모든 시스템 저장 프로 시저와 마찬가지로 이러한 procuedres는 SQL Server의 기본 동작 때문에 데이터베이스를 지정하지 않고 실행할 수 있습니다. "sp_"로 시작하는 저장 프로 시저를 실행하면 SQL Server는 먼저 master 데이터베이스에서 프로 시저를 찾습니다. 프로 시저가 master에서 발견되지 않으면 활성 데이터베이스를 찾습니다. 모든 데이터베이스에서 액세스하려는 저장 프로 시저가있는 경우 마스터로 저장 프로 시저를 만들고 "sp_"접두사가 포함 된 이름을 사용하십시오.

Use Master

CREATE PROCEDURE sp_GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

OUT 매개 변수가있는 저장 절차

저장 프로시 저는 매개 변수 목록에서 OUTPUT 키워드를 사용하여 값을 반환 할 수 있습니다.

단일 출력 매개 변수로 저장 프로 시저 만들기

CREATE PROCEDURE SprocWithOutParams
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam + ' must come out'   
    RETURN
END   
GO

저장 프로 시저 실행

DECLARE @OutParam VARCHAR(30)    
EXECUTE SprocWithOutParams 'what goes in', @OutParam OUTPUT   
PRINT @OutParam 

여러 개의 out 매개 변수를 사용하여 저장 프로 시저 만들기

CREATE PROCEDURE SprocWithOutParams2
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT,
    @OutParam2 VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam +' must come out'   
    SELECT @OutParam2 = @InParam +' must come out'
    RETURN
END    
GO

저장 프로 시저 실행

DECLARE @OutParam VARCHAR(30)    
DECLARE @OutParam2 VARCHAR(30)  
EXECUTE SprocWithOutParams2 'what goes in', @OutParam OUTPUT, @OutParam2 OUTPUT   
PRINT @OutParam 
PRINT @OutParam2

If ... Else 및 Insert into Operation으로 저장 프로 시저

예제 테이블 생성 Employee :

CREATE TABLE Employee
(
    Id INT,
    EmpName VARCHAR(25),
    EmpGender VARCHAR(6),
    EmpDeptId INT
)

저장 프로 시저에서 전달 된 값이 null이 아니거나 비어 있지 않고 Employee 테이블에서 삽입 작업을 수행하는지 여부를 확인하는 저장 프로 시저를 만듭니다.

CREATE PROCEDURE spSetEmployeeDetails
(
    @ID int,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT
)
AS
BEGIN
    IF (
        (@ID IS NOT NULL AND LEN(@ID) !=0) 
        AND (@Name IS NOT NULL AND LEN(@Name) !=0)
        AND (@Gender IS NOT NULL AND LEN(@Gender) !=0)
        AND (@DeptId IS NOT NULL AND LEN(@DeptId) !=0)
    )
    BEGIN
        INSERT INTO Employee
        (
            Id,
            EmpName,
            EmpGender,
            EmpDeptId
        )
        VALUES
        (
            @ID,
            @Name,
            @Gender,
            @DeptId
        )
    END
ELSE
    PRINT 'Incorrect Parameters'
END
GO

저장 프로 시저 실행

DECLARE @ID INT,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT

EXECUTE spSetEmployeeDetails
    @ID = 1,
    @Name = 'Subin Nepal',
    @Gender = 'Male',
    @DeptId = 182666 

내장 프로 시저의 동적 SQL

동적 SQL을 사용하면 런타임에 SQL 문을 생성하고 실행할 수 있습니다. 동적 SQL은 SQL 문에 다른 컴파일 시간에 변경 될 수있는 식별자가 포함되어있을 때 필요합니다.

동적 SQL의 간단한 예제 :

CREATE PROC sp_dynamicSQL
@table_name      NVARCHAR(20),
@col_name        NVARCHAR(20), 
@col_value       NVARCHAR(20) 
AS
BEGIN
DECLARE  @Query  NVARCHAR(max)
SET      @Query = 'SELECT * FROM ' + @table_name
SET      @Query = @Query + ' WHERE ' + @col_name + ' = ' + ''''+@col_value+''''
EXEC     (@Query)
END

위의 SQL 쿼리에서는 @table_name, @col_name, and @col_value 에 @table_name, @col_name, and @col_value 에 값을 정의하여 위 쿼리를 사용할 수 있음을 알 수 있습니다. 쿼리는 런타임에 생성되어 실행됩니다. 이것은 변수에서 문자열로 전체 스크립트를 생성하고 실행할 수있는 기법입니다. 동적 SQL 및 연결 개념을 사용하여보다 복잡한 쿼리를 생성 할 수 있습니다. 이 개념은 여러 조건에서 사용할 수있는 스크립트를 만들 때 매우 강력합니다.

저장 프로 시저 실행 중

DECLARE @table_name     NVARCHAR(20) = 'ITCompanyInNepal',
        @col_name       NVARCHAR(20) = 'Headquarter',
        @col_value      NVARCHAR(20) = 'USA'
    
EXEC    sp_dynamicSQL   @table_name,    
                        @col_name,
                        @col_value

사용 된 표

산출

단순 루핑

먼저 #systables 및 ad라는 임시 테이블에 데이터를 가져 #systables 한 번에 하나의 레코드를 쿼리 할 수 ​​있도록 행 번호를 증가시킵니다.

select
    o.name,
    row_number() over (order by o.name) as rn
into
    #systables
from
    sys.objects as o
where
    o.type = 'S'

다음은 루핑을 제어하고이 예제에서 테이블 이름을 저장하는 몇 가지 변수를 선언합니다.

declare
    @rn int = 1,
    @maxRn int = (
                    select
                        max(rn)
                    from
                        #systables as s
                    )
declare    @tablename sys name

이제 우리는 간단한 동안을 사용하여 반복 할 수 있습니다. select 문에서 @rn 을 증가 @rn ex set @rn = @rn + 1 대한 별도의 명령문 일 수도 있습니다. 이는 사용자의 요구 사항에 따라 달라집니다. 우리는 또한의 값 사용 @rn 가에서 단일 레코드 선택 증가되기 전에 #systables . 마지막으로 테이블 이름을 출력합니다.

while @rn <= @maxRn
    begin

        select
            @tablename = name,
            @rn = @rn + 1
        from
            #systables as s
        where
            s.rn = @rn

        print @tablename
    end

단순 루핑

CREATE PROCEDURE SprocWithSimpleLoop
(
    @SayThis VARCHAR(30),
    @ThisManyTimes INT
)
AS
BEGIN
    WHILE @ThisManyTimes > 0
    BEGIN
        PRINT @SayThis;
        SET @ThisManyTimes = @ThisManyTimes - 1;
    END
    
    RETURN;
END    
GO