Database Tutorial

This article describes the what, why, and how of writing a stored procedure in an Oracle database, using the PL/SQL programming language. PL/SQL procedures act like C functions, or C++, C#, or Java methods that return a void data type.

What are procedures?

You can write a procedure to hold code that you will use in other programs. The process of writing stored program units lets you modularize your programming code. You can call procedures without assigning the results of the procedure to a local variable.

A stored procedure has a name, and the name must be unique within a database (database is synonymous with a schema). A stored procedure may also have a list of parameters. Parameters have a name, data type, and a mode of operation. The mode of operation determines whether you can pass a value or variable into a parameter. These are the three modes of operation for parameters:

• IN mode: Can accept a value or a variable as the parameter value. The parameter value can’t change during the execution of the procedure and is discarded at the end of the procedure’s operation.
• IN OUT mode: Can only accept a variable as the parameter value. The parameter value can change during the execution of the procedure and the value at the end of the procedure’s operation becomes the new value of the original variable.
• OUT mode: Can only accept a variable as the parameter value. The parameter automatically has a null value when the procedure starts it’s execution. The parameter value at the end of the procedure’s operation becomes the new value of the original variable.

You can define procedures to work with data stored in the same database (or schema) or you can define procedures to work with local data stored outside of their database. The default behavior works against data in the same database, and it is known as definer rights. The optional behavior lets you define procedures to work with local data, and it is known as invoker rights.

Most database solutions implement definer rights models because the Internet and Intranet are ubiquitous (or common place). The invoker rights solution was created for distributed databases. You would implement invoker rights approaches when the data needed to reside in independent databases. That was often true for remote locations.

Invoker rights solutions require you to create matching tables and sequences for each database. The tables and sequences to need to mirror the tables and sequences in the database where you create the stored procedures. Ultimately, distributed data solutions support some mechanism for gathering the discrete data into a single repository. The single repository matches the tables and sequences found in the database where you deploy your stored procedures.