[Oracle|PL/SQL|Java] 5주차 수업: 2024.03.18

2024.03.18

*인덱스

-인덱스는 데이터 검색을 빨리 하기 위해 사용

-데이터의 양이 별로 없거나 데이터값이 몇 종류 안 되어 선택도가 높으면 인덱스가 없는 것이 더 빠를 수 있음

-테이블의 컬럼에 대한 제약 조건을 설정할 때 PRIMARY KEY나 UNIQUE로 설정하면

오라클은 자동으로 이 컬럼에 대해 UNIQUE INDEX를 설정

-인덱스 생성이 필요한 경우

- 열에 광범위한 값이 포함된 경우
- 열에 널 값이 많이 포함된 경우
- WHERE 절 또는 조인 조건에서 하나 이상의 열이 함께 자주 사용되는 경우

-인덱스를 생성하지 않아야 할 경우

- 테이블이 작은 경우
- 열이 질의의 조건으로 자주 사용되지 않는 경우
- 테이블이 자주 갱신되는 경우

-유일한 값을 가지는 컬럼에 인덱스 설정: UNIQUE INDEX

-UNIQUE 제약 조건을 부여하면 자동적으로 설정되므로 실제로 잘 사용하지 않음

CREATE UNIQUE INDEX dname_idx ON dept(dname);

*동의어

-동의어(객체의 다른 이름)를 생성하여 객체 액세스를 단순화

CREATE SYNONYM emp20 FOR emp20_view;

*관리자 계정

-오라클 데이터베이스 생성시 관리자 계정인 SYS와 SYSTEM 계정이 자동으로 생성됨

-SYS 계정은 데이터베이스에서 발생하는 모든 문제를 처리할 수 있는 권한을 가짐.

-SYSTEM 계정은 오라클 데이터베이스를 유지, 보수할 때 사용하는 계정. SYS와 달리 데이터베이스를 생성할 권한을 가지지 않음

*사용자 계정

-사용자 계정은 데이터베이스에 접근하여 데이터를 조작(삽입, 삭제, 수정, 검색)하고 관리하는 계정

*사용자 생성

[오라클 11버전]

Run SQL Command Line 선택

SQL>conn sys/1234(비밀번호) as sysdba;

SQL>create user user002 identified by 1234(비밀번호); ← 계정 생성

SQL>GRANT resource,connect TO user002; ← 접속 권한 부여

*DBA

-DBA는 사용자에게 권한을 부여할 수 있음

-롤은 사용자에게 부여할 수 있는 관련 권한을 하나로 묶어 명명한 그룹으로서

롤을 사용하면 권한 취소 및 유지 관리를 쉽게 수행할 수 있음

[PL/SQL]

*PL/SQL?

-'Procedural language extension to Structured Query Language(SQL)’의 약자

-SQL을 확장한 순차적 처리 언어

-데이터베이스 질의어인 SQL과 일반 프로그래밍 언어의 특성을 결합한 언어

-PL/SQL을 사용하면 조건문이나 반복문, 변수나 상수를 선언해서 사용할 수 있을 뿐만 아니라 SQL도 사용할 수 있음

*PL/SQL의 기본 구조

-PL/SQL의 기본 단위는 블록(block)
1) 선언부(declarative part) : 사용할 변수나 상수를 선언(선언부에만 변수와 상수 선언 가능)
2) 실행부(executable part) : 실제 처리할 로직을 담당하는 부분
3) 예외처리부(exception-building part) : 실행부에서 로직을 처리하던 중 발생할 수 있는 각종 오류들에 대해 처리

프로시저를 만들 때는 의무적으로 들어감

-익명블록으로 DBMS 출력창에 Hello World 출력하기

실행부만 명시한 형태
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World!');
END;

변수 사용
DECLARE
--변수를 선언할 수 있는 선언부
     message VARCHAR2(100);
BEGIN
--실행부에 사용할 변수는 선언부에서 미리 선언되어야 함
    message := 'Hello World!!!';
    DBMS_OUTPUT.PUT_LINE(message);
END;

※SQL에서 =는 비교연산자, 대입연산자는 :=임

변수 선언과 초기화를 동시에
DECLARE
    message VARCHAR2(100) := 'Hell o world?';
BEGIN
    DBMS_OUTPUT.PUT_LINE(message);
END;

-선언부, 실행부, 예외처리부

DECLARE
    counter INTEGER;
BEGIN
    counter := counter + 1;
    IF counter IS NULL THEN 
        DBMS_OUTPUT.PUT_LINE('result: counter is null');
    END IF;
    DBMS_OUTPUT.PUT_LINE(counter);
END;

※INTEGER는 PL/SQL에서 별도로 사용 가능한 자료형

※변수 선언 후 초기화를 해주지 않으면 NULL이 됨. (NULL일 때는 출력이 안됨)

DECLARE
    --변수 선언
    counter INTEGER;
    i INTEGER;
BEGIN
    FOR i IN 1..9 LOOP
        counter := 2 * i;
        DBMS_OUTPUT.PUT_LINE('2 * ' || i || ' = ' || counter);
    END LOOP;
END;

※for문 안에서 i가 초기화 됨

-예외처리부

EXCEPTION WHEN 예외1 THEN 예외처리1
WHEN 예외2 THEN 예외처리2
WHEN OTHERS THEN 예외처리

DECLARE
    --변수 선언
    counter INTEGER;
BEGIN
    --변수 초기화
    counter := 10;
    counter := counter/0;
    EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('errors');
END;

*상수

nYear CONSTANT INTEGER:=30; --(O)
nYear CONSTANT INTEGER; --(X)

*%TYPE

-참조할 테이블에 있는 컬럼의 데이터 타입을 자동으로 가져옴

nSal emp.sal%TYPE;

*%ROWTYPE

-%TYPE은 하나의 값에 대해 적용되지만, %ROWTYPE은 하나 이상의 값에 적용됨

콜렉션

*VARRAY
-VARRAY는 VARIABLE ARRAY의 약자로 고정 길이(Fixed Number)를 가진 배열

-변수 배열 타입을 먼저 생성해줘야 함

DECLARE
    TYPE VARRAY_TEST IS VARRAY(3) OF INTEGER;
    varray1 VARRAY_TEST; --위에서 선언한 VARRAY_TEST 타입을 사용하는 변수
BEGIN
    varray1 := VARRAY_TEST(10, 20, 30);
    
    DBMS_OUTPUT.PUT_LINE(varray1(1)); --인덱스는 1번부터 시작
    DBMS_OUTPUT.PUT_LINE(varray1(2));
    DBMS_OUTPUT.PUT_LINE(varray1(3));
END;

*중첩테이블

-VARRAY와 흡사하지만 중첩 테이블은 선언 시에 전체 크기를 명시할 필요가 없음

DECLARE
    TYPE NESTED_TEST IS TABLE OF VARCHAR2(10);
    nested1 NESTED_TEST;
BEGIN
    nested1 := NESTED_TEST('A','B','C','D');
    
    DBMS_OUTPUT.PUT_LINE(nested1(1));
    DBMS_OUTPUT.PUT_LINE(nested1(2));
    DBMS_OUTPUT.PUT_LINE(nested1(3));
    DBMS_OUTPUT.PUT_LINE(nested1(4));
END;

*ASSOCIATIVE ARRAY (Index-by Table): 연관배열
-실제로는 배열이 아닌 맵 형태로 키와 값의 쌍으로 구성된 콜렉션

-하나의 키는 하나의 값과 연관

DECLARE
    TYPE ASSOC_ARRAY_NUM_TYPE IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    --NUMBER는 value의 타입, PLS_INTEGER는 key의 타입
    assoc1 ASSOC_ARRAY_NUM_TYPE;
BEGIN
    --키와 값 저장
    --키는 3, 값은 33
    assoc1(3) := 33; 
    
    DBMS_OUTPUT.PUT_LINE(assoc1(3));
END;

*레코드

-데이터를 테이블에 적용하기 쉽도록 고안된 구조

-테이블의 컬럼들이 서로 다른 유형의 데이터 타입으로 구성되듯이

레코드는 각각의 필드(레코드에서는 요소란 말 대신 필드란 용어를 사용)들이 각기 다른 데이터 타입을 가질 수 있음

DECLARE
    --TYPE으로 선언한 레코드
    TYPE RECORD1 IS RECORD(deptno NUMBER NOT NULL := 50,
                           dname VARCHAR2(14),
                           loc VARCHAR2(13));
    --위에 선언한 RECORD1을 받은 변수 선언
    rec1 RECORD1;
BEGIN
    --RECORD1 타입의 rec1의 dname 필드에 값 할당
     rec1.dname := 'RECORD';
     rec1.loc := 'SEOUL';
     
     --rec1 레코드 값을 dept 테이블에 insert
    INSERT INTO dept VALUES rec1;
    COMMIT;
    
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
END;

PL/SQL 문장과 커서

*IF문

DECLARE
    grade CHAR(1);
BEGIN
    --변수의 초기화
    grade := 'B';
    
    IF grade = 'A' THEN
        DBMS_OUTPUT.PUT_LINE('Excellent');
    ELSIF grade = 'B' THEN
        DBMS_OUTPUT.PUT_LINE('Good');
    ELSIF grade = 'C' THEN
        DBMS_OUTPUT.PUT_LINE('Fair');
    ELSIF grade = 'D' THEN
        DBMS_OUTPUT.PUT_LINE('Poor');
    END IF;
END;

*CASE문

DECLARE
    grade CHAR(1);
BEGIN
    grade := 'C';
    
    CASE grade 
    WHEN 'A' THEN
        DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN
        DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'C' THEN
        DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN 'D' THEN
        DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Not Found');
    END CASE;
END;

*LOOP문

-특정 조건에 도달하기 전까지 무한반복

-IF문으로 조건체크

DECLARE
    --변수 선언
    test_number INTEGER;
    result_number INTEGER;
BEGIN
    test_number := 1;
    
    LOOP
        result_number := 2 * test_number; 
        IF result_number > 20 THEN
            EXIT;
        ELSE
            DBMS_OUTPUT.PUT_LINE(result_number);
        END IF;
        test_number := test_number + 1;
    END LOOP;
    --loop 블럭을 빠져나오면 아래 코드를 실행함
    DBMS_OUTPUT.PUT_LINE('프로그램 끝');
END;

-EXIT WHEN 형태로 조건체크

DECLARE
    test_number INTEGER;
    result_num INTEGER;
BEGIN
    test_number := 1;
    LOOP 
        result_num := 2 * test_number;
        
        EXIT WHEN result_num > 20;
        
        DBMS_OUTPUT.PUT_LINE(result_num);
        test_number := test_number + 1;
    END LOOP;
END;

*WHILE-LOOP문

DECLARE
    test_number INTEGER;
    result_num INTEGER;
BEGIN
    test_number := 1;
    result_num := 0;
    
    WHILE result_num < 20 LOOP --여기서 result_num 값을 사용해야 하기 때문에 미리 초기화
        result_num := test_number * 2;
        DBMS_OUTPUT.PUT_LINE(result_num);
        test_number := test_number + 1;
    END LOOP;
END;

*FOR-LOOP문

DECLARE
    test_number INTEGER;
    result_num INTEGER;
BEGIN
    FOR test_number IN 1..10 LOOP
        result_num := test_number * 2;
        DBMS_OUTPUT.PUT_LINE(result_num);
    END LOOP;
END;

*FOR-LOOP문 REVERSE

DECLARE
    test_number INTEGER;
    result_num INTEGER;
BEGIN
    FOR test_number IN REVERSE 1..10 LOOP
        result_num := test_number * 2;
        DBMS_OUTPUT.PUT_LINE(result_num);
    END LOOP;
END;

*커서(CURSOR)

-쿼리에 의해 반환되는 결과는 메모리 상에 위치하게 되는데

PL/SQL에서는 바로 커서(CURSOR)를 사용하여 이 결과집합에 접근할 수 있음

DECLARE
        --커서 선언: 커서에 이름을 주고, 이 커서가 접근하려는 쿼리를 정의 
        CURSOR emp_crs IS
        SELECT empno
        FROM emp
        WHERE deptno=10;
        
        emp_no emp.empno%TYPE;
BEGIN
    --커서 열기: 커서로 정의된 쿼리를 실행하는 역할
    OPEN emp_crs;
    LOOP
        FETCH emp_crs INTO emp_no;
        --%NOTFOUND 커서에서만 사용 가능한 속성. 더 이상 패치(할당)할 로우가 없음을 의미
        EXIT WHEN emp_crs%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp_no);
    END LOOP;
    
    --커서 닫기: 패치 작업이 끝나면 사용된 커서를 닫는다
    CLOSE emp_crs;
END;

*커서의 속성

속성	정의
%FOUND	PL/SQL 코드가 마지막으로 얻은 커서의 결과 SET에 레코드가 있다면 참
%NOTFOUND	%FOUND 연산자와 반대의 의미
%ROWCOUNT	커서에서 얻은 레코드의 수를 반환
%ISOPEN	커서가 열렸고, 아직 닫히지 않은 상태라면 참

*PL/SQL 서브프로그램

-PL/SQL 서브프로그램은 파라미터와 고유의 이름을 가진 PL/SQL 블록

-데이터베이스 객체로 존재(저장되어 필요할 때마다 호출 가능)

-서브프로그램에는 내장 프로시저(stored procedure)와 함수(function)가 있음

*함수

-함수는 반드시 반환하는 데이터가 있어야 함

-반환 데이터가 없는 것은 프로시저

-함수 구문

CREATE OR REPLACE FUNCTION 함수명(파라미터1 데이터타입,
                                  파라미터2 데이터타입,
                                  …)
   RETURN 데이터타입 IS [AS]
     변수 선언…;
BEGIN
처리내용…;
RETURN 리턴값;
END;

-입력받은 값으로부터 10%의 세율을 얻는 함수

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER) --IN은 생략 가능
    RETURN NUMBER IS
BEGIN
    RETURN p_value * 0.1;
END;

-함수의 호출

SELECT TAX(100) FROM dual;
SELECT empno, ename, sal, TAX(sal) tax FROM emp;

-급여와 커미션을 합쳐서 세금 계산

CREATE OR REPLACE FUNCTION tax2(p_sal IN emp.sal%TYPE, p_comm emp.comm%TYPE)
    RETURN NUMBER IS
BEGIN
    RETURN (p_sal+NVL(p_comm, 0)) * 0.1;
END;

-조건체크가 있는 함수

CREATE OR REPLACE FUNCTION tax3(p_sal IN emp.sal%TYPE, p_comm IN emp.comm%TYPE)
    RETURN NUMBER IS
    l_tax NUMBER;
    l_sum NUMBER := p_sal + NVL(p_comm, 0);
BEGIN   
    IF l_sum < 1000 THEN
        l_tax := l_sum * 0.05;
    ELSIF l_sum BETWEEN 1000 AND 2000 THEN
        l_tax := l_sum * 0.1;
    ELSE
        l_tax := l_sum * 0.2;
    END IF;
    RETURN l_tax;
END;

-사원번호를 통해서 급여를 알려주는 함수

CREATE OR REPLACE FUNCTION getsal(emp_no IN emp.empno%TYPE)
    RETURN NUMBER IS
    nSalaries NUMBER(9);
BEGIN
    SELECT sal 
    --결과행이 단일행일 경우 커서를 사용하지 않고 INTO를 사용해 읽어온 값을 변수에 담을 수 있음
    INTO nSalaries 
    FROM emp 
    WHERE empno = emp_no; 
    RETURN nSalaries;
END;


--함수 호출
SELECT GETSAL(7839) FROM dual;

※결과행이 단일행일 경우 커서를 사용하지 않고 INTO를 사용해 읽어온 값을 변수에 담을 수 있음

※인자를 emp 테이블에서 읽어오는 것이 아니기 때문에 FROM dual 이라고 작성

2024.03.19

[실습 문제]

3.emp테이블의 입사일을 입력하면 근무연차를 구하는 함수를 정의하시오. (소수점 절삭, get_info_hiredate)

[IN CLASS]
CREATE OR REPLACE FUNCTION get_info_hiredate(p_hiredate emp.hiredate%TYPE)
    RETURN NUMBER IS
BEGIN
    RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, p_hiredate)/12);
END;

-쿼리에 의해서 반환되는 결과집합을 구하는 것이 아니라 단순히 입력값에 따라 계산된 결과값을 구하는 것이므로 커서를 사용할 필요가 없음.

4.emp테이블을 이용해서 사원번호를 입력하면 해당 사원의 관리자 이름을 구하는 함수를 정의하시오. (get_mgr_name)

-서브쿼리 방식

[IN-CLASS] --서브쿼리
CREATE OR REPLACE FUNCTION get_mgr_name(emp_no emp.empno%TYPE)
    RETURN VARCHAR2 IS
    m_name VARCHAR2(10);
BEGIN
    SELECT ename
    INTO m_name
    FROM emp
    WHERE empno = (SELECT mgr FROM emp 
                   WHERE empno=emp_no);
    RETURN m_name;
END;

-조인 방식

[IN-CLASS] --조인 (내 답변과 동일)
CREATE OR REPLACE FUNCTION get_mgr_name(emp_no emp.empno%TYPE)
    RETURN VARCHAR2 IS
    m_name VARCHAR2(10);
BEGIN
    SELECT m.ename
    INTO m_name
    FROM emp e, emp m
    WHERE e.mgr=m.empno
    AND e.empno=emp_no;
    RETURN m_name;
END;

5.emp테이블을 이용해서 사원번호를 입력하면 급여 등급을 구하는 함수를 정의하시오. (get_sal_grade)

-CASE문 (직접 급여등급 만들기)

[IN-CLASS] --CASE문 (직접 급여등급 만들기)
CREATE OR REPLACE FUNCTION get_sal_grade(emp_no emp.empno%TYPE)
    RETURN CHAR IS
    sgrade CHAR(1);
BEGIN
    SELECT CASE WHEN sal >= 4000 THEN 'A'
                WHEN sal >= 3000 AND sal < 4000 THEN 'B'
                WHEN sal >= 2000 AND sal < 3000 THEN 'C'
                WHEN sal >= 1000 AND sal < 2000 THEN 'D'
                ELSE 'F'
           END grade
    INTO sgrade 
    FROM emp
    WHERE empno=emp_no;
    
    RETURN sgrade;
END;

-조인 (내 답변과 동일)

[IN-CLASS] --조인 (내 답변과 동일)
CREATE OR REPLACE FUNCTION get_sal_grade(emp_no emp.empno%TYPE)
    RETURN NUMBER IS
    sgrade NUMBER;
BEGIN
    SELECT s.grade
    INTO sgrade
    FROM emp e, salgrade s
    WHERE e.sal BETWEEN s.losal AND s.hisal
    AND e.empno=emp_no;
    
    RETURN sgrade;
END;

6.사원번호를 입력하면 근무지를 구하는 함수(find_loc)

-조인

[MY ANSWER] --조인
CREATE OR REPLACE FUNCTION find_loc(emp_no emp.empno%TYPE)
    RETURN VARCHAR2 IS
    location VARCHAR2(14);
BEGIN
    SELECT d.loc
    INTO location
    FROM dept d JOIN emp e
    ON d.deptno=e.deptno
    WHERE e.empno=emp_no;
    
    RETURN location;
END;

-서브쿼리

[MY ANSWER] --서브쿼리
CREATE OR REPLACE FUNCTION find_loc(emp_no emp.empno%TYPE)
    RETURN VARCHAR2 IS
    location VARCHAR2(14);
BEGIN
    SELECT loc
    INTO location
    FROM dept
    WHERE deptno = (SELECT deptno FROM emp
                    WHERE empno= emp_no);
    
    RETURN location;
END;

*생성된 함수 확인하기

-데이터 사전(DATA Dictionary)을 통해 검색
-데이터 사전에 저장된 모든 값은 대문자로 저장되기 때문에 대문자로 검색

SELECT object_name, object_type FROM user_objects
WHERE object_type='FUNCTION';

-작성된 함수의 소스 코드 확인

SELECT text FROM user_source
WHERE type='FUNCTION' AND name='TAX';

*프로시저

-반환하는 데이터가 없기 때문에 반복적으로 데이터의 삽입, 삭제, 갱신(DML문)이 일어날 때 주로 사용

CREATE OR REPLACE PROCEDURE hello_world
IS
    --변수 선언
    message VARCHAR2(100);
BEGIN
    message := 'Hello World!';
    DBMS_OUTPUT.PUT_LINE(message);
END;

CREATE OR REPLACE PROCEDURE hello_oracle(p_message IN VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(p_message);
END;

-인자가 없으면 정의할 때 소괄호를 넣지 않음

*프로시저 실행

EXECUTE HELLO_WORLD;
EXEC HELLO_WORLD;

EXEC HELLO_ORACLE('Hello Oracle!');

*작성된 Stored Procedure 확인

SELECT object_name, object_type
FROM user_objects
WHERE object_type='PROCEDURE';

-작성된 Procedure의 소스코드 확인

SELECT text FROM user_source
WHERE type='PROCEDURE' AND name='HELLO_WORLD';

[예제]

-부서테이블에 부서정보를 입력하는 프로시저를 생성

CREATE OR REPLACE PROCEDURE add_department(p_deptno IN dept.deptno%TYPE, 
                                           p_dname IN dept.dname%TYPE, 
                                           p_loc IN dept.loc%TYPE)
IS
BEGIN
    --파라미터 변수에 입력 받은 값으로 부서(dept)테이블의 각 컬럼에 데이터를 추가 정상적으로 transaction 종료
    INSERT INTO dept VALUES(p_deptno, p_dname, p_loc);
    COMMIT;
    
    EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(p_dname || ' register is failed');
        ROLLBACK;
END;

-SALES 부서에 속한 사원의 정보 보기 (커서를 이용)

CREATE OR REPLACE PROCEDURE emp_info(p_dept dept.dname%TYPE)
IS
    --커서 선언
    CURSOR emp_cur IS
    SELECT empno, ename 
    FROM emp e JOIN dept d
    ON e.deptno=d.deptno
    WHERE dname = UPPER(p_dept);
    
    --변수 선언
    e_empno emp.empno%TYPE;
    e_ename emp.ename%TYPE;
BEGIN
    OPEN emp_cur;
    --커서로부터 데이터 읽기
    LOOP
        FETCH emp_cur INTO e_empno, e_ename;
        EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(e_empno || ', ' || e_ename);
    END LOOP;
    
    CLOSE emp_cur;
END;

EXEC EMP_INFO('sales');

[실습 문제]

-사원번호와 새 업무를 입력하면 emp 테이블의 해당 사원의 업무를 갱신할 수 있는 프로시저를 작성하시오.

CREATE OR REPLACE PROCEDURE update_job(p_empno emp.empno%TYPE, p_job emp.job%TYPE)
IS
BEGIN
    UPDATE emp SET job=UPPER(p_job) WHERE empno=p_empno;
    COMMIT;
    
    EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(p_empno || ' update is failed');
        ROLLBACK;
END;

-동일한 도서가 있는지 점검한 후 동일한 도서가 없으면 삽입하고 동일한 도서가 있으면 가격을 업데이트

[IN-CLASS]
CREATE OR REPLACE PROCEDURE book_info(mybookid book.bookid%TYPE,
                                      mybookname book.bookname%TYPE, 
                                      mypublisher book.publisher%TYPE,
                                      myprice book.price%TYPE)
IS
    mycount NUMBER;
BEGIN
    SELECT COUNT(*) INTO mycount FROM book 
    WHERE bookname = mybookname;
    
    IF mycount!=0 THEN
        --동일한 도서 존재
        UPDATE book SET price = myprice WHERE bookname = mybookname;
    ELSE
        --동일한 도서 미존재
        INSERT INTO book VALUES(mybookid, mybookname, mypublisher, myprice);
    END IF;
    COMMIT;
    
    EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR!!');
        ROLLBACK;
END;

2024.03.20

*패키지

-업무와 관련된 Stored Procedure 및 Stored Function을 관리하고, 이를 패키지 단위로 배포할 때 유용하게 사용

-패키지는 선언부와 본문으로 구분

-선언부 생성

CREATE OR REPLACE PACKAGE employee_pkg AS
    PROCEDURE print_ename(p_empno NUMBER);
    PROCEDURE print_sal(p_empno NUMBER);
END employee_pkg;

-본문 생성

CREATE OR REPLACE PACKAGE BODY employee_pkg AS
    PROCEDURE print_ename(p_empno NUMBER) IS
        e_name emp.ename%TYPE;
    BEGIN
        SELECT ename
        INTO e_name
        FROM emp
        WHERE empno=p_empno;
        DBMS_OUTPUT.PUT_LINE(e_name);
        EXCEPTION WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Invalid Employee Number');
    END print_ename;
    
    PROCEDURE print_sal(p_empno NUMBER) IS
        e_sal emp.sal%TYPE;
    BEGIN
        SELECT sal
        INTO e_sal
        FROM emp
        WHERE empno = p_empno;
        DBMS_OUTPUT.PUT_LINE(e_sal);
        EXCEPTION WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Invalid Employee Number');
    END print_sal;
END employee_pkg;

*트리거(Trigger)

-트리거는 데이터의 변경(INSERT,DELETE,UPDATE)문이 실행될 때 자동으로 같이 실행되는 프로시저
-오라클은 기본적으로 실행 전 BEFORE 과 실행 후 AFTER 트리거를 지원

-트리거를 생성한 후 dept 테이블에 데이터를 추가하면 등록된 트리거가 동작하면서
'dept 테이블에 정상적으로 데이터가 추가되었습니다.'를 출력함

CREATE OR REPLACE TRIGGER print_message
AFTER INSERT ON dept
BEGIN
    DBMS_OUTPUT.PUT_LINE('dept 테이블에 정상적으로 데이터가 추가되었습니다.');
END;

CREATE OR REPLACE TRIGGER afterinsertbook
--FOR EACH ROW는 매번 추가되는 행의 수만큼 TRIGGER가 발생함
AFTER INSERT ON book FOR EACH ROW
BEGIN
    INSERT INTO book_log
    -- :new.컬럼이름의 형식으로 추가, 수정할 때 해당 컬럼의 새로운 값을 저장함
    VALUES (:new.bookid, :new.bookname, :new.publisher, :new.price);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('삽입한 데이터를 book_log 테이블에 백업했습니다.');
    
    EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERRORS!');
        ROLLBACK;
END;

※FOR EACH ROW는 매번 추가되는 행의 수만큼 TRIGGER가 발생함

※:new.컬럼이름의 형식으로 추가, 수정할 때 해당 컬럼의 새로운 값을 저장함

[Java - Oracle 연동]

*JDBC(Java Database Connectivity)

-자바를 이용하여 데이터베이스에 접근하여 각종 SQL문을 수행할 수 있도록 제공하는 API

-크게 JDBC 인터페이스와 JDBC 드라이버로 구성

*Java - Oracle 연동 방법

-클라우드에서 ojdbc8.jar(드라이버) 설치 -> 프로젝트에서 new source 생성(libs) -> libs 폴더에 jar 파일 붙여넣기

-프로젝트 우클릭 -> Build Path -> Configure Build Path

-> Libraries -> ModulePath -> Add JARs -> ojdbc8.jar 파일 찾아서 등록

*JDBC를 이용한 데이터베이스 연결 방법

-1단계 : 드라이버를 로드한다.

-2단계 : Connection 객체를 생성한다.

-3단계 : Statement 객체를 생성한다.

-4단계 : SQL문에 결과물이 있다면 ResultSet 객체를 생성한다.

-5단계 : 모든 객체를 닫는다.

*1단계: 오라클 드라이버 로드

public class DriverMain {
	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			System.out.println("오라클 드라이버가 메모리에 정상적으로 로드되었습니다.");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
}

※oracle.jdbc.driver.OracleDriver는 옛날에 사용하던 것

*2단계: Connection 객체 생성 (오라클 접속을 위한 인증)

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

public class ConnectionMain {
	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		
		                 // thin -> 우리가 사용하는 드라이버가 thin 드라이버라는 의미
		                 // @localhost -> 원격 환경이면 @localhost 대신 @IP주소
		                 // 1521 -> 오라클의 포트
		                 // xe -> 오라클의 식별자
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";
		try {
			//JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
			//JDBC 수행 2단계 : Connection 객체 생성(오라클 접속을 위한 인증)
			Connection conn = DriverManager.getConnection(db_url, db_id, db_password);
			System.out.println("Connection 객체가 생성되었습니다.");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
}

※db_url = "jdbc:oracle:thin:@localhost:1521:xe"

-thin -> 우리가 사용하는 드라이버가 thin 드라이버라는 의미
-@localhost -> 원격 환경이면 @localhost 대신 @IP주소
-1521 -> 오라클의 포트
-xe -> 오라클의 식별자

*3단계: Statement 객체 생성 (우리가 만든 SQL 문장을 전달)

-DDL, DML문 사용 가능

-자바와 연동하여 DDL문을 사용하는 경우는 드묾

*4단계: SQL문 수행

-데이터베이스를 연동할 때는 필수적으로 자원정리를 해주어야 함

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

public class CreateTableMain {
	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			//JDBC 수행 1단계 : 드라이버 로드
			Class.forName(db_driver);
			//JDBC 수행 2단계 : Connection 객체 생성 (오라클 접속을 위한 인증)
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			
			System.out.println("test1 테이블을 생성합니다.");
			/*
			 * 테이블을 생성하는 SQL문
			 * 접속한 계정에 테이블명이 중복되면 에러가 발생하기 때문에
			 * 동일 계정에서는 한 번만 수행함
			 */
			sql = "CREATE TABLE test1(id VARCHAR2(10), age NUMBER)";
			
			//JDBC 수행 3단계 : Statement 객체 생성
			stmt = conn.createStatement();
			
			//JDBC 수행 4단계 : SQL문을 실행해서 DB에 테이블을 생성
			stmt.executeUpdate(sql);
			
			System.out.println("테이블이 정상적으로 생성되었습니다.");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			//자원정리
			if (stmt != null) {
				try {
					stmt.close();
				} catch(SQLException e) {}
			}
			
			if (conn != null) {
				try {
					conn.close();
				} catch(SQLException e) {}
			}
		}
	}
}

*DML문 실행하기

public class InsertMain {
	public static void main(String[] args) {
		String db_driver = "oracle.jdbc.OracleDriver";
		String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
		String db_id = "user01";
		String db_password = "1234";
		
		Connection conn = null;
		Statement stmt = null;
		String sql = null;
		
		try {
			//JDBC 수행 1단계
			Class.forName(db_driver);
			
			//JDBC 수행 2단계: Connection 객체 생성 (오라클 접속 인증)
			conn = DriverManager.getConnection(db_url, db_id, db_password);
			
			//SQL문 작성
			sql = "INSERT INTO test1(id, age) VALUES('she''g', 30)";
			
			//JDBC 수행 3단계: Statement 객체 생성
			stmt = conn.createStatement();
			
			//JDBC 수행 4단계 : SQL문을 실행해서 하나의 행을 삽입
			//                : 삽입 작업 후 삽입한 행의 개수를 반환
			int count = stmt.executeUpdate(sql);
			System.out.println(count + "개의 행을 추가했습니다.");
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) try { stmt.close();} catch (SQLException e) {}
			if (conn != null) try { conn.close();} catch (SQLException e) {}
		}
	}
}

*테이블에서 데이터 읽어오기

-SQL문을 실행해서 테이블로부터 레코드(행)을 전달받아서 결과집합을 만들고 ResultSet 객체에 담아서 반환

rs = stmt.executeQuery(sql);

-ResultSet에 보관된 결과집합에 접근해서 행단위로 데이터를 추출

while(rs.next()) {
				//컬럼명을 통해서 데이터를 반환
//				System.out.print(rs.getString("id"));
//				System.out.print("\t");
//				System.out.println(rs.getInt("age"));
				
				//컬럼 인덱스를 통해서 데이터를 반환
				System.out.print(rs.getString(1));
				System.out.print("\t");
				System.out.println(rs.getInt(2));
			}

2024.03.21

*PreparedStatement

-데이터를 SQL문에 직접 입력하지 않으므로 보안에 유리

-인자를 '?' 플레이스홀더로 대체한 채로 컴파일

-캐싱한 데이터를 재사용하기 때문에 DB에 부하를 낮추고 속도가 빠름

-특수문자를 사용하는 데에 문제가 없음

->실무상에서는 주로 PreparedStatement만을 사용

*PreparedStatement 객체를 이용하여 테이블에 데이터 삽입하기

public class InsertMain {
	
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		
		try {
			//JDBC 수행 1,2 단계
			conn = DBUtil.getConnection();
			
			//SQL문 만들기
			sql = "INSERT INTO test2(id,name,age,reg_date) VALUES(?,?,?,SYSDATE)";
			
			//JDBC 수행 3단계: PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터를 바인딩
			pstmt.setString(1, "Wendy's"); //1번 ?에 데이터 전달
			pstmt.setString(2, "정웬디"); //2번 ?에 데이터 전달
			pstmt.setInt(3, 18); //3번 ?에 데이터 전달
			
			//JDBC 수행 4단계
			int count = pstmt.executeUpdate(); //prepareStatement()에 sql을 전달했기 때문에 여기에서는 전달하지 않음
			
			System.out.println(count + "개 행을 추가했습니다.");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
}

*DBUtil 클래스

-데이터베이스 연동을 위한 상수 저장

-드라이버 로드, 데이터베이스와 연동하는 반복작업 분리

public class DBUtil {
	private static final String DB_DRIVER = "oracle.jdbc.OracleDriver";
	private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
	private static final String DB_ID = "user01";
	private static final String DB_PASSWORD = "1234";
	
	//Connection 객체를 생성해서 반환
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		//JDBC 수행 1단계: 드라이버 로드
		Class.forName(DB_DRIVER);
		//JDBC 수행 2단계: Connection 객체 생성
		Connection conn = DriverManager.getConnection(DB_URL, DB_ID, DB_PASSWORD);
		
		return conn;
	}
	
	//자원정리
	//PreparedStatement용
	public static void executeClose(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		//rs가 없을 시 null을 넘겨주면 됨
		if (rs!=null) try {rs.close();} catch (SQLException e) {}
		if (pstmt!=null) try {pstmt.close();} catch (SQLException e) {}
		if (conn!=null) try {conn.close();} catch (SQLException e) {}
	}
	
	//CallableStatement용 메서드 오버로딩
	public static void executeClose(ResultSet rs, CallableStatement cstmt, Connection conn) {
		if (rs!=null) try {rs.close();} catch (SQLException e) {}
		if (cstmt!=null) try {cstmt.close();} catch (SQLException e) {}
		if (conn!=null) try {conn.close();} catch (SQLException e) {}
	}
}

*콘솔창에서 입력받아 테이블에 데이터 삽입,수정,삭제하기

-테이블, 기본키로 사용할 시퀀스 생성

CREATE TABLE test3 (
	num NUMBER PRIMARY KEY,
	title VARCHAR2(60) NOT NULL,
	name VARCHAR2(30) NOT NULL,
	memo VARCHAR2(4000) NOT NULL,
	email VARCHAR2(30),
	reg_date DATE NOT NULL
);

CREATE SEQUENCE test3_seq;

-콘솔창에서 입력받은 데이터 삽입하기

public class InsertMain {
	public static void main(String[] args) {
		BufferedReader br = null;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		
		try {
			br = new BufferedReader(new InputStreamReader(System.in));
			System.out.print("제목: ");
			String title = br.readLine();
			System.out.print("이름: ");
			String name = br.readLine();
			System.out.print("메모: ");
			String memo = br.readLine();
			System.out.print("이메일: ");
			String email = br.readLine();
			
			//JDBC 수행 1,2단계
			conn = DBUtil.getConnection();
			
			//SQL문 작성
			sql = "INSERT INTO test3(num, title, name, memo, email, reg_date)"
					+ "VALUES(test3_seq.nextVal, ?, ?, ?, ?, SYSDATE)";
			
			//JDBC 수행 3단계 : PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setString(1, title);
			pstmt.setString(2, name);
			pstmt.setString(3, memo);
			pstmt.setString(4, email);
			
			//JDBC 수행 4단계
			int count = pstmt.executeUpdate();
			
			System.out.println(count + "개 행을 추가했습니다.");
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//자원정리
			DBUtil.executeClose(null, pstmt, conn);
			if (br != null) try {br.close();} catch (IOException e) {}
			//데이터베이스 자원정리는 무조건, BufferedReader 자원정리는 생략되기도 함
		}
	}
}

*테이블 읽어와서 데이터 목록 만들기

//JDBC 4단계 수행
rs = pstmt.executeQuery();
			
System.out.println("번호\t작성자\t등록일\t\t제목");
			
while (rs.next()) {
	System.out.print(rs.getInt("num"));
	System.out.print("\t");
	System.out.print(rs.getString("name"));
	System.out.print("\t");
	System.out.print(rs.getDate("reg_date"));
	System.out.print("\t");
	System.out.print(rs.getString("title"));
	System.out.println();
}

*if문으로 조건체크해서 기본키가 일치하는 데이터 검색

-단일행의 데이터를 읽어올 때는 while문 사용하지 않고 if문 사용

if (rs.next()) {
	System.out.println("번호: " + rs.getInt("num"));
	System.out.println("제목: " + rs.getString("title"));
	System.out.println("작성자: " + rs.getString("name"));
	System.out.println("내용: " + rs.getString("memo"));

	// 이메일 null값 처리
	String email = rs.getString("email");
	if (email == null)
		email = "";

	System.out.println("이메일: " + email);
	System.out.println("등록일자: " + rs.getDate("reg_date"));

} else {
	// 프라이머리키가 올바르지 않을 때
	System.out.println("검색된 데이터가 없습니다.");
}

*검색 작업 (LIKE 사용)

-SQL문 작성

//SQL문 작성
sql = "SELECT * FROM test3 WHERE title LIKE '%' || ? || '%'";

-if문에서 rs.next()를 사용해서 조건체크 할 경우에는 이미 커서가 ResultSet의 첫번째 행을 가리키고 있으므로

while문을 사용하지 않고 do-while문 사용

if (rs.next()) {
	System.out.println("번호\t작성자\t등록일\t\t제목");

	do {
		System.out.print(rs.getInt("num"));
		System.out.print("\t");
		System.out.print(rs.getString("name"));
		System.out.print("\t");
		System.out.print(rs.getDate("reg_date"));
		System.out.print("\t");
		System.out.println(rs.getString("title"));
	} while (rs.next());

} else {
	System.out.println("검색된 데이터가 없습니다.");
}

*CallableStatement

-프로시저 호출 시 사용

-테이블 생성 및 초기 데이터 추가

CREATE TABLE salary(
	name VARCHAR2(10) PRIMARY KEY,
	pay NUMBER NOT NULL
);

INSERT INTO salary VALUES('SMITH', '1000');
INSERT INTO salary VALUES('PETER', '2000');
INSERT INTO salary VALUES('JULIE', '3000');
COMMIT;

-프로시저 작성

CREATE OR REPLACE PROCEDURE adjust(n IN VARCHAR2, rate IN FLOAT) 
IS
	newpay FLOAT;
BEGIN
	SELECT pay INTO newpay FROM salary WHERE name=n;
	newpay := newpay + newpay*rate;
	UPDATE salary SET pay=newpay WHERE name=n;
	COMMIT;
	
	EXCEPTION WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Error Occurred!');
		ROLLBACK;
END;

-프로시저 호출

//JDBC 수행 1,2단계
conn = DBUtil.getConnection();
		
//프로시저 호출 문장 작성
sql = "{call adjust(?,?)}";
			
//JDBC 수행 3단계 : CallableStatement 객체 생성
cstmt = conn.prepareCall(sql);
//?에 데이터 바인딩
cstmt.setString(1, name);
cstmt.setFloat(2, rate);
			
//JDBC 수행 4단계 : 프로시저 호출
cstmt.executeUpdate();
System.out.println("급여 정보를 수정했습니다.");

-EXECTUTE가 아닌 {call}을 통해 호출

*하나의 트랜잭션에서 복수의 SQL문 처리하기

-하나의 SQL문일 때에는 동작이 정상적으로 작동하는 한 오토커밋함

-여러개의 SQL문을 하나의 작업단위(트랜잭션)으로 처리할 때에는

여러 개의 문장 중 하나만 오류가 발생하더라도 전체를 ROLLBACK해야 하므로 오토커밋 기능을 해제해야 함

-오토커밋 기능은 Connection 객체에 기본값으로 설정되어 있으므로 이를 해제

-정상적으로 작동할 경우 수동으로 커밋하고, 예외가 발생할 경우 try-catch문에서 롤백

public class RightTransaction {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		PreparedStatement pstmt3 = null;
		String sql = null;

		try {
			//복수의 SQL문을 실행할 경우 오토커밋을 해제하고 수작업으로 트랜잭션 처리함
			//JDBC 수행 1,2단계
			conn = DBUtil.getConnection();
			//오토커밋 해제
			conn.setAutoCommit(false);

			sql = "INSERT INTO test1 VALUES ('ANNA', 10)";
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.executeUpdate();

			sql = "INSERT INTO test1 VALUES ('MARY', 20)";
			pstmt2 = conn.prepareStatement(sql);
			pstmt2.executeUpdate();

			//테스트용으로 잘못된 SQL문 작성
			sql = "INSERT INTO test1 VAUES ('JAMES', 30";
			pstmt3 = conn.prepareStatement(sql);
			pstmt3.executeUpdate();

			//정상적으로 작업이 완료되면 commit
			conn.commit();

			System.out.println("작업 완료");
		} catch (Exception e) {
			//예외가 발생할 경우 rollback
			try {
				conn.rollback();
				System.out.println("예외가 발생하여 데이터를 삽입하지 않습니다.");
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
		} finally {
			//자원정리
			//pstmt 사이의 정리 순서는 상관 없음
			DBUtil.executeClose(null, pstmt3, null);
			DBUtil.executeClose(null, pstmt2, null);
			DBUtil.executeClose(null, pstmt1, conn);
		}
	}
}

*DAO : Data Access Object

-데이터베이스의 데이터를 전문적으로 호출하고 제어하는 객체

-데이터베이스 연동 작업에서는 자원정리를 해야하기 때문에 에러를 throw 하지 않고
메서드 안에서 try-catch-finally를 해주는 것이 원칙

*게시판 콘솔 프로그램 만들기

NoteDAO 클래스	-글쓰기, 목록 보기, 상세글 보기, 글수정, 글삭제 메서드 인자 받아와서 작성 -각각의 메서드에서 자원정리를 위한 try-catch-finally문 작성
NoteMain 클래스	-BufferedReader, NoteDAO 변수 생성 -메뉴호출 메서드 작성 생성자 -BufferedReader, NoteDAO 객체 생성 -메뉴호출 메서드 호출 -IOException 예외처리 callMenu() -while문 안에서 NumberFormatException 예외처리 *main() -생성자 호출

저작자표시

'Web Development > SIST' 카테고리의 다른 글

[JS] 8주차 수업: 2024.04.08 - 2024.04.12 (1)	2024.05.07
[Java\|HTML\|CSS] 6주차 수업: 2024.03.25 - 2024.03.29 (0)	2024.03.25
[Java\|Oracle] 4주차 수업: 2024.03.11 - 2024.03.15 (0)	2024.03.22
[Java] 3주차 수업: 2024.03.04 - 2024.03.08 (0)	2024.03.10
[Java] 2주차 수업: 2024.02.26 - 2024.02.29 (0)	2024.03.04