如果条件为真，则引发错误并不执行else块

Question

我想写sql脚本来更新我的表，如果一些列没有空值，如果它有空值，update语句将不会执行，我的代码在这里引发错误并执行else块，为什么？

IF EXISTS(select 1 from Trendline.Invoices where Trx_Date is null or Trx_no is null or OperaKey is null) 
     RAISERROR('This script can not been executed because of null value/values in this columns.',0,1)
ELSE

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE Trendline.Invoices
    DROP CONSTRAINT FK_Invoices_RecSkpUser
GO
ALTER TABLE Trendline.Invoices
    DROP CONSTRAINT FK_Invoices_RecConfSkpUsr
GO
ALTER TABLE Trendline.Invoices
    DROP CONSTRAINT FK_Invoices_FollowUpSkpUsr
GO
ALTER TABLE TaeppaCore.Users SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE Trendline.Invoices
    DROP CONSTRAINT FK_Invoices_Currencies

Answer 1

您可以使用函数或plsql块来满足您的需求。

Declare
procedurename p(sales NUMBER)
BEGIN
IF sales>100 THEN
UPDATE STOCK set SALES=sales-100;
ELSE
DBMS_OUTPUT.PUT_LINE('SOME COMMENT');
END IF
END p;

Answer 2

除非后面的语句被BEGIN/END块包围，否则ELSE将控制下一条语句的执行。所以：

IF EXISTS(select 1 from Trendline.Invoices where Trx_Date is null or Trx_no is null or OperaKey is null)
     --The next line executes if the IF was taken
     RAISERROR('This script can not been executed because of null value/values in this columns.',0,1)
ELSE

--The next line executes if the ELSE was taken
BEGIN TRANSACTION
--The next line executes whether either branch was taken
SET QUOTED_IDENTIFIER ON

因此，通常的建议是将应该由BEGIN/END块组合在一起的所有内容包含在一起，如下所示：

IF EXISTS(select 1 from Trendline.Invoices where Trx_Date is null or Trx_no is null or OperaKey is null) 
     RAISERROR('This script can not been executed because of null value/values in this columns.',0,1)
ELSE
BEGIN
  BEGIN TRANSACTION
  SET QUOTED_IDENTIFIER ON
  SET ARITHABORT ON
  SET NUMERIC_ROUNDABORT OFF
  SET CONCAT_NULL_YIELDS_NULL ON
  SET ANSI_NULLS ON
  SET ANSI_PADDING ON
  SET ANSI_WARNINGS ON
  COMMIT
END

但是您不能在BEGIN/END块中使用批处理分隔符(GO)-因此，您需要重新测试每个批处理中的条件，或者找到其他方法来阻止以下语句执行。