1 금요일 복습및 INOUT진도나감
--out매개변수
--프로시저가 out변수를 가지고 있다면, 실행구문을 익명블록에서 실행합니다.
--out매개변수
--프로시저가 out변수를 가지고 있다면, 실행구문을 익명블록에서 실행합니다.
set serveroutput on;
create or replace procedure NEW_JOB_PROC
( P_JOB_ID JOBS.JOB_ID%TYPE,
P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
P_MIN_SAL IN JOBS.MIN_SALARY%TYPE := 0,
P_MAX_SAL IN JOBS.MAX_SALARY%TYPE := 1000,
P_RESULT out VARCHAR2 --전달받은값을 외부로 전달해주기위한 용도
)
IS
V_COUNT NUMBER := 0; --지역변수
BEGIN
SELECT COUNT(*)
INTO V_COUNT --지역변수
FROM JOBS
WHERE JOB_ID = P_JOB_ID;
IF V_COUNT = 0 THEN
INSERT INTO JOBS VALUES(P_JOB_ID, P_JOB_TITLE,P_MIN_SAL,P_MAX_SAL);
P_RESULT := P_JOB_ID; --성공인 경우에는 아웃변수에 아이디를 젖아
ELSE
UPDATE JOBS
SET JOB_TITLE = P_JOB_TITLE,
MIN_SALARY = P_MIN_SAL,
MAX_SALARY = P_MAX_SAL
WHERE JOB_ID = P_JOB_ID;
--업데이트 작업
P_RESULT := '존재하는 값이기 때문에 업데이트 되었습니다.';
END IF;
END;
DECLARE
STR VARCHAR2(50);
BEGIN
NEW_JOB_PROC('TEST1','TEST2',1000,2000, STR);
DBMS_OUTPUT.PUT_LINE(STR); --결과
END;
SET SERVEROUTPUT ON;
---------------------------
--IN OUT변수
create or replace procedure test_proc
(P_VAR1 IN VARCHAR2, --입력 변수 (반환불가)
P_VAR2 OUT VARCHAR2, -- 출력변수(프로시저가 끝나기 전에는 값의 할다잉 안됨)
P_VAR3 IN OUT VARCHAR2 --입,출력변수(둘다 가능)
)
is
begin
DBMS_OUTPUT.PUT_LINE('P_VAR1의 값:'|| P_VAR1 );
DBMS_OUTPUT.PUT_LINE('P_VAR2의 값:'|| P_VAR2 );
DBMS_OUTPUT.PUT_LINE('P_VAR3의 값:'|| P_VAR3 );
--P_VAR1 := '결과1'; --IN변수 치환불가, 피할당자로 사용될수없습니다 라는 오류문구 나옴
P_VAR2 := '결과2';
P_VAR3 := '결과3';
end;
DECLARE
V_A VARCHAR2(100) := 'A';
V_B VARCHAR2(100) := 'B';
V_C VARCHAR2(100) := 'C';
BEGIN
TEST_PROC(V_A, V_B, V_C);
DBMS_OUTPUT.PUT_LINE('V_B변수: ' || V_B);
DBMS_OUTPUT.PUT_LINE('V_C변수: ' || V_C);
END;
---------------------------------
--프로시저의 종료 RETURN
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC
(P_JOB_ID IN JOBS.JOB_ID%TYPE
)
IS
V_COUNT NUMBER := 0;
V_MIN_TOTAL NUMBER := 0; --최소급여 전체합
BEGIN
--값이 없다면 출력후에 프로시저를 종료,있다면 값 출력
SELECT COUNT(*)
INTO V_COUNT
FROM JOBS
WHERE JOB_ID LIKE '%' || P_JOB_ID || '%';
IF V_COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE(P_JOB_ID || '값이 없습니다');
RETURN; --프로시저의 종료
ELSE
SELECT SUM(MIN_SALARY)
INTO V_MIN_TOTAL
FROM JOBS
WHERE JOB_ID LIKE '%' || P_JOB_ID || '%';
DBMS_OUTPUT.PUT_LINE(P_JOB_ID || '의 MIN_SLARY 합:' || V_MIN_TOTAL);
END IF;
DBMS_OUTPUT.PUT_LINE('프로시저 정상종료');
END;
EXEC NEW_JOB_PROC('TETS');
EXEC NEW_JOB_PROC('MAN');
SELECT SUM(MIN_SALARY) FROM JOBS;
SELECT * FROM JOBS;
--연습문제
SELECT * FROM EMPLOYEES;
/*
MEPLOYEE_ID를 받아서 EMPLOYEES에 존재하면,근속년수를 출력
없다면 ,없습니다 를 출력하는 프로시저
*/
CREATE OR REPLACE PROCEDURE NEW_EMP_PROC
(
P_EMP_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE
)
IS
ID_CHECK NUMBER := 0;
P_HIRE_DATE DATE := SYSDATE;
BEGIN
SELECT COUNT(*)
INTO ID_CHECK
FROM EMPLOYEES
WHERE EMPLOYEE_ID LIKE '%' || P_EMP_ID || '%';
IF ID_CHECK = 0 THEN
DBMS_OUTPUT.PUT_LINE(P_EMP_ID || '는 없습니다');
RETURN; --프로시저의 종료
ELSE
SELECT HIRE_DATE
INTO P_HIRE_DATE
FROM EMPlOYEES
WHERE EMPLOYEE_ID LIKE '%' || P_EMP_ID || '%';
DBMS_OUTPUT.PUT_LINE
(
P_EMP_ID||'의 근속년수는'||trunc(months_between(sysdate,P_HIRE_DATE)/12)
);
END IF;
DBMS_OUTPUT.PUT_LINE('프로시저 정상종료');
END;
SELECT HIRE_DATE FROM EMPlOYEES;
select salary from employees;
CREATE OR REPLACE PROCEDURE EMP_YEAR_PROC
(P_EMP_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE
)
IS --프로시저에서 사용할 매개변수
V_COUNT NUMBER := 0;
V_YEAR NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
IF V_COUNT = 0 THEN --없는경우
DBMS_OUTPUT.PUT_LINE('해당 아이디는 존재하지 않습니다');
RETURN;
ELSE --존재하는 경우
SELECT TRUNC((SYSDATE -HIRE_DATE) / 365)
INTO V_YEAR
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
DBMS_OUTPUT.PUT_LINE(P_EMP_ID || '의 근속년수:' || V_YEAR);
END IF;
--예외처리
EXCEPTION WHEN OTHERS THEN --예외처리 구문
DBMS_OUTPUT.PUT_LINE('예외가 발생했습니다');
END;
EXEC EMP_YEAR_PROC (200);
EXEC NEW_EMP_PROC (200);
2.트리거란 ?
before 트리거 after 트리거
