Oracle PLSQL Best Practices by kgg14208


									Oracle PL/SQL Best Practices

  Best Practices for
     Writing SQL
  In Oracle PL/SQL
          Steven Feuerstein
    Quest Software,
        Scope and Challenge

 This seminar will not review tuning tips for SQL

 It will offer suggestions on how best to write SQL
  inside PL/SQL programs for maximum:
   – Readability
   – Maintainability
   – Efficiency
       What's the Big Deal?
 How you write SQL inside PL/SQL code is the most
  critical factor affecting the quality of PL/SQL-based
 Consider:
  – One of the reasons developers like PL/SQL so much is that
    it is so easy to write SQL inside a PL/SQL block of code
  – One of the most dangerous aspects of PL/SQL is that it is
    so easy to write SQL inside a PL/SQL block of code
 Paradox? Irony? SQL is, in fact, a sort of Achilles
  heel of PL/SQL development
      Why We Write PL/SQL Code
 The predominate reason you write PL/SQL programs is to
  interact with the database, which:
   – Is the repository of information that shapes your business
   – Is always changing
 The layer of PL/SQL code should support the data model
   – It should not disrupt your ability to maintain and work with that model
   – Common coding practices tend to do just that: make it extremely
     difficult to modify and enhance your code as the data structures
 The difficulties surface in two different areas:
   – Transaction integrity
   – Poor coding practices
               Transaction Integrity
                   the Hard Way
                      Typical Data Access Method

                                                         Each program must
                                 Order Entry             maintain transaction
                                  Program                     integrity
  Application software
 access data structures

                              Order          Item
                              Table          Table

 When a transaction consists of three updates, two inserts, a delete and six
  queries, how do you guarantee that each developer is going to get it right?
    The Dangers of Poor Coding
 If you are not very careful, it is very easy to write your
  code in ways that cause your code to break whenever
  a change occurs in the underlying structures
 Data Structures             Code
                   PROCEDURE calc_totals          This program is a
     Dept          IS                             ticking time bomb
                      v_dname VARCHAR2(20);      in my application...
                      v_ename CHAR(30);
                      SELECT dname, ename
                         INTO v_dname, v_ename
     Emp                 FROM emp, dept
                        WHERE ...;
   SQL in PL/SQL Best Practices
            The View from 30,000 Feet

 Never repeat an SQL statement in application code.
 Encapsulate all SQL statements behind a procedural
  interface, usually a package.
 Write your code assuming that the underlying data
  structures will change.
 Take advantage of PL/SQL-specific enhancements for
                 Never Repeat SQL
 Take the "acid test" of SQL in PL/SQL: Can you say "Sim!" to
  the following question?
     Do you know all the places in your code where an INSERT (or
        DELETE or UPDATE) occurs for your critical table(s)?

   – If the answer is "not really", then you probably repeat SQL, and you
     have essentially lost control of your application code base
 It is crucial that you avoid repetition of the same logical SQL
   – With repetition, comes variation, and with it excessive parsing.
   – Potentially significant impact on performance and maintainability.
              And sometimes you have to worry about
                   more than logical variations!
     Oracle Gives With One Hand,
        Takes With the Other
 Oracle sometimes improves things in ways that make it very
  difficult for us to take advantage of them
   – Consider what it takes to avoid excessive parsing of SQL
     statements. Compare the statements in Column A and Column B.
              Column A                               Column B
  SELECT COUNT(*)                           select count(*)
    FROM after_deforestation;           =     from after_deforestation;           ?
  BEGIN                                     BEGIN
     UPDATE favorites                          update favorites
         SET flavor = 'CHOCOLATE'                  set flavor = 'CHOCOLATE'
       WHERE name = 'STEVEN';           =        where name = 'STEVEN';           ?
  END;                                      END;

  BEGIN                                     BEGIN
     UPDATE ceo_compensation                   update ceo_compensation
         SET stock_options = 1000000,              set stock_options = 1000000,
          salary = salary * 2.0                        salary = salary * 2
       WHERE layoffs > 10000;           =        where layoffs > 10000;           ?
  END;                                      END;
   Quiz: Count those cursors!

What are the total number of cursors parsed
   by Oracle when those six statements
 (standalone SQL and PL/SQL statements)
               are executed?


           6   9      10       12    14

Analyzing the SGA with TOAD
  Crossing the Physical-Logical Divide
 When you write SQL, you must be aware of the physical
  representation of your code
   – Pre-parsed cursors are only used for byte-wise equal statements
     (analyzed using a hash of the SQL string)
   – White space (blanks, tabs, line breaks) make a difference – except
     when the SQL resides inside PL/SQL blocks
   – PL/SQL reformats SQL to avoid nuisance redundancy
 What can be done, however, about these kinds of logical
  BEGIN                                BEGIN
     UPDATE ceo_compensation              update ceo_compensation
        SET stock_options = 1000000,         set salary = salary * 2,
         salary = salary * 2                     stock_options = 1000000
      WHERE layoffs > 10000;               where layoffs > 10000;
   How to Avoid SQL Repetition
 You should, as a rule, not even write
  SQL in your PL/SQL (and Java and
  C and...) programs
   – You can't repeat it if you don't write it

 Instead, rely on pre-built, pre-tested,
  write-once, use-often PL/SQL
  encapsulations of SQL statements.
   – "Hide" both individual SQL statements
     and entire transactions.

 With this approach you can virtually guarantee transaction integrity!
    Transaction Integrity the PL/SQL Way
    A Method That Guarantees Integrity
                                                  The application calls a
                            Order Entry           packaged program to
                             Program               execute the logical

All business rules are
    embedded and
  maintained in the
                                                        The packaged code
                                                      communicates with the

                         Order       Item
                         Table       Table
          Oracle wrote PL/SQL for just this reason!
              Hide all SQL Behind
              Procedural Interface
 You can't watch over everybody's shoulders to "police"
  the construction of every SQL statement
   – You need to set policies and provide code to make it easy for
     developers to follow the rules – and write better code
 Here are some recommendations:
   –   Build and use table encapsulation packages
   –   Hide all single row queries behind function interfaces
   –   In particular, don't expose the dual table
   –   Move multi-row cursors into packages
    The Beauty of Table Encapsulation
                                     Update                 Application
             Employee                 Delete                  Code

 Store all of your SQL inside packages: one per table or "business
   – All DML statements written by an expert, behind a procedural interface,
     with standardized exception handling
   – Commonly-needed cursors and functions to return variety of data
     (by primary key, foreign key, etc.)
   – If the encapsulation package doesn't have what you need, add the new
     element, so that everyone can take advantage of it
   – Could create separate packages for query-only and change-related functionality
Encapsulation Package in DB Navigator
Using Code Assistant to Deploy Encapsulations
               Allow No Exceptions
Instead of this:                                                 TRUE STORY!
                                                           "I forced all programmers
  INSERT INTO employee                                      to use the encapsulated
     (employee_id, department_id, salary, hire_date)
                                                          INSERT, instead of writing
                                                            their own. Using Quest’s
     (1005, 10, 10000, SYSDATE);
                                                          SQLab, we determined that
Do this:                                                   this one insert statement
                                                               was executed over a
  te_employee.insert (                                    million times! It has been in
     employee_id_in => 1005, department_id_in => 10,        the SGA for over parsed
     salary_in => 10000, hire_date_in => SYSDATE);         one time and two weeks,
                                                          never aging out because it
Check dependency information to identify                     is called so frequently."
                                                                 -- Dan Clamage
program that rely directly on tables
   SELECT owner || '.' || name refs_table,
          REFERENCED_owner || '.' || REFERENCED_name table_referenced
 Minimal Encapsulation a Must!

 At an absolute minimum, hide every single row query
  behind the header of a function.
   – If you hide the query, you can choose (and change) the
     implementation for optimal performance.

 Best approach: put the function in a package, so you
  can take advantage of package-level data.
   – Very useful for data caching mechanisms
    Get Me the Name for an ID...
Instead of this....                  Instead of this....
DECLARE                               CREATE OR REPLACE PACKAGE te_employee
   l_name VARCHAR2(100);              AS
BEGIN                                    SUBTYPE fullname_t IS VARCHAR2
   SELECT last_name || ',' ||         (200);
      INTO l_name                        FUNCTION fullname (
      FROM employee                         l employee.last_name%TYPE,
    WHERE employee_id =                     f employee.first_name%TYPE
             employee_id_in;             )
    ...                                     RETURN fullname_t;
                                         FUNCTION name (
                                            employee_id_in IN
 DECLARE                                 )
    l_name te_employee.fullname_t;          RETURN fullname_t;
 BEGIN                                END;
    l_name :=                         / (
     And Never, Ever Expose the
            Dual Table
 The dual table is 100% kluge. It is astonishing that Oracle still
  relies on it within the STANDARD PL/SQL package

 Always hide queries against the dual table inside a function
   – We need to be optimistic: perhaps in Oracle12i the dual table will
     no longer be necessary
  Instead of this...                      Write this:

  BEGIN                                   BEGIN
     SELECT employee_id_seq.NEXTVAL          l_employee_id :=
        INTO l_employee_id                      te_employee.next_pkey;
         FROM dual;
  Write Code Assuming Change

                    Dependent                       Existing
  Data structure                     Re-compile
                     programs                      code base
    changes                         invalid code
                   marked invalid                    valid

 Use anchoring to tightly link code to underlying data
 Rely on bind variables inside SQL statements
 Fetch into cursor records
 Qualify all references to PL/SQL variables inside SQL
Anchor Declarations of Variables
 You have two choices when        Hard-Coded Declarations
  you declare a variable:          ename VARCHAR2(30);
                                   totsales NUMBER (10,2);
   – Hard-coding the datatype
   – Anchoring the datatype to
     another structure
 Whenever possible, use           Anchored Declarations
  anchored declarations rather     v_ename emp.ename%TYPE;
  than explicit datatype           totsales pkg.sales_amt%TYPE;

  references                       emp_rec emp%ROWTYPE;
   – %TYPE for scalar structures   tot_rec tot_cur%ROWTYPE;

   – %ROWTYPE for composite
              Examples of Anchoring
DECLARE                                                 The emp table
   v_ename emp.ename%TYPE;
   v_totsal config.dollar_amt%TYPE;             ename        VARCHAR2(60)
   newid config.primary_key;
BEGIN                                       empno              NUMBER
   . . .
END;                                        hiredate             DATE
                                                 sal           NUMBER

PACKAGE config
IS                                      Use %TYPE and %ROWTYPE
   dollar_amt NUMBER (10, 2);
                                         when anchoring to database
   pkey_var NUMBER(6);
   SUBTYPE primary_key
                                        Use SUBTYPEs for
      pkey_var%TYPE;                     programmatically-defined types
   SUBTYPE full_name IS
      VARCHAR2(100); -- Oracle8i
END config;                           aq.pkg
             Benefits of Anchoring
 Synchronize PL/SQL variables with database columns and
   – If a variable or parameter does represent database information in
     your program, always use %TYPE or %ROWTYPE.
   – Keeps your programs in synch with database structures without
     having to make code changes.
 Normalize/consolidate declarations of derived variables
  throughout your programs.
   – Make sure that all declarations of dollar amounts or entity names
     are consistent.
   – Change one declaration and upgrade all others with recompilation.

   Remember— Never Repeat Code
The Many Faces of Hard-Coding
  1      name VARCHAR2 (30);
  2      minbal NUMBER(10,2);

  3   BEGIN
  4      OPEN company_pkg.allrows (1507);

  5      FETCH company_pkg.allrows INTO name, minbal;

  6      IF name = ‘ACME’ THEN ...

 Which of these six lines of code do not contain an
  example of hard-coding?

      (1-6) or (3 - 6) or (3 and 5) or 3
         Fetch into Cursor Records!
       name VARCHAR2 (30);
       minbal NUMBER(10,2);
                                Wrong       Fetching into individual
      OPEN company_pkg.allrows;              variables hard-codes
      FETCH company_pkg.allrows               number of items in
          INTO name, minbal;                       select list

        IF name = ‘ACME’ THEN ...

        CLOSE company_pkg.allrows;
                                             Fetching into a record
Right                                            means writing
       rec company_pkg.allrows%ROWTYPE;
                                                   less code
      OPEN company_pkg.allrows;
      FETCH company_pkg.allrows INTO rec;    If the cursor select list
                                                changes, it doesn't
        IF = ‘ACME’ THEN ...        necessarily affect your
        CLOSE company_pkg.allrows;
      Avoid Hard-coding inside SQL
 Don't bury hard-coded values in your SQL statements.
   – Instead, move your cursors to a shared area and then rely on
     that version in all instances
 Here is some inefficient, hard to maintain code:
DECLARE                                              DECLARE
   CURSOR r_and_d_cur IS                                CURSOR marketing_cur IS
      SELECT last_name FROM employee                       SELECT last_name FROM employee
        WHERE department_id = 10;                            WHERE department_id = 20;
And what it should be:                                  OPEN marketing_cur;

   CURSOR name_cur (dept IN INTEGER)
      SELECT last_name FROM employee
       WHERE department_id = dept;
                                                             OPEN bydept.name_cur (20);
         Local variables
    also avoid multiple parses         bindvar.sql
Write SQL Efficiently in PL/SQL
 It's one thing to tune your SQL statements; it is quite
  another to write your SQL inside PL/SQL so that it
  executes as efficiently as possible
 We'll cover some of the most useful new features in
  Oracle8 and Oracle8i PL/SQL for improving SQL
   – The RETURNING Clause
   – BULK BIND and COLLECT (Oracle8i)
 This is, of course, is just a taste of the many things
  you can do to optimize SQL inside PL/SQL...
       Use the RETURNING Clause
 Oracle8 offers a new clause for INSERT and UPDATE
  statements: the RETURNING clause
   – Retrieve information from DML statement w/o a separate query
  Instead of this:
      INSERT INTO favorites VALUES (
          favorites_seq.NEXTVAL, 'STEVEN', 'ICE CREAM', 'CHOCOLATE');
      SELECT favorite_id, preference INTO l_favid, l_flavor
         FROM flavors
        WHERE name = 'STEVEN' AND type = 'ICE CREAM';

  Do this:
      INSERT INTO favorites VALUES (
         favorites_seq.NEXTVAL, 'STEVEN', 'ICE CREAM', 'CHOCOLATE')
      RETURNING favorite_id, preference INTO l_favid, l_flavor;
   Use Bulk Binding and COLLECT
 Oracle8i offers new syntax to improve the performance of
  both DML and queries. In Oracle8, updating from a collection
  (or, in general, performing multi-row DML) meant writing
  code like this:

     PROCEDURE reality_meets_dotcoms (deptlist dlist_t)
        FOR aDept IN deptlist.FIRST..deptlist.LAST
           DELETE emp WHERE deptno = deptlist(aDept);
        END LOOP;

                ―Conventional bind‖ (and lots of them!)
                       Conventional Bind
Oracle server

            PL/SQL Runtime Engine                         SQL Engine
          PL/SQL block
  FOR aDept IN deptlist.FIRST..
     deptlist.LAST                       executor            SQL
     DELETE emp
      WHERE deptno = deptlist(aDept);
  END LOOP;                                               executor

                                          penalty for many
                                          “context switches”
          Enter the “Bulk Bind”
Oracle server

            PL/SQL Runtime Engine                        SQL Engine
          PL/SQL block
  FORALL aDept IN deptlist.FIRST..
     deptlist.LAST                                          SQL
     DELETE emp                          executor
      WHERE deptno = deptlist(aDept);                    statement

                                          Much less overhead
                                          for context
          Use the FORALL Bulk Bind
 Instead of the individual DML operations, you can do this:
   PROCEDURE reality_meets_dotcoms (deptlist dlist_t)
      FORALL aDept IN deptlist.FIRST..deptlist.LAST
         DELETE emp WHERE deptno = deptlist(aDept);

 Some restrictions:
   – Only the single DML statement is allowed— If you want to INSERT
     and then UPDATE, two different FORALL statements
   – Cannot put an exception handler on the DML statement
 Use BULK COLLECT for Queries
                              CREATE OR REPLACE FUNCTION get_a_mess_o_emps

 BULK COLLECT                   (deptno_in IN dept.depno%TYPE)
                              RETURN emplist_t
 performs bulk bind of           emplist emplist_t := emplist_t();
                                 TYPE numTab IS TABLE OF NUMBER;
 results from SQL select         TYPE charTab IS TABLE OF VARCHAR2(12);
                                 TYPE dateTab IS TABLE OF DATE;
 statement                       enos numTab;
                                 names charTab;

 – Returns each selected         hdates dateTab;
   expression in a table of      SELECT empno, ename, hiredate
                                     BULK COLLECT INTO enos, names, hdates
   scalars                           FROM emp
                                    WHERE deptno = deptno_in;
                                 FOR i IN enos.FIRST..enos.LAST
                                     emplist(i) := emp_t(enos(i),
                                        names(i), hiredates(i));
                                 END LOOP;
                                 RETURN emplist;
           Tips and Fine Points
 Use bulk binds if you write code with these characteristics:
   – Recurring SQL statement in PL/SQL loop
   – Use of a collection as the bind variable or code that could be
     transformed to use a collection containing the bind variable
 Bulk bind rules:
   –   Can be used with any kind of collection
   –   Collection subscripts cannot be expressions
   –   The collections must be densely filled
   –   If error occurs, prior successful DML statements are NOT ROLLED
 Bulk collects:
   – Can be used with implicit and explicit cursors
   – Collection is filled starting at row 1
        Writing SQL in PL/SQL
 Never Repeat SQL
  – Maximize performance, minimize impact of change
  – Encapsulate your SQL statements behind a procedural
    interface (likely to be a PL/SQL package, but you could
    also use Java)
 Code for Change
  – Data structures change, data change. Accept it and
    build "contingencies" into your code
 Take advantage of PL/SQL's maturity as an
  extension to the SQL language.
    Q&A - Discussion

  Presentation available at

To top