* ibatis

 : WHERE(AND) column LIKE '%'||#search_value#||'%'

* mybatis

 : WHERE(AND) coumn LIKE '%'||#{search_value}||'%'

 

- IBATIS / MYBATIS의 차이는 #XXX# / #{XXX} 의 차이뿐

저작자표시 비영리 변경금지

'* DB > Oracle' 카테고리의 다른 글

LPAD, RPAD 함수  (0) 2018.08.17
테이블 이름이 존재하는지 확인하기  (0) 2016.08.24
nul(), decode(), case then  (0) 2016.08.18
Table 조작  (0) 2016.08.18
오라클(함수)  (0) 2016.08.11

* LPAD 함수

사용법 : LPAD(변수,길이,변형자)

좌측에 자릿수(길이)만큼 변형자를 채워준다.


예> 기본키 생성시

SELECT LPAD(TO_NUMBER(SELECT ID FROM TB_MST)

+1),3,'0') AS ID 

FROM DUAL;


- (SELECT ID FROM TB_MST) : 결과값이 없는 경우        :>"0" 출력

: 결과값이 "002"로 있는 경우      :>"002" 출력

- TO_NUMBER(0) +1 : 숫자로 변환 후 +1                  :>"1" 출력

:>"3" 출력

- LPAD(1, 3, '0') : 숫자 1을 3자리로 왼쪽부터 '0'을 채워 "001"로 결과값 출력

:>"001" 출력

:>"003" 출력


* RPAD 함수

오른쪽으로 채워준다.



저작자표시 비영리 변경금지

'* DB > Oracle' 카테고리의 다른 글

LIKE 절  (0) 2019.12.16
테이블 이름이 존재하는지 확인하기  (0) 2016.08.24
nul(), decode(), case then  (0) 2016.08.18
Table 조작  (0) 2016.08.18
오라클(함수)  (0) 2016.08.11

*테이블 확인

SELECT COUNT(*) cnt FROM ALL_TABLES WHERE TABLE_NAME = '테이블명'

-테이블명은 대문자로 표기


*컬럼 확인

SELECT COUNT(*) cnt FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '테이블명'

-\"ALL_TAB_COLUMNS" 테이블에 대한 쿼리로 확인

저작자표시 비영리 변경금지

'* DB > Oracle' 카테고리의 다른 글

LIKE 절  (0) 2019.12.16
LPAD, RPAD 함수  (0) 2018.08.17
nul(), decode(), case then  (0) 2016.08.18
Table 조작  (0) 2016.08.18
오라클(함수)  (0) 2016.08.11

cmd 창에서 실행한다.


1. set heading on : 열의 이름을 보여준다.

   set heading off : 열의 이름을 숨긴다.


*student2라는 TABLE 을 임의로 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
SQL> select * from student2;
 
     STRNO NAME                                                                 
---------- ------------------------------------------------------------         
       KOR        ENG       MATH                                                
---------- ---------- ----------                                                
      1111 홍길동                                                               
        90         80         70                                                
                                                                                
      2222 홍길자                                                               
        70         80         90                                                
                                                                                
        33 홍길순                                                               
       100         80         90                                                
                                                                                
 
     STRNO NAME                                                                 
---------- ------------------------------------------------------------         
       KOR        ENG       MATH                                                
---------- ---------- ----------                                                
      3333 김말자                                                               
                                                                                
                                                                                
      4444 이말자                                                               
        50         50         60                                                
                                                                                
 
SQL> set heading on        --열의 이름 출력
SQL> select * from student2;
 
     STRNO NAME                                                                 
---------- ------------------------------------------------------------         
       KOR        ENG       MATH                                                
---------- ---------- ----------                                                
      1111 홍길동                                                               
        90         80         70                                                
                                                                                
      2222 홍길자                                                               
        70         80         90                                                
                                                                                
        33 홍길순                                                               
       100         80         90                                                
                                                                                
 
     STRNO NAME                                                                 
---------- ------------------------------------------------------------         
       KOR        ENG       MATH                                                
---------- ---------- ----------                                                
      3333 김말자                                                               
                                                                                
                                                                                
      4444 이말자                                                               
        50         50         60                                                
                                                                                
 
SQL> set heading off    --열의 이름 제거하고 출력
SQL> select * from student2;
 
      1111 홍길동                                                               
        90         80         70                                                
                                                                                
      2222 홍길자                                                               
        70         80         90                                                
                                                                                
        33 홍길순                                                               
       100         80         90                                                
                                                                                
      3333 김말자                                                               
                                                                                
                                                                                
      4444 이말자                                                               
 
        50         50         60                                                
                                                                                
 
SQL> set heading on
SQL> select * from student2;
 
     STRNO NAME                                                                 
---------- ------------------------------------------------------------         
       KOR        ENG       MATH                                                
---------- ---------- ----------                                                
      1111 홍길동                                                               
        90         80         70                                                
                                                                                
      2222 홍길자                                                               
        70         80         90                                                
                                                                                
        33 홍길순                                                               
       100         80         90                                                
                                                                                
 
     STRNO NAME                                                                 
---------- ------------------------------------------------------------         
       KOR        ENG       MATH                                                
---------- ---------- ----------                                                
      3333 김말자                                                               
                                                                                
                                                                                
      4444 이말자                                                               
        50         50         60                                                
                                             
cs


2. set linesize 150 : 열의 길이


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SQL> select * from student2;
 
     STRNO NAME                                                                 
---------- ------------------------------------------------------------         
       KOR        ENG       MATH                                                
---------- ---------- ----------                                                
      1111 홍길동                                                               
        90         80         70                                                
                                                                                
      2222 홍길자                                                               
        70         80         90                                                
                                                                                
        33 홍길순                                                               
       100         80         90                                                
                                                                                
 
     STRNO NAME                                                                 
---------- ------------------------------------------------------------         
       KOR        ENG       MATH                                                
---------- ---------- ----------                                                
      3333 김말자                                                               
                                                                                
                                                                                
      4444 이말자                                                               
        50         50         60                                                
                                                                                
 
SQL> set linesize 150        --보기편하게 라인 조정
SQL> select * from student2;
 
     STRNO NAME                                                                KOR        ENG       MATH                                              
---------- ------------------------------------------------------------ ---------- ---------- ----------                                              
      1111 홍길동                                                               90         80         70                                              
      2222 홍길자                                                               70         80         90                                              
        33 홍길순                                                              100         80         90                                              
      3333 김말자                                                                                                                                     
      4444 이말자                                                               50         50         60                                              
 
cs


3. set pagesize 15 : 행의 갯수

-열의 이름(ex. ENPNO..등) 또한 갯수에 포함


*행이 많은 TABLE 로 보이기 위해 emp TABLE 을 이용

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
SQL> select * from emp;
 
     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO                                               
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------                                               
      7369 SMITH                CLERK                    7902 80/12/17        800                    20                                               
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30                                               
      7521 WARD                 SALESMAN                 7698 81/02/22       1250        500         30                                               
      7566 JONES                MANAGER                  7839 81/04/02       2975                    20                                               
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30                                               
      7698 BLAKE                MANAGER                  7839 81/05/01       2850                    30                                               
      7782 CLARK                MANAGER                  7839 81/06/09       2450                    10                                               
      7788 SCOTT                ANALYST                  7566 87/04/19       3000                    20                                               
      7839 KING                 PRESIDENT                     81/11/17       5000                    10                                               
      7844 TURNER               SALESMAN                 7698 81/09/08       1500          0         30                                               
      7876 ADAMS                CLERK                    7788 87/05/23       1100                    20                                               
 
     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO                                               
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------                                               
      7900 JAMES                CLERK                    7698 81/12/03        950                    30                                               
      7902 FORD                 ANALYST                  7566 81/12/03       3000                    20                                               
      7934 MILLER               CLERK                    7782 82/01/23       1300                    10                                               
 
14 개의 행이 선택되었습니다.
 
SQL> set pagesize 15        --행의 갯수
SQL> select * from emp;
 
     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO                                               
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------                                               
      7369 SMITH                CLERK                    7902 80/12/17        800                    20                                               
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30                                               
      7521 WARD                 SALESMAN                 7698 81/02/22       1250        500         30                                               
      7566 JONES                MANAGER                  7839 81/04/02       2975                    20                                               
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30                                               
      7698 BLAKE                MANAGER                  7839 81/05/01       2850                    30                                               
      7782 CLARK                MANAGER                  7839 81/06/09       2450                    10                                               
      7788 SCOTT                ANALYST                  7566 87/04/19       3000                    20                                               
      7839 KING                 PRESIDENT                     81/11/17       5000                    10                                               
      7844 TURNER               SALESMAN                 7698 81/09/08       1500          0         30                                               
      7876 ADAMS                CLERK                    7788 87/05/23       1100                    20                                               
      7900 JAMES                CLERK                    7698 81/12/03        950                    30                                               
 
     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO                                               
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------                                               
      7902 FORD                 ANALYST                  7566 81/12/03       3000                    20                                               
      7934 MILLER               CLERK                    7782 82/01/23       1300                    10                                               
 
14 개의 행이 선택되었습니다.
 
SQL> set pagesize 17
SQL> select * from emp;
 
     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO                                               
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------                                               
      7369 SMITH                CLERK                    7902 80/12/17        800                    20                                               
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30                                               
      7521 WARD                 SALESMAN                 7698 81/02/22       1250        500         30                                               
      7566 JONES                MANAGER                  7839 81/04/02       2975                    20                                               
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30                                               
      7698 BLAKE                MANAGER                  7839 81/05/01       2850                    30                                               
      7782 CLARK                MANAGER                  7839 81/06/09       2450                    10                                               
      7788 SCOTT                ANALYST                  7566 87/04/19       3000                    20                                               
      7839 KING                 PRESIDENT                     81/11/17       5000                    10                                               
      7844 TURNER               SALESMAN                 7698 81/09/08       1500          0         30                                               
      7876 ADAMS                CLERK                    7788 87/05/23       1100                    20                                               
      7900 JAMES                CLERK                    7698 81/12/03        950                    30                                               
      7902 FORD                 ANALYST                  7566 81/12/03       3000                    20                                               
      7934 MILLER               CLERK                    7782 82/01/23       1300                    10                                               
 
14 개의 행이 선택되었습니다.
 
cs


4. column name(포맷을 적용할 열의 이름) format A10 : '이름'의 포맷 수정(길이를 10으로 변경)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
SQL> select * from student2;
 
     STRNO NAME                                                                KOR        ENG       MATH                                              
---------- ------------------------------------------------------------ ---------- ---------- ----------                                              
      1111 홍길동                                                               90         80         70                                              
      2222 홍길자                                                               70         80         90                                              
        33 홍길순                                                              100         80         90                                              
      3333 김말자                                                                                                                                     
      4444 이말자                                                               50         50         60                                              
 
SQL> column name format A10        --이름의 포멧 수정(10개로 길이축소)
SQL> select * from student2;
 
     STRNO NAME              KOR        ENG       MATH                                                                                                
---------- ---------- ---------- ---------- ----------                                                                                                
      1111 홍길동             90         80         70                                                                                                
      2222 홍길자             70         80         90                                                                                                
        33 홍길순            100         80         90                                                                                                
      3333 김말자                                                                                                                                     
      4444 이말자             50         50         60                                                                                                
 
SQL> column name format A6
SQL> select * from student2;
 
     STRNO NAME          KOR        ENG       MATH                                                                                                    
---------- ------ ---------- ---------- ----------                                                                                                    
      1111 홍길동         90         80         70                                                                                                    
      2222 홍길자         70         80         90                                                                                                    
        33 홍길순        100         80         90                                                                                                    
      3333 김말자                                                                                                                                     
      4444 이말자         50         50         60                                                                                                    
 
SQL> insert into student2 values (5555,'남궁길동',60,70,80);
 
1 개의 행이 만들어졌습니다.
 
SQL> select * from student2;
 
     STRNO NAME          KOR        ENG       MATH                                                                                                    
---------- ------ ---------- ---------- ----------                                                                                                    
      1111 홍길동         90         80         70                                                                                                    
      2222 홍길자         70         80         90                                                                                                    
        33 홍길순        100         80         90                                                                                                    
      3333 김말자                                                                                                                                     
      4444 이말자         50         50         60                                                                                                    
      5555 남궁길         60         70         80                                                                                                    
           동                                                                                                                                         
                                                                                                                                                      
 
6 개의 행이 선택되었습니다.
 
SQL> column name format A20
SQL> select * from student2;
 
     STRNO NAME                        KOR        ENG       MATH                                                                                      
---------- -------------------- ---------- ---------- ----------                                                                                      
      1111 홍길동                       90         80         70                                                                                      
      2222 홍길자                       70         80         90                                                                                      
        33 홍길순                      100         80         90                                                                                      
      3333 김말자                                                                                                                                     
      4444 이말자                       50         50         60                                                                                      
      5555 남궁길동                     60         70         80                                                                                      
 
6 개의 행이 선택되었습니다.
 
 
cs


*다른 포맷변경

column strno format 0,000 : 숫자형식의 포맷 변경

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
SQL> column strno format 0,000
SQL> select * from student2;
 
 STRNO NAME                        KOR        ENG       MATH                                                                                          
------ -------------------- ---------- ---------- ----------                                                                                          
 1,111 홍길동                       90         80         70                                                                                          
 2,222 홍길자                       70         80         90                                                                                          
 0,033 홍길순                      100         80         90                                                                                          
 3,333 김말자                                                                                                                                         
 4,444 이말자                       50         50         60                                                                                          
 5,555 남궁길동                     60         70         80                                                                                          
 
6 개의 행이 선택되었습니다.
 
SQL> column strno format 9,999    --0,000과 비교
SQL> select * from student2;
 
 STRNO NAME                        KOR        ENG       MATH                                                                                          
------ -------------------- ---------- ---------- ----------                                                                                          
 1,111 홍길동                       90         80         70                                                                                          
 2,222 홍길자                       70         80         90                                                                                          
    33 홍길순                      100         80         90                                                                                          
 3,333 김말자                                                                                                                                         
 4,444 이말자                       50         50         60                                                                                          
 5,555 남궁길동                     60         70         80                                                                                          
 
6 개의 행이 선택되었습니다.
 
SQL> column strno format 0,000.0
SQL> select * from student2;
 
   STRNO NAME                        KOR        ENG       MATH                                                                                        
-------- -------------------- ---------- ---------- ----------                                                                                        
 1,111.0 홍길동                       90         80         70                                                                                        
 2,222.0 홍길자                       70         80         90                                                                                        
 0,033.0 홍길순                      100         80         90                                                                                        
 3,333.0 김말자                                                                                                                                       
 4,444.0 이말자                       50         50         60                                                                                        
 5,555.0 남궁길동                     60         70         80                                                                                        
 
6 개의 행이 선택되었습니다.
 
SQL> column strno format 0,000.000
SQL> select * from student2;
 
     STRNO NAME                        KOR        ENG       MATH                                                                                      
---------- -------------------- ---------- ---------- ----------                                                                                      
 1,111.000 홍길동                       90         80         70                                                                                      
 2,222.000 홍길자                       70         80         90                                                                                      
 0,033.000 홍길순                      100         80         90                                                                                      
 3,333.000 김말자                                                                                                                                     
 4,444.000 이말자                       50         50         60                                                                                      
 5,555.000 남궁길동                     60         70         80                                                                                      
 
6 개의 행이 선택되었습니다.
 
SQL> column strno format 9,999.000
SQL> select * from student2;
 
     STRNO NAME                        KOR        ENG       MATH                                                                                      
---------- -------------------- ---------- ---------- ----------                                                                                      
 1,111.000 홍길동                       90         80         70                                                                                      
 2,222.000 홍길자                       70         80         90                                                                                      
    33.000 홍길순                      100         80         90                                                                                      
 3,333.000 김말자                                                                                                                                     
 4,444.000 이말자                       50         50         60                                                                                      
 5,555.000 남궁길동                     60         70         80                                                                                      
 
6 개의 행이 선택되었습니다.
cs


* 다른 TABLE에 같은 컬럼명이 있다면 같이 적용이 된다.

지금까지 student2 이였고, student 테이블을 확인하면 컬럼변경이 동일하게 되어 있는 것을 확인 할 수있다.

변경한 포맷을 다시 처음으로 돌아가기 위해서는 column strno(컬럼명) clear 을 사용하면 된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SQL> select * from student;
 
        NO        KOR        ENG       MATH NAME                                                                                                      
---------- ---------- ---------- ---------- --------------------                                                                                      
         1         22         33         44 홍길동                                                                                                    
         2         90         80         70 홍길자                                                                                                    
         3         50         60         70 박길자                                                                                                    
         4         50         50         50 이길자                                                                                                    
 
SQL> column name clear
SQL> select * from student;
 
        NO        KOR        ENG       MATH NAME                                                                                                      
---------- ---------- ---------- ---------- ----------------------------------------                                                                  
         1         22         33         44 홍길동                                                                                                    
         2         90         80         70 홍길자                                                                                                    
         3         50         60         70 박길자                                                                                                    
         4         50         50         50 이길자                                                                                                    
 
SQL> column strno clear
SQL> select * from student2;
 
     STRNO NAME                                                                KOR        ENG       MATH                                              
---------- ------------------------------------------------------------ ---------- ---------- ----------                                              
      1111 홍길동                                                               90         80         70                                              
      2222 홍길자                                                               70         80         90                                              
        33 홍길순                                                              100         80         90                                              
      3333 김말자                                                                                                                                     
      4444 이말자                                                               50         50         60                                              
      5555 남궁길동                                                             60         70         80                                              
 
6 개의 행이 선택되었습니다.
 
cs


저작자표시 비영리 변경금지

'* DB > Oracle' 카테고리의 다른 글

LPAD, RPAD 함수  (0) 2018.08.17
테이블 이름이 존재하는지 확인하기  (0) 2016.08.24
nul(), decode(), case then  (0) 2016.08.18
오라클(함수)  (0) 2016.08.11
오라클(기본)  (0) 2016.08.11

+ Recent posts

티스토리툴바