SQL, PL/SQL and Oracle Development Best Practices

Understanding how SQL and PL/SQL work together is critical for good database system development. The conceptual differences between these languages are large, but they complement each other in a way that is unique in the industry. SQL does the “heavy lifting” of data retrieval, while PL/SQL handles the procedural logic. Together, they form the backbone of good database-centric development.

SQL continues to expand its capabilities. It can do more and more that used to only be possible using PL/SQL. However, PL/SQL still allows you to do things that cannot be done well or at all in SQL. The trick is knowing when and why to use each language to leverage its strengths and maintain good system performance and functionality.

The important points to keep in mind regarding expanding your SQL horizons are as follows:

• You should utilize PL/SQL only when it is needed. Otherwise, stay with SQL, because the context switches between the two different languages are costly.
• Embedding PL/SQL in SQL must be handled carefully because you need to measure potential system overhead against potential gains.
• PL/SQL program units can also have associated statistics. These statistics can significantly impact the execution plans in corresponding SQL statements. Getting the right data (cost, selectivity, and cardinality) to the Cost-Based Optimizer allows it to make the right decisions.
• You must manage the total number of PL/SQL function calls that are being generated by SQL. Otherwise, you will be wasting a lot of resources.
• Aggregate and analytic functions are very powerful tools, especially when you need to process lots of data. You can even write your own functions using the ODCI interface.
• Oracle Database 12c contains some new features, namely the PRAGMA UDF clause and the ability to add user-defined functions and procedures directly to SQL as a part of the WITH clause.
• Adding PRAGMA UDF results in major performance improvements when user-defined functions are called from SQL, while the overhead of SQL optimization of these functions within PL/SQL is less noticeable.