SQL存储过程

原创

Aurora_周公子 2024-08-04 21:33:37 博主文章分类：数据库 ©著作权

文章标签 存储过程 EXEC 错误处理 文章分类 SQL Server 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者Aurora_周公子的原创作品，请联系作者获取转载授权，否则将追究法律责任

存储过程是数据库中的一组预编译的SQL语句，可以用来完成特定的功能。

特征

预编译：存储过程在创建时被编译，执行时效率较高。
参数支持：可以接受输入参数和返回输出参数。
控制结构：支持条件语句和循环。
提高安全性：通过存储过程而不是直接访问表，提高了数据的安全性。

各种数据库的CRUD存储过程

1.1 MySQL

增加员工（INSERT）

DELIMITER //

CREATE PROCEDURE AddEmployee(IN firstName VARCHAR(50), IN lastName VARCHAR(50), IN position VARCHAR(50), IN salary DECIMAL(10,2))
BEGIN
    INSERT INTO Employees (FirstName, LastName, Position, Salary) 
    VALUES (firstName, lastName, position, salary);
END //

DELIMITER ;

修改员工信息（UPDATE）

DELIMITER //

CREATE PROCEDURE UpdateEmployee(IN emp_id INT, IN salary DECIMAL(10,2))
BEGIN
    UPDATE Employees 
    SET Salary = salary 
    WHERE EmployeeID = emp_id;
END //

DELIMITER ;

删除员工（DELETE）

DELIMITER //

CREATE PROCEDURE DeleteEmployee(IN emp_id INT)
BEGIN
    DELETE FROM Employees 
    WHERE EmployeeID = emp_id;
END //

DELIMITER ;

查询员工（SELECT）

DELIMITER //

CREATE PROCEDURE GetEmployeeByID(IN emp_id INT)
BEGIN
    SELECT EmployeeID, FirstName, LastName, Position, Salary 
    FROM Employees 
    WHERE EmployeeID = emp_id;
END //

DELIMITER ;

分页查询员工

DELIMITER //

CREATE PROCEDURE GetEmployeesPaginated(IN pageNum INT, IN pageSize INT)
BEGIN
    SET @offset = (pageNum - 1) * pageSize;
    PREPARE stmt FROM 'SELECT * FROM Employees LIMIT ? OFFSET ?';
    SET @pageSize = pageSize;
    SET @offsetValue = @offset;
    EXECUTE stmt USING @pageSize, @offsetValue;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

1.2 PostgreSQL

增加员工（INSERT）

CREATE OR REPLACE FUNCTION AddEmployee(first_name TEXT, last_name TEXT, position TEXT, salary NUMERIC) 
RETURNS VOID AS $$
BEGIN
    INSERT INTO Employees (FirstName, LastName, Position, Salary) 
    VALUES (first_name, last_name, position, salary);
END;
$$ LANGUAGE plpgsql;

修改员工信息（UPDATE）

CREATE OR REPLACE FUNCTION UpdateEmployee(emp_id INT, salary NUMERIC) 
RETURNS VOID AS $$
BEGIN
    UPDATE Employees 
    SET Salary = salary 
    WHERE EmployeeID = emp_id;
END;
$$ LANGUAGE plpgsql;

删除员工（DELETE）

CREATE OR REPLACE FUNCTION DeleteEmployee(emp_id INT) 
RETURNS VOID AS $$
BEGIN
    DELETE FROM Employees 
    WHERE EmployeeID = emp_id;
END;
$$ LANGUAGE plpgsql;

查询员工（SELECT）

CREATE OR REPLACE FUNCTION GetEmployeeByID(emp_id INT) 
RETURNS TABLE(EmployeeID INT, FirstName TEXT, LastName TEXT, Position TEXT, Salary NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT EmployeeID, FirstName, LastName, Position, Salary 
    FROM Employees 
    WHERE EmployeeID = emp_id;
END;
$$ LANGUAGE plpgsql;

分页查询员工

CREATE OR REPLACE FUNCTION GetEmployeesPaginated(page_num INT, page_size INT) 
RETURNS TABLE(EmployeeID INT, FirstName TEXT, LastName TEXT, Position TEXT, Salary NUMERIC) AS $$
BEGIN
    RETURN QUERY 
    SELECT * FROM Employees 
    ORDER BY EmployeeID 
    LIMIT page_size OFFSET (page_num - 1) * page_size;
END;
$$ LANGUAGE plpgsql;

1.3 达梦

增加员工（INSERT）

CREATE PROCEDURE AddEmployee(IN firstName VARCHAR(50), IN lastName VARCHAR(50), IN position VARCHAR(50), IN salary DECIMAL(10,2))
BEGIN
    INSERT INTO Employees (FirstName, LastName, Position, Salary) 
    VALUES (firstName, lastName, position, salary);
END;

修改员工信息（UPDATE）

CREATE PROCEDURE UpdateEmployee(IN emp_id INT, IN salary DECIMAL(10,2))
BEGIN
    UPDATE Employees 
    SET Salary = salary 
    WHERE EmployeeID = emp_id;
END;

删除员工（DELETE）

CREATE PROCEDURE DeleteEmployee(IN emp_id INT)
BEGIN
    DELETE FROM Employees 
    WHERE EmployeeID = emp_id;
END;

查询员工（SELECT）

CREATE PROCEDURE GetEmployeeByID(IN emp_id INT)
BEGIN
    SELECT EmployeeID, FirstName, LastName, Position, Salary 
    FROM Employees 
    WHERE EmployeeID = emp_id;
END;

分页查询员工

CREATE PROCEDURE GetEmployeesPaginated(IN page_num INT, IN page_size INT)
BEGIN
    DECLARE offset INT;
    SET offset = (page_num - 1) * page_size;

    SELECT * FROM Employees 
    LIMIT page_size OFFSET offset;
END;

1.4 Oracle

增加员工（INSERT）

CREATE OR REPLACE PROCEDURE AddEmployee(first_name IN VARCHAR2, last_name IN VARCHAR2, position IN VARCHAR2, salary IN NUMBER) AS
BEGIN
    INSERT INTO Employees (FirstName, LastName, Position, Salary) 
    VALUES (first_name, last_name, position, salary);
END;

修改员工信息（UPDATE）

CREATE OR REPLACE PROCEDURE UpdateEmployee(emp_id IN NUMBER, salary IN NUMBER) AS
BEGIN
    UPDATE Employees 
    SET Salary = salary 
    WHERE EmployeeID = emp_id;
END;

删除员工（DELETE）

CREATE OR REPLACE PROCEDURE DeleteEmployee(emp_id IN NUMBER) AS
BEGIN
    DELETE FROM Employees 
    WHERE EmployeeID = emp_id;
END;

查询员工（SELECT）

CREATE OR REPLACE PROCEDURE GetEmployeeByID(emp_id IN NUMBER) AS
BEGIN
    FOR rec IN (SELECT EmployeeID, FirstName, LastName, Position, Salary 
                FROM Employees 
                WHERE EmployeeID = emp_id) LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || rec.EmployeeID || ', Name: ' || rec.FirstName || ' ' || rec.LastName);
    END LOOP;
END;

分页查询员工

CREATE OR REPLACE PROCEDURE GetEmployeesPaginated(page_num IN NUMBER, page_size IN NUMBER) AS
BEGIN
    FOR rec IN (SELECT * FROM (
                    SELECT EmployeeID, FirstName, LastName, Position, Salary, 
                    ROW_NUMBER() OVER (ORDER BY EmployeeID) AS rn 
                    FROM Employees)
                 WHERE rn BETWEEN (page_num - 1) * page_size + 1 AND page_num * page_size) LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || rec.EmployeeID || ', Name: ' || rec.FirstName || ' ' || rec.LastName);
    END LOOP;
END;

存储过程中的错误处理

MySQL 错误处理

DELIMITER //

CREATE PROCEDURE SafeGetEmployeeByID(IN emp_id INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 错误处理逻辑
        SELECT '发生错误';
    END;

    SELECT EmployeeID, FirstName, LastName 
    FROM Employees 
    WHERE EmployeeID = emp_id;
END //

DELIMITER ;

PostgreSQL 错误处理

CREATE OR REPLACE FUNCTION SafeGetEmployeeByID(emp_id INT) 
RETURNS VOID AS $$
BEGIN
    -- 进行查询
    PERFORM * FROM Employees WHERE EmployeeID = emp_id;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '发生错误: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

达梦和 Oracle 错误处理

在达梦和 Oracle 中可以使用 EXCEPTION 来处理错误。

达梦示例：

CREATE PROCEDURE SafeGetEmployeeByID(IN emp_id INT)
BEGIN
    BEGIN
        SELECT FirstName INTO emp_FirstName FROM Employees WHERE EmployeeID = emp_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            SELECT '未找到员工';
    END;
END;

Oracle 示例：

CREATE OR REPLACE PROCEDURE SafeGetEmployeeByID(emp_id IN NUMBER) AS
BEGIN
    -- 进行查询
    SELECT FirstName INTO emp_FirstName FROM Employees WHERE EmployeeID = emp_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('未找到员工');
END;

扩展存储过程调用后端接口示例：

假设我们有一个 `Orders` 表，记录订单信息。我们希望在更新订单状态时，触发一个存储过程，发送通知给用户。

创建触发器和存储过程

开启组件（2012以上版本支持）

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO

注意事项：

安全性：开启 OLE Automation Procedures 可能会增加 SQL Server 的安全风险，因为恶意代码可能会利用这种能力，因此在生产环境中使用时需要谨慎，并确保相关权限的管理。
性能影响：使用 OLE Automation Procedures 可能会影响数据库性能，特别是当它们被频繁调用时。

1. 创建 `Orders` 表

CREATE TABLE [dbo].[Orders] (
    OrderID BIGINT PRIMARY KEY,
    Status NVARCHAR(50),
    UserEmail NVARCHAR(255)
);

2. 创建存储过程

这是负责发送通知的存储过程：

USE [HyperFCOBS]
GO

ALTER PROCEDURE [dbo].[proc_SendNotification]
    @orderId BIGINT,
    @userEmail NVARCHAR(255)
AS
BEGIN
    DECLARE @apiUrl NVARCHAR(300);
    DECLARE @object INT;
    DECLARE @responseText NVARCHAR(4000);
    DECLARE @hr INT;
    DECLARE @status NVARCHAR(10);
    DECLARE @source VARCHAR(255), @description VARCHAR(255);

    -- 构建 API URL
    SET @apiUrl = CONCAT('http://example.com/api/sendNotification?orderId=', @orderId, '&email=', @userEmail);

    BEGIN TRY
        -- 创建 Server XMLHTTP 对象实例
        EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHTTP', @object OUT;
        IF @hr <> 0
        BEGIN
            EXEC sp_OAGetErrorInfo @object, NULL, @source OUT, @description OUT;
            RAISERROR('创建对象时出错。 %s %s', 16, 1, @source, @description);
            RETURN;
        END

        -- 设置超时
        EXEC sp_OAMethod @object, 'setTimeouts', NULL, 5000, 5000, 5000, 100;

        -- 打开 HTTP 请求
        EXEC sp_OAMethod @object, 'open', NULL, 'GET', @apiUrl, 'false';

        -- 发送请求
        EXEC sp_OAMethod @object, 'send';

        -- 检查请求状态
        EXEC @hr = sp_OAGetProperty @object, 'status', @status OUTPUT;
        IF @hr <> 0 OR @status <> '200'
        BEGIN
            EXEC sp_OAGetErrorInfo @object, NULL, @source OUT, @description OUT;
            RAISERROR('请求失败。状态码: %s, 错误信息: %s %s', 16, 1, @status, @source, @description);
            RETURN;
        END

        -- 获取响应文本
        EXEC @hr = sp_OAMethod @object, 'responseText', @responseText OUTPUT;
        IF @hr <> 0
        BEGIN
            EXEC sp_OAGetErrorInfo @object, NULL, @source OUT, @description OUT;
            RAISERROR('获取响应时出错。 %s %s', 16, 1, @source, @description);
            RETURN;
        END

        -- 输出响应文本
        PRINT @responseText;

    END TRY
    BEGIN CATCH
        -- 处理错误
        DECLARE @errorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        RAISERROR('存储过程执行期间发生错误: %s', 16, 1, @errorMessage);
    END CATCH
    FINALLY
        -- 清理并销毁对象
        IF @object IS NOT NULL
            EXEC sp_OADestroy @object;
END

3. 创建触发器

触发器将在订单状态更新时调用存储过程：

USE [HyperFCOBS]
GO

ALTER TRIGGER [dbo].[trg_OrderStatusUpdate]
ON [dbo].[Orders]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON; -- 避免多余的消息

    -- 检查是否有任何行的Status被更新为‘Shipped’
    IF EXISTS (
        SELECT 1
        FROM inserted i
        WHERE i.Status = 'Shipped'
    )
    BEGIN
        -- 调用存储过程
        DECLARE @orderId BIGINT;
        DECLARE @userEmail NVARCHAR(255);
        
        -- 使用临时表存储符合条件的结果
        DECLARE @OrdersToNotify TABLE (OrderID BIGINT, UserEmail NVARCHAR(255));

        INSERT INTO @OrdersToNotify (OrderID, UserEmail)
        SELECT i.OrderID, i.UserEmail
        FROM inserted i
        INNER JOIN deleted d ON i.OrderID = d.OrderID
        WHERE i.Status = 'Shipped' AND d.Status <> 'Shipped';

        -- 遍历临时表并调用存储过程
        DECLARE order_cursor CURSOR FOR
            SELECT OrderID, UserEmail FROM @OrdersToNotify;

        OPEN order_cursor;

        FETCH NEXT FROM order_cursor INTO @orderId, @userEmail;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- 调用存储过程
            EXEC [dbo].[proc_SendNotification] @orderId, @userEmail;
            FETCH NEXT FROM order_cursor INTO @orderId, @userEmail;
        END

        CLOSE order_cursor;
        DEALLOCATE order_cursor;
    END
END

使用场景

更新订单状态:

假设你想要更改某个订单的状态，可以执行以下 SQL：

UPDATE dbo.Orders
SET Status = 'Shipped'
WHERE OrderID = 101; -- 替换为相关订单ID

网上也有一些存储过程调用接口的文章，可以进行参考，请自行百度

上一篇：SQL索引

下一篇：SignalR即时通讯

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

SQL存储过程

SQL存储过程

特征

各种数据库的CRUD存储过程

1.1 MySQL

增加员工（INSERT）

修改员工信息（UPDATE）

删除员工（DELETE）

查询员工（SELECT）

分页查询员工

1.2 PostgreSQL

增加员工（INSERT）

修改员工信息（UPDATE）

删除员工（DELETE）

查询员工（SELECT）

分页查询员工

1.3 达梦

增加员工（INSERT）

修改员工信息（UPDATE）

删除员工（DELETE）

查询员工（SELECT）

分页查询员工

1.4 Oracle

增加员工（INSERT）

修改员工信息（UPDATE）

删除员工（DELETE）

查询员工（SELECT）

分页查询员工

存储过程中的错误处理

MySQL 错误处理

PostgreSQL 错误处理

达梦和 Oracle 错误处理

扩展存储过程调用后端接口示例：

假设我们有一个 Orders 表，记录订单信息。我们希望在更新订单状态时，触发一个存储过程，发送通知给用户。

创建触发器和存储过程

开启组件（2012以上版本支持）

注意事项：

1. 创建 Orders 表

2. 创建存储过程

3. 创建触发器

使用场景

51CTO博客

假设我们有一个 `Orders` 表，记录订单信息。我们希望在更新订单状态时，触发一个存储过程，发送通知给用户。

1. 创建 `Orders` 表