July 28, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Sample Chapter: Efficient PL/SQL

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

The Simplest Option is Often the Best

If you compare the amount of code required in PL/SQL to process SQL with that required in any other language, PL/SQL usually wins. Any time you can use less code to achieve the same result is a good thing in terms of development time and ongoing maintenance. Consider the following examples, taken from the standard documentation, that show the difference between using PL/SQL and ProC to retrieve a BLOB from one table and insert it into another. First, the PL/SQL version (all eight lines of it!).

PROCEDURE insert_blob_proc IS
  Blob_loc BLOB;
BEGIN
  SELECT ad_photo INTO Blob_loc
  FROM Print_media
  WHERE product_id =3106 AND ad_id=13001;.INSERT INTO Print_media
                         VALUES (2056,12001,Blob_loc);
END;

And now the ProC version.

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s \n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void insertBLOB_proc()
{
  OCIBlobLocator *Lob_loc;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /*Initialize the BLOB Locator:*/
  EXEC SQL ALLOCATE :Lob_loc;

  EXEC SQL SELECT ad_photo INTO :Lob_loc
    FROM Print_media WHERE product_id =2268 AND ad_id =21001;

  /*Insert into the row where product_id =3106 and ad_id =13001:*/
  EXEC SQL INSERT INTO Print_media
    VALUES (3106,13001,:Lob_loc);

  /*Release resources held by the locator:*/
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp ="pm/pm";
  EXEC SQL CONNECT :pm;
  insertBLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

The lower level OCI version of the code is even lengthier than the ProC one. Similarly, with many languages that can access a database, there are several mechanisms available on which SQL can be run and the results retrieved. More often than not, the mechanism that provides the quickest or simplest coding path is not the one that provides optimal efficiency. For example, in Java, the Statement and PreparedStatement classes are readily available for processing SQL statements. For the majority of applications, the most efficient way to write JDBC applications that access Oracle is to use the PreparedStatement.(You'll see the reason for this in the "Achieving Efficiency" section, later in this chapter.) However, this requires more code from the developer, and not surprisingly, developers often take the shortest distance between two points and code with the Statement method.

With PL/SQL, the quick and simple coding solution for the processing of SQL also turns out to be the optimal way. You have to go out of your way to force PL/SQL to process SQL in a way that is not optimal. We'll be exploring this point later in this chapter. But first, we need to define what we mean by efficiency.





Page 2 of 19



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel