------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION 함수명( 파라미터1 데이터타입, 파라미터2 데이터타입, ...)
RETURN 데이터타입 IS [AS]
변수선언 ...;
BEGIN
처리내용 ...;
RETURN 리턴값;
END;
EX)
create or replace function emp_salaries (emp_id number )
return NUMBER IS
nSalaries NUMBER(9);
BEGIN
nSalaries := 0;
SELECT salary
INTO nSalaries
FROM employees
WHERE employee_id = emp_id;
RETURN nSalaries;
END;
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE 프로시저명 ( 파라미터1 데이터타입, [ IN | OUT | INOUT ], ...)
IS [AS]
변수선언부 ...;
BEGIN
프로시저 본문처리 ...;
EXCEPTION
예외처리 ...;
END;
EX)
CREATE OR REPLACE PROCEDURE register_emp (
f_name VARCHAR2,
l_name VARCHAR2,
e_name VARCHAR2,
j_name VARCHAR2,) IS
BEGIN
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (EMPLOYEES_SEQ.NEXTVAL, f_name, l_name, e_acct, sysdate, j_id );
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( f_name || ' ' || l_name || ' register is failed!' );
ROLLBACK;
END;
