oracle使用descode和START WITH . . . CONNECT BY

万俟隽巧

　　decode (expression, search_1, result_1)
decode (expression, search_1, result_1, search_2, result_2)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)
　　decode (expression, search_1, result_1, default)
decode (expression, search_1, result_1, search_2, result_2, default)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)
　　
decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。
以下是一个简单测试，用于说明Decode函数的用法:SQL> create table t as select username,default_tablespace,lock_date from dba_users;
　　Table created.
　　SQL> select * from t;
　　USERNAME                       DEFAULT_TABLESPACE             LOCK_DATE
------------------------------ ------------------------------ ---------
SYS                            SYSTEM
SYSTEM                         SYSTEM
OUTLN                          SYSTEM
CSMIG                          SYSTEM
SCOTT                          SYSTEM
EYGLE                          USERS
DBSNMP                         SYSTEM
WMSYS                          SYSTEM                         20-OCT-04
　　8 rows selected.
　　
SQL> select username,decode(lock_date,null,'unlocked','locked') status from t;
　　USERNAME                       STATUS
------------------------------ --------
SYS                            unlocked
SYSTEM                         unlocked
OUTLN                          unlocked
CSMIG                          unlocked
SCOTT                          unlocked
EYGLE                          unlocked
DBSNMP                         unlocked
WMSYS                          locked
　　8 rows selected.
　　SQL> select username,decode(lock_date,null,'unlocked') status from t;
　　USERNAME                       STATUS
------------------------------ --------
SYS                            unlocked
SYSTEM                         unlocked
OUTLN                          unlocked
CSMIG                          unlocked
SCOTT                          unlocked
EYGLE                          unlocked
DBSNMP                         unlocked
WMSYS
　　8 rows selected.
　　
　　通过START WITH . . . CONNECT BY . . .子句来实现SQL的层次查询.
自从Oracle9i开始，可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。

自从Oracle 10g 中，还有其他更多关于层次查询的新特性 。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，
如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。

在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），
Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。
而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE，
如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。

The start with .. connect by clause can be used to select data that has a hierarchical relationship
(usually some sort of parent->child, boss->employee or thing->parts).
It is also being used when an sql execution plan is explained.

syntax:
select ... [start with initial-condition] connect by [nocycle] recurse-condition

level
With level it is possible to show the level in the hierarchical relation of all the data.

--oracle9i
sys_connect_by_path
With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child.

--oracle 10g
connect_by_root
connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.  
connect_by_is_leaf
connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_iscycle
connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.

--start with ... connect by ... 的处理机制
How must a start with ... connect by select statement be read and interpreted?
If Oracle encounters such an SQL statement, it proceeds as described in the following pseude code.

for rec in (select * from some_table) loop
  if FULLFILLS_START_WITH_CONDITION(rec) then
    RECURSE(rec, rec.child);
  end if;
end loop;

procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
  begin
  APPEND_RESULT_LIST(rec);     
  for rec_recurse in (select * from some_table) loop
    if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then
      RECURSE(rec_recurse,rec_recurse.child);
    end if;
  end loop;
end procedure RECURSE;

created by zhouwf0726 2006.

*******************************************************************************/

--创建测试表，增加测试数据

create table test(superid varchar2(20),id varchar2(20));

insert into test values('0','1');
insert into test values('0','2');

insert into test values('1','11');
insert into test values('1','12');

insert into test values('2','21');
insert into test values('2','22');

insert into test values('11','111');
insert into test values('11','112');

insert into test values('12','121');
insert into test values('12','122');

insert into test values('21','211');
insert into test values('21','212');

insert into test values('22','221');
insert into test values('22','222');

commit;

--层次查询示例
select level||'层',lpad(' ',level*5)||id id
from test
start with superid = '0' connect by prior id=superid;

select level||'层',connect_by_isleaf,lpad(' ',level*5)||id id
from test
start with superid = '0' connect by prior id=superid;

--给出两个以前在"数据库字符串分组相加之四"中的例子来理解start with ... connect by ...
--功能：实现按照superid分组,把id用";"连接起来
--实现：以下两个例子都是通过构造2个伪列来实现connect by连接的。

/*------method one------*/
select superid,ltrim(max(sys_connect_by_path(id,';')),';') from(
select superid,id,row_number() over(partition by superid order by superid) id1,
row_number() over(order by superid) + dense_rank() over(order by superid) id2
from test
)
start with id1=1 connect by prior id2 = id2 -1
group by superid order by superid;

/*------method two------*/
select distinct superid,ltrim(first_value(id) over(partition by superid order by l desc),';')
from(
select superid,level l,sys_connect_by_path(id,';') id
from(
select superid,id,superid||rownum parent_rn,superid||to_char(rownum-1) rn
from test
)
connect by prior parent_rn = rn
);

--下面的例子实现把一个整数的各个位上的数字相加,通过这个例子我们再次理解connect by.

create or replace function f_digit_add(innum integer) return number
is
outnum integer;
begin
        if innum<0 then
                return 0;
        end if;
        select sum(nm) into outnum from(
                select substr(innum,rownum,1) nm from dual connect by rownum<length(innum)
        );
        return outnum;
end f_digit_add;
/

select f_digit_add(123456) from dual;