1 / 19

PL/SQL Startup Accelerator

PL/SQL Startup Accelerator. David Scott Intec Telecom Systems. Why use PL/SQL?. Fast Close Powerful Flexible Extensible Secure. Program. Traffic. DATA. PL/SQL. Traffic. Getting Started with PL/SQL. SQL*Plus and text editor TOAD Oracle PL/SQL Developer Third-party tools

MikeCarlo
Download Presentation

PL/SQL Startup Accelerator

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PL/SQL Startup Accelerator David Scott Intec Telecom Systems

  2. Why use PL/SQL? • Fast • Close • Powerful • Flexible • Extensible • Secure Program Traffic DATA PL/SQL Traffic

  3. Getting Started with PL/SQL • SQL*Plus and text editor • TOAD • Oracle PL/SQL Developer • Third-party tools • Shareware/Freeware • … and a database …

  4. Program units • Anonymous blocks • Used in SQL scripts • Functions and procedures • Smaller units • Triggers • Keep ‘em short! • Packages • Advantages vs. functions and procedures

  5. Uses • As program • In scripts • Called from other programs • Used in SELECT statement • Use in SQL*Loader

  6. Basic programming structure • Header • Parameters • Declare • Variables, cursors, records • Using %rowtype vs. records vs. variables • Begin • Executable statements • Exception • Error handling • Default behavior

  7. Controlling Execution • IF – THEN • CASE statements • Function and procedure calls • Loops • While • Numeric • Cursor

  8. Cursor FOR Loop: Summary Query FOR sumloop in (select order_nbr, sum(price) ext_price from items_sold where sale_dts is not null) LOOPupdate invoices set cost = sumloop.ext_price where order_nbr = sumloop.order_nbr; END LOOP;

  9. Using SQL in PL/SQL • DML statements are allowed • DDL statements are NOT allowed (unless…) • Passing parameters • WARNING: Privilege inheritance • EXECUTE IMMEDIATE: it's magic!

  10. Using Supplied Packages • Documentation • API approach

  11. Exception Handling • Scope: who deals with the problem? • Using ERRMSG • RAISE_APPLICATION_ERROR(-20001,’Text’) • Prebuilt exceptions • NO_DATA_FOUND DUP_VAL_ON_INDEX • INVALID_NUMBER VALUE_ERROR • TIMEOUT_ON_RESOURCE • Doc: PL/SQL User’s Guide and Reference

  12. Exception Example • WHEN no_data_found THEN some_variable_you_need := 0; • WHEN others THEN dbms_output.put_line(substr( ‘ERROR: ‘||SQLERRM),1,254) status := SQLERRM; log_data(my_proc,SQLERRM);

  13. Autonomous Transactions • “… a blessing and a curse” • Independent transaction • Does not cause parent transaction to commit; • Must include COMMIT; • PRAGMA AUTONOMOUS_TRANSACTION

  14. Common Errors • Syntax • Watch the ; ! , • Permissions • No inheritance from roles! (Until 10g.) • Logic • Inefficient processing • Re-inventing the wheel • Dealing with ‘Mutating Tables’

  15. Performance • Don't use row-by-row processing if you can do the same thing with a single SQL statement • Write efficient loops • Investigate BULK COLLECT and FORALL • Avoid EXECUTE IMMEDIATE for DML

  16. Hints, Tips, and Other Chocolate Chip goodies • Use the USER_* views, not DBA_* • Less permission issues • DBMS_OUTPUT pain • sql%rowcount • Overloading functions

  17. PL/SQL Books • Oracle PL/SQL Programming, Third Edition by Steven Feuerstein • Oracle PL/SQL Developer's Workbook by Steven Feuerstein • Oracle PL/SQL Best Practices by Steven Feuerstein • Expert One-on-One Oracle by Thomas Kyte • Oracle Database 10g PL/SQL Programming by Scott Urman, et al • Mastering Oracle PL/SQL: Practical Solutions by Connor McDonald, et al • Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming by Mike Ault, et al

  18. Other PL/SQL Resources • OTN: technet.oracle.com • PL/SQL Technology Center www.oracle.com/technology/tech/pl_sql/index.html • AskTom: asktom.oracle.com • Quest: pipetalk.quest-pipelines.com/default.asp?boardid=plsql • … and ‘Google’ PL/SQL!

  19. Questions? • David ScottDatabase Practice ManagerIntec Telecom Systemsdavid.scott@intec.us404-705-2966

More Related