[Oracle]PL/SQL- CURSOR/PROCEDURE vs FUNCTION 과 예제

 개념 정리

 

# CURSOR

개념	여러 개의 row(record)를 저장할 수 있는 공간 (자바에서의 ResultSet)
처리	cursor → 자바(resultSet)
커서 사용법	1. 커서 등록          Cursor cur_name IS          SELECT * FROM emp      2. open      3. fetch      4. close

 

 

 

# PROCEDURE vs FUNCTION

구분	PROCEDURE	FUNCTION
개념	- 리턴형이 없는 함수   - DML, 페이징     - 보안이 좋음, 캐시메모리에 저장   - 속도가 빠름, 트랜잭션 제어	리턴형이 있는 함수 →  JOIN,SUBQUERY 대신 처리
형식	CREATE OR REPLACE PROCEDURE por_name(       매개변수        = IN : SQL문장 실행시 필요한 데이터     →WHERE, INSERT, UPDATE, DELETE        = OUT : SQL문장 실행 결과값을 가지고 올 때 → SELECT      )       IS(AS)          변수       BEGIN          SQL 구현       END;        /	CREATE OR REPLACE FUNCTION func_name(            매개변수 (OUT 변수가 존재하지 않음                       →RETURN 을 가지고 있기 때문)        )RETURN 데이터형         IS(AS)              변수         BEGIN             SQL 구현         RETURN 결과값         END;         /

 

 

 

 

 

 예제

 

# CURSOR

SELECT * FROM dept;
 
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;
 

qh5944.tistory.com/100

[Oracle] PL/SQL- Procedure(프로시저)와 예제

앞서 만든 테이블에 이어서 작성해보겠습니다.

# 평균 구하기

--평균
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;
/
 

 

 

# 총점 구하기

--총점
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;

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;

 

 

 

# FUNCTION

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;
 
--
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;
/