oracle loap函数用法

最新推荐文章于 2023-10-28 20:58:05 发布

Rhys-Oracle

最新推荐文章于 2023-10-28 20:58:05 发布

阅读量1.9k

点赞数

分类专栏： ORACLE 之 PL /SQL

本文链接：https://blog.csdn.net/rhys_oracle/article/details/8915295

版权

ORACLE 之 PL /SQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

先看测试情况：

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（列名，填充长度，填充字符）