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
- BEGIN
- this part marks the start of the processing of your business logic implemented inside
- EXCEPTION
- to learn more read the article about Exceptions
- END
- 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;