mysql - 创建存储过程时出现“子查询返回超过 1 个值”错误

Question

学习程序的概念并想到自己尝试一些东西。

1)我有一个包含以下列的表贷款号
r_兴趣

贷款金额

贷款日期

时间_年

2)

我创建了一个过程

create procedure proc_update1

as
begin
 declare @interest as decimal

declare @rate as int

declare @p as int

declare @n as int

set @rate=(select r_interest from bank_details)

set @p=(select loan_amt from bank_details)

set @n=(select time_yr from bank_details)

set @interest =(@p*@n*@rate)/100

alter table loan_details add interest1 decimal

update loan_details set interest1=@interest

end

3) 当我使用 exec proc_update1

执行时

Msg 512, Level 16, State 1, Procedure proc_update1, Line 9

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Msg 512, Level 16, State 1, Procedure proc_update1, Line 10

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Msg 512, Level 16, State 1, Procedure proc_update1, Line 11

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Msg 2705, Level 16, State 4, Procedure proc_update1, Line 13

我是不是误会了什么？？？

Answer 1

编辑:我误解了这个问题。您有一张包含贷款金额、时间和利率的表格。您在某个表中有一列，无论是原始表还是新表。您可以使用以下查询更新行。

UPDATE loan_details
SET interest1 = 
    (
    SELECT (loan_amt*time_yr*r_interest)/100 AS 'interest'
    FROM bank_details
    )

但是您应该有一个主键，以便您可以添加:

WHERE loan_details.Account = bank_details.AccountId

如果要在可重用存储过程中添加列，则必须先检查该列是否已存在，然后再继续。您可以通过查询 sys.columns 来完成此操作。如果该列不存在，则会添加它。如果该列不存在，则什么也不会发生。无论如何，您都会继续更新贷款详细信息。

IF EXISTS
    (
    SELECT * FROM sys.columns
    WHERE Name = N'columnName' AND OBJECT_ID = OBJECT_ID(N'tableName')
    )
BEGIN
    ALTER TABLE MyTable
    ADD NewColumn1 INT DEFAULT 1
END