Oracle SQL Tutorial - How To Make Your SQL Lightning-Fast

Tip #2 - Use Oracle PL/SQL Stored Procedures

The next tip in this Oracle sql tutorial is to use Oracle plsql stored procedures which are program units that are literally stored in the database.

Oracle stored procedures contain both SQL statements (they are not just restricted to queries) and logic, they can be written in either PL/SQL or Java and are another good way of ensuring that your SQL statemenst are are always the same as well as encapsulating and hiding complex application or business logic.

As with views, the use of Oracle stored procedures has the added advantage that the queries in the stored procedures have to be tuned only once, rather than every place where they're used.

Another advantage of stored procedures arises from the fact that they are stored in the Oracle database in compiled form which means there is no network overhead of sending queries from the client to the database. This makes PL/SQL or Java stored procedures particularily useful in situations where a lot of complex processing of data has to be performed and where the intermediate results are not required by the end-user.

Stored procedures can also be mixed in with SQL statements (i.e. called from SQL statements) and so can be used to make the query more efficient (by being able to have much more control over the execution of SQL statements) and/or to encapsulate complex queries.

One thing you should be very careful of, is not to use PL/SQL in Oracle when SQL would be more efficient. For example, don't update a table by fetching records in a cursor and then performing an update in the loop, use an SQL UPDATE statement with a WHERE clause instead.

You should also be aware that there is an overhead in switching from SQL to PL/SQL and back again as each time this happens, the Oracle database has to perform a context switch.

The next Oracle SQL tutorial examines why you should use bind variables in your queries.

Return to Oracle SQL Tutorial part 1