SQL Server 数据库----存储过程的创建及使用

一、存储过程

      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 SPJDATABASE
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);
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);

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;	--声明用户变量,用于存储SQL语句的错误编号
		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;
			--判定上一SQL语句的执行状态
			SELECT @ErrorVar = @@ERROR;	--系统变量，上一句SQL的执行状态，
							--会后被下一SQL语句重新赋值，故另存
			IF @ErrorVar != 0	--为0表示代码执行正确，非0值为系统定义的错误编号，
						--可在主调程序中查阅详细错误信息并处理
				BEGIN
					ROLLBACK;	--会重置@@ERROR，故前面用@ErrorVar另存
					RETURN @ErrorVar;
				END
			COMMIT;
			RETURN 0;
	END
GO
--调用/执行存储过程
DECLARE @retstat int; --执行状态
EXECUTE @retstat = INSERT_ORDERS 'O1','S1','P1','J1',100
SELECT @retstat	
IF @retstat = 0
	SELECT '插入成功。'
ELSE 
   SELECT '插入失败。'

​​​​​​