Re: Teaching Oracle PL/SQL class


"www.douglassdavis.com" <douglass_davis@xxxxxxxxxxxxx> wrote in message
news:1192468057.404855.322550@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?


.

Relevant Pages

  • Re: Teaching Oracle PL/SQL class
    ... PL/SQL is used in industry. ... Common Uses? ... Tight Integration with SQL / Higher Productivity ... Subtopics ...
    (comp.databases.oracle.server)
  • Re: Teaching Oracle PL/SQL class
    ... I am teaching an Oracle PL/SQL class. ... Common Uses? ... Tight Integration with SQL / Higher Productivity ... Parameterized Cursors ...
    (comp.databases.oracle.server)
  • Teaching Oracle PL/SQL class
    ... I am teaching an Oracle PL/SQL class. ... Common Uses? ... Tight Integration with SQL / Higher Productivity ... Parameterized Cursors ...
    (comp.databases.oracle.server)
  • Re: Slight "I have some string, how lng it it, BTW, its blue" question
    ... can you expand on what you mean by 'Using cursors to do your join'? ... As I stated before....if you can do it in SQL, choose that approach over PL/SQL. ... So if you have a view call a view, and your write SQL to reference the first view, Oracle will merge all the views into your SQL statement and then execute the entire thing as one large SQL statement. ...
    (comp.databases.oracle.misc)
  • Re: Slight "I have some string, how lng it it, BTW, its blue" question
    ... the rule of thumb I use is if it can be done with SQL ... If not, then use PL/SQL. ... can you expand on what you mean by 'Using cursors to do your join'? ... flexible that a COBOL programmer can write COBOL in PL/SQL. ...
    (comp.databases.oracle.misc)