oracle with as用法_关于Oracle with语句用法说明

引言：

with子句或子查询分解子句是SQL-99标准的一部分，在Oracle 9.2中被添加到Oracle SQL语法中。with子句可以作为内联视图处理，也可以作为临时表解析。后者的优点是，对子查询的重复引用可能更有效，因为可以轻松地从临时表检索数据，而不是由每个引用请求数据。实际生产环境当然也不能乱用，我们需要结合执行计划，10046 autotrace等工具，评估with语句的执行效率。

那么，在Oracle PL/SQL中，WITH子句是一个子查询分解子句，用于创建命名的子查询块这个块充当SQL语句的虚拟表或内联视图。它最初是在Oracle 9.2中引入的。它减少了查询中一个表别名的多个引用的开销。with子句子查询块的作用域是与之关联的SELECT查询。

总结一下，就是一句话：WITH  query_name子句允许将名称分配给子查询块。然后，通过指定查询名称，可以在查询中的多个位置引用子查询块。Oracle通过将查询名作为内联视图或临时表来优化查询。

♡单个别名

WITH AS (subquery_sql_statement)
SELECT FROM ;

♡多个别名

WITH AS
(subquery_sql_statement), AS
(sql_statement_from_alias_one)
SELECT
FROM ,
WHERE ;

♡初始化案例说明：

设想一种场景，我们需要借助一些测试数据join来实现逻辑转储，逻辑内部通过大量union all拼接语句打印测试数据，如下脚本信息：

♡当我们尝试去检索每个员工的经理名字时候，需要不得不再次关联结果，让正常查询变得更加冗长......

♡其实对于逻辑本身是非常简单的，无非就是以别名e作为主表，和另一个拼接的别名m做一次左外关联，行源生成器打印的结果可以看到小沙和我的经理是小dawn，小涛和小杰的经理是甜甜，下面看with语句的实现是如何操作的。

♡到了这里，我们可以重复引用别名e员工表，即使是测试数据发生改变，我们也只需要针对指定子查询内部数据结构做响应调整 即可，为了方便测试我们把临时结果集生辰堆组表。

到目前为止，使用with语句有两个好处，一个是重复的查询内容可以只调用一次，一是增加了代码的阅读性。

Subquery Factoring

♡从Oracle 11gR2开始，子查询因子分解(with)子句得到了增强，包括递归处理。那意味着因子子查询可以引用自身。如果我们尝试在10g中执行此操作，则会出现异常报错ORA-32031：在WITH子句中非法引用查询名称。不是非常具有描述性，但至少比说它更具说明性语法无效，但基本上告诉你，如果引用其他先前的 WITH子句查询，你就可以了，但不是与查询本身相同。
♡递归的基础究竟是什么？让我们从一个简单的PL/SQL示例代码开始：

♡疑惑的地方在于为什么结果集是倒叙排序的，从5开始一直到1结束，PL/SQL实际的内部工作顺序究竟是怎样的，接下来我们反复调用，由于篇幅问题，这里只提供调用逻辑顺序：
♡我们最先尝试使用start=1,end=5调用我们的代码
然后使用start=2,end=5调用自身，接着是start=3,end=5,start=4 end=5,最后start=5 end=5
♡执行返回调用代码内容为：start=4, end=5时候输出start的值为4,
♡执行返回调用代码内容为：start=3,end=5时候输出start的值为3
♡执行返回调用代码内容为：start=3,end=5时候输出start的值为2
♡执行返回调用代码内容为：start=3,end=5时候输出start的值为1
每次存储过程调用时候，它都会在内存中创建一个单独的过程实例，任何存储过程，一旦执行完该过程实例，执行将会返回到调用代码，没有条件组织代码调用自己，会导致oracle最终耗尽内存资源，或者代码fatial,所以在应用程序中，必须有一些限制条件是可以停止代码自身的。

♡这是一个深度的递归层次搜索，在此示例中，代码使用我们的“mgr经理并显示其详细信息，然后获取所有直接员工的列表属于那个经理，并且对于每个人都称该员工为经理。level”(lvl)表示执行堆栈中过程调用的深度，0表示第一次调用，1是0的调用，依此类推。下面我们通过sql语句分层来做个比较：

♡我们得到相同的结果。CONNECT BY查询还具有level伪值，该值同样表示深度级别,在它自己的递归性质内遍历层次结构。
那么，递归的关键特征是什么？
1.递归过程以一些初始值或值开始。(从...开始)
2.递归过程引用自身，通常使用基于当前值的新值(CONNECT BY)
3.递归过程有一些“退出”条件，以防止进一步递归(由CONNECT BY暗示)。
老哥，等等，你在说啥，你分享这篇文章的主题不是with语句吗？这与WITH子句和递归子查询因子分解有什么关系......让我们来看看。

♡重新开始：
基于我们的递归功能，让我们开始构建一个WITH子句，它可以重现我们上面做的EMP层次结构。我们将从START WITH数据开始。

♡对于Subquery Factoring，列的别名应直接在括号中指定查询名称，虽然上面的查询不需要，但在我们进行时将需要它，否则会出现例外。在递归子查询因子术语中，上述“起始”数据被称为Anchor Member。现在我们想要使用这个WITH子查询引用本身，那么我们该怎么做呢？很简单，我们在同一个WITH子句中包含第二个查询(称为递归成员)，并加入这两个查询一起使用UNION ALL语句。

♡此递归成员查询正在执行的操作是查询EMP表中的数据，其中管理器由提供者提供emp_hier子查询中的数据。Oracle非常聪明地理解，在第一个实例中，数据来自Anchor Member查询，然后后续迭代来自与其联合的数据，因此它将继续操作，只要有进一步的EMP记录就会一直递归下去。

♡在这种情况下，我们告诉它我们希望深度优先搜索，按照mgr值的顺序，在新的中设置整体排序值列名为“rn”。我们可以将选择列称为我们喜欢的任何合适的列名，这是自动的添加到我们的结果中，以便我们的最终查询可以按照我们想要的顺序对数据进行排序。
♡除了分层数据处理之外，递归子查询因子子句可以有许多用途; 远远出处日常业务代码，那些固定逻辑思维，比如分割数据，我们可以使用递归子查询因子来分割数据字符串，无论它是数据的分隔值(例如逗号)。或者我们是否只想做一些像文字包装一些文字到一定数量的字符。

♡感受到with语句的强大了是吗，是的，还不够强大，当我们想要替换字符串中的若干个不固定的值时，我们不能使用单个替换语句，我们甚至无法使用更强大的函数，无论是translate replace，比如正则表达式“regexp_replace”，因为每个都需要多个函数调用，彼此嵌套，每个都有单独的搜索条件和替换标准。如果我们只想提供一个单一标准集，可以有效地循环该标准并执行多个替换。虽然它仍然无法在单个函数调用中完成，但我们可以使用递归作为循环机制，允许我们只是提供一套替换标准。

♡别着急，还有，如果我们有一个二进制字符串，我们想得到该字符串的十六进制值(oracle已经提供了一种使用TO_CHAR对NUM进行十六进制的方法，但不是二进制值)。

Entervalue for binary_number: 10100101010100100101
old 9: ,bin as(select '&binary_number' as bin from dual) -- User input
new 9: ,bin as(select '10100101010100100101' as bin from dual) -- User input

♡这是从右侧一次取四个二进制数字并查找那些十六进制数字，构建十六进制值。但是，知道二进制只是2的倍数，我们可以使用TO_CHAR将数字转换为Hex，我们也可以这样做：

♡但是，这受NUMBER数据类型可容纳的最大值的限制，而第一种方法仅受限制提供的二进制字符串的大小。
♡通常sqlplus编写procedure,当not compile时候，我们通常通过show error方式可以看到报错信息内容，例如如下代码：

♡此过程中有两个错误。 首先，我们调用了一个我知道不存在的函数“fred”，其次，我没有在dbms_output行的字符串中转义 我的引号。编译器向我们报告的第一个错误是问题第5行的引号，还没有告诉我们“fred”。如果我们纠正报价问题，我们现在得到如下代码：

♡为了体现with语句的强大，我们把案例做个分解，带着疑问去编程，我们可以将其分解为由括号括号(括号)定义的组成部分 吗？我们从一个递归子查询开始，该子查询解析字符串的“标记”(单个括号“(”和“)”，由周围大括号“{”和“}”定义的值/变量，以及运算符“＆”和“||”)，看起来有点蒙是吗，别着急，来看如下代码：

♡成功了是吗，是的，字符串被剥离了，和我们预期的一样，下面注意看下面的写法：

♡整个表达式分解为括号中定义的组件表达式。级别1是整个表达式，级别2显示级别1中的每个表达式，级别3显示表达式，在任何 2级表达内; 在这种情况下，“({F}＆{G})”表达式位于“({E} ||({F}＆{G}))”内。 请注意，在上面的递归子查询中，Anchor成员提供了几个起始行数据，即所有行，有一个左括号“(”。这使它成为每个 (子)表达式的开始，并通过计算开始和结束括号，因为它将数据连接在一起，它可以确定何时在右括号“)”结束。

路径匹配： 所有前面的例子基本上都归结为使用递归子查询因子来处理字符串; 我们已经在前面的部分中可以看到它如何也可以用于分层查询。 在最后一个例子中，我们会做一些不同的事情; 找到网络中“节点”之间的最短路径。例如，不同地方之间的航班，并希望找到从A到B的 变化最小的航班。这里提供一个思路，让我们来定义我们的数据将会是什么样子，首先创建一张物理表：

♡那么考虑我们需要遍历这个航班网络的路径：1)我们需要跟踪我们已经去过的机场(节点)的路径，所以我们不会回到那里2)我们需要记录每个航班记录以便他们被审计，因此我们可以在最后显示结果3)我们需要知道到达目的地有多少变化，因为我们正在寻找最少的变化4)我们需要知道每个航班的位置(各个目的地)，以便我们可以获取航班当我们在下一个递归步骤到达那里时可以从那里获得。5)我们需要知道我们是否已到达最终目的地。我们还需要知道我们的旅行者想要去哪里。在这种情况下，我们将首先提示用户起始位置及其所需位置：

♡此查询引入了我们根据需要识别的5个元素。有两种可能的航班来自该来源。在这一点上没有变化，我们记录了这些航班的目的地，同时我们已经确定这些目的地是否是最终的目的地。

♡要添加我们查询的递归部分，我们希望它选择来自之前目的地的任何航班，以及哪些航班不要回到我们去过的(任何)机场(我们在“路径”中记录)。随着每个进展通过递归级别，我们增加了“更改”的数量。

♡我们可以看到，我们的“路径”建立了一个(划界的)我们去过的机场列表，因为我们重新启动了航班网络，用于检查我们是否尝试返回我们已去过的任何机场，遵循所有可能的路径，直到我们耗尽了通过网络的可能路径。其中一些路径是明确地确定已到达目的地，而其他人没有(他们无处可去)。要是我们现在整理我们的查询只显示我们需要的数据(航班和更改次数)并过滤它以便我们只检索到达目的地的那些。

♡总结：with语句其实还有很多扩展功能值得我们去深究，如果你是一个开发DBA，或者你一直在苦恼编程逻辑，编程思想包括如何编写高效的sql语句而犯愁，请加入我们吧~

文章好看就点这里