Oracle Database: PL/SQL Workshop

SKU: OCDBPLSQLW

668.160 kr.

This course provides a comprehensive guide to Oracle Database 19c PL/SQL programming. Participants will explore PL/SQL's capabilities to manage data, automate tasks, and develop scalable database solutions. Through a blend of theoretical concepts and practical exercises, learners will gain the skills to write robust, efficient, and maintainable PL/SQL programs.

Forkröfur

  • A basic understanding of relational database concepts.
  • Familiarity with SQL and basic querying techniques.
  • Prior experience with Oracle SQL Developer is recommended but not required

This course is ideal for:

  • Database developers and administrators seeking to enhance their PL/SQL programming skills.
  • Application developers who need to integrate PL/SQL with their software solutions.
  • IT professionals responsible for managing Oracle-based systems and automating database processes.

Nemandi mun læra eftirfarandi

By the end of this course, participants will be able to:

  • Understand the limitations of SQL and the benefits of PL/SQL.
  • Write efficient PL/SQL anonymous blocks and manage variable scope.
  • Use SQL statements within PL/SQL to interact with database objects.
  • Implement control structures for procedural logic.
  • Develop robust exception-handling mechanisms.
  • Create modular subprograms using stored procedures and functions.
  • Utilise advanced PL/SQL features like triggers, packages, and dynamic SQL.
  • Debug and optimise PL/SQL code for better performance.

Samantekt

Module one: Introduction to PL/SQL

  • Overview of PL/SQL runtime architecture.
  • Understanding PL/SQL block structure.
  • Benefits of using PL/SQL for database operations.

Module two: Declaring PL/SQL variables

  • Variable declaration, initialization, and scope.
  • Using the %TYPE and %ROWTYPE attributes.
  • Working with composite data types.

Module three: Writing executable statements

  • Lexical units and PL/SQL block syntax.
  • SQL functions and sequences in PL/SQL.
  • Best practices for writing readable and maintainable code.

Module four: Using SQL statements within PL/SQL

  • Executing SELECT, INSERT, UPDATE, and DELETE within PL/SQL.
  • Managing cursors and SQL cursor attributes.
  • Handling naming ambiguities in SQL statements.

Module five: Writing control structures

  • Conditional statements (IF, CASE).
  • Loops (FOR, WHILE, BASIC).
  • Nested and labelled loops with scope management.

Module six: Exception handling in PL/SQL

  • Types of exceptions (predefined, user-defined).
  • Using RAISE and RAISE_APPLICATION_ERROR.
  • Guidelines for structured exception management.

Module seven: Creating and managing subprograms

  • Differences between procedures and functions.
  • Parameter modes and their applications (IN, OUT, IN OUT).
  • Best practices for modular program design.

Module eight: Advanced PL/SQL features

  • Creating and using packages for modular programming.
  • Writing and managing triggers.
  • Implementing dynamic SQL with EXECUTE IMMEDIATE.

Module nine: Debugging and performance tuning

  • Using Oracle SQL Developer for debugging.
  • Managing dependencies and recompilation.
  • Optimising PL/SQL code using compiler warnings.