SQL Server存储过程的使用及实例分析

存储过程的基本使用 - 创建存储过程：

使用CREATE PROCEDURE语句，基本语法如下：

CREATE PROCEDURE procedure_name [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] | [ FOR REPLICATION ] AS sql_statement [...n ]

调用存储过程：使用EXEC或EXECUTE关键字，后面跟上存储过程名称和相应的参数（如果有）。例如：EXEC procedure_name [parameter_value]。

修改存储过程：使用ALTER PROCEDURE语句，语法与CREATE PROCEDURE类似，可对已存在的存储过程进行修改，如添加、删除或修改参数，更改存储过程体中的SQL语句等。

存储过程实例

无参数存储过程：

-- 创建存储过程 CREATE PROCEDURE sp_display_welcome AS BEGIN PRINT '欢迎来到SQL Server' END -- 调用存储过程 EXEC sp_display_welcome ```

带输入参数的存储过程：假设存在一个名为employee的表，包含id、name、gender和dob等字段。

-- 创建存储过程 CREATE PROCEDURE sp_get_employee_info @gender VARCHAR(10) AS BEGIN SELECT name, dob FROM employee WHERE gender = @gender END -- 调用存储过程 EXEC sp_get_employee_info @gender = '男' ```

带输入输出参数的存储过程： `

-- 创建存储过程 CREATE PROCEDURE sp_select_book_ByNameExt @name VARCHAR(50), @recordRows INT OUT -- 输出类型 AS BEGIN SELECT FROM books WHERE Name LIKE '%'+@name+'%' SELECT @recordRows = COUNT() FROM books END -- 调用存储过程 DECLARE @rs INT EXEC sp_select_book_ByNameExt 'm', @rs OUT PRINT '查询的记录数是：'+ CONVERT(VARCHAR(5),@rs) ```

使用游标遍历表的存储过程：创建一个名为Employees的表，具有EmployeeID、EmployeeName和Department三个字段。

-- 创建存储过程 CREATE PROCEDURE GetEmployees AS BEGIN DECLARE @EmployeeID INT; DECLARE @EmployeeName NVARCHAR(50); DECLARE @Department NVARCHAR(50); DECLARE employee_cursor CURSOR FOR SELECT EmployeeID, EmployeeName, Department FROM Employees; OPEN employee_cursor; FETCH NEXT FROM employee_cursor INTO @EmployeeID, @EmployeeName, @Department; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'ID: '+ CAST(@EmployeeID AS NVARCHAR) + ', Name: '+ @EmployeeName + ', Department: '+ @Department; FETCH NEXT FROM employee_cursor INTO @EmployeeID, @EmployeeName, @Department; END; CLOSE employee_cursor; DEALLOCATE employee_cursor; END; GO

-- 调用存储过程 EXEC GetEmployees;

- 事务处理的存储过程：创建供应商零件供应表SP表和订单表Orders表，向订单表插入订单记录，并修改零件供应表中的库存量balance。

USE SPJ_DATABASE

GO

-- 创建供应商零件供应表SP表、订单表Orders表 CREATE TABLE SP( SNO CHAR(10), PNO CHAR(10), balance INT CHECK(balance >= 0), FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO) ); CREATE TABLE Orders( ONO CHAR(10), SNO CHAR(10), PNO CHAR(10), JNO CHAR(10), Otime DATETIME NOT NULL DEFAULT GETDATE(), quantity INT CHECK(quantity >= 0), PRIMARY KEY (Ono), FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFERENCES J(JNO) ); GO INSERT INTO SP VALUES('S1','P1',1000);

-- 更多插入语句 GO IF OBJECT_ID ( 'INSERT_ORDERS', 'P' ) IS NOT NULL DROP PROCEDURE INSERT_ORDERS; GO CREATE PROCEDURE INSERT_ORDERS @ONO CHAR(10), @SNO CHAR(10), @PNO CHAR(10), @JNO CHAR(10), @quantity INT AS BEGIN DECLARE @ErrorVar INT; BEGIN TRANSACTION; INSERT INTO Orders (ONO,SNO,PNO,JNO,quantity) VALUES(@ONO,@SNO,@PNO,@JNO,@quantity) UPDATE SP SET balance = balance -@quantity WHERE PNO=@PNO AND SNO=@SNO; SELECT @ErrorVar = @@ERROR; IF @ErrorVar = 0 COMMIT TRANSACTION; ELSE ROLLBACK TRANSACTION; END; GO

-- 调用存储过程 EXEC INSERT_ORDERS 'O1', 'S1', 'P1', 'J1', 100; ```