Oracle PL/SQL Fundamentals vol. I & II

Set up Oracle environment by installing Oracle X-C and SQL developer, configure password and listener ports, and use Sequel Plus or Sequel Developer to run scripts creating and populating tables.

Explore Oracle's built-in database tools, learn to configure host, port, and SID connections, implement authentication, use bind variables, and work efficiently with SQL Developer.

Explore how PL/SQL merges the declarative SQL with procedural features, unlocking portability, simple syntax, and performance while covering deployment options and the structure of a PL/SQL program block.

Explore PL/SQL blocks: declare optional, begin mandatory, end, and optional exception; implement for next loops, manage data with commit and rollback, and use single-line and multi-line comments.

Explore sql plus in Oracle pl/sql fundamentals, focusing on data manipulation language (dml), program blocks with declare and begin, and using dbms_output with set server output on for visible results.

Explore declare clause objects in Oracle PL/SQL, including variables, booleans, constants, records, tables, and user defined types, the temporary storage they hold, and naming rules.

Explore Oracle PL/SQL available data types, including integer variants (binary/pos/simple), floating point, number, and character types (char, varchar2), plus date, boolean, rowid, and synonyms, with practical usage notes.

Declare simple data types in PL/SQL and use the colon equals operator for assignments, defaults, and not null constraints. Define constants and booleans, and use exceptions to enforce valid values.

Explore complex data types and the type command with percent type to bind variables to a table field’s data type via schema references, adapting automatically when the table changes.

Declare a percent ROE row type to capture an entire table row, including all fields and data types, and access fields with dot notation to assign or fetch data.

Create a type for a single-dimensional table, declare a variable using that structure, and populate memory with an array of employee SSNs for queries.

Declare and use a custom record in Oracle PL/SQL to combine fields from the employee and department tables, add a bonus, and populate active and inactive records via select statements.

Create and declare user defined types as hierarchical records, such as bonus compensation and employee records, then populate them with cash payments, company cars, and vacation weeks.

Learn what can be included in the begin clause to manipulate database and internal data, access declared objects, and apply logic and branching with SQL, regular expressions, and literals.

Learn to work with char and varchar2 variables in Oracle PL/SQL by assigning values, concatenating strings with the || operator, and handling string literals and regex-based searches.

Learn how to handle string literals in Oracle PL/SQL using the cue literal to safely include apostrophes in messages, avoiding end-of-string errors and ensuring valid user prompts.

Learn how to work with numeric variables in Oracle PL/SQL, assign values with :=, perform math using salary raise calculations, and prompt for numeric input while preventing non-numeric errors.

Discover how oracle stores date values as numeric data and declare date variables with identical types, then calculate next dates by adding days using the current date.

Explore boolean values, including true, false, and null, and learn to initialize booleans, test conditions with parentheses, and use if statements to act on high paid outcomes.

Learn to use comparison operators in PL/SQL, including equals, not equals, is null, like, in, and between, and see how these conditions are evaluated.

Discover how SQL functions such as max, min, upper, lower, and round integrate with PL/SQL scripts, leveraging select into and DBMS output to format data and calculations.

Explore SQL DML within PL/SQL by performing inserts, updates, deletes, and locks, and manage transactions with commit, rollback, savepoint, and set transaction.

Learn how select into handles one record versus multiple records, and why the declared type must match the select fields; explore why explicit cursors are needed for multiple rows.

Explore how Oracle PL/SQL handles exceptions in select into statements, including no data found and too many rows, and how aggregate functions simplify results without errors.

Explore how sequences generate unique values for primary keys in Oracle PL/SQL. Learn creating, referencing nextval, and using sequences in inserts, with scaling considerations and triggers.

Delve into logic control and branching in PL/SQL, cover program labels, the goto statement, and loop structures. Master if-then-else and the case statement, including its two flavors, to build applications.

Master using the goto statement to jump to a labeled location in a PL/SQL script. Adhere to label rules, angle bracket usage, and the requirement that the label exists.

Master PL/SQL loops, including for next, fixed iteration and reverse loops, with exit, exit when, continue, and end loop syntax.

Learn to build an indefinite loop in PL/SQL using the loop and exit when constructs, track remaining records with a count, and consider test-at-end versus test-at-start pitfalls.

Master the conditional loop by testing the condition before each iteration, using a while loop to control flow, and contrasting it with indefinite and for next loops in Oracle PL/SQL.

Master nested loops in Oracle PL/SQL to generate complex data, using outer and inner loops to populate a transactions table with buy and sell records.

Learn how the if-then-else construct in PL/SQL tests binary conditions, uses else and else if, supports nested blocks, and handles case sensitivity with upper or lower functions.

Use the pl/sql case statement to cleanly branch complex logic, choosing between selector and searched flavors for single or multiple conditions, with an else to avoid not found errors.

Explore the exception clause in PL/SQL to trap and handle errors, including types of exceptions, user declared exceptions, and nesting, ensuring program flows continue smoothly.

Trap errors in PL/SQL using the when clause after the exception, testing single or multiple errors, with a global when others then handler to roll back on any unhandled exception.

Identify common Oracle PL/SQL errors and handle them with when clauses, including no data found, too many rows, invalid cursor, and case not found.

Learn how to create user defined exceptions in Oracle PL/SQL with pragma exception_init, map names to error codes (like -1407), and handle them in an exception clause.

Explore error handling in PL/SQL by comparing user defined exceptions via pragma exception and the global when others, and using SQLCODE and SQLERRM for precise debugging.

Explore how Oracle automatically creates an implicit cursor for every dml statement, exposing attributes like found, not found, row_count, and is_open, and contrast it with explicit cursors.

Declare a user defined event by creating an exception object, raise it to enforce a business rule, and handle it in the exception clause to update salaries.

Explore explicit cursors in oracle pl/sql by declaring, opening, fetching, and closing records, tracking found and not found status, and understanding their advantages and limitations for row by row processing.

Explore explicit cursor attributes alongside implicit cursors, and learn to use is open, found, not found, and row count to control fetches and output.

demonstrates using the continue statement inside a loop to skip processing and fetch the next record, and compares with exit when for termination, including null SSN filtering for performance.

Explore updateable cursors with explicit cursors, using for update of and where current of, and understand row locking and the need for where clauses, commits, or rollbacks.

Learn how explicit cursors use declared parameters to filter results in the where clause, open with a passed value, and update rows while avoiding locking and hard coding.

Master the for loop cursor to automatically manage open, fetch, and close operations, delivering concise code and faster performance for large data sets.

Explore nested blocks in PL/SQL to isolate potentially problematic statements, handle exceptions in the inner block, and let the outer block continue, using labels, begin, end, and declare correctly.

Understand global versus local objects in nested blocks, where outer declarations are accessible inside inner blocks. See how exceptions stay local to blocks and bubble up with when others.

Explore subprograms in Oracle PL/SQL, compare them with nested blocks and stored program units, and learn syntax, declare clause rules, and how modular procedures and functions are invoked.

Master essential PL/SQL fundamentals, from development tools and the declare and begin clauses to exception handling, explicit cursors, nested blocks, and subprograms such as triggers and stored procedures.