SQL Language 之Select

如果你是表或者物化视图的拥有者，或者你拥有select权限，那么你就可以从这些表和物化视图种查询数据。系统权限SELECT  ANY  TABLE是你可以访问任何表，物化视图。另外如果你想执行oracle闪回查询，你必须拥有所有查询对象的查询权限，此外你还必须拥有FLASHBACK对象权限或者FLASHBACK  ANY  TABLE 系统权限。

query_block

  [ subquery_factoring_clause ]
SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list
  FROM { table_reference | join_clause | ( join_clause ) }
         [ , { table_reference | join_clause | (join_clause) } ] ...
  [ where_clause ]
  [ hierarchical_query_clause ]
  [ group_by_clause ]
  [ model_clause ]

subquery_factoring_clause

WITH 
  query_name ([c_alias [, c_alias]...]) AS (subquery) [search__clause] [cycle_clause]
  [, query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]...

WITH   query_name  可以让你给一个子查询块命名，并在别的地方通过query_name来调用它，oracle会把它作为内联视图或者临时表来。你可以使用这个子句在任何top-level的查询语句或者子查询中，并且你可以再主查询或者后面的所有子查询中引用，对于 Recursive Subquery Factoring来说，你可以在子句定义内部引用自己。下面是一些with子句的限制：

• 只能指定一个subquery_factoring_clause在single SQL语句中，当然你可以再定义后的任何地方使用
• 复合查询中，with子句只能用在component语句中的From子句中。
• ordering_column和cycle_mark_c_alias不能使用相同的名字。
• ordering_column和cycle_mark_c_alias不能使已存在的with子句中的别名。

Recursive Subquery Factoring 

如果with内部的子查询引用了自己，那么我们说这是一个递归的子查询因子。这类子查询必须至少包含2个子查询，第一个称为锚成员(anchor member)，第二个称为递归成员(recursive member)。anchor必须出现在recursive之前，并且anchor不能引用query_name，当然anchor可以是多个通过集合操作符(union, union all, intersect, minus)连接起来的

多个query block； recursive必须紧接着anchor，并且必须引用一次query_name。另外anchor和recursive必须通过union all连接起来。 为了在recursive中使用anchor中的列，你必须指定与anchor与recursive中查询字段数目相同个数的column alias在query_name后边。下面是一些针对recursive的限制：

• 不能包含distinct或者group by子句
• 不能包含model子句
• 不能使用聚合函数，但是允许使用分析函数
• 不能包含引用query_name的子查询
• 外连接中，query_name不能作为右边的表

search_clause

{ SEARCH
        { DEPTH FIRST BY c_alias [, c_alias]...
            [ ASC | DESC ]
            [ NULLS FIRST | NULLS LAST ]
         | BREADTH FIRST BY c_alias [, c_alias]...
            [ ASC | DESC ]
            [ NULLS FIRST | NULLS LAST ]
        }
        SET ordering_column
}

search_clause可以让你对结果集进行排序处理。

• BREADTH FIRST BY：优先兄弟节点，然后子女节点，从根到叶子
• DEPTH FIRST BY：子女节点优先，然后兄弟节点，从叶子到根
• 同一层的兄弟节点根据BY后面的字段排序
• c_alias必须来自query_name后的column alias集合
• 如果你想在主查询中继续按照with子句中的排序进行排序，你可以使用set ordering_column，然后在主查询中使用order by ordering_column，ordering_column会被oracle自动的添加到with子句的column alias中。

cycle_clause  

{CYCLE c_alias [, c_alias]...
    SET cycle_mark_c_alias TO cycle_value
    DEFAULT no_cycle_value
}

• c_alias必须来自query_name后的column alias集合，oracle使用他们来检查是否有循环。
• cycle_value和no_cycle_value只能被设定为1个字符的长度的值
• 如果oracle检测到一个循环，oracle会设定列cycle_mark_c_alias的值为cycle_value，并且停止检索改行的子女节点。
• 如果没有检测到循环，cycle_mark_c_alias的值为no_cycle_value。
• cycle_mark_c_alias会被oracle自动的添加到with子句的column alias中。
• 如果忽略cycle_clause并且数据中存在循环，那么oracle将会跑出错误。

循环子查询可以不仅实现层次(start with connect by)查询的功能，还可以实现一些新的功能，比如沿路径求和，求积等。具体特殊用法可以参考☆水『若寒』的帖子 http://blog.sina.com.cn/s/blog_4cef5c7b01016jun.html。

table_reference

{ ONLY (query_table_expression)
| query_table_expression [ pivot_clause | unpivot_clause ]
} [ flashback_query_clause ]
  [ t_alias ]

query_table_experssion

{ query_name
| [ schema. ]
  { table [ partition_extension_clause
          | @ dblink
          ]
  | { view | materialized view } [ @ dblink ]
  } ["sample_clause"]
| (subquery [ subquery_restriction_clause ])
| table_collection_expression
}

{ VERSIONS BETWEEN
  { SCN | TIMESTAMP }
  { expr | MINVALUE } AND { expr | MAXVALUE }
| AS OF { SCN | TIMESTAMP } expr
}

DBMS_FLASHBACKFLASHBACK_TRANSACTION_QUERY中，使用该ID可以查到所有事物所有相关的信息。如果versions和as of子句同时出现，那么结果为在按时as of的结果上执行versions。下面是

• 不能再AS OF子句中使用子查询或者列表达式
• 如果指定了for_update_clause，就不能使用AS OF子句
• 不能再定义物化视图的查询语句中使用AS OF子句
• 临时表，外部表，cluster表的flashback query语句中不能使用versions子句
• 不能再闪回查询视图的语句中使用versions子句，但是可以再定义视图的语句中使用
• 如果在query_table_experssion中指定了query_name，那么不能使用flashback_query子句

SAMPLE [ BLOCK ]
       (sample_percent)
       [ SEED (seed_value) ]

sample_percent用来指定会被包含在sample中的总行或者块的百分比，取值范围为[0.000001,100)，这个值表示每行数据可能被选中的概率而不是oracle将会精确的取得该表总行数的比例。

seed值用来确定2次执行同一个sample可以取得相同的结果，如果seed相同的情况下。忽略seed，每次执行的结果都是变化的。下面是

• 不能在DML的子查询中使用sample子句
• 可以使用在物化视图的基表，容器表，和key preserving view，但不能使用在non key preserving view

WITH { READ ONLY
     | CHECK OPTION 
     } [ CONSTRAINT constraint ]

WITH READ ONLY意味着表或者视图不能更新

WITH CHECK OPTION意味oracle将会检查任何针对表或视图的改变是否被包含在子查询中，如果没有包含将会抛出错误。另外如果在子查询中使用，不能使用在where子句的子查询中。

是check option指定的constraint的名字，如果没有指定，oracle将会自动生成一个SYS_Cn 。

TABLE (collection_expression) [ (+) ]

Table表达式使参数中集合表达式的值可以作为表进行查询和DML操作，集合表达式可以是子查询，列，函数，或者集合构造器。无论何种形式，它必须返回一个嵌套表或者数组类型的值。

Note：不明白+的使用规则。

table_reference PIVOT [ XML ]
  ( aggregate_function ( expr ) [[AS] alias ]
      [, aggregate_function ( expr ) [[AS] alias ] ]...
    pivot_for_clause
    pivot_in_clause
  )

pivot_for_clause

FOR { column
    | ( column [, column]... )
    } 

pivot_in_clause

IN ( { { { expr
         | ( expr [, expr]... )
         } [ [ AS] alias]
       }...
     | subquery
     | ANY [, ANY]...
     }
   )

pivot通过交叉查询将指定的行转换成列，转换过程中按照以下步骤执行：

• 首先会根据指定的聚合函数进行，聚合函数是基于所有没有在pivot子句中引用的列和in子句中指定的集合的值进行分组的。
• 输出交叉表，没有在pivot子句中引用的列将按原样输出，in子句中指定的值将会作为列名，如果指定了聚合函数的别名，列名将为函数别名_in中的值或者别名。

XML，如果指定了XML关键字，输出将被转换成一段XML，in子句中使用具体的值的时候，将不允许使用XML，使用XML的时候，in子句中只能使用ANY或者子查询。当你不知道in子句中有哪些值的时候，这将非常有用。

table_reference UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
( { column | ( column [, column]... ) }
  pivot_for_clause
  unpivot_in_clause
)

unpivot_in_clause

IN
( { column | ( column [, column]... ) }
      [  AS { literal | ( literal [, literal]... ) } ]
        [, { column | ( column [, column]... ) }
          [  AS {literal | ( literal [, literal]... ) } ]
        ]..
)

unpivot的作用刚好与pivot相反，将列转换成行。

• column 指定用来存放数据列的列名
• for 用来指定存放数据说明列的列名
• in 子句指定将会存放在for子句指定的列中的数据，比如分类

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7939908/viewspace-766906/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7939908/viewspace-766906/