[ORACLE] JOB BATCH 만들기

- 하루에 1번돌리는 oracle job batch 

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => '실행할 프로시저 이름;'  -- ; 필수 
   ,next_date => SYSDATE 
   ,interval  => 'trunc(sysdate+1)'  -- ' '따옴표  필수 
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

 

 

- oracle job batch에서  실행할 프로시저 

CREATE OR REPLACE PROCEDURE 프로시저
IS
BEGIN
	
	INSERT INTO  삽입할 테이블명 (컬럼명1,컬럼명2 , 컬럼명3 )
	SELECT 
		 컬럼명1,컬럼명2, 컬럼명3
	FROM 
		 (
			SELECT 컬럼명1,컬럼명2 FROM 조회 테이블명   
		 ) T1,
		(
			SELECT '1201' AS 컬럼명3  FROM DUAL UNION ALL SELECT '1202' AS 컬럼명3  FROM DUAL  UNION ALL SELECT '1203' AS 컬럼명3  FROM DUAL 
		) T2
	ORDER BY 컬럼명1, 컬럼명3;
	 
	COMMIT;
END;