Oracle With 语句语法及示例

合集下载

oracle中with递归的用法

oracle中with递归的用法在Oracle数据库中，可以使用"WITH"子句进行递归查询。

递归查询是一种特殊的查询模式，它允许我们在查询中引用查询结果本身，从而实现对自身进行迭代操作的功能。

"WITH"子句也被称为"公共表表达式（CTE）"，它可以像创建临时表一样将一个查询结果作为虚拟表存储在内存中。

在递归查询中，我们可以使用"WITH RECURSIVE"子句来标识递归关系和终止条件。

让我们来看一个简单的示例来理解"WITH RECURSIVE"的用法。

假设我们有一个名为"Employees"的表，其中包含员工的ID和上级ID。

我们想要找到每个员工的所有下属。

首先，我们需要定义递归查询的初始条件，即根节点。

我们可以使用"WITH"子句来定义一个初始查询：```WITH RECURSIVE Subordinates AS (SELECT ID, NameFROM EmployeesWHERE ID = 1 --根节点的IDUNION ALLSELECT E.ID, FROM Employees EINNER JOIN Subordinates S ON E.Supervisor_ID = S.ID)```在上面的例子中，我们选择ID为1的员工作为根节点，并将其放入一个名为"Subordinates"的递归查询中。

然后，我们使用UNION ALL将根节点的下属与子查询的结果连接起来，形成一个递归关系。

接下来，我们需要选择递归查询的结果。

在这个例子中，我们只需选择最终结果，即所有下属的姓名：```SELECT NameFROM Subordinates;```这样，我们就得到了根节点下的所有员工的下属姓名。

需要注意的是，在递归查询中，我们必须保证递归路径是有限的，并且存在终止条件，否则查询将无限循环。

Oracle With语句用法

本文参考网址:/reference/with.html/articles/misc/with-clause.php/with.html ------Understanding the WITH Claus参考文档下载地址:/f/21674385.htmlThe WITH query_nam e clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table.(WITH语句只能位于一条语句的最顶级)You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.A WITH clause is really best used when the result of the WITH query is required more than one time in the body of the query such as where one averaged value needs to be compared against two or three times.Restrictions on Subquery Factoring:∙You cannot nest this clause. That is, you cannot specify the subquery_factoring_clause within the subquery of another subquery_factoring_clause. However, a query_name defined in one subquery_factoring_clause can be used in the subquery of any subsequent subquery_factoring_clause.(WITH语句不允许嵌套，之前定义的WITH语句可以在它后面的任何一个WITH语句中使用)∙In a query with set operators, the set operator subquery cannot contain the subquery_factoring_clause, but the FROM subquery can contain the subquery_factoring_clauseWith语句的语法(AS后面的括号是不可以空缺的)1WITH<alias_name>AS (subquery_sql_statement)2SELECT<column_name_list>FROM<alias>;简单的with语句:WITH A AS(SELECT*FROM DUAL)SELECT*FROM A注意，定义了WITH语句必须在后边的查询中使用，否则提示错误信息:1WITH A AS2 (SELECT*FROM DUAL)3SELECT*FROM dual(错误的原因是因为没有使用定义的WITH语句进行查询)两个with语句的语法:1WITH<alias_one>AS2 (subquery_sql_statement),3<alias_two>AS4 (sql_statement_from_alias_one)5SELECT<column_name_list>6FROM<alias_one>, <alias_two>7WHERE<join_condition>;测试例子:1WITH A AS2 (SELECT*FROM DUAL),3 B AS4 (SELECT*FROM DUAL)5SELECT*FROM B, A当在FROM关键子后面没有全部使用定义的WITH语句，他就会提示同上的错误信息: (不可引用在with子句中定于的查询)在视图中使用WITH语句进行连接:1CREATE OR REPLACE VIEW WITH_V AS2WITH DEPT_V AS (SELECT*FROM DEPT),3 EMP_V AS (SELECT*FROM EMP)4SELECT D.DNAME,D.LOC,E.*FROM EMP_V E5LEFT JOIN DEPT_V D6ON D.DEPTNO = E.DEPTNO使用WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。

oracle with as 原理

oracle with as 原理
Oracle的WITH子句是一种SQL语法，它允许在一个查询中创建一个临时的命名结果集，然后可以在后续的查询中使用这个临时结果集。

WITH子句的语法形式如下：
WITH <子查询名称> AS (
SELECT <列1>, <列2>, ...
FROM <表名>
WHERE <条件>
)
SELECT <列1>, <列2>, ...
FROM <子查询名称>
JOIN <其他表>
WHERE <条件>
在这个语法中，<子查询名称>是一个用户定义的临时表或视图的名称，可以在后续的查询中使用。

在WITH子句中可以编写一个SELECT语句，从一个表或者多个表中选择所需的列，并且可以使用任何问题所需的过滤条件。

然后，在后续的查询中，可以使用<子查询名称>来引用这个临时结果集，并且可以与其他表进行联接，以便获取所需的结果。

使用WITH子句的好处是可以将一个复杂的查询分解为多个更简单的子查询，并且可以更直观地理解查询的逻辑。

此外，WITH子句还可以提高查询的性能，因为它允许数据库引擎在执行查询之前计算结果集，并将其存储在一个临时表中，以供后续的查询使用。

这样可以避免多次执行相同的子查询，从而减少了查询的执行时间。

OracleWITH语法解析

Oracle 9i 数据库WITH查询语法小议)2007-03-07 CBSi中国·类型: 转载来源: 中国IT实验室作者:未知责编: 宝良Oracle9i新增了WITH语法功能，可以将查询中的子查询命名，放到SELECT 语句的最前面。

下面看一个简单的例子:通过WITH语句定义了两个子查询SEG和OBJ，在随后的SELECT语句中可以直接对预定义的子查询进行查询。

从上面的例子也可以看出，使用WITH语句，将一个包含聚集、外连接等操作SQL清晰的展现出来。

WITH定义的子查询不仅可以使查询语句更加简单、清晰，而且WITH定义的子查询还具有在SELECT语句的任意层均可见的特点。

即使是在WITH的定义层中，后定义的子查询都可以使用前面已经定义好的子查询:利用WITH定义查询中出现多次的子查询还能带来性能提升。

Oracle会对WITH 进行性能优化，当需要多次访问WITH定义的子查询时，Oracle会将子查询的结果放到一个临时表中，避免同样的子查询多次执行，从而有效的减少了查询的IO数量。

看一个简单的例子，首先构造一张大表，现在要取出大表中ID最小、ID最大以及ID等于平均值的记录，看看普通写法和WITH语句的区别:为了避免第一次执行时物理读的影响，查询结果选取了SQL的第三次运行，物理读为0时的统计信息。

观察执行计划可以看到，先后对T_WITH表进行了4次全表扫描，并产生了5529个逻辑读。

下面看看WITH语句的表现:观察这次的执行计划，发现只对T_WITH表进行了两次全表扫描，而从逻辑读上也可以观察到，这次只产生了2776的逻辑读，正好是上面不使用WITH语句的一半。

通过分析执行计划，Oracle执行了WITH子查询一次，并将结果放到了临时表中，在随后对子查询的多次访问中，都从临时表中直接读取了数据，这应该也是那1个物理读的由来。

通过上面的例子可以看到，将子查询放到WITH语句中不仅可以简化查询语句的结构，对于子查询需要多次执行的情况，还有可能提示查询的性能。

with as 用法 oracle

with as 用法 oracle在Oracle数据库中，WITHAS是一种用于临时创建一个虚拟表并对其进行处理的方法，常用于复杂查询和子查询中。

本文将介绍WITHAS的用法和注意事项。

一、WITHAS语法WITHAS语法的一般格式如下：WITH虚拟表名(列名1数据类型,列名2数据类型,...)AS(子查询或其他语句)SELECT列名1,列名2,...FROM虚拟表名WHERE条件其中，虚拟表名是自定义的名称，列名和数据类型定义了虚拟表中的列，子查询或其他语句定义了虚拟表的内容，SELECT语句用于从虚拟表中选取数据。

二、WITHAS用法示例以下是一个使用WITHAS的示例查询：假设我们有一个员工表employees，其中包含员工姓名、部门和工资等信息。

我们想要查询每个部门的平均工资，可以使用WITHAS来创建一个虚拟表，包含每个部门的员工数量和工资总额。

WITHdepartment_totalsAS(SELECTdepartment,SUM(salary)AStotal_salaryFROMemployeesGROUPBYdepartment),avg_salaryAS(SELECTdepartment,AVG(total_salary)ASavg_salaryFROMdepartment_totalsGROUPBYdepartment)SELECT*FROMavg_salary;在上述示例中，我们首先使用子查询GROUPBYdepartment将employees表中的数据分组，并计算每个部门的总工资。

然后，我们使用WITHAS创建了两个虚拟表department_totals和avg_salary，其中department_totals包含每个部门的总工资，avg_salary包含每个部门的平均工资。

最后，我们从avg_salary中选择了所有列。

三、注意事项使用WITHAS时需要注意以下几点：1.虚拟表名必须是唯一的，不能与表名或别名冲突。

oracle存储过程中的with用法

一、概述Oracle数据库提供了存储过程来帮助用户封装一系列SQL语句，以便于简化数据库操作和提高性能。

在存储过程中，常常会使用到WITH 子句来创建临时的查询结果集，以便在存储过程的后续语句中使用。

本文将重点介绍在Oracle存储过程中的WITH用法及注意事项。

二、WITH子句概述1、WITH子句是一种通用表表达式（CTE，Common Table Expression），用于创建临时的命名查询结果集。

WITH子句通常由关键字WITH和一个或多个子查询组成，可以在后续的SQL语句中像使用表一样引用这些临时结果集。

2、在存储过程中使用WITH子句能够提高可读性和维护性，同时还可以优化查询性能。

三、在Oracle存储过程中使用WITH子句的示例在存储过程中使用WITH子句的一般语法如下：```sqlCREATE OR REPLACE PROCEDURE procedure_nameASBEGINWITH temp_table (column1, column2, ...)AS(SELECT column1, column2, ...FROM table_nameWHERE ...)-- 后续的SQL语句可以引用temp_tableEND;```下面是一个具体的示例，假设我们有一个存储过程，需要根据员工的工资水平来进行统计和分析。

```sqlCREATE OR REPLACE PROCEDURE calculate_salary_statistics ASBEGINWITH high_salary_employees (employee_id, employee_name, salary)AS(SELECT employee_id, employee_name, salaryFROM employeesWHERE salary > xxx)SELECT COUNT(*)INTO high_salary_employee_countFROM high_salary_employees;-- 后续可以继续使用high_salary_employees来编写其他逻辑END;```四、在存储过程中使用WITH子句的注意事项1、WITH子句内的查询结果集只在当前的SQL语句中有效，后续的SQL语句需要继续引用它的话，必须在相同的语句块中。

oracle不支持使用with字句,Oracle版WITH语句的使用

oracle不⽀持使⽤with字句,Oracle版WITH语句的使⽤说起WITH 语句，除了那些第⼀次听说WITH 语句的⼈，⼤部分⼈都觉得它是⽤来做递归查询的。

其实那只是它的⼀个⽤途⽽已，它的本名正如我们标题写的那样，叫做：公共表表达式(Common Table Expression)，从字⾯理解，⼤家觉得它是⽤来⼲嘛的呢？其实，它是⽤来定义临时集合的。

Sql代码WITH TEMP(ID,USER) AS(SELECT ‘01’,’SAM’ FROM DUALUNION ALLSELECT ‘02’,’MIKE’ FROM DUALUNION ALLSELECT ‘03’,’TOM’ FROM DUALUNION ALLSELECT ‘04’,’JANE’ FROM DUAL)SELECT * FROM TEMP;WITH TEMP(ID,USER) AS(SELECT ‘01’,’SAM’ FROM DUALUNION ALLSELECT ‘02’,’MIKE’ FROM DUALUNION ALLSELECT ‘03’,’TOM’ FROM DUALUNION ALLSELECT ‘04’,’JANE’ FROM DUAL)SELECT * FROM TEMP;WITH可以定义多个结果集，中间⽤逗号分隔。

(这种写法更加符合普通思维的逻辑，写出来的代码更加容易理解)WITH 语句是为复杂的查询为设计的，的确是这样的，下⾯我们举个复杂的例⼦，想提⾼技术的朋友可千万不能错过。

考虑下⾯的情况：Sql代码CREATE TABLE USER(NAME VARCHAR(20) NOT NULL,--姓名DEGREE INTEGER NOT NULL,--学历(1、专科2、本科3、硕⼠4、博⼠)STARTWORKDATE date NOT NULL,--⼊职时间SALARY1 FLOAT NOT NULL,--基本⼯资SALARY2 FLOAT NOT NULL--奖⾦);CREATE TABLE USER(NAME VARCHAR(20) NOT NULL,--姓名DEGREE INTEGER NOT NULL,--学历(1、专科2、本科3、硕⼠4、博⼠)STARTWORKDATE date NOT NULL,--⼊职时间SALARY1 FLOAT NOT NULL,--基本⼯资SALARY2 FLOAT NOT NULL--奖⾦);假设现在让你查询⼀下那些1、学历是硕⼠或博⼠2、学历相同，⼊职年份也相同，但是⼯资(基本⼯资+奖⾦)却⽐相同条件员⼯的平均⼯资低的员⼯。

oracle with的用法

oracle with的用法oracle with语句，也叫做“共享子查询”，是oracle 9i引入的新特性。

可以用在创建视图、临时表、索引等。

一、oracle with语句的定义oracle with语句是一种独立的查询结构，用来从数据库中检索数据。

它在语句的前面采用with子句定义共享结果集，可以在后续的查询中作为临时的视图使用。

oracle的with语句被称为子查询（subquery），用select语句将一个查询放置在一个仅能由oracle解析的语句块中，它可以重复使用，而普通的子查询不可以重复使用。

二、oracle with语句语法WITH 子查询别名 (列表)AS(select语句1union allselect语句2...)select * from 子查询别名;三、oracle with语句应用1、识别重复行在某些情况下，我们需要识别出重复行，并从结果集中删除这些重复行，这时可以使用with语句，with语句可以提取最新的记录：with vw_empas(select emp_no, emp_name, dept_id, ROW_NUMBER()OVER(partition by emp_no order by dept_id desc) as seq_nofrom emp)select emp_no, emp_name, dept_idfrom vw_empwhere seq_no=1;2、使用分组函数使用oracle的分组函数时，如果需要过滤掉重复行，可以考虑使用with语句，而不是在查询中使用group by子句。

with vw_emp as(select emp_no, dept_no, sum(salary) over(partition by emp_no) as total_salaryfrom emp)select * from vw_empwhere total_salary>10000;3、加入计算的逻辑行有时候，我们可以用with语句将一些比较复杂的逻辑以语句的形式写出来，并将它们加入到查询中，以便使查询更加清晰。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

Oracle With 语句语法及示例1、一个完整的Oracle With 语句实例：insert into sms_tmp_stop_circlenum_zsdx –将下面查询的结果插入到临时表中WITH selectdata --Oracle With开始的查询语句AS(SELECT TRUNC(createtime) senddate,agentid,srcnum,ROUND(SUM(CASE WHEN result='4' THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS ratioFROMZSDX_SMS_OTHERSEND_DETAILS WHERE agentid LIKE 'zsdx%' AND createtime>=TO_DATE('2010-12-30 00:00:00','yyyy-mm-dd hh24:mi:ss')AND createtime<TO_DATE('2010-12-30 23:59:59','yyyy-mm-dd hh24:mi:ss') GROUP BY TRUNC(createtime),srcnum,agentid)SELECTa2.senddate,a1.agentid,a1.srcnum,a1.passwd,a1.ACCOUNT,a1.createtime,a2.rati o||'%' AS 当天状态返回失败比率,SYSDATE AS 运维停用时间,'李兴宗' AS 停用操作FROM(SELECT agentid,srcnum,passwd,ACCOUNT,createtime FROM ZSDX_SMS_CIRCLENUM WHERE srcnum IN(SELECT srcnum FROMselectdata WHERE ratio>=100) )a1,(SELECT srcnum,ratio,senddate FROMselectdata WHERE ratio>=100) a2WHERE a1.srcnum=a2.srcnum2、详解：Starting in Oracle9i release 2 we see an incorporation of the SQL-99 "WITH clause", a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.The SQL "WITH clause" is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle "WITH clause":? The SQL "WITH clause" only works on Oracle 9i release 2 and beyond.? Formally, the "WITH clause" is called subquery factoring? The SQL "WITH clause" is used when a subquery is executed multiple times ? Also useful for recursive queries (SQL-99, but not Oracle SQL)To keep it simple, the following example only references the aggregations once, where the SQL "WITH clause" is normally used when an aggregation is referenced multiple times in a query.We can also use the SQL-99 "WITH clause" instead of temporary tables. The Oracle SQL "WITH clause" will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.The SQL-99 "WITH clause" is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the "WITH clause" to start our SQL query, defining the aggregations, which can then be named in the main query as if they were "real" tables:WITHsubquery_nameAS(the aggregation SQL statement)SELECT(query naming subquery_name);Retuning to our oversimplified example, let's replace the temporary tables with the SQL "WITH clause" (Note: You may find a faster execution plan by using Global Temporary tables, depending on your release of Oracle):WITH sum_sales AS( select /*+ materialize */ sum(quantity) all_sales from stores ),number_stores AS( select /*+ materialize */ count(*) nbr_stores from stores ),sales_by_store AS( select /*+ materialize */ store_name, sum(quantity) store_sales from store natural join sales )SELECT store_nameFROM store, sum_sales, number_stores, sales_by_storewhere store_sales > (all_sales / nbr_stores);Note the use of the Oracle undocumented "materialize" hint in the "WITH clause". The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the "WITH" clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.It should be noted that the "WITH clause" does not yet fully-functional within Oracle SQL and it does not yet support the use of "WITH clause" replacement for "CONNECT BY" when performing recursive queries.To see how the "WITH clause" is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick's great work "Understanding the WITH Clause" showing the use of the SQL-99 "WITH clause" to traverse a recursive bill-of-materials hierarchyThe SQL-99 "WITH clause" is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the "WITH clause" to start our SQL query, defining the aggregations, which can then be named in the main query as if they were "real" tables:WITHsubquery_nameAS(the aggregation SQL statement)SELECT(query naming subquery_name);Retuning to our oversimplified example, let's replace the temporary tables with the SQL "WITH" clause":Link：/t_with_clause.htmImproving Query Performance with the SQL WITH ClauseOracle9i significantly enhances both the functionality and performance of SQL to address the requirements of business intelligence queries. The SELECT statement's WITH clause, introduced in Oracle9i, provides powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries.Consider a lengthy query which has multiple references to a single subquery block. Processing subquery blocks can be costly, so recomputing a block every time it is referenced in the SELECT statement is highly inefficient. The WITH clause enables a SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times.The WITH clause, formally known as the subquery factoring clause, is part of the SQL-99 standard. The clause precedes the SELECT statement of a query and starts with the keyword "WITH." The WITH is followed by the subquery definition and a label for the result set. The query below shows a basic example of the clause:WITH channel_summary AS( SELECT channels.channel_desc,SUM(amount_sold) AS channel_totalFROM sales, channelsWHERE sales.channel_id = channels.channel_idGROUP BY channels.channel_desc )SELECT channel_desc, channel_totalFROM channel_summaryWHERE channel_total >( SELECT SUM(channel_total) * 1/3FROM channel_summary );This query uses the WITH clause to calculate the sum of sales for each sales channel and label the results as channel_summary. Then it checks each channel's sales total to see if any channel's sales are greater than one third of the total sales. By using the new clause, the channel_summary data is calculated just once, avoiding an extra scan through the large sales table.Although the primary purpose of the WITH clause is performance improvement, it also makes queries easier to read, write and maintain. Rather than duplicating a large block repeatedly through a SELECT statement, the block is localized at the very start of the query. Note that the clause can define multiple subquery blocks at the start of a SELECT statement: when several blocks are defined at the start, the query text is greatly simplified and its speed vastly improved.The SQL WITH clause in Oracle9i significantly improves performance for complex business intelligence queries. Together with the many other SQL enhancements in Oracle9i, the WITH clause extends Oracle's leadership in business intelligence.。