Re: Teaching Oracle PL/SQL class

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

What planet will this class be on? Received on Tue Oct 16 2007 - 09:57:13 CDT