[NEW] Oracle PL/SQL - An Ultimate Bootcamp - 21c

Explore creating a cloud Live SQL account, install Express Edition for local practice, and run the demo script to build tables and sample data.

Install Oracle 21c XE, explore the multitenant architecture with a PDB, and run a demo and practice session to verify the setup.

Understand Oracle multitenant architecture by comparing non and multi-tenant databases; learn how a container database hosts pluggable databases with shared resources, centralized management, and common versus local users.

Demonstrates creating a demo pdb from the seed, setting up a local pdb admin and a common user, and connecting to the pdb with sql plus and sql developer.

Explore the simple pl sql anonymous block, its declare-begin-end skeleton, and printing results with dbms_output.put_line. Practice in live sql and sql developer, with server output on to view output.

Explore the types of PL/SQL blocks in Oracle database, focusing on anonymous blocks with declare, begin, exception, and end, and identify how procedures and functions differ for future lessons.

Generate output from a PL/SQL block by reading the department table. Declare a number variable, select count(*) into it, and print with dbms_output.

Execute a demo in Oracle PL/SQL to count employees as of today using a variable and dbms_output, then print department counts for departments 10, 20, and 30.

Execute a PL/SQL solution that counts employees by department using variables and dbms_output. Set server output on to print departments 10, 20, 30 without looping.

Recap how PL/SQL extends SQL as a procedural language executed in the PL/SQL engine, alongside SQL and Java, and how dbms_output and the into clause support debugging and variable assignment.

Explain PL/SQL identifiers and variable declarations in an anonymous block, applying rules: max 30 characters, start with a letter, allow $, _, #, no whitespace, and show a 14-record count.

Learn how the declaration section declares local variables, cursors, and local functions or procedures used in the execution section. Omit the declaration section when no locals are defined.

Learn to declare, initialize, and default variables in Oracle PL/SQL, use varchar2 data types, assign values, and declare constants to prevent changes.

Explore scalar data types in PL/SQL, contrast them with composite data types, review examples like number, date, varchar2, boolean, integer, and binary integer; learn when to choose integer over number.

Learn how to use the %TYPE and %ROWTYPE attributes to declare variables that automatically reflect a table's column data type or an entire row, enhancing adaptability when column definitions change.

Master bind variables in Oracle PL/SQL by declaring, assigning, and using them with colon syntax in select statements for department filters, and printing results with dbms_output.

Explore using sequences in PL/SQL expressions to populate a variable within a declare begin end block, via sequence.nextval and the into clause.

Learn to compute department salary totals in Oracle PL/SQL using bind variables and dbms_output, then apply the approach to counting leather sofa sales from a customer’s orders and line items.

Use SQL to query the leather sofa sales for Ricky by binding product id, joining line item, order, and customer data, and print the total quantity sold.

Recap the PL/SQL bootcamp essentials by outlining identifier rules, optional declarations, and variable types, then explain using bind variables and sequences for practical coding practice.

Describe anonymous PL/SQL blocks, covering declare, begin, end, and exception sections, noting case insensitivity, optional declaration and exception blocks, and running with a forward slash.

Discover how to add comments to PL/SQL code to boost readability, usability, and maintenance. Use single-line comments with double hyphen and multi-line comments with forward slash star, with examples.

Learn how to deploy sql functions in pl sql by calling functions inside pl sql blocks, via direct assignment or a select-into approach using dual and an into clause.

Explore converting scalar data types in Oracle PL/SQL with to_char, to_date, to_number, and cast to transform dates, numbers, characters, and strings, while noting format requirements to avoid errors.

Explore nested blocks in pl/sql, showing outer x=10 and inner x=20; observe inner uses its own value while outer retains its value, and note the access rules between blocks.

Identify arithmetic and relational operators in PL/SQL, learn the concatenate operator, and use single-line and multi-line comments. Practice with declare begin end blocks and dbms_output examples.

Apply the assignment to calculate an employee bonus in Oracle PL/SQL by computing years completed from hire date to today, 10% of salary per year, with a sample output.

Calculate employee bonuses in oracle pl/sql by converting months to years, using trunc to extract whole years, and applying a 10% bonus of salary for each completed year.

Recap key PL/SQL topics, including comment styles, into clause usage, type conversions with to_char, to_date, to_number, cast, nested blocks, and core operators for arithmetic, concatenation, assignment, and relational comparisons.

Explore invoking select statements in pl-sql to retrieve data from Oracle, and understand how sql and pl-sql engines interact, using into clauses and dbms_output to display counts from employee data.

Embed SQL statements inside a PL/SQL block to insert, update, and delete data on the EMP table, using declare, begin, end, and a live insert/update/delete demo.

Explore the Oracle cursor concept, how Oracle reads data into the buffer cache and private SQL area, and distinguish implicit cursors from explicit cursors.

Explore how SQL cursor attributes audit DML by reporting whether rows are affected, using percentage found, percentage not found, and percentage row count for implicit and explicit cursors.

Demonstrates save and discard transactions using commit, rollback, and savepoint to control updates, with department salary changes and a PL/SQL savepoint example.

Learn to perform insert, update, and delete operations and log the activity into an audit_log table using a sequence for audit_id and recording operation, affected rows, and run date.

Perform update operations on the employee table and log results to audit_log, including creating the audit table and sequence and a PL/SQL block to record affected rows and run date.

Recap of PL/SQL interaction with the Oracle server. Embed SQL statements in PL/SQL programs with no modification, understand implicit cursors, and use TCL controls like commit, rollback, and savepoint.

Explore how control structures guide program flow through decision making and conditional constructs, illustrated with a ship route example and PL/SQL if-else logic to determine salary grades.

Explore conditional processing with case statements in plsql, covering syntax, case when logic, end case, and a salary-based commission example showing sequential evaluation.

Explain simple loops in oracle pl/sql by initializing a counter to zero, incrementing it inside the loop, printing each value with dbms_output, and exiting when i > 10.

Master using the while loop in Oracle PL/SQL, including initializing a counter, exit conditions, incrementing, and printing with dbms_output, plus a comparison to for and simple loops.

Learn the for loop in PL/SQL, including syntax from lower to upper bound with initialization, increment, and exit conditions, and using dbms_output for 1 to 10 and optional reverse printing.

This lecture introduces 21c for loop features, including lower bound to upper bound with by step, multiple iterations, reverse loops, and the repeat clause with while conditions.

Describe how the continue statement controls loop execution in PL/SQL, jumping to the end of the iteration to skip code (e.g., filtering even numbers) and print odd numbers.

Understand how the goto statement transfers control to a unique label unconditionally. It must precede an executable statement and should be used sparingly, with a prime number example.

Explore the null statement in PL/SQL, using it as a placeholder for future logic and to perform no action while showcasing department-based commission updates and readability benefits.

Map an employee's salary to a grade using a case statement on the employee table with the input employee number, covering ranges 500-1250, 1251-2000, and 2000-5000.

Fetch an employee's salary from the employee table using an employee number. Use a case statement to map salaries to grade one (500–1250), grade two (1251–2000), or grade three (2001–5000).

Learn how control structures drive dynamic, efficient programs with conditional if-else, case statements, iterative loops, and sequential constructs, and prepare for explicit cursors in the next section.

Explore explicit cursors in PL/SQL and learn to declare them with and without parameters, contrasting them with implicit cursors and private datasets.

Learn to work with explicit cursors in Oracle PL/SQL by declaring, opening, fetching rows in a loop, testing end with the cursor percentage not found, and closing the cursor.

Explains how cursor for loop simplifies PL/SQL by implicitly handling declare, open, fetch, and close operations; reads rows directly from the loop variable, demonstrating with examples.

Explore explicit cursor attributes in PL/SQL, including cursor name, percentage row count, percentage found, percentage not found, and cursor is open, with examples of open, fetch, and close operations.

Demonstrate how the for update clause locks a seat and how where current of cursor updates the locked seat, using two sessions to show blocking and commit or rollback.

Create a cursor to loop through the employee table records. Update the commission using a case statement based on salary thresholds.

Learn to use a cursor and loop to compute commissions with a case statement based on salary, then update the table accordingly.

This recap covers cursor basics in pl/sql: declare, open, fetch, exit, close, and implicit looping; explore rowcount and notfound, locking with for update, and releasing locks via commit or rollback.

Understand exceptions and their causes including invalid user input, code errors, and data errors. Implement exception handling to maintain normal flow and provide meaningful user messages.

Learn how to handle exceptions in PL/SQL using exception blocks, manage divide by zero errors, and distinguish predefined, internal, and user-defined exceptions.

Explore predefined Oracle server errors and their codes, such as aura-100 and aura-1722, and see how they map to no data found and invalid numbers in a pl/sql exception block.

Bind non predefined Oracle errors to names with pragma exception_init, and catch them in an exception block to handle foreign key, unique, and not null errors.

Learn to create custom exceptions in Oracle PL/SQL, bind them to error codes, raise them on business logic, and handle them in exception blocks with a null salary example.

Use raise_application_error to raise a user-defined exception by binding an Oracle error code to a description, then handle it in the exception block; illustrated with a salary is null example.

Master how exceptions propagate from inner to outer blocks in PL/SQL, deciding whether to handle in the inner or outer block, and understand how unhandled errors reach the host environment.

Demonstrates building a three-table data flow in Oracle PL/SQL: stage, prod, and error tables; load data as character, process records with per-row exception handling and error logging.

An oracle pl/sql assignment that caps loans at three times the monthly salary, takes an employee number and loan amount, and throws an exception if the limit is exceeded.

In this solution lecture, learn a PL/SQL workflow that checks a loan against a three-times salary limit, raises an exception when exceeded, and reports approval or rejection.

Review key points on exceptions in Oracle PL/SQL, including predefined and non predefined errors, pragma exception init, and retrieving SQL codes and messages for auditing.

Explore stored procedures and functions in PL/SQL, contrasting anonymous blocks with named, reusable code. Learn the declaration section and parameters like sender, recipient, subject, and content used in Saint_mail templates.

Learn how to create a simple named plsql procedure, declare variables, implement a task like calculating a circle's area, and print results with dbms_output in sql developer.

Execute a procedure in PL/SQL block and from SQL*Plus command line, using set server output on, call pro_area_circle, and verify the area of the circle is 314.

Create a simple procedure with an in parameter and print the result using dbms_output. Learn default in mode, compute circle area with pi 3.14, and grasp basic Oracle PL/SQL syntax.

Create a plsql function that returns a value, using the create or replace function syntax and a return clause, with a correctly typed output variable illustrated by counting user tables.

Execute a function in three environments—plsql block, sql plus, and a query—by declaring variables and printing the returned value, such as the number of tables in the schema.

Create a simple pl/sql function with an IN parameter to return the number of columns for a given table by querying the data dictionary to list tables with column counts.

Create a PL/SQL function to compute employee bonuses by salary tiers, then apply it in a SQL query using a case statement to display results.

Create a function to compute bonus from salary using 1000, 2000, 3000 thresholds with 10%, 9%, 7%, 5%; use it in a query to display salary and bonus on console.

Review stored procedures and stored functions, anonymous blocks, and the return clause, plus default in parameter mode, exec usage, and parameterization for table column counts via queries in Oracle PL/SQL.