January 21, 2021
Hot Topics:

Sample Chapter: Efficient PL/SQL

  • By Beck, Kallman, Katz, Knox, and McDonald
  • Send Email »
  • More Articles »

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.

SQL> drop table EMP;

Table dropped.

SQL>drop table DEPT;

Table dropped.

SQL> create table EMP (
  2   EMPNO       NUMBER(8),
  3   ENAME       VARCHAR2(20),
  5   SAL         NUMBER(7,2),
  6   DEPTNO      NUMBER(6));

Table created.

SQL> create table DEPT (
  2   DEPTNO      NUMBER(6),
  3   DNAME       VARCHAR2(20));

Table created.

We add primary keys to the tables, which will in turn index the empno and deptno columns respectively in these tables.

SQL> alter table EMP add constraint EMP_PK
  2  primary key (EMPNO);

Table altered.

SQL> alter table DEPT add constraint DEPT_PK
  2  primary key (DEPTNO);

Table altered.

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.

SQL> create table SRC (x varchar2(10));

Table created.

  2  for i in 1 ..200000 loop
  3   insert into SRC values ('x');
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.


Commit complete.

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.

SQL> insert into EMP
  2  select rownum,
  3      'Name'||rownum,
  4      sysdate+rownum/100,
  5      dbms_random.value(7500,10000),
  6      dbms_random.value(1,10)
  7  from SRC
  8  where rownum <=500;

500 rows created.

We now do a similar exercise with the dept table, simply adding 10 rows with a deptno ranging from 1 to 10.

SQL> insert into DEPT
  2  select rownum,'Dept'||rownum
  3  from SRC
  4  where rownum <=10;

10 rows created.

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!)

Page 14 of 19

This article was originally published on February 5, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date