PL/SQL by Example - Beginner to Advanced PL/SQL

Install Oracle database 11g express edition from the provided link, selecting settings and remembering the SYS and SYSTEM passwords. Finish the install and prepare to download Java for SQL Developer.

Download and install Java from the provided URL, accept the license, run the installation wizard for the 64-bit Windows exe, and remember the install path for SQL Developer.

Install Oracle SQL Developer from site, selecting 64-bit with Java included or 32-bit needing Java, then extract, run the exe, create a connection named myconnection and connect using system.

Configure the core PL/SQL scripts by creating the INVENTORY1 user, connecting via SQL Developer, and running scripts to set up the customer, product, sales, sales_history, and salesperson tables.

explore how pl/sql extends sql with procedural statements to define business logic, control flow, and data processing in a single block, addressing sql's limitations.

Discover the advantages of PL/SQL, a portable, high-performance language that embeds SQL, supports object-oriented features, and delivers faster, secure, and productive Oracle database programming.

Learn the PL/SQL structure as a block-structured language with declare, begin, and exception blocks; declare variables and cursors, perform updates and data retrieval, and handle errors.

Explore your first PL/SQL example by displaying output with dbms_output.put_line. Learn to enable the dbms output window, run the program, and see the welcome message.

Learn how to declare variables in a PL/SQL block, assign defaults with colon equal to and the default keyword, and declare character variables, outputs, and constants to prevent reassignment.

Learn to define PL/SQL comments, including single-line comments with two dashes and multi-line comments using /# to start and #/ to end, and follow best practices for clarity.

Explain global and local variables in PL/SQL with a parent and sub block, showing num1 visible throughout while num2 stays local.

Explore how if-then-else constructs drive discount logic in PL/SQL, applying conditions such as total amount greater than 100 or between 100 and 200 with elseif and else branches.

Explore how to use the case statement as an alternative to if-then-else in PL/SQL, including the syntax case when expression then value else value end case and a discount example.

Learn how to use a while loop in PL/SQL to execute statements until a counter reaches 20, display the counter, and increment it by one before ending.

Explore the for loop in PL/SQL by executing statements a fixed number of times, from 10 to 20 or 20 down to 10, with reverse and the in keyword.

Fetch data from the database using select into in PL/SQL, ensuring variable data types match the column types (number, varchar, date, timestamp, composite) and display results with dbms_output.

Learn how %TYPE links variables to table columns, enabling automatic datatype adaptation for application independence; prefer percentage type to avoid rewriting code when column sizes change.

Learn how to insert data into a database using a PL/SQL block, starting with a declare block that defines variables with table.column%type, then inserting into the customer table and commit.

Learn what anonymous blocks are and why they have no names. They execute and don't save to the database, making them suitable for temporary tests rather than large applications.

Learn to define and call named PL/SQL procedures, pass parameters with in, out, and in out modes, and structure procedures with declaration, begin, and exception sections.

Learn how to call a PL/SQL procedure with 10 parameters using anonymous blocks, compare positional versus named parameter calls, and verify inserted data.

Learn how out mode lets a procedure modify an out parameter, contrasting with in mode. The example uses total_count, select count(1) into total_count from customer, and dbms_output.

Explore inout mode in PL/SQL, learning how a single parameter can read and write, demonstrated by converting a CID parameter and updating tcount to reflect total records.

Learn about PL/SQL functions, including user-defined ones that return values and can be used in SQL expressions, and compare them to procedures with their return type and parameter modes.

Call a function in PL/SQL with select find_salescount from dual using a date input, and print the result with a declare and begin block using dbms_output.put_line, unlike procedures.

Discover how pl/sql uses the exception block to catch errors during execution, differentiate system-defined and user-defined exceptions, and record and handle them with when clauses.

Explore exception handling in PL/SQL with a customer fetch example, catching no data found and too many rows via an exception block and others as a fallback.

Declare a user defined exception, raise it when customer_ID is less than or equal to zero, and catch it to display that customer_ID must be greater than zero.

Define a PL/SQL package specification by declaring procedures and functions with parameters, using create or replace package, and ending with the package name. Group customer subprograms for the package.

Learn to write the package body in PL/SQL, matching the package specification, and define executable statements for procedures and functions, then compile customer_package.

Execute sub programs inside the customer package by calling procedures and functions with the package name and dot notation. Run display_names (no parameters) and get_customer (CID) to see Sonu, USA.

Learn how records in PL/SQL act as a composite data type to fetch a table row into a single structure, replacing nine variables and simplifying code and maintenance.

Learn how to use a record data type to fetch and display customer data in PL/SQL, replacing ten variables with a single composite record for any number of columns.

Explore operations on record data types in PL/SQL, including modifying fields, transferring data between records, and displaying composite data using field access and assignments.

Learn to pass a composite record data type as a parameter from one procedure to another in PL/SQL, using a row type to display customer names.

Fetch data into a record variable, pass it to the show_customer procedure, and insert it into the customer table using one record variable, then commit.

Learn to update database data using a record data type in PL/SQL by fetching into a record, modifying fields, and applying update with a where clause.

Define custom record types using the type record statement to model non-table data, then declare and manipulate these user defined records in PL/SQL.

Define a user defined record type customer_rec with first_name and last_name, declare c_rec of that type, and perform data assignments and updates, compiling and executing to display results.

Learn how Oracle uses a context area and cursor to manage SQL results, the active set stored in the shared global area, with implicit and explicit cursors.

Explore Oracle's implicit cursor, automatically created for every SQL or DML statement, and learn its four attributes: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT, and how to use them in PL/SQL.

Explore explicit cursors in PL/SQL by declaring, opening, fetching, and closing them to access query results and perform insert, update, and delete operations.

Declare an explicit cursor, open it, fetch a row into PL/SQL variables, and display the results, then close the cursor to retrieve a single row from the customer table.

Use an explicit cursor to retrieve multiple rows by wrapping fetch in a loop and exit when c%notfound; declare, open, fetch, and close the cursor to process all rows.

Combine cursors and records to simplify PL/SQL code by fetching into a cursor percentage_row_type record (c_rec) and accessing fields with dot notation (c_rec.first_name, c_rec.last_name, etc.).

Master the cursor for loop to replace explicit cursor handling by automatically processing query results, with Oracle fetching rows internally.

Learn how cursor variables and sys_refcursor enable passing result sets between procedures and functions, and how to open, fetch, and display cursor data in a consumer procedure.

Learn the two default system defined exceptions for cursor processing, cursor already open and invalid cursor, and how to catch them to ensure smooth cursor processing.

Explore collections as a single-dimensional composite data type in Oracle, learning their properties, and how bulk collect, forall, and table functions optimize PL/SQL performance.

Explore PL/SQL collection terminology: index values locate data; elements hold strings, dates, or records; dense collections have no gaps, sparse have gaps; retrieve data with next, last, and fast.

Explore associative arrays in pl/sql, index by table using binary_integer or varchar values, enabling insertion, deletion, and traversal of a one-dimensional varchar2 collection.

Explore nested tables in PL/SQL, including how to create, initialize, extend, and enforce sequential data storage, plus key differences from associative arrays and common operations.

Explore vararrays in PL/SQL, with an upper bound and four elements, and compare them to nested tables that allow deletions. Learn initialization, extension, and the delete error.

Explore PL/SQL collection methods, including exists, count, first, last, prior, next, extend, trim, and delete, with vararray limits; extend by n and by n1,n2, delete by range.

Explore multiset operators in PL/SQL, including multiset union, multiset union distinct, multiset except, and multiset intersect, using nested tables and example outputs to compare with SQL set operators.

Learn to distinguish PL/SQL collections: associative arrays with index by number or character, VARRAYs with a defined maximum size, and nested tables with MULTISET operators and practical use.