M1 Informatique – Database programming

Department of Computer Science and Statistics, ICOM, Université Lumière Lyon 2
Year 2015-2016

Prerequisites

• Knowledge of database design
• Knowledge of SQL language

Objective

Mastering advanced interrogation of databases through programming. Applications in Oracle PL/SQL.

Outline

1. Introduction
2. Variable and constant declaration
3. Operators and control structures
4. Abstract data types
5. Cursors
6. Exceptions
7. Subprograms
8. Stored procedures and packages
9. Triggers
10. Dynamic SQL

Organization / Schedule

• Lecture (4 sessions – 7 hours): Tuesday, from September 15 to October 6, 10:00am-11:45am, Room H010 (Jérôme Darmont)
• Labwork (8 sessions – 14 hours): Tuesday, from October 20 to December 15
  • Group #1: 12:00am-1:45pm until November 10, 10:00am-11:45am from November 17 on (Jérôme Darmont), room i212
    Session of November 24 rescheduled on November 25, 12:00am-1:45pm
    Session of December 8 rescheduled on December 1, 12:00am-1:45pm
    Session of December 15 rescheduled on December 16, 10:00am-11:45am
  • Group #2: 12:00am-1:45pm (), room i201
    Session of December 8 to be rescheduled
  • Group #3: 10:00am-11:45am (), room i201
    Session of December 8 to be rescheduled

Assessment

Labwork reports (40%) + individual project (60%).

On-line material

• Lecture’s slides (356 Ko)
• English-French lexicon (111 Ko)
• SQL reference (114 Ko) (in French)
• Labwork #1 – Simple queries and cursors (56 Ko)
• Labwork #2 – Ad-hoc cursors (49 Ko)
• Labwork #3 – Abstract data types (49 Ko)
• Labwork #4 – Subprograms (50 Ko)
• Labwork #5 – Packages (50 Ko)
• Labwork #6 – Dynamic SQL (49 Ko)
• Labwork #7 – Dynamic cursors (53 Ko)
• Labwork #8 – Triggers (51 Ko)

Bibliography / Webography

• Steven Feuerstein, Bill Pribyl, Oracle PL/SQL Programming Second Edition, O’Reilly, 1997
• Using Oracle PL/SQL, Jeff Ullmann, Stanford University
• PL/SQL Tutorial
• SQL Language Reference, Oracle
• PL/SQL Language Reference, Oracle

Other on-line resources

• SQL Developer (free Oracle client) – Connection information:
  • Connection name: your choice
  • User name: your login (the one you use in class)
  • Password: your password (still the one you use in class)
  • Host: disora.univ-lyon2.fr
  • Port: 1521
  • SID: bdetu