DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Reduce the Volume of PL/SQL Code You Write With These Tips

Reduce the Volume of PL/SQL Code You Write With These Tips

Reduce the volume of PL/SQL code you write with these tips.

Steven Feuerstein user avatar by
Steven Feuerstein
·
Nov. 29, 18 · Tutorial
Like (1)
Save
Tweet
Share
15.76K Views

Join the DZone community and get the full member experience.

Join For Free

Reduce the volume of PL/SQL code you write.

I'm not known for being concise. I'm best known in the world of Oracle Database for my "magnum opus" Oracle PL/SQL Programming, which checks in at 1340 pages (the index alone is 50 pages long).

But I've picked up a few tips along the way for writing PL/SQL code that is, well, at least not as long, as verbose, as it could have been. And certainly shorter than my books. :)

You probably have some ideas of your own; please offer them in comments and I will add them to the post.

Qualified Expressions (New to 18c)

In the bad old days before Oracle Database 18c was released (and is now available for free in its "XE" form), if you wanted to initialize an associative array with values, you had to do in the executable section as follows:

DECLARE   
   TYPE ints_t IS TABLE OF INTEGER   
      INDEX BY PLS_INTEGER;   
   
   l_ints   ints_t;   
BEGIN   
   l_ints (1) := 55;  
   l_ints (2) := 555;  
   l_ints (3) := 5555;  
  
   FOR indx IN 1 .. l_ints.COUNT   
   LOOP   
      DBMS_OUTPUT.put_line (l_ints (indx));   
   END LOOP;   
END;

As of 18c, you can use a qualified expression (think: constructor function) as follows:

DECLARE  
   TYPE ints_t IS TABLE OF INTEGER  
      INDEX BY PLS_INTEGER;  
  
   l_ints   ints_t := ints_t (1 => 55, 2 => 555, 3 => 5555);  
BEGIN  
   FOR indx IN 1 .. l_ints.COUNT  
   LOOP  
      DBMS_OUTPUT.put_line (l_ints (indx));  
   END LOOP;  
END;

The same is true for user-defined record types. This feature not only leads to a reduction in lines of code but also allows you to write more intuitive code.

Check out my LiveSQL script for lots of examples and also my blog post on this topic.

Normalized Overloading (Hey I think I Just Invented That Term!)

Normalization of code is just as important as normalization of data. Don't repeat your data, and don't repeat your code (a.k.a., DRY: don't repeat yourself, and SPOD: single point of definition).

A great example of how important this can be is with overloading. Overloading, also known as static polymorphism (sorry, just couldn't help throwing that in), means you have two or more subprograms with the same name, but different parameter lists or program type (procedure vs function).

It's a very nice feature when it comes to reducing the number of moving parts in your API (package specification), and making it easier for developers to use your code. Usually, those multiple subprograms with the same name are doing almost the same thing, which means most of their implementation will be the same, which means...watch out for redundant code!

Here's an example from the thread (discussion) manager package of the Oracle Dev Gym backend. I start off with a single procedure to insert a new thread:

PACKAGE BODY qdb_thread_mgr
IS
   PROCEDURE insert_thread (
       user_id_in               IN PLS_INTEGER
    ,  parent_thread_id_in      IN PLS_INTEGER
    ,  thread_type_in           IN VARCHAR2
    ,  subject_in               IN VARCHAR2
    ,  body_in                  IN CLOB)
   IS
   BEGIN
      INSERT INTO qdb_threads (...) VALUES (...);
   END;
END qdb_thread_mgr;

That's great, but I now run into a situation in which I need to get the new thread ID back to use in another step of a process. The easiest thing to do is cut and paste.

PACKAGE BODY qdb_thread_mgr
IS
   PROCEDURE insert_thread (
      user_id_in               IN PLS_INTEGER
    ,  parent_thread_id_in      IN PLS_INTEGER
    ,  thread_type_in           IN VARCHAR2
    ,  subject_in               IN VARCHAR2
    ,  body_in                  IN CLOB)
   IS
   BEGIN
      INSERT INTO qdb_threads (...) VALUES (...);
   END;

   PROCEDURE insert_thread (
      user_id_in               IN     PLS_INTEGER
    ,  parent_thread_id_in      IN     PLS_INTEGER
    ,  thread_type_in           IN     VARCHAR2
    ,  subject_in               IN     VARCHAR2
    ,  body_in                  IN     CLOB
    ,  thread_id_out               OUT PLS_INTEGER)
   IS
   BEGIN
      INSERT INTO qdb_threads (...) VALUES (...);
        RETURNING thread_id
             INTO thread_id_out;
   END;
END qdb_thread_mgr;

It's not hard to argue, in this case, "so what, why not?" After all, the procedure consists of just a single INSERT statement. Why not copy-paste it? I get that, but here's the thing to keep in mind always with code:

It's only going to get more complicated over time.

That one statement will grow to three statements, then to 25 statements. And each time, along the way, you must remember to keep the two procedures in synch. And what if there are five of them?

It makes so much more sense to have a single "reference" procedure or function with all of the common logic in it. Each overloading then takes any actions specific to it before calling the reference procedure, followed by any finishing-up actions.

For the thread manager package, this means that the procedure returning the new primary key is the "reference" implementation, and the original procedure that ignores the new primary key

PACKAGE BODY qdb_thread_mgr
IS
   PROCEDURE insert_thread (
      user_id_in               IN     PLS_INTEGER
    ,  parent_thread_id_in      IN     PLS_INTEGER
    ,  thread_type_in           IN     VARCHAR2
    ,  subject_in               IN     VARCHAR2
    ,  body_in                  IN     CLOB
    ,  thread_id_out               OUT PLS_INTEGER)
   IS
   BEGIN
      INSERT INTO qdb_threads (...)
           VALUES (...)
        RETURNING thread_id
             INTO l_thread_id;
   END;

   PROCEDURE insert_thread (
      user_id_in               IN PLS_INTEGER
    ,  parent_thread_id_in      IN PLS_INTEGER
    ,  thread_type_in           IN VARCHAR2
    ,  subject_in               IN VARCHAR2)
   IS
      l_id   PLS_INTEGER;
   BEGIN
      insert_thread (
         user_id_in               => user_id_in
       ,  parent_thread_id_in      => parent_thread_id_in
       ,  thread_type_in           => thread_type_in
       ,  subject_in               => subject_in
       ,  body_in                  => body_in
       ,  thread_id_out            => l_id);
   END;
END qdb_thread_mgr;

This is straightforward stuff, not rocket science. It just comes down to discipline and an aversion to repetition. Of course, sometimes it's a bit more of an effort to identify all the common logic and corral it into its own procedure. But it is a refactoring project that is well worth the effort.

CASE Expressions, Not Statements

One of things I like best about CASE over IF is that it comes in two flavors: a statement (like IF) and an expression. CASE expressions help me tighten up my code (check out this LiveSQL script for examples).

Consider the following function, which returns the start date for the specified period (month, quarter or year) and date.

FUNCTION start_date (
   frequency_in   IN VARCHAR2,
   date_in        IN DATE DEFAULT SYSDATE)
   RETURN VARCHAR2
IS
BEGIN
   IF frequency_in = 'Y'
   THEN
      RETURN TO_CHAR (ADD_MONTHS (date_in, -12), 'YYYY-MM-DD');
   ELSIF frequency_in = 'Q'
   THEN
      RETURN TO_CHAR (ADD_MONTHS (date_in, -3), 'YYYY-MM-DD');
   ELSIF frequency_in = 'M'
   THEN
      RETURN TO_CHAR (ADD_MONTHS (date_in, -1), 'YYYY-MM-DD');
   END IF;
END;

Hmmm. Methinks there's some repetition of logic in there. CASE expression to the rescue!

BEGIN
   RETURN TO_CHAR (
             CASE frequency_in
                WHEN 'Y' THEN ADD_MONTHS (date_in, -12)
                WHEN 'Q' THEN ADD_MONTHS (date_in, -3)
                WHEN 'M' THEN ADD_MONTHS (date_in, -1)
             END,
             'YYYY-MM-DD');
END;

Now I have a single RETURN statement (that always makes me breath a sigh of relief when I have to debug or maintain a function). But wait! I still see some repetition. Let's take another pass at this one.

BEGIN
   RETURN TO_CHAR (
             ADD_MONTHS (
                date_in,
                CASE frequency_in
                   WHEN 'Y' THEN -12
                   WHEN 'Q' THEN -3
                   WHEN 'M' THEN -1
                END),
             'YYYY-MM-DD');
END;

Now all repetition has been removed and CASE expression simply converts a period type to a number of months to "go back."

You can probably see that using CASE expressions is unable to result in some massive reduction in code volume (same with qualified expressions).

However:

  • Every little bit counts.
  • The more you get into the habit of paying attention to unnecessary code and finding ways to get rid of it, the more examples you will find.

Well, I bet you've got your own ideas for writing lean PL/SQL code. Let me know!

PL/SQL code style Database

Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Handling Automatic ID Generation in PostgreSQL With Node.js and Sequelize
  • Artificial Intelligence in Drug Discovery
  • ChatGPT: The Unexpected API Test Automation Help
  • Image Classification With DCNNs

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: