时间戳

首页 > 解决方案 > 递归地向数组添加值 pl/sql

oracle - 递归地向数组添加值 pl/sql

问题描述

我有两个表 INFOAGENT（代理信息），HIERARCHIAGENT（每个代理的分层附件）我希望创建一个程序（pl/sql），它在数组中递归地添加公司的 paramidal 层次结构，直到代理代码在参数中传递，并返回以 CEO 开头的数组

这是数据。

INFOAGENT 
ID           NAME
P001         AAAA
P002         BBBB
P003         CCCC
P004         DDDD
P005         EEEE
P006         FFFF

HIERARCHIAGENT 
INCHARGE     IDAGENT
P001         P002
P001         P003         
P002         P005
P003         P006
P003         P004

我试过这个

type hierarchie is table of INFOAGENT.ID%TYPE index by binary_integer;
create or replace PROCEDURE AGENTHIERARCHIE ( codeagent INFOAGENT.ID%TYPE,
                                      arrayresponse out hierarchie%TYPE)
AS

DECLARE

arrayresponse hierarchie;

cursor cur is SELECT ID FROM INFOAGENT where ID= codeagent

begin 

FOR rec_agent in cur LOOP
// i don't know how to do it here 

end loop;

EXCEPTION
    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE(replace(sqlerrm,'ORA-','ORA_'));

END AGENTHIERARCHIE ;

标签： oracleplsql

解决方案

我认为这可能符合您的要求。至少，它可以帮助您入门。

CREATE OR REPLACE PACKAGE hierarcy_defs AS
  TYPE hierarcy IS TABLE OF INFOAGENT.ID%TYPE INDEX BY BINARY_INTEGER;
END hierarcy_defs;

CREATE OR REPLACE PROCEDURE agenthierarchy (
  codeagent     IN     infoagent.id%TYPE,
  p_index       IN     BINARY_INTEGER,
  arrayresponse IN OUT hierarcy_defs.hierarcy)
AS
  i BINARY_INTEGER;
  l_codeagent infoagent.id%TYPE;
BEGIN
  i := p_index;

  arrayresponse(i) := codeagent;

  BEGIN
    SELECT incharge
    INTO   l_codeagent
    FROM   hierarchiagent
    WHERE  idagent = codeagent;

    i := i + 1;

    agenthierarchy(l_codeagent, i, arrayresponse);
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
      FOR j IN arrayresponse.FIRST .. arrayresponse.LAST
      LOOP
        DBMS_OUTPUT.PUT_LINE(arrayresponse(j));
      END LOOP;
  END;  
EXCEPTION 
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(REPLACE(SQLERRM,'ORA-','ORA_'));
END agenthierarchy;

用于测试的匿名块：

DECLARE
  l_arrayresponse hierarcy_defs.hierarcy;
BEGIN
  agenthierarchy('P006', 1, l_arrayresponse);
END;

推荐阅读