如何将 select 嵌套在 if 中？

发布于 2024-12-06 09:36:51 字数 824 浏览 1 评论 0原文

我对 Firebird 很陌生，但我想知道如何使用 select 语句作为我的条件标准的一部分。我觉得我已经在互联网上试图找到一种方法来做到这一点，但还没有想出太多。以下是我尝试让它发挥作用的尝试。

SET TERM ^ ;
ALTER PROCEDURE sp_test (
IPADD Varchar(32),
HN Varchar(32),
NOTE Varchar(200) )
RETURNS ( update_count integer )
AS
BEGIN
IF((SELECT COUNT(*)
    FROM ADDRESSES a
    WHERE a.ADDRESS_TYPE = 'Reserved'
      AND a.ALIVE = 'N'
      AND (a.HOST_NAME = '' OR a.HOST_NAME is NULL)
      AND (a.DNS_NAME = '' OR a.DNS_NAME is NULL)
      AND (a.SYSTEM_NAME = '' OR a.SYSTEM_NAME is NULL)) > 0)
THEN
  UPDATE 
    ADDRESSES a
  SET
    a.HOST_NAME = :HN,
    a.ADDRESS_TYPE = 'Assigned',
    a.NOTES = :NOTE
  WHERE
    a.SHORT_IP_ADDRESS = :IPADD;
  update_count = 1;
  SUSPEND;
ELSE
  update_count = 0;
  SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
ON PROCEDURE sp_test TO  SYSDBA;

原文

I'm very new to Firebird, but I want to know how I can use a select statement as part of my conditional criteria. I feel like I've been to the internet in back trying to find a way to do this, but haven't come up with much. Below is my attempt at getting this to work.

SET TERM ^ ;
ALTER PROCEDURE sp_test (
IPADD Varchar(32),
HN Varchar(32),
NOTE Varchar(200) )
RETURNS ( update_count integer )
AS
BEGIN
IF((SELECT COUNT(*)
    FROM ADDRESSES a
    WHERE a.ADDRESS_TYPE = 'Reserved'
      AND a.ALIVE = 'N'
      AND (a.HOST_NAME = '' OR a.HOST_NAME is NULL)
      AND (a.DNS_NAME = '' OR a.DNS_NAME is NULL)
      AND (a.SYSTEM_NAME = '' OR a.SYSTEM_NAME is NULL)) > 0)
THEN
  UPDATE 
    ADDRESSES a
  SET
    a.HOST_NAME = :HN,
    a.ADDRESS_TYPE = 'Assigned',
    a.NOTES = :NOTE
  WHERE
    a.SHORT_IP_ADDRESS = :IPADD;
  update_count = 1;
  SUSPEND;
ELSE
  update_count = 0;
  SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
ON PROCEDURE sp_test TO  SYSDBA;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

那伤。 2024-12-13 09:36:51

使用 COUNT 检查是否有要更新的记录不是最好的方法，请使用 EXISTS 代替，即您的 IF 会是

IF(EXISTS(SELECT 1 FROM ADDRESSES a
    WHERE a.ADDRESS_TYPE = 'Reserved'
      AND a.ALIVE = 'N'
      AND (a.HOST_NAME = '' OR a.HOST_NAME is NULL)
      AND (a.DNS_NAME = '' OR a.DNS_NAME is NULL)
      AND (a.SYSTEM_NAME = '' OR a.SYSTEM_NAME is NULL)))
THEN

但似乎有返回值 update_count 存在问题 - 如果执行 UPDATE，则返回 1，但受该语句影响的实际行数可能是其他值。我建议您改用 ROW_COUNT 上下文变量。所以你的程序是

ALTER PROCEDURE sp_test (
IPADD Varchar(32),
HN Varchar(32),
NOTE Varchar(200) )
RETURNS ( update_count integer )
AS
BEGIN
  IF(EXISTS(SELECT 1 FROM ADDRESSES a
    WHERE (a.ADDRESS_TYPE = 'Reserved')
      AND (a.ALIVE = 'N')
      AND (a.HOST_NAME = '' OR a.HOST_NAME is NULL)
      AND (a.DNS_NAME = '' OR a.DNS_NAME is NULL)
      AND (a.SYSTEM_NAME = '' OR a.SYSTEM_NAME is NULL)))
  THEN BEGIN
     UPDATE ADDRESSES a SET
       a.HOST_NAME = :HN,
       a.ADDRESS_TYPE = 'Assigned',
       a.NOTES = :NOTE
     WHERE a.SHORT_IP_ADDRESS = :IPADD;
     update_count = ROW_COUNT;
  END ELSE update_count = 0;
  SUSPEND;
END^

Using COUNT to check is there records to update is not the best way, use EXISTS instead, ie your IF would be

IF(EXISTS(SELECT 1 FROM ADDRESSES a
    WHERE a.ADDRESS_TYPE = 'Reserved'
      AND a.ALIVE = 'N'
      AND (a.HOST_NAME = '' OR a.HOST_NAME is NULL)
      AND (a.DNS_NAME = '' OR a.DNS_NAME is NULL)
      AND (a.SYSTEM_NAME = '' OR a.SYSTEM_NAME is NULL)))
THEN

But there seems to be a problem with your return value, update_count - you return 1 if you execute the UPDATE, but the actual number of rows affected by the statement might be something else. I suggest you use ROW_COUNT context variable instead. So your procedure would be

ALTER PROCEDURE sp_test (
IPADD Varchar(32),
HN Varchar(32),
NOTE Varchar(200) )
RETURNS ( update_count integer )
AS
BEGIN
  IF(EXISTS(SELECT 1 FROM ADDRESSES a
    WHERE (a.ADDRESS_TYPE = 'Reserved')
      AND (a.ALIVE = 'N')
      AND (a.HOST_NAME = '' OR a.HOST_NAME is NULL)
      AND (a.DNS_NAME = '' OR a.DNS_NAME is NULL)
      AND (a.SYSTEM_NAME = '' OR a.SYSTEM_NAME is NULL)))
  THEN BEGIN
     UPDATE ADDRESSES a SET
       a.HOST_NAME = :HN,
       a.ADDRESS_TYPE = 'Assigned',
       a.NOTES = :NOTE
     WHERE a.SHORT_IP_ADDRESS = :IPADD;
     update_count = ROW_COUNT;
  END ELSE update_count = 0;
  SUSPEND;
END^

回复收藏 0 原文

~没有更多了~