Chinaunix首页 | 论坛 | 博客
  • 博客访问: 980960
  • 博文数量: 358
  • 博客积分: 8185
  • 博客等级: 中将
  • 技术积分: 3751
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-15 16:27
个人简介

The views and opinions expressed all for my own,only for study and test, not reflect the views of Any Company and its affiliates.

文章分类

全部博文(358)

文章存档

2012年(8)

2011年(18)

2010年(50)

2009年(218)

2008年(64)

我的朋友

分类: Oracle

2009-07-10 16:13:30

Chapter 7: Advanced Queries
 214~280
 ■Set Operators:
using a set operator: The number of
columns and the column types returned by the queries must match, although the column names
may be different
・UNION ALL:Returns all the rows retrieved by the queries, including duplicate rows
・UNION:Returns all non-duplicate rows retrieved by the queries.
・INTERSECT:Returns rows that are retrieved by both queries.
・MINUS:Returns the remaining rows when the rows retrieved by the second query are subtracted from the rows retrieved by the first query.
■Translate() Functions
TRANSLATE(x, from_string, to_string)
eg:SQL> SELECT TRANSLATE(12345,54321,67890)
  2  FROM dual;
TRANS
-----
09876
SQL>
■DECODE() Function
DECODE(value, search_value, result, default_value)
DECODE() is an old Oracle proprietary function, and therefore you should use CASE expressions instead if you are using Oracle Database 9i and above (you will learn about CASE in the next section). The DECODE() function is mentioned here because you may encounter it when using older Oracle databases.
CASE Expression
①CASE search_expression
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2
    ...
    WHEN expressionN THEN resultN
    ELSE default_result
 END
②CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   ...
   WHEN conditionN THEN resultN
   ELSE default_result
 END
■Hierarchical Queries
Syntax:
 SELECT [LEVEL], column, expression, ...
 FROM table
 [WHERE where_clause]
 [[START WITH start_condition] 
 [CONNECT BY PRIOR prior_condition]];
・LEVEL is a pseudo column that tells you how far into a tree you are.
・start_condition specifies where to start the hierarchical query.
・prior_condition specifies the relationship between the parent and child rows.You must specify a CONNECT BY PRIOR clause when writing a hierarchical query.
eg:
①Using the LEVEL Pseudo Column
SQL> l
  1  select level,
  2  lpad(' ',2*level-1)||first_name||' '||last_name as employee
  3  from more_employees
  4  start with last_name ='Jones'
  5* connect by prior employee_id =manager_id
SQL> /

     LEVEL EMPLOYEE
---------- -------------------------
         1  Susan Jones
         2    Jane Brown
         3      Henry Heyson
         2    John Grey
         3      Jean Blue
②Formatting the Results from a Hierarchical Query
  1  select level,
  2  lpad(' ',2*level-1)||first_name||' '||last_name as employee
  3  from more_employees
  4  start with last_name like'%n%'
  5* connect by prior employee_id =manager_id
SQL> /

     LEVEL EMPLOYEE
---------- -------------------------
         1  Ron Johnson
         2    Fred Hobbs
         2    Rob Green
         1  Susan Jones
         2    Jane Brown
         3      Henry Heyson
         2    John Grey
         3      Jean Blue
         1  Rob Green
         1  Jane Brown
         2    Henry Heyson
         1  Henry Heyson
         1  Keith Long
         1  Doreen Penn
③Using a Subquery in a START WITH Clause
SQL> l
  1  select level,
  2  lpad(' ',2*level-1)||first_name||' '||last_name as employee
  3  from more_employees
  4  start with employee_id =(
  5  select employee_id
  6  from more_employees
  7  where first_name='Kevin'
  8  and last_name='Black'
  9  )
 10* connect by prior employee_id=manager_id
SQL> /

     LEVEL EMPLOYEE
---------- -------------------------
         1  Kevin Black
         2    Keith Long
         2    Frank Howard
         2    Doreen Penn
  1  select level,
  2  lpad(' ',2*level-1)||first_name||' '||last_name as employee
  3  from more_employees
  4  start with first_name='Kevin'
  5  and last_name='Black'
  6* connect by prior employee_id=manager_id
SQL> /

     LEVEL EMPLOYEE
---------- -------------------------
         1  Kevin Black
         2    Keith Long
         2    Frank Howard
         2    Doreen Penn
⑤Including Other Conditions in a Hierarchical Query
You can include other conditions in a hierarchical query using a WHERE clause.
SQL> l
  1  select level,
  2  lpad(' ',2*level-1)||first_name ||' '||last_name as employess,
  3  salary
  4  from more_employees
  5  where salary <=50000
  6  start with employee_id =1
  7  connect by prior employee_id =manager_id
  8*
SQL> /

     LEVEL EMPLOYESS                          SALARY
---------- ------------------------------ ----------
         3      Rob Green                      40000
         3      Jane Brown                     45000
         4        Henry Heyson                 30000
         3      John Grey                      30000
         4        Jean Blue                    29000
         3      Keith Long                     50000
         3      Frank Howard                   45000
         3      Doreen Penn                    47000

已选择8行。
⑥Eliminating Nodes and Branches from a Hierarchical Query
You can eliminate a particular node from a query tree using a WHERE clause.
SQL> select level,
  2  lpad(' ',2*level-1)||first_name||' '||last_name as employee
  3  from more_employees
  4  where last_name!='Johnson'
  5  start with employee_id =1
  6  connect by prior employee_id =manager_id;

     LEVEL EMPLOYEE
---------- -------------------------
         1  James Smith
         3      Fred Hobbs
         3      Rob Green
         2    Susan Jones
         3      Jane Brown
         4        Henry Heyson
         3      John Grey
         4        Jean Blue
         2    Kevin Black
         3      Keith Long
         3      Frank Howard
         3      Doreen Penn
Traversing Upward Through the Tree
You don’t have to traverse a tree downward from parents to children: you can start at a child andtraverse upward. You do this by switching child and parent columns in the CONNECT BY PRIOR clause.
1)
  1  select level,lpad(' ',2*level-1)||first_name ||' '||last_name as employee
  2  from more_employees
  3  start with last_name='Blue'
  4* connect by prior manager_id =employee_id
SQL> /

     LEVEL EMPLOYEE
---------- -------------------------
         1  Jean Blue
         2    John Grey
         3      Susan Jones
         4        James Smith
2)
  1  select level,lpad(' ',2*level-1)||first_name ||' '||last_name as employee
  2  from more_employees
  3  start with last_name='Blue'
  4* connect by prior employee_id=manager_id
SQL> /

     LEVEL EMPLOYEE
---------- -------------------------
         1  Jean Blue









阅读(515) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~