概述

UBF对象查询语言有两种应用场景——简单条件过滤与复杂报表查询。

简单条件过滤在是针对一个强类型的对象集合指定一个过滤条件，对集合中的每个对象应用此过滤条件进行筛选，结果是所有满足条件的对象所构成的子集合。这种场景主要在实体编程中，用指定的条件查找并返回实体对象的集合。

复杂报表查询基本上是SQL select语句在Object-Oriented中的对应物，它用对象的类名，属性名等概念表达SQL中原来用表名，列名来表达的查询概念，但返回的是一个表格状的数据。你可以使用order by、group by 等SQL类似的语言结构。在实体框架中有EntityDataQuery API，可以获取DataTable 或DataReader来对结果进行操纵。

由于简单条件过滤仅在原单一类型上过滤/排序，因此可以返回该类型的对象集合，但复杂报表查询可以任意组合不同的对象类型的属性作为返回列，这种组合不对应任意已有的类，因此返回结果是弱类型的表格形式。

考虑到与C#风格一致，OQL是大小写敏感的，因此所有的关键字都必须小写。

简单形式兼容ObjectSpace OPath（历史上最早由OPath启发而来）, 表示查询条件，使用时应当指定对应的主实体，即条件是在该主实体上过滤。

OPath语法

支持主实体 + 相对于主实体开始的导航表达式，如：

@select billhead.DocNo + billline.LineNum as DataNo

支持别名形式，如：

@select 2 as ApplyDocType

类型使用::连接的类型全名称表达，如

from UFIDA::U9::AR::Receival::ReceivalBillHead billhead

需要精确控制join的，用别名形式，形如UFIDA::U9::AR::Receival::ReceivalBillHead a 
inner join a.ReceivalBillLine b ，　注意在from子句中的导航表达式只支持两级，即Ａ.B形式，若要Ａ.B.C可写为：    from A a left join a.B b left join b.C c, 在其它地方如select子句，where子句，导航表达式的级数没有限制。

如：

@select 2 as ApplyDocType,

3 as DocTag,billhead.id as DocDataTag,

billhead.DocNo + billline.LineNum  as DataNo,

billhead.ReceivalDate as DataDate,

0 as PriorityGrade,0 as DocOrderNo,

billhead.ReceivalCurrency as DocCurrency,

billhead.RCToFCExchRateType as DocExchangeRateType,

billhead.RCToFCExchRate as DocExchangeRate,

0 as CanEnjoyDiscount,

billline.SettlementMethodClass as SettlementMethod,

billhead.Department.Name as Department,

isnull(billhead.Employee, billhead.ReceivalOperator) as Transactor,

billhead.Receiver as Receiver,

billhead.Project.Name as Project,

null as Item 

from UFIDA::U9::AR::Receival::ReceivalBillHead billhead 

inner join billhead.ReceivalBillLine billline 

where billhead.Organization = 1002006051600036001 

and billhead.HasSubReceive=0 

and billhead.Customer.CustomerCode = 'C01' 

and billhead.ReceivalCurrency = 1105400 

and billhead.ID = 25809101

用户自定义函数与SQL标准函数：

SQL标准函数不用任何特殊处理，直接在OQL语句中当函数使用，只是对应的参数用对象风格的导航表达式来表达。 OQL翻译引擎直接将函数名转换成同名的SQL标准函数。

用户自定义函数需要在如下格式的xml文件(udf.xml)中注册：

其中hello定义如下：

create function hello() 

returns nvarchar(40) as

begin

    return 'Hello Faint!'

end

如果不指定owner, OQL默认dbo

这样：

@select ID as ID , hello(), DocType.ID as DocType 

from UFIDA::U9::CBO::PL::PriceList::PriceList

将被翻译为：

@"select  A1.[ID] as [ID], dbo.hello(), A2.[ID] as [DocType] from  CBO_PriceList as A  inner join [Base_Doc] as A1 on (A.ID = A1.ID)  left join [Base_DocType] as A2 on (A1.[DocType] = A2.[ID])"

OQL条件

简单条件

"Customer.ID = 'ALFKI'", 

Customer是当前实体的一个属性，类型为Customer，ID是Customer的一个属性，在元源数据中有定义，如Customer映射到Customers表， ID映射为CustomerID列。

多Entity Join条件

"Customer.Order.ShipName = ''",

QueryEngine将Order视为Customer类的一个属性，仍然没有即报错。最后一列总是属性，此处ShipName是Order实体的属性。

OPath风格指定条件

"Customer.Order.ID > 100 and Customer.Order.ID != 0",

更复杂的路径表达式:

"Customer.Order.OrderLine.ProductID is not null",

注意is not null是针对ProductID列是否为空。

Between and:

"Order.OrderLine.UnitPrice between 23 and 50",

逻辑或条件 + 参数:

"OrderLine.UnitPrice > 14 or OrderLine.Quantity < @a", 

参数名以@开头，符合通常的变量名命名规则, @是ADO.NET习惯，注意不要与XPath中的元素属性混淆。

in :

"Product.OrderLine.Order.ShipName in ('a', 'b', 'CCCC')",

like & not like:

"Product.OrderLine.Order.Employee.LastName not like 'D%'",

OQL作为完整的查询语言

作为完整的查询语言， OQL支持多列，这些列可以属于不同的Entity, 这扩展了OPath, 因此返回的是一个DataTable, 不是一个强类型。

兼容性问题！！！

由于多项高级SQL风格特性的引入，OQL报表查询必须在前加select关键字，否则无法解析。这是新增功能所引起的唯一不兼容的地方，实际上更有助于意识到OQL两种风格：条件过滤和报表查询的差异。

基本语法为：

( @select [Column]{,[Column]} {from joinClause} {where Condition} {group by columns {having Condition}} order by columns ) (union | union all) (select …)

对类起一个别名，在路径表达式中使用之。

@"select distinct x.Age from Persons x where x.Name = 'Pat'",

没有别名时假设为当前类的属性。

@"select distinct Age from Persons where Name = 'Pat'",

from子句可用逗号分隔多个路径表达式(OPath), 如果之间用别名连接，按inner join处理，没有别名作纽带，则按cross join处理。

@"select c.Address from Persons p, p.Children c",

本例则为cross join, 要对两个集合作笛卡尔积。

@"select p from Persons p, Flowers f where p.Name = f.Name",

@"select * from AuditLog log, Payment payment",

@"select * from User user, LogRecord log where user.UserName = log.UserName",

子查询用在IN Predicate中

@"select s.Salary from Student s where s in (select sec.Assistant from Sections sec)",

子查询用在EXISTS Predicate中

@"select scope1 from Persons, Cities c where exists (select scope2 from Childrens as child)",

左外连接

@"select * from Item item left join item.Bids where item.Description like '%gc%'",

两个左外连接，因为是用别名和OPath表达，OQL Compiler能够产生所需要的SQL on条件。

@"select * from Bid bid left join bid.Item left join bid.Bidder where bid.Amount > 100",

join 就是inner join, 与直接使用OPath的效果一样。总共支持left, right, full, 三种外连接，其中的outer关键字是可选的，inner也是可选的。另外，cross join的效果可直接用关键字表达。

@"select * from Item item join item.Bids bid where item.Description like '%gc%' and bid.Amount > 100",

@"select * from Item i full join i.Seller u where u.UserName = 'steve'",

@"select item.ID from Item item right join item.Bids where bid.Amount > 100",

@"select item.ID, item.Description, bid.Amount from Item item right outer join item.Bids bid where bid.Amount > 100",

@"select * from Cat as cat inner join cat.Mate as mate left outer join cat.Kittens as kitten",

@"select * from Cat as cat cross join cat.Mate.Kittens as kittens",

@"select * from Cat as cat left outer join cat.Kittens as kitten on kitten.BodyWeight > 10.0",

Distinct关键字

@"select distinct item.Description from Item item",

数据库函数upper, 任意这样的数据库函数，用户自定义函数可随意使用

@"select item.StartDate, item.EndDate, upper(item.Name) from Item item",

@Count（*）

@"select count(*) from Item",

@Count

@"select count(item.SuccessfulBid) from Item item",

@Min， Max

@"select min(bid.Amount), max(bid.Amount) from Bid bid where bid.Item.ID = 1",

@"select max(BodyWeight) as max, min(BodyWeight) as min, count(*) as n from Cat cat",

Count(distinct …)

@"select count(distinct item.Description) from Item item",

@Group by

@"select bid.Item.ID, count(bid), avg(bid.Amount) from Bid bid where bid.Item.SuccessfulBid is null group by bid.Item.ID",

@"select user.LastName, count(user) from User user group by user.LastName having user.LastName like 'A%'",

相关子查询（correlated subquery）

@"select * from User u where 10 < (select count(i) from u.Items i where i.SuccessfulBid is not null)",

嵌套的标量子查询（nested scalar subquery）

@"select * from Bid bid where bid.Amount + 1 >= (select max(b.Amount) from Bid b)",

全称子查询

@"select * from Item item where 100 > all(select b.Amount from item.Bids b)",

存在子查询

@"select * from Item item where 100 < any(select b.Amount from item.Bids b)",

@"select * from Item item where 100 = some(select b.Amount from item.Bids b)",

@"select * from DomesticCat as cat where cat.Name = some (select name.NickName from Name as name)",

@"select * from Cat as cat where not exists (select * from Cat as mate where mate.Mate = cat)",

子查询用在in谓词中

@"select * from Item item where 100 in (select b.Amount from item.Bids b)",

@"select * from DomesticCat as cat where cat.Name not in (select name.NickName from Name as name)"

标量子查询用在where条件中            

@"select * from Cat as fatcat where fatcat.Weight > (select avg(cat.Weight) from DomesticCat cat)",

标量子查询用在select子句中

@"select cat.id, (@select max(kit.weight) from cat.kitten kit) from Cat as cat",

@Union 与 @Union @All

@"(@select * from Cat a) @union (@select * from Cat b)",

@"(@select * from Cat a) @union all (@select * from Cat b)",         

子查询用在from子句中作为”@derived class”

@"select * from Cat a left join (select p.Namer from Player p where 3 > count(p.Scores)) b on c.Name = b.Name where c.Age > 3",

对Case When 表达式的支持       

C风格的条件表达式，是表达简单if else逻辑的推荐方式

@"select p.UnitPrice > 100 ? p.UnitPrice : p.UnitPrice * 1.3 as UnitPrice from Products as p",

条件表达式可以嵌套，可用于表达复杂的if elseif else 判定逻辑

@"select p.CategoryID = 1 ? p.UnitPrice * 1.1 : (p.CategoryID = 2

? p.UnitPrice * 1.2 : p.UnitPrice) as DiscountPrice from Products p",

简单CASE表达式

@"select p.UnitPrice as UnitPrice, 

       case p.CategoryID

       when 1 then p.UnitPrice * 1.1

       when 2 then p.UnitPrice * 1.2

       else p.UnitPrice

       end as DiscountPrice from Products as p",

搜索CASE表达式来代替

@"select p.UnitPrice as UnitPrice,

       case 

       when p.CategoryID = 1 then p.UnitPrice * 1.1

       when p.CategoryId = 2 then p.UnitPrice * 1.2

       else p.UnitPrice

       end as DiscountPrice from Products as p",

一个复杂搜索CASE表达式的例子

@"select o.Customer.Name as Customer, count(*) as Orders,

       case 

       when count(*) between 1 and 10 then '*'

       when count(*) between 11 and 20 then '**'

       when count(*) between 21 and 30 then '***'

       else '****'

       end as Rating from Orders o group by o.CustomerID",

对dateadd/datediff/datepart/datename中关键字的支持        

现在可以支持在dateadd/datediff/datepart/datename四个函数调用中用T-SQL风格的关键字day/year/month, etc, 但是这不是一种标准和同一的风格，建议用字符串即单引号的方式．函数名大小写均可。

@"select dateadd(day, 3, '20061031') from Products"

@"select dateadd('day', 3, '20061031') from Products";

@"select datediff(year, '20061031', getdate()) from Products";

@"select datediff(dd, '20061031', getdate()) from Products";

@"select datepart(month, '20061031') from Products";

@"select datepart('month', getdate()) from Products";

@"select datename(Hour, getdate()) from Products";

@"select getdate() from Products";

@"select getutcdate() from Products";

@"select day(getdate()) from Products";

@"select month(getdate()) from Products";

@"select year(getdate()) from Products";

对cast类型转换函数的支持

OQL中cast必须以下述纯函数风格调用

@"select cast('abc', varchar(5)) from Products";

convert函数使用没有特殊限制

@"select convert(bigint, --convert(varbinary(20), "5.1")) from Products";

注意字符串函数中, 由于小写的@left right在@left join 和 @right join中被用作关键字，因此@left right用作字符串函数名时不能用小写，全大写或大小写混合均可。

@"select Left('abc', 2) from Products";

@"select RIGHT('abc', 2) from Products";

对@Update & @Delete语句的支持

@"update UFIDA::U9::CBO::PL::PriceList::PriceList p set p.ID = 10232 where p.ID < 0";

@"update UFIDA::U9::CBO::PL::PriceList::PriceList set ID = 10232 where ID < 0";

@"delete from UFIDA::U9::CBO::PL::PriceList::PriceList p where p.ID is null and 1 = 2";

@"delete from UFIDA::U9::CBO::PL::PriceList::PriceList where ID is null and 1 = 2"

注意

1．@Update @Delete 语句只能用来操纵报表编程中的临时表，其它情形下行为不可预料。

2．@Delete 中 from关键字是必选， 这与SQL Server和Oracle中from可省的习惯不同，但与SQL99标准兼容。

3．OQL中@update/@delete 可使用表别名，SQL不支持。