英文:
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 LATERAL
从dbo.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('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
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, 'A' as user_id, sysdate as "Date" from dual
union select 2, 'B', sysdate from dual)
-- your query:
select maxUserSalary.id as "UserSalary"
from (select trim(COLUMN_VALUE) as item
from xmltable(('"'||replace(:usersalary, ';', '","')||'"'))) userid -- note ';' delimiter
cross apply (
select * from User_Salary usersalary
where usersalary.User_Id = userid.item
order by usersalary."Date" desc
fetch first 1 row only
) maxUserSalary;
If you run this and pass in 'A;B;C'
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,
"UserSalary"
should also have double quotes, not single. -
You can't use
as
in table aliases. -
I removed
into usersalary
, sinceinto
is only used with queries which return a single row, and your query can return multiple rows.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论