LEVEL, LAPD
lpad ('string', n [, 'string_pad')
rpad ('string', n [, 'string_pad')
string is left padded to length n with string_pad. If string_pad is ommited, a space will be used as default rpad is similar, but pads right instead of left.
SQL> select LPAD('L', 10, 'a') LPAD, RPAD('R', 10, 'b') RPAD from dual;
LPAD RPAD
---------- ----------
aaaaaaaaaL Rbbbbbbbbb
SQL>
The Oracle DUAL table
dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.
SQL> desc dual;
이름 널? 유형
----------------------------------------------------- -------- -----------
DUMMY VARCHAR2(1)
SQL>
SQL> select * from dual;
D
-
X
SQL>
The owner of dual is SYS but dual can be accessed by every user. As dual contains exactly one row (unless someone fiddled with it), it is guaranteed to return exactly one row in select statements. Therefor, dual is the prefered table to select a pseudo column (such as sysdate
select sysdate from dual
Although it is possible to delete the one record, or insert additional records, one really should not do that!.
SQL>
SQL> CREATE TABLE employee (
2 employee_id INTEGER,
3 manager_id INTEGER,
4 first_name VARCHAR2(10) NOT NULL,
5 last_name VARCHAR2(10) NOT NULL,
6 title VARCHAR2(20),
7 salary NUMBER(6, 0)
8 );
Table created.
SQL>
SQL>
SQL> select * from employee;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
1 0 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Sales Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green Sales Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin Black Ops Manager 100000
11 10 Keith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 10 Doreen Penn Ops Person 47000
13 rows selected.
SQL>
SQL> SELECT LEVEL,
2 LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
3 last_name AS employee
4 FROM employee
5 START WITH employee_id = 1
6 CONNECT BY PRIOR employee_id = manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 James Smith
2 Ron Johnson
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
13 rows selected.
SQL>
SQL> col 이름 format a20;
SQL> select LPAD('|__', (level-1)*4, ' ') || ename as 이름, mgr, sal from emp start with ename='KIN
G' connect by prior empno=mgr ORDER SIBLINGS BY empno desc;
이름 MGR SAL
-------------------- ---------- ----------
KING 5000
|__CLARK 7839 2450
|__MILLER 7782 1300
|__BLAKE 7839 2850
|__JAMES 7698 950
|__TURNER 7698 1500
|__MARTIN 7698 1250
|__WARD 7698 1250
|__ALLEN 7698 1600
|__JONES 7839 2975
|__FORD 7566 3000
이름 MGR SAL
-------------------- ---------- ----------
|__SMITH 7902 800
|__SCOTT 7566 3000
|__ADAMS 7788 1100
14 개의 행이 선택되었습니다.