EE-IRM631
Database programming language
| Evaluation system |
|---|
| CC + Exam |
| Credits |
|---|
| 4 ECTS |
| Generic Skills (+) |
||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| . | . | |||||||||
| Specific Skills (+) |
||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |
| . | . | . | ||||||||
List of expected learning outcomes :
- Master the PL/SQL language
- Know how to implement procedures and stored functions.
- Know how to handle cursors, exceptions and triggers
Prerequisites: Algorithms & Data Structures (EE-IRM511), Relational Databases (EE-IRM532).
Keywords: PL/SQL.
Teaching aim: This course introduces Oracle PL/SQL and the advantages of this powerful programming language. It shows how to create anonymous PL/SQL blocks and describes stored procedures and functions. It also covers the declaration of identifiers and the interception of exceptions and triggers.
Teaching content :
- Lesson 1: PL/SQL Environment
- Section 1: Block structures of a PL/SQL program
- Section 2: Declaration section of a PL/ block
- Section 2.1: Declaration of local variables of type Oracle and PL/SQL
- Section 2.2: Declaration of Oracle and PL/ type constants
- Section 2.3: Tables
- Section 2.4: Declaration of Dynamic Types
- Section 2.5: Declaration of external variables to PL/SQL
- Section 3: Execution section of a PL/SQL block
- Section 3.1: Assignment instruction
- Section 3.2: External Exchanges
- Section 3.3: Control Structures iv. Section 3.4: SQL statements in a PL/SQL program
- Section 3.5: The PL/SQL language and the SELECT statement
- Lesson 2: Subprograms
- Section 1: Block nesting
- Section 2: Declaration of a procedure
- Section 3: Declaration of a function
- Section 4: Passing parameters
- Section 5: Calling procedures and functions
- Section 6: Stored procedures and functions
- Section 7: Packages
- Lesson 3: Cursors
- Section 1: Explicit Cursor Management
- Section 2: Implicit cursors
- Section 3: Parameterized Cursors
- Lesson 4: Exceptions
- Section 1: Pre-defined exceptions
- Section 2: User exceptions
- Section 3: Error handling by the environment
- Lesson 5: Triggers
- Section 1: Principle
- Section 2: Characteristics
- Section 3: Description of a trigger
- Section 4: Typology of triggers
- Section 5: Creating a Trigger
- Section 6: Event combinations
- Section 7: Access to lines being modified
Possible projects to be carried out: Management of a library Basic manual(s): Coursepack and interactive course
Bibliography :
[ 1 ] Bales, D. "PL/SQL from Novice to Professional", Apress, New York, 2007
[ 2 ] Feuerstein, S., Pribyl, B. "Oracle PL/SQL Programming", O'Reilly edition, 2009
[ 3 ] Feuerstein, S. "Oracle PL/SQL, programming guide", O'Reilly edition, 1997
