Microsoft SQL Server
Procedury przechowywane

Wprowadzenie

Składnia

• UTWÓRZ {PROCEDURA | PROC} [nazwa schematu.] Nazwa procedury
• [@parameter [typ_schema_name.] typ danych
• [VARYING] [= domyślnie] [OUT | WYDAJNOŚĆ | TYLKO CZYTAĆ ]
• , @parameter [typ_schema_name.] typ danych
• [VARYING] [= domyślnie] [OUT | WYDAJNOŚĆ | TYLKO CZYTAĆ ] ]
• [Z {SZYFROWANIEM | RECOMPILE | EXECUTE AS Clause}]
• [DO REPLIKACJI]
• TAK JAK
• ZACZYNAĆ
• [sekcja_sekcji]
• sekcja_wykonywalna
• KONIEC;

Tworzenie i wykonywanie podstawowej procedury składowanej

Korzystanie z tabeli Authors w bazie danych biblioteki

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

Możesz wykonać procedurę z kilkoma różnymi składniami. Po pierwsze, możesz użyć EXECUTE lub EXEC

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

Dodatkowo możesz pominąć polecenie EXEC. Nie musisz też określać przekazywanego parametru, ponieważ przekazujesz wszystkie parametry.

GetName NULL, 'Ernest Hemingway'

Jeśli chcesz określić parametry wejściowe w innej kolejności niż deklarowane w procedurze, możesz podać nazwę parametru i przypisać wartości. Na przykład

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

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

normalną kolejnością wykonywania tej procedury jest najpierw określenie wartości @ Param1, a następnie @ Param2. Będzie to wyglądać mniej więcej tak

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

Ale możliwe jest również, że możesz użyć następujących

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

w tym miejscu podajesz wartość @ param2 najpierw i @ Param1 sekunda. Co oznacza, że nie musisz zachowywać takiej samej kolejności, jak zadeklarowano w procedurze, ale możesz mieć dowolną kolejność według własnego uznania. ale musisz określić, dla którego parametru ustawiasz wartość

Dostęp do procedury składowanej z dowolnej bazy danych

A także możesz utworzyć procedurę z przedrostkiem sp_ te procuedres, podobnie jak wszystkie systemowe procedury składowane, mogą być wykonywane bez określania bazy danych z powodu domyślnego zachowania SQL Server. Podczas wykonywania procedury składowanej, która zaczyna się od „sp_”, SQL Server najpierw szuka procedury w głównej bazie danych. Jeśli procedura nie zostanie znaleziona w trybie głównym, szuka w aktywnej bazie danych. Jeśli masz procedurę składowaną, do której chcesz uzyskać dostęp ze wszystkich baz danych, utwórz ją w trybie głównym i użyj nazwy zawierającej przedrostek „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

ZAPISANA PROCEDURA z parametrami OUT

Procedury składowane mogą zwracać wartości za pomocą słowa kluczowego OUTPUT na liście parametrów.

Tworzenie procedury składowanej z parametrem single out

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

Wykonanie procedury składowanej

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

Tworzenie procedury składowanej z wieloma parametrami wyjściowymi

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

Wykonanie procedury składowanej

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

Procedura składowana z operacją If ... Else and Insert Into

Utwórz przykładową tabelę Employee :

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

Tworzy procedurę przechowywaną, która sprawdza, czy wartości przekazane w procedurze przechowywanej nie są puste lub niepuste, i wykonuje operację wstawiania w tabeli pracowników.

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

Wykonaj procedurę przechowywaną

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

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

Dynamiczny SQL w procedurze przechowywanej

Dynamiczny SQL umożliwia nam generowanie i uruchamianie instrukcji SQL w czasie wykonywania. Dynamiczny SQL jest potrzebny, gdy nasze instrukcje SQL zawierają identyfikator, który może się zmieniać w różnych czasach kompilacji.

Prosty przykład dynamicznego 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

W powyższym zapytaniu sql możemy zobaczyć, że możemy użyć powyższego zapytania, definiując wartości w @table_name, @col_name, and @col_value w czasie wykonywania. Zapytanie jest generowane w czasie wykonywania i wykonywane. Jest to technika, w której możemy tworzyć całe skrypty jako ciąg znaków w zmiennej i wykonywać je. Możemy tworzyć bardziej złożone zapytania za pomocą dynamicznego SQL i koncepcji konkatenacji. Ta koncepcja jest bardzo skuteczna, gdy chcesz utworzyć skrypt, którego można używać w kilku warunkach.

Wykonywanie procedury składowanej

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

Tabela, której użyłem

Wynik

Proste zapętlenie

Najpierw #systables dane do tabeli tymczasowej o nazwie #systables i #systables numer wiersza, abyśmy mogli wyszukiwać jeden rekord naraz

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

Następnie deklarujemy niektóre zmienne do sterowania zapętleniem i przechowujemy nazwę tabeli w tym przykładzie

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

Teraz możemy zapętlić się za pomocą prostej chwili. @rn w instrukcji select , ale może to być także osobna instrukcja dla ex set @rn = @rn + 1 , będzie to zależeć od twoich wymagań. Używamy również wartości @rn zanim zostanie zwiększona, aby wybrać pojedynczy rekord z #systables . Na koniec wypisujemy nazwę tabeli.

while @rn <= @maxRn
    begin

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

        print @tablename
    end

Proste zapętlenie

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