2024_07_03_수~ 07_04

오늘의 코딩순서 + 코딩 포인트

1. 데이터베이스 보안

1. 데이터베이스 보안

1. 다중 사용자 환경(multi-user environment)

1) 사용자는 자신이 생성한 객체에 대해 소유권을 가지고 데이터에 대한 조작이나 조회 가능
2) 다른 사용자가 소유한 객체는 소유자로부터 접근 권한을 부여받지 않는 접근 불가
3) 다중 사용자 환경에서는 데이터베이스 관리자의 암호를 철저하게 관리

2. 중앙 집중적인 데이터 관리

3. 시스템 보안

1) 데이터베이스 관리자는 사용자 계정, 암호 관리, 사용자별 허용 가능한 디스크공간 할당
2) 시스템 관리 차원에서 데이터베이스 자체에 대한 접근 권한을 관리

4. 데이터 보안

1) 사용자별로 객체를 조작하기 위한 동작 관리
2) 데이터베이스 객체에 대한 접근 권한을 관리

2. 권한(Privilege) 부여

1. 정의

사용자가 데이터베이스 시스템을 관리하거나 객체를 이용할 수 있는 권리

2. 유형

2-1) 시스템 권한: 시스템 차원의 자원 관리나 사용자 스키마 객체 관리 등과 같은 데이터베이스 관리 작업을 할 수 있는 권한
[1] 데이터베이스 관리자가 가지는 시스템 권한

CREATE USER : 사용자를 생성할 수 있는 권한
DROP USER : 사용자를 삭제할 수 있는 권한
DROP ANY TABLE : 임의의 테이블을 삭제할 수 있는 권한
QUERY REWRITE : 함수 기반 인덱스를 생성하기 위한 권한

[2] 일반사용자가 가지는 시스템 권한

CREATE SESSION : DB에 접속할 수 있는 권한
CREATE TABLE : 사용자 스키마에서 테이블을 생성할 수 있는 권한
CREATE SEQUENCE : 사용자 스키마에서 시퀀스를 생성할 수 있는 권한
CREATE VIEW : 사용자 스키마에서 뷰를 생성할 수 있는 권한
CREATE PROCEDURE : 사용자 스키마에서 프로시저, 함수, 패키지를 생성할 수 있는 권한

2-2) 객체 권한: 테이블, 뷰, 시퀀스, 함수 등과 같은 객체를 조작할 수 있는 권한

실습) Admin 사용자 생성 /권한 부여

1. session 권한 부여1 --but, 접속권한만 주어짐

-- 1-1. USER 생성
CREATE USER usertest01 IDENTIFIED BY tiger;
--위의 생성 후 새 접속>>usertest01로 이름과 아이디를 만들고 테스트를 하면 권한없음 오류가 뜸

GRANT CREATE session to usertest01;
-- 다시 새 접속후 usertest01로 이름 아이디 비번 tiger하고나서 테스트하면 성공>>접속하기

CREATE TABLE "BONUS"
    ("ENABLE" VARCHAR2(10 BYTE),
    "JOB" VARCHAR2(9 BYTE),
    "SAL" NUMBER,
    "COMM"NUMBER
    );		--접속권한만 있어서 TBL생성불가

2. session 권한 부여2 -- 접속권한, 생성권한 주어짐 주어짐, 그래도 TBL생성불가

-- 1-2. USER 생성
CREATE USER usertest02 IDENTIFIED BY tiger;

GRANT CREATE session , CREATE table , CREATE VIEW to usertest02;

CREATE TABLE sampleTBL(
    memo varchar2(50)
);		--위의 명령어에도 불구하고 TBL 생성 불가

3. 현장에서 DBA가 개발자 권한 부여: CONNECT와 RESOURCE

-- 1-3. USER 생성
CREATE USER usertest03 IDENTIFIED BY tiger;

GRANT CONNECT , RESOURCE to usertest03;

CREATE TABLE sampleTBL(
    memo varchar2(50)
);	-- 드디어 TBL 생성 성공!
--최소한 CONNECT와 RESOURCE라도 주어야 생성이 가능

↳ ∴ 최소한 CONNECT와 RESOURCE라도 주어야 생성이 가능

4. 권한 부여와 회수

CREATE USER usertest04 IDENTIFIED BY tiger;

GRANT DBA to usertest04;    --나중에 sysnonym 권한을 주기 위해

REVOKE DBA  FROM usertest04;    -- DBA 권한 회수

GRANT CONNECT , RESOURCE to usertest04;     -- 다시 기본 권한만 주기

↓↓↓ CONNECT와 RESOURCE를 줘서 웬만한 것은 다 생성 가능

CREATE TABLE sampleTBL4(
    memo varchar2(50)
);

↓↓↓

5.scott 에서 권한 주기

--1. scott의 student TBL의 READ 권한을 usertest04에게 주겠다
GRANT SELECT ON scott.student TO usertest04;

문제2-1) system에 있는 systemTBL에 Read 권한 usertest04 주세요

GRANT SELECT ON systemTBL TO usertest04 WITH GRANT OPTION;

권한 부여는 되지만 번거로움

↓↓↓번거로움 해결방법: SYNONYM

CREATE PUBLIC SYNONYM pub_system FOR systemTBL; -- SYNONYM: 공용동의어

INSERT INTO privateTBL values('7월 푸름');
INSERT INTO privateTBL values('결실을 맺으리라');

3. synonym (동의어)

1. 정의

하나의 객체에 대해 다른 이름을 정의하는 방법

2. 동의어와 별명(Alias) 차이점

: 동의어는 데이터베이스 전체에서 사용, 별명은 해당 SQL 명령문에서만 사용

3. 동의어의 종류

1) 전용 동의어(private synonym) : 객체에 대한 접근 권한을 부여 받은 사용자가 정의한 동의어로 해당 사용자만 사용

CREATE TABLE privateTBL(
    memo varchar2(50)
    );

↓↓↓

INSERT INTO privateTBL values('7월 푸름');
INSERT INTO privateTBL values('결실을 맺으리라');

SELECT * FROM privateTBL;	-- 나는 스스로 조회가능

문제 3-1. system에 있는 privateTBL Read 권한 usertest04 주세요

GRANT SELECT ON privateTBL To usertest04 WITH GRANT OPTION;

SELECT * FROM sampleTBL4;

--- X: 권한없음
SELECT * FROM scott.emp;

--- OK--> scott이 권한을 할당해주면 가능함
SELECT * FROM scott.student;

-- X: SELECT 권한만 줬으므로 UPDATE 불가능
UPDATE  scott.student
SET     name = '김춘추'
WHERE   studno = 30102
;

2) 공용 동의어(public sysnonym) : 권한을 주는 사용자가 정의한 동의어로 누구나 사용, DBA 권한을 가진 사용자만 생성 (예 : 데이터 딕셔너리)

SELECT * FROM system.systemTBL;	-- 공용동의어를 주지않으면 이것밖에 쓸 수 없음

↓↓↓ 권한 부여 했지만 번거로움 ==> 공용동의어 사용

SELECT * FROM systemTBL;

GRANT SELECT ON systemTBL RO uawerwar04 WITH GRANT OPTION;
-- system에 있는 systemTBL에 Read권한 usertest04 주기

CREATE PUBLIC SYNONYM systemTBL FOR systemTBL;	-- 현장용

-- OK: TBL을 만들수는 없지만 CONNECT는 활성화 되어 읽는 작업은 할 수 있다
SELECT * FROM scott.emp;

--획득한 권한을 재할당하기
GRANT SELECT on scott.emp TO usertest04 WITH GRANT OPTION;
--WITH GRANT OPTION: 현 SELECT 권한 부여 개발자 권한부여
-- WITH GRANT OPTION 주지 않으면 재할당 불가

--권한을 받아 TBL을 보는 것은 가능
SELECT * FROM scott.stud_101;

--하지만 권한을 다시 재할당할 권한은 없음***
GRANT SELECT on scott.stud_101 TO usertest04;

2.PL/SQL

1. 개념

Oracle에서 지원하는 프로그래밍 언어의 특성을 수용한 SQL의 확장
PL/SQL Block내에서 SQL의 DML(데이터 조작어)문과 QUERY(검색어)문, 그리고 절차형 언어(IF,LOOP)등을 사용하여 절차적으로 프로그래밍을 가능하게 한 강력한 트랜잭션(Transaction, 7/1 일지 참고) 언어

2. 장점

: SQL에서는 사용할 수 없는 절차적 프로그래밍 기능을 가지고 있음, Java와 비슷함

프로그램 개발의 모듈화
- Block 내에서 논리적으로 관련된 문장들을 기술함
- 강력한 프로그램을 작성하기 위해 Block내에 Sub Block들을 포함함
- 복잡한 프로그램을 의미있고 잘 정의된 작은 Block들로 분해
변수선언
- 변수, 상수 등을 선언하여 SQL과 절차형 언어에서 사용함
- 단일형(Scalar 데이터 타입과 복합형(Composite) 데이터 타입을 선언함
- 테이블과 칼럼의 데이터 타입을 기반으로 하는 유동적인 변수를 선언함
절차형 언어의 사용
- IF문을 사용하여 조건에 따라 일련의 문장을 실행함
- LOOP문을 사용하여 일련의 문장을 반복적으로 실행함
- Explicit Cursor를 사용하여 여러 행을 검색함
에러처리
- Exception 처리 루틴을 사용하여 Oracle 서버 에러를 처리함
- 사용자 정의 에러를 선언하고 Exception 처리 루틴에서 처리함
이식성
- PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 호스트로도 프로그램을 옮길 수 있음
성능 향상
- PL/SQL은 응용 프로그램의 성능을 향상시킴
- PL/SQL은 여러 SQL 문장을 Block으로 묶고 한번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있음tip

TIP) Java의 특징

높은 이식성: 서로 다른 실행 환경의 시스템 간에 프로그램을 옮겨 실행할 수 있음
객체지향 언어(OOP, Object Oriented Programming)
: 객체 지향적 프로그래밍 언어로서 캡슐화, 상속, 다형성이 잘 적용된 언어임
자동적인 메모리 관리가 가능함
: Java는 메모리를 직접 관리함으로서, 객체를 생성할 떄 자동으로 메모리 영역을 찾아 할당하고 사용이 끝나면 Garbage Collector를 실행해 자동으로 사용하지 않는 객체를 제거해줌
Garbage Collector가 메모리 관리를 대행해주기 때문에 Java 프로세스가 한정된 메모리를 효율적으로 사용할 수 있게 해주고, 개발자 입장에서 메모리 관리 및 메모리 누수문제에 대해 관리하지 않아도 되어 오롯이 개발에 집중할 수 있게 해줌
보안에 강함
: 원래부터 네트워크 분산처리환경에서 사용하기 위해 디자인된 언어인만큼, 네트워크 환경은 다른 환경보다 보안의 측면이 강조되어 있기 때문에 JVM 위에서 실행되어 높은 보안성을 가짐
멀티스레드를 쉽게 구현할 수 있음
: 스레드 생성 및 제어와 관련된 라이브러리 API를 제공하기 때문에 실행되는 운영체제와 관련없이 멀티 스레드를 쉽게 구현 가능함
풍부한 API가 있으며 개발자에게 다양한 기능을 제공하는 방대한 오픈 소스 라이브러리가 있음
: 해당 기능을 사용하면 데이터베이스 연결, XML 처리 등과 같은 일반적인 작업을 더 쉽게 수행할 수 있음

3. 작성요령

1) 기본 Block

DECALRE		-- 선언부
BEGIN		-- 선언부, 실행부
EXCEPTION	-- 실행부, 에러처리부
END			-- 에러처리부
;

부분	설명	포함
선언부	실행부에서 참조할 모든 변수, 상수, CURSOR, EXCEPTION을 선언	옵션
실행부	데이터베이스의 데이트를 처리할 SQL문과 블럭안의 데이터를 처리할 PL/SQL문을 기술	필수
에러처리부	실행부에서 에러와 비정상적인 조건이 발생했을 때 수행될 문장을 기술	옵션

2) 작성요령

PL/SQL 블럭내에서는 한 문장이 종료할 때마다 세미콜론(;)을 쓴다
END 뒤에 ;을 사용하여 하나의 Block이 끝났다는 것을 명시함
PL/SQL 블럭의 작성은 편집기를 통해 파일로 작성할 수도 있고 SQL프롬프트에서 바로 작성할 수도 있음
SQL*Plus

4. PL/SQL 프로그램의 종류

1. FUNCTION(함수)

각 프로세스를 수행하기 위해 필요한 기능들 ex) ID나 PW를 체크하는 기능들
반환되는 변수가 하나임
클라이언트에서 실행되기 때문에 PROCEDURE보다 느림

예제) 특정한 수에 세금을 7%로 계산하는 Function을 작성

CREATE OR REPLACE FUNCTION tax
    (p_num IN number)
RETURN  number
IS
    v_tax number;
BEGIN        
    v_tax := p_num * 0.07;
    RETURN(v_tax);
END;

SELECT  tax(100)
FROM    dual
;

2. PROCEDURE

어떤 업무를 수행하기 위한 절차를 뜻함 ex) 쇼핑몰의 로그인, 배송지, 결제창 등을 절차적으로 기술해 놓은 목록
Java의 메소드와 비슷하다고 생각하면 됨
반환되는 변수가 상황에 따라 여러개, 복잡한 QUERY 문제를 묶어서 처리할 때 사용
일련의 쿼리를 하나의 함수로 실행할 수 있는 쿼리의 집합
장점
1. 하나의 요청으로 여러 SQL문을 실행할 수 있음
2. 서버에서 실행되기 때문에 속도가 빠름
3. 여러개의 리턴값을 보낼 수 있음
단점: 재사용성이 좋지 않음

3. FUNCTION과 PROCEDURE의 차이

Procedure는 로직을 기술하여 해당 업무 처리를 직접 하는 특정 작업을 수행하는 용도로 많이 사용되고, Function은 로직을 도와주는 목적으로 특정 계산을 수행하는 용도로 사용됨
Procedure은 select, where문 등에서 사용이 불가능 하지만 Function은 사용 가능함
Procedure은 클라이언트에서 값을 건네받아 서버에서 작업을 한 뒤 클라이언트에게 전달하지만, Function은 클라이언트에서 값을 건네 받고 서버에서 필요한 값을 가져와서 클라이언트에서 작업을 하고 반환함
Procedure는 리턴값을 가질수도 안가질수도 있으며 여러 개의 리턴값을 가질수 있지만,
Function은 리턴값을 반드시 가져야하며 리턴값을 오직 하나만 가질 수 있음
Procedure는 단독으로 문장 구성이 가능하지만, Function은 단독으로 문장 구성이 불가능함

문제 4-1) ⭐⭐⭐⭐⭐(복합문제) (실무에서 많이 사용하는 방법)

1) Procedure Insert_emp
2) Parameter(제약조건: In(안에서만 사용, 바깥으로 던져주지 않겠다)) => p_empno , p_ename , p_job , p_mgr , p_deptno
3) 변수명 v_comm
4) 로직 1) p_job MANAGER면 v_comm(1000)
2) p_job ELSE( MANAGER이 아니면) v_comm(150)
3) emp TBLInsert =) 조건: hiredate를 현재 일자로

CREATE OR REPLACE PROCEDURE Insert_emp
(p_empno IN EMP.EMPNO%TYPE,
 p_ename IN EMP.ENAME%TYPE,
 p_job   IN EMP.JOB%TYPE,
 p_mgr   IN EMP.MGR%TYPE,
 p_sal   IN EMP.SAL%TYPE,
 p_deptno IN EMP.DEPTNO%TYPE
)
IS 
    v_comm EMP.COMM%TYPE;
BEGIN
    IF p_job = 'MANAGER'THEN    
        v_comm := 1000;
    ELSE   
        v_comm := 150;
    END IF;
    INSERT INTO emp(empno , ename , job , mgr , hiredate , sal , comm , deptno)
    VALUES         (p_empno , p_ename , p_job , p_mgr , SYSDATE , p_sal , v_comm , p_deptno);
    COMMIT;
END;

(0704)

문제 4-2) (Function) EMP 테이블에서 사번을 입력받아 해당 사원의 급여에 따른 세금을 구함.
-- 급여가 2000 미만이면 급여의 6%,
-- 급여가 3000 미만이면 8%,
-- 5000 미만이면 10%,
-- 그 이상은 15%로 세금
--- FUNCTION  emp_tax3
-- 1) Parameter : 사번 p_empno
--      변수     :   v_sal(급여)
--                     v_pct(세율)
-- 2) 사번을 가지고 급여를 구함
-- 3) 급여를 가지고 세율 계산
-- 4) 계산 된 값 Return   number

create or replace FUNCTION emp_tax3	--FUNCTION은 반드시 선언을 해줘야함
    (p_empno IN emp.empno%TYPE)    --1) p_:Parameter/여기서는 사번
RETURN number
IS
    v_sal      emp.sal%TYPE;
    v_pct      NUMBER(5,2); -- 앞 숫자는 정수자리, 뒤 숫자는 소수점자리
BEGIN
    --2) 사번을 가지고 급여를 구함
    SELECT      sal
    INTO        v_sal
    FROM        emp
    WHERE       empno = p_empno
    ;
    --3) 급여를 가지고 세율 계산    
    IF      v_sal < 2000        THEN
            v_pct := (v_sal*0.06);
    ELSIF   v_sal < 3000        THEN
            v_pct := (v_sal*0.08);
    ELSIF   v_sal < 5000        THEN
            v_pct := (v_sal*0.10);
    ELSE
            v_pct := (v_sal*0.15);
    END IF;

    RETURN(v_pct);
END emp_tax3
;

SELECT  ename , sal , EMP_TAX3(empno) emp_rate
FROM    emp
;

문제 4-3) Procedure up_emp 실행 결과(현장HW)
-- SQL> EXECUTE up_emp(1200);  -- 사번
-- 결과       : 급여 인상 저장
--               시작문자
--   변수     :   v_job(업무)
--                  v_up세율)

-- 조건 1) job = SALE포함         v_up : 10
--           IF  v_job LIKE 'SALE%' THEN
--     2)    IF  v_job LIKE    MANAGER   v_up : 7
--     3)    NOT                         v_up : 5
--   job에 따른 급여 인상을 수행  sal = sal+sal*v_up/100
-- 확인 : DB -> TBL

>>내가한것

CREATE OR REPLACE PROCEDURE up_emp
    (p_empno IN emp.empno%TYPE,
     p_job   IN emp.job%TYPE,
     p_sal   IN emp.sal%TYPE,
     p_mgr   IN emp.mgr%TYPE
)
IS
    v_job       emp.job%TYPE;
    v_up        NUMBER(5,2);
BEGIN
    SELECT      job
    INTO        v_job
    FROM        emp
    WHERE       empno = p_empno
    ;
    IF      v_job = 'SALESMAN'   THEN
            v_up := (sal+sal*10/100);
    ELSIF   v_job = 'MANAGER'   THEN
            v_up := (sal+sal*7/100);
    ELSE    
            v_up := (sal+sal*5/100);
    END IF;
    INSERT INTO emp(empno , job , sal , mgr)
    VALUES      (p_empno , p_job , p_sal , p_mgr);
    COMMIT;
END 
;

>>정답

CREATE OR REPLACE PROCEDURE up_emp
    (p_empno IN emp.empno%TYPE)
IS
    v_job       emp.job%TYPE;
    v_up        NUMBER(3);
BEGIN
    SELECT      job
    INTO        v_job
    FROM        emp
    WHERE       empno = p_empno
    ;
    
    IF      v_job LIKE 'SALE%'   THEN
            v_up := 10;
    ELSIF   v_job LIKE 'MAN%'   THEN
            v_up := 7;
    ELSE    
            v_up := 5;
    END IF;
    
    UPDATE      emp
    SET         sal = sal+sal*v_up/100
    WHERE       empno = p_empno
    ;
END 
;

>>> 실행방법: 프로시저에서 UP_EMP>>우클릭>>실행>>입력 값에 원하는 값 넣고 확인> 해당 TBL가서 새로고침후 확인

문제 4-4) (현장HW)행동강령 : 부서번호 입력 해당 emp 정보  PROCEDURE
-- SQL> EXECUTE DeptEmpSearch(40);
--  조회화면 :    사번    : 5555
--              이름    : 홍길동

CREATE OR REPLACE PROCEDURE DeptEmpSearch
    (p_deptno IN emp.deptno%TYPE)
IS
    v_empno     emp.empno%TYPE;
    v_ename     emp.ename%TYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT      empno , ename
    INTO        v_empno , v_ename
    FROM        EMP
    WHERE       deptno = p_deptno
    ;
    DBMS_OUTPUT.PUT_LINE('사번 : '||v_empno);
    DBMS_OUTPUT.PUT_LINE('이름 : '||v_ename);
END DeptEmpSearch
;

↓↓↓ ROWTYPE를 이용한 방법

CREATE OR REPLACE PROCEDURE DeptEmpSearch2
    (p_deptno IN emp.deptno%TYPE)
IS
--    v_empno     emp.empno%TYPE;
--    v_ename     emp.ename%TYPE;
    v_emp emp%ROWTYPE;    
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT      *
    INTO        v_emp
    FROM        EMP
    WHERE       deptno = p_deptno
    ;
    DBMS_OUTPUT.PUT_LINE('사번 : '||v_emp.empno);
    DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.ename);
END DeptEmpSearch2
;

Tip) DBMS_OUTPUT이란?

메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스를 제공하는 오라클 패키지
하나의 프러시저, 함수, 트리거 등에 의해 저장된 메시지는 다른 프러시저, 함수, 트리거 등에서 읽어올 수 있음
이 패키지의 PUT 프로시저 및 PUT_LINE 프로시저를 사용하면 다른 트리거, 프로시저 또는 패키지에서 읽을 수 있는 정보를 버퍼에 저장할 수 있고, 별도의 PL/SQL 프로시저 또는 익명 블록에서 GET_LINE 프로시저 및 GET_LINES 프로시저를 호출하여 버퍼링된 정보를 표시할 수 있음

3. Exception

3-1. 정의

PL/SQL에서 발생하는 Error
Exception은 PL/SQL Block의 실행중에 발생하여 Block의 작업을 중단시킴

3-2. Exception이 발생되는 2가지 방법

PL/SQL이 Oracle Server 에러가 발생하면 이와 관련된 Exception이 자동 발생하는 방법
Block에 Raise문을 써서 명시적으로 Exception을 발생시키는 방법
Begin Section에서 Exception이 발생하면 Exception Section의 해당 Exception 처리부로 제어가 넘어감

3-3. Exception 유형

---1. Predefined Exception

NO_DATA_FOUND: 데이터를 반환하지 않은 SELECT문
TOO_MANY_ROWS: 두 개 이상을 반환한 SELECT문
INVALID_CURSOR: 잘못된 CURSOR 연산 발생
ZERO_DIVIDE: 0으로 나누기
DUP_VAL_ON_INDEX: UNIQUE COLUMN에 중복된 값을 입력할 때

---2. Non- Predefined Exception

: Oracle Server Error중 미리 정의되지 않은 Error는 사용자가 DECLARE Section에서 EXCEPTION명을 정의하고 Oracle Server에서 제공하는 Error번호를 사용하여 Error와 연결한 후 Exception Section에서 Error처리 Routine을 기술함

---3. User DefinedEXCEPTION

사용자정의 EXCEPTION은 oracle server error는 아니고, 사용자가 정한 조건이 만족되지 않을 때, Error를 발생시키는 방법
DECLARE Section에서 EXCEPTION명을 정의하고 BEGIN Section에서 RAISE문을 써서 에러를 발생시킴, 그리고EXCEPTION Section에서 에러처리문을 기술함

---4. SQLCODE, SQLERRM

일반적인 Error 처리를 위하여 Oracle에서 제공하는 함수인 SQLCODE, SQLERRM을 활용할 수 있음

문제 4-5) 10 입력했더니 오류발생=> 요구한 row보다 너무 많은 값

-- Multi Row Error --> 실제 인출은 요구된 것보다 많은 수의 행을 추출
    EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR CODE 1 : ' || TO_CHAR(SQLCODE));
        DBMS_OUTPUT.PUT_LINE('ERR CODE 2 : ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);

CREATE OR REPLACE PROCEDURE DeptEmpSearch3
    (p_deptno IN emp.deptno%TYPE)
IS
--    v_empno     emp.empno%TYPE;
--    v_ename     emp.ename%TYPE;
    v_emp emp%ROWTYPE;    
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT      *
    INTO        v_emp
    FROM        EMP
    WHERE       deptno = p_deptno
    ;
    DBMS_OUTPUT.PUT_LINE('사번 : '||v_emp.empno);
    DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.ename);
END DeptEmpSearch3
;	---오류뜸

↓↓↓ Exception 이용하는 법⭐⭐⭐(개발자들이 많이 이용하는 코드 추가)

CREATE OR REPLACE PROCEDURE DeptEmpSearch3
    (p_deptno IN emp.deptno%TYPE)
IS
--    v_empno     emp.empno%TYPE;
--    v_ename     emp.ename%TYPE;
    v_emp emp%ROWTYPE;    
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT      *
    INTO        v_emp
    FROM        EMP
    WHERE       deptno = p_deptno
    ;
    DBMS_OUTPUT.PUT_LINE('사번 : '||v_emp.empno);
    DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.ename);
    
    EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR CODE 1 : '||TO_CHAR(SQLCODE));	--오류코드 반환
        DBMS_OUTPUT.PUT_LINE('ERR CODE 2 : '||SQLCODE);
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : '||SQLERRM);	-- SQLCODE에 해당하는 오류 메시지 반환
                                                            -- Java의 E.GETMESSAGE 같은 것 
        
END DeptEmpSearch3
;

4. CURSOR⭐⭐⭐⭐⭐

1. 정의

Oracle Server는 SQL문을 실행하고 처리한 정보를 저장하기 위해 "Private SQL Area"이라고 하는 작업영역을 이용하는데, 이 영역에 이름을 부여하고 저장된 정보를 처리할 수 있게 해주는데 이를 CURSOR이라고 함
Java의 ResulSet과 비슷함

Tip) ResultSet이란?

SQL 쿼리의 결과를 저장하고 있는 객체
데이터베이스로부터 데이터를 가져올 때 사용되며, 여러 행(row)의 데이터를 포함할 수 있음
저장된 값을 한 행 단위로 불러올 수 있음
한 행에서 값을 가져올 때는 타입을 지정해 불러올 수 있음
SQL은 executeQuery를 통해 Query를 실행하여 ResultSet타입으로 반환을 해주어 결과값을 저장함
select, show 등을 실행할 목적으로 사용됨

2. 종류

1) Implicit (묵시적인) CURSOR

Attribute⭐⭐⭐	설명
SQL%ROWCOUNT	가장 최근의 SQL문에의해처리된Row 수
SQL%FOUND	가장 최근의 SQL문에 의해 처리된 Row의 개수가 한 개 이상이면 True
SQL%NOTFOUND	가장 최근의 SQL문에 의해 처리된 Row의 개수가 없으면 True
SQL%SOPEN	PL/SQL은 Implicit CURSOR를 사용한 후 즉시 닫기 때문에 항상 False

2) Explicit(명시적인) CURSOR

↳ Explicit CURSOR 4단계 명령⭐⭐⭐(면접질문+기사시험)

--     1) DECLARE 단계 : 커서에 이름을 부여하고 커서내에서 수행할 SELECT문을 정의함으로써 CURSOR를 선언
--     2) OPEN 단계 : OPEN문은 참조되는 변수를 연결하고, SELECT문을 실행
--     3) FETCH 단계 : CURSOR로부터 Pointer가 존재하는 Record의 값을 변수에 전달
--     4) CLOSE 단계 : Record의 Active Set을 닫아 주고, 다시 새로운 Active Set을만들어 OPEN할 수 있게 해줌

문제 4-6) EXECUTE 문을 이용해 함수를 실행합니다.
-- SQL>EXECUTE show_emp3(7900);

CREATE OR REPLACE PROCEDURE show_emp3
    (p_empno    IN  emp.empno%TYPE)
IS
    -- 1.DECLARE 단계
    CURSOR  emp_cursor IS
    SELECT  ename , job , sal
    FROM    emp
    WHERE   empno Like P_empno||'%';
    --Like: 쿼리문 WHERE절에 주로 사용되며 부분적으로 일치하는 칼럼을 찾을때 사용됨
    
    v_ename  emp.ename%TYPE;
    v_sal    emp.sal%TYPE;
    v_job    emp.job%TYPE;
    
BEGIN
    -- 2) OPEN단계
    OPEN emp_cursor;
        DBMS_OUTPUT.PUT_LINE('이름    '||'업무'||'급여');     --제목 기재
        DBMS_OUTPUT.PUT_LINE('-----------------------');
    LOOP
    -- 3) FETCH단계 ==> 하나씩 꺼냄
        FETCH emp_cursor INTO v_ename , v_job , v_sal;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_ename||'    '||v_job||' '||v_sal);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(emp_cursor%ROWCOUNT||'개의 행 선택.');
    -- 4) CLOSE단계
    CLOSE emp_cursor;
END;

문제 4-7)(종합문제)Fetch 문의 원리⭐⭐⭐⭐⭐
-- SQL> EXECUTE Cur_sal_Hap (5);
-- CURSOR 문 이용 구현
-- 부서만큼 반복
-- 부서명 : 인사팀
-- 인원수 : 5
-- 급여합 : 5000

CREATE OR REPLACE PROCEDURE Cur_sal_Hap
    (p_deptno IN emp.deptno%TYPE)
IS
-- 1)DECLARE 단계
    CURSOR  dept_sum 
    IS
        SELECT  dname , Count(*)cnt , SUM(sal) sumSal
        FROM    emp e , Dept d
        WHERE   e.deptno = d.deptno
        and     e.deptno LiKE p_deptno||'%';
        GROUP BY dname	--count가 나오니까 GROUP BY필수
        ;
    vdname     dept.dname%TYPE;
    vdeptno    number;
    vsumsal    number;
    
BEGIN
    DBMS_OUTPUT.ENABLE;
    -- 2) OPEN 단계
    OPEN dept_sum;
    LOOP
    -- 3)FETCH 단계
        FETCH       dept_sum INTO vdname , vcnt , vsumSal;
        EXIT WHEN dept_sum%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('부서명 : '||vdname);
        DBMS_OUTPUT.PUT_LINE('인원수 : '||vcnt);
        DBMS_OUTPUT.PUT_LINE('급여합 : '||vsumSal);
    END LOOP;
    -- 4)CLOSE 단계
    CLOSE dept_sum;
END Cur_sal_Hap
;

↓↓↓ FOR문으로 활용하기

: FOR문을 사용하면 커서의 OPEN, FETCH, CLOSE가 자동 발생하므로 따로 기술할 필요가 없고, 레코드 이름도 자동 선언되므로 따로 선언할 필요가 없다.

CREATE OR REPLACE PROCEDURE FORCursor_sal_Hap
IS
-- 1)DECLARE 단계  ==> CURSOR 선언
CURSOR  dept_sum IS
        SELECT  b.dname , COUNT(a.empno)cnt , SUM(a.sal)salary
        FROM    emp a , dept b
        WHERE a.deptno = b.deptno
        GROUP BY b.dname;
BEGIN
    DBMS_OUTPUT.ENABLE;
    --CURSOR을 FOR문에서 실행시킨다 ==> OPEN, FETCH, CLOSE가 자동 발생
    FOR emp_list IN dept_sum LOOP
        DBMS_OUTPUT.PUT_LINE('부서명 : '||emp_list.dname);     --여기까지 하고 다시 루프가 되어 FETCH로 돌아감
        DBMS_OUTPUT.PUT_LINE('인원수 : '||emp_list.cnt);
        DBMS_OUTPUT.PUT_LINE('급여합 : '||emp_list.salary);
    END LOOP;
    
    EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생');
END;

5. 예외처리

: 오라클 PL/SQL은 자주 일어나는 몇가지 예외를 미리 정의해 놓았으며, 이러한 예외는 개발자가 따로 선언할 필요가 없다.
미리 정의된 예외의 종류

NO_DATA_FOUND : SELECT문이 아무런 데이터 행을 반환하지 못할 때
DUP_VAL_ON_INDEX : UNIQUE 제약을 갖는 컬럼에 중복되는 데이터 INSERT 될 때
ZERO_DIVIDE : 0으로 나눌 때
INVALID_CURSOR : 잘못된 커서 연산

문제 5-1)

CREATE OR REPLACE PROCEDURE PreException
    (v_deptno IN emp.deptno%TYPE)
IS
 v_emp emp%ROWTYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    
    SELECT  empno ,         ename ,     deptno
    INTO    v_emp.empno , v_emp.ename , v_emp.deptno
    FROM    emp
    WHERE deptno = v_deptno
    ;
    
    DBMS_OUTPUT.PUT_LINE('사번 : '||v_emp.empno);
    DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.ename);
    DBMS_OUTPUT.PUT_LINE('부서번호 : '||v_emp.deptno);
    EXCEPTION
     WHEN DUP_VAL_ON_INDEX THEN
         DBMS_OUTPUT.PUT_LINE('중복 데이터가 존재합니다.');
         DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러 발생');
     WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS 에러 발생');   --해당 TBL의 ROW보다 많은 수를 입력하면 발생
     WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND 에러 발생');   --해당 TBL에 없는 값을 입력하면 발생
     WHEN OTHERS THEN   -- 나머지 exception 처리
         DBMS_OUTPUT.PUT_LINE('기타 에러 발생');
END;

문제 5-2)

Procedure :  in_emp
----   Action    : emp Insert
----   1. Error 유형
---      1) DUP_VAL_ON_INDEX  :  PreDefined --> Oracle 선언 Error
---      2) User Defind Error :  lowsal_err (최저급여 ->1500)

CREATE OR REPLACE PROCEDURE in_emp
    (p_name    IN  emp.ename%TYPE, --1) DUP_VAL_ON_INDEX    -- 중복된 값을 넣으면 발생하는 오류
     p_sal     IN  emp.sal%TYPE,   --2) 개발자 Defind Error: lowsal_err (최저급여=> 1500)
     p_job     IN  emp.job%TYPE,
     p_deptno  IN  emp.deptno%TYPE
    )
IS
    v_empno    emp.empno%TYPE;
    -- 개발자 Defind Error
    lowsal_err  EXCEPTION;
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT  MAX(empno)+1
    INTO   v_empno
    FROM    emp
    ;
    
    IF  p_sal >= 1500 THEN
        INSERT INTO emp(empno , ename , sal , job , deptno , hiredate)
        VALUES(v_empno , p_name , p_sal , p_job , 10 , SYSDATE);
    ELSE
        RAISE   lowsal_err;
    END IF;
    
    EXCEPTION
        --Oracle PreDefined Error
        WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('중복 데이터 ENAME 존재합니다');
            DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러 발생');
        --개발자 Defined Error
        WHEN lowsal_err THEN
            DBMS_OUTPUT.PUT_LINE('ERROR!!!-지정한 급여가 너무 적습니다. 1500이상으로 다시 입력하세요.');
            
END in_emp;

문제 5-3)

Procedure :  in_emp3
----   Action    : emp Insert
----   1. Error 유형
---      1) DUP_VAL_ON_INDEX  :  PreDefined --> Oracle 선언 Error
---      2) User Defind Error :  highsal_err (최고급여 ->9000 이상 오류 발생)
---   2. 기타조건
---      1) emp.ename은 Unique 제약조건이 걸려 있다고 가정
---      2) parameter : p_name, p_sal, p_job
---      3) PK(empno) : Max 번호 입력
---      3) hiredate     : 시스템 날짜 입력
---      4) emp(empno,ename,sal,job,hiredate)  --> 5 Column입력한다 가정
---      5) DUP_VAL_ON_INDEX ==> 중복 데이터 ename 존재합니다 / DUP_VAL_ON_INDEX 에러발생
---         highsal_err  -->ERROR!!!-지정한 급여가 너무 많습니다. 9000이하으로 다시 입력하세요

CREATE OR REPLACE PROCEDURE in_emp3
    (p_name IN emp.ename%TYPE,
     p_sal  IN emp.sal%TYPE,
     p_job  IN emp.job%TYPE
    )
IS
    v_empno     emp.empno%TYPE;
    highsal_err EXCEPTION;
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT  MAX(empno)+1
    INTO    v_empno
    FROM    emp
    ;
    
    IF  p_sal <= 9000 THEN
        INSERT INTO emp(empno , ename , sal , job , hiredate)
        VALUES(v_empno , p_name , p_sal , p_job , SYSDATE);
    ELSE
        RAISE   highsal_err;
    END IF;
    
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('중복 데이터 ENAME 존재합니다');
            DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX');
        WHEN highsal_err THEN
            DBMS_OUTPUT.PUT_LINE('ERROR!!!-지정한 급여가 너무 많습니다. 9000이하으로 다시 입력하세요');    
END in_emp3;

문제 5-4)
-- 1. PROCEDURE update_empno
-- 2. parameter -> p_empno, p_job
-- 3. 해당 empno에 관련되는 사원들을(Like) job을 사람의 직업을 p_job으로 업데이트
-- 4. Update -> emp 직업
-- 5. 입사일은 현재일자
-- 6. 기본적 EXCEPTION 처리

--FOR문 아닌 버전

>>내가 하던 것(다 못짬)

CREATE OR REPLACE PROCEDURE update_empno
    (p_empno    IN emp.empno%TYPE,
     p_job      IN emp.job%TYPE
    )
IS
    v_empno     emp.empno%TYPE;
    v_job       emp.job%TYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT  empno , job
    INTO    v_empvo , v_job
    FROM    emp
    WHERE   empno = p_empno
    ;
    
    IF      v_job   LIKE 'MAN%' THEN
            v_   :=  
    ELSE    
            v_
    END IF;
    
    UPDATE  emp(job , hiredate)
    VALUES     (v_job , sysdate);

>> 선생님이 짜 주신 것

CREATE OR REPLACE PROCEDURE update_empno3
    (p_empno    IN emp.empno%TYPE,
     p_job      IN emp.job%TYPE
    )
IS
    -- 1.DECLARE 단계
    CURSOR  emp_list
    IS
      SELECT empno
      FROM emp
      WHERE empno LIKE p_empno||'%';
   
    v_empno     emp.empno%TYPE;

BEGIN
    DBMS_OUTPUT.ENABLE;
    -- 2) OPEN 단계
    OPEN   emp_list;

   LOOP
        Fetch   emp_list Into v_empno;
        EXIT WHEN emp_list%NOTFOUND;
        
         --  emp_list커서에 해당하는 사람의 직업을 SALESMAN으로 업데이트 시킵니다.
        UPDATE emp
        SET job = p_job , hiredate = sysdate
        WHERE empno = v_empno
        ;
        
    END LOOP;     
    DBMS_OUTPUT.PUT_LINE('수정 성공');
    EXCEPTION
    WHEN OTHERS THEN
        -- 에러 발생시 에러 메시지 출력
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END update_empno3;

--FOR문 버전

create or replace PROCEDURE update_empno
    (p_empno    IN emp.empno%TYPE,
     p_job      IN emp.job%TYPE
    )
IS
    CURSOR  emp_list
    IS
      SELECT    empno
      FROM      emp
      WHERE     empno LIKE p_empno||'%';

BEGIN
    DBMS_OUTPUT.ENABLE;
    FOR emp_row IN emp_list LOOP
        DBMS_OUTPUT.PUT_LINE('부서명 : '||emp_row.empno);
        --  emp_list커서에 해당하는 사람의 직업을 SALESMAN으로 업데이트 시킵니다
        UPDATE  emp
        SET     job = p_job , hiredate = sysdate
        WHERE   empno = emp_row.empno
        ;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('수정 성공');
    EXCEPTION
    WHEN OTHERS THEN
    -- 에러 발생시 에러 메시지 출력
    DBMS_OUTPUT.PUT_LINE('ERR MESSAGE: '||SQLERRM);
END;

수업교재 P311 ~P

311 312 314 315 317 318 319 321 322 323

327 334 336 337 338 339 348 354

360 370 377 390 392

412 413

현장과제

오늘의 숙제

문제1) (위치: 4-3 다음) -- PROCEDURE Delete_emp

-- SQL> EXECUTE Delete_emp(5555);

-- 사원번호 : 5555

-- 사원이름 : 55

-- 입 사 일 : 81/12/03 -- 데이터 삭제 성공

-- 1. Parameter : 사번 입력

-- 2. 사번 이용해 사원번호 ,사원이름 , 입 사 일 출력

-- 3. 사번 해당하는 데이터 삭제

>>내가 한 것

CREATE OR REPLACE PROCEDURE Delete_emp
    (p_empno IN emp.empno%TYPE)
IS
    v_empno     emp.empno%TYPE;
    v_ename     emp.ename%TYPE;
    v_hiredate  emp.hiredate%TYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT      empno , ename , hiredate
    INTO        v.empno , v.ename , v_hiredate
    FROM        EMP
    WHERE       empno = p_empno
    ;
    DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_empno);
    DBMS_OUTPUT.PUT_LINE('사원이름 : '||v_ename);
    DBMS_OUTPUT.PUT_LINE('입 사 일 : '||v_hiredate);
END Delete_emp
;

>>정답

CREATE OR REPLACE PROCEDURE Delete_emp
    (p_empno IN emp.empno%TYPE)
IS
    v_empno     emp.empno%TYPE;
    v_ename     emp.ename%TYPE;
    v_hiredate  emp.hiredate%TYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT      empno , ename , hiredate
    INTO        v_empno , v_ename , v_hiredate
    FROM        EMP
    WHERE       empno = p_empno
    ;
    DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_empno);
    DBMS_OUTPUT.PUT_LINE('사원이름 : '||v_ename);
    DBMS_OUTPUT.PUT_LINE('입 사 일 : '||v_hiredate);
    -- 삭제쿼리
    DELETE
    FROM    emp
    WHERE   empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('데이터 삭제 성공')
    ;    
END Delete_emp
;

'DB > Oracle' 카테고리의 다른 글

2024_07_08_월 DB(ORACLE) 시험 (0)	2024.07.08
2024_07_05_금~07_08_월 (0)	2024.07.05
2024_07_02_화 (0)	2024.07.02
2024_07_01_월 (0)	2024.07.01
2024_06_28_금 (0)	2024.06.28

코딩일기

2024_07_03_수~ 07_04_목

오늘의 코딩순서 + 코딩 포인트

1. 데이터베이스 보안

1. 데이터베이스 보안

1. 다중 사용자 환경(multi-user environment)

2. 중앙 집중적인 데이터 관리

3. 시스템 보안

4. 데이터 보안

2. 권한(Privilege) 부여

1. 정의

2. 유형

3. synonym (동의어)

1. 정의

2. 동의어와 별명(Alias) 차이점

3. 동의어의 종류

2.PL/SQL

1. 개념

2. 장점

3. 작성요령

4. PL/SQL 프로그램의 종류

3. Exception

4. CURSOR⭐⭐⭐⭐⭐

5. 예외처리

수업교재 P311 ~P

현장과제

오늘의 숙제

'DB > Oracle' 카테고리의 다른 글

티스토리툴바

2024_07_03_수~ 07_04_목

오늘의 코딩순서 + 코딩 포인트

1. 데이터베이스 보안

1. 데이터베이스 보안

1. 다중 사용자 환경(multi-user environment)

2. 중앙 집중적인 데이터 관리

3. 시스템 보안

4. 데이터 보안

2. 권한(Privilege) 부여

1. 정의

2. 유형

3. synonym (동의어)

1. 정의

2. 동의어와 별명(Alias) 차이점

3. 동의어의 종류

2.PL/SQL

1. 개념

2. 장점

3. 작성요령

4. PL/SQL 프로그램의 종류

3. Exception

4. CURSOR⭐⭐⭐⭐⭐

5. 예외처리

수업교재 P311 ~P

현장과제

오늘의 숙제

'DB > Oracle' 카테고리의 다른 글

관련글

티스토리툴바