Stored Procedure

Syntax

CREATE [OR REPLACE] PROCEDURE <procedure_name> [(parameter1 [IN | OUT | IN OUT] [, parameter2 [IN | OUT | IN OUT] ] ]
IS/AS
[<declaration_section>]
BEGIN
 <executable_section>
[EXCEPTION
<exception_section>]
END <procedure_name>;

Example

CREATE OR REPLACE PROCEDURE updatesalary (p_emp_no IN NUMBER)
IS
   v_number NUMBER;
BEGIN
   UPDATE employee_master
      SET salary = salary + 10%
    WHERE employee_num = p_emp_no;
EXCEPTION
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR (-20001, 'Error occurred while updating salary of employee - '||p_emp_no);
END updatesalary;