Oracle中的表变量样式实体 [英] Table Variable Style Entities in Oracle

问题描述

我一直在寻找Oracle中某种类似于SQL Server中的表变量的东西.我发现人们在SO上问这样的问题，人们总是说是的，Oracle就是这样"，但是示例表明这些实体根本不像SQL Server.有人可以告诉我如何在Oracle中执行以下简单的TSQL解决方案吗?

I've been looking around for awhile for something in Oracle that acts like a table variable in SQL Server. I've found people asking questions like this here on SO and people always say "Yes, Oracle has that" but the examples show that the entities are not like SQL Server at all. Can someone show me how to perform the below simple TSQL solution in Oracle?

declare @users table (
    ID int,
    Name varchar(50),
    Age int,
    Gender char(1)
)

;with users as (
    select 1001 as ID, 'Bob' as Name, 25 as Age, 'M' as Gender
    union
    select 1021 as ID, 'Sam' as Name, 29 as Age, 'F'
)
insert into @users (ID, Name, Age, Gender)
select *  from users

declare @grades table (
    UserID int,
    ClassID int,
    Grade int
)

;with grades as (
    select 1001 as UserID , 120 as ClassID, 4 as Grade
    Union
    select 1001 as UserID , 220 as ClassID, 2 as Grade
    Union
    select 1021 as UserID , 130 as ClassID, 4 as Grade
    Union
    select 1021 as UserID , 230 as ClassID, 4 as Grade
    Union
    select 1021 as UserID , 340 as ClassID, 2 as Grade
)
insert into @grades
select * from grades

select u.ID, u.Name, GPA = AVG(cast(g.grade as decimal))
from @users u
    inner join @grades g on u.ID=g.UserID
group by u.ID, u.Name

推荐答案

某些答案可能会告诉您Oracle具有表变量，并且在一定程度上起作用.但是，大多数答案都会告诉您，您根本不应该在Oracle中执行此操作.根本没有必要.

Some answers may tell you that Oracle has table variables, and it does to a certain extent. However, most answers will tell you that you should not be doing this in Oracle at all; there's simply no need.

在您的情况下，我将仅使用CTE:

In your case I would simply use a CTE:

with users as (
    select 1001 as ID, 'Bob' as Name, 25 as Age, 'M' as Gender from dual
    union
    select 1021 as ID, 'Sam' as Name, 29 as Age, 'F' from dual
          )
 , grades as (
    select 1001 as UserID , 120 as ClassID, 4 as Grade from dual
    Union
    select 1001 as UserID , 220 as ClassID, 2 as Grade from dual
    Union
    select 1021 as UserID , 130 as ClassID, 4 as Grade from dual
    Union
    select 1021 as UserID , 230 as ClassID, 4 as Grade from dual
    Union
    select 1021 as UserID , 340 as ClassID, 2 as Grade from dual
           )
select u.ID, u.Name, AVG(g.grade) as gpa
  from users u
  join grades g on u.ID = g.UserID
 group by u.ID, u.Name

更新:很久以来，我一直试图得到的答案是在Ben的评论中，以下是我的评论: 没有变量，您可以即时创建并连接到标准SQL @wcm中的其他表，是的.可以创建许多不同类型的对象，这些对象可以使您执行此操作，但不完全是就像在T-SQL中一样".

UPDATE: The answer I've been trying to get for a long time is in Ben's comment below which I include here: "There is no variable, which you can create on the fly and join to other tables in standard SQL @wcm, yes. There is a number of different type of objects that can be created that will allow you to do this, but not exactly as you would in T-SQL".

这篇关于Oracle中的表变量样式实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！