[pl/sql] insert or update procedure

CREATE OR REPLACE PROCEDURE JOB_PROC ( --param P_JOBID IN JOBS.JOB_ID%TYPE, P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE, P_MIN_SAL IN JOBS.MIN_SALARY%TYPE, P_max_sal IN JOBS.max_salary%TYPE ) IS VN_CNT NUMBER:= 0; BEGIN --동일한 job id가 있는지 확인 SELECT count(*) into vn_cnt FROM JOBS WHERE JOB_ID = P_JOBID; --없으면 insert IF VN_CNT = 0 THEN INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( P_JOBID, P_JOB_TITLE, P_MIN_SAL, P_MAX_SAL ); --있으면 update ELSE UPDATE JOBS SET JOB_TITLE = P_JOB_TITLE ,MIN_salary = P_MIN_SAL ,max_salary = p_max_sal WHERE JOB_ID = P_JOBID; end if; commit; END; / SHOW ERR; -- pl/sql 실행 EXEC JOB_PROC('SM_JOB1','Sample JOB1',4000,8000); / --실행 결과 조회 SELECT * FROM JOBS where job_id = 'SM_JOB1';

---plsql
CREATE OR REPLACE PROCEDURE JOB_PROC
(
  --param
  P_JOBID IN JOBS.JOB_ID%TYPE,
  P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
  P_MIN_SAL IN JOBS.MIN_SALARY%TYPE,
  P_max_sal IN JOBS.max_salary%TYPE
)
IS 
  VN_CNT NUMBER:= 0;
BEGIN
  --동일한 job id가 있는지 확인
  SELECT count(*)
  into vn_cnt
  FROM JOBS
  WHERE JOB_ID = P_JOBID;
  
  --없으면 insert
  IF VN_CNT = 0 THEN
    INSERT INTO JOBS
    (
      JOB_ID,
      JOB_TITLE,
      MIN_SALARY,
      MAX_SALARY
    )
    VALUES
    (
      P_JOBID,
      P_JOB_TITLE,
      P_MIN_SAL,
      P_MAX_SAL
    );
    
  --있으면 update
  ELSE
    UPDATE JOBS
    SET JOB_TITLE = P_JOB_TITLE
    ,MIN_salary = P_MIN_SAL
    ,max_salary = p_max_sal
    WHERE JOB_ID = P_JOBID;
  end if;
  
  commit;
END;
/
SHOW ERR;


EXEC JOB_PROC('SM_JOB1','Sample JOB1',4000,8000);
/
SELECT * FROM JOBS
where job_id = 'SM_JOB1';

exec job_proc(p_jobid =>'SM_JOB1', p_job_title => 'Sample JOB1', p_min_sal=> 5000, p_max_sal => 10000);

/