하위^^

암시적 커서

암시적 커서는 SQL문장이 처리되는 곳에 대한 익명의 주소

실행되는 모든 SQL문은 암시적인 커서가 생성되고, 그대로 이용 할 수 있다.

SQL%ROWCOUNT, 해당 SQL문에 영향을 받는 행의 수

SQL%FOUND, 해당 SQL문에 영향을 받는 행의 수가 한 개 이상일 때 TRUE

SQL%NOTFOUNT, 해당 SQL문에 영향을 받는 행의 수가 없을 때 TRUE

SQL%ISOPEN, 항상 FALSE, 암시적 커서가 열려있는지 여부

커서는 사용하고 꼭 닫아줘야 한다.

JAVA의 몇몇 클래스들 처럼...

결론으로 커서는 쿼리문을 주소로 담고있는것.

명시적 커서

명시적 커서는 직접 커서를 만들어 사용하는 것.

커서에는 한개 이상의 데이터가 담기게 되고 데이터를 OPEN, PATCH, CLOSE 등을 사용하여 다룬다.

CURSOR 커서이름 IS 실행 할 문장

CURSOR test_cursor IS SELECT * FROM dept

와 같은 형식으로 사용

SET SERVEROUTPUT ON;

-- 1) 암시적(묵시적) 커서
-- SQL%ROWCOUNT : 쿼리를 만족하는 갯수 추출
-- SQL%FOUND    : 쿼리를 만족하는 갯수가 있는가? T/F
-- SQL%NOTFOUND : 쿼리를 만족하는 갯수가 없는가? T/F

DECLARE
    v_emp employees%ROWTYPE;
    BEGIN
        SELECT salary INTO v_emp.salary FROM employees WHERE emp_no = 115;
        IF SQL%FOUND THEN
        -- FOUND - 해당 문장 값이 존재하는가
            DBMS_OUTPUT.PUT_LINE('검색한 값이 있습니다.');
            DBMS_OUTPUT.PUT_LINE('115번 사원의 급여 : '||v_emp.salary);
            DBMS_OUTPUT.PUT_LINE('115번 사원의 수 : '||SQL%ROWCOUNT);
        ELSE
            DBMS_OUTPUT.PUT_LINE('검색한 값이 없습니다.');
        END IF;
    END;
/


SELECT * FROM dept;
-- 2) 명시적 커서
DECLARE
    CURSOR cur IS SELECT * FROM dept;
    v_dept dept%ROWTYPE;
    BEGIN
        OPEN cur;
        LOOP
            FETCH cur INTO v_dept.deptno, v_dept.deptname, v_dept.loc;
            DBMS_OUTPUT.PUT_LINE('부서번호 : '||v_dept.deptno);
            DBMS_OUTPUT.PUT_LINE('부서명 : '||v_dept.deptname);
            DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
            DBMS_OUTPUT.PUT_LINE('Count : '||cur%ROWCOUNT);
            EXIT WHEN cur%ROWCOUNT >= 5;
        END LOOP;
        CLOSE cur;
    END;
/

-- FOR IN LOOP
-- CURSOR의 OPEN,PATCH,CLOSE 가 필요 없다.
DECLARE
    CURSOR cur IS SELECT * FROM dept;
    v_dept dept%ROWTYPE;
    BEGIN
        FOR v_dept IN cur LOOP
            DBMS_OUTPUT.PUT_LINE('부서번호 : '||v_dept.deptno);
            DBMS_OUTPUT.PUT_LINE('부서명 : '||v_dept.deptname);
            DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
        END LOOP;
    END;
/

다른 언어의 기본적인 문법 중 반복문 역시 사용 가능하다.

LOOP, WHILE, FORLOOP 가 있다.

차이점은 따로 없으며 예제를 통해 간단히 확인하자

LOOP

SET SERVEROUTPUT ON;
-- LOOP문은 빠져나갈 조건이 만족 될 때까지 반복된다.

DECLARE
    i NUMBER(1) := 0;
    BEGIN
        LOOP
            i := i + 1;
            DBMS_OUTPUT.PUT_LINE(i);
            EXIT WHEN i > 5;
        END LOOP;
    END;
/

-- dept에 있는 데이터를 하나씩 불러온다. (전체 5개))
SELECT deptno, deptname, loc FROM dept WHERE deptno = 1;

DECLARE
    i NUMBER(1) := 0;
    v_val dept%ROWTYPE;
    BEGIN
        LOOP
            i := i + 1;
            SELECT deptno, deptname, loc INTO v_val.deptno, v_val.deptname, v_val.loc FROM dept WHERE deptno = i;
            DBMS_OUTPUT.PUT_LINE(v_val.deptno||'/'||v_val.deptname||'/'||v_val.loc);
            EXIT WHEN i > 4;
        END LOOP;
    END;
/

WHILE

-- WHILE 문, 브레이크를 하나 더 만들어 주는 것.
DECLARE
    i NUMBER(1) := 0;
    v_val dept%ROWTYPE;
    BEGIN
        WHILE i <= 5 LOOP
            i := i + 1;
            SELECT deptno, deptname, loc INTO v_val.deptno, v_val.deptname, v_val.loc FROM dept WHERE deptno = i;
            DBMS_OUTPUT.PUT_LINE(v_val.deptno||'/'||v_val.deptname||'/'||v_val.loc);
            EXIT WHEN i >= 5;
        END LOOP;
    END;
/

FOR LOOP

FOR문은 향상된 FOR문처럼 데이터를 가져오는데

DB의 데이터를 반복문을 통해 한번에 가져올 때 씀

SET SERVEROUTPUT ON;
-- for loop은 JAVA의 향상된 FOR문과 비슷하다
-- 덩어리에서 하나씩 쪼개어 변수에 담는다.
-- FOR[변수] IN [덩어리]


-- 숫자를 통해 반복
DECLARE
    BEGIN
        FOR i IN 1..10
        LOOP
            DBMS_OUTPUT.PUT_LINE(i);
        END LOOP;
    END;
/

-- 반복으로 찾을 SELECT항목
SELECT ename, hiredate FROM emp WHERE hiredate > TO_DATE('15/01/02') ORDER BY hiredate;
-- SELECT 항목으로 반복으로 찾을 데이터
DECLARE
    BEGIN
        FOR emp_list IN (SELECT ename, hiredate FROM emp WHERE hiredate > TO_DATE('15/01/02') ORDER BY hiredate)
        LOOP
            DBMS_OUTPUT.PUT_LINE('사원이름 : '||emp_list.ename);
            DBMS_OUTPUT.PUT_LINE('입사일 : '||emp_list.hiredate);
        END LOOP;
    END;
/

IF문은 말그대로 JAVA같은 타 언어에서 사용하던 조건문이랑 똑같은 기능을 한다.

조건에 맞으면 작성한 코드를 실행하고

틀리다면 작성한 다른 코드를 실행하는 구문

데이터 결과랑 출력 구문인 DBMS_OUTPUT이랑 틀릴것입니다. 제가 ~~팀 입니다를 귀찮아서 안찾고 막 적었습니다.

SET SERVEROUTPUT ON;
SELECT * FROM dept;
-- 부서 번호를 통해 팀 이름 출력과 위치
SELECT deptname, loc FROM dept WHERE deptno = 1;

CREATE OR REPLACE PROCEDURE dept_find(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        SELECT loc INTO v_dept.loc
        FROM dept WHERE deptno = v_no;
        
        IF v_no = 1 THEN
            DBMS_OUTPUT.PUT('세일즈 팀');
        END IF;
        
        IF v_no = 2 THEN
            DBMS_OUTPUT.PUT('인사 팀');
        END IF;
        
        IF v_no = 3 THEN
            DBMS_OUTPUT.PUT('배송 팀');
        END IF;
        
        IF v_no = 4 THEN
            DBMS_OUTPUT.PUT('개발 1팀');
        END IF;
        
        IF v_no = 5 THEN
            DBMS_OUTPUT.PUT('개발 2팀');
        END IF;
        DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
    END;
/

EXEC dept_find(3);

IF ELSE 문

SET SERVEROUTPUT ON;

-- 부서 번호에 따라 세일즈 팀인지 아닌지를 출력
-- sales_find(v_no)
-- 테이블 : dept
SELECT * FROM dept;
CREATE OR REPLACE PROCEDURE sales_find(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        SELECT deptname INTO v_dept.deptname FROM dept WHERE deptno = v_no;
        
        IF v_dept.deptname = 'sales' THEN
            DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀 입니다.');
        ELSE IF
            DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀이 아닙니다.');
        END IF;
    END;
/
EXEC sales_find(2);



-- 조건에 없는 번호가 나왔을 경우 '해당 데이터가 없습니다.' 출력
-- JAVA에선 예외처리하면 되지만 DB에선??
CREATE OR REPLACE PROCEDURE sales_find2(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        IF v_no = 1 THEN
            DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀 입니다.');
        ELSE
            IF v_no >= 2 THEN
                IF v_no <= 5 THEN
                    DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀 아닙니다.');
                ELSE
                    DBMS_OUTPUT.PUT_LINE('올바른 숫자를 입력해주세요.');
                END IF;
            ELSE
                DBMS_OUTPUT.PUT_LINE('올바른 숫자를 입력해주세요.');
            END IF;
        END IF;
    END;
/
EXEC sales_find2(-1);
-- IF안에 IF를 넣는것도 가능

ELS IF 문

SET SERVEROUTPUT ON;
-- IF로 만든 프로시저를 ELSE IF로 변경
-- dept_find
SELECT name, text FROM USER_SOURCE WHERE name = 'DEPT_FIND';
-- 만들어 둔 프로시저 소스 확인

-- 부서 번호를 통해 팀 이름 출력과 위치
SELECT deptname, loc FROM dept WHERE deptno = 1;

CREATE OR REPLACE PROCEDURE dept_findels(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        SELECT loc INTO v_dept.loc
        FROM dept WHERE deptno = v_no;
        
        IF v_no = 1 THEN
            DBMS_OUTPUT.PUT('세일즈 팀');
        ELSIF v_no = 2 THEN
            DBMS_OUTPUT.PUT('인사 팀');
        ELSIF v_no = 3 THEN
            DBMS_OUTPUT.PUT('배송 팀');
        ELSIF v_no = 4 THEN
            DBMS_OUTPUT.PUT('개발 1팀');
        ELSIF v_no = 5 THEN
            DBMS_OUTPUT.PUT('개발 2팀');
        END IF;
        DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
    END;
/

EXEC dept_findels(2);

위의 코드

SELECT name, text FROM USER_SOURCE WHERE name = 'DEPT_FIND';

위와 같이 USER_SOURCE를 조회하여 확인할 수 있다.

또는

좌측에서 프로시저 항목을 보여주는데 클릭하면 위와같이 보여준다.