쌍용교육센터 - 22일

2024.03.19

생성된 함수 확인하기

데이터 사전(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';

 

프로시져(Procedure)

// 파라미터가 없는 함수
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;

 

작성된 Stored Procedure 확인

SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE = '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
    --PARAMETER 변수에 입력받은 값으로 부서(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;

활용 예시
EXEC ADD_DEPARTMENT(60,'IT SERVICE','BUSAN');

 

커서를 이용하여 질의 수행 결과 반환되는 여러 행을 처리

CREATE OR REPLACE PROCEDURE INFO_HIREDATE(P_YEAR IN VARCHAR2)
IS
E_EMP EMP%ROWTYPE;
--커서 선언
CURSOR EMP_CUR IS
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYY')=P_YEAR;
BEGIN
    --커서 열기
    OPEN EMP_CUR;
    
    --커서로부터 데이터 읽기
    LOOP
        FETCH EMP_CUR INTO E_EMP.EMPNO, E_EMP.ENAME, E_EMP.SAL;
        EXIT WHEN EMP_CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(E_EMP.EMPNO||','||E_EMP.ENAME||','||E_EMP.SAL);
    END LOOP;
    --커서 닫기
    CLOSE EMP_CUR;
END;

EXEC INFO_HIREDATE('1981');

 

함수(FUNCTION)는 반드시 값을 반환합니다.

프로시저(PROCEDURE)는 반환 값이 없고 주로 작업을 수행하는 데 사용됩니다.