展开全部
第一个。SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[acc_moneyout]
(@acc_from INTEGER,@acc_to INTEGER,@money INTEGER,@rtn INTEGER OUTPUT)
--@rtn表示返回值。返回为0时执62616964757a686964616fe78988e69d8331333335343437行成功。其他值时表示错误。不需要可取消
AS
BEGIN TRAN
DECLARE @tran_err INTEGER
SET NOCOUNT ON;
UPDATE userInfo SET ucount = ucount + @money WHERE uid = @acc_to
SET @tran_err = @tran_err + @@ERROR
UPDATE userInfo SET ucount = ucount - @money WHERE uid = @acc_from
SET @tran_err = @tran_err + @@ERROR
IF @tran_err <> 0
BEGIN ROLLBACK TRAN
SET @rtn = 0
END
ELSE
BEGIN COMMIT TRAN
SET @rtn = 1
END
GO
第二个SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[add_salary]
(@depart_in VARCHAR(10),@rtn INTEGER OUTPUT)
--@rtn表示返回值。不需要可取消。为0时表示输入的部门查询到的平均工资为0,正常情况不会出现,除非输入的部门不存在
--@rtn为1时表示加100,为2时表示加200
AS
BEGIN
SET NOCOUNT ON;
DECLARE @empavg DECIMAL(9,2)
DECLARE @depavg DECIMAL(9,2)
SELECT @depavg = ISNULL(ROUND(AVG(empSalary),2),0) FROM salaryInfo WHERE departName = @depart_in
IF @depavg = 0
BEGIN
SET @rtn = 0
END
ELSE
BEGIN
SELECT @empavg = ISNULL(ROUND(AVG(empSalary),2),0) FROM salaryInfo
IF @depavg
BEGIN
UPDATE salaryInfo SET empSalary = empSalary + 200 WHERE departName = @depart_in
SET @rtn = 2
END
ELSE
BEGIN
UPDATE salaryInfo SET empSalary = empSalary + 100 WHERE departName = @depart_in
SET @rtn = 1
END
END
PRINT @rtn
END
GO