sql - 在存储过程中更新后选择

Question

我试图在不直接使用锁定语句的情况下编写过程。

SET TERM ^ ;
ALTER PROCEDURE PROC_GETSTATUS (
    IDTBL1 Integer )
RETURNS (
    STATUS Varchar(255) )
AS
declare variable t int;

BEGIN

   t=gen_id(RPUSING,1);
   update  TBL1 a set a.STATUS=cast('USINGBY' as varchar(255))||cast(:t as varchar(255)) where a.STATUS='free' and a.ID=:IDTBL1 order by a.LASTUPDATED rows 1 to 1;

   STATUS=cast('USINGBY' as varchar(255))||cast(:t as varchar(255));
   SUSPEND;
   END^
SET TERM ; ^


GRANT EXECUTE
 ON PROCEDURE PROC_GETSTATUS TO  SYSDBA;

当我通过如下查询从中选择数据时:

select * from TBL1 a where a.STATUS in (select b.STATUS from PROC_GETSTATUS(1));

它返回空值。但是这个选择

select * from TBL1 a where a.STATUS like '%USINGBY%'

在当前事务中返回更新的数据。如何在当前事务中通过一个select to procedure重写这个查询？

Answer 1

为什么不返回更新记录的id和它的新状态？

CREATE OR ALTER PROCEDURE PROC_GETSTATUS (
  IDTBL1 INTEGER)
RETURNS (
  ID INTEGER,
  STATUS VARCHAR(255))
AS
BEGIN
  FOR
    SELECT FIRST 1 a.id 
    FROM tbl1 a
    WHERE a.status='free' AND a.id=:IDTBL1 
    ORDER BY a.lastupdated 
    INTO :ID
  DO BEGIN
    STATUS = 'USINGBY' || gen_id(RPUSING,1); 
    UPDATE tbl1 a SET a.status = :STATUS
      WHERE a.id = :ID;
    SUSPEND;
  END 
END

然后在查询中使用它:

SELECT a.*, p.status FROM tbl1 a LEFT JOIN proc_getstatus(1) p ON a.id = p.id