C# ????????????? ??????????: SQL ??????????? ?? ?????????. ??????? #45

?? ????????

????????????, ??? ????? ???? ???????? ?? ????? ???????? ?????????? ?????. ??????? ?????, ??? ????? ????? ??????? ????? ?????? ?????????? ????????????, ????? ??????? ??????? ???? ??????? ???????. Stored Procedures ?? Functions ?????? ??? ??????? SQL ???????? - ????? ???? ???????? ???????? SQL ????? "?????????", ???????? ????????? ??????? ???????????.

?? ??? ??????????

Stored Procedures (???????????)

• ?? ???? Stored Procedure ?? ????? ??????????
• ????? ???????? ?? ??????????? ?????
• ???????????? ??????????
• ??????????? ?????????? (????????, ?????, ?????????)

SQL Functions (?????????)

• ????????? ????????? (???????? ??? ???????????? ?????????)
• ???????? ????????? (???????? ?????? ?????????)
• ????? ?????? ???????????

?? Stored Procedures

???????????? ???????? ???????:

1. ????? ??????? ?????? "?????????"
2. ??????? ???????? ???????? ????????????? ????????
3. ????? ??? ?????

Stored Procedure ?????? ??? ???????:

-- ???????? ???????? ??????? SQL-??
CREATE PROCEDURE ????????????_????????
    @?????? NVARCHAR(50),
    @????? INT
AS
BEGIN
    -- ?????????
    IF @????? < 18
        RETURN '?????? ?????????????????';
        
    -- ????????
    INSERT INTO ????????????? (??????, ?????)
    VALUES (@??????, @?????);
    
    -- ???????????
    SELECT '???????????? ?????????? ???????';
END        

?? SQL Functions - ????????

????????? ???????

???????????? ???????????? ??????, ??????? ??? ????????? ?????????? ????????:

CREATE FUNCTION ??????_????????
(
    @?????????_?????? DATE
)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @?????????_??????, GETDATE())
END        

???????? ???????

???????????? ?????? ?????? ?????? ?????????:

CREATE FUNCTION ???????????_??????
(
    @???_???? DECIMAL
)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM ??????????
    WHERE ???? >= @???_????
)        

?? ???????

???????? 1: ??????????? ??????? ???????

???????? Stored Procedure, ???????:

1. ????????? ???? ????????
2. ?????????? ???? ?? ??? ???????? ???? ?????????
3. ????????? ????????? ????????????

CREATE PROCEDURE ?????????_???????????
    @?????? NVARCHAR(50),
    @????? NVARCHAR(50),
    @??????_?????? CHAR(11)
AS
BEGIN
    -- ?????????
    IF EXISTS (SELECT 1 FROM ?????????? WHERE ??????_?????? = @??????_??????)
    BEGIN
        SELECT '???????? ???? ???????? ?????????';
        RETURN;
    END
    
    -- ????????
    INSERT INTO ?????????? (??????, ?????, ??????_??????)
    VALUES (@??????, @?????, @??????_??????);
    
    SELECT '???????? ?????????? ?????????????';
END        

???????? 2: ???????? ????????

???????? ???????, ??????? ?????????? ????????? ??????? ?????:

CREATE FUNCTION ???????_?????_????????
(
    @?????????_ID INT
)
RETURNS DECIMAL(4,2)
AS
BEGIN
    DECLARE @??????? DECIMAL(4,2);
    
    SELECT @??????? = AVG(CAST(???? AS DECIMAL(4,2)))
    FROM ??????????
    WHERE ?????????_ID = @?????????_ID;
    
    RETURN @???????;
END        

?? ???????

Stored Procedures ?? Functions ???? SQL ???????? ?????? ?????????????, ???????? ??????????:

1. ????? ??????????????? ??????????? ?????? ??????, ????????? ????????? ??????? ?????????? ?? ?????????
2. ???????????? ???????????? ???????????? ??????? ???????? SQL ????????????? ?????
3. ?????????? ?????????????? ?????????????? ????????? ??????? ??????? ???????

?? ??????? ????????

1. ??????? ??????? Stored Procedures-???? ???????
2. ?????????? Functions ??????????? ?????????????
3. ????????? ?????????? ??????????
4. ???????? ?????? ???????? ???????

?? ??????? ?????????????

• ??????? ??????? ???????????
• ????????? ?????????? ??????????? ?????
• ?? ????????? ??????????
• ?????? ?? ????????? ?????-?????? ?????????