SQL Server 到 Oracle – 在 Oracle 中使用 Cross Apply

huangapple go评论76阅读模式
英文:

SQL Server to Oracle - using Cross Apply with Oracle

问题

我明白你的问题。在Oracle中,你可以使用CROSS JOIN LATERAL来模拟SQL Server中的CROSS APPLY,以将数据传递给函数。以下是你可以尝试的方式:

select maxUserSalary.id as "UserSalary"
bulk collect into splitted
from (
    select userid.item
    from dbo.Split(usersalary, ';') as userid
) cross join lateral (
    select *
    from User_Salary usersalary
    where usersalary.User_Id = userid.item
    order by usersalary.Date desc
) maxUserSalary;

这个查询将使用CROSS JOIN LATERALdbo.Split函数返回的userid.item中的每个值,然后将每个值传递给User_Salary表的查询,以获取最新的用户工资记录。结果将收集到splitted变量中。

请注意,我已将maxUserSalary的结果集直接放入了splitted变量中,因为Oracle中的函数不能像SQL Server中的临时表那样直接插入数据。你可能需要在函数中处理这个splitted变量,具体取决于你的需求。

英文:

I have a function that takes primary keys and separates them with commas.

Oracle function:

create or replace function split(
  list in CHAR,
  delimiter in CHAR default ','
) 

return split_tbl as
  splitted split_tbl := split_tbl();
  i pls_integer := 0;
  list_ varchar2(32767) := list;
  
begin
  loop
    i := instr(list_, delimiter);
    if i > 0 then
      splitted.extend(1);
      splitted(splitted.last) := substr(list_, 1, i - 1);
      list_ := substr(list_, i + length(delimiter));
    else
      splitted.extend(1);
      splitted(splitted.last) := list_;
      return splitted;
    end if;
  end loop;
end;

and I have this query in SQL Server that returns the data of this query in the function table

select maxUserSalary.id as 'UserSalary'
into #usersalary
from dbo.Split(@usersalary,';') as userid
cross apply (
	select top 1 * from User_Salaryas usersalary
	where usersalary.User_Id= userid.item
	order by usersalary.Date desc
)  as maxUserSalary

The problem is, I'm not able to use cross apply in Oracle to throw this data into this function that is returning a table.

How can I use cross apply with Oracle to return this data in function?

答案1

得分: 2

你正在使用Oracle 18c,所以可以使用CROSS APPLY语法。Oracle在12c中添加了它(以及LATERAL和OUTER APPLY)。

以下是您逻辑的简化版本:

select us.name
       , us.salary
from table(split('FOX IN SOCKS,THING ONE,THING TWO')) t       
     cross apply (select us.name, max(us.salary) as salary 
                  from user_salaries us
                  where us.name = t.column_value ) us

在db<>fiddle上有一个可工作的演示。

如果这不能完全解决您的问题,请发布一个包含表结构、示例数据和从该示例数据中派生的预期输出的完整问题。

英文:

You're using Oracle 18c so you can use the CROSS APPLY syntax. Oracle added it (as well as LATERAL and OUTER APPLY ) in 12c.

Here is a simplified version of your logic:

select us.name
       , us.salary
from table(split(&#39;FOX IN SOCKS,THING ONE,THING TWO&#39;)) t       
     cross apply (select us.name, max(us.salary) as salary 
                  from user_salaries us
                  where us.name = t.column_value ) us

There is a working demo on db<>fiddle .


If this doesn't completely solve your problem please post a complete question with table structures, sample data and expected output derived from that sample.

答案2

得分: 1

APC已经很好地回答了你的直接问题。另外,我想建议你不要编写自己的函数来执行此操作。有几种现有的解决方案可以将分隔的字符串值拆分成虚拟表,而无需创建自定义类型,并且不会在SQL和PL/SQL引擎之间切换上下文引起性能开销。

-- 示例数据 - 从中删除此部分以在User_Salary表中进行测试
with User_Salary as (select 1 as id, 'A' as user_id, sysdate as "Date" from dual 
                     union select 2, 'B', sysdate from dual)
-- 你的查询:
select maxUserSalary.id as "UserSalary"
from (select trim(COLUMN_VALUE) as item 
      from xmltable(('"'||replace(:usersalary, ';', '","')||'"'))) userid -- 注意';'分隔符
cross apply (
    select * from User_Salary usersalary
    where usersalary.User_Id = userid.item
    order by usersalary."Date" desc
    fetch first 1 row only
) maxUserSalary;

如果运行此代码并将':usersalary'传入为' 'A;B;C',你将获得'1'和'2'的结果。

一些注意事项:

  • 在此示例中,我使用';'作为分隔符,因为这是你的查询使用的分隔符。

  • 我尝试匹配你的表/列名称,但你的列名'Date'是无效的 - 它是Oracle保留关键字,所以必须放在引号中才能成为有效的列名。

  • 作为列标识符,'"UserSalary"' 也应该使用双引号,而不是单引号。

  • 你不能在表别名中使用'as'。

  • 我删除了'into usersalary',因为'into'只用于返回单行的查询,而你的查询可能返回多行。

英文:

I think APC answered your direct question well. As a side note, I wanted to suggest NOT writing your own function to do this at all. There are several existing solutions to split delimited string values into virtual tables that don't require you to create your own custom types, and don't have the performance overhead of context switching between the SQL and PL/SQL engines.

-- example data - remove this to test with your User_Salary table
with User_Salary as (select 1 as id, &#39;A&#39; as user_id, sysdate as &quot;Date&quot; from dual 
                     union select 2, &#39;B&#39;, sysdate from dual)
-- your query:
select maxUserSalary.id as &quot;UserSalary&quot;
from (select trim(COLUMN_VALUE) as item 
      from xmltable((&#39;&quot;&#39;||replace(:usersalary, &#39;;&#39;, &#39;&quot;,&quot;&#39;)||&#39;&quot;&#39;))) userid -- note &#39;;&#39; delimiter
cross apply (
    select * from User_Salary usersalary
    where usersalary.User_Id = userid.item
    order by usersalary.&quot;Date&quot; desc
    fetch first 1 row only
) maxUserSalary;

If you run this and pass in &#39;A;B;C&#39; for :usersalary, you'll get 1 and 2 back.

A few notes:

  • In this example, I'm using ; as the delimiter, since that's what your query used.

  • I tried to match your table/column names, but your column name Date is invalid - it's an Oracle reserved keyword, so it has to be put in quotes to be a valid column name.

  • As a column identifier, &quot;UserSalary&quot; should also have double quotes, not single.

  • You can't use as in table aliases.

  • I removed into usersalary, since into is only used with queries which return a single row, and your query can return multiple rows.

huangapple
  • 本文由 发表于 2020年1月7日 01:54:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/59616733.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定