SQL高级编码题：某银行的数据库中有一账户信息表userInfo，结构如下：

发布网友发布时间：2022-04-24 08:10

共1个回答

热心网友时间：2023-10-09 02:34

第一个。

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时执行成功。其他值时表示错误。不需要可取消
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 < @empavg
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