190614_Day45 <회원가입 마무리>, <JCF 자바컬렉션 프레임워크>

< Java Collection Framework ( JCF ) >컬렉션 프레임워크

  • 자료구조를 바탕으로 객체들을 추가 삭제 검색 할 수 있도록 인터페이스와 클래스들을 포함
  • 데이터(참조데이터)를 담는 바구니
  • JCF는 구조를 정의하기 위해 Collection, List, Set, Map 인터페이스를 참조함
  • 배열보다 더 편리하게 객체를 다룰 수 있습니다.
  • JCF내의 클래스들을 java.util.*안에 위치
  • 객체를 수집해서 저장하는 역할을 하기 위해 사용방법을 미리 정해 놓은 라이브러리
  • 대표적인 인터페이스 : List, Set, Map
  • List

    • 순차적 나열 순서지정이 가능한 요소들의 집합
    • 객체를 일렬로 늘어놓은 구조를 가지며 객체저장시 자동 인덱스가 부여
    • ArrayList, Vector, LinkedList
    • 순서를 유지하고 저장
    • 중복저장 가능
  • Set
    • HashSet, TreeSet
    • 순서를 유지하지 않고 저장
    • 중복을 허용하지 않는 요소들의 집합
  • SortedSet
    • 값들이 정렬된 Set
  • Map
    • 키와 키에 대응하는 값으로 이루어진 구조.
  • SortedMap
    • 키가 정렬된 Map.

VO새로 만들기는 부담스러울때 Map을 사용하기도 한다!

ListTest

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Vector;

import j0610.model.dto.Person;

class A{    
    void method1(Vector v) {
       for(int i=0; i<v.size(); i++) {
           System.out.println(v.get(i));
       }
    }

    void method2(ArrayList v) {
        for(int i=0; i<v.size(); i++) {
            System.out.println(v.get(i));
        }
    }

    void methodCommon(List list) {
        for(int i=0; i<list.size(); i++) {
            System.out.println(list.get(i));
        }
    }
}
//--------------------------------------------------
public class ListTest {    
  public static void main(String[] args) {
     //java.util.List(인터페이스)
     //   ----> 구현클래스: Vector, ArrayList, LinkedList
      Vector v = new Vector();
         v.add("홍길동");
         v.add("길라임");

      A a  = new A();
        a.method1(v);

      System.out.println("===============");  
      ArrayList ar = new ArrayList();
         ar.add("김주원");
         ar.add("김유신");
       a.method2(ar);


      List v2 = new Vector();
          v2.add(100);
          v2.add(200);
          a.methodCommon(v2);
      List ar2 = new ArrayList();
          ar2.add(3000);
          ar2.add(4000);

      //ArrayList와 Vector는 사용문법이 같다
        // 차이점 : ArrayList가 처리 속도가 빠르다. 
      //(이유 : Vector에는 동기화 코드가 추가되어있으므로 )

//          자료(데이터에 대한 추가, 검색, 수정, 삭제
          Vector<String> v3 = new Vector<String>();
          v3.add("길동");
          v3.add("라임");
          v3.add("즈원");

          //백터 toString()오버라이딩을 구현해서 저장된 데이터를 보여줌
          System.out.println(v3);

          ArrayList<String> alist = new ArrayList<String>();
          alist.add("이");
          alist.add("딩");
          alist.add("듀");
          System.out.println(alist);

          LinkedList<String> linkList = new LinkedList<String>();
          linkList.add("a");
          linkList.add("b");
          linkList.add("c");
          System.out.println(linkList);
          System.out.println("==================================");

          /*
           * ArrayList => 데이터 1개 저장 : 한개의 index 필요
           *     만약 데이터의 수가 많고 (1000000), 첫번째 데이터 삭제할 때(인덱스 : 0)를 삭제할때
           *  뒤의 999999데이터는 인덱스를 다시 지정해야 함.
           */
          /*
           * LinkedList => 데이터 1개 저장 : 한개의 index, 앞의 데이터 주소, 뒤의 데이터 주소 저장 필요
           * 만약 데이터의 수가 많고 (1000000), 첫번째 데이터 삭제할 때(인덱스 : 0)를 삭제할때
           * 201호 202호 203호
           * "김"  "이"  "비"에서 삭제시 하나씩 땡겨옴!
           */

          //ArrayList와 LinkedList 속도 테스트
           ArrayList<Person> list10 = new ArrayList<Person>();
           LinkedList<Person> list20 = new LinkedList<Person>();

           //첫번째 : 순자적인 입력, 데이터를 뒤로 추가

          long start = System.currentTimeMillis();
          for(int i=0; i<1000000; i++) {//반복횟수
              list10.add(new Person(1,"홍길동",13,"학생"));
          }
          long end = System.currentTimeMillis();
          System.out.println("ArrayList데이터입력시간(순차):"+ (end-start));
          //---------------------------------------------------------------
          start = System.currentTimeMillis();
          for(int i=0; i<1000000; i++) {//반복횟수
              list20.add(new Person(1,"홍길동",13,"학생"));
          }
          end = System.currentTimeMillis();
          System.out.println("LinkedList데이터입력시간(순차):"+ (end-start));
          //---------------------------------------------------------------
          System.out.println("==========================");
          //두번째:중간 입력(ArrayList는 인덱스에 대한 재배열)!!
          // List[]        0,"길동"입력
          // List["길동"]   0,"주원"입력
          // List["주원","길동"]   0,"라임"입력
          //        0     1
          // List["라임","주원","길동"]
          //               1    2
          ArrayList<Person>  list30 = new ArrayList<Person>();
          LinkedList<Person>  list40 = new LinkedList<Person>();

          start = System.currentTimeMillis();
          for(int i=0; i<1000000; i++) {//반복횟수
              //list30.add(int index, Object 데이터);
              list30.add(0, new Person(1,"홍길동",13,"학생"));
          }
          end = System.currentTimeMillis();
          System.out.println("ArrayList데이터입력시간(중간입력):"+ (end-start));

          start = System.currentTimeMillis();
          for(int i=0; i<1000000; i++) {//반복횟수          
              list40.add(0, new Person(1,"홍길동",13,"학생"));
          }
          end = System.currentTimeMillis();
          System.out.println("LinkedList데이터입력시간(중간입력):"+ (end-start));

          /*
           * 결론
           * 만약 데이터의 수가 적고, 순차적 저장을 하며 조회업무를 많이한다면 => ArrayList
           * 만약 데이터의 수가 많고, 순차적인지 않은 저장을 하며, 삭제업무를 많이 한다 => LinkedList
           */
  }
}

SetTest

import java.util.Iterator;
import java.util.SortedSet;
import java.util.TreeSet;

public class SetTest
{
    public static void main(String[] args)
    {
        // Set: 중복데이터 제거(유일한 데이터만 저장), (저장되는)순서가 일정치 않다. null허용? O

        // Set<String> set = new HashSet<String>();

        SortedSet<String> set = new TreeSet<String>();// 정렬된 Set : null허용? X
        // 데이터 추가
        set.add("홍길동");
        set.add("길라임");
        set.add("김주원");
        set.add("홍길동");
        set.add("이순신");
        set.add("홍길동");
        // set.add(null);
        // set.add(null);

        System.out.println("Set에 저장된 요소 갯수: " + set.size());

        // 모든 요소 출력
        Iterator<String> it = set.iterator();// ---> 모든 데이터 얻어오기
        // 열거형 인터페이스

        while (it.hasNext())
        { // ---> it에 열거된 데이터가 존재한다면 true리턴
            System.out.println(it.next()); // 데이터 뽑아오기
        }

        /*
         * 출력결과) null 김주원 홍길동 길라임 이순신
         * 
         * 
         */

    }// main
}

LottoSet

import java.util.HashSet;
import java.util.Random;
import java.util.Set;

public class LottoSet
{
    //로또번호 -> 1~45까지의 중복되지 않는 6개의 수
    public static void main(String[] args)
    {
        Random r = new Random();
        Set<Integer> set = new HashSet<Integer>();
        while(true)
        {
            set.add( r.nextInt( 45 ) + 1 );
            if( set.size() == 6 ) break;
        }

        Object []ob = set.toArray();

        for (int i = 0; i < ob.length; i++)
        {
            System.out.println(ob[i]);            
        }
    }
}

MapTest

import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import j0611.model.dto.Person;

public class MapTest
{
    //Map - 지도 (위도, 경도)
    //            x축, y축 처럼!
    //저장데이터(키값, 데이터값) 사용!
    /*
     *  Map<K,V>
     *      - 사물함 클래스 ( 책 : 데이터, 열쇠 : 키 )
     *      - K : Key의 자료형, V : Value의 자료형
     *      - Key는 보통 문자열 사용, Value(저장데이터)는 Object
     *      - Key값은 중복X ( 유일한 값 )
     */

    //★ 기억!
    // - 저장 : map.put(키값, 저장할 데이터);
    // - 조회 : map.get(키값);
    public static void main(String[] args)
    {
        Map<String, Object> map = new HashMap<>();
        //데이터 입력 ("기묭진" , 13, "학생")
//        map.put(String key, Object value)
        map.put("name", "기묭진");
        map.put("age", 13);
        map.put("job", "학생");

        Map<String, String> map2 = new HashMap<>();
        //데이터 입력 ( "기묭진", "이딩듀", "이비키", "진주니어", "김바나" )
        map2.put("k1", "기묭진");
        map2.put("k2", "이딩듀");
        map2.put("k3", "이비키");
        map2.put("k4", "진주니어");

        //전체데이터 출력?
        System.out.println( "map2.toString() = " + map2.toString() );
        //map2.toString() = {k1=기묭진, k2=이딩듀, k3=이비키, k4=진주니어}

        //특정 데이터 출력 ( K2키에 저장된 데이터를 출력! )
        //map2.get(Object key);
        map2.get("k2");
        System.out.println(map2.get("k2"));
        //이딩듀

        //특정 데이터 삭제( 특정 키삭제 : K3 )
        //map2.remove(Object key)
        map2.remove("k3");
        System.out.println(map2.toString());
        map2.put("k3", "돌아온 이비키");
        System.out.println(map2.toString());
        map2.remove("k2");
        map2.put("k2", "돌아온 자다가 뿡뿡 두번하고 자는척하는 이딩듀");
        System.out.println(map2.toString());

        //특정 키에 저장된 데이터 수정
        map2.replace("k4", "이딩푸들과 진주니어");

        //전체 데이터 출력
        Collection<String> col = map2.values();

        Iterator<String> it = col.iterator();

        System.out.println("<<전체 map2데이터>>");
        while (it.hasNext())
        {
            System.out.println(it.next());
        }

        //map2의 ket값만 따로 얻어오기
        System.out.println("<<전체 map2의 키 출력>>");
        Set<String> set = map2.keySet();
        Iterator<String> keyset = set.iterator();
        while(keyset.hasNext())
        {
            String key = keyset.next();
            System.out.println(key + "에 저장된 데이터 : " + map2.get(key));
        }

        /*
         * 문제 ) Map m3; 에 Person클래스 (4, "길동" , 13,  "학생")
         *                     Car클래스 ("소나타", 4, 100)
         *                     거리데이터 (10KM)를 저장하시오
         */

        Map<String, Object> map3 = new HashMap<>();
        Person p = new Person(4, "길동" , 13,  "학생");
//        Car c = new Car("소나타", 4, 100);
        String d = "10km";
        map3.put("Person", p);
        map3.put("Distance", d);
        System.out.println(map3.toString());

        //문제) distance키에 저장된 거리를 출력
        System.out.println("거리 : " + map3.get("anykey")); // 일치하는 key없을 때 리턴 null
        //문제) person키에 저장된 이름과 나이를 출력
//        Object ob = map3.get("person");
//        System.out.println("이름 : " + ob.getName()); 에러발생, 부모레퍼런스 ob통해 자식메소드 호출 불가
        Person ob = (Person) map3.get("person");
        System.out.println(ob.getName());
        System.out.println(ob.getAge());

    }
}

Mission

serviceform에서 String keyword = tf_search.getText(); 부분 수정하기


  • 인터페이스 구현 클래스 특징
    List LinkedList
    Stack
    Vector
    ArrayList
    순서 있는 데이터 집합
    데이터의 중복을 허용
    Set HashSet
    TreeSet
    순서 유지하지 않는 데이터 집합
    데이터의 중복을 허용하지 않는다.
    Map HashMap
    TreeMap
    HashTable
    Properties
    Key와 Value의 쌍으로 이루어진 데이터의 집합
    순서는 유지되지 않는다.
    키의 중복을 허용하지 않는다.
    값의 중복은 허용한다.

190612,13 Day43,44 <로그인, 회원가입 DB>

유효성 검사 마무리-42일차에 추가수정


<로그인과 회원가입>

  1. View

    • LoginForm( 로그인폼 )

      tf_id

      tf_pass

      bt_login

      bt_join

  • JoinForm

    tf_id

    tf_pass

    tf_pass2

    tf_name

    tf_ssn1

    tf_ssn2 => JPasswordField

    tf_phone1

    tf_phone2

    tf_phone3

    tf_addr

 cb_job

​ bt_submit 등록버튼

​ bt_reset 취소버튼

​ bt_checkid 중복확인버튼

  • UpdateForm ( 회원정보수정폼 )

    : 타이틀 = > 회원정보수정

    : id, 이름, 주민번호의 편집불능

    : 중복확인 버튼 제거

  • ServiceForm (서비스창) => 로그인 성공시 보여지는 서비스폼

    : id, 이름, 나이, 성별, 전화번호, 주소, 직업 출력

    : 보기버튼 ===> 전체보기, 수정, 삭제, 이름검색 으로!

    : JTable table, DefaultTableModel dtm

    : bt_sel_all, bt_up, bt_del,

    bt_sel_name, bt_exit

  1. Model (j0621.model.vo, j0612.model.dao)

    • MembershipVO : DB(테이블)에 있는 (한 개의) 레코드를 표현하는 객체!
    • MembershipDAO(추가, 삭제, 수정, 조회)
      • create, remove, modify, find, findAll

<작업순서>

  1. 시작뷰는 로그인폼

    • Controller에 뷰 등록
    • LoginForm만 setVisible(true)
  2. 회원가입

    • LoginForm의 '신규가입' 버튼 클릭으로 JoinForm이동

    • JoinForm 입력값 얻기

    • 입력값을 Membership으로 묶어주기

    • MembershipDao : insert()호출

      ​ => DB입력

    • 입력성공 또는 실패 메시지 후 성공 LoginForm으로 이동.

  3. 로그인

    • LoginForm 입력값(아이디,비밀번호) 얻기
    • MembershipDAO : findLogin()호출
      ---> DB조회  
    • 아이디와 비밀번호 일치시 '로그인성공!!' 후 ServiceForm 이동.
      불일치시  '로그인실패!!'메시지 출력.
  4. 서비스폼

    • 회원정보 출력
    • LoginForm => ServiceForm 이동 시 전체 회원정보 JTable에 출력
    • ServiceForm에서 '전체보기'버튼 클릭시 전체 회원정보 JTable에 출력
    • ===> MembershipDAO : findAll()의 결과값을 통해!!
                         ---> DB조회

    ​ UI MembershipDAO DB테이블

    ​ 액션 ArrayList findAll() membership (회원정보)

    ​ ---> selectfrom 각행 --> 회원한명

    ​ where X ---

    ​ VO

    준비:
    ArrayList에 저장된
    데이터를 출력할 컴포넌트!! ----> JTable

[회원가입폼의 직업샘플]
"학생","공무원","언론/출판","군인/경찰","일반사무직",
"영업직","기술/전문직","보건/의료","자영업","주부","기타"

  1. 서비스폼(JTable): 회원정보 수정
    5-1. ==> 이전에 저장된 (특정회원)데이터 조회!!

    • 입력대화상자 통해 수정 아이디 입력!!
    • MembershipDAO : findById()
      --->예상sql?  select ~ from ~ where id=? 
      findById(String id) <===파라미터                      
      VO findById(String id) <===리턴                      
    • 리턴데이터를 출력할 UI ---> 수정폼 (UpdateForm)
      ===> 뷰 이동 (서비스폼 ---> 수정폼)
      5-2. ==> 이전데이터 수정!!
    • 아래 SQL에 필요한 데이터 ==> getText() : 서비스폼으로 부터
    • MembershipDAO : modify()
      ---> 예상sql?   update ~ set addr=? job=? ....
                              where id=?
      modify(VO) <==== 매개변수
      boolean modify(VO) <==== 리턴
    • 리턴에 의한 뷰이동 : 서비스폼 이동(수정 성공시) 또는 수정폼에 머무르기(수정 실패시)
  2. 서비스폼(JTable) : 회원삭제(탈퇴)

    • MembershipDao : remove()호출

      ​ > 예상되는 sql?

      ​ delete from where id = ?

      ​ remove(String id) <=== 매개변수

      ​ boolean remove(String id) <==리턴

      리턴에 의한 뷰이동 : 서비스폼에서 상황 메시지 출력 (성공시 JTable에 데이터 반영)

  3. 서비스폼 (JTable) : 이름검색 (입력된 문자를 포함하는)

    • 입력대화상자 통해 조회 이름 입력!!

    • MembershipDAO : findByName()호출

      ​ > 예상되는 sql?

      ​ select ~ from ~

      ​ where name like ?

      ​ findByName(String name) <=매개변수

      ArrayList findByName(String name) <= 리턴

      • 리턴에 의한 뷰 이동 : 서비스폼에서 상황 데이터 출력(성공시 JTable에 데이터 반영)
  4. 회원정보 입력(회원가입창) : 중복확인!! (중복아이디체크)

    • ※ 중복 체크할 아이디 얻기

             첫번째 방법)
           joinForm의 '등록'버튼 클릭시'
                        또는
           joinForm의 '중복확인'버튼 클릭시
              ==> 회원가입폼.tf_id.getText() 이용하여  중복검사 
             두번째 방법)              
           '중복확인' 버튼 클릭시  
              ==> 새로운 프레임 오픈  
                                   예)  class CheckId extends JFrame{
                            JTextField tf_id;
                            JButton   bt_confirm;//확인  

      ​ CheckId(){
      ​ setTitle("중복확인");
      ​ setLayout(new FlowLayout());

      ​ add(tf_id);
      ​ add(bt_confirm);
      ​ }
      ​ }

      ​ ==> 입력대화상자 사용
      ​ 예) String id = JOptionPane.showInputDialog("아이디입력");
      ​ 세번째방법)
      ​ JoinForm에서 '중복확인'버튼 제거
      ​ JoinForm의 tf_id에 keyListener를 적용
      ​ ==> 문자입력될때마다 중복체크해서 결과 보이기

      • MembershipDAO : findExistId()

        예상sql? select count(*) from ~

                  where id=?

        findExistId(String id) <=== 파라미터
        int findExistId(String id) <=== 리턴 0,1
        //---------------------------------------------------------


코드


DAO

public class MembershipDAO
{

    Connection conn;
    // Statement stmt;
    PreparedStatement stmt;
    ResultSet rs;

    Properties pro;// DB접속관련 정보 저장 객체

    public MembershipDAO()
    {
        try
        {
            pro = new Properties();// 속성 담는 객체 (속성0개)
            pro.load(new FileReader("conn/conn.properties"));
            // 속성driver,url,user,password 적재 (속성4개)
            Class.forName(pro.getProperty("driver"));
        } catch (FileNotFoundException e)
        {
            e.printStackTrace();
        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        } catch (IOException e)
        {
            e.printStackTrace();
        }
    }// 생성자

    //수정 폼에 입력된 데이터로 DB데이터 갱신(재입력)
    public boolean modify(MembershipVO vo)
    {
        connect();

        try
        {
            String sql = " update membership set pass = ? , phone = ?, addr = ?, job = ?" + "where id = ?";
            stmt = conn.prepareStatement(sql);
                stmt.setString(1,  vo.getPass());
                stmt.setString(2,  vo.getPhone());
                stmt.setString(3,  vo.getAddr());
                stmt.setString(4,  vo.getJob());
                stmt.setString(5,  vo.getId());

            int t = stmt.executeUpdate(); // sql실행 요청
            if( t == 1 ) return true; // t : 수정된 행의 갯수
        } catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally
        {
            disconnect();            
        }


        return false;

    }

//    public boolean remove(String id) // 선택 삭제
//    {
//        connect();
//        
//        try
//        {
//            String sql = " delete from membership where id = '" + id + "'";
//            stmt = conn.prepareStatement(sql); // sql전송
//            stmt.setString(1, id);
//            int t = stmt.executeUpdate(); // sql실행 요청
//            if( t == 1 ) return true; // t : 수정된 행의 갯수
//        } catch (SQLException e)
//        {
//            e.printStackTrace();
//        }finally
//        {
//            disconnect();            
//        }
//        
//        
//        return false;
//    
//    }

    public boolean remove(String id) {
        connect();    

        try {
            String sql="delete from membership where id=?";
            stmt = conn.prepareStatement(sql);//sql전송
               stmt.setString(1, id);
            int t =stmt.executeUpdate();//sql실행요청
            if(t==1) return true;//t:수정된 행의 갯수
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            disconnect();
        }
        return false;
    }//remove


    // (전체)회원 정보 조회
    public ArrayList<MembershipVO> findAll()
    {
        connect();
        ArrayList<MembershipVO> list = new ArrayList<MembershipVO>();
        // 컬럼: id,pass,name,ssn1,ssn2,phone,addr,job
        try
        {
            String sql = "select id,name,ssn1,ssn2,phone,addr,job from membership";
            stmt = conn.prepareStatement(sql);// sql문 전송
            rs = stmt.executeQuery();// sql문 실행요청(실행시점!!)
            // 덩어리

            while (rs.next())
            {// 행얻기
                // 열데이터 얻기
                MembershipVO vo = new MembershipVO();
                // 7개의 관련있는 속성데이터를 묶어주기 위해 사용.
                vo.setId(rs.getString("id"));
                vo.setName(rs.getString("name"));
                vo.setSsn1(rs.getInt("ssn1"));
                vo.setSsn2(rs.getInt("ssn2"));
                vo.setPhone(rs.getString("phone"));
                vo.setAddr(rs.getString("addr"));
                vo.setJob(rs.getString("job"));

                list.add(vo);
            } // while
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            disconnect();
        }

        return list;
    }// findAll

    public ArrayList<MembershipVO> findByName(String name)
    {
        connect();
        ArrayList<MembershipVO> list = new ArrayList<MembershipVO>();
        // 컬럼: id,pass,name,ssn1,ssn2,phone,addr,job
        try
        {
            String sql = "select id,name,ssn1,ssn2,phone,addr,job from membership where name like ?";
            stmt = conn.prepareStatement(sql);// sql문 전송
            stmt.setString(1, "%" + name + "%");
            rs = stmt.executeQuery();// sql문 실행요청(실행시점!!)
            // 덩어리

            while (rs.next())
            {// 행얻기
                // 열데이터 얻기
                MembershipVO vo = new MembershipVO();
                // 7개의 관련있는 속성데이터를 묶어주기 위해 사용.
                vo.setId(rs.getString("id"));
                vo.setName(rs.getString("name"));
                vo.setSsn1(rs.getInt("ssn1"));
                vo.setSsn2(rs.getInt("ssn2"));
                vo.setPhone(rs.getString("phone"));
                vo.setAddr(rs.getString("addr"));
                vo.setJob(rs.getString("job"));

                list.add(vo);
            } // while
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            disconnect();
        }

        return list;
    }// findByName

    // 회원가입: UI에 입력된 데이터들을 DB테이블에 저장!!
    public boolean create(// String id,String pass,String name,int ssn1,int ssn2,String phone,String
                            // addr,String job
            MembershipVO vo)
    {
        connect();
        // 컬럼: id,pass,name,ssn1,ssn2,phone,addr,job
        try
        {
            String sql = "insert into membership (id,pass,name,ssn1,ssn2,phone,addr,job) " + "values (?,?,?,?,?,?,?,?)"; // ?:바인드변수,
                                                                                                                            // 데이터
                                                                                                                            // 채우기
            stmt = conn.prepareStatement(sql);// (데이터를 제외한)sql문을 DB에 전송
            stmt.setString(1, vo.getId());
            stmt.setString(2, vo.getPass());
            stmt.setString(3, vo.getName());
            stmt.setInt(4, vo.getSsn1());
            stmt.setInt(5, vo.getSsn2());
            stmt.setString(6, vo.getPhone());
            stmt.setString(7, vo.getAddr());
            stmt.setString(8, vo.getJob());
            stmt.executeUpdate();// 전송된 sql문에 대해 실행요청!!
            return true;
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            disconnect();
        }

        return false;
    }// create

    // 회원정보 수정(폼)에 필요한 데이터 조회(검색)
    // 컬럼: id,pass,name,ssn1,ssn2,phone,addr,job
    public MembershipVO findById(String id)
    {
        connect();
        MembershipVO vo = null;// 조회된 결과행이 없음을 표현

        try
        {
            String sql = "select id,pass,name,ssn1,ssn2,phone,addr,job from membership " + "where id=?";
            stmt = conn.prepareStatement(sql); // sql문 전송
            stmt.setString(1, id);
            rs = stmt.executeQuery();// 전송sql문에 대한 실행 요청

            if (rs.next())
            {// 아이디에 일치하는 행이 존재한다면
                vo = new MembershipVO(rs.getString("id"), rs.getString("pass"), rs.getString("name"), rs.getInt("ssn1"),
                        rs.getInt("ssn2"), rs.getString("phone"), rs.getString("addr"), rs.getString("job"));
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            disconnect();
        }
        return vo;
    }// findById

    public boolean findLogin(String id, String pass)
    {
        connect();
        /*
         * select count(*) from emp; (O) select deptno, count(*) from emp; (X) select
         * deptno, count(*) from emp group by deptno; (O) select decode(1,2,3), count(*)
         * from emp; (X) select decode(1,2,3), count(*) from emp group by decode(1,2,3);
         * (O)
         */

        try
        {
            // stmt = conn.createStatement();
            String sql = "select count(*) from membership where id=? and pass=?";
            stmt = conn.prepareStatement(sql); // DB에 sql문 전송 ==> DBMS에서 파싱

            stmt.setString(1, id);// 1: 첫번째 물음표
            stmt.setString(2, pass);// 2: 두번째 물음표

            // String sql="select count(*) from membership where id='gildong' and
            // pass='1234'";
            // String sql="select count(*) cnt from membership where id='"+id
            // +"' and pass='"+pass+"'";
            System.out.println("로그인 SQL>>>" + sql);
            // rs = stmt.executeQuery(sql);//(조회)sql문 실행요청
            rs = stmt.executeQuery();// 왜? 이미 위에서 sql문을 전송했으므로 중복해서 전송X

            if (rs.next())
            {
                // rs.getInt("count(*)") ==> (O)
                // rs.getInt("cnt") ==> (O)
                int cnt = rs.getInt(1);
                if (cnt > 0)
                    return true;
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            disconnect();
        }
        return false;
    }// findLogin

    public void checkId(String id)
    {

    }

    public String findLogin2(String id, String pass)
    {

        /*
         * <비권장> String sql="select * from membership"; ---> 만약 회원의 수의 10만이면 얻어오는 행의 수도
         * 10만!!
         * 
         * String sql="select * from membership where id=id and pass=pass"; ---> 필요한
         * 데이터는 id와 pass(2개)인데 '*'를 통해 불필요한 컬럼(6개)도 조회!!
         * 
         * String
         * sql="select id,pass from membership where id='gildong' and pass='1234'"; --->
         * 'gildong','1234' 데이터에 일치하는 조회 결과도 'gildong','1234'
         * 
         * <양호> String sql="select pass from membership where id='gildong'"; ---> 아이디에
         * 일치하는 pass정보를 DB로 부터 조회 ---> 장점) 아이디가 존재하는지 여부 비밀번호가 일치하는지 여부를 자세히 파악하기에 좋다!!
         * <권장> String sql="select count(*) from membership where id=id and pass=pass";
         * 
         */

        // String sql="select pass from membership where id='gildong'";
        connect();

        try
        {
            // stmt = conn.createStatement();
            String sql = "select pass from membership where id='" + id + "'";
            System.out.println("로그인SQL>>>" + sql);
            rs = stmt.executeQuery(sql);// sql문 DB전달, sql문 실행요청!!==> DBMS에서 파싱

            if (rs.next())
            {
                // 아이디 존재 O
                String dpass = rs.getString("pass");
                // dpass:DB에 저장된 비번 , pass:UI통해 전달된 비번
                if (dpass.equals(pass))
                {
                    // 1. 아이디 존재O, 비번 일치O
                    return "success";
                } else
                {
                    // 2. 아이디 존재O, 비번 일치X
                    return "fail_pass";
                }

            } // else {
                // 3. 아이디 존재 X
                // return "fail_id";
                // }
        } catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally
        {
            disconnect();
        }
        // 3. 아이디 존재 X
        return "fail_id";
    }// findLogin2

    private void connect()
    {// 연결객체생성
        try
        {
            conn = DriverManager.getConnection(pro.getProperty("url"), pro);
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    private void disconnect()
    {// DB자원반환
        try
        {
            if (conn != null)
                conn.close();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}// MembershipDAO

Controller

public class Controller implements ActionListener
{
    LoginForm loginForm; // 시작뷰
    JoinForm joinForm;
    UpdateForm updateForm;
    ServiceForm serviceForm;

    public Controller()
    {
        loginForm = new LoginForm();
        joinForm = new JoinForm();
        updateForm = new UpdateForm();
        serviceForm = new ServiceForm();

        eventUp();
    }// 생성자

    private void eventUp()
    {// 이벤트 소스 등록
        // 로그인폼
        loginForm.bt_login.addActionListener(this);
        loginForm.bt_join.addActionListener(this);
        loginForm.tf_pass.addActionListener(this);
        // 회원가입폼
        joinForm.bt_submit.addActionListener(this);
        joinForm.addWindowListener(new WindowAdapter()
        {
            // 회원가입폼X버튼 ----> 로그인 폼
            public void windowClosing(WindowEvent e)
            {
                joinForm.setVisible(false);
                loginForm.setVisible(true);
            }
        });

        // 서비스폼
        serviceForm.bt_sel_all.addActionListener(this);
        serviceForm.bt_up.addActionListener(this);
        serviceForm.bt_del.addActionListener(this);
        serviceForm.bt_sel_name.addActionListener(this);
        serviceForm.bt_exit.addActionListener(this);
        serviceForm.addWindowListener(new WindowAdapter()
        {
            // 서비스폼X버튼 ----> 로그인 폼
            public void windowClosing(WindowEvent e)
            {
                serviceForm.setVisible(false);
                loginForm.setVisible(true);
            }
        });

        // 회원수정폼
        updateForm.bt_submit.addActionListener(this);
        updateForm.addWindowListener(new WindowAdapter()
        {
            // 회원수정폼X버튼 ----> 로그인 폼
            public void windowClosing(WindowEvent e)
            {
                updateForm.setVisible(false);
                serviceForm.setVisible(true);
            }
        });

    }// eventUp

    @Override
    public void actionPerformed(ActionEvent e)
    {
        Object ob = e.getSource();// 이벤트 발생시킨 컴포넌트의 주소

        if (ob == loginForm.bt_login || ob == loginForm.tf_pass)
        {
            // 1. 로그인폼: 로그인버튼 클릭 ==> DB로그인조회 , 서비스폼으로 이동
            // 2. UI입력 데이터 얻기
            String id = loginForm.tf_id.getText();
            String pass = new String(loginForm.tf_pass.getPassword());
            // --------------------------------
            // char []
            /*
             * str.toCharArray() str.getBytes() <--------------- ------------> char []ch
             * String str="abc" byte []b {'a','b','c'} {97,98,99} ------------->
             * <------------ String s1 =new String(ch); String s2 = new String(b); ---- ---
             * "abc" "abc"
             * 
             */

            // 3. 모델호출
            MembershipDAO dao = new MembershipDAO();
            if (dao.findLogin(id, pass))
            {
                loginForm.showMsg("로그인성공^O^*");

                serviceForm.displayTable(dao.findAll());

                loginForm.setVisible(false);
                serviceForm.setVisible(true);
            } else
            {
                // loginForm.showMsg("로그인실패T.T*");
                loginForm.showMsg("아이디 또는 비밀번호가 일치하지 않습니다!!");
            }

//                 String result = dao.findLogin(id, pass);
//                 if(result.equals("success")) {
//                     loginForm.showMsg("로그인성공^O^*");
//                 }else if(result.equals("fail_id")) {
//                     loginForm.showMsg("아이디가 존재하지 않습니다!!");
//                     //confirm("회원가입하시겠습니까?");
//                 }else if(result.equals("fail_pass")) {
//                     loginForm.showMsg("비밀번호가 일치하지 않습니다!!");
//                 }

            // 4. 서비스폼 이동
        } else if (ob == joinForm.bt_submit)
        {// 회원가입폼: 등록버튼 클릭시

            // 컬럼: id,pass,name,ssn1,ssn2,phone,addr,job
            // UI에 입력된 데이터 수집!!
            String id = joinForm.tf_id.getText();
            String pass = new String(joinForm.tf_pass.getPassword());
            String name = joinForm.tf_name.getText();
            String ssn1 = joinForm.tf_ssn1.getText();
            String ssn2 = new String(joinForm.tf_ssn2.getPassword());
            String phone = joinForm.tf_phone1.getText() + "-" + joinForm.tf_phone2.getText() + "-"
                    + joinForm.tf_phone3.getText();
            // phone="010-1234-5678"
            String addr = joinForm.tf_addr.getText();
            String job = joinForm.cb_job.getSelectedItem().toString();

            MembershipVO vo = new MembershipVO(id, pass, name, Integer.parseInt(ssn1), Integer.parseInt(ssn2), phone,
                    addr, job);

            MembershipDAO dao = new MembershipDAO();
            // ★
            if (dao.create(vo))
            {// DB입력이 성공했다면 (회원가입성공)
                joinForm.showMsg("회원가입성공!!");
                // 회원가입폼 ----> 로그인폼 이동
                joinForm.setVisible(false);
                loginForm.setVisible(true);
            } else
            {
                joinForm.showMsg("입력값을 확인하세요!!");
            }

        } else if (ob == loginForm.bt_join)
        {// 로그인폼 : 신규가입버튼 클릭시
            loginForm.setVisible(false);
            joinForm.setVisible(true);
        } else if (ob == serviceForm.bt_sel_all)
        {// 서비스폼: 전체보기 버튼 클릭시
            MembershipDAO dao = new MembershipDAO();
            ArrayList<MembershipVO> list = dao.findAll();
            serviceForm.displayTable(list);
        } else if (ob == serviceForm.bt_up)
        {// 서비스폼: 수정 버튼 클릭시
            // findById호출을 위해 아이디 값 얻어오기
            String id = serviceForm.showInput("수정할 아이디:");
            // ------------------------------------------------
            MembershipDAO dao = new MembershipDAO();
            MembershipVO vo = dao.findById(id);
            // ------------------------------------------------
            if (vo == null)
            { // DB에 일치하는 아이디가 존재하지 않는다면
                serviceForm.showMsg("존재하지 않는 아이디입니다!!");
                return;
            }
            // 수정폼에 조회결과(vo)를 출력!!
            updateForm.initText(vo);

            // 이동 : 서비스폼 ---> 회원정보 수정폼
            serviceForm.setVisible(false);
            updateForm.setVisible(true);
        } else if (ob == updateForm.bt_submit)
        {// 회원정보수정폼 : 등록(수정)버튼 클릭시
            // 수정할 정보(데이터) 수집
            String phone = updateForm.tf_phone1.getText() + "-" + updateForm.tf_phone2.getText() + "-"
                    + updateForm.tf_phone3.getText();

            // 수집된 정보를 한개의 변수명(vo)으로 정의
            MembershipVO vo = new MembershipVO();
            vo.setId(updateForm.tf_id.getText());
            vo.setPass(new String(updateForm.tf_pass.getPassword()));
            vo.setPhone(phone);
            vo.setAddr(updateForm.tf_addr.getText());
            vo.setJob(updateForm.cb_job.getSelectedItem().toString());
            // ----------------------------------------------------
            MembershipDAO dao = new MembershipDAO();
            if (dao.modify(vo))
            {
                // ----------------------------------------------------
                // DAO실행결과를 반영(뷰이동, 뷰의 내용을 변경)
                serviceForm.displayTable(dao.findAll());

                updateForm.setVisible(false);
                serviceForm.setVisible(true);
            } else
            {
                updateForm.showMsg("다시!");
            }
        } else if (ob == serviceForm.bt_del) //회원 삭제 폼
        {
            String id = serviceForm.showInput("삭제할 아이디 : ");

            MembershipDAO dao = new MembershipDAO();

            if (dao.remove(id))
            {
                serviceForm.displayTable(dao.findAll());
            }else
            {
                serviceForm.showMsg("삭제 실패! 관리자에게 문의하세요");
            }
        } else if (ob == serviceForm.bt_sel_name) //서비스 폼 : 이름검색 버튼 클릭시
        {
            String name = serviceForm.showInput("조회할 이름 : ");

            MembershipDAO dao = new MembershipDAO();
            ArrayList<MembershipVO> list = dao.findByName(name);

            //조회된 결과 뷰에 반영
            serviceForm.displayTable(list);
        } else if (ob == serviceForm.bt_exit) //서비스폼 : 종료 버튼 클릭시
        {
            System.exit(0);
        }

    }// actionPerformed

    public static void main(String[] args)
    {
        new Controller();
    }

}

궁금상자
<Statement와 PreparedStatement>
    가장 큰 차이점은 cache사용 여부,
    Statement는 쿼리 수행시마다, 
    1. 쿼리 문장 분석
    2. 컴파일
    3. 실행 을 반복하게 됨.

    PreparedStatement는 한번만 세 단계를 거친 후,
    캐시에 담아 재사용을 함. DB 부하 감소
    SQL Injection 방지, 

    ---
    ▶ Statement (java.sql.Statement)
   Statement에서는 executeQuery("DQL문"),excecuteUpdate("DML문")메소드를 
     실행하는 시점에 파라미터로 SQL문을 DB에 전달한다.   

    String sql="insert into person values (1,'"+name+"',13,'학생')";

    stmt = conn.createStatement();
    stmt.executeUpdate(sql);  ==> 사용자가 입력한 데이터를 sql문과 조합해서 최종 sql문 생성
                                    ==> 생성된 sql문 실행!!

   장점 : 사용된 SQL문 전체를 명확히 알 수 가 있어서 디버깅이 쉽다.
   단점 : 조건값이 틀린 많은 수의 SQL문을 반복 실행하게 되는 경우, DB서버에서 모두 새롭게 
     PARSING되어야 하므로 부하가 생길수 있다.
     SQL Injection에 취약.
     -------------
     SQL문장 주입  ==> 사용자가 입력한 데이터가 SQL문장을 구성할 수 있다!!
     예)select count(*) cnt from membership where id='a' or 1=1 --' and pass='1234'
                                                   ------------
                                                                                              사용자가 입력한 데이터
                                                   ----------------
                                                   sql구문으로 변환                                                                                              


-----------------------------------------------------------------------
▶ PreparedStatement(java.sql.PreparedStatement)
  PreparedStatement는 커넥션에서 생성하면서 SQL문이 DB에 전송되어진다.
  Statement클래스를 상속하고 있음  

  String sql="insert into person values (?,?,?,?)";
                                         ---
                                         ? : 바인드 변수

    stmt = conn.prepareStatement(sql);  ===> (데이터가 빠진)sql문 전송!!
       //?의 수만큼 설정(데이터 설정)!!
       stmt.setInt   (1,   1);
       stmt.setString(2,  name);
       stmt.setInt   (3,   13);
       stmt.setString(4,  "학생");

    stmt.executeUpdate(); ==> ※주의: execute()메소드내에 sql기입하면 안됨.
       ==> 장점1) 작은따옴표, 큰따옴표, 콤마의 조합 그리고 자료형에 신경쓰지 않아도 됨.
                    장점2) 사용자가 입력한 (sql구문)데이터가 변경될 염려가 없다.


  장점: bind변수를 사용하여 DB서버에서 파싱된 SQL을 재사용하게 만듬으로, 
           반복적인 다량의 SQL수행시 성능상 이득이 있다.
           반복 루프를 통해서 하나의 SQL문에 변수값만 입력하며 반복 실행 할 수 있음.
         ★   SQL injection예방의 방법이 될 수 있다.     
  단점:
     오류발생 시, 변수에 입력되는 값을 알 수 없어서 디버깅이 어렵다.
궁금상자
- Properties
    Hashtables의 하위 클래스, Map의 속성 Key와 Value를 갖고, 파일 입출력을 지원

- Hash는 내부적으로 배열을 사용하여 데이터를 저장하는 것, 빠른 검색 속도.
  Hashtable은 Hash의 key값이 불규칙함으로 고유한 숫자를 만들어 낸 뒤 이를 인덱스(hash code)로 사용한 구조.
  Hash function(hash method) 사용하여 Hash code 반환.

- Hashtables는 key와 value에 null을 허용하지 않음.

- HashMap은 key값은 중복되지 않고, value는 중복이 허용
- Map이란 Key와 Value를 하나의 쌍으로 묶어 저장, List형태의 조상?! 
- 느리지만 많은양의 데이터 검색하는데 뛰어남

- HashMap와 Hashtable은 동기화 유무의 차이
    HashMap은 동기화 지원 안함 (thread-not-safe)
    Hashtable은 동지화 지원 (thread-safe)
    Hashmap은 멀티쓰레드 환경에서 사용하지 않는 걸 권장
    ---
    Hashmap은 Value에 Null허용
    Hashtable은 Null 허용 안함

102210_0507_javacollect1

 


190611_Day42 <기존 코드 대입> 어제에 이어서...


M

  • DAO , 데이터를 가지고 Calculator

V

C

  • 요청분석
  • 데이터 얻기
  • 모델호출
  • 뷰호출
  • 유효성검사

<작업순서>

어제에 이어서 개선하고, 추가하자!

  1. 입력폼 : X버튼 클릭시 '메인뷰'로 이동

  2. 입력폼 : 입력버튼 클릭시

    • '입력폼에 입력한 데이터들 얻어와서 하나로 모으기 '
    • 데이터 (3개) 얻어오기 => 하나의 변수명으로 저장하기
    • 알맞은 모델호출 (Insert)
      • boolean insert (Person p)
    • 입력이 잘 되었을 때 메인뷰로 이동
      • => 이동 전 전체 Person의 정보 얻어오기
      • => 알맞은 모델 호출 (selectAll)
      • => 얻어 온 정보를 메인뷰에 출력
      • => Main뷰 클래스에 출력하는 메소드 정의 (예 : displayTable)
  3. 메인뷰 : 수정버튼 클릭시

    • 모델호출에 필요한 수정번호(사용자로부터) 얻기

      • 방법1) JTable에 선택된 행의 번호 얻기
      • 방법2) JOptionPane에 입력창을 통한 입력된 번호 얻기
    • 이동 전 수정 폼에 출력될 내용 (DB로부터) 조회 : 모델호출

      • => Person select (int no)
    • 메인뷰 => 수정폼 이동

  4. 수정폼 : 수정버튼 클릭시

    • 데이터 (2개) 얻어오기
      • 하나의 변수명으로 저장하기
    • 알맞은 모델호출(update)
      • boolean update (Person p)
    • 수정이 잘 되었을 때 메인뷰로 이동
      • 이동 전 전체 Person의 정보 얻어오기
        • 알맞은 모델호출 (selectAll)
      • 정보 (수정 후 결과) 를 메인뷰 (JTable)에 출력
        • Main뷰 클래스에 출력하는 메소드 정의 ( 예 : displayTable)
  5. 메인뷰 : 삭제버튼 클릭시

    • JOptionPane의 inputDialog를 통해 삭제할 번호 입력

    • 알맞은 모델(DAO) 호출 (delete)

      • boolean delete (int no)
    • 수정이 잘 되었을 때 메인뷰로 이동

      • 이동 전 전체 Person의 정보 얻어오기
        • 알맞은 모델호출 (selectAll)
      • 정보 (삭제 후 결과) 를 메인뷰 (JTable)에 출력
        • Main뷰 클래스에 출력하는 메소드 정의 ( 예 : displayTable)
      • 삭제가 잘 되지 않았을 때
        • 실패메시지 출력
  6. 유효성검사

    • 사용자가 입력한 데이터를 대상으로!
    • 입력폼, 수정폼에서 빈값에 대한 체크
    • 입력폼, 수정폼에서 나이에 숫자값 체크
    • 삭제번호 입력 -> 숫자값 체크
  7. 기타

    • 입력폼의 TextField 초기화

      • InputForm에 메소드 추가 : void initText(){

                                                 예:  tf_job.setText("");
                                       }
                                        ---> 입력폼 호출시
                                        ---> 입력폼: 취소버튼 클릭시
        • 수정폼의 취소버튼 클릭시 : 처음 출력된 데이터를 다시 출력
        • ---> UpForm에 메소드 추가 : void initText(){
                                                     예:  tf_job.setText("학생");
                              }
        • JTable범위밖의 데이터가 출력될때 ==> 스크롤바의 위치(포커스)를 하단으로 이동.

DAO

package j0611.model.dao;

import java.sql.Statement;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;

import j0610.model.dto.Person;

public class PersonDAO
{
    Connection conn;
    Statement stmt;
    ResultSet rs;

    Properties pro;


public PersonDAO()
{
    try
    {
        pro = new Properties();
            pro.load(new FileReader("conn/conn.properties"));
            Class.forName(pro.getProperty("driver"));
    }catch (Exception e) {
        e.printStackTrace();
    }
}
//public boolean insert(자바빈즈) {}
public boolean insert(Person p) {
//또는public void insert(Person p) {}
  connect();

  try {
    stmt = conn.createStatement();
      String sql="insert into person (no,name,age,job) values "
              //+ "(1, '홍길동' , 13 , '학생' )";
             + "(person_seq.nextval, '"+p.getName()+"' , "+p.getAge()
              +" , '"+p.getJob()+"' )";
      System.out.println("추가SQL==> "+ sql);
      stmt.executeUpdate(sql);//DB에게 입력요청
      return true;

  } catch (SQLException e) {
    e.printStackTrace();
  } finally {
      disconnect();
  }

  return false;

}//insert

//또는 public boolean update(자바빈즈) {}
public boolean update(Person p) {
   connect();    

   try {
    stmt = conn.createStatement();
       System.out.println("수정할 Person>>>"+ p);
       //String sql="update person set age=16, job='학생2' where no=3";
       String sql="update person set age="+p.getAge()+", job='"+p.getJob()+
                   "' where no="+p.getNo();
       System.out.println("수정 SQL>>>"+ sql);
       int t = stmt.executeUpdate(sql);//수정요청!!
       System.out.println("수정된 행의 갯수>>>"+ t);
       if(t==1)//수정이 성공하였다면 
         return true;
   } catch (SQLException e) {
       e.printStackTrace();
   } finally {
       disconnect();
    }

   return false;
}//update
//또는public void update(Person p) {}

//public void delete(프라이머리 키) {}
public boolean delete(int no) {
   connect();

   try {
    stmt = conn.createStatement();
       //String sql="delete from person where no=3";
       String sql="delete from person where no="+no;
       int t = stmt.executeUpdate(sql);//(삭제)실행요청
       if(t==1) {
           return true;
       }
    } catch (SQLException e) {
    e.printStackTrace();
    }finally {
        disconnect();
    }

   return false;
}//delete
//또는 public void delete(int no) {}

//public void select(프라이머리 키) {}
public Person select(int no) {//한 개의 Person정보 얻어오기 --> 수정폼
  connect();

  try {
    stmt = conn.createStatement();
      String sql="select name,age,job from person where no="+no;
      rs = stmt.executeQuery(sql);

      if(rs.next()) {//조회된 행이 있다면
          //DB로 부터 데이터 얻기  : rs.get~()
           //rs.getString("name");
           //rs.getString("age");//"13"
           //rs.getInt("age");// 13
           //rs.getString("job");

          Person p = new Person( no, rs.getString("name"),
                                 rs.getInt("age"),
                                 rs.getString("job")); 
        return p;  
      }//else {
          //특정 번호 없음!!
      //}
   } catch (SQLException e) {
    e.printStackTrace();
   } finally {
       disconnect();
   }

  return null;//번호에 일치하는 Person정보를 얻지 못했을때
}//select


public ArrayList<Person> selectAll() {//모든 Person 정보 얻어오기
  connect();    
  ArrayList<Person> list = new ArrayList<Person>();//(Person)데이터 담는 바구니

  try {
    stmt = conn.createStatement();
      String sql="select no,name,age,job from person order by no";
      rs =stmt.executeQuery(sql);
       while(rs.next()) {
         /*  
          int no= rs.getInt("no");//rs.get~() ==> DB로 부터 데이터 얻기
          String name = rs.getString("name");
          int age = rs.getInt("age");
          String job = rs.getString("job");

          //4개의 변수를 하나의 변수로 저장
          Person p = new Person(no, name, age, job);
                             //p.getNo()
          list.add(p);
             //list.get(0)
             //list.get(0).getNo()
         */

           list.add(new Person(rs.getInt("no"), rs.getString("name"),
                               rs.getInt("age"), rs.getString("job")));
       }
    } catch (SQLException e) {
       e.printStackTrace();
     }finally {
       disconnect();
     }
  return list;    
}//selectAll


private void connect() {
      try {
        conn = DriverManager.getConnection(pro.getProperty("url"),pro);
                                           //pro.getProperty("user"),  
                                           //pro.getProperty("pwd"));

    } catch (SQLException e) {
        e.printStackTrace();
    }   
  }//connect

private void disconnect() {
      try {
            //연결끊기(DB자원반환)
            if(conn != null)conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
}//disconnect


}//PersonDAO

DTO

package j0610.model.dto;

public class Person
{
    // 속성정의
    private int no;
    private String name;
    private int age;
    private String job;

    public Person()
    {

    }

    public Person(int no, String name, int age, String job)
    {
        this.no = no;
        this.name = name;
        this.age = age;
        this.job = job;
    }

    public int getNo()
    {
        return no;
    }

    public void setNo(int no)
    {
        this.no = no;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public int getAge()
    {
        return age;
    }

    public void setAge(int age)
    {
        this.age = age;
    }

    public String getJob()
    {
        return job;
    }

    public void setJob(String job)
    {
        this.job = job;
    }

    @Override
    public String toString()
    {
        return "Person [no=" + no + ", name=" + name + ", age=" + age + ", job=" + job + "]";
    }

//    @Override
//    public String toString() {        
//        return no+": ["+name+","+age+","+job+"]";
//    }

}// class Person

Controller

package j0610.control;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.util.ArrayList;

import javax.swing.JOptionPane;
import javax.swing.JTable;

import j0610.model.dao.PersonDAO;
import j0610.model.dto.Person;
import j0610.view.InputForm;
import j0610.view.MainView;
import j0610.view.UpForm;

public class Controller implements ActionListener
{
    /*
     * <컨트롤러의 역할>★ - 전체 프로그램에 대한 흐름 제어!! 1. 사용자 요청 분석 (예: 어떤 버튼을 눌렀는지) 2. 사용자 입력 데이터
     * 얻어오기 (예: 입력폼 또는 수정폼을 통해 입력된 데이터) 3. ★모델객체생성(예: PersonDAO) - 메소드호출 - 결과값 (리턴값)
     * 얻기, 저장, 판단 4. 페이지(뷰) 이동 (예: 메인(JTable) ----> 입력폼)
     * 
     * 선택사항) 유효성 검사(valid check) <=== 사용자가 입력한 데이터에 대한!!
     * 
     */

    // 뷰등록
    MainView mainView;
    InputForm form;
    UpForm upForm;

    public Controller()
    {
        // 객체생성
        mainView = new MainView();
        mainView.displayTable(new PersonDAO().selectAll());
        form = new InputForm();
        upForm = new UpForm();

        eventUp();
    }// 생성자

    private void eventUp()
    {// 이벤트 소스 등록
        // 메인뷰
        mainView.bt_insert.addActionListener(this);
        mainView.bt_update.addActionListener(this);
        mainView.bt_del.addActionListener(this);
        mainView.bt_exit.addActionListener(this);

        // 입력폼
        form.bt_submit.addActionListener(this);
        // form == JFrame == Window
        form.addWindowListener(new WindowAdapter()
        {
            // class 무명 extends WindowAdapter{ }
            @Override
            public void windowClosing(WindowEvent e)
            {// 윈도우 프레임 우측상단X버튼 클릭시
                form.setVisible(false);
                mainView.setVisible(true);
            }
        });

        // 수정폼
        upForm.bt_submit.addActionListener(this);
        upForm.addWindowListener(new WindowAdapter()
        {
            // class 무명 extends WindowAdapter{ }
            @Override
            public void windowClosing(WindowEvent e)
            {// 윈도우 프레임 우측상단X버튼 클릭시
                upForm.setVisible(false);
                mainView.setVisible(true);
            }
        });

    }// eventUp

    @Override
    public void actionPerformed(ActionEvent e)
    {
        Object ob = e.getSource();// action이벤트를 발생시킨 이벤트 소스의 주소 얻기

        if (ob == mainView.bt_insert)
        {// 1.메인뷰 : 입력버튼 ==> 분석: 입력폼요청!!
            // 4.메인뷰 ---> 입력폼 이동!!
            mainView.setVisible(false);
            form.setVisible(true);
        } else if (ob == form.bt_submit)
        {// 1.입력폼: 입력버튼 ==> 분석: DB입력 요청!!

            // 2. 입력데이터 얻기
            String name = form.tf_name.getText();
            String age = form.tf_age.getText();
            String job = form.tf_job.getText();

            // 세개의 변수를 p변수로 정의하기(한개의 변수명으로 정의하기)
            Person p = new Person(0, name, Integer.parseInt(age), job);

            // 3. 모델 객체생성
            PersonDAO dao = new PersonDAO();
            if (dao.insert(p))
            {// 입력성공
                // 메인뷰에 출력할 (DB로부터) 전체 데이터 얻기
                ArrayList<Person> list = dao.selectAll();
                // Person []perArr = (Person[]) list.toArray();
                mainView.displayTable(list);

                // 입력폼 ----> 메인뷰
                form.setVisible(false);
                mainView.setVisible(true);
            }
        } else if (ob == mainView.bt_update)
        {
            // 1. 메인뷰: 수정버튼클릭시 ---> 수정폼 요청

            int row = mainView.table.getSelectedRow();

            if (row == -1)
            {// 선택된 행이 없다면
                // JOptionPane.showMessageDialog(mainView, "수정할 행을 선택!!");
                mainView.showMsg("수정할 행을 선택!!");
                return;
            }

            // System.out.println("선택된 행 인덱스>>>"+ row);
            // 2.
            Object value = mainView.table.getValueAt(row, 0);
            // 0 column: 번호표현
            // System.out.println("선택된 번호>>>"+ value);
            int no = Integer.parseInt(value.toString());
            // 3 <---- "3"

            upForm.upNo = no;// 수정폼 멤버에 '번호'저장

            // 3.
            PersonDAO dao = new PersonDAO();
            Person p = dao.select(no);
            // DB조회값 ----> 수정폼 전달
            /*
             * p.getName() upForm.tf_name.setText(); p.getAge() upForm.tf_name.setText();
             * p.getJob() upForm.tf_name.setText();
             */
            upForm.tf_name.setText(p.getName());
            upForm.tf_age.setText(p.getAge() + "");
            // tf_age.setText(13); (X) tf_age.setText("13"); (O)
            upForm.tf_job.setText(p.getJob());

            // 4.
            mainView.setVisible(false);
            upForm.setVisible(true);
        } else if (ob == upForm.bt_submit)
        {
            // 1. 수정폼: 수정버튼 클릭시 ----> DB수정 요청시

            // 2.
            String age = upForm.tf_age.getText();
            String job = upForm.tf_job.getText();

            // upForm.upNo//수정폼 멤버에 저장된 '번호' 찾기

            Person p = new Person();// 기본생성자 : (no=0, name=null, age=0, job=null)
            p.setAge(Integer.parseInt(age));// : (no=0, name=null, age=20, job=null)
            p.setJob(job);// : (no=0, name=null, age=20, job="장군")
            p.setNo(upForm.upNo);// : (no=4, name=null, age=20, job="장군")
            // Person p = new Person(upForm.upNo, null,Integer.parseInt(age), job);//오버로딩
            // 생성자

            // 3.
            PersonDAO dao = new PersonDAO();
            if (dao.update(p))
            {// 수정이 잘되었다면 ---> JTable에 수정내용을 반영
                mainView.displayTable(dao.selectAll());// 내용 갱신

                upForm.setVisible(false);
                mainView.setVisible(true);
            }

        } else if (ob == mainView.bt_del)
        {
            // 1.메인폼 : 삭제버튼 클릭시 ----> DB삭제 요청시

            // 2.(삭제할 번호)데이터 얻기
            String no = mainView.showInput("삭제할 번호:");

            // 3.
            PersonDAO dao = new PersonDAO();
            if (dao.delete(Integer.parseInt(no)))
            {
                mainView.showMsg("삭제성공!!");
                mainView.displayTable(dao.selectAll());
            } else
            {
                mainView.showMsg("삭제실패!!");
            }

        } else if (ob == mainView.bt_exit)
        {
            System.exit(0);
        }

    }// actionPerformed

    public static void main(String[] args)
    {
        new Controller();
    }

}
궁금상자 
DTO VO
    두개 이상의 속성을 묶어주는 역할에서는 동일함, 
    DB에서 묶어주는것 VO
    뷰에서 항목들 모아서 전달하는것이 DTO
    정도로 어제 했갈렸던것을 정리!

190610_DAY41 <기존 인메모리 코드에 DB연결 실습 및 연습>


package j0610;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCTest
{
    //채시라 기억!
    Connection conn;
    Statement stmt;
    //----------------DML업무(insert,delete,update)

    ResultSet rs;//--->조회된 결과 행열을 저장하는 클래스!!
    //----------------DQL업무(select)

    public JDBCTest()
    {
try
{
    //        --JDBC연습--
    //    [1]드라이버 로딩 ( 제품군 선택)
    //        Class.forName("패키지명.클래스명");
            Class.forName("oracle.jdbc.driver.OracleDriver"); // ojdbc6.jar에 존재

    //    [2]Connection연결객체 생성
    //        conn = DriverManager.getConnection("url", "user", "password");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
            System.out.println("db연결 성공");
//            conn.setAutoCommit(false); 


    //    [3]Statement 실행객체 생성 ==> DML, DQL 요청
            stmt = conn.createStatement();
    /*
     * drop table emp3;
     * create table emp3
     * as select * from emp;
     * 
     * 문제1) 사원테이블(emp3)에 정보를 등록하시오.
     * ===> 7000, 홍길동, 2000, 10
     * ===> 7002, 길라임, 3000, 20
     * ===> 7004, 김주원, 4000, 30
     * 
     */

//        sql문 작성
            String sql = "insert into emp3(empno, ename, sal, deptno) values(7000, '홍길동', 2000, 10)";

            // sql문 실행(요청)!! --> execute()메소드 사용!
            // 만약 DML을 실행하고 싶다!! --> int stmt.executeUpdate(sql);
            // ===> 수정, 삭제된 행의 갯수 리턴!
            // 만약 DQL을 실행하고 싶다!! --> ResultSet stmt.executeQuery(sql);
            // ===> 조회된 행열데이터 저장

//            stmt.executeUpdate(sql); // 실행(요청) 시점! (프로그램내에서는 AutoCommit발생)

            //AutoCommit하고 싶지 않다면 수정을 해야 한다.
                        //[2]번의 conn 밑에 conn.setAutoCommit(false); 
            //기본값 : conn.setAutoCommit(false); 
            //프로그램내에서 DML에 대한 AutoCommit

            sql="insert into emp3 (empno, ename, sal, deptno) values "
                    + "(7002, '길라임', 3000, 20)";
//            stmt.executeUpdate(sql);//실행(요청) 시점!!

            sql="insert into emp3 (empno, ename, sal, deptno) values "
                                + "(7004, '김주원', 4000, 30)";
//            stmt.executeUpdate(sql);//실행(요청) 시점!!

            System.out.println("입력성공!");
            conn.commit();
//            conn.rollback();


            //문제2) 길라임 사원을 삭제
            sql = "delete from emp3 where ename = '길라임'";
            int t = stmt.executeUpdate(sql); //실행요청 --> DBMS에게 SQL문 전달
            System.out.println("DB삭제 성공! = >? " + t);

            //문제3) 김주원 사원을 10번 부서로 이동
            sql = "update emp3 set deptno = 10 where ename = '김주원'";
            //sql = "update emp3 set deptno = 10 where ename IN ('김주원', '홍길동')"; 심심해서 IN 써본...거
            int y = stmt.executeUpdate(sql);
            System.out.println("부서 이동 성공 ! =? " + y);

            //문제4) 전체 사원에 대한 정보(사우너번호, 사원명, 급여, 부서번호) 출력
            //[4] ResultSet객체 생성
            // =======> 순차적으로 1.rs.next(); 2.rs.get자료형(인덱스번호); 또는 rs.get자료형("컬럼명");
            sql = "select empno, ename, sal, deptno from emp3";
            //인덱스 번호        1        2    3        4        

            /*
             *     boolean b = rs.next();
             *     ==> b : 결과행이 있으면 true, 없으면 false
             */

            /*
             * 1. rs.next()    // select count(*) from emp;
             * 2. if(rs.next()){}    //select ename from emp where empno = 7788;
             * 3. while(rs.next()){} // select ename from emp;
             * 세개가 무슨 차이가 있을까?!
             * 1. 몇개인지 알때?
             * 2. 조건 where이 들어갈때?
             * 3. 총 몇행인지 모를때 
             */

            // 실행요청 rs 에는 조회된 행열 덩어리 데이터!
            rs = stmt.executeQuery(sql);

            System.out.println("<<전체 사원 정보>>");

            System.out.println("첫번째행 존재유무: "+ rs.next());//1행
            System.out.println("==> 첫번째 데이터: "+ rs.getInt(1)+", "+ rs.getString(2)
                                +", "+ rs.getInt(3) +", "+ rs.getInt(4));

            while(rs.next()) //2행부터
            {
//                rs.getInt(1) rs.getString(2) rs.getInt(3) rs.getInt(4) 이렇게도 가능하고

                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                int sal = rs.getInt("sal");
                int deptno = rs.getInt("deptno");

                System.out.println("사원번호 : " + empno + ", 사원이름 : " + ename + ", 급여 :  " + sal + ", 부서번호 : " + deptno);
            }

            System.out.println("조회된 행 존재유무: "+ rs.next());
            System.out.println("==> 마지막 데이터에서 한번 더 넘어가면? : "+ rs.getInt(1)+", "+ rs.getString(2)
            +", "+ rs.getInt(3) +", "+ rs.getInt(4)); // 에러발생 결과집합을 모두 소모했음
            //행데이터를 얻지 못한 상태에서 rs.getInt(1)할 때 에러 발생!
            //java.sql.SQLException

} catch (ClassNotFoundException e)
{
    // TODO Auto-generated catch block
    e.printStackTrace();
} catch (SQLException e)
{
    // TODO Auto-generated catch block
    e.printStackTrace();
}


    }//생성자

    public static void main(String[] args)
    {
        new JDBCTest();
    }

}
  • 이전에 만들었던 이름등록 프로그램에 DB를 연결하자!
//NameMenuTest
package j0610;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class NameMenuTest {
  public static void main(String[] args) throws IOException {
    //반복되는 메뉴출력(화면 뷰처리)
    //번호입력을 위한 read() 또는 readLine() 메인에서 사용  
    //이름입력을 위한 readLine() 메인에서 사용  

   BufferedReader in = new BufferedReader(new InputStreamReader(System.in));       
   NameMenu menu = new NameMenu();

   String no; 
  do {      
    System.out.println("<이름메뉴>");  
    System.out.println("1.추가 2.검색 3.수정 4.삭제 5.종료");  
    System.out.print("번호입력 ==> ");
     no= in.readLine();//no= "1"  "2"   "3"   "4"   "5"

     System.out.println();
     switch(no) {
       case "1": {
                  System.out.print("이름입력: ");
                  String name= in.readLine();
                  menu.add(name);
                 }
                  //메소드호출 : 메소드명( 데이터  );
                 break;
       case "2": menu.search();
                 break;

       case "3": System.out.print("기존이름입력: ");
                 String oldName= in.readLine();

                 System.out.print("변경이름입력: ");
                 String newName= in.readLine();

                 menu.update(oldName, newName);

                 break;
       case "4": System.out.print("삭제이름입력: ");
                 String delName = in.readLine();

                 menu.delete(delName);//메소드명( 데이터 );
     }//switch


     System.out.println();
   }while(!no.equals("5"));//번호에 1,2,3,4번 입력했다면

   System.out.println("-- END --");
 /* 
    <NOT연산자>
     - 부정연산자
     - 부호 '!' 사용
     - 사용:  !(논리데이터)

     예)   !true   ----> false
        !false  ----> true
  */        

  }//main
}
//NameMenu
package j0610;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class NameMenu
{
    Connection conn;
    Statement stmt;
    ResultSet rs;


    public NameMenu()
    {

        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }

    }

    public void add(String name)
    {// DB에 이름 저장 Create
        try
        {
            //연결
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");

            //sql실행(요청)
            stmt = conn.createStatement();
            String sql = "insert into names (name) values ('"+ name + "')";
            System.out.println("추가되는 SQL : " + sql);
            stmt.executeUpdate(sql);
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally 
        {
            try
            {
                //DB자원 : Connection생성 --> Statement생성 --> ResultSet생성
                //DB자원반환은 역순! rs.close() stmt.close() conn.close()
                if( rs != null ) rs.close();
                if( stmt != null )stmt.close();

                //연결 끊기, 반드시 끊어주어야 한다.
                if( conn != null )conn.close(); // DB는 데이터 공유를 위해 사용!
                //Connection 은 유한개 --> 다른 사람을 위해 사용한 연결객체는 반환!
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }// add

    public void search()
    {// 현재 DB내의 이름들 조회 Read
        try
        {
            //연결!
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");

            System.out.println("#이름목록");
            //sql(조회) 요청
            stmt = conn.createStatement();
            String sql = "select name from names";
            rs = stmt.executeQuery(sql); //조회요청!
            //rs.next() --> rs.get자료형(컬럼인덱스 또는 "컬럼명" 또는 "별명")
            while(rs.next())//조회된 행이 존재한다면
            {
                System.out.println(" " + rs.getString(1));
                //혹은 rs.getString("name");
            }
        } catch (SQLException e)
        {

            e.printStackTrace();
        } finally
        {
            //Connection만 끊어도 충분!
            try
            {
                if(conn != null) conn.close();
            } catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }


        //연결끊기(자원반환)
    }

    public void update(String oldName, String newName)
    {// 배열에 저장된 이름을 변경 Update
        connect();
        try
        {
            //실행객체 생성
            stmt = conn.createStatement();

            //sql실행 요청
            String sql = "update names set name = '" + newName +"' where name = '" +oldName + "'"; 
            stmt.executeUpdate(sql);

        } catch (Exception e)
        {
            // TODO: handle exception
        }finally
        {
            disconnect();            
        }
    }// update

    public void delete(String delName)
    {// 배열에 저장된 이름을 삭제 Delete
        connect();
        try
        {
            //실행객체 생성
            stmt = conn.createStatement();

            //sql실행 요청
            String sql = "delete from names where name = '"+ delName + "'"; 
            stmt.executeUpdate(sql);

        } catch (Exception e)
        {
            // TODO: handle exception
        }finally
        {
        disconnect();
        }
    }// delete

    private void connect()
    {
        try
        {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    private void disconnect()
    {
        try
        {
            if (conn != null) conn.close();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}// class NameMenu

<JDBC.>

  1. 드라이버 로딩 (DB제품군 선택)

       Class.forName("클래스명");
       Class.forName("패키지명.드라이버클래스명");
  2. Connection객체 생성( 특정 DB서버 연결)

    Connection conn = DriverManager.getConnection("url정보","user정보","pwd정보");
    
        url : 접속DB서버 ip, port번호, sid
                                      //---- 서비스 id, 데이터베이스명
        user : 접속계정
        pwd  : 접속계정에 대한 비밀번호
  3. Statement객체생성 (execute("sql문") - DB에게 SQL문 전달!!)

      Statement stmt = conn.createStatement();
    
      int i = stmt.executeUpdate("전달하고자 하는 SQL(DML)문");
       // i ---> 수정 또는 삭제된 행의 갯수
    ------------------------- DML업무 ---------------------------------------
    
       ResultSet rs =  stmt.executeQuery("전달하고자 하는 SQL(DQL)문");
       //        rs ----> 조회된 행과 열의 덩어리 데이터(3행 3열, 3행1열,  1행1열) 
  4. ResultSet객체생성( 조회된 행열데이터를 저장)

       ResultSet rs =  stmt.executeQuery("전달하고자 하는 SQL(DQL)문");
         4_1
           boolean b =  rs.next();// 덩어리 데이터 중 맨 위에서 부터 한 행을 얻어오기.
                   b: 가져온 행이 있으면 true
    
            rs.next()는 보통 if 또는 while과 많이  사용!!
              if()는 where절에 primary key 비교      
              while()은 조회된 행의 갯수가 2행 이상이 예상 되어질때
    
         4_2
            rs.get자료형(int 인덱스1~);    rs.get자료형(String 컬럼명,별명);
    
                 예) select empno,         ename,          hiredate   hire  from emp;
              인덱스:         1              2                3
    
               rs.getInt(1);      
               rs.getInt("empno");      
                                 rs.getString(2);
                                 rs.getString("ename");
                                                    rs.getDate(3);
                                                    rs.getDate("hire");      (O)
                                                    rs.getDate("hiredate");  (X)
    
    
```

------------------------- DQL업무 ---------------------------------------


------

- test.properties

```java
# FileName ==> test.properties   
# Expression  ==>  propertyName=propertyValue
#                      variable=data
# Line Delimiter!!

name=gildong
age=13
job=\uD559\uC0DD
  • PropertiesTest.java
package j0610;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.Enumeration;
import java.util.Properties;

public class PropertiesTest
{
    public static void main(String[] args)
    {
        //Property : 속성
        Properties pro = new Properties(); // 속성 데이터 (문자열, text)를 담는 클래스

        //데이터 저장(입력)
        //pro.setProperty(String key, String value);
        //key : 저장 또는 검색을 위한 값( 유일한 값 )
        //value : 저장하고자 하는 문자열(text) 데이터.

        pro.setProperty("k1", "길동"); // k1키로 "길동"데이터 저장!
        pro.setProperty("k2", "라임");
        pro.setProperty("k3", "주원");

        pro.setProperty("k3", "유신");

        //데이터 출력 (조회)
        System.out.println("k1에 저장되어있는 데이터는 누구 ?! " + pro.getProperty("k1"));
        System.out.println("k2에 저장되어있는 데이터는 누구 ?! " + pro.getProperty("k2"));
        System.out.println("k3에 저장되어있는 데이터는 누구 ?! " + pro.getProperty("k3")); // 주원이 안나오고 마지막 저장한 유신이 나옴!

        System.out.println("==========================================");
        //만약 키값을 모르는 경우?!

        Enumeration enu = pro.propertyNames();
        //Enumeration : 열거형 인터페이스
        //enu = [k1, k2, k3]

        while(enu.hasMoreElements())
        {
            Object key = enu.nextElement();
            System.out.println(key + " : " + pro.getProperty(key.toString()));
        }

         //test.properties파일로 부터 속성데이터 얻어오기!!
         /*
             <properties파일작성>
             - 형식) 속성이름=속성데이터
             - 구분자)  서로 다른 속성을 라인으로 구분
             - 주의)  '=' 보통 등호 앞과 뒤에 공백을 주지 않는다!!
          */


        System.out.println("==========================================");
        try
        {
            pro.load(new FileReader("src/j0610/test.properties"));
        }catch (IOException e)
        {
            e.printStackTrace();
        }
         System.out.println("이름 : "+ pro.getProperty("name"));
         System.out.println("나이 : "+ pro.getProperty("age"));
         System.out.println("직업 : "+ pro.getProperty("job"));
         System.out.println("etc : "+ pro.getProperty("etc"));

    }
}

<미션2>
==> Person GUI에 JDBC적용하기!!

  1. person패키지의 내용 복사하기

    model.dao

    • Model ===> PersonDAO

      ​ Data(Base), Access, Object => DB전담 클래스!

model.dto

-   Person ===> 그대로 사용 또는 PersonDTO, PersonVO, PersonBean

    ​ DTO = Data Transfer Object

    ​ VO = Value Object


j0610.view

-   InputForm, MainView, UpForm 그대로 사용

com.encore.j0610.control

-   Controller 새로작성
  1. Person 테이블 생성하기

    create table person
    (
        no number constraint person_no_pk primary key,
        name varchar2(15) not null,
        age number not null,
        job varchar2(15) not null
    );
    
    drop sequence person_seq;
    create sequence person_seq
            start with 1
            increment by 1
            nocycle
            nocache; 
            --person.sql
            --이렇게 설정 안하면 2부터 시작된다!

     <컨트롤러의 역할 ★> - 전체 프로그램에 대한 흐름 제어!!
     1. 사용자 요청 분석(예 : 어떤 버튼을 눌렀는지)
     2. 사용자 입력 데이터 얻어오기 ( 예 : 입력폼 또는 수정폼을 통해 입력된 데이터)
     3. ★모델객체 생성(예 : personDAO)
         - 메소드 호출
          - 결과값 (리턴값) 얻기, 저장, 판단
     4. 페이지(뷰) 이동 (예 : 메인(JTable) ---> 입력폼)
    
     선택사항) 유효성 검사 (valid check) <== 사용자가 입력한 데이터에 대한 올바른 데이터인지 아닌지 판단!

Person 사람등록 프로그램 DB연결

Model

  • DAO
package j0610.model.dao;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

import j0610.model.dto.Person;

public class PersonDAO
{// ★DAO : Data(Base) Access Object
    // 데이터 베이스 전용 객체(DB관련된 일 전담!!)
    // ----> CRUD 작성 (Create, Read, Update, Delete)
    // insert select update delete

    Connection conn;
    Statement stmt;

    // conn.properties파일 ==> DB연결정보 저장!!
    Properties pro;

    public PersonDAO()
    {
        try
        {
            pro = new Properties(); // 속성 무
            pro.load(new FileReader("conn/conn.properties"));// 속성 4개 적재
            Class.forName(pro.getProperty("driver"));

        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }// 생성자

    // public boolean insert(자바빈즈) {}
    public boolean insert(Person p)
    {
        // 또는public void insert(Person p) {}
        connect();

        try
        {
            stmt = conn.createStatement();
            String sql = "insert into person (no,name,age,job) values "
                    // + "(1, '홍길동' , 13 , '학생' )";
                    + "(person_seq.nextval, '" + p.getName() + "' , " + p.getAge() + " , '" + p.getJob() + "' )";
            System.out.println("추가SQL==> " + sql);
            stmt.executeUpdate(sql);// DB에게 입력요청
            return true;
        } catch (SQLException e)
        {
            e.printStackTrace();
        } finally
        {
            disconnect();
        }

        return false;

    }// insert

    // 또는 public boolean update(자바빈즈) {}
    public void update(Person p)
    {
    }
    // 또는 public boolean update(Person p) {}

    // public void delete(프라이머리 키) {}
    public void delete(int no)
    {
    }
    // 또는 public boolean delete(int no) {}

    // public void select(프라이머리 키) {}
    public Person select(int no)
    {// 한 개의 Person정보 얻어오기 --> 수정폼
        return null;
    }

    public ArrayList<Person> selectAll()
    {// 모든 Person 정보 얻어오기
        return null;
    }

    private void connect()
    {
        try
        {
            conn = DriverManager.getConnection(pro.getProperty("url"), pro);
            // pro.getProperty("user"),
            // pro.getProperty("pwd"));

        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }// connect

    private void disconnect()
    {
        try
        {
            // 연결끊기(DB자원반환)
            if (conn != null)
                conn.close();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }// disconnect

}// PersonDAO
  • DTO
package j0610.model.dto;

public class Person {
   //속성정의
    private int no;
    private String name;
    private int age;
    private String job;

    public Person() {

    }

    public Person(int no, String name, int age, String job) {
        this.no = no;
        this.name = name;
        this.age = age;
        this.job = job;
    }

    public int getNo() {
        return no;
    }

    public void setNo(int no) {
        this.no = no;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    @Override
    public String toString() {
        return "Person [no=" + no + ", name=" + name + ", age=" + age + ", job=" + job + "]";
    }

//    @Override
//    public String toString() {        
//        return no+": ["+name+","+age+","+job+"]";
//    }
}//class Person

View

뷰는 딱히...

Controller

package j0610.control;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import j0610.model.dao.PersonDAO;
import j0610.model.dto.Person;
import j0610.view.InputForm;
import j0610.view.MainView;
import j0610.view.UpForm;

public class Controller implements ActionListener
{
    /*
     * <컨트롤러의 역할 ★> - 전체 프로그램에 대한 흐름 제어!!
     * 1. 사용자 요청 분석(예 : 어떤 버튼을 눌렀는지)
     * 2. 사용자 입력 데이터 얻어오기 ( 예 : 입력폼 또는 수정폼을 통해 입력된 데이터)
     * 3. ★모델객체 생성(예 : personDAO)
     *     - 메소드 호출
     *     - 결과값 (리턴값) 얻기, 저장, 판단
     * 4. 페이지(뷰) 이동 (예 : 메인(JTable) ---> 입력폼)
     * 
     * 선택사항) 유효성 검사 (valid check) <== 사용자가 입력한 데이터에 대한 올바른 데이터인지 아닌지 판단!
     * 
     */

    //뷰등록
    MainView mainView;
    InputForm form;
    UpForm upForm;

    public Controller()
    {
        mainView = new MainView();
        form = new InputForm();
        upForm = new UpForm();

        eventUp();
    }// 생성자

    private void eventUp() 
    {
        mainView.bt_insert.addActionListener(this);
        mainView.bt_update.addActionListener(this);
        mainView.bt_del.addActionListener(this);
        mainView.bt_exit.addActionListener(this);
          //입력폼
           form.bt_submit.addActionListener(this);
          //수정폼
    }

    @Override
    public void actionPerformed(ActionEvent e)
    {
        Object ob = e.getSource();//action이벤트를 발생시킨 이벤트 소스의 주소 얻기

         if(ob==mainView.bt_insert) 
        {
             //4.메인뷰 : 입력버튼
             mainView.setVisible(false);
             form.setVisible(true);
        }else if (ob == form.bt_submit) //1. 입력폼 : 입력버튼 ==> 분석 : DB분석 요청
        {
            //2. 입력데이터 얻기
            String name = form.tf_name.getText();
            String age = form.tf_age.getText();
            String job = form.tf_job.getText();

            //세 개의 변수를 p변수로 정의하기( 한개의 변수명으로 정의하기 )
            Person p = new Person(0, name, Integer.parseInt(age), job);

            //3. 모델 객체 생성
            PersonDAO dao = new PersonDAO();
            dao.insert(p);
        }
    }

    public static void main(String[] args)
    {
        new Controller();
    }
}
궁금상자
- DAO, DTO
    DAO : Data Access Object, Database의 data에 접근하기 위한 객체, 
        커넥션은 많이 발생하게 된다 이는 비효율적인 방식인데 ConnectionPool이란 Connection 객체를 미리 만들어 놓고 그것을 쓰는것이 DAO, 
        효율성을 위해 DB에 접속하는 객체를 전용으로 하나만 만들고, 
        모든 페이지(접근)에서 그 객체를 호출해서 사용하는 것, 
        DB를 사용해 데이터를 조회하거나 조작하는 기능을 전담하도록 만든 오브젝트, 
        사용자는 Interface를 DAO에 던지고 DAO는 이 인터페이스를 구현한 객체를 사용자가 편하게 쓰도록 반환.
    ======================================================
    DTO : Data Transfer Object는 Value Object로 바꿔 말할 수 있다. 
        계층 간 데이터 교환을 위한 자바빈즈! , 
        일반적으로는 로직이 없는 순수한 데이터 객체, 
        속성과 그 속성에 접근하기 위한 getter, setter 메소드로만 이루어진 클래스!
궁금상자
- DTO랑 VO랑 같은거라고? Beans와의 차이는?
    자바 빈은 특정 형태의 클래스
    VO는 계층형 구조에서 계층간 값을 전달하기 위해 자바 빈의 형태로 구현한 클래스
    DTO는 한 시스템에서 다른 시스템으로 작업을 처리하는 것

    VO는 특정한 비지니스 값을 담는 객체, 값이 같으면 동일 오브젝트
    VO a = VO(1); Vo b = VO(1); a == b
    DTO는 레이어간의 통신용도로 오가는 객체, 값이 같아도 다른 오브젝트
    DTO a = DTO(1); DTO b = DTO(1); a != b 
    이런 차이가 있다.
    그런데 대부분의 사람들은 같은 개념으로 이야기 한다.

    원래 자바 빈은 컴포넌트처럼 서드파티나, 응용프로그램에서 가져다 쓸 수 있는 개념이었으나, 
    본래의 의미는 없어지고 퍼블릭 생성자와 속성을 갖는 클래스 정도로 바뀜?! POJO와 비슷한 개념이라고 함
궁금상자
- POJO는 또 뭔데...?
    Plain Old Java Object
    어디서는 POJO = Java Bean이라고 하네...
    깡통 빈 클래스를 통해서 생성된 객체!
    어디서도 명쾌하게 답을 알려주진 않지만
    getter setter를 가진 단순한 자바 오브젝트 가 아니라
    getter setter를 가진 단순한 자바 오브젝트 는 POJO 이다가 가장 맞는 설명인듯?!
    EJB를 대체하게 되었다고 한다, 그럼...  EJB는 또 뭐야
궁금상자
- EJB
    서버를 관리하고 프로그램 관련 문제 처리 프로그램?...
    속도는 JSP Beans사용보다 느리지만 안정적인 분산시스템, 공공기관, 금융권 같은 접속자 많은곳에서 사용한다고 한다.
    그런데 복잡하고, 생산성이 떨어짐.

190504_DAY39 <복습> <바인드변수>

<Rollup & Cube> <카티션 곱> <집합연산자>


<저장 프로시저>

  • 자주 사용되는 DML, SQL을 모듈화(독립적인 객체)

  • 서로 연관있는 DQL, DML을 연결해서 실행하는 단위

    CREATE OR REPLACE PROCEDURE 프로시저명(바이트정의없는 변수선언 v2 VARCHAR2)
    IS
        --변수선언;
        v VARCHAR2(15);
    BEGIN
        조건문(IF문, IF ELSE문, IF~ELSIF~ELSE), 반복문(LOOP, FORLOOP, WHILELOOP)
        SQL문1;
        SQL문2;
        SQL문3;
    END;
    /
    사용법)
    1. CREATE PROCEDURE ~ (프로시저를 DB에 저장) 이후 @test.sql로 실행
    2. EXEC[UTE] 프로시저명();
    2-1.EXEC[UTE] 프로시저명(데이터);
        VARIABLE 변수명 자료형;
    2-2.EXEC[UTE] 프로시저명(데이터, :변수명);
    3. PRINT 변수명 

<저장함수>

  • CREATE OR REPLACE FUNCTION 함수명(바이트 정의 없는 변수선언)
    RETURN 자료형
    IS
        --변수선언
        v VARCHAR2(15);
    BEGIN
        조건문(IF문, IF ELSE문, IF~ELSIF~ELSE), 반복문(LOOP, FORLOOP, WHILELOOP)
        SQL문1;
        SQL문2;
        SQL문3;
    END;
    /
    
  • 사용법

    사용법)
    1. CREATE FUNCTION ~ (함수를 DB에 저장) 이후 @test.sql로 실행
    2. VARIABLE 바이드변수명 자료형;
    3. EXEC[UTE] : 바이드변수 := 함수명();
    4. PRINT 변수명 
  • 문제

    문제)
    부서테이블(DEPT)에 정보를 모두(모든행) 출력하시오 --> PL/SQL 사용
    DECLARE
        vdeptno NUMBER(2);
        vdname VARCHAR2(15);
        vloc VARCHAR2(15);
    BEGIN
        SELECT deptno, dname, loc INTO vdeptno, vdname, vloc
        FROM dept;
    
        dbms_output.put_line('부서번호 / 부서명 / 부서위치');
        dbms_output.put_line(vdeptno || '/' || vdname || '/' || vloc);
    END;
    /
    
    --실행하면
    --ORA-01422: exact fetch returns more than requested number of rows
    --에러발생 실제 인출은 요구된것 (1행) 보다 많은 수의 행(2행이상)을 추출합니다
    --조회(select) 결과가 2개 이상일 때 에러 발생
    --해결 Cursor사용 또는 Loop 사용

<Cursor.> 커서

  • select의 결과가 2개행 이상일 때 명시적 사용

  • 2개 행 이상을 출력하는 SELECT문을 저장하는 변수!

  • 형식

    DECLARE
        -- 변수선언, 커서(SELECT문 저장하는 객체라고 생각하면 됨) 정의
        CURSOR 커서명 IS SELECT문장;
    BEGIN
        -- 커서사용
        OPEN 커서명;
            FETCH 커서명 INTO 변수명; -- FETCH는 인출, 데이터를 뽑아온다. 데이터1행 인출!
    
        CLOSE 커서명;
    END;
    /
    
    --=====================================================================================
    DECLARE
        CURSOR cur IS SELECT deptno, dname, loc FROM dept;
            --  변수명 테이블명.컬럼명%type; ==> 특정테이블의 컬럼에 정의된 자료형 참조!
            --  변수명 테이블명%rowtype; ==> 특정 테이블이 갖는 모든 컬럼에 정의된 자료형 참조!
        vdept dept%ROWTYPE; -- %rowtype(테이블타입 - 테이블을 구성하는 모든 컬럼!)
    
    BEGIN
        OPEN cur;
            dbms_output.put_line('부서번호 / 부서명 / 부서위치');
        LOOP
            FETCH cur INTO vdept; --vdept : deptno, dname, loc(==dept테이블)
                EXIT when cur%NOTFOUND; -- 커서를 통해 인출된 행이 없다면
            dbms_output.put_line( vdept.deptno || '/' || vdept.dname || '/' || vdept.loc);
        END LOOP; --이건 while true랑 같은거임
    
    
  CLOSE cur;

END;
/


- 문제

  ```sql
  문제)
  특정 부서에 있는 사원의 사원번호, 사원명, 급여, 입사일을 출력하는 프로시저를 작성하시오
  --> 프로시저명 : dept_member(부서번호)
  --> 커서에 for문 적용 : open, getch, close 생략하는것이 가능
  -- 예 ) for vemp in cur

  DECLARE
      CURSOR cur IS SELECT empno, ename, sal, hiredate FROM emp;
      vemp emp%ROWTYPE;

  BEGIN
      DBMS_OUTPUT.PUT_LINE('사원번호 / 사원명 / 급여 / 입사일');
      FOR vemp in cur

      LOOP
          DBMS_OUTPUT.PUT_LINE('사원번호 : '||vemp.empno || ' 사원명 : '|| vemp.ename || ' 급여 : '|| vemp.sal || ' 입사일 : '|| vemp.hiredate);
      END LOOP;
  END;
  /

  --================================================
  CREATE OR REPLACE PROCEDURE dept_member(vdeptno emp.deptno%type)
  IS
      CURSOR cur
       is
       select empno, ename, sal, to_char( hiredate, 'yyyy"년 "mm"월 "dd"일"') hiredate
       from   emp
       where  deptno=vdeptno;

       cnt number := 0;
    begin

         dbms_output.put_line('< '||vdeptno||' 번 부서 >');
       dbms_output.put_line('사원번호/사원명/급여/입사일');
       dbms_output.put_line('-----------------------------------');

       for vemp in cur   -- cur의 실행결과의 행 수만큼 반복!!
       --    <-----cur에 정의된 select 실행후 한 행 리턴
       loop
           dbms_output.put_line(vemp.empno||' / '||vemp.ename||' / '||
                          vemp.sal||' / '||vemp.hiredate);
       cnt := cnt+1;
       end loop;

        dbms_output.put_line(cnt||'명의 사원이 있어요');
    end;
    /

<Package.> 패키지

  • 동일한 프로시저명 또는 함수명을 구분( NameSpace개념)

    ---------------------패키지 선언---------------------
    CREATE OR REPLACE PACKAGE 패키지명
    IS
        정의될 저장프로시저와 저장함수
    END;
    /
    ---------------------패키지 실행(body)---------------------
    CREATE OR REPLACE PACKAGE BODY 패키지명
    IS
        저장프로시저와 저장함수 정의
    END;
    /
    
    실행) EXEC[UTE] 패키지명.저장프로시저명
    -------==================================================================================
    ---------------------패키지 선언---------------------
    create or replace package encore
    is
       procedure del_ename(delEname varchar2);
       function call_bonus(vename emp.ename%type) return number;
    end;
    /
    ---------------------패키지 실행(body)---------------------
    create or replace package body encore
    is
        -- 특정사원(이름) 삭제 프로시저
        procedure del_ename(delEname varchar2)
        is
    
        begin    
            delete from emp3
            where ename= delEname;
            commit;
            dbms_output.put_line('#'||delEname||'사원이 삭제되었습니다');
       end; 
    
       -- 특정사원의 보너스 얻는 함수
       function call_bonus(vename emp.ename%type)
          return number
          is
            sal emp.sal%type;
            bonus   number;
          begin
              select sal into sal
              from emp
              where ename = vename;  
              -- 보너스 계산
              bonus := sal*2;
       return bonus;    
       end;   
    end;
    /
    --==> Package body created
    SQL> exec encore.del_ename('FORD');
    #FORD사원이 삭제되었습니다
    
    SQL> variable bonus number;
    SQL> exec :bonus  := encore.call_bonus('KING');
    
    SQL> print bonus
    
         BONUS
    ----------
         10000

<Trigger.> 트리거

  • 이벤트 처리(~했을 때 수반되는 처리)

  • 특정테이블에 이벤트가 발생했을 시 다른 테이블에 연관된 내용을 변경하도록 하는 일.

  • 형식

    CREATE [OR REPLACE] TRIGGER 트리거명
    ( BEFORE | AFTER ) ( INSERT | DELETE | UPDATE ) -- 이벤트(사건발생)
    ON 테이블명 -- 어디에서? ON, 이벤트가 발생하는 테이블
    [FOR EACH ROW] -- 실행될 문장 행에 각각 적용
    [WHEN 조건식]
    BEGIN
        --이벤트 발생시 실행할 문장(주로 DML문장)
    END;
    /
  • 문제

    문제)
    사원테이블(EMP2)에 사원정보가 새로 입력될 때마다(이벤트) 입사 환영메시지(이벤트처리 BEGIN~END)를 출력하시오.
    
    DROP TABLE emp2;
    CREATE TABLE emp2
    AS SELECT empno, ename, deptno
    FROM emp
    WHERE 1=0;
    
    -- 환영 트리거 생성
    CREATE OR REPLACE TRIGGER welcome
    AFTER INSERT ON emp2 --emp2 테이블에서 행입력이 발생했다면! (발생한 후)
    FOR EACH ROW
    BEGIN
        DBMS_OUTPUT.PUT_LINE('ENCORE 주식회사 입사를 환영합니다.');
    END;
    /
    
    --
    INSERT INTO emp2 VALUES (7000, '진주',10);
    --ENCORE 주식회사 입사를 환영합니다.
    INSERT INTO emp2 VALUES (7000, '용진',20);
    --ENCORE 주식회사 입사를 환영합니다.
    INSERT INTO emp2 VALUES (7002, '비키',30);
    --ENCORE 주식회사 입사를 환영합니다.

<바인드변수> - 매개변수, 전달변수, PL/SQL에서는 변수명 앞에 ':'을 붙여서 사용

​ :NEW - 새로 입력 (INSERT, UPDATE) 된 데이터

​ :OLD - 기존 데이터 (이미 저장되어 있는 데이터)

  • BEGIN ~ END 에서 사용법

    : NEW. 컬럼명

    ​ VALUES (데이터)

    ​ SET 컬럼명 = 데이터

    : OLD.컬럼명

    CREATE OR REPLACE TRIGGER welcome
    AFTER INSERT ON emp2
    FOR EACH ROW
    BEGIN
        DBMS_OUTPUT.PUT_LINE('[' || :NEW.ename || '] 님의 ENCORE 주식회사 입사를 축하합니다');
    END;
    /
    
    --
    INSERT INTO emp2 VALUES (7006, '딩딩', 20);
    --[딩딩] 님의 ENCORE 주식회사 입사를 축하합니다
    INSERT INTO emp2 VALUES (7008, '삑삑', 20);
    --[삑삑] 님의 ENCORE 주식회사 입사를 축하합니다
  • 문제

    문제)
    사원테이블(emp3)에 사원정보가 (empno, ename, sal, hiredate)입력외었을 때 급여테이블(salary[no,sal])에 그 사원에 대한 급여 정보가 자동으로 입력되도록 트리거를 생성하시오
    
    DROP TABLE emp3;
    
    CREATE TABLE emp3
    AS SELECT empno, ename, sal, hiredate
    FROM emp
    WHERE 1=0;
    
    CREATE TABLE salary
    (
        no NUMBER,
        sal NUMBER(7,2)
    );
    
    CREATE SEQUENCE salary_seq
    START WITH 1
    INCREMENT BY 1
    NOCYCLE
    NOCACHE;
    
    CREATE OR REPLACE TRIGGER sal_info
    AFTER INSERT ON emp3
    FOR EACH ROW
    BEGIN
        INSERT INTO salary(no, sal) VALUES(salary_seq.nextval, :new.sal);
    END;
    /
    
    --먼저 조회부터
    SELECT * FROM salary;
    
    --emp3테이블 : empno, ename, sal, hiredate
    INSERT INTO emp3 VALUES (8000, '딩딩', 3000, SYSDATE);
    INSERT INTO emp3 VALUES (8001, '용용', 4000, SYSDATE);
    INSERT INTO emp3 VALUES (8002, '빜빜', 5000, SYSDATE);
    
    --
    SELECT * FROM emp3;
    SELECT * FROM salary;
    

<테이블 만들기>

1. 상품테이블
CREATE TABLE 상품
(
    상품코드     CHAR(4) CONSTRAINT 상품_PK PRIMARY KEY,
    상품명      VARCHAR2(15) NOT NULL,
    제조사         VARCHAR2(15),
    소비자가격     NUMBER,
    재고수량     NUMBER DEFAULT 0
);

2. 입고테이블
DROP TABLE 입고;
CREATE TABLE 입고
(
    입고번호     NUMBER CONSTRAINT 입고_PK PRIMARY KEY,
    상품코드     CHAR(4) CONSTRAINT 입고_FK REFERENCES 상품(상품코드),
    입고일자     DATE DEFAULT SYSDATE,
    입고수량     NUMBER,
    입고단가     NUMBER,
    입고금액     NUMBER
);

CREATE SEQUENCE 입고_SEQ
            START WITH 1
            INCREMENT BY 1
            NOCYCLE
            NOCACHE;

<상품테이블에 정보입력>
- 상품코드, 상품명, 제조사, 소비자가격, 재고수량(POINT!)

INSERT INTO 상품 ( 상품코드, 상품명, 제조사, 소비자가격 )
            VALUES ( 'a001', '마우스', '진꼬', 1000);
INSERT INTO 상품 ( 상품코드, 상품명, 제조사, 소비자가격 )
            VALUES ( 'a002', '키보드', '꼬진', 2000);
INSERT INTO 상품 ( 상품코드, 상품명, 제조사, 소비자가격 )
            VALUES ( 'a003', '모니터', '빜빜', 5000);    

상품코드 상품명                         제조사                         소비자가격   재고수량
-------- ------------------------------ ------------------------------ ---------- ----------
a001     마우스                         진꼬                                 1000          0
a002     키보드                         꼬진                                 2000          0
a003     모니터                         빜빜                                 5000          0
--================================================================================================
1. 입력트리거 ( 입고테이블에 상품이 입력 되었을 때 재고수량 증가! )
예) 입고테이블에 키보드가 10개 입고되었을 때 자동으로 삼품테이블의 'a002'상품의 재고가 0 - - > 10으로 변경

CREATE OR REPLACE TRIGGER product_insert
AFTER INSERT ON 입고
FOR EACH ROW
BEGIN
    -- 상품테이블에 대한 [재고수량] 수정
    UPDATE 상품 SET 재고수량 = 재고수량 + :NEW.입고수량 -- 0 + 10
    WHERE 상품코드 = :NEW.상품코드 ; -- 상품코드 = 'a002'
END;
/

SELECT 상품코드, 상품명, 재고수량 FROM 상품;
상품코드 상품명                           재고수량
-------- ------------------------------ ----------
a001     마우스                                  0
a002     키보드                                  0
a003     모니터                                  0

--입고테이블) 입고번호, 상품코드, 입고일자, 입고수량, 입고단가, 입고금액
INSERT INTO 입고 ( 입고번호, 상품코드, 입고수량, 입고단가, 입고금액 )
             VALUES( 입고_seq.nextval, 'a002', 10, 1000, 10000);

--이렇게 두번 넣으면
SELECT * FROM 상품;                                ;

상품코드 상품명                         제조사                         소비자가격   재고수량
-------- ------------------------------ ------------------------------ ---------- ----------
a001     마우스                         진꼬                                 1000          0
a002     키보드                         꼬진                                 2000         20
a003     모니터                         빜빜                                 5000          0


--================================================================================================

2. 수정트리거 (입고테이블에 상품의 입고 수량이 변경되었을 때 상품테이블의 재고수량 변경)
--> product_update
--> 예) 종업원의 실수로 잘못입력된 입고번호 10 ----------->20으로 변경시

해당 상품코드에 대한 (상품테이블) 재고 수량이 40 --> 50으로 변경되어 지는

[입고]테이블의 입고 수량 변경 ----------> [상품] 테이블의 재고 수량 변경

CREATE OR REPLACE TRIGGER product_update
AFTER UPDATE ON 입고
FOR EACH ROW
BEGIN
    -- 상품테이블에 대한 [재고수량] 수정
    UPDATE 상품 SET 재고수량 = 재고수량 -:OLD.입고수량 + :NEW.입고수량
    WHERE 상품코드 = :OLD.상품코드 ; -- 상품코드 = 'a002'
END;
/

---
<상황발생>
update 입고 set 입고수량=30
            where 입고번호=2;

select 입고번호, 상품코드, 입고수량 from 입고;

select 상품코드, 상품명, 재고수량 from 상품;

--================================================================================================

3. 삭제트리거
    ----> 입고번호 2에 대한 삭제!!
          (다른 대리점에 전달되어야 하는 제품이 잘못 배달 되었음)
    ----> [상품]테이블  재고수량 50  ---> 30 변경

    create or replace trigger product_delete
    after delete on 입고   -- 이벤트: 입고테이블에서 삭제 발생시 
    for each row
    begin
      -- 입고 테이블 삭제 발생시  실행할 SQL
      update 상품 set 재고수량= 재고수량        - :old.입고수량
                         -- 50(기존재고) - 20(삭제된입고)  ==> 30      
      where  상품코드 = :old.상품코드;   --'a002'; 
    end;
    /                           

<상황발생>
 delete from 입고 where 입고번호=2;

select 입고번호, 상품코드, 입고수량 from 입고;

select 상품코드, 상품명, 재고수량 from 상품;

< Rollup과 Cube >

ROLLUP

  • GROUP BY절과 같이 사용
  • 그룹지어진 집합 결과에 좀 더 상세한 정보 반환

Cube

  • Cross-Tab에 대한 Summary를 추출하는데 사용
  • Rollup에 의해 나타나는 Item Total과 Column Total값을 나타 낼 수 있다.

결론 : 총계와 소계 구할때

ppt보기

1559635341445155963536308815596353691541559635523445

select *
from BOOK, (select rownum
from BOOK
where rownum < 5);
--96행 나옴
-------------------------------------------
select decode(rn, 2, '합계', 3, publisher||' 소계', publisher) "출판사", sum(price)
from BOOK, (select rownum rn
from BOOK
where rownum < 5)
group by decode(rn,2,'합계',3,publisher||' 소계', publisher);

-------------------------------------------

select decode(rn,2,'합계',3,publisher||' 소계', publisher) "출판사",
decode(rn,1,book_type||' 소계',4, book_type) "종류",
sum(price) "가격"
from BOOK, (select rownum rn
from BOOK
where rownum < 5)
group by decode(rn,2,'합계',3,publisher||' 소계', publisher),
decode(rn,1,book_type||' 소계',4, book_type);

-------------------------------------------
select decode(rn,2,'합계',3,publisher||' 소계', publisher) "출판사",
decode(rn,1,book_type||' 소계',4, book_type) "종류",
decode(rn,4,book_name)"책명",
sum(price) "가격"
from BOOK, (select rownum rn
from BOOK
where rownum < 5)
group by decode(rn,2,'합계',3,publisher||' 소계', publisher),
decode(rn,1,book_type||' 소계',4, book_type),
decode(rn,4,book_name)
order by decode(rn,2,'합계',3,publisher||' 소계', publisher);
-------------------------------------------
select publisher, book_type, book_name, sum(price)
from book
group by rollup(publisher, book_type, book_name);

카티션 곱(car)

oracle 8i부터 소계와 합계를 구해주는 함수 제공!!

  • Rollup : group by 순서에 의한 멀티레벨 소계 및 총계를 구해주는 함수
  • Cube : crosstab 형태의 소계를 계산할 때 사용.
SELECT job, SUM(sal)
FROM emp
GROUP BY ROLLUP(job);

JOB                  SUM(SAL)
------------------ ----------
CLERK                    4150
SALESMAN                 5600
PRESIDENT                5000
MANAGER                  8275
ANALYST                  6000

--====================================================================

select job, SUM(sal)
from emp
group by ROLLUP(job);

JOB                  SUM(SAL)
------------------ ----------
ANALYST                  6000
CLERK                    4150
MANAGER                  8275
PRESIDENT                5000
SALESMAN                 5600
                        29025

<부서별 직책 인원수와 급여합계>

SELECT dname, job, SUM(sal) sal, COUNT(*) 인원수
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP by dname,job;

DNAME                        JOB                       SAL     인원수
---------------------------- ------------------ ---------- ----------
SALES                        MANAGER                  2850          1
SALES                        CLERK                     950          1
ACCOUNTING                   MANAGER                  2450          1
ACCOUNTING                   PRESIDENT                5000          1
ACCOUNTING                   CLERK                    1300          1
RESEARCH                     MANAGER                  2975          1
SALES                        SALESMAN                 5600          4
RESEARCH                     ANALYST                  6000          2
RESEARCH                     CLERK                    1900          2
--9행 나옴
--==============================================================================
SELECT dname, job, SUM(sal) sal, COUNT(*) 인원수
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP by ROLLUP( dname,job );

DNAME                        JOB                       SAL     인원수
---------------------------- ------------------ ---------- ----------
SALES                        CLERK                     950          1
SALES                        MANAGER                  2850          1
SALES                        SALESMAN                 5600          4
SALES                                                 9400          6
RESEARCH                     CLERK                    1900          2
RESEARCH                     ANALYST                  6000          2
RESEARCH                     MANAGER                  2975          1
RESEARCH                                             10875          5
ACCOUNTING                   CLERK                    1300          1
ACCOUNTING                   MANAGER                  2450          1
ACCOUNTING                   PRESIDENT                5000          1
ACCOUNTING                                            8750          3
                                                     29025         14
--13행이 나옴, 소계 총계가 같이 나온다.
--앞에 있는거 기준으로 소분류되며 소계 총계까지 나온다.
--==============================================================================
--이번에는 job끼리 묶고 싶어 이럴 때 cube~!
--직책 기준의 소계를 알고 싶다~!
SELECT dname, job, SUM(sal) sal, COUNT(*) 인원수
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP by ROLLUP( dname,job )
    UNION
SELECT job, SUM(sal) sal, COUNT(*) 사원수 -- 직책 기준의 소계를 알고 싶다!
FROM emp
GROUP BY ROLLUP(job);
--ORA-01789: query block has incorrect number of result columns
--주의사항★ : 두 쿼리문의 칼럼수가 일치해야 하고 자료형도 일치해야 합니다.
SELECT dname, job, SUM(sal) sal, COUNT(*) 인원수
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP by ROLLUP( dname,job )
    UNION
SELECT '',job, SUM(sal) sal, COUNT(*) 사원수 -- 직책 기준의 소계를 알고 싶다!, ''빈 문자열이라도 넣어서 수 맞추기
FROM emp
GROUP BY ROLLUP(job);

DNAME                        JOB                       SAL     인원수
---------------------------- ------------------ ---------- ----------
ACCOUNTING                   CLERK                    1300          1
ACCOUNTING                   MANAGER                  2450          1
ACCOUNTING                   PRESIDENT                5000          1
ACCOUNTING                                            8750          3
RESEARCH                     ANALYST                  6000          2
RESEARCH                     CLERK                    1900          2
RESEARCH                     MANAGER                  2975          1
RESEARCH                                             10875          5
SALES                        CLERK                     950          1
SALES                        MANAGER                  2850          1
SALES                        SALESMAN                 5600          4
SALES                                                 9400          6 -- 여기까지는 위와 동일
                             ANALYST                  6000          2 -- 여기서부터 추가, 직책기준으로~!
                             CLERK                    4150          4
                             MANAGER                  8275          3
                             PRESIDENT                5000          1
                             SALESMAN                 5600          4
                                                     29025         14

--근데 CUBE사용하면 더 편해~!
SELECT dname, job, SUM(sal), COUNT(*) 사원수
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE( dname, job ); -- danme, job으로 한번 job으로 한번

DNAME                        JOB                  SUM(SAL)     사원수
---------------------------- ------------------ ---------- ----------
                                                     29025         14
                             CLERK                    4150          4
                             ANALYST                  6000          2
                             MANAGER                  8275          3
                             SALESMAN                 5600          4
                             PRESIDENT                5000          1
SALES                                                 9400          6
SALES                        CLERK                     950          1
SALES                        MANAGER                  2850          1
SALES                        SALESMAN                 5600          4
RESEARCH                                             10875          5
RESEARCH                     CLERK                    1900          2
RESEARCH                     ANALYST                  6000          2
RESEARCH                     MANAGER                  2975          1
ACCOUNTING                                            8750          3
ACCOUNTING                   CLERK                    1300          1
ACCOUNTING                   MANAGER                  2450          1
ACCOUNTING                   PRESIDENT                5000          1

18 rows selected.
  •  

<GROUPING'> (컬럼)

  • GROUPING 함수는 ROLLUP, CUBE에 모두 사용할 수 있다.
  • GROUPING 함수는 해당 컬럼의 Row가 GROUP BY에 의해서 산출된 Row인 경우에는 0을 반환하고, ROLLUP이나 CUBE에 의해서 산출된 Row인 경우에는 1을 반환하게 된다.
  • 따라서 해당 Row가 결과집합에 의해 산출된 Data 인지,
  • ROLLUP이나 CUBE에 의해서 산출된 Data 인지를 알 수 있도록 지원하는 함수이다.
GROUPING_ID(컬럼a, 컬럼b[, ...])
-- GROUPING(컬럼a)||GROUPING(컬럼b)의 값을 2진수에서 10진수로 변환한 값이다.
128,64,32,16,8,4,2,1
---------------
00000000 --> 0
00000001 --> 1
00000010 --> 2
00000011 --> 3
00000100 --> 4
00000101 --> 5

SELECT deptno, 
        empno, 
        SUM(sal) s_sal, 
        GROUPING(deptno) grp_dept,
        GROUPING(empno) grp_emp,
        GROUPING_ID(deptno, empno) gid
FROM emp e
GROUP by ROLLUP( deptno , empno );

    DEPTNO      EMPNO      S_SAL   GRP_DEPT    GRP_EMP        GID
---------- ---------- ---------- ---------- ---------- ----------
        10       7782       2450          0          0          0
        10       7839       5000          0          0          0
        10       7934       1300          0          0          0
        10                  8750          0          1          1
        20       7369        800          0          0          0
        20       7566       2975          0          0          0
        20       7788       3000          0          0          0
        20       7876       1100          0          0          0
        20       7902       3000          0          0          0
        20                 10875          0          1          1
        30       7900        950          0          0          0
        30       7499       1600          0          0          0
        30       7521       1250          0          0          0
        30       7654       1250          0          0          0
        30       7698       2850          0          0          0
        30       7844       1500          0          0          0
        30                  9400          0          1          1
                           29025          1          1          3

18 rows selected.

<집합연산자>

  • 합집합 UNION

  • 형식)

    SELECT 문
    UNION
    SELECT 문
  • 합집합 UNION ALL ( UNION + 중복데이터 )

    SELECT문1 결과 -------> [1,2,3,4,5,6]
    SELECT문2 결과 -------> [5,6,7,8,9]
    
    UNION결과 ==> [1,2,3,4,5,6 5,6,7,8,9]를 중복 제거해서 [1,2,3,4,5,6,7,8,9]로!
    
    UNION ALL결과 ==> [1,2,3,4,5,6,5,6,7,8,9]로!
  • 교집합 INTERSECT

    SELECT문1 결과 --> [1,2,3,4,5,6]
    SELECT문2 결과 --> [5,6,7,8,9]
        INTERSECT 결과 --> [5,6]
  • 차집합 MINUS(오라클의 경우), EXCEPT(SQL서버의 경우)

    SELECT문1 결과 --> [1,2,3,4,5,6]
    SELECT문2 결과 --> [5,6,7,8,9]
    
    SELECT문1
        MINUS 또는 EXCEPT
    SELECT문2
    
    결과 --> [1,2,3,4]
궁금상자
- LOOP문
        LOOP 
        [반복 될 수행문] 
        EXIT WHEN [끝낼 조건]; 
        END LOOP; 

    LOOP :   exit문 사용되었을 경우, 
            EXIT WHEN 이 사용되면 WHEN 절에 LOOP를 빠져나가는 조건 제어 가능

- WHILE LOOP
        WHILE [LOOP가 돌아갈 조건] LOOP 
        [반복 될 수행문] 
        END LOOP; 

    WHILE LOOP : FOR문과 비슷
                조건이 True일 경우에만 반복
                WHILE cnt < 100 LOOP ~ ... ~ EXIT WHEN cnd > 100

-  FOR LOOP
        FOR [반복문에서 사용할 변수 (number 형) - DECLARE에서 정의되면 안됨] 
        IN [반복 시작 상수] .. [반복 끝낼 상수] LOOP 
        [반복 될 수행문] 
        END LOOP; 

        --테이블 행수만큼 반복
            FOR  [반복문에서 사용할 변수 - DECLARE에서 정의되면 안됨]
            IN  ( [테이블] ) LOOP 
            [반복 될 수행문] 
            END LOOP; 
            -- 이때 변수는 number 형이아님
            -- 변수를 r이라고 칭하면 r에 SELECT 된 테이블이 다 들어간다고 생각하면됨
            -- 나중에 r.[필드명]으로 사용


출처: https://jyosssss.tistory.com/36 [개미는 뜐뜐]
궁금상자2
- TRIGGER 왜 쓰는거야? 그냥 IF문 쓰거나 명령어 이어 쓰면 안됨?
궁금상자3
- WHERE 1=0;

 

190605_Day40 <JDBC.>

<JDBC.> Java DataBase Connectivity

  • JDBC 관련 클래스 (java.sql 패키지)

    • DriverManger

      • JVM에서 JDBC전체 관리, Driver등록, Connection 연결작업등..

    • Driver

      • 자신들의 DB를 Vendor(Oracle등)에 연결 할 수 있는 Class를 만드는 인터페이스

    • 채시라--------- 밑에 3개가 중요 순차적으로

    • Connection

      • DB와 연결성을 갖는 인터페이스

    • Statement

      • SQL문을 실행하는 인터페이스

    • ResultSet

      • 조회된 결과 데이터를 갖는 인터페이스

JDBC 프로그래밍

  1. 제품군 선택 (Driver loading) : Oracle? SQLite? 뭐쓸래?

  2. 연결 객체 생성 (Connection) : 서로다른 IP(DB마다 할당된) 중 원하는 DB에 연결하고 싶다라고 하는

    • DB서버의 주소가 필요

    • 포트번호 (한 PC안에서 무슨 서비스를 사용할지 정하기 위해)

    • 계정, 비밀번호

  3. 실행 객체 생성 (Statement)

    • Sql문 작성

  4. 결과 객체 생성 (ResultSet)

    • 행 단위 데이터 얻기(뽑기)

    • 열 데이터 얻기(뽑기)

![1559697810645](C:\Users\Playdata\Documents\K\190605_40일차.assets\1559697810645.png)

 


190603_DAY38 <복습>, <조건문>,<반복문>, ,

<PL/SQL>

  • SQL언어에 절차적인 프로그래밍 언어 요소(조건문,반복문)를 가미해 만든 것

  • 여러개의 SQL문(DQL,DML) 사이에 연결관계를 표현

    select 컬럼명 into 변수명

    from 테이블명

    if 변수명 < 10
    then insert into~ end if;

<PL/SQL구조>

declare -- 선언부

--   실행부에서 사용하는 변수에 대한 선언!!
    a number(4); -- 변수선언
    b emp.ename%type; -- 변수선언( 존재하는 테이블안의 컬럼이 갖는 자료형을 참조 )
    c number(2) := 30;

begin --실행부 시작
    a := 300;

    SQL문1 작성
    SQL문2 작성
    SQL문3( DQL, DML ) 작성--------1번 실행
           ---- 주의 select empno, ename, sal into a, b, c 
               --        from emp;
               dbms_output.put_line(empno); -- X ==> 조회된 컬럼명 empno 인식 불가
               dbms_output.put_line(a); -- O ==> 조회된 사원번호를 begin~end 사이에서 a변수로 사용!

            if(조건식)
                then SQL문4; -- 조건식 참일때 -----0,1번 실행
            end if; (if문 종결문, {} 못씀)

            for()
            {
                SQL문5; -- 0 ~ 무한대 실행
            }
end; -- 실행부 끝
/

<조건문> begin~end사이에 들어갈 수 있는

  1. IF문

    • 형식

      IF 조건식
          THEN --시작
              조건식 결과가 참일 때 실행할 (SQL)문장;
      END IF; -- 끝
  2. IF ~ ELSE문

    • 형식

      IF 조건식
          THEN 조건식 결과가 참일 때 실행할 문장;
          ELSE 조건식 결과가 거짓일 때 실행할 문장;
      END IF;
    • 문제

      문제) 특정사원이 커미션을 받는지 안 받는지 구분해서 출력하시오
          예1 : 사번 7788은 SCOTT사원이고 커미션을 받지 않습니다.
      
          예2 : 사번 7654는 MARTIN사원이고 커미션을 1400 받습니다.
          => IF_ELSE_TEST.SQL
      
      declare 
          vEMPNO emp.empno%type;
          vENAME emp.ename%type;
          vCOMM emp.comm%type;
      
      begin
          select empno, ename, comm into vEMPNO, vENAME, vCOMM -- v는 변수명
          from emp
          where empno = 7654;
      
          IF (vCOMM IS NOT NULL)
                      THEN
                          dbms_output.put_line( vEMPNO ||'은(는) '||vENAME ||'사원이고 커미션을' || vCOMM ||'받습니다.');                    
                      ELSE
                          dbms_output.put_line( '커미션 안받음' );
          END IF;
      end;
      /
      문제) 특정사원 한 명의 사원번호, 사원명, 연봉을 출력하시오.
           (연봉계산은 급여*12+comm)
      
          ==> where empno=7788;   where empno=7654;
          ==> 예) 사번 7788은 SCOTT사원이고 연봉은 36000입니다.
      
      declare 
          vempno emp.empno%type;
          vename emp.ename%type;
          vsal emp.sal%type;
          vcomm emp.comm%type;
      
          annsal number;
      begin
          select empno, ename, sal, comm into vempno, vename, vsal, vcomm
          from emp
          where empno = 7788;
      
      
      
     IF(vcomm is null)
         then
             annsal := vsal * 12 + 0;
         else
             annsal := vsal * 12 + vcomm;
     END IF;
             dbms_output.put_line('사번' || vempno || '은(는) ' || vename || 
                                    '사원이고 ' || '연봉은' || annsal || ' 입니다.' );
 end;
 /
 ```
  1. IF ~ ELSIF ~ ELSE 문

    • 형식

      IF (조건식1)
          THEN 조건식1 참일때 실행할 문장;
          ELSIF (조건식2)
              THEN 조건식1이 거짓이고 조건식2 참일때 실행할 문장
          ELSIF (조건식3)
              THEN 조건식1,2가 거짓이고 조건식3 참일때 실행할 문장
          ELSE 조건식1,2,3 이 거짓일 때 실행할 문장
      END IF;
    • 문제

      문제) 특정사원의 사원번호, 사원명, 소속된 부서명을 출력하시오
      예 : 사번 7788은 SCOTT 사원이고 RESEARCH 부서에 근무합니다.
      if_elsif_test.sql
      
      declare
          vempno emp.empno%type;
          vename emp.ename%type;
          vdeptno emp.deptno%type;
          vdname dept.dname%type;
      
      begin
          vempno := 7788;
      
          select ename, deptno into vename, vdeptno
          from emp
          where empno = vempno; -- 여기서 empno 써서 select에 안써도 됨.
      
          if (vdeptno = 10)
              then vdname := 'ACCOUNTING';
          elsif (vdeptno = 20)
              then vdname := 'RESEARCH';
          elsif (vdeptno = 30)
              then vdname := 'SALES';
          else
              vdname := 'OPERATION';
          end if;    
      
          dbms_output.put_line('사번' || vempno || '은(는) ' ||
                                          vename || '사원이고 ' || vdname || '부서에 근무합니다.');
      
      end;
      /

<반복문>

  1. LOOP (반복문)

    • 형식

      LOOP
          반복실행할 문장1;
          반복실행할 문장2;
          반복실행할 문장3;
          EXIT [WHEN 조건식]; -- 탈출조건
      END LOOP;
    • 문제

      문제) 1부터 10까지 출력하세요
      loop_test.sql
      declare
      
          i number := 0;
      
      begin
          loop
              i := i + 1;
              dbms_output.put_line( i || '번째  안녕~!' );
              exit when i = 10;
          end loop;
      end;
      /
      
      문제2) 화면에 3, 6, 9를 출력하시오
      declare
      
          j number := 0;
      
      begin
          loop
              j := j + 3;
              dbms_output.put_line( j );
              exit when j = 9;
          end loop;
      end;
      /
  2. FOR LOOP

    • 형식

      FOR 변수 IN 최소값.. 최대값
      LOOP
          반복 실행할 문장;
      END LOOP;
    • 문제

      문제) 화면에 '나는 SQL이 좋아요!! '를 10번 출력하시오.
      for_test.sql
      
      declare
      
      begin
          FOR i in 1..10
          LOOP
          dbms_output.put_line('나는 딩꼬가 좋아요!!==> ' || i);
          END LOOP;
      
          dbms_output.put_line('++++++++++++++++++++++++');
          FOR i in 10..1
          LOOP
          dbms_output.put_line('10부터 1까지는 에러는 없지만 출력도 안되네');
          END LOOP;
      
          dbms_output.put_line('++++++++++++++++++++++++');
          FOR i in reverse 1..10
          LOOP
          dbms_output.put_line('in 뒤에 reverse 사용해서 10부터 1까지' || i);
          END LOOP;
      end;
      /
      
      문제) 3단에서 5단까지 출력하시오.
      for_loop 사용 
      for_dan.sql
      
      declare
      
      begin
          FOR dan in 3..5
          LOOP
              dbms_output.put_line('< ' || dan || '단 >');
              FOR i in 1..9
              LOOP
               dbms_output.put_line( dan || ' X ' || i || ' = ' || dan*i);
      
          END LOOP;
          END LOOP;
      
      end;
      /
  3. WHILE LOOP

    • 형식

      WHILE 조건식
      LOOP
          반복실행문장;
      END LOOP;
    • 문제

      문제) 1부터 10까지의 합을 출력하시오
      declare
          i number := 1;
          total number := 0;
      begin
          while i <= 10
          loop
              total := total+i;
              i := i+1;
          end loop;
      
          dbms_output.put_line('total = ' || total);
      end;
      /

<Stored Procedure'> ★저장 프로시저

  • 자주사용되는 DQL, DML 을 모듈화 시켜서 DB에 저장하였다가 필요할 때 호출해서 사용!

  • JAVA의 메소드와 같은 역할

  • 형식)

    CREATE [OR REPLACE] PROCEDURE 프로시저명( 변수명1 [IN|OUT] 자료형, 변수명2 자료형)
    IS
        변수선언;
    BEGIN
        실행할 문장;
        실행할 문장;
        실행할 문장;
        --초기화, SQL문, 함수호출, 절차적요소 ( 조건문, 반복문 )
    END;
    /
  • 실행방법)

    EXECUTE 프로시저명();
  • 문제)

    문제) 부서테이블 (dept3)에서 40번 부서를 삭제하시오 ( 프로시저 사용 )
    - pro_test.sql
    
    drop table dept3;
    
    create table dept3
    as select * from dept;
    ---
    create or replace procedure del_dept
    
    is
        -- 실행부에서 사용 할 변수 선언!
    begin
    
        delete from dept3
        where deptno = 40;
    
        dbms_output.put_line('#40번 부서를 삭제하였습니다.');
    
    end;
    /
    ---
    --하고 끝나는것이 아니라 프로시저를 실행해야 한다
    EXTCUTE del_dept();
    
    --이제 20번 부서 지우고 싶은데
    --그럼 또 재정의하고, 등록하고 삭제해야해?!...
    
    drop table dept3;
    
    create table dept3
    as select * from dept;
    
    --다시 해보자!
    create or replace procedure del_dept(delNo number)
    is
        -- 실행부에서 사용 할 변수 선언!
    begin
        delete from dept3
        where deptno = delNo;
        dbms_output.put_line('#40번 부서를 삭제하였습니다.');
    end;
    /
    --
    --이제 프로시저 등록 후 
    -- 실행할 때, 
    EXECUTE del_dept(원하는 deptno); --하면 
    --원하는 dept3 테이블의 deptno 컬럼이 지워진다.
    --=====================================================================
    
    -- 0603/pro_test.sql (40번 부서 삭제)
    -- 0603/pro_test.sql (특정 부서 삭제)
    
    --create or replace procedure del_dept
    -- 호출?  exec del_dept  또는   exec del_dept()
    --create or replace procedure del_dept(delNo number)
    -- 호출?  exec del_dept(40)
    create or replace procedure del_dept(delNo in number, tomorrow out varchar2)
    -- 변수 선언  variable tom  varchar2(15);
    -- 바인드 변수(전달 변수) 사용==>  :변수명
    -- 호출?  exec del_dept(40, :tom)
    -- 확인   print tom;
    is
      -- 실행부에서 사용할 변수 선언!!
    
    begin
        tomorrow := '토요일';
      /*    
       delete from dept3
       where deptno=40;
       dbms_output.put_line('#40번 부서를 삭제하였습니다!!');    
      */
       delete from dept3
       where deptno=delNo;
       dbms_output.put_line('#'||delNo||
                                 '번 부서를 삭제하였습니다!!');    
       commit;
    
    end;
    /
    
    show errors;
    
    variable tom varchar2(15);
    exec del_dept(40, :tom);
    
    --============================================================
    문제 ) select 문을 통해 tomorrow에 오늘의 요일의 아웃되도록
    
    create or replace procedure del_dept(delNo in number, tomorrow out varchar2)
    
    is
    
    begin
       delete from dept3
       where deptno=delNo;
       dbms_output.put_line('#'||delNo||
                                 '번 부서를 삭제하였습니다!!');    
       commit;
    
    --   select to_char(sysdate, 'yy/mm/dd') into -- 년월일 
       select to_char(sysdate, 'day') into tomorrow -- 년월일 
       from dual;
    end;
    /
    
    show errors;
    

    1559542009180

    문제) 사원테이블 (emp3)에서 특정 사원명을 (매개변수) 입력을 통해 삭제하는 프로시저를 작성하고 현재 사원수를 출력하시오
    
    drop table emp3;
    create table emp3
    as select empno, ename, sal
    from emp;
    
    --> pro_test2.sql
    --> 프로시저 명 : del_ename
    
    create or replace procedure del_ename(deleName in varchar2)
    
    is
        cnt number;
        total number;
    begin
        --사원이름 조회 유1 무0
        select count(ename) into cnt 
        from emp3
        where ename = delename;
    
        if cnt>0 -- 존재한다면
            then
            delete from emp3
            where ename = deleName;
    
            commit;
    
            select count(*) into total
            from emp3;
    
            dbms_output.put_line('#'|| deleName ||' 사원이 삭제되었습니다. 총 사원은  '|| total ||'입니다.');
    
        else
            select count(*) into total
            from emp3;
            dbms_output.put_line('#'|| deleName ||' 사원이 존재하지 않고. 총 사원은  '|| total ||'입니다.');
    
    
  end if; 

end;
/

show errors;

--============================================================
개선1) if then과 else select 문을 프로시저 내에 한번만 정의해서 출력
select count(*) into total
from emp3;

개선2)
del_ename('ADAMS')
del_enmae('adams') 상관없이 일치하는 사원 삭제

create or replace procedure del_ename(deleName in varchar2)
is
cnt number;
total number;
begin
--사원이름 조회 유1 무0
select count(ename) into cnt
from emp3
where upper(ename) = upper(delename);

  select count(*) into total
  from emp3;

  if cnt>0 -- 존재한다면
      then
      delete from emp3
      where lower(ename) = lower(deleName);

      commit;

      dbms_output.put_line('#'|| deleName ||' 사원이 삭제되었습니다. 총 사원은  '|| (total-1) ||'입니다.');

  else
      dbms_output.put_line('#'|| deleName ||' 사원이 존재하지 않고. 총 사원은  '|| total ||'입니다.');


  end if; 

end;
/

show errors;




------

<Stored Function'> 저장함수

- 실행(함수 호출) 후 결과를 되돌려 받을 수 있는 return 데이터; 를 반드시 명시해야 함!

- 형식

  ```SQL
  CREATE OR REPLACE FUNCTION 함수명( 매개변수 선언 )
  RETURN 자료형
  IS
      변수선언
  BEGIN
      (순차적으로) 실행할 SQL문
      ...
      RETURN 데이터;
  END;
  /
  • 사용법

    1. CREATE FUNCTION~ (함수를 DB에 저장) --함수 생성
    2. VARIABLE 바인드 변수명; --변수 선언 
    3. EXEC[UTE] : 바인드변수명 := 함수명() -- 함수호출, 결과값 저장
    4. PRINT 바인드변수명; --저장값 확인
  • 참고

          void hello(String  name){
             System.out.println("안녕,"+name);
          }       
               hello();
          -----> Stored Procedure (리턴키워드 없고)
    
          String hi( ){
             return "좋은 하루~!!";
          }      
              String msg =   hi();
          -----> Stored Function (리턴키워드 있고)
  • 문제

    문제)
    특정 사원에게 보너스를 지급하려고 하는데 급여의 200%에 해당하는 비용을 지급하려 한다. 이 보너스 금액을 FUNCTION을 통해 구하시오
    --> fun_test.sql
    --> 함수명 : call_bonus('SMITH');
    CREATE OR REPLACE FUNCTION call_bonus( vename emp.ename%type )
    return number
    IS
    
        sal emp.sal%type;
        bonus number;
    BEGIN
        SELECT sal INTO sal -- 컬렴명 sal, 변수명 vsal
        FROM emp
        WHERE ename = vename;
        --SELECT 문의 ';' 끝난 후 컬럼명은 사라지고 변수명만 남는다.
        --SELECT는 죽어서 변수명을 남긴다!
    
        --보너스 계산
        bonus := sal*2;
    
        return bonus; --프로시저와 다르게 펑션은 반드시 리턴값이 지정되어야 함.
    END;
    /
    
    

--=========================================================

문제) call_bonus2 Function을 정의하고 매개변수로 1.사원명과 보너스 비율을 넣어서 보너스 금액을 리턴하시오

exec :bonus := call_bonus('SMITH', 200)
print bonus











궁금상자

  • select into가 헷갈려
    원본은 있고 대상 테이블 새롭게 생성하려고 할때, 그럼 테이블 create 로 하면 되는데 왜 selet into로 하는거지?...
    부분적으로 원본테이블을 가져오려고 할때?

  • 그럼 select into 와 insert into는?
    select into는 타겟테이블이 실제로 존재하지 않아도 된다,
    반면 insert into는 타겟테이블이 존재해야 하고,


190531_DAY <뷰>, <시퀀스>, <PL/SQL>

<VIEW.> 뷰

  • 물리적인 테이블을 근거한 논리적인 가상테이블.
  • 가상 : 실질적으로 데이터를 저장하고 있지 않는다. (독립적인 테이블은 아니다.)
  • 기본테이블에 대한 하나의 쿼리문 (TEXT를 저장하는 '객체')
  • 자주 사용되는 복잡하고 긴 쿼리문을 저장하는 객체
  • 특정컬럼만 보여지게끔하여 보안을 목적으로 사용
  • 뷰는 테이블이 하는거 다 할 수 있다, 근데 뷰는 원래 목적은 보여주는거야, 그래서 옵션을 줘서 DML을 제한하고 조회만! (추가 삭제 수정 못하게 막아줌)
  1. 텍스트를 저장한다. SELECT
  2. 복잡한 쿼리문 간편하게 뷰이름으로만 호출 가능
  3. 제한적인 데이터를 조회할 수 있게 해준다.
  • 형식

    CREATE VIEW 뷰이름
    AS select_statement;
    
    CREATE VIEW emp_copy
    AS SELECT * FROM emp;
    -- ORA-01031: insufficient privileges ( 권한 불충분 )
    -- 권한 부족
    
    -- <권한 부여> - 관리자로 접속
    -- 형식)
        GRANT system_privilege
        TO user_name;
    
    -- <권한 회수> - 관리자
    -- 형식)
        REVOKE system_privilege
        FROM user_name;
    
    -- 관리자 계정 연결
    SQL> conn system/oracle
    -- Connected.
    SQL> show user
    -- USER is "SYSTEM"
    SQL> GRANT CREATE VIEW TO SCOTT; -- 뷰 객체 생성 권한
    -- Grant succeeded.
    
    --이후 다시 뷰 만들어보자!
    CREATE VIEW emp_copy
    AS SELECT * FROM emp;
    
    CREATE VIEW emp_copy
    AS SELECT * FROM emp;
    -- View created.
    
    desc emp_copy; -- 로 확인

<데이터 베이스 관리자가 가지는 시스템 권한>

  • CREATE USER - 새롭게 사용자를 생성하는 권한
  • DROP USER - 사용자를 삭제하는 권한
  • DROP ANY TABLE - 임의의 테이블을 삭제할 수 있는 권한
  • QUERY REWRITE - 질의 재작성을 할 수 있는 권한
  • BACKUP ANY TABLE - 임의의 테이블을 백업할 수 있는 권한

<데이터베이스 관리 권한>

  • CREATE SESSION - 데이터베이스에 접속할 수 있는 권한
  • CREATE TABLE - 사용자 스키마에서 테이블을 생성할 수 있는 권한.
  • CREATE VIEW - 사용자 스키마에서 뷰을 생성할 수 있는 권한.
  • CREATE SEQUENCE - 사용자 스키마에서 시퀀스을 생성할 수 있는 권한.
  • CREATE PROCEDURE - 사용자 스키마에서 함수을 생성할 수 있는 권한.
문제)
30번 부서 사원들의 정보(사원번호, 사원명, 급여, 부서번호)를 출력 (emp_copy뷰객체 통해서)

SELECT empno, ename, sal, deptno
FROM emp_copy
WHERE deptno=30;

     EMPNO ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
      7499 ALLEN                      1600         30
      7521 WARD                       1250         30
      7654 MARTIN                     1250         30
      7698 BLAKE                      2850         30
      7844 TURNER                     1500         30
      7900 JAMES                       950         30

--===========================================================================

문제)
emp테이블(원본, 물리적테이블)에 홍길동 사원을 추가히시오!
INSERT INTO emp (empno, ename, sal, deptno)
            VALUES (8000, '홍길동', 3000, 30);

SELECT empno, ename, sal, deptno
FROM emp_copy
WHERE deptno=30;
-- emp_copy조회했는데

     EMPNO ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
      8000 홍길동                     3000         30
      7499 ALLEN                      1600         30
      7521 WARD                       1250         30
      7654 MARTIN                     1250         30
      7698 BLAKE                      2850         30
      7844 TURNER                     1500         30
      7900 JAMES                       950         30
-- 홍길동 생성 !, emp 수정해도 emp_copy(뷰) 에도 바로 반영된다.
-- 만약 CREATE TABLE emp2 AS SELECT * FROM emp; 로 생성한 후 추가했다면 emp2에는 반영 안되었을 텐데...
-- 뷰는 기준테이블(emp)을 참조하는(바라보는) 논리적 테이블!, 바라만 보기 때문에 실시간으로 같이 수정되는구나!

--DBMS에서는 user들이 만드는 객체에 대해 '시스템테이블'을 만들어 관리!!

        테이블생성      -----> tab
        제약 추가(생성)   -----> user_constraints
        뷰생성         -----> user_views   

desc user_views                                                

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)
 EDITIONING_VIEW                                    VARCHAR2(1)
 READ_ONLY                                          VARCHAR2(1)  

select view_name, text from user_views;   

VIEW_NAME    TEXT
------------------------------------------------------------
EMP_COPY    select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp  

--===========================================================================

문제) 30번 부서에 근무하는 사원의 사원번호, 사원명, 급여를 자주 조회한다.
         이를 뷰로 생성하시오.

     create view emp_copy
     as select empno, ename, sal
        from   emp
        where  deptno=30;         
   ---> ORA-00955: name is already used by an existing object
           ---> 테이블과 마찬가지로 뷰객체도 이미 존재하는 이름을 사용할 수 없음!!

     create view emp30
     as select empno, ename, sal
        from   emp
        where  deptno=30;         
    -- ==> 뷰생성 성공!!

     select * from emp30;

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7499 ALLEN                      1600
      7521 WARD                       1250
      7654 MARTIN                     1250
      7698 BLAKE                      2850
      7844 TURNER                     1500
      7900 JAMES                       950
      8000 홍길동                                         3000         


<VIEW형식>

   CREATE  [OR REPLACE]  [FORCE|NOFORCE]  VIEW 뷰이름  [(alias)]
   AS select_statement
   [WITH CHECK OPTION [CONSTRAINT 제약명]]
   [WITH READ ONLY [CONSTRAINT 제약명]];

   -- 뷰 emp30 에 입사일, 부서번호를 추가!
CREATE VIEW emp30
AS SELECT empno, ename, sal, hiredate, deptno
FROM emp
WHERE deptno = 30;
-- ORA-00955: name is already used by an existing object
-- 이미 위에서 만듬

CREATE OR REPLACE VIEW emp30
--존재하지 않는 뷰는 새로 생성하고 존재하는 뷰의 경우에는 TEXT내용 교체
AS SELECT empno, ename, sal, hiredate, deptno
FROM emp
WHERE deptno = 30;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 SAL                                                NUMBER(7,2)
 HIREDATE                                           DATE
 DEPTNO                                             NUMBER(2)

 --===========================================================================

 문제)
 abc 테이블 ( 존재하지 않는 테이블 )의 모든 컬럼을 조회하는 뷰를 생성하시오 ( 뷰명 : abc_view )
 CREATE OR REPLACE VIEW abc_view
 AS SELECT * FROM abc;
 -- ORA-00942: table or view does not exist
 -- 엥? 위에서 존재하지 않는 뷰는 새로 생성하는게 아닌가?...

 CREATE OR REPLACE FORCE VIEW abc_view 
 AS SELECT * FROM abc;
 -- Warning: View created with compilation errors.
 -- 생성 하긴 했는데 에러 생길 수 있어~! 경고해줄게, 텍스트만 저장할게

  --===========================================================================

 문제) EMP30에서 mgr을 조회하시오
 SELECT empno, ename FROM emp30; -- 이건 잘 되는데

select empno, ename, mgr from emp30; -- 얘는 안되네
 --ORA-00904: "MGR": invalid identifier EMP30에 정의되지 않은 애는 조회 불가

 DELETE FROM emp30 WHERE empno = 7900;


 SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        14

SQL> SELECT COUNT(*) FROM emp30;

  COUNT(*)
----------
         6

SQL> ROLLBACK;
--Rollback complete.

SQL> DROP VIEW emp_copy;
--View dropped.

CREATE TABLE emp_copy 
AS SELECT * FROM emp;

--뷰생성--
CREATE OR REPLACE VIEW emp30
AS SELECT empno, ename, sal, deptno
FROM emp_copy
WHERE deptno = 30;

SELECT * FROM emp30;

<뷰를 통한 입력>
INSERT INTO emp30 VALUES (8000, '김용진', 3000, 30);
INSERT INTO emp30 VALUES (6000, '이진주', 3000, 30);

SQL> SELECT * FROM emp30;
    SELECT * FROM emp_copy; -- 뷰가 아닌 테이블에도 잘 들어옴

     EMPNO ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
      7499 ALLEN                      1600         30
      7521 WARD                       1250         30
      7654 MARTIN                     1250         30
      7698 BLAKE                      2850         30
      7844 TURNER                     1500         30
      7900 JAMES                       950         30
      8000 김용진                     3000         30
      6000 이진주                     3000         30

<뷰를 통한 삭제>
    delete from emp30;   -- 뷰emp30을 통한 테이블emp_copy삭제, 8행 삭제됨
    select * from emp30; -- 없대

SQL> select empno,ename from emp_copy;
     EMPNO ENAME
---------- --------------------
      7369 SMITH
      7566 JONES
      7782 CLARK
      7788 SCOTT
      7839 KING
      7876 ADAMS
      7902 FORD
      7934 MILLER
      8002 길라임

   ※ 20번 부서의 정보를 조회하는 뷰생성!!
   CREATE VIEW emp20
   AS SELECT empno, ename, sal, deptno
   FROM emp_copy
   WHERE deptno = 20
   WITH READ ONLY;
   -- 읽기 전용, DML 불가, 뷰생성
           <뷰를 통한 입력>
           INSERT INTO emp20 values (9000, '김주원', 900, 20);
           -- ORA-42399: cannot perform a DML operation on a read-only view

           <뷰를 통한 삭제>
           DELETE FROM emp20;
           -- ORA-42399: cannot perform a DML operation on a read-only view

           <뷰를 통한 수정>
           UPDATE emp20 SET sal = 9999;
           -- ORA-42399: cannot perform a DML operation on a read-only view
           CREATE[OR REPLACE] [FORCE | NOFORCE] VIEW 뷰 이름 [(alias)]
           AS select_statement
           [WITH CHECK OPTION [ CONSTRAINT 제약명]]
           [WITH READ ONLY [CONSTRAINT 제약명]];

           <뷰에 별명주기>
           CREATE OR REPLACE VIEW emp20 (나별명)
           AS SELECT empno, ename, sal, deptno
           FROM emp_copy
           WHERE deptno = 20;
           --ORA-01730: invalid number of column names specified
           --뷰이름 다음에 명시되는 alias는 컬럼에 대한 별명임!! 
           -- 컬럼 갯수만큼 alias 줘야 한다.

           CREATE OR REPLACE VIEW emp20 (사원번호, 사원명, 급여, 부서번호)
           AS SELECT empno, ename, sal, deptno
           FROM emp_copy
           WHERE deptno = 20;
           --뷰 생성 성공
           SELECT * FROM emp20;
             사원번호 사원명                     급여   부서번호
---------- -------------------- ---------- ----------
      7369 SMITH                       800         20
      7566 JONES                      2975         20
      7788 SCOTT                      3000         20
      7876 ADAMS                      1100         20
      7902 FORD                       3000         20

 --===========================================================================

문제) emp20뷰에서 사원번호와 사원명만 조회하시오!!
    select empno,ename
    from emp20;     
   ==> ORA-00904: "ENAME": invalid identifier
         --->  뷰생성시 별명을 정의했다면 조회시에는 별명만 사용해야 함!!

    select 사원번호, 사원명
    from emp20;

        사원번호 사원명
---------- --------------------
      7369 SMITH
      7566 JONES
      7788 SCOTT
      7876 ADAMS
      7902 FORD
      8002 길라임                  

 --===========================================================================

문제) 'ADAMS'사원을 10번 부서로 이동하시오.(emp20뷰를 통해)
UPDATE emp20 SET 부서번호 = 10
WHERE 사원번호 = 7876;

SQL> SELECT * FROM emp20;

  사원번호 사원명                     급여   부서번호
---------- -------------------- ---------- ----------
      7369 SMITH                       800         20
      7566 JONES                      2975         20
      7788 SCOTT                      3000         20
      7902 FORD                       3000         20

 --===========================================================================

문제) '김유신'사원을 30번 부서에 추가하시오.(emp20뷰를 통해)
insert into emp20  values (8004,'김유신',2000,30);
select * from emp20;

 --===========================================================================

    CREATE  [OR REPLACE]  [FORCE|NOFORCE]  VIEW 뷰이름  [(alias)]
   AS select_statement
   [WITH CHECK OPTION [CONSTRAINT 제약명]]
   [WITH READ ONLY [CONSTRAINT 제약명]];     

       CREATE OR REPLACE VIEW emp20 (사원번호, 사원명, 급여, 부서번호)
       AS SELECT empno, ename, sal, deptno
       FROM emp_copy
       WHERE deptno = 20 WITH CHECK OPTION; -- CHECK OPTION : DEPT20에 위반되는 추가, 수정을 제한

문제) 
'FORD'사원을 10번 부서로 이동하시오(emp20뷰를 통해)
UPDATE emp20 SET 부서번호 = 10
WHERE 사원번호 = 7369;
--=> ORA-01402: view WITH CHECK OPTION where-clause violation

문제) '손오공'사원을 30번 부서에 추가하시오.(emp20뷰를 통해)
insert into emp20 values (8006,'손오공',2000,30);
--==> ORA-01402: view WITH CHECK OPTION where-clause violation

insert into emp20 values (8006,'손오공',2000,20);
--=> 입력성공!!

<인라인 뷰>

  • 서브쿼리문에서 바깥쪽 SELECT문(메인쿼리)의 FROM절에 사용된 서브쿼리문

  • 내부에 사용되는 서브쿼리는 별칭이 부여, 이렇게 부여된 별칭이 뷰처럼 사용

    SELECT ...., -- 메인쿼리
                (SELECT ...) -- 서브쿼리
    FROM ....(SELECT ... -- 서브쿼리, 인라인뷰!
             ) Alias
    
    WHERE (SELECT ...) -- 서브쿼리
    ...;

<Top-N 구하기>

  • ROWID : 주소로서 ROW가 실제로 저장되어 있는 공간을 표시

  • ROWNUM : 번호로서 ROW의 주소 순서대로 출력되어짐.

    ​ 출력되는 행에 대해 첫행부터 순차적인 번호를 부여 ( 1,2,3, ...... )

    SELECT empno, ename, sal, rownum
    FROM emp;
    
         EMPNO ENAME                       SAL     ROWNUM
    ---------- -------------------- ---------- ----------
          7369 SMITH                       800          1
          7499 ALLEN                      1600          2
          7521 WARD                       1250          3
          7566 JONES                      2975          4
          7654 MARTIN                     1250          5
          7698 BLAKE                      2850          6
          7782 CLARK                      2450          7
          7788 SCOTT                      3000          8
          7839 KING                       5000          9
          7844 TURNER                     1500         10
          7876 ADAMS                      1100         11
          7900 JAMES                       950         12
          7902 FORD                       3000         13
          7934 MILLER                     1300         14

    문제 ) 위의 출력된 사원 중 세명만 (ROWNUM : 1~3 출력)
    SELECT empno, ename, sal
    FROM emp
    WHERE rownum BETWEEN 1 AND 3;
    
         EMPNO ENAME                       SAL
    ---------- -------------------- ----------
          7369 SMITH                       800
          7499 ALLEN                      1600
          7521 WARD                       1250
    
     --===========================================================================
    
    문제 ) 가장 급여를 많이 받는 3명의 사원 정보(사원번호, 사원명, 급여)를 ROWNUM을 사용해서 출력하시오.
    
    SELECT empno, ename, sal
    FROM emp
    WHERE rownum BETWEEN 1 AND 3
    ORDER BY sal DESC; -- 실행순서때문에 원하는 값 안나옴, 순서를 바꿔주어야 한다.
    
    SELECT empno, ename, sal        --실행4
    FROM  (SELECT empno, ename, sal --실행2
           FROM EMP                 --실행1
           ORDER BY sal desc)            --실행3
    WHERE rownum BETWEEN 1 AND 3;      --실행5
         EMPNO ENAME                       SAL
    ---------- -------------------- ----------
          7839 KING                       5000
          7902 FORD                       3000
          7788 SCOTT                      3000
    
     --===========================================================================
    
    문제) 가장 오래된(오래 근무한) 사원 3명의 사원번호 사원명, 입사일을 출력하시오
    SELECT empno, ename, hiredate
    FROM (SELECT empno, ename, hiredate
             FROM EMP
             ORDER BY HIREDATE asc)
    WHERE rownum BETWEEN 1 AND 3 ;
         EMPNO ENAME                HIREDATE
    ---------- -------------------- --------
          7369 SMITH                80/12/17
          7499 ALLEN                81/02/20
          7521 WARD                 81/02/22
    
     --===========================================================================
    
    문제) 급여를 4번째로 많이 받는 사원부터 6번째까지의 사원의 정보(사원번호, 사원명, 급여)를 출력하시오
    
    SELECT empno, ename, sal
    FROM (SELECT empno, ename, sal
          FROM emp
          ORDER BY sal DESC
         )
    WHERE rownum BETWEEN 4 AND 6;
    --결과 없음, 이유 : rownum은 항상 1부터 1씩 증가하는 값을 카운트 해야 함!
    
    SELECT empno, ename, sal
    FROM (SELECT empno, ename, sal, rownum rb-- rownum 이 두개가 돌아다니니 별명 지어줘야 함
          FROM (SELECT empno, ename, sal
                  FROM emp
                  ORDER BY sal DESC
                )
         )
    WHERE rb BETWEEN 4 AND 6;
    

<SEQUENCE시퀀스> : 자동번호 부여

  • 숫자값이 일정한 규칙에 의해서 연속적으로 자동증가.

  • 테이블에 종속적이지 않다(테이블과 독립적)
    형식)

    CREATE SEQUENCE sequence_name  
                   [INCREMENT BY] 증가치
                   [START WITH] 초기값
                   [MAXVALUE n|NOMAXVALUE]
                   [MINVALUE|NOMINVALUE]
                   [CYCLE|NOCYCLE]
                   [CACHE|NOCACHE];

    maxvalue: 시퀀스가 가질 수 있는 최대값 지정.

    ​ nomaxvalue일 경우 ascending순서일 경우 10의 27승
    ​ descending일 경우 -1

    ​ minvalue: 시퀀스가 가질 수 있는 최대값 지정.
    ​ nominvalue일 경우 ascending 1
    ​ descending 10의 26승으로 설정.
    cache: 메모리상의 시퀀스 값을 관리하도록 하는 것인데 기본값은 20.
    ​ nocache는 메모리 상에서 시퀀스를 관리하지 않음.

    • 사용법)

      시퀀스명.nextval -- 발생된 번호값( 그다음 )
      시퀀스명.currval -- 현재 번호값
      
      CREATE SEQUENCE test_seq; -- 기본 1부터 시작해서 1씩 증가되는 숫자값
      --시퀀스 생성
      
      DROP SEQUENCE test_seq;
      
      CREATE SEQUENCE test_seq
                      START WITH 1
                      INCREMENT BY 1
                      NOCYCLE
                      NOCACHE;
                      --시퀀스 생성
      현재 숫자 보기 (조회)
      SELECT test_seq.currval FROM dual;
      -- ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session
      
      자동 번호(숫자) 얻어오기
      SELECT test_seq.nextval FROM dual;
      SELECT test_seq.nextval FROM dual;
      SELECT test_seq.nextval FROM dual;
      SELECT test_seq.nextval FROM dual;
      SELECT test_seq.nextval FROM dual; -- 5번!
      
      현재 숫자 보기 (조회)
      SELECT test_seq.currval FROM dual;
         CURRVAL
      ----------
               5
      
      테이블에 적용한 사례)
      alter table dept3
      drop primary key cascade;
      
      DROP TABLE dept3;
      
      CREATE TABLE dept3
      AS SELECT * FROM dept
      WHERE 1 = 0;
      
      <dept2 테이블 deptno 에 PRIMARY KEY설정>
      alter table dept3
      add constraint dept3_pk primary key (deptno);
      
      <행 데이터 입력>
         insert into dept3 values (10, '영업부', '서초');
         insert into dept3 values (20, '자재부', '대전');
         insert into dept3 values (30, '개발부', '남터');
      
         select * from dept3;
      
      ---------------------------------------------------
      -- 오랜 시간이 흘러 새로운 부서에 정보를 입력할 때
      데이터 ==> '기획부'        '강남'
      INSERT INTO dept3 (dname, loc) VALUES ( '기획부', '강남'); -- 에러
      
      INSERT INTO dept3 (dname, loc) VALUES ( '기획부', '강남'. '잠실'); --에러
      
      --입력 성공
      SELECT MAX(deptno) FROM dept;
      
      select deptno from dept3;
      
      insert into dept3 (deptno, dname, loc) values (40, '기획부', '강남');
      ---------------------시퀀스 객체를 사용했다면
      
       --===========================================================================
      
      문제) dept3부서테이블의 전체 데이터를 삭제하시오!!
          DELETE FROM dept3;
          TRUNCATE TABLE dept3;
      
       --===========================================================================
      
      문제) dept3 테이블 내의 deptno에 적용할 시퀀스 객체를 생성하시오
      -- (10부터 시작해서 10씩 증가)
      CREATE SEQUENCE dept3_seq
                      start with 10
                      increment by 10
                      nocycle
                      nocache;
      
      ※시퀀스 적용된 부서번호 생성!!
      <행 데이터 입력>
      INSERT INTO dept3 VALUES(dept3_seq.nextval , '영업부', '서초');
      INSERT INTO dept3 VALUES(dept3_seq.nextval , '자재부', '대전');
      INSERT INTO dept3 VALUES(dept3_seq.nextval , '개발부', '남터');
      
      SQL> select * from dept3;
      
          DEPTNO DNAME                        LOC
      ---------- ---------------------------- --------------------------
              10 영업부                       서초
              20 자재부                       대전
              30 개발부                       남터
              40 기획부                       강남
      
       --===========================================================================
       * Oracle 11g에서 시퀀스 사용시 주의할 점!!
        deferred_segment_creation=true(기본값) ===> false로 변경
      
        alter system set deferred_segment_creation=false;    
        ORA-01031: insufficient privileges ==> 권한불충분
      
      SQL> conn system/oracle      
      Connected.
      
      SQL> alter system set deferred_segment_creation=false;
      System altered.
      
      SQL> conn scott/tiger
      Connected.          

<PL/SQL>

  • SQL의 장점은 쿼리문 하나로 원하는 데이터를 검색 조작할 수 있다는 점인데, SQL문 자체는 비절차적 언어이기 때문에 몇개의 쿼리문 사이에 어떠한 연결 및 절차성이 있어야 하는 경우는 사용할 수 없다.

        insert into ~ values ~;
  • 이 점을 극복하기 위해서 SQL 언어에 절차적인 프로그래밍 언어를 가미해 만든 것이 PL/SQL

    if( select 결과가 내가 원하는 값이라면 )
    {
        insert into ~ values ~;
    }

<PL/SQL구조> - 눈사람을 생각해줘~!

  • DECLARE SECTION (선언부)

    • PL/SQL에서 사용하는 모든 변수나 상수를 선언
  • EXECUTABLE SECTION (실행부)

    • 절차적형식으로 SQL을 실행할 수 있도록 절차적요소인 제어문, 반복문
    • 함수정의 등 로직기술
  • EXCEPTION SECTION (예외처리)

    • 실행 중 발생되는 에러를 해결하기 위한 문장으로 구성

<변수선언>

변수명 자료형;

vempno number(4);

vempno emp.empno%type; --기존 존재하는 emp테이블의 empno컬럼이 갖는 자료형을 참조하겠다.

<레퍼런스 변수>

  • %TYPE (한 개의 칼럼), %ROWTYPE (한 행을 구성하는 모든 컬럼을 참조)
  • vemp emp&ROWTYPE;-- emp테이블이 갖는 모든 컬럼의 자료형을 vemp 변수로 참조하겠음.
형식)
declare -- 선언부
        -- (실행부에 필요한) 변수선언
begin  -- 실행부 {
        -- SQL문
end;/ -- } 끝난다~ /는 run의 의미임

-------------------------------------------------------------------------------------------
문제) 화면 콘솔에 '안녕, PL/SQL~!' 을 출력하시오.
     declare 
     begin 
        dbms_output.put_line('안녕, PL/SQL~!!');
     end; 
     /

문제)
7788사번을 갖는 사원의 사원번호와 사원명, 급여를 화면에 출력

DECLARE

BEGIN
    SELECT empno, ename, sal
    FROM emp
    WHERE empno = 7788;
    dbms_output.put_line(ename)
END;
/
-- 실행 안되네...
-------------------------------------------------------------------------------
DECLARE
    -- 실행부에서 사용할 변수 선언!
    -- 형식) 변수명 자료형;
    vempno number(4); --SELECT문에서 호출한 애들은 변수 선언 필수!
    vename varchar2(15);
    vsal emp.sal%TYPE; --기억 안나...emp테이블에서 가져올래!
BEGIN
    SELECT empno, ename, sal 
    -- 얘네들은 변수가 아니라 칼럼이야!
    INTO vempno, vename, vsal 
    --v 안뭍여도 된다~! 근데 식별때문에 씀
    -- into는 PL/SQL에서만 쓴다.
    FROM emp
    WHERE empno = 7788;

    --조회문(select) 사용시 into절을 사용해야 하는 이유?
    --select문을 실행하고 난 후 begin~end에서 조회한 데이터를 유지하려면
    --변수라는 곳에 담아야 하기 때문에
    --select 컬럼명 into 변수명!!
    dbms_output.put_line( '사원번호 : ' || vempno ); --조회된 칼럼명 인식X, 데이터를 담은 변수명 인식O
    dbms_output.put_line( '사원명 : ' || vename || '  급여 : ' || vsal);

END;
/

glogin

  • 오라클 서버 실행 되기 전에 항상 읽는 문서 여기에 미리 set해두면 항상 옵션이 설정된다.

<PL/SQL 형식 다시 정리>

DECLARE --선언부
--실행부에서 사용할 변수 선언

BEGIN --실행부 시작
    -- SQL문(DQL, DML)!!

    SELECT empno, ename, sal INTO 변수명1, 변수명2, 변수명3
    FROM emp;

    -- 이 영역에서 empno, ename, sal의 대한 정보가 사라짐!!
    -- 조회된 결과 데이터를 저장할 변수 정의가 필요하다.
    -- INSERT, DELETE문, UPDATE문

    --조건문 또는 반복문과 함께 하는 SQL문 작성!!

    --함수 호출
END; --실행부 끝
/

<조건문 IF>

  • 형식)

    <조건문 if>
      형식)
           if  ( 조건식   )  -- ()소괄호 생략 가능
              then -- { 시작을 표현
    
                     --조건식을 만족했을 때 실행할 문장;
    
    
     end if;  -- } 끝을 표현

    ----> if_test.sql
    ----> 두 수를 비교해서 크다, 작다를 출력!!






궁금상자

  • glogin에 좋은 옵션?! SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
    SET TIME ON
    SET TIMING ON
    SET TERMOUT ON
    SET SERVEROUTPUT ON
    SET FEED ON
    SET LINES 120
    SET PAGES 40
    SET ECHO ON

190530_DAY36 트랜잭션, 무결성제약조건, 데이터 딕셔너리

<Transaction.> 트랜잭션

  • 데이터 처리의 한 단위

  • 오라클에서 발생하는 여러 개의 SQL 명령문 (DML) 들을 하나의 논리적인 작업단위로 처리

  • 하나의 트랜잭션은 All-or-Nothing 방식으로 처리

  • 목적 : 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구

  • 명령어 : COMMIT ROLLBACK SAVEPOINT

    sqlplus scott/tiger
    ----------------------
    SELECT 명령문;
    INSERT 명령문;
    SELECT 명령문;
    UPDATE 명령문
    ...
    ...
    COMMIT;
    ----------------------
     -- COMMIT 할때 트랜잭션에 있던 INSERT UPDATE 등이 물리적으로 DB에 반영된다.
     -- COMMIT 이후 트랜잭션이 끝나고 다른 트랜잭션이 실행된다.
  • 명령어 하나로도 물리적 반영 되지 않나? 왜 트랜잭션을 사용하지 ? INSERT UPDATE간에 연관성이 있을때!

  • CREATE DROP TRUNCATE 같은 DDL 사용하면 Auto COMMIT 됨, 자동 DB 반영

  • 그냥 INSERT SELECT 는 작업자에게는 보이지만, 외부에서 접근해서 DB조회할때에는 보이지 않는다.

  1. COMMIT
    • 저장되지 않은 모든 데이터를 데이터베이스에 저장하고 현재의 트랜잭션을 종료하라는 명령어
    • 커밋하면 트랜잭션의 처리과정이 모두 반영되며 하나의 트랜잭션 과정이 끝남
    • 트랜잭션이 발생하는 경우 새롭게 생성되거나 갱신된 데이터들이 물리적으로 영구히 저장됨
    • DDL( create, drop, alter, rename, truncate) 은 AutoCommit!
    • 정상적인 종료시에도 Commit작업을 수행
    • 예) exit종료
  2. ROLLBACK (TO)
    • 저장되지 않은 모든 데이터 변경사항(DML)을 취소하고 현재의 트랜잭션을 끝내라는 명령
    • 트랜젝션으로 인한 하나의 묶음처리가 시작되기 이전의 상태로 되돌려지는 것
    • 이전상태로 되돌아가 지금까지 수행했던 데이터베이스의 변경을 모두 무효화
    • 비정상적인 종료시 ROLLBACK 작업을 수행
    • 예) 우측 상단 X버튼 클릭시
문제) 20번 부서와 40번 부서를 삭제하시오 (DML)
DELETE FROM dept3
WHERE deptno IN (20,40);

SELECT * FROM dept3;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        30 SALES                        CHICAGO

--===========================================================================

문제) 30번 부서의 이름을 '영업부'로 변경하시오 (DML)
UPDATE dept3
SET dname = '영업부'
WHERE deptno = 30;

SELECT * FROM dept3;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        30 영업부                       CHICAGO
하고 ROLLBACK;     

SELECT * FROM dept3;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS
--===========================================================================

문제) 10, 20, 30 번 부서를 삭제하시오
DELETE FROM dept3
WHERE deptno IN (10,20,30);

SELECT * FROM dept3;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        40 OPERATIONS                   BOSTON

COMMIT;
ROLLBACK;
SELECT * FROM dept3; -- 이미 늦어버렸따, 이미 커밋해버렸어...

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        40 OPERATIONS                   BOSTON

<SAVEPOINT.> : 저장점

  • 형식) SAVEPOINT 저장점명;

    DROP TABLE dept4;
    
    CREATE TABLE dept4
    AS SELECT * FROM dept;
    
    SELECT * FROM dept4;
    
        DEPTNO DNAME                        LOC
    ---------- ---------------------------- --------------------------
            10 ACCOUNTING                   NEW YORK
            20 RESEARCH                     DALLAS
            30 SALES                        CHICAGO
            40 OPERATIONS                   BOSTON
    
    SAVEPOINT sp1;
    
    -- 20번 부서 삭제
    DELETE FROM dept4
    WHERE deptno = 20;
    
    SELECT * FROM dept4;
    
        DEPTNO DNAME                        LOC
    ---------- ---------------------------- --------------------------
            10 ACCOUNTING                   NEW YORK
            30 SALES                        CHICAGO
            40 OPERATIONS                   BOSTON
    SAVEPOINT sp2;
    
    DELETE FROM dept4
    WHERE deptno = 10;
    
    DELETE FROM dept4
    WHERE deptno = 30;
    
    SAVEPOINT sp3;
    -- 지...지워졌다! 근데 잘못지웠거나, 다시 돌아가고 싶다면
    ROLLBACK to sp1;
    SELECT * FROM dept4;
        DEPTNO DNAME                        LOC
    ---------- ---------------------------- --------------------------
            10 ACCOUNTING                   NEW YORK
            20 RESEARCH                     DALLAS
            30 SALES                        CHICAGO
            40 OPERATIONS                   BOSTON
    -- 돌아왔네!
    ROLLBACK to sp2;
    SELECT * FROM dept4;
    -- 에러 발생...! 순차적으로 가야 한다 여기서는 sp3 - sp2 - sp1 순으로는 롤백이 가능하지만
    -- sp1 했다가 sp2 sp3 하게 된다면 에러가 발생한다.
    ORA-01086: savepoint 'SP2' never established in this session or is invalid

<무결성 제약조건> Data Integrity Constraint Rule

  • 테이블에 부적절한 자료가 입력(INSERT, UPDATE) 되는 것을 방지하기 위해서 테이블 생성할 때 각 컬럼에 대해서 정의하는 여러가지 규칙.
  • (테이블 생성 후 컬럼에 규칙하는 (CONSTRAINT 적용) 하는 것도 가능)
  1. NOT NULL

    • 해당 컬럼값으로 NULL을 허용하지 않음
    • (행단위)입력시 데이터를 무조건 받겠다!!
  2. UNIQUE

    • 테이블 내에서 해당 컬럼값은 항상 유일무이한 값을 가질 것
    • 중복을 허용하지 않겠다!
  3. PRIMARY KEY

    • 해당 컬럼값은 반드시 존재해야 하고 유일해야 한다는 조건
    • 테이블 내에서 서로 다른 행을 구분하기 위해서 사용
  4. FOREIGN KEY

    • 해당 컬럼의 값이 타컬럼의 값을 참조해야만 함

    • 즉, 참조되는 컬럼에 존재하지 않는 값은 입력 불가

    • - 참조하는 사원테이블에서 설정( 예 : 사원테이블(자식)의 deptno에 참조키 설정!)
      - 참조되는 쪽 부서테이블 (부모테이블)
      
      EMP테이블 (deptno) -----------------> DEPT테이블(deptno) 10, 20, 30, 40
      
      사원 INSERT 시 (empno, ename, deptno)
          VALUES (8000, '김길동', 30) => 존재하는 부서30에 저장 성공!
          VALUES (8002, '박길동', 50) => 존재하지 않는 부서50에 저장 실패
      
      SCOTT 사원의 사번 = 7788
      UPDATE emp SET deptno = 70 WHERE empno = 7788; --존재하지 않는 부서 70으로 인해 에러 발생
  1. CHECK

    • 해당 컬럼에 저장 가능한 데이터 값의 범위나 사용자 조건을 지정
    • age컬럼 : ( 1~100 )
    • gender컬럼 : (남성, 여성)

<제약조건 설정하기 1 > - 컬럼레벨 정의 방법

  • 컬럼레벨 : 제약조건의 명시를 테이블 생성시 컬럼정의와 함께!! (같은 줄에 제약 정의)

  • 형식

    CREATE TABLE 테이블명
    (
        컬럼명 자료형 CONSTRAINT_TYPE
    );
    
    DROP TABLE dept2;
    
    CREATE TABLE dept2
    (
        deptno number(2),
        dname varchar2(15),
        location varchar2(15)
    );
    
    INSERT into dept2 VALUES ( 10, '영업부', '서울');
    INSERT into dept2 VALUES ( 10, '자재부', '서울');
    INSERT into dept2 VALUES ( NULL, '총무부', '서울');
    INSERT into dept2 VALUES ( 10, '영업부', '서울');
    
    select * from dept2;
    
        DEPTNO DNAME                          LOCATION
    ---------- ------------------------------ ------------------------------
            10 영업부                         서울
            10 자재부                         서울
               총무부                         서울
            10 영업부                         서울

<제약조건 설정하기1_2> - 컬럼레벨(컬럼선언시) 정의 방법, 이름지정!!

  • 형식)

    DROP TABLE dept2;
    
    CREATE TABLE dept2
    (
        deptno NUMBER(2) CONSTRAINT dept2_deptno_pk PRIMARY KEY,
        dname VARCHAR2(15),
        location VARCHAR2(15)
    );
    
    INSERT INTO dept2 VALUES ( 10, '영업부', '서울');
    
    INSERT INTO dept2 VALUES ( 10, '자재부', '서울');
    ORA-00001: unique constraint (SCOTT.DEPT2_DEPTNO_PK) violated
    --얘는 에러 발생 제약명을 지정하지 않은 경우 에러!

제약종류 세부

​ 1. NOT NULL || 2.UNIQUE || 3. PRIMARY KEY || 4. FOREIGN KEY(외래키, 참조키)

  1. NOT NULL

    • 데이터를 반드시 입력!

    • 수정시 NULL로 변환 불가!

      DROP TABLE emp2;
      
      CREATE TABLE emp2
      (
          empno NUMBER(4) CONSTRAINT emp2_empno_nn NOT NULL, --  NOT NULL의 제약명은 보통 생략
          ename VARCHAR2(15),
          sal NUMBER(7,2),
          job VARCHAR2(15)
      );
      
      -- 위에서 실습했던 첫번째 행과 두번째 행 완전 동일시 지우기 힘듬, (lowid...같은거 사용해야함) 이런 상황 미연에 방지하기 위해 제약을 둬야 한다. CONSTRAINT 나 NOT NULL의 제약명은 생략
      
      INSERT INTO emp2 VALUES (7000, '홍길동', 2000, '사원');
      INSERT INTO emp2 (empno, ename, sal, job) VALUES (7002,'길라임', 2000, '사원' );
      INSERT INTO emp2 (empno, ename, sal, job) VALUES (7002,'길라임', 2000, '사원' );
      
      SELECT * FROM emp2;
      
      --NOT NULL 은 유일한 값을 보장하지 못함.
      --유일하지 않고 반드시 입력 또는 수정해야 할 컬럼에 설정을 해줘야 한다.
  2. UNIQUE

    • 중복된 데이터를 방지

<제약조건 설정하기2> - 데이블 레벨 (전체 컬럼선언이 끝나고 난 후) 정의 방법

DROP TABLE emp2;
CREATE TABLE emp2
(
    empno NUMBER(4),
    ename VARCHAR2(15),
    sal NUMBER(7,2),
    job VARCHAR2(15),
    CONSTRAINT emp2_empno_uk UNIQUE (empno)
);

INSERT INTO emp2 VALUES (7000, '홍길동', 2000, '사원');
INSERT INTO emp2 VALUES (7002, '홍길동', 2000, '사원');
INSERT INTO emp2 VALUES (7002, '홍길동', 2000, '사원'); -- 위와 같은 데이터값
ORA-00001: unique constraint (SCOTT.EMP2_EMPNO_UK) violated -- 에러,UNIQUE 가 중복 방지

INSERT INTO emp2 VALUES (7004, '홍길동', 2000, '사원');
INSERT INTO emp2 VALUES (NULL, '김주원', 2000, '사원'); -- 입력 성공
INSERT INTO emp2 VALUES (NULL, '김주원', 2000, '사원'); -- 위와 같은 데이터 값, 입력 성공됨!...
--UNIQUE제약은 중복 데이터를 방지해준다, 하지만 NULL의 중복은 방지하지 못함!! 

  1. PRIMARY KEY( 기본키, 주키 )

    • not null AND unique를 동시에 만족하는 데이터 입력.

    • 테이블내의 서로 다른 행을 구분하는 목적으로 사용.

    • 한 테이블내에 단 한 개의 primary key만 존재!!

      DROP TABLE emp2;
      
      CREATE TABLE emp2
        (
           empno number(4) primary key,
           ename varchar2(15) primary key,
           sal   number(7,2),
           job   varchar2(15)
        );   
       ==> ORA-02260: table can have only one primary key

<제약조건 설정하기3> - 제약추가 : 이미 테이블 객체가 만들어진 상태에서 제약 추가!!

DROP TABLE EMP2;

  create table emp2
  (
     empno number(4),
     ename varchar2(15),
     sal   number(7,2),
     job   varchar2(15)
  );   

ALTER TABLE emp2
ADD CONSTRAINT emp2_pk PRIMARY KEY (EMPNO); -- CONSTRAINT 는 생략 가능, 시스템에서임의로 잡아줌

INSERT INTO EMP2 VALUES (8000, '홍길동', 2000, '사원'); 

INSERT INTO EMP2 VALUES (8000, '홍길동', 2000, '사원'); -- 에러나야 하는데... 남!
-- ORA-00001: unique constraint (SCOTT.EMP2_PK) violated , 동일 값 잘 잡아줌!

INSERT INTO EMP2 VALUES (NULL, '홍길동', 2000, '사원'); -- 에러나야 하는데... 남!
-- ORA-01400: cannot insert NULL into ("SCOTT"."EMP2"."EMPNO"), NULL 안들어가게 잘 잡아줌!
drop table emp2;
  create table emp2
  (
     empno number(4)  ,
     ename varchar2(15),
     sal   number(7,2),
     job   varchar2(15)
  ); 

  alter table emp2
  add constraint emp2_pk primary key (empno, ename);
  ==> Table altered. (empno와 ename을 조합한 한 개의 primary key설정!!)

  insert into emp2 values (8000,'홍길동',3000,'사원');
  --> 성공
  insert into emp2 values (8000,'길라임',3000,'사원');
  --> 성공
  insert into emp2 values (8002,'길라임',3000,'사원');
  --> 성공
  insert into emp2 values (8002,'길라임',3000,'사원');
  --> ORA-00001: unique constraint (SCOTT.EMP2_PK) violated
문제)
emp2 테이블의 PRIMARY KEY(emp2_pk)를 삭제하시오

    삭제1)
    ALTER TABLE emp2
    DROP CONSTRAINT emp2_pk;

    ALTER TABLE emp2
    ADD CONSTRAINT emp2_pk PRIMARY KEY(empno, ename);

    삭제2)
    ALTER TABLE emp2
    DROP PRIMARY KEY;

--===========================================================================

문제) 
emp2 테이블을 새롭게 생성(empno, ename, sal, deptno컬럼) 하고 테이블 생성 후 empno에 PRIMARY KEY설정을 하고 ename, sal, deptno에 NOT NULL하시오

DROP TABLE emp2;

CREATE TABLE emp2
(
    empno NUMBER(4),
    ename VARCHAR2(10),
    sal NUMBER(7,2),
    deptno NUMBER(6)
);

ALTER TABLE emp2
ADD CONSTRAINT emp2_pk ADD PRIMARY KEY (empno);

--혹은

ALTER TABLE emp2
ADD PRIMARY KEY (empno);

ALTER TABLE emp2
ADD CONSTRAINT emp2_ename_nn NOT NULL (ename);
-- ORA-00904: : invalid identifier 에러발생
-- ADD NOT NULL 안되는 이유? = 테이블 내의 모든 컬럼은 틀별한 설정을 하지 않았을 경우 NULL 값이 기본으로 설정되어짐, 그런데 거기에 ADD 한다고 하니 에러가 발생, NULL을 NOT NULL로 바꿔 주어야 겠다. 밑에서 제약수정!

<제약 수정>

ALTER TABLE 테이블명
MODIFY 컬럼명 [ CONSTRAINT 제약명 ] NOT NULL | NULL -- 둘 중 하나가 들어가면 된다.

그럼 위의 예문에 이어서 수정한다면.

ALTER TABLE emp2
MODIFY ename NOT NULL;
ALTER TABLE emp2
MODIFY sal NOT NULL;
ALTER TABLE emp2
MODIFY deptno NOT NULL;

desc emp2
                       Null?    Type
 ------------------------------------------------------------------------------------------
 EMPNO                                                                                                                                                                                NOT NULL NUMBER(4)
 ENAME                                                                                                                                                                                NOT NULL VARCHAR2(10)
 SAL                                                                                                                                                                                  NOT NULL NUMBER(7,2)
 DEPTNO                                                                                                                                                                               NOT NULL NUMBER(6)

문제) 
emp2테이블의 sal컬럼의 not null제약을 삭제하시오.
  alter table emp2
  modify sal null;
            not null ---> null변경

<제약이름을 통한 not null삭제>

  alter table emp2
  modify sal  constraint emp2_sal_nn not null;
--         null    --->  not null변경

  alter table emp2
  drop constraint emp2_sal_nn;            

SQL> desc emp2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                     NOT NULL VARCHAR2(15)
 SAL                                                NUMBER(7,2)
 DEPTNO                                    NOT NULL NUMBER(2)
  1. foreign key(외래키, 참조키)

    • 참조하는 테이블의 컬럼데이터 범위내의 데이터만 입력.

    • 예) 사원정보를 입력할 때 반드시 존재하는 부서의 번호를 속성값으로 가져와야 함!!

      • 사원테이블(emp)에서 사원의 정보를 입력,수정시
      • 부서테이블(dept)로 부터 존재하는 부서번호인지를 참조, 확인해야 함!!
    • 참조되는 컬럼은 unique 또는 primary key 설정이 되어 있어야 함.

       사원테이블(deptno 입력, 수정) -------> 부서테이블(deptno참조)
                                            10
                                            20
                                            30
                                            40
         ---> 컬럼레벨
           create table 테이블명emp
           (
                     컬럼명     자료형  [CONSTRAINT 제약명]  REFERENCES 참조테이블명 (참조컬럼명) 
              deptno  number(2)                references    dept   (deptno)                
           );
      
         ---> 테이블레벨
           create table 테이블명emp
           (
                      컬럼명1        자료형,
                      컬럼명2deptno  자료형,
                      컬럼명3        자료형,
      
               [CONSTRAINT 제약명]  FOREIGN KEY (컬럼명2)
                                   REFERENCES 참조테이블명 (참조컬럼명)
               ---> foreign key (deptno) 
                    references dept (deptno)     
           );
      
         ---> 테이블 생성 끝나 후 foreign key 추가
         alter table 테이블명
         ADD   [CONSTRAINT 제약명] foreign key (deptno) 
                                 references dept (deptno)
      
      --===========================================================================
      
      참조되는 테이블(부모테이블) dept2
      DROP TABLE emp2;
      
      CREATE TABLE emp2
      AS SELECT empno, ename, sal, deptno
      FROM EMP
      WHERE 1 = 0; -- 데이터는 복사하지 않겠습니다~
      --===========================================================================
      문제)
      emp2 테이블 내의 empno에 PRIMARY KEY 설정, deptno에 FOREIGN KEY 설정을 하시오!
      ALTER TABLE emp2
      ADD PRIMARY KEY(empno); --CONSTRAINT emp2_empno_pk 는 생략 가능
      
         alter table emp2
         add constraint emp2_deptno_fk  
             foreign key (deptno) -- 현재테이블의 컬럼
             references dept2 (deptno); -- 참조(부모)테이블의 컬럼  
      ---> ORA-02270: no matching unique or primary key for this column-list
      -- 참조되는 컬럼(dept2테이블 ---> deptno컬럼)은 반드시 unique 또는 primary key설정!!
      
      --dept2의 deptno : unique설정   
           alter table dept2
           add constraint dept2_uk unique (deptno);
      
      -- 또는 
      
      -- dept2의 deptno : primary key설정                           
           alter table dept2
           add constraint dept2_pk primary key (deptno);                     
           ==> 변경성공!!
      
      ALTER TABLE emp2
      ADD  CONSTRAINT emp2_dept_fk
                      FOREIGN KEY(deptno) -- 현재 테이블의 칼럼
                      REFERENCES dept2 (deptno); -- 참조(부모) 테이블의 컬럼
                      --외래키 설정 성공
      --===========================================================================
      
      문제1) emp3테이블을 새롭게 생성(empno, ename, sal, deptno)하고
            dept3테이블을 dept테이블 참조하는 서브쿼리를 통해 생성(전체구조, 데이터)하시오.
      DROP TABLE emp3;
      
      CREATE TABLE emp3
      (
          empno NUMBER(4),
          ename VARCHAR2(10),
          sal NUMBER(7,2),
          deptno NUMBER(4)
      );
      
      DROP TABLE dept3;
      
      CREATE TABLE dept3
      AS SELECT *
      FROM dept;
      --===========================================================================
      
      문제2) emp3테이블의 deptno가 dept3테이블의 deptno를 참조하도록 하시오.
            (dept3테이블의 deptno에 unique설정)
      
      ALTER TABLE emp3
      ADD CONSTRAINT dept3_uk unique(deptno);
      
      ALTER TABLE emp3
      ADD CONSTRAINT emp3_fk FOREIGN KEY (deptno) -- 현재(자식)테이블
                          REFERENCES dept3(deptno); -- 부모테이블
      --===========================================================================
      
      문제3) dept3테이블의 unique제약을 삭제하고 deptno에 primary key 제약 설정.
      --                    --------DROP                --------------------ADD
      
      ALTER TABLE dept3
      DROP CONSTRAINT dept3_uk;               
      
      ---> ORA-02273: this unique/primary key is referenced by some foreign keys
      ---> 참조하는(자식) 테이블이 존재하는 경우는 unique 와 primary key를 삭제할 수 없음!!
      
      --  삭제해결1) 
      -- 참조하는 (자식)테이블의 foreign key를 삭제 후 부모테이블의 unique, primary key 삭제
      
             -- 외래키 삭제 (자식테이블쪽의 foreign key)
                 alter table emp3
                 drop  constraint emp3_fk;
                 --==> 변경성공!!
      
             -- unique 삭제           
                alter table dept3
                drop constraint dept3_uk;      
                 --==> 변경성공^^*
      
      SELECT table_name, constraint_name
      FROM user_constraints
      WHERE table_name IN ('EMP3', 'DEPT3'); -- 대문자로 저장됨, 그래서 대문자로 입력
      
      
 TABLE_NAME                         CONSTRAINT_NAME
 ------------------------------------------------------------
 DEPT3                              DEPT3_UK
 EMP3                               EMP3_FK


 -- 삭제해결2)         
         CASCADE옵션을 사용(참조하는 (자식) 테이블이 여러 개 있을 때 유용)
         ALTER TABLE dept3
         DROP CONSTRAINT dept3_uk CASCADE;
         -- 변경성공
         -- CASCADE : 자동으로 관련된 모든 fk를 먼저 삭제해 줌.

         select table_name, constraint_name
         from   user_constraints
         where  table_name IN ('EMP3','DEPT3');
         ==> no rows selected 

        alter table dept3
        add  constraint dept3_deptno_pk primary key (deptno);
        ==> 변경성공!!

        alter table emp3
        add constraint emp3_deptno_fk  
             foreign key (deptno)
             references dept3(deptno);
        ==> 변경성공!!        

     --조회
     select table_name, constraint_name
     from   user_constraints
     where  table_name IN ('EMP3','DEPT3');

 TABLE_NAME                                                   CONSTRAINT_NAME
 ------------------------------------------------------------ -----------------------------
 DEPT3                                                        DEPT3_DEPTNO_PK
 EMP3                                                         EMP3_DEPTNO_FK       
 ```

<CHECK 제약>

  • 특정 범위내의 데이터만 입력

  • 정해진 데이터만 입력

  • 형식

    CHECK (조건식)
    
    -- 문제)
    -- emp4테이블 생성
    
        사원번호 : 행을 구분하는 용도     -- PRIMARY KEY
        사원명   : 기본값 '무명'지정       -- DEFAULT 데이터
        급여       : 최소 500, 최대 1000  -- CHECK ( 조건식 )
        매니저    : 반드시 입력            -- NOT NULL
        부서번호  : 부서테이블(dept3)의 부서번호 참조    -- FOREIGN KEY
        성별(Gender) : 'M' 또는 'F'입력 체크 -- CHECK ( 조건식 )
    
    DROP TABLE emp4;
    CREATE TABLE emp4
    (
        empno NUMBER(4) CONSTRAINT emp4_empno_pk PRIMARY KEY,
        ename VARCHAR2(10) DEFAULT '무명' ,
        sal NUMBER(7,2) CHECK ( sal BETWEEN 500 AND 1000 ) ,
        mgr NUMBER(4) NOT NULL ,
        deptno NUMBER(2) CONSTRAINT emp4_deptno_fk REFERENCES dept3(deptno),
        gender CHAR(1) CHECK (gender IN ( 'F', 'M' ) )
                            -- (gender = 'M' OR gender = 'F') 이렇게 해도 됨 위가 줄인것
    );
    -- 테이블 생성 성공!
    --테스트
    INSERT INTO emp4 (empno, ename, sal, mgr, deptno, gender)
                VALUES (8000,'이진주',1000, 8001, 10, 'F'); --성공
    INSERT INTO emp4 (empno, sal, mgr, deptno)
                VALUES (8001, 1000, 8001, 10); -- 무명이라고 잘 나오나?
    insert into emp4  (empno, ename,   sal,  mgr,  deptno , gender)
              values  (8002,  '길라임', 1001, 8001,   10   ,  'F' );  -- sal : check
    --==> ORA-02290: check constraint (SCOTT.SYS_C007032) violated
                     ---> sal : 500~1000
    insert into emp4  (empno, ename,   sal,  mgr,  deptno , gender)
              values  (8002,  '길라임', 1000, 8001,   10   ,  'F' );  -- sal : check
    insert into emp4  (empno, ename,   sal,  mgr,  deptno , gender)
              values  (8003,  '김주언', 1000, 8001,   10   ,  '남' );  -- gender : check
    --==> ORA-12899: value too large for column "SCOTT"."EMP4"."GENDER" (actual: 3,maximum: 1)
    insert into emp4  (empno, ename,   sal,  mgr,  deptno , gender)
              values  (8003,  '김주언', 1000, 8001,   10   ,  'Y' );  -- gender : check
    --==> ORA-02290: check constraint (SCOTT.SYS_C007033) violated
                         --==> 'M' 또는 'F'
    
    /*
         EMPNO ENAME                                 SAL GE
    ---------- ------------------------------ ---------- --
          8000 홍길동                                                           1000 F
          8001 무명                                                              1000
          8002 길라임                                                           1000 F
          8003 김주언                                                           1000 M
    */
※ 참고)

   create table emp5
   (
       deptno  number(2)  references 참조테이블명(참조컬럼명) on delete cascade
                                                         ---------
        -- 부모 (부서)테이블에서 특정 번호(예:10번부서)를 삭제했을때
        -- 자식 (사원)테이블에서 10번 부서에 근무하는 사원들을 삭제!! 
   );

   create table emp5
   (
       deptno  number(2)  references 참조테이블명(참조컬럼명) on delete set null
                                                         ---------
    -- 부모 (부서)테이블에서 특정 번호(예:10번부서)를 삭제했을때
    -- 자식 (사원)테이블에서 10번 부서에 근무하는 사원들의 deptno를 null값으로 변경!!
   );                

테스트1)
   drop table dept5;
   create table dept5
   as select * from dept;

   alter table dept5
   add constraint dept5_pk primary key (deptno);

   ------------------------------------------------
   drop table emp5;
   create table emp5
   (
      empno   number(4),
      ename   varchar2(15),
      sal     number(7,2),
      deptno  number(2)  constraint emp5_fk references dept5(deptno) on delete cascade
   );


   insert into emp5 (empno,ename,sal,deptno)
   select empno,ename,sal,deptno from emp;                

   ## 부모 테이블의 10번 부서 삭제 ##
   select count(*) from emp5;        ---> 14명 사원           
   delete from dept5 where deptno=10;   ---> 1행(10번부서) 삭제

   select count(*) from emp5;        ---> 11명 사원 (10번부서의 사원들도 함께 삭제 되었음)           

 테스트2)
   alter table emp5
   drop constraint emp5_fk;

   drop table dept5;
   create table dept5
   as select * from dept;

   alter table dept5
   add constraint dept5_pk primary key (deptno);

   ------------------------------------------------
   drop table emp5;
   create table emp5
   (
      empno   number(4),
      ename   varchar2(15),
      sal     number(7,2),
      deptno  number(2)  constraint  emp5_fk references dept5(deptno) on delete set null
   );  

   insert into emp5 (empno,ename,sal,deptno)
   select empno,ename,sal,deptno from emp;                

   ## 부모 테이블의 10번 부서 삭제 ##
   select count(*) from emp5;        ---> 14명 사원           
   delete from dept5 where deptno=10;   ---> 1행(10번부서) 삭제

   select count(*) from emp5;        ---> 14명 사원 (10번부서의 사원들의 deptno가 null로 변경됨)                  

SQL> select empno, ename, sal, deptno
     from emp5
     where deptno is null;

     EMPNO ENAME                                 SAL     DEPTNO
---------- ------------------------------ ---------- ----------
      7782 CLARK                                2450
      7839 KING                                 5000
      7934 MILLER                               1300                 

<데이터 딕셔너리> DD 시스템 테이블

  • 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는
    시스템 테이블.
  • 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할때
    데이터베이스서버에 의해 자동으로 갱신되는 테이블.

<제약조건 확인하기>

  • USER_CONSTRAINTS

  • DESC USER_CONSTRAINTS

    OWNER : 제약조건을 소유한 사용자명
    CONSTRAINT_NAME : 제약조건명
    CONSTRAINT_TYPE : 제약조건타입
    TABLE_NAME : 제약조건이 속한 테이블명
    SERCH_CONDITION : check제약일 경우 조건으로 사용되어지는 설명
    R_CONSTRAINT_NAME : FOREING KEY경우 어떤 PRIMARY KEY............
<CONSTRAINT_TYPE>
P : PRIMARY KEY
R : FOREIGN KEY
U : UNIQUE
C : CHECK, NOT NULL
궁금상자
- TABLE 생성시에 FOREIGN KEY 설정 안되나? 됨...

190529_DAY35, <복습>, ,


<조인>

  • 두 개 이상의 테이블을 참조해서 데이터를 검색
  • from 뒤에 두개이상의 테이블 정의

조인의종류)

​ cross join, equi join, non-equi join, self join, outer join

  • cross join

    • Where절 없이 사용하는 조인
    • 각 테이블이 갖는 행의 곱만큼 결과행을 생성
    FROM emp, dept
  • equi join

    • 테이블 간의 공통된(연관된) 칼럼을 '='을 통해 비교
    FROM emp, dept
    WHERE emp.deptno = dept.deptno
  • non-equi join

    • 대소비교, 범위를 표현하는 연산자를 사용할 때
    FROM emp, salgrade
    WHERE emp.sal between salgrade.losal AND salgrade.hisal
  • self join

    • 같은 테이블을 두 번 이상 참조 조회.
    • 마치 테이블이 두개 이상 존재하는 것처럼 사용
    • 조회하고자 하는 데이터가 같은 행에 없을 때 사용! (SMITH의 MGR 7902(FORD)로 FORD의 정보 조회할 때)
    SELECT e1.ename, e2.ename
    FROM emp e1, emp e2
    WHERE e1.ename = 'SMITH'
        AND
        e1.mgr = e2.empno;
  • outer join

    • 조회 결과행에 배제된 (빠진) 행을 출력하고 싶을 때 사용
    SELECT e1.ename || 
    '사원의 상사는'||
    e2.ename||
    '입니다.'
    FROM emp e1, emp e2
    WHERE e1.mgr = e2.empno;
    -- 기대 : 14명 사원 각각의 직속 상사명 출력
    -- 결과 : 13row... 뭔가 빠졌네
    -- 원인 : 매핑되지 않는 행이 발생
        -- 예
        WHERE e1.mgr = e2.empno;
        -- KING 의 mgr은 NULL , NULL = 데이터 => 결과도 NULL, 검색행에 누락됨.
        -- ※ NULL은 비교, 연산, 할당 불가!
    
    -- 해결 : where e1.mgr = e2.empno(+);
    
    SELECT e1.ename || 
    '사원의 상사는'||
    NVL(e2.ename, '[미정]')||
    '입니다.' "상사는 누구?"
    FROM emp e1, emp e2
    WHERE e1.mgr = e2.empno(+);

<ANSI 조인>

  • 표준조인, DBMS종류와 상관없이 실행.
  • FROM 뒤에 ',' 콤마를 사용하지 않는다.
  • cross join, inner join, natural join, outer join
  • cross join

    FROM emp CROSS JOIN dept
  • inner join

    FROM emp INNER JOIN dept
    -- ON 공통칼럼 비교 -- 반드시 ON 정의, but USING 절로 대체가 가능
    ON emp.deptno = dept.deptno
    
    USING (deptno)
    
    WHERE 추가 기능 -- 추가적인 조건 검색
  • natural join

    • 두 테이블을 비교해서 동일한 이름의 컬럼을 자동 using 해준다
    • 단, 단점으로는 동일한 이름으로 의도치 않은 컬럼도 묶어버림.
    FROM emp NATURAL JOIN dept
  • outer join

    FROM emp (LEFT 혹은 RIGHT 혹은 FULL) [OUTER] JOIN dept
    
    -- 오라클 문법에서는 모자란쪽(부족한쪽) 에 (+)를 붙이지만
    -- 
    SELECT e1.ename || 
    '사원의 상사는'||
    NVL(e2.ename, '[미정]')||
    '입니다.' "상사는 누구?"
    FROM emp e1 LEFT OUTER JOIN emp e2
    ON e1.mgr = e2.empno;

<서브쿼리>

  • 특정 테이블에서 검색된 결과를 다른 테이블에 전달하여 새로운 결과를 검색 할 때 사용

  • 메인 쿼리 이외의 추가되는 모든 SELECT문

    SELECT 컬럼명 -- 메인쿼리
            (SELECT ~ FROM ~) -- 서브쿼리
    
    FROM 테이블명
            (SELECT ~ FROM ~) -- 서브쿼리, 인라인 뷰
                            -- 서브쿼리의 결과가 하나의 테이블에 대한 뷰처럼 사용되기에 이렇게 부르는 것이다. 
    
    출처: https://narwhals.tistory.com/9 [IT 공부 블로그]
    
    WHERE 컬럼명 연산자 데이터 
            논리연산자( OR | AND )
            컬럼명 연산자 ( SELECT ~ FROM ~ ) -- 서브쿼리
    
    -- 단 서브쿼리에서 지켜주어야 하는 것!
    -- SELECT 서브쿼리는 1행 1열
    -- FROM 서브쿼리는 1행 1열도 되고, 다수행 다수열도 가능
    -- WHERE 서브쿼리는 결과행 1개 : 연산자 =, > , <
    --                        2개이상 : 연산자에 등호, 부등호를 사용 불가!! , 에러발생, 이를 IN, ANY, SOME, ALL, EXISTST 등(Multi Row Operator, 다중 행 연산자)를 통해 해결!
    -- 다중 행 연산자 사용
    WHERE deptno IN (
                       SELECT deptno
                        FROM dept
                        WHERE deptno < 30
                   )
    WHERE sal > ANY (
                       SELECT sal
                       FROM emp
                       WHERE deptno = 20
                 )
    
    WHERE sal > ALL (
                       SELECT sal
                       FROM emp
                       WHERE deptno = 20
                 )    
    
    WHERE exists (서브쿼리) -- TRUE FALSE로 반환, 결과행 0개 : FALSE, 결과행 1개이상 : TRUE

<DDL.>

  • CREATE, DROP, ALTER, RENAME, TRUNCATE

<테이블생성>

CREATE TABLE 테이블명
(
    컬럼명1 자료형, 
    컬럼명2 자료형
);
--테이블 객체생성! 구조만생성, 데이터는 없음 => 앞으로 INSERT명령문 통해 행단위의 입력!

<테이블생성2 - 서브쿼리를 이용한 테이블 생성>

  • 기존 테이블의 구조(컬럼, 자료형) 와 데이터를 복사

  • 제약(CONSTRAINT)은 복사에서 제외!

  • NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

  • 형식)

    -- 예전에 했던 복사 테이블 생성 방식
    
    SELECT tname, tabtype 
    FROM tab
    WHERE tname LIKE '%E%';
    
    SELECT tname, tabtype
    FROM tab
    WHERE tname LIKE 'EM%';
    
    DROP TABLE emp2;
    
    CREATE TABLE emp2
    AS SELECT * FROM emp;

<테이블 생성3 >

  • 서브쿼리를 이용한 테이블 생성 , 원하는 컬럼과 행만 복사

    CREATE TABLE 테이블명
    AS 서브쿼리;
    
    SELECT tname, tabtype from tab; -- 조회
    
    FROM emp
    WHERE deptno = 30;
    
    -- 생성
    CREATE TABLE emp3
    AS SELECT empno, ename, job -- 선택컬럼, 모든컬럼은 * 로
    FROM emp -- 원본테이블
    WHERE deptno = 30; -- 행제어 WHERE절 생략시 모든 행
    
    --오라클 조회어
    DESC emp3;
    

<데이블 생성4>

  • 서브쿼리를 이용한 테이블 생성 : 구조(컬럼, 자료형)만 복사

    CREATE TABLE emp4
    AS SELECT * FROM emp
    WHERE '길동' = '라임';
    -- 1 = 0,  1>2
    
    select * from emp4;
    -- no rows selected

<테이블의 구조 변경하기> ALTER

  • 컬럼추가, 컬럼삭제, 컬럼의 자료형, 바이트 수를 변경

    ALTER TABLE emp4
    ADD  (email  varchar2(20));
    --==> Table altered.
    
    desc emp4;
    
    ALTER TABLE emp4
    DROP COLUMN email;
    --==> 테이블 변경
    ※ALTER 정리
    ALTER TABLE 테이블명
    - 컬럼 추가 : ADD ( 추가 컬럼명 자료형 )
    - 컬럼 수정 : MODIFY ( 기존 컬럼명 자료형 )
    - 컬럼 삭제 : DROP COLUMN 삭제할 컬럼명
    - 컬럼이름변경 : RENAME COLUMN 기존컬럼명 TO 새컬럼명
    - 컬럼사용안함 : SET UNUSED (컬럼명)
    - UNUSED 설정 된 컬럼들 삭제 : DROP UNUSED COLUMN

<테이블 객체 삭제>

  • 형식)

    DROP TABLE 테이블명;
    
    SELECT tname, tabtype from tab;
    - 시스템테이블
    - 연결계정이 갖는 테이블명과 테이블 타입을 출력
    
    DROP TABLE emp2; 
    

<테이블 객체 이름 변경>

  • 형식)

    RENAME old_객체명  TO  new_객체명;
    
    CREATE TABLE emp2
    AS SELECT * FROM emp;
    
    SELECT tname, tabtype from tab;
    
    RENAME emp2 TO 사원테이블;
    
    select tname, tabtype from tab;
    
    

<테이블의 전체 데이터 지우기>

- 형식)

  - TRUNCATE TABLE 테이블명;

  ```SQL
  SELECT COUNT (*) FROM 사원테이블;

  TRUNCATE table 사원테이블;  

  ---> Table truncated. (테이블이 잘렸습니다!! : 테이블내의 모든 데이터가 삭제 되었음)
  ---> ※주의: truncate명령어를 통해 삭제된 데이터는 복구가 불가능!!

<DML.> Data Manipulation Language : 데이터 조작어

  • INSERT(입력), DELETE(삭제), UPDATE(수정, 재입력) --행단위 실행

<INSERT.> : 테이블에 (행) 데이터 추가 (최초입력)!

  • 형식

    INSERT INTO 테이블명 [( 컬럼명1, 컬럼명2, 컬럼명3)] -- 생략가능
                VALUES (데이터1, 데이터2, 데이터3);
                --만약, 테이블명 뒤에 컬럼명을 생략하는 경우
                -- 테이블 구조에 있는 순서에 따라 전체 컬럼명이 명시된 것으로 간주!!
                ---> 명시된 컬럼의 갯수와 데이터 갯수는 반드시 일치.
                  ---> 컬럼의 순서에 맞는 자료형 데이터가 와야함.
    
    

INSERT INTO dept
VALUES (50, '대전', '자재부');


create table dept2
as select * from dept;

select * from dept2;

insert into dept2 (deptno, dname, loc)
values ( 50, '기획부', '남터');

select * from dept2;


<데이터 입력시 에러 예>

```sql
insert into dept2 (deptno, dname)  values (60,'자재부','대전');
==> ORA-00913: too many values  

insert into dept2 (deptno, dname)  values (60);
==> ORA-00947: not enough values

insert into dept2 (deptno, dname, loc)  values ('자재부','대전',60);
또는
insert into dept2     values ('자재부','대전',60);
                               *
==> ORA-01722: invalid number

<NULL값 입력방법>

  • 컬럼명을 명시하지 않는다.

       create table dept3
       as select * from dept;
    
       insert into dept3  (deptno, dname)
                   values (50    , '개발부');
    
    select * from dept3;
문제) 아직 부서의 위치가 정해지지 않은 부서는 어디?
     select dname
     from   dept3
     where  loc  is null;   
     ==> 개발부

문제) 데이터가 들어가는 자리에 직접 null을 명시.
   insert into dept3  (deptno, dname, loc)  values (60,'기획부', null);
   insert into dept3  values (60,'기획부', null); -- 이렇게 줄일 수 있다.

 select * from dept3;

 문제) 데이터가 들어가는 자리에 ''를 입력
insert into dept3 (deptno, dname, loc)
 values (70,    '총무부', '');

 문제) 아직 부서의 위치가 정해지지 않은 부서는 어디?
    select dname
    from dept3
    where loc is null;

<서브쿼리로 행 추가>

  • 형식

    create table dept4
    as select * from dept
       where 1=0;        -- dept테이블의 구조(컬럼)만 복사, 데이터 복사X
    
    select * from dept4;
    ---> no rows selected       
    
    insert into dept4
    select * from dept; 
    
    문제) dept5테이블을 생성하고(dept테이블의 구조만 복사, 데이터복사X)
         dept테이블의 20,40번 부서의 정보(부서번호, 부서이름)를 서브쿼리를 통해 입력하시오.
    drop table dept5;
    
         create table dept5
         as select * from dept
            where 1=0;    
    
         insert into dept5           ---> 컬럼명 생략은 전체 컬럼 즉, (deptno, dname, loc) 생략 간주
         select deptno, dname       -- 2열
         from   dept   -- 4행
         where  deptno  IN (20,40);  -- 2행
    
    ---> ORA-00947: not enough values  : 데이터가 충분하지 않음!!
    
         insert into dept5 (deptno, dname)        
         select deptno, dname       -- 2열
         from   dept   -- 4행
         where  deptno  IN (20,40); 
    ---> 2 rows created.
    
    문제) 만약 dept테이블의 부서번호가 10단위로 구성되어 있다고 가정했을때
         10,30,50,70,....번의 부서의 정보를 dept5테이블에 옮기시오.(서브쿼리를 통한 행추가!!)
         ----------------
         십의 자리수가 홀수인 부서!!
    
         insert into dept5 -- (deptno, dname, loc)
         select deptno, dname, loc
         from   dept
         where  mod( deptno/10,  2)  = 1;
    ---> 2 rows created.     
    
    SQL> select * from dept5;
    
        DEPTNO DNAME                        LOC
    ---------- ---------------------------- --------------------------
            20 RESEARCH
            40 OPERATIONS
            10 ACCOUNTING                   NEW YORK
            30 SALES                        CHICAGO

<다중테이블에 다중 로우(행) 입력>

  • 형식

    INSERT ALL
        INTO 테이블명1 VALUES (COL, COL, COL)
        INTO 테이블명2 VALUES (COL, COL, COL)
        INTO 테이블명3 VALUES (COL, COL, COL)
    SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9
    FROM 테이블명;
    
    문제) emp테이블에서 30번 부서 사원들의 정보를 얻어서(조회해서)
    emp_hir(구조만 : empno, enmae, hiredate)테이블과
    emp_mgr(구조만 : empno, ename, mgr )테이블에 데이터를 입력하시오.
    
    CREATE TABLE emp_hir
    AS SELECT empno, ename, hiredate
    FROM emp
    WHERE 1 = 0;
    
    CREATE TABLE emp_mgr
    AS SELECT empno, ename, mgr
    FROM emp
    WHERE 1 = 0;
    
    INSERT ALL
        INTO emp_hir VALUES (empno, ename, hiredate)
        INTO emp_mgr VALUES (empno, ename, mgr)
    SELECT empno, ename, hiredate, mgr
    FROM emp
    WHERE deptno = 30;

<조건 (WHEN)에 의해 다중테이블에 다중 로우 입력하기>

  • 형식

    WHEN 조건식 THEN 실행할 문장
    
    테스트테이블 생성)
    CREATE TABLE emp_hir2
    AS SELECT empno, ename, hiredate
    FROM emp
    WHERE 1 = 0;
    
    CREATE TABLE emp_sal
    AS SELECT empno, ename, sal
    FROM emp
    WHERE 1 = 0;
    
    문제) 1982년 이후에 입사한 사원의 정보를 emp_hir2테이블에 입력하고
    급여가 2000이상을 받는 사원의 정보를 emp_sql테이블에 입력하시오.
    (insert all사용, 데이터정보는 emp테이블에서 조회)
    
    INSERT all
        WHEN hiredate >= to_date('1982/01/01')
        THEN INTO emp_hir2 VALUES (empno, ename, hiredate)
        WHEN sal >= 2000
        THEN INTO emp_sal VALUES (empno, ename, sal)
    SELECT empno, ename, hiredate, sal
    FROM emp;
    

<UPDATE.> : 기존 데이터에 대한 수정, 갱신, 재입력

  • 형식

    UPDATE 테이블명
    SEL 컬럼명1 = (변경할) 데이터, 컬럼명2 = (변경할) 데이터
    [WHERE 조건식];
    
    -- 주의 : UPDATE와 DELETE문을 실행하기 전 반드시 WHERE절 사용을 고려하자
    -- 왜? 만약 WHERE절 사용하지 않는다면 전체행에 대한 수정 또는 삭제가 실행되기 때문
    
    DROP TABLE dept3;
    
    CREATE TABLE dept3
    AS SELECT * FROM dept3;
    
    SELECT * FROM dept3;
    
    문제) 30번 부서의 급혀를 10% 인상하라
    DROP TABLE emp3;
    
    CREATE TABLE emp3
    AS SELECT empno, ename, sal, deptno
    FROM emp;
    
    UPDATE emp3
    SET sal = sal*1.1
    WHERE deptno = 30;
    
    SELECT * FROM emp3;

<서브쿼리를 이용한 데이터 수정>

DROP TABLE dept4;

CREATE TABLE dept4
AS SELECT * FROM dept;

문제) 부서번호 20번의 부서명과 지역을 40번 부서와 동일하게 변경하시오!

UPDATE dept4
SET dname = (
            SELECT dname
            FROM dept4
            WHERE deptno = 40
            ),
     loc = (
             SELECT loc
             FROM dept4
             WHERE deptno = 40
             )
WHERE deptno =20;

UPDATE dept4
SET ( dname, loc ) = (
             SELECT dname, loc
             FROM dept4
             WHERE deptno = 40
             ) --from where이 동일함으로 이렇게 묶어 줄 수 있다!
WHERE deptno =20;

<DELETE.> : ( 행 단위 )데이터 삭제명령어

  • 형식

    DELETE FROM 테이블명 --전체 행 삭제(모든데이터), 보통 drop해서 드문 일
    [WHERE 조건식]; --특정 행 삭제
    
    문제) 부서테이블(DEPT4)내의 모든 부서를 삭제하시오.
    DELETE FROM dept4;
    
    문제) DEPT4테이블을 유지한 상태(rollback하지 말고)에서     
         DEPT테이블의 데이터들을 복사(입력)하시오.
    UPDATE dept4
    VALUES (데이터);
    
    INSERT INTO dept4
    서브쿼리;
    
    INSERT INTO dept4
    SELECT deptno, dname, loc
    FROM dept;
    
    SELECT * FROM dept4;
    문제) 20번 부서를 삭제하시오.
    
    DELETE FROM dept4
    WHERE deptno = 20;
    
    SELECT * FROM dept4;
    
    문제) SALES부서에 속한 사원들을 사원테이블(EMP3)에서 삭제하시오.
    DROP TABLE emp3;
    
    CREATE TABLE emp3
    AS SELECT * FROM emp;
    
    SELECT * FROM emp3;
    --------------------------------
    delete from emp3
    where  deptno = 30;
    
    delete from emp3
     where  deptno = (select deptno
                          from  dept
                          where dname='SALES');    
    ----> 6 rows deleted.
    
    select empno, ename, deptno, dname
    from       emp3   inner join dept
    using   (deptno);

<MERGE.> 합병(병합)

  • 구조가 같은 두 개의 테이블을 하나의 테이블로 합치는 기능.

  • 기존에 존재하는 행이 있다면 새로운 값으로 갱신(update)되고
    존재하지 않는 행이 있다면 새로운 행으로 추가(insert)해라!!

  • 형식

       MERGE INTO 기준테이블명   별명
           USING  참조테이블명   별명
           ON (매칭 조건식)
       WHEN MATCHED      -- 매칭되는 행이 있다면
          THEN 업데이트문
       WHEN NOT MATCHED  -- 매칭되는 행이 없다면 
          THEN 추가문;
    
    CREATE TABLE emp11 -- 기분테이블
    AS SELECT * FROM emp;
    
    CREATE TABLE emp12 -- 참조테이블
    AS SELECT * FROM emp
    WHERE job = 'MANAGER';
    
    UPDATE emp12 SET job = '사원';
    INSERT INTO emp12 (empno, ename, job,   mgr, hiredate, sal, comm, deptno)
               VALUES (9000, '나길동','사원', 7788, sysdate, 2000, null, 30); -- emp11에는 없는 사원
    
    SELECT empno, ename, job FROM emp12;
    
    MERGE INTO emp11 e1
        USING emp12 e2
        ON (e1.empno = e2.empno)
    WHEN MATCHED -- 매칭되는 행이 있다면
        THEN -- 업데이트(변경)문
            UPDATE SET --SET앞에 생략
                e1.job = e2.job,
                e1.mgr = e2.mgr,
                e1.sal = e2.sal,
                e1.comm = e2.comm,
                e1.deptno = e2.deptno
    WHEN NOT MATCHED -- 매칭되는 행이 없다면
        THEN -- 추가문
            INSERT VALUES (e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm, e2.deptno);
궁금상자
- 테이블 조회하기
    SELECT * FROM all_all_tables;
- 테이블 목록 조회(접속한 계정)
    SELECT * FROM tabs; -- 그런데 이렇게 하면 어마어마하게 많이 나온다.
    SELECT tname, tabtype FROM tab; -- 이정도로만 조회하도록 하자!
- 테이블을 지우고 싶은데 일일이 지우기에는 너무 귀찮다... 어떻게 하면 편하게 지울 수 있을 까?
    모든 테이블을 지울 때에는
    SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;

    CASCADE CONSTRAINTS는 부모, 자식 테이블에서 우선적으로 자식테이블을 삭제한 후에야 부모테이블을 지울 수 있는데 이 옵션으로 참조되어진 모든 값을 오류없이 같이 지워버리기 때문에 사용, 단 참조값들은 참조 연결만 끊어지고 삭제되지 않는다. = 제약조건 삭제
- DELECATE TRUNCATE DTOP의 차이 (밑의 표 참조)    
DELETE( DML ) 원하는 데이터 삭제 테이블 용량 감소 안한다.
TRUNCATE ( DDL ) 테이블 자료 모두 삭제 원하는 데이터만 삭제 불가, 복구가 힘듬( ROLLBACK 안됨. )
DROP ( DDL ) 테이블 자체 삭제

190528_DAY ANSI JOIN 복습, 서브쿼리, DDL시작


============================= JOIN문제 ============================================
※아래의 문제들을 ANSI JOIN으로 해결하시오.

문제1) 사원들의 이름, 부서번호, 부서이름을 출력
select ename, emp.deptno, dname
from emp inner join dept
on emp.deptno = dept.deptno; -- inner join 들어왔다면 반드시,, 반드시 on이 뒤따라야 함, 공통항목 이어주는

select ename, deptno, dname
from emp inner join dept
using (deptno);

select ename, deptno, dname
from emp natural join dept;

문제2) 부서번호가 30번인 사원들의 이름, 직급, 부서번호, 부서위치를 출력
select ename, job, e.deptno, loc
from emp e inner join dept d --
on e.deptno = d.deptno
where e.deptno = 30; --where 말고 and e.deptno = 30; 으로 해도 같은 결과, 단 동등조건과 추가 조건 나눈다면 where을 사용해야 한다. 

select ename, deptno, loc
from emp natural join dept -- natural join은 using 사용하는것과 동일하다.
where deptno = 30;

select ename, deptno, loc
from emp inner join dept
using (deptno)
where deptno = 30;

문제3) 커미션을 받는 사원의 이름, 커미션, 부서이름, 부서위치 출력(커미션 0은 제외)
select ename, comm, dname, loc
from emp natural join dept
where comm <> 0
and comm is not null;

select ename, comm, dname, loc
from emp inner join dept
on emp.deptno = dept.deptno
where comm != 0
and comm is not null;

문제4) DALLAS에서 근무하는 사원들의 이름, 직급, 부서번호, 부서이름을 출력
select ename, job, deptno, dname
from emp natural join dept
where loc = 'DALLAS';

select ename, job, deptno, dname
from emp inner join dept
on emp.deptno = dept.deptno
where loc = 'DALLAS';

문제5) 사원이름에 'A'가 들어가는(포함하는) 사원들의 이름과 부서이름을 출력
select ename, dname
from emp inner join dept
on emp.deptno = dept.deptno
where ename like '%A%';


문제6) 사원이름과 직급, 급여, 급여등급을 출력
select ename, job, sal, grade
from emp inner join salgrade
on sal between losal and hisal;

/*
select ename, job, sal, grade, dept.deptno --오류 내가 ㅠㅠ
from emp left join salgrade
on emp.deptno = dept.deptno
where grade between losal and hisal;
*/

문제7) 사원들의 이름과 그 사원과 같은 부서에 근무하는 사원의 사원명, 부서번호를 출력
       ===> 자기 이름을 제외한 동료(56행) 정보 출력!!
select e1.ename, e2.ename deptno
from emp e1 inner join emp e2
on e1.deptno = e2.deptno
where e1.ename <> e2.ename; --혹은 e1.empno != e2.empno



/*
select e1.ename, e2.ename e2.deptno --오류 내가  ㅠㅠ
from emp e1 left join dept e2
on e1.deptno = e2.deptno;
where e1.ename != e2.ename;
*/

★<서브쿼리> Sub Query

  • 특정 테이블에서 검색된 결과를 다른 테이블에 전달하여 새로운 결과를 검색 할 때 사용.

    문제) JAMES 사원이 근무하는 부서이름을 출력
    select deptno
    from emp
    where ename = 'JAMES'; -- 30번 부서 근무
    
    select dname
    from dept
    where deptno = 30; -- 부서명 SALES
    
    select dname
    from dept
    where deptno = (select deptno
                    from emp
                    where ename = 'JAMES');
    결과)
    DNAME
    ----------------------------
    SALES
    
    --===========================================================================
    
    문제) FORD사원과 동일한 직책을 가진 사원에 대해 사원번호, 사원명, 급여, 직책을 출력하시오
    select empno, ename, sal, job
    from emp
    where job = 'ANALYST';
    
    select job
    from emp
    where ename = 'FORD';
    
    select empno, ename, sal, job
    from emp
    where ename <> 'FORD'
            and
            job = (select job
                     from emp
                     where ename = 'FORD');
    결과)
         EMPNO ENAME                       SAL JOB
    ---------- -------------------- ---------- ------------------
          7788 SCOTT                      3000 ANALYST
    
     --===========================================================================
    
     문제) MARTIN사원의 급여와 동일하거나 더 많이 받는 사원의 사원명, 급여를 출력
     select sal
     from emp
     where ename = 'MARIN';
    
     select ename, sal
     from emp
     where sal >= ( 
             select sal
             from emp
             where ename = 'MARTIN'
             );
    
     결과)
     ENAME                       SAL
    -------------------- ----------
    ALLEN                      1600
    WARD                       1250
    JONES                      2975
    MARTIN                     1250
    BLAKE                      2850
    CLARK                      2450
    SCOTT                      3000
    KING                       5000
    TURNER                     1500
    FORD                       3000
    MILLER                     1300
    
     --===========================================================================
    
     문제) DALLAS에서 근무하는 사원의 이름, 부서번호를 출력
    
     select loc
     from dept
     where loc = 'DALLAS';
    
     select ename, deptno
     from emp
     where deptno = 
                     (
                         select deptno
                         from dept
                         where loc = 'DALLAS'
                     );
    
     select ename, deptno
     from emp natural join dept
     where loc = (
                 select loc
                 from dept
                 where loc = 'DALLAS'
                 );
    
     select ename, deptno
     from emp inner join dept
     using (deptno)
     where loc = (
                 select loc
                 from dept
                 where loc = 'DALLAS'
                 );
    
     결과)
     ENAME                    DEPTNO
    -------------------- ----------
    JONES                        20
    FORD                         20
    ADAMS                        20
    SMITH                        20
    SCOTT                        20
    
    --===========================================================================
    SUB QUERY 문제들
    문제)
    SALES부서에서 근무하는 모든 사원의 이름과 급여, 부서번호를 출력
    SELECT deptno
    FROM dept
    WHERE dname = 'SALES';
    
    SELECT ename, sal, deptno
    FROM emp
    WHERE deptno =
                (
                    SELECT deptno
                    FROM dept
                    WHERE dname = 'SALES'
                  );
    
    결과)
    ENAME                       SAL     DEPTNO
    -------------------- ---------- ----------
    ALLEN                      1600         30
    WARD                       1250         30
    MARTIN                     1250         30
    BLAKE                      2850         30
    TURNER                     1500         30
    JAMES                       950         30
    
    --===========================================================================
    
    문제) 자신의 직속상사가 KING인 사원의 이름과 급여, 직속상사의 사원번호를 출력.
    SELECT empno
    FROM emp
    WHERE ename = 'KING';
    
    SELECT ename, sal, mgr
    FROM emp
    WHERE mgr = (
                   SELECT empno
                    FROM emp
                    WHERE ename = 'KING'
                );
    결과)
    ENAME                       SAL        MGR
    -------------------- ---------- ----------
    JONES                      2975       7839
    BLAKE                      2850       7839
    CLARK                      2450       7839
    
    --===========================================================================
    
    문제) 평균급여보다 많은 급여를 받은 사원들의 사번, 사원명, 급여를 출력.(급여 내림차순)                
    SELECT AVG(sal)
    FROM emp;
    
    SELECT empno, ename, sal
    FROM emp
    WHERE sal >= (
                SELECT AVG(sal)
                FROM emp
                )
    ORDER BY SAL DESC;
    
    결과)
         EMPNO ENAME                       SAL
    ---------- -------------------- ----------
          7839 KING                       5000
          7902 FORD                       3000
          7788 SCOTT                      3000
          7566 JONES                      2975
          7698 BLAKE                      2850
          7782 CLARK                      2450
    
    --===========================================================================
    
    문제) 10번 부서의 사원 중 전체(사원) 최대급여를 받는 사원과 동일한 급여를 받는 사원의 사원번호와 사원명을 출력.
    (문제 조금 이상...)
    SELECT MAX(sal)
    FROM emp
    WHERE deptno = '10';
    
    SELECT empno, ename --열제어
    FROM emp
    WHERE sal >= (
                   SELECT MAX(sal)
                    FROM emp
                    WHERE deptno = '10'
                );
    
    SELECT empno, ename --열제어
    FROM emp
    WHERE     sal = (
                    SELECT MAX(sal)
                    FROM emp
                  )
            and
            deptno = 10;
    
    결과)
         EMPNO ENAME
    ---------- --------------------
          7839 KING
    
    --===========================================================================
    
    문제) 10번 부서에서 근무하는 사원의 이름과 10번 부서의 부서명을 출력하시오.
    SELECT a,b,c,d,e,f
    FROM (SELECT a,b,c FROM test1), (SELECT d,e,f FROM test2);
    --FROM 뒤에 테이블명(행렬구성)도 올 수 있고 서브쿼리(결과값 행렬)도 위치 할 수 있다.
    
    SELECT empno, ename, sal --에러발생 : 서브쿼리에 sal컬럼에 대한 정보가 없기 때문
    FROM (SELECT empno, ename FROM emp);
    
    SELECT ename, dname
    FROM emp, (select dname from dept where deptno = 10 )
    WHERE deptno = '10';
    
    SELECT ename, (select dname from dept where deptno = 10 ) --아무거나 쓸 수는 없다.
    --SELECT 옆, WHERE에는 1행 1열 값만 들어갈 수 있고, FROM에는 행렬만 들어가면 관계 없다.
    FROM emp
    WHERE deptno = '10';
    
    결과)
    ENAME                DNAME
    -------------------- ----------------------------
    CLARK                ACCOUNTING
    KING                 ACCOUNTING
    MILLER               ACCOUNTING
    
    --추가 정보
    SELECT ename, (SELECT '몸에 좋은 딩꼬' FROM dual ) MSG
    FROM emp
    WHERE deptno = '10';
    
    SELECT ename, (SELECT '몸에 좋은 딩꼬', '이것도 될까?'  --이건 안된다 1행 2열은 안됨! 
                   FROM dual ) MSG
    FROM emp
    WHERE deptno = '10';
    
    ORA-00913: too many values --이 오류 발생
    
    --===========================================================================
    
    문제) BLAKE와 같은 부서에 있는 사원들의 이름과 입사일자, 부서번호를 출력하시오.
    SELECT ename, hiredate, deptno
    FROM EMP
    WHERE deptno = 30;
    
    SELECT ename, hiredate, deptno
    FROM emp
    WHERE deptno = (
                    SELECT deptno
                    FROM emp
                    WHERE ename = 'BLAKE'
                    );
    
    결과)
    ENAME                HIREDATE     DEPTNO
    -------------------- -------- ----------
    ALLEN                81/02/20         30
    WARD                 81/02/22         30
    MARTIN               81/09/28         30
    BLAKE                81/05/01         30
    TURNER               81/09/08         30
    JAMES                81/12/03         30
    
    --===========================================================================
    
    추가)
    만약 BLAKE라는 이름을 갖은 신입이 새로 들어왔다면?!
    --사원추가 (다른 부서에 BLAKE 등장!)
    INSERT INTO emp ( empno, ename, hiredate, deptno )
                values ( 8000, 'BLAKE', sysdate, 20 );
    --하고 나서 다시 위~~의 명령어를 다시 입력하면?
    ORA-01427: single-row subquery returns more than one row
    --에러 발생! 이렇게 데이터의 크기가 커지면 문제가 발생 할 수 있는 것.
    
    --해결방법!
    SELECT ename, hiredate, deptno
    FROM emp
    WHERE deptno = 30 or deptno = 20;
    
    SELECT ename, hiredate, deptno
    FROM emp
    WHERE deptno in (30,20); --이렇게 줄일 수 있다.
    
    --다시 위~~~ 의 명령어로 돌아가서
    SELECT ename, hiredate, deptno
    FROM emp
    WHERE deptno IN (
                    SELECT deptno
                    FROM emp
                    WHERE ename = 'BLAKE'
                    );
    --실행이 너~ 어어어 무 잘된다!
    
    ENAME                HIREDATE     DEPTNO
    -------------------- -------- ----------
    BLAKE                19/05/28         20
    SMITH                80/12/17         20
    ALLEN                81/02/20         30
    WARD                 81/02/22         30
    JONES                81/04/02         20
    MARTIN               81/09/28         30
    BLAKE                81/05/01         30
    SCOTT                87/04/19         20
    TURNER               81/09/08         30
    ADAMS                87/05/23         20
    JAMES                81/12/03         30
    FORD                 81/12/03         20
    

<다중 행 서브 쿼리>

  • 서브쿼리의 결과행이 여러개 ( 2개행 이상 )
  • 반드시 다중 행 연산자 ( Multiple Row Operator) 와 함께 사용

※다중 행 연산자

  • IN : 메인쿼리의 비교조건이 서브쿼리의 결과중에서 하나라도 일치하면 참
  • su in (10,20,30)
  • ANY, SOME : 메인쿼리의 비교조건이 서브쿼리의 결과와 하나이상 일치하면 참.
  • ALL : 메인쿼리의 비교조건이 서브쿼리의 결과와 모든 값이 일치하면 참.
  • EXISTS : 메인쿼리의 비교조건이 서브쿼리의 결과중에서 만족하는 값이 하나라도 존재하면 참
SELECT *
FROM dept
WHERE exists(SELECT * --서브쿼리 결과
              FROM emp
             WHERE deptno = 10);
    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
--위와 달리! WHERE절을 바꿔보자!
SELECT *
FROM dept    
WHERE exists(SELECT * 
              FROM emp
             WHERE deptno = 40); --10이 아니라 40으로!
--이러면 결과 안나온다, 데이터 존재 유무에 따라 SELECT, DELETE, UPDATE, INSERT시 제어하는 역할로 사용.
--서브쿼리의 결과가 있느냐 없느냐만 체크
no rows selected --결과값
사용법)
WHERE EXISTS (서브쿼리)
WHERE NOT EXISTS (서브쿼리)

--===========================================================================

문제)
급여를 3000이상 받는 사원이 소속된 부서와 동일한 부서에 근무하는 사원들의 사원명, 급여, 부서번호를 출력하시오. (여기서 조건은 **급여를 3000이상 받는 사원이 소속된 부서** 조건절로!)
SELECT ename, sal, deptno
FROM emp
WHERE deptno IN (
                  SELECT deptno
                    FROM emp
                    WHERE sal >= 3000
                ); --20번 부서와 10번 부서의 사람들
ENAME                       SAL     DEPTNO
-------------------- ---------- ----------
FORD                       3000         20
ADAMS                      1100         20
SCOTT                      3000         20
JONES                      2975         20
SMITH                       800         20
BLAKE                                   20
MILLER                     1300         10
KING                       5000         10
CLARK                      2450         10

조건서브쿼리)
SELECT deptno
FROM emp
WHERE sal >= 3000;

    DEPTNO
----------
        20
        10
        20

--===========================================================================

문제)
부서번호가 30번인 사원들 중 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 사원명, 급여를 출력.
(ALL 다중 행 연산자 사용!!)

조건서브쿼리)
SELECT SAL
FROM EMP
WHERE DEPTNO = 30;

결과)
       SAL
----------
      1600
      1250
      1250
      2850
      1500
       950

SELECT ename, sal
FROM emp
WHERE sal > 
            (
               SELECT SAL
                FROM EMP
                WHERE DEPTNO = 30
            ); --에러 발생!

SELECT ename, sal
FROM emp
WHERE sal > ALL --ALL을 쓰자!
            (
               SELECT SAL
                FROM EMP
                WHERE DEPTNO = 30
            );

SELECT ename, sal
FROM emp
WHERE sal > 
            (
               SELECT MAX(SAL) -- MAX 쓰면 ALL안써도 된다, 하지만 위에서 연습겸 ALL을 사용한 것.
                FROM EMP
                WHERE DEPTNO = 30
            );

결과)
ENAME                       SAL
-------------------- ----------
JONES                      2975
FORD                       3000
SCOTT                      3000
KING                       5000

--===========================================================================

문제) 부서번호가 30번인 사원들 중 급여를 가장 많이 적게 사원보다 더 많은 급여를 받는 사원의 사원명, 급여를 출력(ANY 다중 행 연산자 사용!)

SELECT ename, sal
FROM EMP
WHERE sal  >
         (
            SELECT sal --1600, 1250, 1250, 2850, 1500, 950 들어옴
            FROM emp
            WHERE deptno = 30
          );

ORA-01427: single-row subquery returns more than one row -- 에러발생

SELECT ename, sal
FROM EMP
WHERE sal  > ALL --모든 데이터중 가장 큰 값
         (
            SELECT sal --1600, 1250, 1250, 2850, 1500, 950 들어옴
            FROM emp
            WHERE deptno = 30
          );
ENAME                       SAL
-------------------- ----------
JONES                      2975
FORD                       3000
SCOTT                      3000
KING                       5000

SELECT ename, sal
FROM EMP
WHERE sal  > ANY --모든 데이터중 가장 큰 값, 각 항목보다 커도 좋고(1600보다, 1250보다...)
         (
            SELECT sal --1600, 1250, 1250, 2850, 1500, 950 들어옴
            FROM emp
            WHERE deptno = 30
          );

ENAME                       SAL
-------------------- ----------
KING                       5000
FORD                       3000
SCOTT                      3000
JONES                      2975
BLAKE                      2850
CLARK                      2450
ALLEN                      1600
TURNER                     1500
MILLER                     1300
WARD                       1250
MARTIN                     1250
ADAMS                      1100

문제 해답
SELECT ename, sal
FROM EMP
WHERE sal  > --모든 데이터중 가장 큰 값, 각 항목보다 커도 좋고(1600보다, 1250보다...)
         (
            SELECT min(sal) --1600, 1250, 1250, 2850, 1500, 950 들어옴
            FROM emp
            WHERE deptno = 30
          );

ENAME                       SAL
-------------------- ----------
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
SCOTT                      3000
KING                       5000
TURNER                     1500
ADAMS                      1100
FORD                       3000
MILLER                     1300

--===========================================================================

문제1)

SCOTT과 급여가 동일하거나 더 많이 받는 사원의 이름과 급여 출력
SELECT ename, sal
FROM emp
WHERE sal >= any ( --SCOTT가 하나 있으면 >= 도 관계 없지만, 중복되면 any등으로 사용해야 한다.
                SELECT sal
                FROM emp
                WHERE ename = 'SCOTT'
            );

ENAME                       SAL
-------------------- ----------
SCOTT                      3000
KING                       5000
FORD                       3000

--추가
SELECT ename, sal
FROM emp
WHERE sal >= all ( --SCOTT가 하나 있으면 >= 도 관계 없지만, 중복되면 any등으로 사용해야 한다.
                SELECT sal
                FROM emp
                WHERE ename = 'SCOTT'
            );
--===========================================================================

문제2)
직급(job)이 사원(CLERK)인 사람이 속한 부서의 부서번호와 부서명, 부서위치를 출력.
SELECT dname, loc
FROM dept
WHERE deptno IN (
                SELECT deptno
                FROM emp
                WHERE job = 'CLERK' 
            );

DNAME                        LOC
---------------------------- --------------------------
ACCOUNTING                   NEW YORK
RESEARCH                     DALLAS
SALES                        CHICAGO

--===========================================================================

문제3)
사원명에 'T'를 포함하고 있는 사원들과 같은 부서에서 근무하고 있는 사원의 사원번호 이름을 출력.
SELECT empno, ename
FROM emp
WHERE deptno IN ( -- = 을 쓴다면 모두 만족해야 하는 것, IN을 써야 한다.
                SELECT deptno
                FROM emp
                WHERE ename like '%T%'
              );

     EMPNO ENAME
---------- --------------------
      7902 FORD
      7876 ADAMS
      7788 SCOTT
      7566 JONES
      7369 SMITH
      8000 BLAKE
      7900 JAMES
      7844 TURNER
      7698 BLAKE
      7654 MARTIN
      7521 WARD
      7499 ALLEN
--===========================================================================

문제4) 부서위치가 NEW YORK인 모든 사원의 이름, 부서번호를 출력

SELECT ename, deptno
FROM emp
WHERE deptno IN ( --NEW YORK이 또 생길 수도 있잖아. 지사라던가... 그래서 IN을 쓰는것이 좋다.
                   SELECT deptno
                    FROM dept
                    WHERE loc = 'NEW YORK'
                  );

ENAME                    DEPTNO
-------------------- ----------
CLARK                        10
KING                         10
MILLER                       10

--===========================================================================

문제5) SALES부서의 모든 사원의 이름과 급여출력

SELECT ename, sal
FROM emp
WHERE deptno = 
               (
                   SELECT deptno
                   FROM dept
                   WHERE dname = 'SALES'
                );

ENAME                       SAL
-------------------- ----------
ALLEN                      1600
WARD                       1250
MARTIN                     1250
BLAKE                      2850
TURNER                     1500
JAMES                       950

--===========================================================================

문제6) KING에게 보고하는 모든 사원의 이름과 급여를 출력

SELECT ename, sal
FROM emp
WHERE mgr IN
            (
                SELECT empno
                FROM emp
                WHERE ename = 'KING'
             );

ENAME                       SAL
-------------------- ----------
JONES                      2975
BLAKE                      2850
CLARK                      2450

--===========================================================================

문제7) 자신의 급여가 평균 급여보다 많고  이름에 'S'가 들어가는 사원과
      ------------------------ ----------
                (1번째 조건)      (2번째 조건)
                ---> 1,2번을 동시에 만족하는 사원의 부서

       동일한 부서에서 근무하는 모든 사원의 사원번호, 사원명, 급여 출력.

SELECT empno, ename, sal
FROM emp
WHERE deptno IN -- IN 은 = 과 같음 (거으...의?), 여기서는 IN 또는 ANY 또는 ALL이 나와야 한다!
            (
                SELECT deptno
                FROM emp
                WHERE ename like '%S%'
                        and
                sal > (SELECT AVG(sal) FROM emp)
              );

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7902 FORD                       3000
      7876 ADAMS                      1100
      7788 SCOTT                      3000
      7566 JONES                      2975
      7369 SMITH                       800
      8000 BLAKE

-- WHERE 그룹함수() => 에러
-- WHERE sal > avg(sal) 이러면 에러 
-- 정상실행 위해서는 서브쿼리로 !
-- WHERE sal > ( SELECT avg( sal )
--                FROM emp)  => 정상실행

-- HAVING 그룹함수() => 정상실행
-- HAVING avg(sal) > 2000 => 정상실행
-- HAVING deptno = 10 => 정상실행 왜? 그룹 중 10번 부서만!!
-- HAVING sal > 2000 => 에러? 왜? 10번부서에는 sal속성이 없기 때문에

<DDL.> Data Definition Language : (객체)데이터 정의어

  • 객체 : TABLE, VIEW, SEQUENCE, TRIGGER

  • create, drop, alter, rename, truncate

  • DDL은 AUTO 커밋!

<테이블(객체) 생성>

형식)

CREATE TABLE 테이블명
(
    컬럼명1 자료형,
    컬럼명2 자료형,
    컬럼명3 자료형
);

<자료형> 데이터 : 문자, 숫자, 날짜, 이미지등

  • CHAR : 주어진 크기만큼 고정길이의 문자 저장 1바이트 ~ 2000바이트
pName char(10)
gildong입력 ---> '    gildong' 이 입력됨 ( 앞에 공백)
  • VARCHAR2 : 주어진 크기만큼 가변길이의 문자 저장 1바이트 ~ 4000바이트
pName varchar2(10)
gildong입력 ---> 'gildong' 입력!
  • NUMBER(p[,s]) : 정밀도와 스케일로 표현되는 숫자
    • 바이트 수 생략 가능
number(7,2) --전체 자릿수 7, 소숫점 이하 2자리까지 표현, 정수는 5자리까지!
--예) no number( 7, 2 ) => 12345.67
--입력 ==>12345 ( O )
--입력 ==>99999 ( O )
--입력 ==>123456 ( X ) 
--입력 ==>12345.678 ( O ), 입력은 된다, 하지만 저장은 12345.68(올림처리)
--입력 ==>99999.99 ( O )
--입력 ==>99999.992 ( O )
--입력 ==>99999.995 ( X ), 저장 100000.00이기때문에
pName varchar2(10) -- ( O )
pName varchar2 -- ( X )
pAge number(10) -- ( O )
pAge number -- ( O )
  • DATE : 날짜 형식 저장
  • TIMESTAMP : DATE형의 확장된 형태
  • BLOB : 대용량의 바이너리 데이터를 저장, 최대 4GB
  • CLOB : 대용량의 텍스트 데이터를 저장, 최대 4GB
  • BFILE : 대용량의 바이너리 데이터를 파일형태로 저장, 최대 4GB

CREATE TABLE emp
( );
--ORA-00904: : invalid identifier --에러발생, 이클립스에도 이런식으로 뜬다면 DB의 문제라는것을 파악해야 한다.
--테이블을 생성하기 위해서는 최소 한 개 이상의 컬럼을 정의해야 함!

--===========================================================================

CREATE TABLE emp
(
    empno number(4)
);
--ORA-00955: name is already used by an existing object --에러 발생
-- 이미 존재하는 테이블명(객체명)과 같은 이름은 생성 불가!

--===========================================================================

CREATE TABLE 2emp
(
    empno number(4)
);
--ORA-00903: invalid table name --에러발생. 부적절한 테이블(식별자)명
-- 식별자 이름규칙이 있다!
-- 첫글자 숫자불가, 예약어불가, 특수문자 불가

--===========================================================================

CREATE TABLE emp2
(
    empno number(4)
);
--Table created.
--===========================================================================
SQL> select tname from tab;
TNAME
------------------------------------------------------------
DEPT
EMP
EMP2
SALGRADE
--임포트한 테이블 조회 가능

--===========================================================================

CREATE TABLE emp2
(
    empno number(4)
    ename VARCHAR(4)
    sal number(7,2)
);
--ORA-00907: missing right parenthesis -- 에러발생
--두 개 이상의 컬럼을 정의할 때 그 구분을 위해 ','(콤마)를 사용!

CREATE TABLE emp3
(
    empno number(4),
    ename VARCHAR(4),
    sal number(7,2)
);
--Table created. -- 생성 완료!

SELECT * FROM emp2;
SELECT * FROM emp3;
--no rows selected --조회결과 없음

<서브쿼리를 이용한 테이블 생성>

  • 기존 테이블의 구조( 컬럼, 자료형 )와 (행) 데이터를 복사

  • 제약(constraint)은 복사에서 제외

  • 형식

    CREATE TABLE 테이블명
    AS 서브쿼리;
    
    create table emp4
    as select * from emp; --select는 열제어 *(와일드카드)는 모든 행
    --Table created. 메시지
    
    desc emp4;
    desc emp;
    --NULL? 은 NULL허용 여부
    
    SQL> select count(*) from emp3;
    
      COUNT(*)
    ----------
             0
    
    SQL> select count(*) from emp4;
    
      COUNT(*)
    ----------
            15
    
    SELECT empno, ename, sal
    FROM emp4;
         EMPNO ENAME                       SAL
    ---------- -------------------- ----------
          8000 BLAKE
          7369 SMITH                       800
          7499 ALLEN                      1600
          7521 WARD                       1250
          7566 JONES                      2975
          7654 MARTIN                     1250
          7698 BLAKE                      2850
          7782 CLARK                      2450
          7788 SCOTT                      3000
          7839 KING                       5000
          7844 TURNER                     1500
          7876 ADAMS                      1100
          7900 JAMES                       950
          7902 FORD                       3000
          7934 MILLER                     1300
궁금상자
1. 다중행연산자 이해가 안된다. (IN, ANY, SOME, ALL, EXISTS)
- IN은 OR OR OR... =과 같으나 =보다는 IN을 쓰는것이 좋다. 중복된 값이 있을때에는 = 쓰면 에러가 발생한다. 
- ANY랑 SOME은 같은 의미로 사용되는듯 ?
- ALL과 ANY는 아래의 표 참조
- EXISTS는 서브쿼리 값 있으면 반환

2. 서브쿼리는 SELECT문 내의 또다른 SELECT문 정도?, 데이터 가공 위해, 선생님의 수업시간 설명 참조. SELECT INSERT UPDATE DELETE 에서 사용 SELECT리스트 FROM절 WHERE절에도 조건에 따라 사용 가능

3. CHAR VARCHAR VARCHAR2
아래 표 참조
 VARCHAR와 VARCHAR2차이 없으나, 오라클에서는 VARCHAR2 사용 권장, 오라클에서 VARCHAR 타입은 추후 다른 용도로 사용한다고 함
     
컬럼 > ANY 가장 작은 값보다 크다, ? > MIN
컬럼 < ANY 가장 큰 값보다 작다, ? <MAX
컬럼 > ALL 가장 큰 값보다 크다, 최대값 반환 > MAX
컬럼 < ALL 가장 작은 값보다 작다, 최소값 반환 < MIN
CHAR VARCHAR VARCHAR2
고정형 가변형 가변형
2000byte 4000byte4 000byte

+ Recent posts