Oracle Apex中的调用过程 [英] Calling procedure in oracle apex

问题描述

我创建了一个带有两个输入的包，如下所示，但是我在测试该包以查看它是否起作用时遇到了麻烦.我想调用该过程并在输入为1和1000的情况下对其进行测试.有人可以提出一种在Oracle Apex中进行测试的方法吗?我不确定是否在包内以正确的方式生成输出，如果我在程序以外的地方测试代码并将其打包，则工作正常.目前，我只想在SQL命令中对其进行测试.

I have created a package with two in inputs as shown below, however I am having trouble with testing the package to see if it works. I want to call the procedure and test it works with the inputs being 1 and 1000. Could anyone suggest a way of testing in Oracle Apex please? I'm not sure if I'm producing the output in the correct way inside the package, if I test the code outside of being a procedure and package it works fine. At the minute I just want to test it in the SQL Command.

包装

CREATE OR REPLACE PACKAGE pl_work_allocation_pkg
IS
  PROCEDURE pl_work_allocation_pp(lv_crime_id       IN  NUMBER,
                                  lv_emp_no         IN  NUMBER,
                                  lv_end_date       OUT DATE,
                                  lv_work_desc      OUT VARCHAR,
                                  lv_police_officer OUT NUMBER,
                                  lv_work_days      OUT NUMBER,
                                  lv_status         OUT VARCHAR);

  FUNCTION GET_WORK_DAYS1(p_work_end_date IN DATE)
    RETURN NUMBER;

  FUNCTION OVERDUE_DAYS1(p_work_end_date IN DATE)
    RETURN VARCHAR;
END;​

包装体

CREATE OR REPLACE PACKAGE BODY pl_work_allocation_pkg
IS
  FUNCTION GET_WORK_DAYS1(p_work_end_date IN DATE) RETURN NUMBER
  IS
    lv_work_days NUMBER(5);
    BEGIN
      lv_work_days := ROUND(p_work_end_date - SYSDATE);
      RETURN lv_work_days;
    END get_work_days1;

  FUNCTION OVERDUE_DAYS1(p_work_end_date IN DATE) RETURN VARCHAR
  IS
    lv_status    VARCHAR(10);
    lv_work_days NUMBER(5);
    BEGIN
      lv_work_days := ROUND(p_work_end_date - SYSDATE);
      IF lv_work_days > 1
      THEN lv_status := 'DUE';
      ELSIF lv_work_days < 1
        THEN lv_status := 'OVERDUE';
      END IF;
      RETURN lv_status;
    END overdue_days1;

  PROCEDURE pl_work_allocation_pp(lv_crime_id       IN  NUMBER,
                                  lv_emp_no         IN  NUMBER,
                                  lv_end_date       OUT DATE,
                                  lv_work_desc      OUT VARCHAR,
                                  lv_police_officer OUT NUMBER,
                                  lv_work_days      OUT NUMBER,
                                  lv_status         OUT VARCHAR)
  IS
    BEGIN
      SELECT
        work_desc,
        lead_police_officer,
        work_end_date
      INTO lv_work_desc, lv_police_officer, lv_end_date
      FROM pl_work_allocation
      WHERE s_reported_crime_id = lv_crime_id
            AND d_emp_id = lv_emp_no;
      lv_work_days := GET_WORK_DAYS(lv_end_date);
      lv_status := OVERDUE_DAYS(lv_end_date);

      dbms_output.PUT_LINE(lv_emp_no || ' is ' || lv_status || ' on case no: ' ||
                           lv_crime_id || ' by ' || lv_work_days || '. Report to ' || lv_police_officer ||
                           ' for ' || lv_work_desc || ' details');

    END pl_work_allocation_pp;
END;

推荐答案

听起来像您想要的

DECLARE
  lv_end_date       date;
  -- Guessing at the length.  You'll be better served using anchored
  -- types everywhere
  lv_work_desc      varchar2(100);  
  lv_police_officer number;
  lv_work_days      number;
  lv_staus          varchar2(10);
BEGIN
 PL_WORK_ALLOCATION_PKG.PL_WORK_ALLOCATION_PP(
    <<first parameter>>,
    <<second parameter>>,
    LV_END_DATE,
    LV_WORK_DESC,
    LV_POLICE_OFFICER,
    LV_WORK_DAYS,
    LV_STATUS );
END;

当然，您可能想对调用返回的值进行处理.

Of course, you probably want to do something with the values that are returned from your call.

这篇关于Oracle Apex中的调用过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！