Stored Procedures

1
Stored Procedures

• By Oracle Bigot ? (Jon Nelson)

2
About me

• 7 years with Hormel Foods
• Oracle DBA 4-5 years
• Purchased Software
• Custom Development

3
Agenda

• Separation of Display and Business Logic
• Transactional Control
• Performance
• Scalibility
• Reuse
• Security
• Development Tools

4
Separation of Screen and Business Logic

• Cleaner ColdFusion Source
• Ability to call same logic from multiple
  architectures
• Ability to reface applications

5
Transactional Control

• Avoid ltcftransactongt
• Let the database handle the transactional control
• XML allows transactions to be entered in batches
  (with error checking and not a total rollback or
  half-completed transaction)

6
Performance
Better Network Performance
7
Performance

• Ability to return ONLY what is needed to the
  calling program AVOID CURSORS!!
• Code stays parsed, saves on database overhead
• Execute many statements from a single call
• Return multiple result sets in a single call
• Less over the line

8
Scalability

• Single point of maintenance
• Consistency among logic
• Ability to leverage ColdFusion and the Database

9
Reuse

• Reusable among disparate technologies and systems
• Ability to achieve many different results with
  the same query
• PROCEDURE GetEmps
• (v_emp_id IN NUMBER,
• v_output OUT HfcTypes.ref_cursor)
• IS
• BEGIN
• OPEN v_output FOR
• SELECT emp_id,
• emp_name
• FROM emp
• WHERE emp_id decode(v_emp_id,NULL,emp_id,v_e
  mp_id)
• END

10
Security

• Encapsulate business logic
• API to the database
• Limit access to data

11
Tools

• TOAD Available for both Oracle, SQL Server and
  DB2 users
• Most DBAs
• There are no other tools

12
The Perfect SQL Statement

• Soap-box Database performance, lazy is the key

13
How to build the perfect SQL Statement

• Start with small understandable components
• Use Custom Functions to reduce complexity
• Build upward
• Example BookingReport.BookingsByTradeGroup

14
Questions?

• Stored Procedures
• SQL
• DB Tuning