On Oct 16, 10:57 am, "Tim Arnold" <timkarn..._at_comcast.net> wrote:
> "www.douglassdavis.com" <douglass_da..._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?- Hide quoted text -
>
> - Show quoted text -
Earth :-) Any other questions?
Received on Tue Oct 16 2007 - 10:48:37 CDT
