Oracle loap函数,oracle loap函数用法

先看测试情况：

SCOTT@orcl#select level,lpad(' ',2*level-1)||ename as ename from emp

2 start with ename='KING'

3 connect by prior empno=mgr

4 order by level;

LEVEL ENAME

---------- --------------------------------------------------

1 KING

2 JONES

2 BLAKE

2 CLARK

3 TURNER

3 ALLEN

3 WARD

3 MARTIN

3 SCOTT

4 ADAMS

已选择10行。

SCOTT@orcl#select length('abc'),lpad('abc',4,'$'),lpad('abc',5,'^'),lpad('abc',5,'gf'),lpad('abc',6,'gf') from dual;

LENGTH('ABC') LPAD('AB LPAD('ABC' LPAD('ABC' LPAD('ABC',6

------------- -------- ---------- ---------- ------------

3 $abc ^^abc gfabc gfgabc

SCOTT@orcl#select level,lpad(level,4*level-1,'#')||ename as ename from emp

2 start with ename='KING'

3 connect by prior empno=mgr

4 order by level;

LEVEL ENAME

---------- --------------------------------------------------

1 ##1KING

2 ######2JONES

2 ######2BLAKE

2 ######2CLARK

3 ##########3TURNER

3 ##########3ALLEN

3 ##########3WARD

3 ##########3MARTIN

3 ##########3SCOTT

4 ##############4ADAMS

已选择10行。

SCOTT@orcl#select level,lpad(level,4*level)||ename as ename from emp

2 start with ename=

3 'JONES'

4 CONNECT BY prior empno=mgr

5 order by level;

LEVEL ENAME

---------- --------------------------------------------------

1 1JONES

2 2SCOTT

3 3ADAMS

SCOTT@orcl#select level,lpad(level,4*level-1)||ename as ename from emp

2 start with ename= 'JONES'

3 CONNECT BY prior empno=mgr

4 order by level;

LEVEL ENAME

---------- --------------------------------------------------

1 1JONES

2 2SCOTT

3 3ADAMS

SCOTT@orcl#select level,lpad(level,4*level-1,'^')||ename

2 as ename from emp

3 start with ename='JONES'

4 connect by prior empno=mgr

5 order by level;

LEVEL ENAME

---------- --------------------------------------------------

1 ^^1JONES

2 ^^^^^^2SCOTT

3 ^^^^^^^^^^3ADAMS

SCOTT@orcl#

SCOTT@orcl#r

1 select level,lpad('',4*level-1)||ename as ename from emp

2 start with ename=UPPER('king')

3 connect by prior empno=mgr

4* order by level

LEVEL ENAME

---------- --------------------------------------------------

1 KING

2 JONES

2 BLAKE

2 CLARK

3 TURNER

3 ALLEN

3 WARD

3 MARTIN

3 SCOTT

4 ADAMS

已选择10行。

SCOTT@orcl#1

1* select level,lpad('',4*level-1)||ename as ename from emp

SCOTT@orcl#c /''/' '

1* select level,lpad(' ',4*level-1)||ename as ename from emp

SCOTT@orcl#r

1 select level,lpad(' ',4*level-1)||ename as ename from emp

2 start with ename=UPPER('king')

3 connect by prior empno=mgr

4* order by level

LEVEL ENAME

---------- --------------------------------------------------

1 KING

2 JONES

2 BLAKE

2 CLARK

3 TURNER

3 ALLEN

3 WARD

3 MARTIN

3 SCOTT

4 ADAMS

已选择10行。

SCOTT@orcl# 说明：lpad(列名，填充长度，填充字符)