Teaching Oracle PL/SQL class

From: www.douglassdavis.com <douglass_davis_at_earthlink.net>
Date: Mon, 15 Oct 2007 10:07:37 -0700
Message-ID: <1192468057.404855.322550@k35g2000prh.googlegroups.com>

Hello,

I am teaching an Oracle PL/SQL class. The class will be 9 nights, < 3 hours each night.

Outline that I have so far for class is here:

http://plsqltopics.wikispaces.com/

I am in search of the most important topics to cover based on how the PL/SQL is used in industry. I also am in search of tasks that can be done in a lab type setting that would be close to those done in industry but not take more than 30 minutes each. If you have any comments, you may add to discussion section. If there are any topics you deem important that have been missed, you may add them directly to the wiki (note: you may have to go into text mode to edit outline).

Thank you.

Here is what I have so far (for new updates please go to http://plsqltopics.wikispaces.com/ ):

Nine Day Class outline

      Day 1 - SQL, SQL*Plus, and PL/SQL overview (ch 1,2,3, start 4)
         1. Topics
               1. SQL Review
               2. About PL/SQL
               3. Sql*Plus
               4. console input/output
               5. anonymous blocks
               6. variables
               7. Anchored Types
               8. SELECT
         2. Why SQL*Plus? Common Uses?
               1. connect to DB
               2. utilities: logging/running scripts/substitution vars
         3. Why PL/SQL? Common Uses?
               1. used for procedural tasks
               2. Tight Integration with SQL / Higher Productivity
               3. Runs on any Oracle DB
               4. Tight Security/ App code on server/ can be
obfuscated
               5. Access to Pre-defined Packages
               6. Support for Object-Oriented Programming
               7. Support for Developing Web Applications and Pages
               8. efficient/less network traffic than doing on client
               9. can be stored on server or client
         4. Subtopics
               1. Theory: set oriented/4th Gen vs. procedural/3rd Gen
               2. How PL/SQL works w/ server
               3. Sql*Plus: screen size, logging, running scripts,
substitution vars
               4. PL/SQL block structure
               5. server output
               6. retrieving errors and warnings
               7. variables
               8. naming conventions
               9. Anchored types / single point of definition
              10. SELECT INTO. errors w/too many/too few rows
   2.
      Day 2 - More SQL and Selection/Decision ( ch4 (continued), 5,6)
         1. Topics
               1. Continue SELECT, Other SQL in PL/SQL
               2. IF Statements
               3. Case
         2. Why If/Case? Common Uses?
               1. Selection
         3. Subtopics
               1. types of SQL that can go in PL/SQL
               2. FOR UPDATE [OF] and Oracle Concurrency
               3. logical operators
               4. comparing nulls
               5. ELSIFS - mk mutually exclusive
               6. PL_INTEGER
               7. CONSTANTS
               8. nested blocks (and use w/if to save resources)
   3.
      Day 3 - Procedures, Functions, Start Exception Handling (ch 7,
12, 13)
         1. Topics
               1. i. Start Exception Handling
               2. ii. Procedures and Functions
         2. Why Procedures/Functions? Common Uses?
               1. Store Code for later use
               2. Hide SQL/ provide interface to SQL
               3. Utilities to be run at certain times
         3. Why Exception Handling? Common Uses?
               1. uniform way to represent errors
               2. Catch/handle errors
               3. Catch errors/re-report
         4. Subtopics
               1. named notation for actual params (arguments)
               2. getting stored code info
               3. scope
               4. NOCOPY
               5. params and in/out/in out
               6. tiggers for more than one DML: INSERT/UPDATE
   4.
      Day 4 - Start Loops and Cursors(ch 8,9)
         1. Main Topics:
               1. Loops: Simple/While/For
               2. Start Cursors
               3. Parameterized Cursors
         2. Why loops? Common Uses?
               1. repetition
               2. process items w/cursors
               3. process table (array) data struct
         3. Why cursors? Common Uses?
               1. process SQL statement
               2. process lists of data
         4. Subtopics
               1. when to use each type of loop
               2. ORA-01555 snapshot too old
               3. nested loops
               4. record types
               5. cursor attributes
   5. Day 5 - Advanced Cursors and Bulk SQL (ch 15)
         1. Topics
               1. Advanced Cursors:
                     1. parameterized
                     2. Ref Cursors
               2. Bulk SQL
                     1. FORALL
                     2. BULK COLLECT
         2. Why Bulk SQL Used? Common Uses?
               1. FORALL - issue many queries at once
               2. BULK COLLECT - retrieve results at once instead of

one row at a time.

more efficient

Why Ref Cursors Used? Common Uses?
pointer to cursor
can pass reference to cursor to different program units.
Subtopics 1. Day 6 - Triggers (ch 17)
Triggers
Why Triggers? Common Uses?
Automatically generate derived column values
Prevent invalid transactions
Enforce complex security authorizations
Enforce referential integrity across nodes in a distributed database
Enforce complex business rules
Provide transparent event logging
Gather statistics on table access
Modify table data when DML statements are issued against views
Publish information about database events, user events, and SQL statements to subscribing applications
Auditing (FGA replaces many uses)
enforce complex security rules
Modify table data when DML statements are issued against views
Subtopics
mutating tables
table/system/DDL
row /statement / instead of
before/ after
insert /update/delete
NEW / OLD
how to find stored code 2. Day 7 (ch 14, 21) - Creating Packages and Oracle supplied packages
Main Topics
packages
oracle supplied packages
Why Use? Common Uses?
Define related types/cursors/constants/exceptions/ procedures/functions in one place
Loaded once instead of for each procedure
Encapsulation - members can be public/private. separate interface/implementation
initialization -1st time package used in session can run initialization code
Subtopics
do interface 1st.
put types in package
overloading
set/get on vars
oracle packages: regular expressions, UTL_FILE, DBMS_JOB, DBMS_FGA (?) 3. Day 8 (ch 10, 11, 20) - Advanced Exceptions and Dynamic SQL
Topics
Advanced Exceptions
Execute immediate
bind vars
Autonomous transactions
Why execute immediate? Common Uses?
When you don't know what SQL will look like
Why bind vars? Common Uses
automatic w/ most SQL in PL/SQL
for efficiency especially w/ execute immediate.
Why Autonomous transactions? How used?
used when absolutely necessary
error logging
Subtopics
error handling conventions/strategies / packages to handle errors
preventing injection attacks (and assert)
parsing SQL and efficiency
encapsulating dynamic SQL/catching errors
invoker rights/when to use
EXCEPTION_INIT
RAISE_APPLICATION_ERROR
AUTONOMOUS_TRANSACTION
SQLERRM /SQLCODE 4. Day 9 (ch 18,19) - Collections and Records
Topics
Collections
Records
Why advanced data types? Common Uses?
easier to do certain algorithms
pass data back via collections from procedure
Subtopics

--
http://www.douglassdavis.com

Received on Mon Oct 15 2007 - 12:07:37 CDT