ORACLE SQLPLUS
from
<ORACLE 결과값 포맷팅, QUERY(SELECT) 의 형태, JOIN >
1. ORACLE에서 결과 포맷팅하기
A. SQL 쿼리를 수정하기 전에 결과가 어떤식으로 왜 그렇게 나오는지 알아 둘 필요가 있다고 생각한다.
SQL> select empno,ename from emp;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
기본적으로 많이 사용하는 COLUMN FORMATTING에 대해 이야기 하겠다. SCOTT 유저의 EMP 테이블을 사용해 위의 쿼리를 던지면 위와 같은 결과가 나온다.
컬럼 밑의 ---- 부분을 세어보면 각각 10 길이를 가진다.
SQL> desc dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> select dname from dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
Dept 테이블의 내용을 조회해 보았다. Desc로 dname이 varchar2(14) 로 최대 14바이트까지의 문자열 입력이 가능하다. Dept 테이블의 내용을 조회해 보았다. Dname 밑의 줄의 수를 확인 해 보면 14길이임을 확인 할 수 있었다. 여기서 떠오른 것은 10보다 작은 길이의 컬럼이라 할지라도 기본적으로 10길이( NUMBER 타입에 해당 NUMWIDTH 변경으로 조절가능 )를 할당을 하며 그보다 큰 경우에는 그 수를 길이로 활용하지 않나 라는 것이었다.
SQL> create table del(
2 id1 varchar2(1),
3 id2 varchar2(20));
Table created.
SQL> insert into del values('h','h');
1 row created.
SQL> select * from del;
I ID2
- --------------------
h h
위의 결과를 보면 길이가 10을 넘어가는 길이의 컬럼(ID2) 은 자신의 테이블 생성시 설정상의 길이대로 받는다. 위의 경우 예상과 달리 id1 컬럼은 10길이가 아닌 1길이를 할당받았고 그런 이유로 컬럼명이 잘려나갔다. 이를 보았을 때 컬럼명은 출력길이에 영향을 주는 건 아니란걸 확인하였다.
SQL> create table del
2 (id1 varchar2(1),
3 id2 varchar2(2),
4 id3 varchar2(3),
5 id4 varchar2(4),
6 id5 varchar2(5),
7 id6 varchar2(6),
8 id7 varchar2(7),
9 id8 varchar2(8),
10 id9 varchar2(9));
Table created.
SQL> insert into del values('a','bb','ccc','dddd','eeeee','ffffff','ggggggg','hhhhhhhh','iiiiiiiii');
1 row created.
SQL> select * from del;
I ID ID3 ID4 ID5 ID6 ID7 ID8 ID9
- -- --- ---- ----- ------ ------- -------- ---------
a bb ccc dddd eeeee ffffff ggggggg hhhhhhhh iiiiiiiii
위에서는 id1 ( varchar2(1) ) ~ id9 (varchar2(9) ) 컬럼을 만들어 조회해 본 결과이다. 결과를 보면 desc의 조회값 즉 각 컬럼의 varchar2 의 얼마만한 길이를 할당했는지에 나뉘어진다.
SQL> create table del
2 (id1 varchar2(1),
3 id2 varchar2(2),
4 id3 varchar2(3),
5 id4 varchar2(4),
6 id5 varchar2(5),
7 id6 varchar2(6),
8 id7 varchar2(7),
9 id8 varchar2(8),
10 id9 varchar2(9));
Table created.
SQL> insert into del values('a','bb','ccc','dddd','eeeee','ffffff','ggggggg','hhhhhhhh','iiiiiiiii');
1 row created.
SQL> select * from del;
I ID ID3 ID4 ID5 ID6 ID7 ID8 ID9
- -- --- ---- ----- ------ ------- -------- ---------
a bb ccc dddd eeeee ffffff ggggggg hhhhhhhh iiiiiiiii
앞서 추측한 문자열의 경우 그 길이대로 화면상에서 공간을 차지하지 않나 확인 해 본 결과이다. 추측대로 문자열의 길이와 화면 출력길이는 동일하였다. 문자길이가 테이블 생성시 지정한 길이값보다 큰 값이 들어갈 수는 없으므로 결과적으로 문자는 화면상에 항상 표현해준다는 것을 알 수 있다.
SQL> drop table del;
Table dropped.
SQL> create table del
2 (id number(1),
3 id1 number(4));
Table created.
SQL> insert into del values(1,1111);
1 row created.
SQL> select * from del;
ID ID1
---------- ----------
1 1111
Number 타입의 두 컬럼이 테이블 생성시 할당된 값과 무관하게 출력길이 10을 차지하고 있다.
SQL> alter table del add (id2 number(14));
Table altered.
혹시 10보다 큰 길이의 데이터 값을 가지는 경우를 확인해 보았다.
SQL> select * from del;
ID ID1 ID2
---------- ---------- ----------
1 1111
SQL> update del set id2 = 11111111111111 where id=1;
1 row updated.
14자리의 값을 ID2에 할당하였다.
SQL> select * from del
2 ;
ID ID1 ID2
---------- ---------- ----------
1 1111 1.1111E+13
변화가 생겨났다. ID2의 출력길이는 10으로 여전하고 화면에 보이는 값 자체도 10자리가 맞지만 우리는 실제값이 14자리라는 것을 알고 있다.
이를 화면에 출력하는 방법으로 많이 사용되는 것은 아래와 같다.
SQL> column id2 format 99999999999999
SQL> select * from del;
ID ID1 ID2
---------- ---------- ---------------
1 1111 11111111111111
Column column_name format 99999999 ( 숫자 자리만큼 보이게 )
Column column_name format a9 ( 문자 9자리만큼 보이게 )
위의 Column 은 col 과 format 은 for와 동일하다
Ex> col column_name for a9
이로서 DESC를 통해 정보를 얻었을 때 쿼리를 던졌을 때 나올 모습을 예상할 수가 있다.
쿼리결과를 보기 좋게 하기 위해서 고려해야 할 것은
기본적으로 한 라인은 80자
문자값은 항상 전부 표현된다
숫자값은 10( NUMWIDTH DEFAULT 값 )자리를 넘어가면 지수표기된다.
정도이다.
숫자값에 비해 문자열이 통상적으로 길며 실제값이 짧더라도 많은 공백을 가질 수 있는 값이기에 큰 길이값을 가지는 문자열 컬럼을 먼저 포맷팅하는게 좋다고 생각한다.
마지막으로 숫자값의 10자리 값은 numwidth 값을 수정해 변경할 수 있다.
SQL>show all 에서 numwidth 10 값을 확인 할 수 있다.
또다른 방법으로 show numwidth 를 치면 값을 확인할 수 있다.
마지막으로 col(column) 과 많이 쓰이는 pagesize와 linesize를 적용해 보겠다.
SQL> create table copied_table
2 as select ename from emp
3 where rownum < 6;
Table created.
SQL> set pagesize 2
( pagesize를 2로 설정하였다. 아래의 결과는 set pagesize 1과 동일하다 )
SQL> select * from copied_table;
SMITH
ALLEN
WARD
JONES
MARTIN
값만 덩그러니 나오는 걸 확인할 수 있다.
SQL> set pagesize 3
SQL> select * from copied_table;
ENAME
----------
SMITH
ENAME
----------
ALLEN
ENAME
----------
WARD
ENAME
----------
JONES
ENAME
----------
MARTIN
컬럼명과 구분줄 그리고 값을 확인할 수 있다. 보통 최소 이 정보가 표시되길 원하기에 pagesize는 3이상을 사용할 것이다. 구분자가 다시 출력되기 전에 더 많은 내용이 출력되길 원한다면 값을 높이면 된다.
다음은 linesize에 대해 이야기 해 보겠다.
SQL> set pagesize 20
SQL> select * from copied_table;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
SQL> set linesize 4
SQL> select * from copied_table;
ENAM
----
SMIT
H
ALLE
N
WARD
JONE
S
MART
IN
결과를 보면 알듯이 linesize는 한 줄의 출력길이이다. 이 길이가 한 행의 출력길이보다 짧으면 캐리지리턴이 발생한다. 적당히 길게 잡는 것이 보기좋은 결과를 볼 수 있게 한다.
2. SELECT QUERY의 사용법
SELECT [ COLUMN_NAME | * ]
FROM TABLE_NAME
[WHERE 절 ]
[GROUP BY 절 ]
[HAVING 절 ]
[ORDER BY 절 ]
위의 절에서 SELECT, COLUMN 명 ( 혹은 * ) FROM 그리고 TABLE 명은 반드시 필요하다.
A. WHERE 절은 컬럼의 값을 제약하는 데 사용된다.
B. GROUP BY 절에 집단함수는 올 수 없다.
SELECT A,AVG(B),SUM(C)
…
GROUP BY A
C. HAVING 절은 SELECT 절의 집단함수 컬럼에 대하여 조건을 추가한다.
D. ORDER BY 절은 선택된 값을 가지고 정렬한다.
E. ALIAS
i. ALIAS는 두곳에서 사용 가능하다.
1. COLUMN
A. SELECT BYTES/1024/1024 MB,FILE_SIZE
…
B. 위와같이 호출하고 싶은 컬럼뒤에 한칸 간격을 두고 문자를 사용하면 출력시 해당 문자열을 사용한다.
C. mb로 쓰든 Mb로 쓰든 mB로 쓰든 alias는 기본적으로 대문자로 변환한다. 게다가 alias에 띄어쓰기를 하면 에러가 난다.
D. 간단한 예로 BYTES/1024/1024 MEGA BYTES, FILE_SIZE 이런식으로 썼다면 에러가 리턴된다. ( "" 를 쓰면 공백사용이 가능하다. )
E. 자신이 원하는 문자열을 쓰려면 “ “ 로 묶어 쓰면 된다.
SQL> select ename "Employee Name" from copied_table;
Employee N
----------
SMITH
ALLEN
WARD
JONES
MARTIN
SQL> col "Employee Name" for a30
앞서 말했듯이 컬럼명은 컬럼 출력사이즈에 아무런 영향을 미치지 못한다. ename의 데이터 타입은 varchar2 (10) 이며 10이란 사이즈의 영향을 받는다. 만일 컬럼이 온전하게 나오게 하려면 위와같이 col 을 사용하면 되고 alias로 지정한 컬럼이기에 위와 같이 입력해야한다.
SQL> /
Employee Name
------------------------------
SMITH
ALLEN
WARD
JONES
MARTIN
SQL>
2. TABLE
A. 테이블에서의 alias는 column과 동일하게 사용가능하다.
1* select "hello_world".ename
2* from copied_table "hello_world"
SQL> /
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
3. JOIN
4. 예제 테이블의 작성
SQL> create table copied_emp as
2 select ename,deptno from emp
3 where rownum < 4;
Table created.
SQL> select * from copied_emp;
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
SQL> create table copied_dept as
2 select deptno,dname from dept
3 ;
Table created.
SQL> select count(*) from copied_emp;
COUNT(*)
----------
3
SQL> select count(*) from copied_dept;
COUNT(*)
----------
4
SQL> select e.ename,d.dname
2 from copied_emp e,copied_dept d
3 where e.deptno = d.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
위의 조인이 일반적으로 사용하는 동등조인이다. 동등은 ( = ) 를 사용한 걸 말한다.
ANSI 버전으로 쿼리를 고치면
1 select e.ename,d.dname
2* from copied_emp e natural join copied_dept d
SQL> /
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
어떤 책에서는 natural join 에서는 alias를 사용하지 못한다고 했지만 위에서는 사용가능했다. 내가 사용한 oracle 버전은 9.2.0.4.0 이었다. 지금에 이르러 가능하게 된 듯 하다.
이 쿼리의 한계로는 컬럼명이 일치해야 한다는 것이다.
SQL> desc copied_dept;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
SQL> alter table copied_dept
2 add (deptno1 number(2) );
Table altered.
SQL> update copied_dept
2 set deptno1 = deptno;
4 rows updated.
SQL> commit;
Commit complete.
SQL> alter table copied_dept drop column deptno;
Table altered.
위의 쿼리에서 기존의 deptno 컬럼과 동일한 값을 가지는 deptno1 을 추가하고 deptno을 삭제하였다. 다음 쿼리에서 기존에 사용했던 조인 쿼리를 다시 실행한 모습이다.
SQL> @join
ENAME DNAME
---------- --------------
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
SMITH RESEARCH
ALLEN RESEARCH
WARD RESEARCH
SMITH SALES
ALLEN SALES
WARD SALES
SMITH OPERATIONS
ALLEN OPERATIONS
WARD OPERATIONS
12 rows selected.
SQL>
copied_emp 의 row수가 3개 copied_dept의 수가 4개였다. 위의 조인으로 12개의 값이 반환되었고 이는 두 테이블 행 수의 곱에 해당한다. 다시말해 카티션 곱이 일어났다. ename 파트를 유심히 보면 3개를 주기로 4번 반복되는 현상을 관찰할 수 있다.
( 진행을 위해 다시 원복하였다. 과정 생략 )
아래의 쿼리는 inner join의 예이다. where 절 대신 on 을 사용해 연결이 일어날 컬럼을 직접적으로 지정하였다. 결과적으로 오라클의 일반적인 동등조인과 동등하다.
SQL> get join
1 select e.ename,d.dname
2 from copied_emp e inner join copied_dept d
3* on e.deptno = d.deptno
SQL> /
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
이제 OUTER JOIN을 살펴보도록 하겠다. 지금까지의 결과는 COPIED_DEPT의 값 하나가 연결되는 값이 없는 관계로 결락되어있다.OUTER JOIN 은 이 결락된 부분을 출력해주기 위해 사용하는 방법이다. 오라클의 경우 조인조건 옆에 (+) 기호를 사용하며 ANSI의 경우 LEFT OUTER JOIN, RIGHT OUTER JOIN 형태로 사용한다.
SQL> get join
1 select e.ename,d.dname
2 from copied_emp e,copied_dept d
3* where e.deptno(+) = d.deptno
SQL> @join
ENAME DNAME
---------- --------------
ACCOUNTING
SMITH RESEARCH
ALLEN SALES
WARD SALES
OPERATIONS
(+) 부호는 더 보여지길 바라는 table의 반대측에 삽입해 사용한다.
SQL> get join
1 select e.ename,d.dname
2 from copied_emp e right outer join copied_dept d
3* on e.deptno = d.deptno
SQL> @join
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
OPERATIONS
ACCOUNTING
SQL>
LEFT | RIGHT 는 더 보여지길 바라는 테이블 측에 사용한다. 위의 경우는 기존에 매칭되는 값이 없어 나오지 못했던 값이 DNAME 에 존재하였고 copied_emp e right outer join copied_dept d
와 같이 나오지 못했던 값을 갖는 테이블이 우측에 위치했기에 RIGHT를 사용하였다.
