Lesson 1

Welcome to the second course in the Introduction to Oracle Certification Series, Programming in PL/SQL.
As the name implies, this series focuses on preparing you for certification in Oracle.

The information covered in this series helps you get started on the Oracle Certified Database Administrator and/or Certified Application Developer paths. Another important goal of this series is to enable you to

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

PL/SQL is an integral part of the Oracle database engine. This course will introduce you to PL/SQL and explain how you can build PL/SQL programming structures. PL stands for Procedural Language.
This language offers procedural techniques to build your SQL (Structured Query Language) commands.
You can use this language to construct procedures, triggers, functions, packages, and more.

PL/SQL (Procedural Language for SQL) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is integrated into the Oracle Database (since version 7), and it adds a wide array of procedural features, such as control structures, looping, exception handling, and more, to the powerful SQL database language. For a PL/SQL developer, understanding and effectively using the following primary PL/SQL constructs is crucial:

1. Blocks: The fundamental building units of PL/SQL applications are blocks, which can be anonymous or named (procedures, functions, packages, triggers, and so on). A block consists of a declaration section, an execution section, and an exception handling section.
2. Variables and Constants: These are used to store temporary data. Variables can change their value throughout the execution, while constants hold the same value once they are initialized.
3. Data Types: Understanding various data types (scalar, composite, reference, and LOBs) is crucial for declaring variables and constants effectively.
4. Control Structures: These include conditional statements (IF THEN ELSE) and loops (LOOP, WHILE, FOR), which control the flow of execution based on certain conditions.
5. Cursors: Cursors are used to process individual rows returned by database queries. Cursors can be implicit or explicit, and understanding how to manage them is key to handling data sets effectively.
6. Exception Handling: Exception handling allows a PL/SQL program to catch and process runtime errors or predefined and user-defined exceptions gracefully using the EXCEPTION block.
7. Procedures and Functions: These are named PL/SQL blocks that perform a specific task and can be invoked with parameters. Functions return a value, while procedures do not.
8. Packages: Packages are groups of related procedures, functions, variables, and other package constructs. They are used to encapsulate and organize related PL/SQL constructs into a single logical unit.
9. Triggers: Triggers are procedures that are automatically executed in response to certain events on a particular table or view, such as insertions, updates, or deletions.
10. Records: Records are composite data structures that can hold data from multiple fields of different data types, similar to a row in a database table.
11. Collections: Collections are single-dimensional arrays that can be indexed by integers or strings (Associative arrays, Nested tables, Varrays).
12. Dynamic SQL: It allows for the construction and execution of SQL statements dynamically at runtime, which is useful for writing flexible code.

Understanding and being able to effectively use these constructs allows a PL/SQL developer to write powerful, efficient, and secure database applications. Mastery of these elements is foundational for advanced database programming and application optimization in an Oracle environment.

After completing the course, you will be able to:

1. Describe the basic structure of a PL/SQL block
2. Create a nested PL/SQL block
3. Describe the different PL/SQL and non-PL/SQL variables
4. Identify the scope of variables when using nested blocks
5. Apply best practices for naming conventions and commenting your code
6. Gain an overview of the different PL/SQL datatypes, including composite datatypes such as PL/SQL RECORD and TABLE
7. Place identifiers and use literals and operators within a PL/SQL block
8. Build logic by using PL/SQL control structures, including loops, labels, and the GOTO statement
9. Use explicit cursors to process multiple records
10. Raise implicit and explicit exceptions

The bottom line is this:

1. Inside PL/SQL programs, the most flexible, and often the best-performing, of the collections is the index-by table.
2. Use nested tables when working with nested table data stored in the database. Nested tables are appropriate for large collections that the application typically stores and retrieves a portion of at a time.
3. Use VARRAYs when working with VARRAY table data stored in the database. This type of collection is appropriate for small collections that the application stores and retrieves in their entirety.

In this course, you will learn and practice PL/SQL skills using diagams, carousels and Slide Shows.

Programming in PL/SQL is the second of three courses in the introduction to Oracle Certification Series. In the next lesson, the Oracle PL/SQL Course Requirements will be discussed.