如何在 Oracle 中显示过程之外的过程的结果 [英] How to display the results of a procedure outside of it in Oracle

问题描述

我正在处理一个应用程序，并决定所有查询都是程序.我希望通过这种方式获得性能和易于维护的收益.我们的 DBA 也表示有兴趣这样做.

I am working on an application and made the decision that all the queries would be procedures. I hope to have gains in performance and ease of maintenance by doing it this way. Our DBA's have also expressed interest in having it done this way.

我有一个 HR 表，每晚都会对其执行操作，任何更改都记录在辅助表中.我们不做审计，这些变更记录会一直保留到下一次运行，并向用户展示已经发生的变更.

I have an HR table where operations are performed on it each night, and any changes are recorded in a secondary table. We are not doing auditing, these change records are kept until the next run and show users the changes that have happened.

为了让我的问题更简短，我减少了 HR 中的列数.

To keep my question shorter I have reduced the number of columns in HR.

HR 表 ID、GROUP_NAME 和 GROUP_LEVEL.Drill 表有 ID 和 TYPEVALUE.

The HR table ID, GROUP_NAME, and GROUP_LEVEL. The Drill table has ID and TYPEVALUE.

CREATE OR REPLACE PROCEDURE DOCSADM.DRILL_RECORD_POSITION (
    RECORD_TYPE IN VARCHAR2,
    OUT_ID OUT VARCHAR2,
    OUT_GROUP_NAME OUT VARCHAR2,
    OUT_GROUP_LEVEL OUT VARCHAR2
) AS
BEGIN
    SELECT  HR.ID,  HR.GROUP_NAME,  HR.GROUP_LEVEL
    INTO    OUT_ID, OUT_GROUP_NAME, OUT_GROUP_LEVEL
    FROM HR_POSITION HR JOIN DRILL_POSITION DP ON (HR.ID = DP.ID) WHERE DP.TYPEVALUE = RECORD_TYPE;
END DRILL_RECORD_POSITION;

程序编译没有问题.在应用程序中完成所有工作以链接到过程并提取在这种情况下最终将显示在视图或网页中的值之前，我想要一个快速的小脚本来调用过程然后显示结果我可以在 Oracle 中验证.

The procedure compiles without issue. Before doing all the work in the application to link to the procedure and extract the values which in this case will eventually be displayed in a view or webpage, I wanted to have a quick little script that would call the procedure and then display the results so I can verify in Oracle.

循环

BEGIN
for t in (DRILL_RECORD_POSITION('D', V1,V5,V6))
loop
    --dbms_output.put_line(t.V1 || t.V5 || t.V6);
    dbms_output.put_line(t.OUT_ID);

end loop;
END;
/

光标

DECLARE
V1 HR_POSITION.ID%TYPE;
V5 HR_POSITION.GROUP_NAME%TYPE;
V6 HR_POSITION.GROUP_LEVEL%TYPE;
CURSOR T_CUR IS DRILL_RECORD_POSITION('D', V1,V5,V6);
BEGIN
OPEN T_CUR;
    DBMS_OUTPUT.PUTLINE('START');
    LOOP
        FETCH T_CUR INTO V1,V5,V6;
        EXIT WHEN T_CUR%NOTFOUND;
        DBMS_OUTPUT.PUTLINE(V1||V5||V6);
    END LOOP;
CLOSE T_CUR;
END;

for 循环

DECLARE
V1 HR_POSITION.POSITION_ID%TYPE;
V5 HR_POSITION.GROUP_NAME%TYPE;
V6 HR_POSITION.GROUP_LEVEL%TYPE;
BEGIN
    DBMS_OUTPUT.PUTLINE('START');
    FOR INDEX IN (DRILL_RECORD_POSITION('D', V1,V5,V6))
LOOP
        --DBMS_OUTPUT.PUTLINE(INDEX.ID);
        DBMS_OUTPUT.PUTLINE(INDEX.V1||INDEX.V5||INDEX.V6);
    END LOOP;

结束;

注意:我编辑了列名并在转移到这里时缩短了一些，所以我可能犯了一些错误.

Note: I edited the column names out and shorted some when transferring here so I might have made a few mistakes.

我在网上看到的所有文章都向我展示了如何从原始程序中或使用视图、游标、记录来显示.除非我错了，否则 Eclipse 使用当前表单中的信息不会有任何问题，这就是我以这种方式传递它的原因.因此，我对更改程序不感兴趣，并希望按原样使用它，因为这就是应用程序的工作方式.

All the articles I have seen online show me how to display from within the original procedure or by using views, cursors, records. Unless I am wrong, Eclipse wont have any problems using the information in the current form which is why I am passing it that way. So I am not interested in changing the procedure and would like to work with it as is, since thats how the application will be doing it.

由于这是我为应用程序执行的第一个存储过程，而不是使用应用程序中的即席查询，我没有任何现有示例可供使用，这就是为什么我相信结果会正常工作，因为它应该与临时使用的格式相同.

As this is the first of the stored procedures I am doing for the application, instead of using adhoc queries from the application, I dont have any existing examples to work from, which is why I believe the results will work fine, because it should be the same format the adhoc ones use.

更新:

在其中一条评论中，有人指出我应该是解决方案.其下的另一个解决方案证实了这一点.

In one of the comments, I was pointed to what should have been a solution. This was confirmed by another solution that was under it.

我一直收到错误

ORA-01422:精确获取返回的行数超过请求的行数

ORA-01422: exact fetch returns more than requested number of rows

所以我返回了多行，但这是我的期望和正在发生的事情.我似乎无法弄清楚如何显示结果.

So Im returning multiple rows, but that is my expectation and what is happening. I just cant seem to figure out how to display the results.

推荐答案

要测试您展示的过程，您可以执行以下操作:

To test the procedure you showed, you would do something like:

declare
  l_id hr_position.id%type;
  l_group_name hr_position.group_name%type;
  l_group_level hr_position.group_level%type;
begin
  drill_record_position('D', l_id, l_group_name, l_group_level);
  dbms_output.put_line(l_id ||':'|| l_group_name ||':'|| l_group_level);
end;
/

但那 - 或者更具体地说，你的过程 - 只有当查询的结果集中对于传入的值类型只有一行时才有效.似乎您期望返回多行(这会得到太多行)，但也可能没有(这会导致找不到数据).

But that - or more specifically, your procedure - only works if there is exactly one row in the query's result set for the passed-in value type. It seems you're expecting multiple rows back (which would get too-many-rows), but there could also be non (which would get no-data-found).

所以看起来您的问题应该是关于如何编写您的程序，以便它与您尝试过的检索/测试方法之一一起使用.

So really it seems like your question should be about how to write your procedure so it works with one of the retrieval/test methods you tried.

如果你的过程需要返回多行，那么它可以使用引用游标，例如:

If your procedure needs to return multiple rows then it can use a ref cursor, e.g.:

create or replace procedure drill_record_position (
  p_record_type in varchar2,
  p_ref_cursor out sys_refcursor
)
as
begin
  open p_ref_cursor for
    select hr.id, hr.group_name, hr.group_level
    from hr_position hr
    join drill_position dp
    on hr.id = dp.id
    where dp.typevalue = p_record_type;
end drill_record_position;
/

然后您可以使用以下内容进行测试:

which you could then test with something like:

declare
  l_ref_cursor sys_refcursor;
  l_id hr_position.id%type;
  l_group_name hr_position.group_name%type;
  l_group_level hr_position.group_level%type;
begin
  drill_record_position('D', l_ref_cursor);
  loop
    fetch l_ref_cursor into l_id, l_group_name, l_group_level;
    exit when l_ref_cursor%notfound;
    dbms_output.put_line(l_id ||':'|| l_group_name ||':'|| l_group_level);
  end loop;
  close l_ref_cursor;
end;
/

您也可以将其作为函数来执行，这可能更容易在您的应用程序中使用:

You can also do that as a function, which might be easier to work with from your application:

-- drop procedure drill_record_position;

create or replace function drill_record_position (p_record_type in varchar2)
return sys_refcursor as
  l_ref_cursor sys_refcursor;
begin
  open l_ref_cursor for
    select hr.id, hr.group_name, hr.group_level
    from hr_position hr
    join drill_position dp
    on hr.id = dp.id
    where dp.typevalue = p_record_type;
  return l_ref_cursor;
end drill_record_position;
/

declare
  l_ref_cursor sys_refcursor;
  l_id hr_position.id%type;
  l_group_name hr_position.group_name%type;
  l_group_level hr_position.group_level%type;
begin
  l_ref_cursor := drill_record_position('D');
  loop
    fetch l_ref_cursor into l_id, l_group_name, l_group_level;
    exit when l_ref_cursor%notfound;
    dbms_output.put_line(l_id ||':'|| l_group_name ||':'|| l_group_level);
  end loop;
  close l_ref_cursor;
end;
/

您也可以使用集合和流水线函数来做到这一点，这需要更多的设置工作:

You coudl also do this with collections and a pipelined function, which is more work to set up:

create type t_drill_obj as object (
   -- use your real data types...
  id number,
  group_name varchar2(10),
  group_level number
)
/

create type t_drill_tab as table of t_drill_obj
/

create or replace function drill_record_position (p_record_type in varchar2)
return t_drill_tab pipelined as
begin
  for l_row in (
    select t_drill_obj(hr.id, hr.group_name, hr.group_level) as obj
    from hr_position hr
    join drill_position dp
    on hr.id = dp.id
    where dp.typevalue = p_record_type
  )
  loop
    pipe row (l_row.obj);
  end loop;
  return;
end drill_record_position;
/

但您可以将其作为另一个查询的一部分调用，甚至可以根据需要加入结果:

but you could call it as part of another query, and even join tot he result if you needed to:

select * from table(drill_record_position('D'));

这篇关于如何在 Oracle 中显示过程之外的过程的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！