정의)
CREATE OR REPLACE PROCEDURE 디비명.프로시저명 ( 매개변수명1 IN 데이터타입 := 기본값 ,매개변수명2 IN 데이터타입 := 기본값 ) IS 변수,상수 선언 BEGIN 실행부;
예외처리부; END;
실전) V_COUNT 값에 따라 UPDATE / INSERT 분기
CREATE OR REPLACE PROCEDURE DB_NAME.PROCEDURE_NAME ( P_ID IN VARCHAR2 -- 주석 , P_TITLE IN VARCHAR2 -- 주석 ) IS
V_COUNT NUMBER != 0;
BEGIN
SELECT COUNT(*) INTO V_COUNT FROM DB_NAME.TABLE_NAME WHERE ID = P_ID;
IF V_COUNT > 0 THEN UPDATE DB_NAME.TABLE_NAME SET TITLE = P_TITLE, DATE = SYSDATE WHERE ID = P_ID; ELSE INSERT INTO DB_NAME.TABLE_NAME (ID, TITLE, CREATE_DATE, UPDATE_DATE) VALUES (P_ID, P_TITLE, SYSDATE, SYSDATE); END IF;
END;