Here's a video I recorded of a webcast to a team of developers who had one hour to spare to learn about some PL/SQL best practices. I offer tips on using compile time warnings, avoiding hard-coding/repetition, using nested subprograms and in the performance arena, lightning fast coverage of FORALL, function result cache, NOCOPY and PRAGMA UDF. Fun stuff and here's the guide:
03:00 Compile-time warnings
16:25 Don’t repeat anything
21:15 Tips on writing SQL in PL/SQL
24:50 Avoid hard-coding of formulas
28:35 Keep executable sections tiny
35:15 Avoid row-by-row processing
42:10 Function result cache
54:28 NOCOPY parameter hint
56:02 Optimizing user-defined function execution in SQL
Files referenced in presentation may be found at http://www.oracle.com/webfolder/technetwork/tutorials/plsql/sfdemo.zip. But for the latest set of PL/SQL scripts, go to LiveSQL.oracle.com and search by keywords (forall, nocopy, etc.).
More on PL/SQL at my blog https://stevenfeuersteinonplsql.blogspot.com
PL/SQL quizzes, workouts and classes at https://devgym.oracle.com
Practically Perfect PL/SQL with Steven Feuerstein
Copyright © 2018 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.