use Perl

• Paging (Score:3, Informative)

  by jdavidb (1361) on 2003.02.13 15:45 (#17000) Homepage Journal

  I presume you've already done this [perl.org], since you mentioned nesting queries three levels deep.

  Learn from how we do it in Perl. Got useful functionality that looks ugly, even hacking the language? Encapsulate it in a module. Abstract out the parameters you need (lower bound, upper bound, maybe table name and where clause if you want to use dynamic SQL), then stick it in a stored procedure.

  Oracle PL/SQL Best Practices [oreilly.com] says:

  • SQL-04: Put single-row fetches inside functions; never hardcode a query in your block.
  • SQL-06: Define multi-row cursors in packages so they can be used from multiple programs.

  Or, to generalize, encapsulate your queries in stored packages, procedures, and functions. Makes things reusable and, more importantly, nice and clean for you and your successors.

  --
  J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers

  Reply to This

  • Re:Paging (Score:3, Informative)

    by jdavidb (1361)

    Hmm, even better, from the beginning of the chapter:

    SQL-00: Establish and follow clear rules for how to write SQL in your application.

    • Never repeat a SQL statement.
    • 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 SQL.

    Okay, not all of that was relevant here, but the point was, once you've written the triple-nested select, encapsulate so from now

    --
    J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers