查询程序的结果PL/SQL(Oracle DBMS)

Question

我已经写了一个程序，该程序检查了物业的空置房间的数量。

CREATE OR REPLACE PROCEDURE prop_vacancy_query(
p_property_id       properties.tracking_id%TYPE
)
IS
property_refcur     SYS_REFCURSOR;
v_prop_rooms        properties.num_rooms%TYPE;
BEGIN
OPEN property_refcur FOR 
    'SELECT COUNT(room_status) FROM rooms 
     JOIN properties ON
     properties.property_id = rooms.property_id
     WHERE room_status = :status AND properties.tracking_id = :track_id' USING     'VACANT', p_property_id;
LOOP
    FETCH property_refcur INTO v_prop_rooms;
    EXIT WHEN property_refcur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('Available Rooms: ' || v_prop_rooms);
END LOOP;
CLOSE property_refcur;
END;

我想在触发器中使用此过程，以自动设置属性的状态，当可用的房间返回为0。

时

我尝试了

IF prop_vacancy_query(:NEW.property_status) = 0 THEN
   :NEW.property_status := 'OCCUPIED';
END IF;

但这不起作用。我如何在触发器中使用条件逻辑调用此过程？还是不可能的。

注意：我还担心这可能需要的性能问题，我不确定在DB更新时如何处理自动更新，任何指向我如何解决此问题的指针都将不胜感激。

Answer 1

首先，如果您有一个代码，其唯一目的是运行查询并返回值，请使用FUNCTION而不是PROCEDURE。程序应对数据进行某种操纵。

第二，如果您不需要动态SQL，请不要使用动态SQL。通常，这要慢一些，但更重要的是，编写，支持和调试要困难得多。另外，您将编译时间异常变成运行时间异常，因此您在尝试运行代码之前不会找到语法错误。

您可以大大简化代码

CREATE OR REPLACE FUNCTION get_num_vacancies(
    p_property_id       properties.tracking_id%TYPE
  )
  RETURN NUMBER
IS
  v_prop_rooms        properties.num_rooms%TYPE;
BEGIN
  SELECT COUNT(room_status) 
    INTO v_prop_rooms
    FROM rooms 
         JOIN properties ON
         properties.property_id = rooms.property_id
   WHERE room_status = 'VACANT'
     AND properties.tracking_id = p_property_id;
  RETURN v_prop_rooms;
END;

然后，您可以以最初想要的方式调用该功能

IF prop_vacancy_query(:NEW.property_status) = 0 THEN
   :NEW.property_status := 'OCCUPIED';
END IF;

Answer 2

CREATE OR REPLACE PROCEDURE prop_vacancy_query(
p_property_id       properties.tracking_id%TYPE
status      OUT   NUMBER
)
.
.
.
status := 0
END;
/

并以这种方式打电话

outstatus NUMBER:= -1;
prop_vacancy_query(:NEW.property_id,outstatus);
IF out status = 0 THEN
   :NEW.property_status := 'OCCUPIED';
END IF;