오늘 정리
프로시저 왜 사용하나? select문 매번 쓰는 것 보다 빠르다, 파라미터 이용할 수 있다. 공유 가능하다.
Join 잘하려면? 테이블에 대한 이해가 먼저 되어야 한다. 어떤 데이터를 조회하고자 하는지, 그 데이터에 어떤 테이블에 있고, 어떤 관계인지 파악하여 구문을 작성한다.
복습
[Join : 두 개 이상의 테이블을 합침]
- 조인 전에 테이블에 대한 설계 이해 필요(by ERD)
- 설계 🏗️: 개념적 → 논리적(Logical / 엔티티, 속성) → 물리적 설계(Physical / 테이블, 컬럼)
- 관계 : 1:1, 1:N, N:M
- 크로스 조인 : 모든 경우의 수(n*m)
- 조건을 어떻게 가져갈 것인가? → natural join/self join/outer join …
- 조건에는 무엇을 쓸 것인가? → PK, FK의 상속관계
- 어떤 상황인가? insert(회원가입), select(로그인), delete(탈퇴), update(수정)…
- ➡️과정(폭포수모델) : 분석(문서수집) → 설계(요구사항정의, DB설계 등) → 개발 → 테스트 → 배포
[프로시저]
- 예제(예외 일으키기) : 예외 발생시 처리하는 프로시저
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;
/ - 예제(loop) : for문 사용이 아닌 반복문 연습 필요 → 무한루프 해결 경험
--1부터 10까지 세면서 짝수의 합을 구하는 프로시저 만들기
--loop, mod(if), end, 출력
CREATE OR REPLACE PROCEDURE proc_loop2
IS
n_i number(2);
hap number(5);
BEGIN
n_i := 1;
hap := 0;
dbms_output.put_line('1부터 10까지 숫자 중 짝수의 합을 출력합니다..');
LOOP
n_i := n_i + 1;
IF mod(n_i,2)=0 THEN
hap := hap + n_i;
END IF;
exit WHEN n_i = 10;
END LOOP;
dbms_output.put_line('1부터 10까지 숫자 중 짝수의 합은 "'||hap||'" 입니다.');
END;
/
-------------------------------------------------------------------------------------
SQL> exec proc_loop2;
1부터 10까지 숫자 중 짝수의 합을 출력합니다..
1부터 10까지 숫자 중 짝수의 합은 "30" 입니다.
PL/SQL 처리가 정상적으로 완료되었습니다.- 심화예제(1-1) : 테이블 select 문과 함께 사용하여 자주 사용하는 조회문을 프로시저로 등록
CREATE OR REPLACE PROCEDURE proc_sal(emp_no IN number, msg OUT varchar2) IS ename varchar2(30) :=''; emp_sal number(20) :=0; avg_sal number(10,2) := 0; rate number(5,2) := 0; BEGIN SELECT ename, sal INTO ename, emp_sal FROM emp WHERE empno = emp_no; SELECT avg(sal) INTO avg_sal FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE empno = emp_no); IF emp_sal >avg_sal THEN rate := 1.1; ELSE rate := 1.2; END IF; UPDATE emp set sal = sal * rate WHERE emp_no = empno; COMMIT; msg := ename||' 사원의 '||emp_sal||' 급여가 '||rate||'인상분으로 '||emp_sal*rate||'로 인상되었습니다.'; END; / ---------------------------------------------------------------------------------- SQL> variable msg varchar2(300); SQL> exec proc_sal(7499, :msg); PL/SQL 처리가 정상적으로 완료되었습니다. SQL> print msg; MSG -------------------------------------------------------------------------------- ALLEN 사원의 1600 급여가 1.1인상분으로 1760로 인상되었습니다.-
프로시저 왜 사용하나❓
select문 매번 쓰는 것 보다 빠르다, 파라미터 이용할 수 있다. 공유 가능하다. -
프로시저는 어디에 사용되나??
자주 사용하는 프로세스를 만들어두어서 사용(예 : 임금인상 검색, 로그인 등) -
인수 타입선언
👉**IN** => 내부 프로그램에 제공 변수이름 IN 데이터타입 변수이름 IN 테이블이름.컴럼명%TYPE := 값; 👉**OUT** => 호출자에게 제공(프로시저 실행 시 매개변수를 전달, 프로시저에서 특정 값 할당) 변수이름 IN 데이터타입; 변수이름 IN 테이블이름.컬럼명%TYPE; 👉**IN OUT** => 입력과 동시에 출력 변수이름 IN 데이터타입; 변수이름 IN 테이블이름.컬럼명%TYPE; -
프로시저 호출
exec 프로시저이름; --인수 없는 경우 exec 프로시저이름(값1, 값2, ...) --인수 있는 경우
-
- 심화예제(1-2)
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); PL/SQL 처리가 정상적으로 완료되었습니다. 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 개의 행이 선택되었습니다.
강의 내용
[Join 실습 준비]
1.ERD 설계
2. toad 계정 생성(tomato)
- SYSTEM 계정 로그인 → 유저 생성 및 권한 주기
--사용자 계정 생성하기
CREATE user tomato IDENTIFIED BY tomato;
--사용자 계정으로 커넥션 허용하기(DCL)
GRANT connect, RESOURCE TO tomato;
--시퀀스 생성 권한 주기
GRANT CREATE SEQUENCE TO tomato;
--테이블 생성 권한 주기(실행계획보는 권한 주기 위해 with 부분 작성 필요
GRANT CREATE TABLE TO tomato WITH ADMIN OPTION;
--뷰 생성 권한 주기
GRANT CREATE VIEW TO tomato;3. ERD 내보내기
- forward engineer → tomato 계정으로 내보내기(영상 참고)
- check
4. 확인하기
- cmd에서 확인하기
select * from tabs; - toad에서 확인하기
- tomato/tomato 로 로그인 → 생성된 테이블 확인
[JOIN 실습]
JOIN? 둘 이상의 테이블을 연결(상속)하여 데이터를 검색하는 방법
- 방법 : natuaral(등가, equal), non-equal, self, outer
- 방식 : nested loop join, hash join 등
OUTER JOIN : 한 편에만 데이터 값이 있는 경우, 조인 측에 (+) 기호 위치
- RIGHT : 왼편에 값이 없을 때
- FULL : 양 편 모두 값이 없을 때
--사번이 없는 사람의 경우 빼고 사번, 사원이름, 부서이름 가져오기
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; - LEFT : 오른편에 값이 없을 때
SELF JOIN : 한 테이블을 스스로 조인이 발생(나 자신과 1:1, 1:n)
- 언제 쓰나? 포함관계, 게층, PK-FK 관계 있을 때 등
SELECT A.ename AS "매니저", b.ename AS "사원"
FROM emp A, emp b
WHERE A.empno = b.mgr
ORDER BY A.ename;CROSS JOIN : 카타시안의 곱과 같음.
join 예시
--temp와 tdept를 이용하여 다음 컬럼을 보여주는 SQL을 만들어 보자.
--상위부서가 'CA0001'인 부서에 소속된 직원을 1.사번, 2.성명, 3.부서코드
--4.부서명, 5.상위부서코드, 6.상위부서명, 7.상위부서장코드, 8.상위부서장성명
--순서로 보여주면 된다.(temp(20) 2, tdept(10) 2번) ,
SELECT *
FROM temp, tdept;
--1~4까지 출력
--옛날방식(natural join)
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;
--natural join으로 명시(AS 사용x, 조건이 눈에 보이지 않음)
SELECT emp_id, emp_name, dept_code, dept_name
FROM temp NATURAL JOIN tdept;
--테이블 갯수에서 n-1한 숫자가 조인조건의 숫자와 같음.
SELECT A.emp_id AS "사번", A.emp_name AS "성명"
, b.dept_code AS "부서코드", b.dept_name AS "부서명"
, 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';
--테이블 갯수에서 n-1한 숫자가 조인조건의 숫자와 같음.
SELECT A.emp_id AS "사번", A.emp_name AS "성명"
, b.dept_code AS "부서코드", b.dept_name AS "부서명"
, C.DEPT_code AS "상위부서코드", c.dept_name AS "상위부서명"
, c.boss_id AS "상위부서장 아이디"
, 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';[SQL 구문
- DDL : 구조정의 언어(Create, Alter, Rename, Drop)
- DML : 조작 언어(Select, Insert, Delete, Update)
- DCL : 권한 언어(Grant, Revoke, Rollback, Commit)
강의 마무리
- 업무에서 활용되는 테이블 쿼리 작성 경험 필요(테이블 2~3개 이상의 조인 익숙해지기)
