SQL Is Bad!Step 1. Hardcoding is bad.Step 2: Every SQL statement you write is a hardcoding.Step 3. Draw the logical conclusion.General SQL Best PracticesThe best way to avoid problematic code is to not write
it.Hide your SQL statements behind a programmatic
interface.Problem: PL/SQL developers are addicted to SQL!Solution: Never repeat a SQL statement; instead, implement
SQL behind procedures and functions.Never hardcode or expose a query in your application-level
code.Encapsulate INSERT, UPDATE, and DELETE statements behind
procedure calls.The path to an effective programmatic interface for your
SQL.You may write PL/SQL code, but SQL always takes
precedence.Qualify PL/SQL variables with their scope names when referenced
inside SQL statements.Problem: Global search-and-replace to the rescue!Solution: Always check after global search-and-replace, and
qualify all variable references.When one transaction is not enough.Use autonomous transactions to isolate the effect of COMMITs
and ROLLBACKs.Problem: Your error log entries have disappeared!Solution: Save your log information separately from your
business transaction logic.I don’t always want to save my changes.Don’t hardcode COMMITs and ROLLBACKs in your code.Problem: Is everything Lizbeth does in her programs some kind
of hardcoding?Solution: Call your own program to do the commit (or
rollback), and make it more flexible.ResourcesBest Practices for Querying Data from PL/SQLIt’s always better to fetch items into a single basket.Fetch into cursor records, never into a hardcoded list of
variables.Problem: A change in one place affects many others—never a
good idea.Solution: Skip all those declarations and replace them with a
single record.Answer the question being asked; that is, be a good
listener.Use COUNT only when the actual number of occurrences is
needed.Problem: Delaware is not a good listener.Solution: Use COUNT only when you need to know “How many
rows?”Your code makes my head spin.Don’t use a cursor FOR loop to fetch just one row.Problem: Jasper chooses the wrong time to be lazy.Solution: Use the cursor FOR loop only when fetching multiple
rows.Best Practices for Changing Data from PL/SQLAssume the worst!Don’t forget exception handlers for your DML
statements.Problem: Jasper knows what he’s supposed to do. He just can’t
bring himself to do it.Solution: Make sure that any errors that can be anticipated
are logged and communicated to the user.Things only get more complicated over time.List columns explicitly in your INSERT statements.Problem: It’s hard to think about what a table will be like
in the future.Solution: Always explicitly list the columns that are part of
the INSERT statement.Timing is everything in the world of cursors.Reference cursor attributes immediately after executing the SQL
operation.Problem: I check the contents of the SQL%ROWCOUNT too late in
the game.Solution: Remember that SQL% attributes always refer to the
most recently executed implicit cursor in your session.Best Practices for Dynamic SQLMake it easy to untangle and debug your dynamic SQL
statements.Always parse a string variable; do not EXECUTE IMMEDIATE a
literal.Problem: There’s something wrong with Lizbeth’s dynamic SQL,
but she can’t figure it out.Solution: Make sure your exception section can display the
string that failed to parse.Give the RDBMS a break.Avoid concatenation of variable values into dynamic SQL
strings.Problem: You have lots of dynamic SQL updates, with different
values each time.Solution: Bind, don’t concatenate, to optimize performance
and simplify dynamic string construction.So you think you know what users might do with your code?Do not allow malicious injection of code into your dynamic
statements.Problem: So many teenagers, so many ways to enter data at the
web site.Solution: Avoid concatenation of SQL text, rely on bind
variables, and secure your schemas.It’s rude to drop someone else’s objects.Apply the invoker rights method to stored code that executes
dynamic SQL.Problem: A seemingly handy utility goes badly wrong.Solution: Make sure your dynamic SQL programs run under the
invoker’s authority.Resources