Oracle 3일차 - SQL 단일행함수

SQL> conn hr/happy
Connected.
SQL> CREATE TABLE test1(value NUMBER);

Table created.

SQL> CREATE TABLE test2(value NUMBER);

Table created.

SQL> INSERT INTO test1 VALUES(1);

1 row created.

SQL> INSERT INTO test1 VALUES(2);

1 row created.

SQL> INSERT INTO test1 VALUES(3);

1 row created.

SQL> INSERT INTO test1 VALUES(4);

1 row created.

SQL> INSERT INTO test2 VALUES(3);

1 row created.

SQL> INSERT INTO test2 VALUES(4);

1 row created.

SQL> INSERT INTO test2 VALUES(5);

1 row created.

SQL> INSERT INTO test2 VALUES(6);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM test1;

     VALUE
----------
         1
         2
         3
         4

4 rows selected.

SQL> SELECT * FROM test2;

     VALUE
----------
         3
         4
         5
         6

SQL> SELECT * FROM test1 UNION SELECT * FROM test2;

     VALUE
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL> SELECT * FROM test1 UNION ALL SELECT * FROM test2;

     VALUE
----------
         1
         2
         3
         4
         3
         4
         5
         6

8 rows selected.

SQL> SELECT * FROM test1 INTERSECT SELECT * FROM test2;

     VALUE
----------
         3
         4

SQL> SELECT * FROM test1 MINUS SELECT * FROM test2;

     VALUE
----------
         1
         2
         
SQL> SELECT * FROM test2 MINUS SELECT * FROM test1;

     VALUE
----------
         5
         6

 

1. 단일행 함수

   1) 문자 함수

     => 변환함수

             (1) UPPER (대문자)   king => KING

             (2) LOWER (소문자)

             (3) INITCAP (이니셜) KING => King

SQL> SELECT ename, UPPER(ename), LOWER(ename), INITCAP(ename) FROM emp;

ENAME                UPPER(ENAME)         LOWER(ENAME)
-------------------- -------------------- --------------------
INITCAP(ENAME)
--------------------
SMITH                SMITH                smith
Smith

 

             (4) REPLACE => 문자변경

                  REPLACE('Hello', 'l', 'k') ==> Hekko

SQL> SELECT ename, REPLACE(ename,'A', 'M') FROM emp;

ENAME                REPLACE(ENAME,'A','M
-------------------- --------------------
SMITH                SMITH
ALLEN                MLLEN
WARD                 WMRD
JONES                JONES
MARTIN               MMRTIN
BLAKE                BLMKE
CLARK                CLMRK
SCOTT                SCOTT
KING                 KING
TURNER               TURNER
ADAMS                MDMMS

ENAME                REPLACE(ENAME,'A','M
-------------------- --------------------
JAMES                JMMES
FORD                 FORD
MILLER               MILLER

 

     => 제어함수

             (1) CONCAT : 문자열 결합 => ||

                 CONCAT('A', 'B') => AB

                 'A'||'B'

SQL> SELECT CONCAT('Hello', 'Oracle'), 'Hello'||'Oracle' FROM DUAL;

CONCAT('HELLO','ORACLE 'HELLO'||'ORACLE'
---------------------- ----------------------
HelloOracle            HelloOracle

 

             (2) SUBSTR : 문자를 분해 => subString()

                  SUBSTR('Hello Oracle', 1, 3); => Hel

                                      시작위치, 글자수 (자바는 시작위치와 끝위치)

                                   시작위치 ' - '  붙으면 뒤에서부터 시작

                  Hello Oracle

                  123456789101112 (문자번호 1로 시작)

SQL> SELECT SUBSTR('Hello Oracle', 5, 3) FROM DUAL;

SUBSTR
------
o O

-- emp 테이블에서 12월에 입사한 사원의 모든 정보 출력 (80/12/07) => 4번부터 2글자 잘라야 월을 가져옴

SQL> SELECT * FROM emp WHERE SUBSTR(hiredate,4,2) = 12;

     EMPNO ENAME                JOB                       MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
       SAL       COMM     DEPTNO
---------- ---------- ----------
      7369 SMITH                CLERK                    7902 80/12/17
       800                    20

      7788 SCOTT                ANALYST                  7566 82/12/09
      3000                    20

      7900 JAMES                CLERK                    7698 81/12/03
       950                    30

-- 3일에 입사한 사원

SQL> SELECT * FROM emp WHERE SUBSTR(hiredate,7,2) = 03;

     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

-- emp 이름중에 3번째 자리가 A인 사원의 모든 정보 출력

SQL> SELECT * FROM emp WHERE SUBSTR(ename,3,1) = 'A';

SQL> SELECT * FROM emp WHERE ename LIKE '__A%';

SQL> SELECT SUBSTR('Hello Oracle',1,3) FROM DUAL;

SUBSTR
------
Hel

SQL> SELECT SUBSTR('Hello Oracle',-1,3) FROM DUAL; //마지막 자리에서 3자리인데 없기때문에 e만

SU
--
e

SQL> SELECT SUBSTR('Hello Oracle',-6,3) FROM DUAL;

SUBSTR
------
Ora

             (3) INSTR : 특정 문자위치 찾기 => 자바의 indexOf()

                 INSTR('A/B/C/D', '/' , 1, 3)

                                   시작위치, 몇번째

SQL> SELECT INSTR('A/B/C/D', '/', 1, 1) FROM DUAL;

INSTR('A/B/C/D','/',1,1)
------------------------
                       2
                       
SQL> SELECT INSTR('A/B/C/D', '/', 3, 2) FROM DUAL; //3번째 자리인 B에서 시작
                                                   // '/'가 2번째 C뒤이므로 원래 문자자리 6
INSTR('A/B/C/D','/',3,2)
------------------------
                       6

 

     => 기타함수

         ***(1) LENGTH => 문자의 갯수

SQL> SELECT ename FROM emp WHERE LENGTH(ename)=5;

ENAME
--------------------
SMITH
ALLEN
JONES

             (2) LENGTHB => Byte의 갯수

SQL> SELECT ename, LENGTH(ename), LENGTHB(ename) FROM emp;

ENAME                LENGTH(ENAME) LENGTHB(ENAME)
-------------------- ------------- --------------
SMITH                            5              5
ALLEN                            5              5
WARD                             4              4

SQL> SELECT LENGTH('HONG'), LENGTH('홍길동') FROM DUAL;
                                                 // 임시테이블
LENGTH('HONG') LENGTH('홍길동')
-------------- ----------------
             4                3
             
SQL> SELECT LENGTHB('HONG'), LENGTHB('홍길동') FROM DUAL;

LENGTHB('HONG') LENGTHB('홍길동')
--------------- -----------------
              4                 9

             (3) LPAD, RPAD => 비밀번호나 아이디 찾기할 때

                  ***in  ad***

SQL> SELECT LPAD('SCOTT', 10, '*') FROM DUAL;
                       //글자수
LPAD('SCOTT',10,'*')
--------------------
*****SCOTT

SQL> SELECT RPAD('SCOTT', 10, '*') FROM DUAL;

RPAD('SCOTT',10,'*')
--------------------
SCOTT*****

 

SQL> SELECT ename, RPAD(SUBSTR(ename, 1, 2), LENGTH(ename), '*') FROM emp;

ENAME
--------------------
RPAD(SUBSTR(ENAME,1,2),LENGTH(ENAME),'*')
--------------------------------------------------------------------------------
SMITH
SM***

ALLEN
AL***

WARD
WA**

SQL> SELECT RPAD('Hello Oracle', 5, '#') FROM DUAL;

RPAD('HELL
----------
Hello

SQL> SELECT RPAD('Hello Oracle', 15, '#') FROM DUAL;
                            // 15 => 문자 출력 갯수 
RPAD('HELLOORACLE',15,'#')
------------------------------
Hello Oracle###

             (4) TRIM, RTRIM, LTRIM => 지정한 문자를 제거

                 LTRIM('AAAAABBAAAA', 'A') => BBAAAA

                 LTRIM(' AAAAABBAAAA', 'A') => 문자앞에 공백이 있으면 공백부터 시작해서 지우질 못함

                                                            (먼저 문자지정없이 공백제거한뒤)

                 LTRIM(LTRIM('  AAAAABBAAAA', 'A')

                 LTRIM('AAAAABBAAAA') => AAAAABBAAAA

                 지정안하면 공백이 지워짐

SQL> SELECT LTRIM('AAAAABBBAAAAA','A') FROM DUAL;

LTRIM('AAAAABBBA
----------------
BBBAAAAA

SQL> SELECT LTRIM(' AAAAABBBAAAAA','A') FROM DUAL;

LTRIM('AAAAABBBAAAAA','A')
----------------------------
 AAAAABBBAAAAA

SQL> SELECT LTRIM(' AAAAABBBAAAAA') FROM DUAL;

LTRIM('AAAAABBBAAAAA')
--------------------------
AAAAABBBAAAAA

SQL> SELECT LTRIM(LTRIM(' AAAAABBBAAAAA'),'A') FROM DUAL;

LTRIM(LTRIM('AAA
----------------
BBBAAAAA

SQL> SELECT RTRIM('AAAAABBBAAAAA','A') FROM DUAL;

RTRIM('AAAAABBBA
----------------
AAAAABBB

SQL> SELECT LTRIM(RTRIM('AAAAABBAAAAA', 'A'), 'A') FROM DUAL;

LTRI
----
BB

SQL> SELECT TRIM('A' FROM 'AAAAABBAAAAA') FROM DUAL;

TRIM
----
BB

--emp에서 이름중에 => A를 제거해서 출력

SQL> SELECT ename, LTRIM(ename,'A') FROM emp;

ENAME                LTRIM(ENAME,'A')
-------------------- --------------------
SMITH                SMITH
ALLEN                LLEN
WARD                 WARD
JONES                JONES
MARTIN               MARTIN
BLAKE                BLAKE
CLARK                CLARK
SCOTT                SCOTT
KING                 KING
TURNER               TURNER
ADAMS                DAMS

ENAME                LTRIM(ENAME,'A')
-------------------- --------------------
JAMES                JAMES
FORD                 FORD
MILLER               MILLER

 

   2) 숫자 함수

         (1) ROUND(실수, 자리수) : 반올림함수

              ROUND(12345.6789,2)

SQL> SELECT ROUND(12345.6789,2) FROM DUAL;

ROUND(12345.6789,2)
-------------------
           12345.68

         (2) TRUNC(실수, 자리수) : 버림함수

              TRUNC(12345.6789,2)

         (3) CEIL(실수) : 올림함수

             CEIL(12.1)

         (4) MOD(정수, 정수) : 나머지(%)

             MOD(10, 3) ==> 1 (자바의 10%3)

   3) 날짜 함수

   4) 변환 함수

   5) 일반 함수

 

 

=========================================

EmpVO

package com.sist.main;
// 데이터를 모아주는 클래스
import java.util.*;
/*
 * 	empno NUMBER => 사번
 *  ename VARCHAR2 => 이름
 *  job   VARCHAR2 => 직위
 *  mgr   NUMBER => 사수(사번)
 *  hiredate DATE => 입사일
 *  sal   NUMBER => 급여
 *  comm  NUMBER => 성과급
 *  deptno NUMBER => 부서번호
 *  
 *  => 데이터베이스 매칭 => ~VO, ~DTO
 *  
 */
// 사원 정보 => 캡슐화
public class EmpVO {
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private Date hiredate;
	private int sal;
	private int comm;
	private int deptno;
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public int getMgr() {
		return mgr;
	}
	public void setMgr(int mgr) {
		this.mgr = mgr;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public int getSal() {
		return sal;
	}
	public void setSal(int sal) {
		this.sal = sal;
	}
	public int getComm() {
		return comm;
	}
	public void setComm(int comm) {
		this.comm = comm;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	
	
}

 

EmpDAO

package com.sist.dao;
// 데이터베이스 연결
import java.util.*;
import java.sql.*;

public class EmpDAO {
	// 오라클 연결 => Socket
	private Connection conn;
	// 송신, 수신
	private PreparedStatement ps;
	// ps안에 BufferedReader (값읽어옴), OutputStream (값주기) 들어있음
	private final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
	private static EmpDAO dao;
	
	// 드라이버 등록 => 한번만 실행
	public EmpDAO(){
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch(Exception ex) {
			System.out.println(ex.getMessage());
		}
	}
	
	// 싱글턴 패턴
	public static EmpDAO newInstance() {
		if(dao==null)
			dao=new EmpDAO();
		
		return dao;
	}
	
	// 연결
	public void getConnection() {
		try {
			conn = DriverManager.getConnection(URL,"hr","happy");
		}catch(Exception ex) {}
	}
	
	// 해제
	public void disConnection() {
		try {
			if(ps!=null) ps.close(); // !=null 열려있을때
			if(conn!=null) conn.close();
		}catch(Exception ex) {}
	}

	public Connection getConn() {
		return conn;
	}

	public PreparedStatement getPs() {
		return ps;
	}

	
	//======== 모든 데이터베이스의 공통 사항

	// 기능
}

 

(라이브러리로 만듦 => BuildPath로 jar파일 가져옴)

MyDAO

package com.sist.dao;
import java.util.*;
import java.sql.*;
public class MyDAO {
	EmpDAO dao;
	public MyDAO() {
		dao = EmpDAO.newInstance();
	}
	public void empAllData() {
		dao.getConnection();
		
	}
}