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

摘要：CREATE PROCEDURE procedure_name [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [,...n ] [ WITH { RECOMPILE | EN

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

使用`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

-- 创建供应商零件供应表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; ```