If you’re a developer or a database administrator looking for an easy way to master PL/SQL programming language, these PL/SQL tutorial series are for you.
These PL/SQL tutorial series contain information that every developer and even database administrator should know to use PL/SQL in their daily tasks efficiently.
As you go through the entire tutorial series, you will learn a lot of new facts about PL/SQL programming. These PL/SQL tutorial series provide the best practices on PL/SQL programming language. The best part is that they contain a lot of examples and helpful script in each tutorial.
Section 1. Getting started with PL/SQL
- What is PL/SQL – introduce you to PL/SQL programming language and its architecture.
- Anonymous Block – explain PL/SQL anonymous blocks and shows you how to execute an anonymous block in SQL*Plus and Oracle SQL Developer tools.
- Data Types – give you a brief overview of PL/SQL data types including number, Boolean, character, and datetime.
- Variables – introduce you to PL/SQL variables and shows you how to manipulate variables in programs efficiently.
- Comments – use single-line or multi-line comments to document your code to make it more readable and maintainable.
- Constants – learn how to declare constants that hold values which remain unchanged throughout the execution of the program.
Section 2. Conditional control
- IF statements – introduce you various IF statement to either execute or skip a sequence of statements based on a condition.
- CASE statements – learn how to choose one sequence of statements out of many possible sequences to execute.
- GOTO – explain the GOTO statement and shows how to use it to transfer control to a labeled block or statement.
- NULL statement – show you how to use the NULL statement to make the code more clear.
Section 3. Iterative processing with loops
- Basic LOOP statement – show you how to use the basic LOOP statement to execute a sequence of code multiple times.
- Numeric FOR LOOP statement – learn how to execute a sequence of statements a fixed number of times.
- WHILE loop – execute a sequence of statements as long as a specified condition is TRUE.
- CONTINUE – use the CONTINUE statement to skip the current iteration of the loop and immediately continue the next iteration.
Section 4. Select Into
- SELECT INTO – learn how to fetch a single row from a table into variables.
Section 5. Exception handlers
- Exception – show you how to handle exceptions in a block.
- Raise exceptions – learn how to raise an exception explicitly with the
- Using raise_application_error – raise an exception with a user-defined error message.
- Exception propagation – learn about how PL/SQL propagates an unhandled exception from the current block to its enclosing block.
- Handling other unhandled exceptions – show you how to use the
SQLERRMfunctions to handle other unhandled exceptions.
Section 6. Records
- Record – learn how to use record type to make your code more efficiently by shifting operations from field-level to record-level.
Section 7. Cursors
- Cursor – introduce you to cursors including implicit and explicit cursors and shows you how to use them effectively to fetch data from a table.
- Cursor FOR LOOP – show you how to use the cursor
FOR LOOPstatement to fetch and process each row from a cursor.
- Cursor with parameters – learn how to use the cursor with parameters to fetch data according to the input arguments passed to the cursor each time it is opened.
- Updatable cursor – introduce you to the Oracle updatable cursor to update data in the table.
Section 8. Stored procedures and Functions
- Procedure – a step-by-step guide to create, compile and execute a procedure from Oracle SQL Developer.
- Function – show you how to develop a PL/SQL function and explain you various ways to call a function.
- Cursor variables – learn how to use cursor variables using
Section 9. Packages
- Introduction to the PL/SQL package – introduce you to PL/SQL packages and explain to you the advantages of using them in your application development.
- Package specification – show you step by step how to create a package specification.
- Package body – learn how to create a package body.
- Drop Package – illustrate how to drop a PL/SQL package.
Section 10. Triggers
- Triggers – introduce you to the Oracle trigger and show you how to create a new trigger.
- Statement-level triggers – learn how to use statement-level triggers to enforce additional security to the transaction.
- Row-level triggers – show you how to use row-level triggers for data-related activities.
- INSTEAD OF triggers – learn how to use the
INSTEAD OFtriggers to update tables via their views which cannot be modified directly through DML statements.
- Disable triggers – show you to disable a trigger or all triggers of a table.
- Enable triggers – describe steps to enable a trigger or all triggers of a table.
- Drop Triggers – guide you on how to drop a trigger from the database.
- How to fix the mutating table error – learn about the mutating table error and how to fix it using a compound trigger.
Section 11. PL/SQL Collections
- Associative Arrays – introduce you to associative arrays including declaring associative arrays, populating values, and iterating over array elements.
- Nested Tables – learn about nested tables.
- VARRAY – learn about variable-sized array and how to manipulate its elements effectively.