More Advanced PLSQL Programing Advanced Databases 1 Agenda

More Advanced PL/SQL Programing Advanced Databases 1

Agenda • Indexes – Not PL/SQL but a RBMS performance enhancement • PL/SQL – Functions – Cursor For Loops Advanced Databases 2

Indexes • Database object which provides capability to speed up the search process. Table ID Color 1 Red 2 Blue 3 Red 4 Red 5 Blue 6 Green 7 Red 8 Red Table Index on Color ID’s Blue 2, 5 Green 6 Red 1, 3, 4, 7, 8 Impacts on: Guide. SELECT? to Oracle. INSERT? 10 g UPDATE? DELETE? Advanced Databases 3

Indexes in SQL Guide to Oracle 10 g Advanced Databases 4

Why not Index Everything? • Every Index makes one of those “index tables” • If you have 10 indexes on a table, an INSERT, DELETE or UPDATE needs to write to that table + the 10 index tables. • Indexing speeds up reads at the expense of writes. Guide to Oracle 10 g Advanced Databases 5

Functions • Functions are stored procedures which return a value. • Functions are not executed like stored procedures. Instead, they are called from within SQL or PL/SQL. • SELECT myfunction() … Guide to Oracle 10 g Advanced Databases 6

Functions Guide to Oracle 10 g Advanced Databases 7

Cursor For Loops • Easier to implement cursors. • Simplified – no open, close, fetch or exit conditions! • If you need to use a cursor, use these. Guide to Oracle 10 g Advanced Databases 8

Questions Advanced Databases 9