Oracle procedures are stored blocks of code that you can call anytime you want with or without parameters. It is especially handy when you need to call the same code multiple times or on multiple places; the maintenance is easy 🙂
Oracle procedures may or may not accept parameters. There are three main types of parameters: IN, INOUT and OUT. Nonetheless, do not mix OUT parameter of a procedure with the return value from the function. The procedure itself does not return any value. That is the main difference between function and procedure. Let’s have a closer look at the procedure template.
CREATE [OR REPLACE] PROCEDURE my_procedure [parameter1, ... ] IS|AS -- there is no difference between them so choose whichever you like the most -- here you can declare variables, cursors, collections, ... BEGIN -- here comes the body of the procedure where your logic is executed EXCEPTION --- even though this one is not mandatory I recommend using it all the time WHEN ... -- here you can define all types of exceptions END; -- alternatively you can use "END my_procedure;"
To sum up, there are four main parts:
- CREATE [OR REPLACE] PROCEDURE
- here you can name your procedure and define parameters
- use OR REPLACE keyword if you want to overwrite an existing procedure with the same name inside the same schema (be careful!);
- all parameters must have an indicator of whether they are input (IN) parameters, output (OUT) parameters or input & output (INOUT) parameters. For the input, it is not mandatory to explicitly define the IN keyword (all parameters are considered as IN if not specified), although I highly recommend using those as well for the readability’s sake.
- example of parameters:
- CREATE OR REPLACE PROCEDURE my_test_proc (p1 IN NUMBER, p2 INOUT NUMBER, p3 OUT DATE, p4 OUT VARCHAR, p5 IN DATE)
- example of parameters:
- after the parameter type declaration, you have to declare the data type
- this statement follows by AS or IS … they are both equal so pick one
- this part marks the start of the processing of your business logic implemented inside
- to learn more read the article about Exceptions
- this part marks the end of the procedure.
I will now demonstrate on the following example how the procedure could work:
CREATE OR REPLACE PROCEDURE upd_salary_by_emp_id (p_emp_id IN NUMBER, p_salary IN NUMBER, p_result OUT VARCHAR) IS -- custom exceptions definition ex_emp_not_found EXCEPTION; ex_not_number EXCEPTION; ex_salary_too_high EXCEPTION; v_emp_exists NUMBER := 0; -- default 0 (=employee does not exist) v_max_allowed_salary NUMBER := 50000; v_emp_name employee.name_full%TYPE; BEGIN -- first check whether the emp_id is a number IF LENGTH(TRIM(TRANSLATE (p_emp_id, '0123456789',' '))) IS NULL THEN -- check if the employee exists SELECT count(*) INTO v_emp_exists FROM employee WHERE id_employee = p_emp_id; IF v_emp_exists > 0 THEN -- exists -- last check for the salary IF p_salary <= v_max_allowed_salary THEN -- final update UPDATE employee SET salary = p_salary WHERE id_employee = p_emp_id; -- Get employee name for the final message SELECT name_full INTO v_emp_name FROM employee WHERE id_employee = p_emp_id; p_result := 'Employee '||v_emp_name||' ('||p_emp_id||') updated ...'; ELSE RAISE ex_salary_too_high; END IF; ELSE RAISE ex_emp_not_found; END IF; ELSE RAISE ex_not_number; END IF; EXCEPTION WHEN ex_not_number THEN ROLLBACK; p_result := 'Given employee ID is not a number!'; WHEN ex_emp_not_found THEN ROLLBACK; p_result := 'Given employee ID does not exist!'; WHEN ex_salary_too_high THEN ROLLBACK; p_result := 'Given salary is too high!'; WHEN OTHERS THEN -- for all other errors ROLLBACK; p_result := 'Unknown error.'; END; / -- now check the table data SELECT * FROM employee; /* output id_employee | name_full | id_department | salary -------------------------------------------------- 1 | John Doe | 10020 | 2000 */ -- call the procedure DECLARE v_operation_rslt VARCHAR2(255); BEGIN upd_salary_by_emp_id(p_emp_id => 1, p_salary => 3000, p_result => v_operation_result); -- to store the result message into the local variable dbms_output.put_line(v_operation_result); -- check the status message of the operation END; /* output Employee John Doe (1) updated ... */ -- check the table again SELECT * FROM employee; /* output id_employee | name_full | id_department | salary -------------------------------------------------- 1 | John Doe | 10020 | 3000 */ -- test the exceptions now DECLARE v_operation_rslt VARCHAR2(255); BEGIN upd_salary_by_emp_id(p_emp_id => 1, p_salary => 100000, -- TOO HIGH !! p_result => v_operation_result); -- to store the result message into the local variable dbms_output.put_line(v_operation_result); -- check the status message of the operation END; /* output Given salary is too high! */ -- nothing has changed in the table ... -- test the exceptions now DECLARE v_operation_result VARCHAR2(255); BEGIN upd_salary_by_emp_id (p_emp_id => 10, -- non-existing employee p_salary => 5000, p_result => v_operation_result); -- to store the result message into the local variable dbms_output.put_line(v_operation_result); -- check the status of the operation END; /* output Given employee ID does not exist! */ -- and again -> nothing has changed in the table ...
I call this type of approach “paranoid mode” 🙂 It has all checks to make it the most secure procedure ever. However, this was just an example on how to update the salary of an employee to demonstrate how IN, OUT and INOUT parameters work. You can, of course, be more create and use procedures for INSERTing new rows so for example auto-generated ID will always use the following number from the associated sequence and therefore there will be no “typos” or other data inconsistencies when some initiative developer will do some hardcoded max(id)+1, use a random value or a different sequence etc. You have unlimited options 🙂 Just consider the more complicated logic the more time it will take to execute!
If you need to get rid of a procedure, you simply DROP it 🙂
DROP PROCEDURE upd_salary_by_emp_id;