Oracle 11g PL/SQL Fundamentals I

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.

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.

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 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.

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.

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.

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.

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.