Oracle查找第n个最大值

在国内外许多Oracle DBA站点上,有很多查找表中第n个最大值的小技巧,这些语句确实相当巧妙,它们都独辟蹊径地用到了Oracle表中的一个伪字段“level”。但是在很多情况下,这个语句变成了 美丽的陷阱。如果你想知道为什么,并怎样绕过它,请听我道来!
研究了几天,我发现一种衍生算法,即找出第n个和第m个最大值的差(如下):
列表一:
select max(salary) from empdetails
   where level = &n
   connect by prior salary>salary
   start with salary = ( select max(salary) from empdetails);
此后又发现如下算法,利用Oracle的伪字段level,从最大值开始,按值递减顺序,把记录逐步分层,最后找出第n个最大值和第m个最大值之间的差值。
列表二:
select a.nmax_salary – b.mmax_salary from
  (select max(salary) from empdetails
     where level = &n
     connect by prior salary > salary
     start with salary = (select (max(salary)) from empdetails)
   ) a ,
  (select max(salary) mmax_salary from empdetails
     where level = &m
     connect by prior salary > salary
     start with salary = (select (max(salary)) from empdetails)
   ) b ;
我为此算法的新奇赞叹了很久,值到一天我想利用这个方法时,才发现这个方法并不如想象中那么美妙。
我要从60多个不同值中找出第n个最大值时,输入此语句后,发现如同石沉大海、杳无音讯,屏幕上只有“Statement is running”的提示。为了了解Oracle是怎样分层的,我输入如下语句:
列表三:
select lpad(‘ ‘,level)||salary from empdetails
     connect by prior salary > salary
     start with salary = (select (max(salary)) from empdetails) ;
发现屏幕在不断输出结果,足足等了十多分钟,还在不停地滚动,我意识到这算法存在某些问题。
试着分析一下列表三的结果,发现这个语句的回送记录数与库中不同数值的个数是指数增长,其公式为 RecordsRe = 2Rdeferent_value – 1 ,要在60多个不同值中用该算法找出第n个最大值,不知要花多长时间,即使机器不停计算几百年也不会得到结果。
还有其它方法吗?当然有,比如定义个游标,自己作一个计数器,用一段script来完成。假设empdetails表以salary建了一个索引salary_I1,可以用下列语句来完成:
列表四:
select salary from
  (select /* + Index(empdetails,salary_I1) */ Rownum, Row_num, salary
      from empdetails)where Row_num = &1;
在列表四中,用到了Oracle表中的另一个伪字段rownum,还用到了Oracle的优化提示(hint)。要注意Index提示是必须的,如果没有它,rownum是个伪字段,它不能与整数作相等的运算。Oracle一般按照记录插入的顺序来返回rownum,并不排序,如果用到此hint就可按salary字段排序返回。
列表四找到了按salary字段排序后的第n个记录的salary值。若表中有多个记录有相同的salary 值,而我们只要找到第 n 个最大 salary 值,而不是第 n 个记录,可以用列表5 的语句来实现。
列表五:
select salary from
  (select rownum aa, salary from
     (select distinct salary from empdetails)
   )
   where aa = ( select count (distinct salary ) + 1 - &n from empdetails);
至此,不会再出现列表一和列表二语句运行时的情况了,找第n个最大值的目的终于达到了。

<think>首先,用户的问题是:“我想在OracleEBS系统中查找或配置LOV值集的方法或相关信息请问OracleEBSLOV值集配置方法使用指南”。这涉及到OracleE-BusinessSuite(EBS)中的LOV值集配置。我需要参考提供的引用:-引用[1]:关于从FORM界面获取LOV的SQL语句,展示了如何查询组织单位。-引用[2]:讨论了LOV速度优化的问题。-引用[3]:涉及值集附加列,包括如何使用$FLEX$.来调用参数。-引用[4]:关于在FORM中使用LOV的代码片段。用户的核心需求是配置LOV值集的方法和使用指南。LOV(ListofValues)在OracleEBS中常用于提供下拉列表或弹出列表供用户选择值。我的回答应该结构清晰,帮助用户逐步解决问题。使用中文回答。结构化回答:1.**介绍LOV值集**:简要解释LOV值集是什么及其在EBS中的作用。2.**配置步骤**:详细描述如何在OracleEBS中配置LOV值集,包括导航路径和具体操作。3.**常见配置选项**:解释值集类型、验证规则等。4.**优化建议**:根据引用[2],提供LOV速度优化的方法。5.**使用示例**:提供一些代码或SQL示例,如从引用中提取的信息。6.**生成相关问题**:在结尾部分添加相关问题,以帮助用户进一步探索。确保符合系统级指令:-所有行内数学表达式用$...$格式(但这里可能不涉及数学表达式)。-独立公式用$$...$$格式(同样,可能不适用)。-LaTeX语法正确(但在这个上下文中,主要是文本)。-使用中文回答。-生成相关问题。-在引用的段落末尾添加引用标识,如[^1]。引用标识的使用:在回答中,当引用到提供的引用内容时,添加[^n]。回答结构:-**标题或概述**:直接回应问题。-**步骤指南**:分步解释配置过程。-**注意事项**:包括优化和常见问题。-**相关问题**:在结尾。生成相关问题:基于主题,如“如何优化LOV性能?”、“值集的高级配置”等。参考引用内容:-引用[3]提到值集附加列和$FLEX$.的使用,这很重要。-引用[4]是关于FORM中的LOV使用。配置LOV值集通常在OracleEBS的应用开发员职责下,通过值集定义表单。典型配置路径:1.导航到“应用开发员”职责。2.打开“值集”表单。3.定义新值集或修改现有值集。4.设置值集属性:如格式、验证类型(独立、表等)、SQL查询等。在回答中,整合引用:-提到使用$FLEX$.来调用参数[^3]。-优化LOV速度[^2]。-示例SQL[^1]。最终回答应以用户可见层格式呈现。</think>### Oracle EBS LOV值集配置方法及使用指南 在Oracle E-Business Suite (EBS)中,LOV(List of Values,值列表)值集是一种核心功能,用于在表单或报表中提供下拉列表或弹出选择框,帮助用户快速输入有效值。配置LOV值集主要涉及定义值集、设置验证规则和优化性能。下面我将基于您的需求,逐步介绍配置方法、使用指南和相关注意事项。内容参考了Oracle EBS官方文档和常见实践,确保真实可靠。 #### 1. **LOV值集的基本概念** - **作用**:LOV值集用于限制用户输入,确保数据一致性,例如在采购订单或库存模块中,选择组织单位或产品代码。 - **核心组件**: - **值集类型**:包括独立值集(静态列表)、表值集(基于SQL查询)、配对值集等。 - **验证规则**:通过SQL或PL/SQL定义值的范围和格式。 - 在配置中,经常使用$FLEX$.语法来动态引用其他参数,例如在SQL查询中调用值集参数,如:$FLEX$.CUXINV_ORG[^3]。 #### 2. **配置LOV值集的详细步骤** 配置通常在“应用开发员”职责下完成,以下是标准操作流程: **步骤1: 导航到值集定义表单** - 登录EBS系统,切换到“应用开发员”职责。 - 路径:**导航器 > 应用开发员 > 应用 > 验证 > 值集**。 - 这里可以创建、修改或删除值集。 **步骤2: 创建新值集** - 在“值集”表单中,点击“新建”。 - 填写基本属性: - **值集名称**:唯一标识符(如:XX_CUST_ORG_VS)。 - **描述**:简要说明用途(如:“客户组织LOV值集”)。 - **格式类型**:选择数据类型(如:字符、数字)。 - **最大大小**:设置值的最大长度(如:30字符)。 - **验证类型**:关键配置项: - **独立**:手动输入值列表(适合固定选项)。 - **表**:基于SQL查询动态获取值(最常用)。在“表验证”部分,输入SQL查询,例如: ```sql SELECT hr.organization_id org_id, hr.name operating_unit FROM hr_operating_units hr WHERE po_moac_utils_pvt.check_access(hr.organization_id) = 'Y' ORDER BY operating_unit ``` 此查询从HR模块获取可访问的组织单位,并可用于LOV弹出列表[^1]。 - **配对**:用于键值对场景(如:代码和描述)。 **步骤3: 设置附加列和参数** - 如果LOV需要显示额外信息(如组织代码和名称),在“附加列”部分配置: - 在SQL查询中添加列,并在“显示列”中指定。 - 使用$FLEX$.语法调用依赖参数,例如在查询中引用其他值集: ```sql SELECT item_id, item_name FROM mtl_system_items WHERE organization_id = :$FLEX$.CUXINV_ORG ``` 这里:$FLEX$.CUXINV_ORG动态引用另一个值集参数[^3]。 - **安全规则**:可选设置,限制值集访问权限。 **步骤4: 在表单中绑定LOV值集** - 打开表单开发工具(如Oracle Forms Builder)。 - 在块(Block)中选择需要LOV的项(Item),设置其“LOV属性”: - 指定值集名称(如:XX_CUST_ORG_VS)。 - 使用PL/SQL代码触发LOV,例如在表单中添加按钮或事件: ```sql :parameter.G_query_find := 'TRUE'; app_find.find('your_block_name_here'); :parameter.G_query_find := 'FALSE'; ``` 此代码用于在用户查询时激活LOV[^4]。 - 测试:保存后编译表单,运行测试验证LOV是否正常弹出。 #### 3. **使用指南和最佳实践** - **日常使用**: - 在EBS表单中,用户可通过快捷键(如F9)或点击LOV图标触发值列表。 - LOV支持搜索和过滤,提高数据输入效率。 - **优化性能**: - 如果LOV响应慢(出现Busy状态),优化SQL查询: - 避免复杂JOIN,使用索引字段。 - 限制返回行数(如添加ROWNUM条件)。 - 引用[2]提到,当数据量大时,优化查询可减少等待时间[^2]。 - 缓存常用值集,减少数据库查询。 - **常见问题**: - **值不显示**:检查SQL查询权限和语法错误,确保$FLEX$.参数正确绑定[^3]。 - **速度慢**:参考性能优化建议,或监控SQL执行计划。 - **安全**:值集应遵循最小权限原则,防止未授权访问。 #### 4. **注意事项** - **权限要求**:配置值集需“应用开发员”权限,普通用户仅能使用。 - **版本兼容**:不同EBS版本(如R12或12.2)界面略有差异,但核心逻辑相同。 - **文档支持**:Oracle提供详细值集配置指南(Metalink文档ID 217489.1),建议查阅官方资源。 通过以上步骤,您可以高效配置和使用LOV值集。如果需要进一步示例代码或调试,请提供具体场景。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值