使用功能获取Oracle中的帐户详细信息 [英] Get Account details in Oracle using function

问题描述

我们有两个表帐户和部门，其中accunt级别1将来自帐户表，而级别2和3帐户级别则来自部门.桌子

we have two tables account and Dept where acocunt level 1 will be coming from acount table and level 2 and level 3 account level coming from dept. table

现在，基于输入，我们需要找到两个表中的accountkey，并且应该根据源帐户ID，数据库ID和帐户级别显示哪个accountkey.

Now based on the input we need to find the accountkey which is in both tables and it should display which accountkey based on source account id, db id and account level.

输入: [112].[22].[1],[113].[23].[1],[245].[21].[2],[289].[20].[2],[301].[21].[3], [304].[20].[3]

Input: [112].[22].[1],[113].[23].[1],[245].[21].[2],[289].[20].[2],[301].[21].[3], [304].[20].[3]

112-帐户ID(1级帐户ID或2级帐户ID或3级帐户ID)，
22-数据库ID，
1或2或3个帐户级别

112 - Account id(level1 accountid or leve2 accountid or level3 accountid),
22 - Database id,
1 or 2 or 3 -account level

例如:

帐户表:

Eg:

Account table :

DIM_CUST_KEY level1 account id databaseid 1123 112 22 1234 113 23

DIM_CUST_KEY level1 account id databaseid 1123 112 22 1234 113 23

部门表:

DIM_CUST_KEY level2 account id level3 account id databaseid 1587 245 301 21 1576 289 304 20

DIM_CUST_KEY level2 account id level3 account id databaseid 1587 245 301 21 1576 289 304 20

`create or replace function get_accountdetails (par_input in varchar2) return 
varchar2 is
v_ret varchar2(20) := '';
begin
select dim_cust_key from dim_cust_acnt a
inner join dim_cust_dept d using (dim_cust_key)

where ( 1 = regexp_substr(par_input, '\d+', 1, 3)  
       and regexp_substr(par_input, '\d+', 1, 1) = level1_account_id 
       and regexp_substr(par_input, '\d+', 1, 2) = a.database_id )
or    ( 2 = regexp_substr(par_input, '\d+', 1, 3)  
      and regexp_substr(par_input, '\d+', 1, 1) = level2_account_id 
      and regexp_substr(par_input, '\d+', 1, 2) = d.database_id )
or    ( 3 = regexp_substr(par_input, '\d+', 1, 3)  
      and regexp_substr(par_input, '\d+', 1, 1) = level3_account_id 
      and regexp_substr(par_input, '\d+', 1, 2) = d.database_id )
  return v_ret;
end;`

推荐答案

问题描述似乎有些混乱，请格式化问题并使其更具可读性.无论如何，解决方案很简单.您只需要一个if语句， 在哪里，根据级别，您在第一张或第二张表格以及适当的列中进行搜索:

You seems to be a little chaotic in problem description, please format your question and make it more readable. Anyway the solutions is simple. You only need one if statement, where, depending on level, you search in first or second table and proper column:

create or replace function get_accountdetails (par_input in varchar2) return varchar2 is
  v_aid varchar2(10);
  v_db  varchar2(10);
  v_lvl varchar2(10);
  v_ret varchar2(20) := '';
begin
  v_aid := regexp_substr(par_input, '\d+', 1, 1);
  v_db  := regexp_substr(par_input, '\d+', 1, 2);
  v_lvl := regexp_substr(par_input, '\d+', 1, 3);

  if v_lvl = 1 then
     select dim_cust_key
       into v_ret
       from dim_cust_acnt
       where level1_account_id = v_aid and database_id = v_db;
  elsif v_lvl = 2 then
     select dim_cust_key
       into v_ret
       from dim_cust_dept
       where level2_account_id = v_aid and database_id = v_db;
  else
     select dim_cust_key
       into v_ret
       from dim_cust_dept
       where level3_account_id = v_aid and database_id = v_db;
  end if;
  return v_ret;
end;

以下是表和示例函数调用:

Here are tables and sample function calls:

create table dim_cust_acnt (dim_cust_key, level1_account_id, database_id) as (
    select 1123, 112, 22 from dual union all
    select 1234, 113, 23 from dual );

create table dim_cust_dept (dim_cust_key, level2_account_id, level3_account_id, database_id) as (
    select 1587, 245, 301, 21 from dual union all
    select 1576, 289, 304, 20 from dual);

select get_accountdetails('[112].[22].[1]') from dual;     -- result: 1123
select get_accountdetails('[289].[20].[2]') from dual;     -- result: 1576
select get_accountdetails('[301].[21].[3]') from dual;     -- result: 1587

请使用真实数据中使用的正确列名称，并根据需要调整变量类型和长度.我认为您也可以使用一个联合查询，不需要特殊功能，如下所示.我使用full join，因为您的示例不包含匹配的行.只需简单的join就足够了.

Please use proper columns names which you have in your real data, and adjust variable types and length if needed. I think you could also use one joined query, no special function needed, something like below. I used full join, because your examples do not contain matching rows. Probably simple join will be enough.

with t(par_input) as (select '[112].[22].[1]' from dual)
select dim_cust_key
  from dim_cust_acnt a
  full join dim_cust_dept d using (dim_cust_key) 
  cross join t
  where ( 1 = regexp_substr(par_input, '\d+', 1, 3)  
          and regexp_substr(par_input, '\d+', 1, 1) = level1_account_id 
          and regexp_substr(par_input, '\d+', 1, 2) = a.database_id )
     or ( 2 = regexp_substr(par_input, '\d+', 1, 3)  
          and regexp_substr(par_input, '\d+', 1, 1) = level2_account_id 
          and regexp_substr(par_input, '\d+', 1, 2) = d.database_id )
     or ( 3 = regexp_substr(par_input, '\d+', 1, 3)  
          and regexp_substr(par_input, '\d+', 1, 1) = level3_account_id 
          and regexp_substr(par_input, '\d+', 1, 2) = d.database_id )

结果:

DIM_CUST_KEY
------------
        1123

如果删除with和cross join部分并添加into子句，则可以在函数中使用此查询而不是if语句.

If you remove with and cross join parts and add into clause then you can use this query in function instead of if statement.

抱歉，我最近没有看Stack Overflow.这是两个如何编写函数的示例:

Sorry for delay, I did not look at Stack Overflow lately. Here are two examples how to write your functions:

此函数返回串联的字符串:

This function returns concatenated string:

select get_details_1('[112].[22].[1],[289].[20].[2],[301].[21].[3]') as list from dual;

LIST
------------------
1123,1576,1587

第二个函数进行了流水线处理，并以字符串的预定义集合形式返回数据，因此值位于单独的行中.

And second function is pipelined and returns data as predefined collecton of strings, so values are in separate rows.

select column_value 
  from table(get_details_2('[112].[22].[1],[289].[20].[2],[301].[21].[3]'));

COLUMN_VALUE
------------
        1123
        1576
        1587

您还可以首先解析所有输入数据，将它们存储在某个集合中，然后在一个查询中使用批量收集.有很多解决方案和可能性，就我个人而言，我将使用流水线函数，但这取决于您需要哪种输出形式(集合或串联字符串).您也可以添加begin ... end块并处理异常when no_data_found.您可以随后显示特殊信息或中断执行，这取决于在这种情况下预期的行为.

You can also parse all input data at first, store them in some collection and then use bulk collect in one query. There is many solutions and possibilities, personally I would use pipelined function, but it depeneds on what form of output you need (collection or concatenated string). Also you can add begin ... end block and handle exception when no_data_found. You can present special info then or break execution, it depends on what behavior is expected in such situation.

功能1:

create or replace function get_details_1 (par_input in varchar2) return varchar2 is
    v_aid varchar2(10);
    v_db  varchar2(10);
    v_lvl varchar2(10);
    v_ret varchar2(20);
    v_all varchar2(200) := '';

    i_cnt int := 0;
begin
    loop
        v_aid := regexp_substr(par_input, '\d+', 1, i_cnt + 1);
        v_db  := regexp_substr(par_input, '\d+', 1, i_cnt + 2);
        v_lvl := regexp_substr(par_input, '\d+', 1, i_cnt + 3);
        i_cnt := i_cnt + 3;
    exit when v_aid is null;
        select dim_cust_key
          into v_ret
          from dim_cust_acnt a
          full join dim_cust_dept d using (dim_cust_key)
          where (v_lvl = 1 and level1_account_id = v_aid and a.database_id = v_db)
             or (v_lvl = 2 and level2_account_id = v_aid and d.database_id = v_db)
             or (v_lvl = 3 and level3_account_id = v_aid and d.database_id = v_db);
       v_all := v_all||','||v_ret;
  end loop;
  return ltrim(v_all, ',');
end;

功能2:

create or replace function get_details_2 (par_input in varchar2) 
    return sys.odcinumberlist pipelined is

    v_aid varchar2(10);
    v_db  varchar2(10);
    v_lvl varchar2(10);
    v_ret varchar2(20);
    i_cnt int := 0;
begin
    loop
        v_aid := regexp_substr(par_input, '\d+', 1, i_cnt + 1);
        v_db  := regexp_substr(par_input, '\d+', 1, i_cnt + 2);
        v_lvl := regexp_substr(par_input, '\d+', 1, i_cnt + 3);
        i_cnt := i_cnt + 3;
    exit when v_aid is null;
        select dim_cust_key
          into v_ret
          from dim_cust_acnt a
          full join dim_cust_dept d using (dim_cust_key)
          where (v_lvl = 1 and level1_account_id = v_aid and a.database_id = v_db)
             or (v_lvl = 2 and level2_account_id = v_aid and d.database_id = v_db)
             or (v_lvl = 3 and level3_account_id = v_aid and d.database_id = v_db);
       pipe row (v_ret);
  end loop;
  return;
end;

这篇关于使用功能获取Oracle中的帐户详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！