DATA - Data Architecture > Data and Database Design > Generating SQL scripts > Using Stored Procedures
Using Stored Procedures
HOPEX Data Architecture allows you to create stored procedures.
A stored procedure combines a procedural language and SQL requests within a program. It enables execution of a particular task on a database. It is recorded in a database and can be called from a program external to the database of from a trigger.
A stored procedure can be implemented in two ways; either as a procedure or as a function.
*A procedure is a set of instructions executing a sub-program.
*A function is a procedure returning a value on completion of execution.
To create a procedure stored on a database:
1. Open the database properties dialog box.
2. Click the drop-down list then Components.
The Stored Procedures section displays the list of stored procedures.
3. Click the New button.
4. In the window that opens, specify the name of the procedure and its nature (Procedure or Function).
5. Click OK.
The stored procedure appears. Open its properties to define its code.
Example of stored procedure for Oracle
This is an example of a stored procedure updating the unit price of a part as a function of the part identifier:
CREATE PROCEDURE update_part_unitprice (part_id IN INTEGER, new_price IN NUMBER)
IS
Invalid_part EXCEPTION;
BEGIN
-- HERE'S AN UPDATE STATEMENT TO UPDATE A DATABASE RECORD
UPDATE sales.parts
SET unit_price = new_price
WHERE id = part-id;
-- HERE'S AN ERROR-CHECKING STATEMENT
If SQL%NOTFOUND THEN
RAISE invalid_part;
END IF;
EXCEPTION
-- HERE'S AN ERROR-HANDLING ROUTINE
WHEN invalid_part THEN
raise_application_error(-20000, 'Invalid Part ID');
END update_part_unitprice;