PL/SQL Startup Accelerator

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

• SQLPlus 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 SQLLoader

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 Users 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
• sqlrowcount
• 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.ht
  ml
• AskTom asktom.oracle.com
• Quest pipetalk.quest-pipelines.com/default.asp?bo
  ardidplsql
• and Google PL/SQL!

19
Questions?

• David ScottDatabase Practice ManagerIntec
  Telecom Systemsdavid.scott_at_intec.us404-705-2966