oracle create procedure example

1CREATE OR REPLACE PROCEDURE my_schema.my_procedure(param1 IN VARCHAR2) IS
2    cnumber NUMBER;
3BEGIN
4    cnumber := 10;
5    INSERT INTO my_table (num_field) VALUES (param1 + cnumber);
6    COMMIT;
7EXCEPTION
8    WHEN OTHERS THEN
9        raise_application_error(-20001, 'An error was encountered - '
10            || sqlcode || ' -ERROR- ' || sqlerrm);
11END;

1-- Oracle
2
3-- EXAMPLE
4CREATE OR REPLACE PROCEDURE select_employees()
5BEGIN
6   select * 
7   from employees 
8   limit 1000; -- Remember, the delimiters within the stored procedure remain ;
9END;
10
11/* SYNTAX
12CREATE OR REPLACE PROCEDURE <Your-procedure-name>(<argument1><argument2>...<argumentN>)
13BEGIN
14   <Code-that-stored-procedure-executes>; -- Remember, the delimiters within the stored procedure remain ;
15END;
16*/