PL/SQL Programming with Real-World Examples

Set up oracle express edition and sql developer, create and connect the nfl schema with the provided script, and start services to establish a ready PL/SQL environment.

Explore PL/SQL primitive data types and syntax elements, from booleans and three-valued logic to char, varchar2, number, date, and lob types, plus assignment, delimiters, and schema references.

Explore user defined data types and structures in PL/SQL, including records, nested records, arrays, indexed by tables (associative tables), with guidance on memory use and when to use physical tables.

Explore flow control in PL/SQL, covering if, else if, and case statements, plus loops (while and for) with explicit and implicit cursors, reference cursors, and dynamic rescue.

Explore variable scope in a PL/SQL package, from global variables to scope-bound nested procedures, with hands-on examples. Learn error handling and debugging with rescue and safe user messages for production.

Explore PL/SQL triggers, focusing on a before-insert row-level trigger for the users table that enforces password strength, assigns a sequence-based user id, and outlines pitfalls and testing.

Explore primary key assignment in a master–child setup with before insert triggers and sequences, and prevent mutating triggers by moving logic into a package with white-box definition and black-box body.

Define a PL/SQL procedure that parses an external XML file into relational tables, including directory setup, inserting into games, and extracting values with extract value function.

Explore Oracle predefined packages and procedures, including the undocumented search_files in dbms_backup_restore, for listing external directory files, and master debugging with the rescue developer debugger and IDE.

Explore building a password strength function in PL/SQL, defining arrays for upper, lower, and symbol sets, applying length and mixed-character criteria, and comparing custom logic to online tools.

Develop a function that predicts the next nfl play from game conditions using user defined types, dynamic sql, and a weighted random number generator, with input from subject matter experts.

Master how PL/SQL packages encapsulate public and hidden routines, manage local scope data, and implement real-world tools like password auditing, email notifications, and device registration using context functions.

Review the PL/SQL course objectives and key topics, including primitive data types, syntax, real world examples, user defined types, flow control, scope, and triggers, procedures, functions, and packages.

Explore how to create and use PL/SQL procedures and functions with real-world examples, including parsing an external Ex-Im file into a relational table, and using directory objects and inline views.

Explore how to design and use PL/SQL packages, including signatures, bodies, overloading, and internal vs external structures, with real-world examples and debugging tips.