오라클 PL/SQL 함수 & 프로시저 구문형식

------------------------------------------------------------------------------------------------------------

PL/SQL 함수

------------------------------------------------------------------------------------------------------------
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;







------------------------------------------------------------------------------------------------------------

PL/SQL / 프로시저

------------------------------------------------------------------------------------------------------------

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;