一、存储过程

1、存储过程

存储过程(Stored Procedure) 是一组为了完成特定功能的SQL 语句集，经编译后存储在服务器端数据库中，用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来执行它。

2、存储过程的分类

（1）系统存储过程：由SQL Server系统提供的存储过程，主要存储在master数据库和用户数据库中，主要实现一些系统的管理功能和数据库对象管理功能。

（2）用户自定义存储过程：由用户根据需要创建的存储过程，实现用户特定的应用。

3、存储过程的优点

（1） 存储过程可以实现组件化管理

存储过程是实现特定功能的程序体，不同的应用程序都可以通过名称和参数调用存储过程，对存储过程的修改完善不会影响应用程序，提高系统的可移植性。

（2）存储过程能够实现较快的执行速度

因为存储过程是经过预编译和优化过的程序代码。

（3）存储过程能够减少网络流量

客户端程序通过名称和参数调用存储过程，而非传递整个TSQL代码来执行操作。

（4）存储过程可以实现数据的安全性

存储过程的调用需要权限，且对数据的操作是被封装的，只提供调用接口。

4、用TSQL命令创建存储过程

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

二、实例

新增供应商零件供应表SP表和订单表Orders表；

创建销售存储过程，向订单表插入订单记录，并修改零件供应表中的库存量balance，其中订单时间取插入订单时的系统时间（使用getdate() 函数）。调用该销售存储过程向数据库添加每一笔交易的信息。

USE SPJDATABASEGO--创建供应商零件供应表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),);GOINSERT INTO SP VALUES('S1','P1',1000);INSERT INTO SP VALUES('S1','P2',1000);INSERT INTO SP VALUES('S1','P3',1000);INSERT INTO SP VALUES('S1','P4',1000);INSERT INTO SP VALUES('S1','P5',1000);INSERT INTO SP VALUES('S1','P6',1000);INSERT INTO SP VALUES('S2','P1',1000);INSERT INTO SP VALUES('S2','P2',1000);INSERT INTO SP VALUES('S2','P3',1000);INSERT INTO SP VALUES('S2','P4',1000);INSERT INTO SP VALUES('S2','P5',1000);INSERT INTO SP VALUES('S2','P6',1000);INSERT INTO SP VALUES('S3','P1',1000);INSERT INTO SP VALUES('S3','P2',1000);INSERT INTO SP VALUES('S3','P3',1000);INSERT INTO SP VALUES('S3','P4',1000);INSERT INTO SP VALUES('S3','P5',1000);INSERT INTO SP VALUES('S3','P6',1000);INSERT INTO SP VALUES('S4','P1',1000);INSERT INTO SP VALUES('S4','P2',1000);INSERT INTO SP VALUES('S4','P3',1000);INSERT INTO SP VALUES('S4','P4',1000);INSERT INTO SP VALUES('S4','P5',1000);INSERT INTO SP VALUES('S4','P6',1000);INSERT INTO SP VALUES('S5','P1',1000);INSERT INTO SP VALUES('S5','P2',1000);INSERT INTO SP VALUES('S5','P3',1000);INSERT INTO SP VALUES('S5','P4',1000);INSERT INTO SP VALUES('S5','P5',1000);INSERT INTO SP VALUES('S5','P6',1000);GOIF OBJECT_ID ( 'INSERT_ORDERS', 'P' ) IS NOT NULL DROP PROCEDURE INSERT_ORDERS;GOCREATE PROCEDURE INSERT_ORDERS@ONO CHAR(10),@SNO CHAR(10),@PNO CHAR(10),@JNO CHAR(10), @quantity intASBEGINDECLARE @ErrorVar INT;--声明用户变量,用于存储SQL语句的错误编号BEGIN TRANSACTION;INSERT INTO Orders (ONO,SNO,PNO,JNO,quantity)VALUES(@ONO,@SNO,@PNO,@JNO,@quantity)UPDATE SPSET balance = balance -@quantityWHERE PNO=@PNO AND SNO=@SNO;--判定上一SQL语句的执行状态SELECT @ErrorVar = @@ERROR;--系统变量，上一句SQL的执行状态，--会后被下一SQL语句重新赋值，故另存IF @ErrorVar != 0--为0表示代码执行正确，非0值为系统定义的错误编号，--可在主调程序中查阅详细错误信息并处理BEGINROLLBACK;--会重置@@ERROR，故前面用@ErrorVar另存RETURN @ErrorVar;ENDCOMMIT;RETURN 0;ENDGO--调用/执行存储过程DECLARE @retstat int; --执行状态EXECUTE @retstat = INSERT_ORDERS 'O1','S1','P1','J1',100SELECT @retstatIF @retstat = 0SELECT '插入成功。'ELSE SELECT '插入失败。'

​​​​​​