Sample Videos

What is included in the course

PL/SQL programming course helps you strengthen your SQL code with 8 interactive sessions.

1. Introduction to PL/SQL

We begin with the basics of PL/SQL like meaning, features, environment and benefits. Then comes the essential lesson on structure of PL/SQL block, PL/SQL identifiers, delimiters, comments, executing statements and PL/SQL tables. We then write, compile, execute and test our first PL/SQL block.

2. Variables and data types

We take next step forward with PL/SQL data types(Scalar, LOB, Composite, Reference), scalar types (Numeric, character, boolean, datetime), LOB subtypes, operators (arithmetic, relational, comparison, logical, string), operator precedence, constants, literals, variable declaration, Initialization, naming conventions, scope of variables - global and local, %TYPE attribute, displaying output, converting data types, using non-PL/SQL variables, substitution and host variables.

3. Control structures

We write PL/SQL blocks with conditional statements like IF-THEN, IF-THEN-ELSE, IF-THEN-ELSEIF and CASE-ENDCASE and loops such as LOOP-END LOOP, WHILE and FOR loop. Learn how to use compound IF statements, nested loops and control statements like EXIT, EXIT WHEN, CONTINUE and GOTO. Additional lessons on searched case statement, labels, counter variables and choosing correct type of loop in your programs.

4. PL/SQL subprograms

We take our PL/SQL learning to next level with named PL/SQL blocks. You will study PL/SQL subprograms, difference between anonymous blocks and subprograms, advantages of subprograms, subprogram types (procedures, functions), parts of named blocks(declarative, executable, exception handling), creating a procedure, executing procedure, passing parameters, types of parameters(IN, OUT, INOUT), drop procedure, creating function, calling function, recursive functions, drop function, difference between functions and procedures and PL/SQL built-in procedures and functions. Additional lessons on using Oracle iSQL*PLUS and NOCOPY parameter.

5. Cursors and records

This session helps you write complex PL/SQL programs. Learn about the meaning and need of cursor, cursor types(explicit, implicit), declaring and opening a cursor, fetching cursor, closing cursor and cursor attributes - %NOTFOUND, %ROWCOUNT, %ISOPEN. We then have a lesson on PL/SQL records, types of records (Table based, cursor based, user-defined), defining records and accessing fields. An additional lesson on cursor FOR loop.

6. Triggers

We take PL/SQL learning to next level with meaning and uses of triggers, creating triggers, types of triggers, levels of triggers, difference between triggers and stored procedures, executing triggers, dropping triggers, actions and events in triggers. Additional lessons on WHEN clause, OR REPLACE argument, triggers in simultaneous database operations, cascading triggers, order of trigger execution and maximum number of triggers that can be used in a table.

7. Exception handling

Create bug-free code with meaning of exception, types of exception (system defined, user defined), Difference between exception and error, list of system defined PL/SQL exceptions, trapping exceptions, raising exceptions with RAISE statement, user-defined exceptions, pre-defined exceptions, declaring PL/SQL exceptions, scope rules for PL/SQL exceptions. Additional lessons on error-trapping functions, propagating exceptions, defining custom error messages and using OTHERS clause.

8. Packages

Make the maintenance of PL/SQL code easy with packages. Study the meaning of packages, creating packages, components of package, developing a package, invoking package, altering package and removing package. A lesson on referring to the objects declared in a package. Difference between function, procedure and package.