- 함수에서는 일정한 연산을 수행하고 결과 값을 반환하는 역할을 했지만, 프로시저에서는 RETURN문을 만나면 이후 로직을 수행하지 않고 프로시저를 빠져 나간다.
--메소드에서는 RETURN값을 반환 후 메소드 종료
RETURN
CREATE OR REPLACE PROCEDURE MY_NEW_JOB_PROC
( P_JOB_ID IN JOBS.JOB_ID%TYPE,
P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
P_MIN_SAL IN JOBS.MIN_SALARY%TYPE:=10,
P_MAX_SAL IN JOBS.MAX_SALARY%TYPE:=100
) --IN키워드가 있는 파라미터라 값 설정 가능.
IS
VN_CNT NUMBER :=0;
BEGIN --BEGIN절 이내에는 DDL기술 불가하므로, 원시적 EXECUTE을 이용해 사용가능
IF P_MIN_SAL < 1000 THEN
DBMS_OUTPUT.PUT_LINE('최소 급여값은 1000 이상이어야 한다.');
RETURN; --실행문 완전 종료
END IF;
SELECT COUNT(*) --존재하는 행이 없어도 0을 반환함
INTO VN_CNT
FROM JOBS
WHERE JOB_ID = P_JOB_ID;
IF VN_CNT > 0 THEN
UPDATE JOBS
SET JOB_TITLE = P_JOB_TITLE,
MIN_SALARY = P_MIN_SAL,
MAX_SALARY = P_MAX_SAL,
UPDATE_DATE = SYSDATE
WHERE JOB_ID = P_JOB_ID;
DBMS_OUTPUT.PUT_LINE('데이터 갱신완료');
ELSE
INSERT INTO JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY, CREATE_DATE, UPDATE_DATE)
VALUES (P_JOB_ID, P_JOB_TITLE, P_MIN_SAL, P_MAX_SAL, SYSDATE, SYSDATE);
DBMS_OUTPUT.PUT_LINE('데이터 추가완료');
END IF;
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE DEPT_INSERT_PROC
( p_tnm VARCHAR2, --'DEPT01'
p_dno DEPT.DEPTNO%TYPE, --'TEST'
p_nm DEPT.DNAME%TYPE, --50
p_loc DEPT.LOC%TYPE )--'TEST'
IS
V_CNT NUMBER;
V_SQL VARCHAR2(1000);
BEGIN
DBMS_OUTPUT.PUT_LINE('프로시저 시작');
V_SQL := 'SELECT COUNT(*) FROM ' || p_tnm ||' WHERE DEPTNO =:A';
-- SQL내 문자열 ' '빈 공간에 따라 오류가 발생할 수 있다.
EXECUTE IMMEDIATE V_SQL INTO V_CNT USING p_dno;
IF V_CNT <=0 THEN
V_SQL := 'INSERT INTO '||p_tnm||' VALUES (:A, :A, :A)';
EXECUTE IMMEDIATE V_SQL USING p_dno, p_nm, p_loc;
DBMS_OUTPUT.PUT_LINE('인서트 성공');
ELSE
V_SQL := 'UPDATE '||p_tnm||' SET DNAME =:A, LOC =:A WHERE DEPTNO =:A';
EXECUTE IMMEDIATE V_SQL USING p_nm, p_loc, p_dno;
DBMS_OUTPUT.PUT_LINE ('업데이트 성공');
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE ('프로시저 종료');
END;
/
DECLARE
V_CNT NUMBER;
V_SQL VARCHAR2(1000);
BEGIN
SELECT COUNT(*) INTO V_CNT FROM ALL_TABLES WHERE TABLE_NAME = 'DEPT01';
IF V_CNT <= 0 THEN
V_SQL := 'CREATE TABLE DEPT01 AS SELECT * FROM DEPT';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('테이블 생성 성공');
END IF;
SELECT COUNT(*) INTO V_CNT FROM ALL_TABLES WHERE TABLE_NAME = 'DEPT01';
IF V_CNT > 0 THEN
DEPT_INSERT_PROC('DEPT01',50,'TEST','TEST');
DBMS_OUTPUT.PUT_LINE('프로시저 호출 성공');
END IF;
END;
/