C#高级：在SQLserver中使用视图和存储过程

一、视图

1.视图是什么，有什么作用？

①一个虚拟表

②简化查询

2.视图和存储过程有什么区别？

①视图：用于简化查询和数据展示

②存储过程：用于执行一系列SQL语句，包含增删改查等，即业务逻辑得处理。

3.建立一个视图，名为PersonBorrowView，SQL已给出：

  SELECT U.Name,BW.BoTime,BI.BookName FROM UserInfo U
  INNER JOIN BorrowInfo BW ON U.UserID=BW.UserID
  INNER JOIN BookInfo BI ON BW.BookID=BI.BookID

【解答】建立视图的SQL语句：

CREATE VIEW PersonBorrowView AS
SELECT U.Name, BW.BoTime, BI.BookName
FROM UserInfo U
INNER JOIN BorrowInfo BW ON U.UserID = BW.UserID
INNER JOIN BookInfo BI ON BW.BookID = BI.BookID;

4.如果往BorrowInfo加一条记录，我原本的SQL会增加一条记录，那么查询视图，数据会增加吗？

会，因为查询视图，本质是查询视图保存的SQL

5.写出查询视图的SQL。

SELECT * FROM PersonBorrowView;

6.写出根据视图名获取视图的SQL

SELECT OBJECT_DEFINITION(OBJECT_ID('PersonBorrowView')) AS VIEWSQL;

7.小结：

-- 建立视图：
CREATE VIEW 视图名 AS
SQL-- 写你的查询SQL

-- 查询视图：
SELECT * FROM 视图名

-- 根据视图名查询视图保存的SQL：
SELECT OBJECT_DEFINITION(OBJECT_ID('视图名')) AS VIEWSQL

二、存储过程

1.创建一个存储过程(一键归还非管理员用户2024年借的书)，要求做以下几件事情：

-- 筛选出需要一键归还的用户
SELECT UserID,Name FROM UserInfo WHERE Power !=1

-- 筛选出2024年未归还的书
SELECT BorID,UserID,BookID FROM BorrowInfo Where Year(BoTime)='2024' and BackTime is null and UserID in (xxxxx)

-- 将这些书的归还状态更新为今天
Update BorrowInfo set BackTime = GETDATE() Where  BorID in (xxxxx)

-- 将一键归还涉及到的用户、书名、还款日期插入到PC_Table中
INSERT INTO PC_TABLE  ([PCName],[PCBoTime],[PCBookName]) VALUES(xxxx)

【解答：存储过程的创建SQL】

  定义存储过程的名称：ReturnBooksForNonAdmin2024

  另外定义一些临时表去推进存储过程的流程

CREATE PROCEDURE ReturnBooksForNonAdmin2024
AS
BEGIN
    -- 1. 筛选出需要一键归还的用户
    DECLARE @NonAdminUsers TABLE (UserID NVARCHAR(100), Name NVARCHAR(100));
    INSERT INTO @NonAdminUsers (UserID, Name)
    SELECT UserID, Name
    FROM UserInfo
    WHERE Power != 1;

    -- 2. 筛选出2024年未归还的书
    DECLARE @BooksToReturn TABLE (BorID NVARCHAR(100), UserID NVARCHAR(100),BookID NVARCHAR(100));
    INSERT INTO @BooksToReturn (BorID, UserID,BookID)
    SELECT BorID, UserID,BookID
    FROM BorrowInfo
    WHERE YEAR(BoTime) = 2024
    AND BackTime IS NULL
    AND UserID IN (SELECT UserID FROM @NonAdminUsers);

    -- 3. 将这些书的归还状态更新为今天
    UPDATE BorrowInfo
    SET BackTime = GETDATE()
    WHERE BorID IN (SELECT BorID FROM @BooksToReturn);

    -- 4. 将一键归还涉及到的用户、书名、还款日期插入到PC_TABLE中
    INSERT INTO PC_TABLE ([PCName], [PCBoTime], [PCBookName])
    SELECT u.Name AS [PCName], GETDATE() AS [PCBoTime], bi.BookName AS [PCBookName]
    FROM @BooksToReturn b
    JOIN UserInfo u ON b.UserID = u.UserID
    JOIN BookInfo bi ON b.BookID= bi.BookID;

END;

2.写出执行存储过程的SQL/方法

EXECUTE ReturnBooksForNonAdmin2024;

3.假设一个存储过程分为4步，第2步发现报错，那么它会执行1、2步，还是都不执行呢？

取决于SQL的事务：

• 如果步骤1和步骤2在同一个事务中执行，并且第2步失败，步骤1也不会生效。
• 如果每一步都在独立的事务中，那么步骤1和步骤2会分别执行，失败的步骤不会影响之前成功的步骤。

(本例的存储过程没有指定事务，因此会被当做是独立的事务，因此会执行第1、2步，而不执行后续的SQL)

4.谈谈临时表和公共表达式的区别

-- 临时表
DECLARE @NonAdminUsers TABLE (UserID NVARCHAR(100), Name NVARCHAR(100));
INSERT INTO @NonAdminUsers (UserID, Name)
SELECT UserID, Name
FROM UserInfo
WHERE Power != 1;
SELECT * FROM @NonAdminUsers

-- 公共表达式CTE
WITH NonAdminUsers AS
(
    SELECT UserID, Name
    FROM UserInfo
    WHERE Power != 1
)
SELECT * FROM NonAdminUsers

【区别和特点】

1. 作用域: 临时表通常用于存储过程或复杂逻辑，公共表达式（CTE）更适合单次查询，特别是递归查询。
2. 复杂性: 临时表处理复杂的增删改查操作时更具优势。
3. 用途: 公共表达式适合在查询中引用和计算临时数据。

5.存储过程的出入参和异常处理

【需求】

        传入一个USERID，如果查到下面的SQL则插入，查不到就不插入，且查到且成功插入返回1，查不到返回0，查到了插入失败返回-99

SELECT U.Name,BW.BoTime,BI.BookName FROM UserInfo U
INNER JOIN BorrowInfo BW ON U.UserID=BW.UserID
INNER JOIN BookInfo BI ON BW.BookID=BI.BookID
WHERE U.UserID=''

【创建存储过程】

CREATE PROCEDURE InsertUserData
    @UserID NVARCHAR(100),  -- 输入的用户ID
    @Result INT OUTPUT     -- 输出结果
AS
BEGIN
    -- 初始化结果为0
    SET @Result = 0;

    -- 使用事务处理数据插入操作
    BEGIN TRANSACTION;

    BEGIN TRY
        -- 检查是否有记录需要插入
        IF EXISTS (
            SELECT 1
            FROM UserInfo U
            INNER JOIN BorrowInfo BW ON U.UserID = BW.UserID
            INNER JOIN BookInfo BI ON BW.BookID = BI.BookID
            WHERE U.UserID = @UserID
        )
        BEGIN
            -- 将所有相关数据插入目标表
            INSERT INTO PC_TABLE ([PCName], [PCBoTime], [PCBookName])
            SELECT U.Name, BW.BoTime, BI.BookName
            FROM UserInfo U
            INNER JOIN BorrowInfo BW ON U.UserID = BW.UserID
            INNER JOIN BookInfo BI ON BW.BookID = BI.BookID
            WHERE U.UserID = @UserID;

            -- 如果插入成功，设置结果为1
            SET @Result = 1;
        END
        ELSE
        BEGIN
            -- 如果没有记录符合条件，设置结果为0
            SET @Result = 0;
        END

        -- 提交事务
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- 如果发生错误，回滚事务
        ROLLBACK TRANSACTION;

        -- 设置结果为-99
        SET @Result = -99;
    END CATCH
END

【执行存储过程】

DECLARE @OutputResult INT;

EXEC InsertUserData @UserID = 'some_user_id', @Result = @OutputResult OUTPUT;

-- 查询输出结果
SELECT @OutputResult AS Result;