Procedures

Procedures are program unit that executes one or more statements and can receive or return zero or more values through their parameter list.

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name {IN|OUT|IN OUT} parameter_datatype [,parameter])]
{IS|AS}
declaration_section
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
/

Example procedure

PROCEDURE award_bonus (emp_id NUMBER) IS
bonus REAL;
comm_missing EXCEPTION;
BEGIN — executable part starts here
SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;
EXCEPTION — exception-handling part starts here
WHEN comm_missing THEN

END award_bonus;
/

Introduction to Oracle supplied PL/SQL packages HERE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: