Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


How to Write Sweet PL/SQL

Patterns and practices of great PL/SQL programmers

Bill Coulam

on 11 January 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of How to Write Sweet PL/SQL

Our Agenda Today
History, best use and future of PL/SQL

How to Write Sweet PL/SQL !
Contact Info:
Bill Coulam
Brief History of PL/SQL
Similar to Ada and Pascal
Oracle added to provide a procedural option around execution of SQL
v1 with Oracle 6 in 1991.
v2 added stored routines
v2.1 dynamic SQL
v2.3 added arrays
v8 bulk binds, autonomous tx
v9 record-based DML, CASE
v10 regexp, conditional compilation
v11 function cache
Best Uses of PL/SQL
Future of PL/SQL
Processing close to the data = fast!
Backend, timed or event-driven transformations, calculations or processing of large data
Consumption or production of data-filled files
Ensuring data access layer is free of SQL injection
When a trigger is the right choice
Ensuring business logic is kept in one place
Data access: DBA review, tuned, instrumented, monitored, easy to modify and redeploy
NoSQL is the buzzword of the day, but its uses are limited
PL/SQL like COBOL & C: too useful, too widespread, too ingrained to disappear soon
Adopted by IBM in DB2 9.7
PostresSQL made their pgPLSQL look alike
Oracle APEX is pure PL/SQL
Very likely Oracle will add to MySQL
Too Many Cooks in the Kitchen
Anti-Pattern 1
Establish a programming and style standard
Ensure the standard is adhered to
Use templates, automation and formatting tools to make it easy
TOAD Formatter
PL/SQL Developer Beautifier
SQL Developer Formatter
SQL Detective's Clear SQL
Instant SQL Formatter
etc. (one in most every tool now)
The Dung Beetle Ball
Anti-Pattern 2
Anti-Pattern 3

Anti-Pattern 4
Mona Lisa at the Mall
Anti-Pattern 5
Flying Blind
Anti-Pattern 6
Everyone's got their hand in the pot
Everyone thinks they're a great chef; no SPOA
Everyone adds favorite spices, ruining the dish
Eventually the code is so tangled, decorated and inconsistent, that it sours, becoming too costly to keep it in its current state.
Keep it Simple
Routine no longer than a page+
Routine does one thing and one thing well
Don't repeat anything
Create constants and table-driven literals and parameters
Make vertical functions private to package body
Make public functions for common business logic
Extract distinct SQL, business decision points and functions
Place in their own atomic routine
Starts innocently. Developer sees no harm in writing a few standalone routines. Then two of his friends follow suit, and they tell their two friends...
Within a few short years, the codebase is a tangled, unnavigable mess of standalone routines with no owners.
Legitimacy is a real concern, but they're all kept around "just in case."
No organization to the enterprise code base
Very little explanation as to who, when, why created
No idea of relationship to other routines
Blind to reuse of shared data, SQL and functions
Layer upon layer of functionality in a single proc
No opportunity for reuse
Lots of points of failure
Raised exceptions are onerous
Code is too long; difficult to grasp and modify
The complexity attracts bugs
The fixes pile up and accrete until the routine resembles a jungle, or a steaming heap
Best pulled apart and re-written the right way
Stick to a Standard
Studied scientifically for over 430 yrs
There have been many theories put forth to explain its existence and usage.
We're still not 100% sure everything its 4 phases of construction were used for.
Your code should not take 430 years to decipher either.
Leave better records than the builders of Stonehenge.
Document the Interface
Placeholders for explanation in templates
Each stored object should have a comment block explaining who, when and why.
Document assumptions and test them in the code
Document tricky parameters or usage, caveats, design notes, alternatives rejected and why
Everything Needs a Home
Everything goes in a PL/SQL package
If package becomes a dumping ground, the name was too generic.
Break up package into functional groups
Do not put all literals in one package; keep them grouped in the package spec to which they are more closely aligned.
Trigger and job code should also go in a package
Keeping the Mona Lisa in a location publicly accessible is unconscionable; she's been through enough already. Could get defaced like this.
The original belongs in a special, environmentally controlled and secure case.
If the original were damaged like this, there's no going back.
Database code objects are precious too. Keeping them or editing them in the database is an idea equally as bad as displaying da Vinci's masterpiece in the hall at the local mall.
Version the Source Code
Install and configure a reliable source code control system (Subversion, Git, RCS, CVS, PVCS, VSS, etc.)
Ensure it is used religiously.
Development and maintenance activities always begin with the source file, not the database object.
Some tools now default database object browsing to read-only mode. Have to force them in order to edit a DB object in-situ.
Which visual acuity would
for your
Yet this is exactly how 90+% of DBAs deploy code: with zero insight into the innards of production code.

No logging or monitoring
No auditing
No metrics
No history
No notifications
No ability to debug on the fly
When something goes wrong, it becomes a slow and costly, error-prone, misguided effort in heroics.
It SHOULD be as simple as opening logs and change history, and spending a couple minutes reading what happened, who did it, when it happened, and what data (if any) was involved.
If debugging is required, should be easy. Recompiling production PL/SQL to spit out DBMS_OUTPUT lines should be a badge of shame.
Instrument to Illuminate
Adopt an existing instrumentation library. Automatically gain:
Dynamic debugging (invisible and little overhead to production; but can be activated by changing a column value)
Change auditing
Proactive monitoring and email notifications of anomalous events
Recording of metrics
"Tag" sessions and long operations
Debug strings inherently comment the code as well!
Practices of Great PL/SQL Programmers
Get involved early
Model right, then make it friendly
Know and use your tools
Only handle expected exceptions
Peer review
Do it in Bulk or a Single SQL
Caller in charge of transaction
There’s this thing called “testing”...
The data model is the foundation of the structure. Ensure that at least the data model is done right.
Ignore those pleading for more friendly columns, quicker data access, elimination of joins.
AFTER the model is correct, normalized, and reviewed, then make it friendly with views, materialized views, updateable views, virtual columns, etc.
Model Right
Take 10 minutes a day to explore the user guide of a favorite tool
Learn and configure keyboard shortcuts
DBAs can really benefit from model visualization, nameless macros, data generation, debuggers, data ETL, code/table/schema/model/database comparison tools, and DDL generators.
Know Your Tools
Ban the use of WHEN OTHERS
Except when hiding the error is intentional
Only write exception handling for expected exceptions.
Use a standard way of logging and re-raising
Allow PL/SQL’s default exception raising and transaction rollback to handle everything else
Pair programming is fantastic
If not formally, quickly and informally get peer review for:
simple DML scripts
source code
design approach and assumptions
DB build manifest
whenever you’re stuck for more than 20-30 minutes
Another Set of Eyes
Quickest way to do something is not do it at all.

If performance is bad, ask...
“Do I really need this?”
"Can it be done another way?"
Next best is to do it in a single SQL statement
Followed by doing it with Bulk PL/SQL features (collections, bulk bind, FORALL, and DML with collections of record type)
Do it in Bulk
Let the client control the transaction
If you are writing a backend job that begins life in the database, then you will write the driver and control COMMIT vs. ROLLBACK.
Otherwise, leave it to database client software to decide.
Document requirements and assumptions in the interface
Write tests first, to the interface
Then write body, re-testing all cases as you add/modify the code to reach requirements.
Left with nice suite of re-usable tests
Test with typical load and data quality
Get involved in early stages of project work
Ask the questions of a data architect: length, uniqueness, longevity, audit, security, relationships, cardinality, etc.
Prepare conceptual and logical models for review and project discussions
Be aware of overall application architecture, hardware and human requirements

Get Involved

Contact me:
What is an Anti-Pattern?

“Something that looks like a good idea, but which
backfires badly when applied.” - Jim Coplien

"Anti-patterns are Negative solutions that present more problems than they address." - antipatterns.com

By understanding how NOT to do something, we can prevent it from happening again and recovering when it was mistakenly applied.
Full transcript