Oracle 11g PL/SQL Fundamentals I

Set up the Oracle XE environment, install SQL Developer, configure the system password and listener ports, and run scripts to create and populate tables.

Explore Oracle's built-in database tools, how to connect via host name, port, and SID, and use bind variables to create dynamic scripts for developers and dbas.

Master PL/SQL, merging declarative SQL with procedural logic to boost Oracle performance. Explore block structure, syntax, comments, and deployment options in SQL Plus, sequel developer, and stored programs.

Understand the structure of PL/SQL blocks in Oracle, including declare, begin, program logic, optional exception, and end, with proper comments and commit or rollback.

Learn to use sql plus for data manipulation language (dml) and data definition language (ddl) basics, including declare begin end blocks, variable substitution, and dbms_output with set server output on.

Discover Oracle PL/SQL available data types, from binary and simple integers to floating point, number, character types such as char and varcar, plus boolean and date.

Declare simple data types, use colon equal for assignments, set defaults, enforce not null, declare constants, and show concatenation with pipes in PL/SQL.

Shows how to declare a percent rowtype variable to capture an entire table row and access fields with dot notation, enabling single variable assignments for all columns.

Declare a type of table that acts as a single dimensional array in memory, built from employee ssn and indexed by a simple integer, then populate management and worker lists.

Define a custom record type and declare its fields from multiple tables, then populate active and inactive employee variables with targeted selects to show flexible data assembly.

Master the begin clause to manipulate database and environment data, access Oracle data via select into, and apply logic with branching and DML.

Learn to work with char and varchar2 variables, handle string literals and apostrophes, and concatenate names with the two pipes, while using assignment, sql functions, and regular expressions.

Learn how to handle string literals in Oracle PL/SQL with the q literal to safely include apostrophes. Use cue literals with bang delimiters to avoid termination errors in messages.

Learn to work with numeric variables in PL/SQL, using the colon equals assignment and math to compute salary raises by percent, with user prompts and input validation.

Explore how Oracle stores dates as numeric values, declare date variables, set them to today with cist date, and add days to calculate future review dates.

learn how to use boolean values in pl sql, including true, false, and null, test conditions with if statements, and set defaults to streamline salary-based logic.

Learn how to use comparison operators in Oracle PL/SQL, including =, !=, is null, like, in, and between, with examples and guidance for readable, debuggable code.

Apply sql functions such as max, min, upper, lower, and round to manipulate data in pl/sql scripts, and use select into with dbms_output to format and round bonuses.

Learn to manipulate data with sql dml inside pl/sql, including insert, update, delete, and transaction control with commit rollback savepoint and set transaction, plus inner joins and aliases.

Learn how the select into statement handles single-row results, why the select list must match the target type exactly, and how explicit cursors address multiple rows.

Explore how exceptions affect select into usage in PL/SQL, comparing single-row record results, too many rows error, and no data found, and learn how summary functions avoid these issues.

Learn how Oracle 11g PL/SQL uses sequences to generate unique primary keys, create sequences, reference nextval in inserts, and auto populate fields with triggers for efficient data insertion.

Explore logic control and branching in PL/SQL, using program labels and the goto statement, loops, if-then-else, and the case statement to build smarter applications.

Learn how to implement an indefinite loop in PL/SQL, exit conditions with exit when, and how test timing affects loop behavior using a least productive employee example.

Apply nested loops in PL/SQL to perform repeated operations across related data, using outer and inner loops with index variables to insert into transactions and simulate buy or sell records.

Learn how the if-then-else and else-if structures in pl/sql enable smart branching with binary conditions, optional else blocks, and case-insensitive input handling using upper and lower functions.

Learn to use the case statement in PL/SQL to replace long if-then-else chains with selector and searched forms, handle multiple conditions, and always include an else clause.

Explore the PL/SQL exception clause, trap and test errors including any type, internal Oracle, and user declared exceptions, and apply control flow rules like no return to begin.

Start trapping errors with the when statement after the exception clause, testing for single or multiple errors, and use when others then to roll back on untested issues.

Identify common Oracle PL/SQL errors like no data found, too many rows, and invalid number; trap them with when and when others, isolate details via exception_init, and inspect SQLCODE/SQLERRM.

Create user-defined exceptions in Oracle 11g by declaring a named exception and mapping it with pragma exception_init to a specific error code, then handle it in the exception section.

Explore how to use sqlcode and sqlerrm within when others to access the actual error code and message, enabling smarter debugging and targeted exception handling.

Create user defined events to encode business rules by declaring an exception object, raising it under certain conditions, and handling it in an exception clause, not as a real error.

Learn how to use the continue statement inside loops to skip processing and fetch the next record, improving performance in PL/SQL cursors. Compare exit when with continue for efficient filtering.

Declare explicit cursors with parameters to filter data via the where clause, and pass the parameter when opening the cursor. The parameter is declared inside the cursor declaration.

Discover how the for loop cursor simplifies PL/SQL by automatically handling opening, fetching, and closing, delivering concise code and faster performance for large data sets.