Lesson 1

The three courses listed below

1. Oracle SQL Extensions
2. Programming PL/SQL
3. PL/SQL Stored Objects

will help you get started on the Oracle Certified Database Administrator or the Certified Application Developer.
The three courses listed above will

1. enable you to create advanced SQL queries,
2. write beginning to intermediate PL/SQL blocks, and
3. become familiar with Oracle's unique SQL extensions.

PL/SQL is an integral part of the Oracle database engine and this course will introduce you to PL/SQL and explain how you can build PL/SQL programming structures.
The term PL stands for Procedural Language and this language offers procedural techniques to build your SQL commands. You can use this language to construct

1. packages,
2. procedures,
3. triggers,
4. functions.

PL/SQL Stored Objects refer to a collection of database objects in Oracle databases that are written in PL/SQL (Procedural Language/Structured Query Language). Here's a breakdown of what these objects typically include:

1. Stored Procedures: These are named blocks of code that can be executed as a single unit. They can accept parameters, return multiple values, and perform complex logic.
2. Functions: Similar to stored procedures, but functions always return a value and can be used in SQL statements like any other expression.
3. Packages: These are groups of related procedures, functions, and variables. Packages allow for better organization of code and can include both public (visible) and private (hidden) elements.
4. Triggers: These are special kinds of procedures automatically executed ("triggered") by the Oracle engine in response to certain events on a table or view, such as INSERT, UPDATE, or DELETE operations.
5. Views: While views themselves are not written in PL/SQL, they can be associated with PL/SQL in the form of triggers or functions used within the view definition.
6. Sequences: Not PL/SQL per se, but often used within PL/SQL for generating unique numbers, like for auto-incrementing keys.
7. Synonyms: These are aliases or alternate names for objects, simplifying their reference in code.
8. Types: This includes user-defined types like objects, collections (VARRAY, nested table), and REF CURSOR types which can be used in PL/SQL programs for complex data structures.
9. Indexes: While primarily for performance, certain types like function-based indexes involve PL/SQL functions in their definitions.

Each of these objects can be stored in the database and can be reused, which helps in:

• Modularity: Code can be written once and called multiple times from different parts of the application.
• Performance: Since these are compiled and stored in the database, they execute more efficiently.
• Security: Access to these objects can be controlled independently, enhancing database security.

These stored objects are central to the efficient operation of database-centric applications, allowing for complex logic handling directly at the database level, which can be particularly useful for maintaining data integrity, performing business logic checks, and improving application performance by reducing network traffic.

After completing this course, you will be able to:

1. Create stored PL/SQL objects
2. Create your own customized functions that can be used in queries
3. Add triggers to your tables to assure that rows inserted into the tables contain valid data
4. Create a PL/SQL procedure, which encapsulates logic and can be executed simply by calling it by name
5. Wrap a set of procedures and functions into a single container called a package
6. Set up security to allow or not allow access to your database objects, including the procedures, functions, and packages that you create in this course

The next lesson explains prerequisites for this course.