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

데이터베이스 모델링

• 테이블 설계,컬럼결정,타입을 정함,관계정의

• 논리적설계(개체,Entity,속성(Attribute))

• 물리적설계(테이블,컬럼)-타입이 결정된다

• ER-WIN -> ERD(ENtity Relation Diagran)

• 관계 형태를 그림으로 그리면 PK와 FK 확인할 수 있다

• PK와 FK 통해서 상속관계 증명(주는 쪽과 받는 쪽 결정)

DML

• SELECT
  컬럼명1,컬럼명2,...함수명(컬럼명3)

• FROM
  집합1,집합2,(SELECT문-인라인뷰)

• WHERE
  컬럼명1=값(상수만 있는 게 아니라 SELECT문도 가능하다-서브쿼리)-조건검색만 가능한 게 아니라 조인도 한다

• AND
  컬럼명2=값(SELECT문)-교집합:원소가 줄어든다-경우의 수가 줄어든다(속도가 빨라진다)

• OR
  컬럼명3=값(IN을 대신 썼었다)
  : OR을 쓰게 되면 합집합->경우의 수가 자꾸 증가한다(일량이 늘어나서 잘 안 쓴다)

  오라클에 OR이 있지만 잘 안 쓰는 것처럼, char타입(hello___:고정형)이 있지만 varchar2(hello나머지칸 반납:가변형)타입을 쓴다
  : WHERE char=varchar2 -> false가 나옴 - 논리적 에러. 흐름이 바뀐다

• GROUP BY
  컬럼명1,컬럼명2(단,그룹함수가 아니다/group by절에 없는 컬럼을 썼을 때 문제가 되니 잘 해결해야 한다)

• [[Having]]

• ORDER BY

---

• 타입 문제

--문제:ename과 sum(sal)은 다른 타입이라 같이 쓸 수 없다
SELECT ename,sum(sal)
  FROM emp;
  
--해결방법1:ename 타입 맞춰주기
SELECT max(ename),sum(sal)
  FROM emp;
  
--해결방법2:그룹으로 묶기
SELECT ename,sum(sal)
  FROM emp
 GROUP BY ename;

• GROUP BY

--업무에 대한 복잡도가 높을 수록
--GROUP BY절에 여러 개의 조건이 온다
SELECT deptno,job
  FROM emp
 GROUP BY deptno,job
 ORDER BY deptno;

• sum(decode(...))패턴 : 소계,총계,계 등을 구할 때 사용

SELECT decode(job,'CLERK',sal,null)
  FROM emp;

--sum을 할 때 null은 계산하지 않는다
SELECT sum(decode(job,'CLERK',sal,null))
  FROM emp;
  
SELECT count(empno),count(comm) FROM emp;

연습문제

temp 테이블의 사원이름을 한 행에 사번, 성명을 3명씩 보여주시오

--20명 줄을 세운다
SELECT rownum rno FROM temp;

--이름을 나타내고
SELECT rownum rno,emp_name FROM temp;

--1,2,3이 모두 1이 출력되도록 한다
--왜? 3개 이름은 모두 첫 줄에 출력해야 하니까
SELECT
       rno,ceil(rno/3)cno
  FROM (
        SELECT rownum rno FROM temp      
        );
        
--mod함수 사용
SELECT
       rno,ceil(rno/3)cno,mod(rno,3)mno
  FROM (
        SELECT rownum rno FROM temp      
        );
        
--이름 넣어주기
SELECT
       rno,ceil(rno/3)cno,mod(rno,3)mno
       ,emp_name
  FROM (
        SELECT rownum rno,emp_name FROM temp      
        );
        
--GROUP BY 적용
--왜 7이 나왔을까? 20/3=6.xxxx -> 7
SELECT
       ceil(rno/3)cno
  FROM (
        SELECT rownum rno FROM temp      
        )
 GROUP BY ceil(rno/3) 
 ORDER BY cno;

참고그림

--참고 그림을 만들려면 어떻게 해야 할까?

--1.아래가 아니라 옆으로 컬럼이 늘어나고 있다
--2.패턴이기 때문에 전체적인 틀을 잡아보는 것

SELECT '김길동','홍길동','박문수' FROM dual
UNION ALL
SELECT '정도령','이순신','지문덕' FROM dual

--그림에서 김길동,null,null 만들어주기 위해 decode 사용
DECODE(mod(rno,3),1,'김길동')

SELECT
       ceil(rno/3)cno
      ,max(DECODE(MOD(rno,3),1,MOD(rno,3)))d1
      ,max(DECODE(MOD(rno,3),2,MOD(rno,3)))d2
      ,max(DECODE(MOD(rno,3),0,MOD(rno,3)))d3
  FROM (
        SELECT rownum rno FROM temp      
        )
 GROUP BY ceil(rno/3) 
 ORDER BY cno;

<정답>
 SELECT
       ceil(rno/3)cno
      ,max(DECODE(MOD(rno,3),1,emp_id))||'_'||max(DECODE(MOD(rno,3),1,emp_name))
      ,max(DECODE(MOD(rno,3),2,emp_id))||'_'||max(DECODE(MOD(rno,3),2,emp_name))
      ,max(DECODE(MOD(rno,3),0,emp_id))||'_'||max(DECODE(MOD(rno,3),0,emp_name))
  FROM (
        SELECT rownum rno,emp_id,emp_name FROM temp      
        )
 GROUP BY ceil(rno/3) 
 ORDER BY cno;
 
<3명이 아닌 5명씩 정렬>
SELECT
       ceil(rno/5)cno
      ,max(DECODE(MOD(rno,5),1,emp_id))||'_'||max(DECODE(MOD(rno,5),1,emp_name))
      ,max(DECODE(MOD(rno,5),2,emp_id))||'_'||max(DECODE(MOD(rno,5),2,emp_name))
      ,max(DECODE(MOD(rno,5),3,emp_id))||'_'||max(DECODE(MOD(rno,5),3,emp_name))
      ,max(DECODE(MOD(rno,5),4,emp_id))||'_'||max(DECODE(MOD(rno,5),4,emp_name))
      ,max(DECODE(MOD(rno,5),0,emp_id))||'_'||max(DECODE(MOD(rno,5),0,emp_name))
  FROM (
        SELECT rownum rno,emp_id,emp_name FROM temp      
        )
 GROUP BY ceil(rno/5) 
 ORDER BY cno;

조인

• 2개 이상의 테이블을 가지고 한다
  (→ 집합과 집합은 관계가 있다)
• 관계형태 - 1:1,1:n,n:n
• 주의사항 - n:n은 업무에 대한 정의가 덜 된 경우이므로 조인을 하면 카타시안의 곱이 된다(ERD(최종본)를 보고 확인할 것)

1. natural join

• equai join(옛날 표현)
• 양 쪽에 모두 있는 값만 나온다
• 어느 한 쪽 테이블에만 있는 값은 나오지 않는다(이건 outer join)

SELECT empno,ename,dname
  FROM emp;
  
SELECT empno,ename,dname
  FROM emp,dept; --카타시안의 곱

SELECT empno,ename,dname
  FROM emp NATURAL JOIN dept;
  
--alias 적용
SELECT a.empno,a.ename,b.dname
  FROM emp a NATURAL JOIN dept b;

--이런 표현식도 있었음
SELECT empno,ename,dname
  FROM emp a NATURAL JOIN dept b
    ON a.deptno = b.deptno;

--구 표현식
SELECT empno,ename,dname
  FROM emp dept
 WHERE emp.deptno = dept.deptno;

연습문제
tcom의 work_year = '2001'인 자료와 temp를 사번으로 연결해서 join한 후, comm을 받는 직원의 성명, salary + COMM을 조회해 보시오.

SELECT
       a.emp_id,a.emp_name,100+10
  FROM temp a;

<정답>
SELECT
       a.emp_id,a.emp_name,a.salary+b.comm
  FROM temp a, tcom b
 WHERE a.emp_id = b.emp_id
   AND b.work_year = '2001';

<정답-NATURAL JOIN>
SELECT
       emp_id,emp_name,salary+comm
  FROM temp NATURAL JOIN tcom
 WHERE work_year = '2001';

2. non-equai join

• '= 같다'로 비교하는 것 이외에 모든 것

연습문제

• temp와 emp_level을 이용해 emp_level의 과장 직급의 연봉 상한/하한 범위 내에 드는 직원의 사번과, 성명, 직급, salary를 읽어보자.

SELECT 
       a.emp_id,a.emp_name
  FROM temp a,emp_level b
 WHERE b.lev ='과장';

SELECT count(emp_id) FROM temp WHERE lev='과장';

<정답>
SELECT 
       a.emp_id,a.emp_name,a.lev
  FROM temp a,emp_level b
 WHERE b.lev ='과장'
   AND a.salary between from_sal and to_sal;

3. Outer Join

• 두 개 이상의 테이블 조인 시 한쪽 테이블의 행에 대해 다른 쪽 테이블에 일치하는 행이 없더라도, 다른 쪽 테이블의 행을 Null로 하여 행을 리턴하는 것

SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;

SELECT deptno FROM emp
MINUS
SELECT deptno FROM dept; 

SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp; 

SELECT distinct(deptno) FROM emp;

SELECT empno,deptno,dname
  FROM emp,dept
 WHERE emp.deptno(+)=dept.deptno;

연습문제. 수습사원 내용까지 보여주기

--옛날 outer join 쓰던 방법
--더 보여줘야 하는 쪽에 +를 붙인다
SELECT
       b.emp_id 사번
      ,b.emp_name 성명
      ,b.salary 연봉
      ,a.from_sal 하한
      ,a.from_sal 상한
  FROM emp_level a,temp b
 WHERE a.lev(+) = b.lev;

4. self join

• 다른 테이블과의 조인이 아닌, 나 자신과의 relation이 1:1로 맺어져 있을 때
• ex. 상위부서-하위부서: self join의 대상이 된다
  

연습문제
tdept테이블에 자신의 상위 부서 정보를 관리하고 있다. 이 테이블을 이용하여 부서코드, 부서명, 상위부서코드, 상위부서명을 읽어오는 쿼리를 만들어 보자.

--다 나옴. 다 나오는 게 아니라 상위부서만 나와야 함
SELECT
       dept_name
  FROM tdept;
  
--상위부서 이름이 나와야하는데 그렇지 않다
SELECT
       dept_name,parent_dept
  FROM tdept;
 
--이렇게도 아니다
SELECT
       a.dept_name as "부서명"
      ,b.dept_name as "상위부서명"
  FROM tdept a,tdept b;
  
SELECT
       a.dept_name as "부서명"
      ,b.dept_name as "상위부서명"
  FROM tdept a,tdept b
 WHERE a.parent_dept = b.dept_code;

<정답>
SELECT
       a.dept_code as "부서코드"
      ,a.dept_name as "부서명"
      ,b.dept_code as "상위부서코드"
      ,b.dept_name as "상위부서명"
  FROM tdept a,tdept b
 WHERE a.parent_dept = b.dept_code;
  

PL/SQL이란?

• PL/SQL 은 Oracle’s Procedural Language extension to SQL 의 약자 이다.
• SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며,오라클 자체에 내장되어 있는 Procedure Language 이다.
• DECLARE문을 이용하여 정의되며, 선언문의 사용은 선택 사항 이다.
• PL/SQL 문은 블록 구조로 되어 있고 PL/SQL자신이 컴파일 엔진을 가지고 있다.

PL/SQL의 장점

• PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB로 보내서 처리하므로 수행속도를 향상 시킬수 있다.
• PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다.
• 보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있다.
• VARIABLE, CONSTANT, CURSOR, EXCEPTION을 정의하고, SQL문장과 Procedural 문장에서 사용 한다.
• 단순, 복잡한 데이터 형태의 변수를 선언 한다.
• 테이블의 데이터 구조와 컬럼명에 준하여 동적으로 변수를 선언 할 수 있다.
• EXCEPTION 처리 루틴을 이용하여 Oracle Server Error를 처리 한다.
• 사용자 정의 에러를 선언하고 EXCEPTION 처리 루틴으로 처리 가능 하다.

출처: http://www.gurubee.net/lecture/1039

SQL 실행 - 프롬프트 순서

• sql plus
• 사용자명 / 암호 입력
• SQL> show user
• SQL> conn hr/tiger
• SQL> conn (user 이름);

프로시저(procedure)

--출력하는 프로시저
declare
a number(5);
begin
dbms_output.put_line('hello oracle');
dbms_output.put_line('오늘은'||to_char(sysdate,'YYYY-MM-DD')||'입니다.');
end;
/

--Procedure created
--in-외부로 내보낼 수 있음
--IS-변수로 선언한다 / a number(5):=0;-초기화 / 
create or replace procedure proc_hello(x IN NUMBER,msg OUT VARCHAR2)
IS
a number(5):=0;
begin
dbms_output.put_line('hello oracle');
dbms_output.put_line('오늘은'||to_char(sysdate,'YYYY-MM-DD')||'입니다.');
end;
/

create or replace procedure proc_hello(x IN NUMBER,msg OUT VARCHAR2)
IS
a number(5):=0;
begin
msg:='오늘은'||to_char(sysdate,'YYYY-MM-DD')||'입니다.';
dbms_output.put_line('hello oracle');
dbms_output.put_line('오늘은'||to_char(sysdate,'YYYY-MM-DD')||'입니다.');
end;
/

msg 내용 확인 방법(프롬프트)

• SQL> variable msg varchar2(100);
• SQL> exec proc_hello(1,:msg);
• SQL> print msg;

--1부터 10까지 합 구하는 프로시저
create or replace procedure proc_hap(num in number,msg out varchar2)
is
    n_i number(5):=0;
    n_hap number(5):=0;
begin
    for n_i in 1..10 loop
        n_hap :=n_hap+n_i;
    end loop;
    msg :='1부터 10까지의 합은 '||n_hap||'입니다.';
end;

<프롬프트>
exec proc_hap(1,:msg);
SQL> print msg

--1부터 100까지 세면서 5의 배수의 합
create or replace procedure proc_hap2(msg out varchar2)
is
    n_i number(5):=0;
    n_hap number(5):=0;
begin
  for n_i in 1..100 loop
    if mod(n_i,5)=0 then
       n_hap := n_hap + n_i;
    end if;
  end loop;
  msg :='5의 배수의 합은 '||n_hap||'입니다.';
end;

*else if가 아니라 elsif임

-- 5의 배수일 때 fizz 출력 / 7의 배수일 때 buzz 출력
-- 5,7의 공배수일 때 fizzbuzz 출력
create or replace procedure proc_hap3(msg in varchar2)
is
    n_i number(5):=0;
begin
  for n_i in 1..100 loop
    if mod(n_i,35)=0 then
        dbms_output.put_line('fizzbuzz');
    elsif mod(n_i,5)=0 then
        dbms_output.put_line('fizz');
    elsif mod(n_i,7)=0 then
        dbms_output.put_line('buzz');
    else
        dbms_output.put_line(n_i);
    end if;
  end loop;
end;

begin
    proc_hap3('');
  end;

• end if 밑에 exit when n_i > 50;를 추가하면 51까지 나오고 멈춘다

프로시저 연습문제

-- 3을 입력하면 3단 출력, 5를 입력하면 5단 출력(구구단)
CREATE OR REPLACE PROCEDURE proc_gugudan(dan in number)
IS
    n_i number(2);
BEGIN
    n_i:=0;
        dbms_output.put_line(dan||'단을 출력합니다.');
        for n_i in 1..9 loop
            dbms_output.put_line(dan||'*'||n_i||'='||(dan*n_i));
    end loop;
END ;
/

<test>
begin
    proc_gugudan(5);
  end;
  
<test>
exec proc_gugudan(9);

연습문제
SQL4장(t_worktime)

SELECT * FROM t_worktime;

이 데이터의 작업시간이 짧게 걸리는 시간 순서대로 1부터 15까지의 순위를 매겨서 출력하시오. (그림예시)

--오라클에서 쓰는 함수
SELECT 
       workcd_vc,time_nu
      ,rank()over(order by time_nu)rnk
  FROM t_worktime;

--데이터 3개만 뽑아서 써보자 
SELECT rownum rno,workcd_vc,time_nu FROM t_worktime
 WHERE rownum < 4;
------------------------------------------
SELECT
       *
  FROM (
       SELECT rownum rno,workcd_vc,time_nu FROM t_worktime
       WHERE rownum < 4
       )a,
       (
       SELECT rownum rno,workcd_vc,time_nu FROM t_worktime
       WHERE rownum < 4
       )b;
------------------------------------------
SELECT
       a.workcd_vc,a.time_nu,count(b.workcd_vc)
  FROM (
       SELECT rownum rno,workcd_vc,time_nu FROM t_worktime
       WHERE rownum < 4
       )a,
       (
       SELECT rownum rno,workcd_vc,time_nu FROM t_worktime
       WHERE rownum < 4
       )b
 WHERE a.time_nu >= b.time_nu
 GROUP BY a.workcd_vc,a.time_nu;

프로시저 내용정리

함수와 프로시저

• 사용자 정의 함수는 반드시 반환값이 존재
  (return 예약어 사용)
• 프로시저는 return 예약어를 사용하지 않고 파라미터 자리에 out 속성을 추가하여 자바단으로 내보낼 수 있다

--예시.함수는 반환값이 꼭 있다
SELECT mod(5,2),substr('hello',2),sign(100-500) 
  FROM dual;

• 함수와 프로시저 둘 다 호출해야 실행된다
• 프로시저는 SELECT ... INTO문을 지원하는데, INTO 변수를 주어서 조회된 값을 변수에 담을 수 있다(자바처럼 치환 가능). 단, 한 개 로우에 대해서만 가능하다. 만일 멀티로우에 있는 값을 담으려면 반드시 cursor를 사용해야 한다

--tmp 변수에 담았다
create or replace procedure proc_dept(x in number)
is
    tmp varchar2(20);
begin
    SELECT dname INTO tmp
      FROM dept
     WHERE deptno = x;
     dbms_output.put_line(tmp);
end;

<test>
begin
    proc_dept(10);
  end;
-----------------------  
begin
    proc_dept(20);
  end;

create or replace procedure proc_dept(x in number)
is
    vdname varchar2(20);
    vloc varchar2(30);
begin
    SELECT dname,loc INTO vdname,vloc
      FROM dept
     WHERE deptno = x;
     dbms_output.put_line(vdname||','||vloc);
end;

<test>
begin
    proc_dept(30);
  end;