Oracle 20일차 - Cursor, Function

2020.11.11~12(수, 목)

-- cursor : 여러개 Row(Record)를 저장할 수 있는 공간 ==> ResultSet
-- 처리 => CURSOR => 자바 (ResultSet)

 

1. 커서등록
    cursor cur_name IS
        SELECT * FROM emp

2. open

3. fetch

4. close

SET SERVEROUTPUT ON;
DECLARE
    vemp emp%ROWTYPE;
    CURSOR cur IS
    SELECT * FROM emp;
BEGIN
    OPEN cur;
    DBMS_OUTPUT.PUT_LINE('====결과====');
    LOOP 
     FETCH cur INTO vemp;
      EXIT WHEN cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('사번:'||vemp.empno);
      DBMS_OUTPUT.PUT_LINE('이름:'||vemp.ename);
      DBMS_OUTPUT.PUT_LINE('직위:'||vemp.job);
      DBMS_OUTPUT.PUT_LINE('입사일:'||vemp.hiredate);
      DBMS_OUTPUT.PUT_LINE('급여:'||vemp.sal);
    END LOOP;
    CLOSE cur;
END;

DECLARE
    vdept dept%ROWTYPE;
    /*
        dept
         deptno
         dname
         loc
    */
    CURSOR cur IS
     SELECT * FROM dept;
BEGIN
    FOR vdept IN cur LOOP
     DBMS_OUTPUT.PUT_LINE(vdept.deptno||' '||vdept.dname||' '||vdept.loc);
    END LOOP;
END;

 

--PROCEDURE

/*
    생성 => ALTER가 없다
    CREATE (OR REPLACE) PROCEDURE pro_name(
        매개변수 ,
        매개변수...
        1) 스칼라 변수, 2) %TYPE
        
    )
    IS (AS)
        지역변수 설정
    BEGIN
        제어 => 제어문 , 연산자 , SQL
    END;
    /
    삭제
    DROP PROCEDURE pro_name;
    호출
        SELECT => EXECUTE
        INSERT, UPDATE, DELETE => CALL
    매개변수
        IN : 내부에서만 사용하는 변수
        OUT : Call By Reference => 결과값을 받는 변수
        INOUT : 내부사용, 값을 받는 변수
        
        예)
            CREATE PROCEDURE empInsert(
                name IN VARCHAR2(20),
                addr IN VARCHAR2(100),
                tel IN VARCHAR2(20),
                result OUT VARCHAR2(100) => 메모리 주소 (주소에 값을 채운다)
            )
            
            int* p;
            void disp(int* p)
            (
                *p=100;
            )
            disp(p); ==> p=100
*/
CREATE TABLE pl_student(
    hakbun NUMBER PRIMARY KEY,
    name VARCHAR2(34) NOT NULL,
    kor NUMBER, eng NUMBER, math NUMBER
);
INSERT INTO pl_student VALUES(1,'홍길동',90,90,100);
INSERT INTO pl_student VALUES(2,'박문수',85,80,75);
COMMIT;
SELECT * FROM pl_student;

 

--INSERT 추가

CREATE OR REPLACE PROCEDURE studentInsert(
    pName pl_student.name%TYPE,
    pKor pl_student.kor%TYPE,
    pEng pl_student.eng%TYPE,
    pMath pl_student.math%TYPE
)
IS
BEGIN
    INSERT INTO pl_student VALUES(
        (SELECT NVL(MAX(hakbun)+1,1) FROM pl_student),
        pName,pKor,pEng,pMath
    );
    COMMIT;
END;
/
 
CALL studentInsert('심청이',80,90,76);

 

-- 데이터 상세

CREATE OR REPLACE PROCEDURE studentDetailData(
    pNo NUMBER,
    pName OUT VARCHAR2,
    pKor OUT NUMBER,
    pEng OUT NUMBER,
    pMath OUT NUMBER
)
IS
BEGIN
    SELECT name, kor, eng, math INTO pName, pKor, pEng, pMath
    FROM pl_student
    WHERE hakbun=pNo;
END;
/
VARIABLE pName VARCHAR2;
VARIABLE pKor NUMBER;
VARIABLE pEng NUMBER;
VARIABLE pMath NUMBER;
EXECUTE studentDetailData(1,:pName,:pKor,:pEng,:pMath);
PRINT pName;
PRINT pKor;
PRINT pEng;
PRINT pMath;
 

 

-- DELETE

CREATE OR REPLACE PROCEDURE studentDelete(
    pNo pl_student.hakbun%TYPE
)
IS
BEGIN
    DELETE FROM pl_student
    WHERE hakbun=pNo;
    COMMIT;
END;

 

-- UPDATE

CREATE OR REPLACE PROCEDURE studentUpdate(
    pNo NUMBER,
    pName VARCHAR2,
    pKor NUMBER,
    pEng NUMBER,
    pMath NUMBER
)
IS
 -- 변수
BEGIN
    UPDATE pl_student SET
    name=pName, kor=pKor, eng=pEng, math=pMath
    WHERE hakbun=pNo;
    COMMIT;
END;

 

-- Total

CREATE OR REPLACE PROCEDURE studentListData(
    pResult OUT SYS_REFCURSOR
    
)
IS
BEGIN
    OPEN pResult FOR
        SELECT * FROM pl_student;hakbun,name,kor,eng,math,studentTotal(hakbun),studentAvg(hakbun) FROM pl_student;
END;

 

--studentAvg

CREATE OR REPLACE FUNCTION studentAvg(
    pNo pl_student.hakbun%TYPE
)RETURN NUMBER
IS
    pAvg NUMBER;
BEGIN
    SELECT (kor+eng+math)/3 INTO pAvg
    FROM pl_student
    WHERE hakbun=pNo;
    
    RETURN pAvg;
END;

 

--studentTotal

CREATE OR REPLACE FUNCTION studentTotal(
    pNo pl_student.hakbun%TYPE
)RETURN NUMBER
IS
    pTotal NUMBER;
BEGIN
    SELECT kor+eng+math INTO pTotal
    FROM pl_student
    WHERE hakbun=pNo;
    
    RETURN pTotal;
END;
/
 
SELECT name,kor,eng,math,studentAvg(hakbun),studentTotal(hakbun)
FROM pl_student;

---

FUNCTION

*PROCEDURE은 결과값이 없음

FUNCTION은 결과값이 있기 떄문에 RETURN으로 넘겨줘야함

*쿼리 문장이 복잡해지면 FUNCTION 또는 PROCEDURE을 만든다

*자동처리 => TRIGGER

/*
    Function : 리턴값이 있는 함수
    형식)
    CREATE (OR REPLACE) FUNCTION func_name(
        매개변수
        ..
        ..
    )RETURN 데이터형
    IS
    지역변수
    BEGIN
        처리 => SQL
        결과값을 보내준다
        RETURN 값
    END;
    /
*/
 
SELECT ename, job, dname, loc
FROM emp, dept
WHERE emp.deptno=dept.deptno;
 
SELECT ename, job, (SELECT dname FROM dept d WHERE d.deptno=e.deptno) as dname,
        (SELECT loc FROM dept d WHERE d.deptno=e.deptno) as loc
FROM emp e;
 
--함수
 
CREATE OR REPLACE FUNCTION getDname(
    pDeptno emp.deptno%TYPE
)RETURN VARCHAR
IS
    vdname dept.dname%TYPE;
BEGIN
 SELECT dname INTO vdname
 FROM dept
 WHERE deptno=pDeptno;
 RETURN vdname;
END;
/
 
SELECT ename,job,getDname(deptno) FROM emp;
 
-- loc 찾는 함수
 
CREATE OR REPLACE FUNCTION getLoc(
    pDeptno emp.deptno%TYPE
)RETURN VARCHAR
IS
    vdname dept.dname%TYPE;
BEGIN
    SELECT loc INTO vdname
    FROM dept
    WHERE deptno=pDeptno;
    RETURN vdname;
END;
/
 
SELECT ename,job,getDname(deptno),getLoc(deptno) FROM emp;

---

PL/SQL

 1. 변수

   = 스칼라 변수 no NUMBER, name VARCHAR2(34)

   = %TYPE => 실제 테이블에 존재하는 데이터형을 읽어 온다

     emp

      empno NUMBER(4)

      vempno NUMBER(4)

      emp.empno%TYPE

   = CURSOR => 여러개의 레코드를 모아서 가지고 올때 (ResultSet)

2. 제어문, 연산자

  = 연산자

    1) 산술연산자 +, -, *, / => MOD(숫자,나머지) => MOD(10,2) = 0

       % (X)

    2) 비교연산자 =, !=(<>,^=), <, >, <=, >=

    3) 논리연산자 AND, OR, NOT

        NOT BETWEEN 1 AND 10, NOT LIKE, NOT IN

    4) BETWEEN AND (기간,범위)

    5) IN

    6) LIKE

   = 제어문

       조건문

        IF

        IF ~ ELSE

        IF ~ ELSIF ~ ELSIF ~ ELSE

        CASE

           WHEN 조건 THEN 결과

           WHEN 조건 THEN 결과

           WHEN 조건 THEN 결과

        ELSE

       반복문

         - LOOP

             반복처리

         END LOOP;

         WHILE (조건) LOOP

            처리

            증가식 ++(X) a=a+i

         END LOOP;

 

         FOR 변수 IN (REVERSE) lo..hi LOOP

           처리

         END LOOP;

         =========

         DECLARE => CREATE PROCEDURE, CREATE FUNCTION, CREATE TRIGGER => PL

         BEGIN

            처리

         END;

         /

---

         1. PROCEDURE : 리턴형이 없는 함수 => DML, 페이징,보안, 캐시메모리에 저장 속도가 빠르다

                                                              트랜잭션 제어

            형식)

                     CREATE (OR REPLACE) PROCEDURE por_name(

                          매개변수

                          => IN : SQL문장 실행시 필요한 데이터 => WHERE, INSERT, UPDATE, DELETE

                          => OUT : SQL문장 실행 결과값을 가지고 올때 => SELECT

                     )

                     IS|AS

                       변수선언

                     BEGIN

                        SQL 구현

                     END;

                      /

 

         2. FUNCTION : 리턴형이 있는 함수 => JOIN, SUBQUERY 대신 처리

                 CREATE (OR REPLACE) FUNCTION func_name(

                         매개변수 (OUT변수가 존재하지 않는다)

                 )RETURN 데이터형

                 IS|AS

                       변수선언

                 BEGIN

                       구현

                       RETURN 결과값

                 END;

                      /