[46][23.11.27][구디아카데미 후기/국비지원 IT개발자취업/김승수 선생님]

조인

: 두 개 이상의 테이블을 가지고
: 카타시안의 곱 - 경우의 수까지 모두 조회됨(배수로 생성)
: 조건을 어떻게 가져갈 것인가에 따라 natural join,self join,non-equal join,outer join
: 조건식에 무엇(PK,FK-상속관계)을 쓸 것인가?

• 모델링을 하면 관계를 표현하게 되고
  ERD(entity relation diagram) - ER-WIN설치

• 논리적 모델링(Entity,Attribute) - 추상화 단계 - 아직 결정 안 됨(번복)

• 물리적 모델링(Table,column)

---

ex.헬스짐 사이트

• 회원, 강사
• 프로그램: 수영, 요가, 골프, pt...
• 게시판: 공지사항,양도양수,QnA,자유게시판,...
• 회원관리: 회원가입(insert), 로그인(select){권한,인증}, 회원탈퇴(delete),회원정보수정(update) → DML

구체화하고 문서화해서 하나의 약속(표준)으로 시방서-산출물-기준으로 구현한다

• 폭포수 모델/애자일

• 분석(문서 수집)-설계(요구사항정의서,화면정의서,클래스설계,DB설계)-개발-테스트-배포

• 후보군
  회원,강사,QnA,QnA_comment,Notice,FreeBoard
  

• 화면에 대한 요구사항 정의서

• QnA 글 작성하기
  : 제목
  : 작성자 로그인을 한 사람만 작성할 수 있다 - 로그인 정보 쿠키나 세션에 담김 - 인증을 받았다면 이름이 있다(보여지는 건 이름이지만 회원번호를 insert 한다)
  : 내용

• ERwin
  

--사용자 계정 생성하기

create user tomato identified by tomato;

--사용자 계정으로 커넥션 허용하기

grant connect,resource to tomato;

grant create sequence to tomato;

-- 테이블 생성 권한(with 실행계획)
grant create table to tomato with admin option;

--(인라인) 뷰를 생성하는 권한
grant create view to tomato;

-- 작성자 이름 출력: natural join
<구방법>
SELECT 
       qna_title,qna_content,mem_name
  FROM qna,member
 WHERE qna.mem_no = member.mem_no; 
 
<natural join> 
SELECT 
       qna_title,qna_content,mem_name
  FROM qna natural join member;
  
-- 댓글 존재하는지 찾아서 가져오는
SELECT 
       qna_title,qna_content,mem_name,qc_content
  FROM qna,member,qna_comment qc
 WHERE qna.mem_no = member.mem_no
   AND qna.qna_no = qc.qna_no;
   
 -- outer join
SELECT 
       qna_title,qna_content,mem_name,qc_content
  FROM qna,member,qna_comment qc
 WHERE qna.mem_no = member.mem_no
   AND qna.qna_no = qc.qna_no(+);

조인

• 둘 이상의 테이블 연결(상속,상속이 아닌 경우)하여 데이터를 검색하는 방법
• 회원 집합과 상품집합의 관계형태는? n:n

SELECT * FROM t_giftmem;
SELECT * FROM t_giftpoint;

SELECT *
  FROM t_giftmem,t_giftpoint;
  
SELECT *
  FROM t_giftmem mem,t_giftpoint poi
 WHERE poi.name_vc = '과자종합';
 
--non-equi join
SELECT *
  FROM t_giftmem mem,t_giftpoint poi
 WHERE poi.name_vc = '과자종합'
   AND poi.point_nu <= mem.point_nu;

조인 방법과 방식

• 조인 방법: Natural Join(등가조인,equi조인),Non-equi조인,Self조인,Outer조인
• 조인 방식: Nested Loop Join 방식,Hash Join 방식

Outer Join

• 두 개 이상의 테이블 조인시 한 쪽 테이블의 행에 대해 다른 쪽 테이블에 일치하는 행이 없더라도 다른 쪽 테이블의 행을 null로 하여 행을 리턴하는 것
• 연산자를 사용할 수 있다(+)
• 한 쪽에만 올 수 있다
• 조인시에 값이 없는 조인측에 (+) 기호를 위치시킨다

---

• LEFT OUTER JOIN - 오른편에 값이 없을 때
• RIGHT OUTER JOIN - 왼편에 값이 없을 때
• FULL OUTER JOIN - 양편에 모두 값이 없을 때

<ex>
SELECT
       empno,ename,dname
  FROM emp,dept
 WHERE emp.deptno(+) = dept.deptno;
 
 
SELECT
       empno,ename,dname
  FROM emp RIGHT OUTER JOIN dept
    ON emp.deptno = dept.deptno;
    
SELECT
       empno,ename,dname
  FROM dept LEFT OUTER JOIN emp
    ON emp.deptno = dept.deptno;
    
--사원집합에는 30번까지 다 있고, 부서집합은 30~40까지 다 있어서
--FULL OUTER JOIN은 의미가 없다   
SELECT
       empno,ename,dname
  FROM dept FULL OUTER JOIN emp
    ON emp.deptno = dept.deptno;

SELF JOIN

• 하나의 테이블에서 조인이 발생
• 나 자신과 1:1 또는 1:n 관계일 때 발생

-- self join
SELECT
       a.ename,b.ename as "매니저"
  FROM emp a,emp b
 WHERE a.empno = b.mgr;

CROSS JOIN

• 카타시안 곱과 같다 생각하면 됨

SELECT
       *
  FROM emp CROSS JOIN dept;

연습문제

temp와 tdept를 이용하여 다음 컬럼을 보여주는 SQL을 만들어 보자.

• 상위부서가 'CA0001'인 부서에 소속된 직원을

1. 사번 2. 성명 3. 부서코드 4. 부서명 5. 상위부서코드 6. 상위부서명 7. 상위부서장코드 8. 상위부서장성명 순서로 보여주면 된다.

SELECT 
       a.emp_id,a.emp_name,b.dept_code,b.dept_name
  FROM temp NATURAL JOIN tdept ;
  
  
SELECT 
       a.emp_id,a.emp_name,b.dept_code,b.dept_name
  FROM temp a,tdept b
 WHERE a.dept_code = b.dept_code;
 
-- 테이블 개수에서 (n-1)한 숫자가 조인 조건의 숫자와 같다
SELECT 
       a.emp_id,a.emp_name,b.dept_code,b.dept_name
      ,c.dept_code as "상위부서코드"
      ,c.dept_name as "상위부서명"
  FROM temp a,tdept b,tdept c
 WHERE a.dept_code = b.dept_code
   AND b.parent_dept = c.dept_code
   AND c.dept_code = 'CA0001';
   
   
SELECT 
       a.emp_id,a.emp_name,b.dept_code,b.dept_name
      ,c.dept_code as "상위부서코드"
      ,c.dept_name as "상위부서명"
      ,c.boss_id as "상위부서장id"
      ,d.emp_name as "상위부서장명"
  FROM temp a,tdept b,tdept c,temp d
 WHERE a.dept_code = b.dept_code
   AND b.parent_dept = c.dept_code
   AND c.boss_id = d.emp_id
   AND c.dept_code = 'CA0001';

연습문제

• 각 사번의 성명, 이름, salary, 연봉하한금액, 연봉상한금액을 보고자 한다. temp와 emp_level을 조인하여 결과를 보여주되, 연봉의 상하한이 등록되어 있지 않은 '수습' 사원은 성명, 이름, salary 까지만이라도 나올 수 있도록 쿼리를 작성하시오.

프로시저

• 실행방법:
  SQL> set serveroutput on;
  SQL> exec proc_hap; (exec 파일명;)

<예외처리 테스트>
create or replace procedure proc_exception1
is
    n_i number(5);
begin
    n_i :=0;
    n_i :='김유신';
    exception
      when invalid_number then
        dbms_output.put_line('잘못된 숫자값에 대한 에러');
      when value_error then
        dbms_output.put_line('잘못된 데이터값에 대한 에러');
      when others then
        dbms_output.put_line('잘못된 숫자나 데이터값은 아닌 에러');
end;
/

<테스트>
SQL> exec proc_exception1;

<결과>
잘못된 데이터값에 대한 에러

PL/SQL 처리가 정상적으로 완료되었습니다.

<예외처리 테스트2>
create or replace procedure proc_errormsg
is
--변수 선언부
    err_num number;
    err_msg varchar2(300);
    n_i number(5) :=0;
begin
--프로그램 코딩부
    n_i :=120/0;
    exception
      when others then
        err_num:= SQLCODE;
        err_msg:= substr(SQLERRM,1,100);
        dbms_output.put_line('에러코드: '||err_num);
        dbms_output.put_line('에러내용: '||err_msg);
end;
/

<테스트>
SQL> exec proc_exception1;

<결과>
에러코드: -1476
에러내용: ORA-01476: 제수가 0 입니다

PL/SQL 처리가 정상적으로 완료되었습니다.

• DDL: 구조 정의하는 언어(create,alter,drop)
  메모리 세그먼트를 사용하지 않아서 속도가 빠르다
• DML: select(GET),insert(POST),update(PUT),delete(DELETE) - CRUD 8시간 안에 끝내는 걸 원함

--예외처리 강제로 일으키는 
create or replace procedure proc_raise
is
--변수 선언부
    user_excep exception; --사용자정의 예외객체
begin
--프로그램 코딩부
    raise user_excep;
    exception
      when user_excep then
        dbms_output.put_line('Raise를 이용한 사용자 예외처리방법');
      when others then
        dbms_output.put_line('그 외 예외처리');
end;
/
<테스트>
SQL> exec proc_raise;

<결과>
Raise를 이용한 사용자 예외처리방법

PL/SQL 처리가 정상적으로 완료되었습니다.

반복문

CREATE OR REPLACE PROCEDURE proc_loop1(dan in number)
IS
    n_i number(2);
BEGIN
--파라미터에 선언된 변수는 재정의 불가함
    n_i:=0;
    dbms_output.put_line(dan||'단을 출력합니다.');
end;
/

<테스트>
SQL> exec proc_loop1(7);

<출력>
7단을 출력합니다.

CREATE OR REPLACE PROCEDURE proc_loop1(dan in number)
IS
    n_i number(2);
BEGIN
--파라미터에 선언된 변수는 재정의 불가함
    n_i:=1;
    dbms_output.put_line(dan||'단을 출력합니다.');
    loop
        dbms_output.put_line(dan||'*'||n_i||'='||(dan*n_i));
        n_i:= n_i+1;
        if n_i > 9 then
            exit;
        end if;
    end loop;
end;
/

<테스트>
SQL> exec proc_loop1(3);

<결과>
3단을 출력합니다.
3*1=3
3*2=6
3*3=9
3*4=12
3*5=15
3*6=18
3*7=21
3*8=24
3*9=27

--반복문
CREATE OR REPLACE PROCEDURE proc_loop2
IS
    n_i number(2);
    tot number(5);
BEGIN
    n_i:=1;
    tot:=0;
    loop
        if mod(n_i,2)=0 then
            tot := tot + n_i;
        end if;
        n_i:= n_i+1;
        exit when n_i=10;
    end loop;
    dbms_output.put_line('짝수의 합은 '||tot);
end;
/

<테스트>
SQL> exec proc_loop2;

<결과>
짝수의 합은 20

--exit when n_i=10에서 10을 11로 바꾸면
--10까지의 합을 구하므로 '짝수의 합은 30'

연습문제1

사원번호를 입력받아서 그 사원이 속한 부서의 평균 급여보다
많이 갖고 있으면 10%인상을, 적거나 같게 받고 있으면 20% 인상하여 테이블을 업데이터 하는 프로시저를 작성하시오

CREATE OR REPLACE PROCEDURE proc_emp_sal(p_empno in number,msg out varchar2)
IS
    ename varchar2(30):='';
    sal number(7):=0;
    avg_sal number(10,2):=0;
    rate number(5,2):=0;
BEGIN
    select ename,sal into ename,sal
      from emp
     where empno = p_empno;
    select avg(sal) into avg_sal
      from emp
     where deptno = (select deptno from emp where empno = p_empno);
    if sal > avg_sal then
       rate:=1.1;
    else 
       rate:=1.2;
    end if;
    update emp
       set sal = sal * rate
     where empno = p_empno;
    commit;
    msg:= ename||'사원의 '||sal||'급여가 '||rate||'인상분으로 '||sal*rate||'으로 인상되었습니다.'; 
end;
/

<테스트>
SQL> variable msg varchar2(300);
SQL> exec proc_emp_sal(7499, :msg);
SQL> print msg;

<결과>
MSG                
----------------------
ALLEN사원의 1600급여가 1.1인상분으로 1760으로 인상되었습니다.                                 

사용자 정의 Ref Cursor

• 여러 로우 처리 가능

-- 커서 테스트
CREATE OR REPLACE PROCEDURE proc_empcursor(rc_emp out sys_refcursor)
IS
BEGIN
    open rc_emp
    for select empno,ename,sal,hiredate from emp;
end;
/

<테스트>
SQL> variable r_emp refcursor;
SQL> exec proc_empcursor(:r_emp);
SQL> print r_emp;

<결과>
     EMPNO ENAME             SAL HIREDATE
---------- ---------- ---------- --------
      7369 SMITH             800 80/12/17
      7499 ALLEN            1760 81/02/20
      7521 WARD             1250 81/02/22
      7566 JONES            2975 81/04/02
      7654 MARTIN           1250 81/09/28
      7698 BLAKE            2850 81/05/01
      7782 CLARK            2450 81/06/09
      7788 SCOTT            3000 87/04/19
      7839 KING             5000 81/11/17
      7844 TURNER           1500 81/09/08
      7876 ADAMS            1100 87/05/23

     EMPNO ENAME             SAL HIREDATE
---------- ---------- ---------- --------
      7900 JAMES             950 81/12/03
      7902 FORD             3000 81/12/03
      7934 MILLER           1300 82/01/23

14 개의 행이 선택되었습니다.

연습문제2

부서번호를 입력받아서 그 부서의 평균급여보다 많이 받는 사원은
10%인상을 하고 적게받는 사원은 20%인상을 적용하여 급여정보를 수정하는 프로시저를 작성하시오. (커서를 사용해야 함)

코드를 입력하세요