Sample Chapter: Efficient PL/SQL

Don 't Use PL/SQL to Do the Job of SQL

PL/SQL is often used excessively. The very first sentence of the PL/SQL Users Guide and Reference book that comes as part of the Oracle 9.2 documentation is "PL/SQL, Oracle's procedural extension of SQL...." PL/SQL was designed as (and always has been) an extension to SQL, that is, a tool that can be used when SQL cannot do the job requested.

To demonstrate, let's look at a fictitious example that would appear to be tailor-made for PL/SQL. We will re-create the ubiquitous emp and dept tables for our example so that we can populate them with some larger sample data sizes.

Before proceeding, we will re-create the src table from the previous section to hold 200,000 rows of nonsensical data. We'll use this table as a source of data to populate other tables in this example. We don't care what is in the table rows, just that there are at least 200,000 of them. If your system already has such a table, you can use that one in any of the following examples. Alternatively, if you are using version 9 or above, you can use the pipeline function solution presented in the previous section.

Let's now populate the emp table with 500 employees by using the rownum pseudo-column to generate names and date of hiring, dbms_random to generate some random salaries, and ensure that the department (deptno) ranges between 1 and 10.

Now there are 500 employee records, each assigned to one of 10 departments. The reason for creating this example is that the CEO is disgusted at the vast difference between the salaries of the highest income earners in each department versus the pittance that the low incomes earners currently receive. (As we said, it is a fictitious example!)