Oracle PL/SQL Database Programming

Enhance Your Database Programming Skills

Programming Basics & Databases4.9(96)4h 47m23 lectures8 sections

What you'll learn

  • Get to know the PL/SQL language and its features
  • Work with variables and write executable statements
  • Use SQL statements inside PL/SQL blocks
  • Create control structures and loops
  • Work with composite data types
  • Use explicit cursors
  • Handle errors and exceptions
  • Create procedures, functions and packages
  • Create triggers

About this course

This course teaches database programming with Oracle PL/SQL, from a general introduction to programming and databases up to an advanced hands-on workshop. It starts with the PL/SQL language, its features and uses, working with variables, writing executable statements and using SQL statements inside PL/SQL blocks. Next you learn to create control structures for decision making and loops, then composite data types and their uses, understand explicit cursors and how to use them, and manage and handle errors and exceptions. Then you move on to the practical workshop, where you create procedures, functions, packages and triggers, with advanced applications for each. The course suits database students and programmers, and it is best if you already know the basics of SQL. By the end you will be able to write PL/SQL programs, create procedures, functions, packages and triggers, and handle errors inside an Oracle database.

Expected outcomes

  • Master the skills of writing PL/SQL code professionally.
  • Ability to create procedures, functions, packages, and triggers in Oracle databases.
  • Develop the ability to solve programming problems using PL/SQL.
  • Readiness to work as a professional PL/SQL developer in corporate environments.

Learner reviews

These reviews are from learners who bought the content and completed at least 50% of it — there are no reviews from anyone who hasn't gone through the material.
4.9
★★★★★
96 reviews
5★
87
4★
8
3★
1
2★
0
1★
0

    Course content

    1

    Introduction and Basics of PL/SQL

    2 lectures
    1. General Introduction to Programming and DatabasesIntroduces the Oracle PL/SQL course: what the procedural language is and its features such as blocks, subprograms, packages, error handling, triggers and tuning, plus setting up the environment through an Oracle Apex Workspace and the training tables.21:14
    2. Understanding PL/SQL Language, Features, and UsesStarts hands-on PL/SQL: writing a block with its sections, adding comments, anonymous blocks versus named blocks, declaring variables and assigning values, and the kinds of subprograms such as functions, procedures and packages.10:07
    2

    Building Programming Basics

    3 lectures
    1. Handling Variables in PL/SQLExplains declaring variables in PL/SQL: why variables are useful, valid naming rules such as not starting with a digit, choosing meaningful names, data types such as VARCHAR2, NUMBER and Boolean, and assigning an initial value.18:59
    2. Writing Executable Statements in PL/SQLExplains writing executable statements inside a PL/SQL block: using functions and arithmetic and string operators, and the difference between variables in inner and outer blocks and the scope where each is visible, with worked examples.14:49
    3. Using SQL Statements Inside PL/SQL BlocksExplains using SQL statements inside PL/SQL blocks: fetching a value from a table into a variable with SELECT INTO, matching the variable's type to the column, avoiding clashes between variable and column names, followed by practical examples.13:10
    3

    Control Structures

    3 lectures
    1. Creating Control Structures for Programming DecisionsExplains conditional statements in PL/SQL: the IF, ELSE and ELSIF syntax, building conditions such as less than, greater than and NOT, testing them on a value entered by the user, and handling NULL correctly in comparisons.11:40
    2. Programming Loops - Part OneExplains the CASE statement in both of its forms with an example that evaluates a value entered by the user, then loops in PL/SQL, starting with the basic LOOP, a counter that increases each iteration, and an EXIT condition to leave it.11:53
    3. Programming Loops - Part TwoContinues loops in PL/SQL, covering the WHILE loop and the FOR loop counting up and down, nested loops, and the statement that restarts the iteration to skip a given value.9:06
    4

    Composite Data Types

    2 lectures
    1. Introduction to Composite Data TypesIntroduces composite data types in PL/SQL: how they differ from scalar types, why group several variables into one type, creating a record based on a table row, defining your own composite type, then filling it and reading its fields.12:56
    2. Advanced Practical Applications on Composite DataExplains the two composite types in PL/SQL, Record and Collection, and how to define an array type with INDEX BY PLS_INTEGER, declare a variable of it, fill its elements and print them.5:36
    5

    Explicit Cursors

    2 lectures
    1. Understanding Explicit Cursors and Their Uses - Part OneExplains cursors in PL/SQL: the difference between explicit and implicit, that a cursor is a container holding the multi-row result of a query, the steps to open it, fetch a record and read its fields, applied to an employees table.18:00
    2. Advanced Applications for Explicit Cursors - Part TwoExplains explicit cursors with parameters, showing how a value is passed to the cursor when it is opened so it can be reused several times, with an example on employees and departments.5:02
    6

    Exception Handling and Creating Procedures and Functions

    6 lectures
    1. Managing and Handling Errors and Exceptions in PL/SQLExplains error and exception handling in PL/SQL: types of errors such as syntax errors, handling a predefined exception like too many rows when fetching several rows into one variable, and raising a custom error with raise application error.15:21
    2. Introduction to Procedures and Functions in DatabasesIntroduces subprograms as named blocks and shows how to create a Function that takes a department number and returns its name, then test it, modify it and run it again.13:07
    3. Introduction to the Advanced Practical WorkshopIntroduces the second workshop and outlines its guided practical topics, including Packages as a more organized way to group subprograms.3:06
    4. Creating Procedures - Part OneExplains creating Procedures and how they differ from functions in not returning a value, with an example procedure with two parameters that raises an employee's salary by a given percentage using UPDATE.12:08
    5. Creating Procedures - Part TwoExplains the three procedure parameter modes IN, OUT and IN OUT, with examples of passing a value into a procedure and receiving a value back in a variable.12:53
    6. Creating and Using FunctionsExplains creating and using Functions, which must return a value unlike procedures, and ways to call them such as inside a SELECT, with a salary function example.11:40
    7

    Packages

    2 lectures
    1. Introduction to Software Packages and Their CreationIntroduces packages in PL/SQL: grouping related functions and procedures under one topic, a package having a specification for declarations and a body for implementation, with a practical example of creating one and calling a function from it.9:05
    2. Advanced Practical Applications on Software PackagesExplains Packages and their benefits for organizing code and easing maintenance, their Specification and Body parts, calling their members by package name, and querying existing packages.5:12
    8

    Triggers

    3 lectures
    1. Introduction to Triggers and Their Basic UsesIntroduces triggers, similar to events in other languages: code that runs when an operation happens on a table, before or after it, with an example that logs the added record's ID, the operation type and the date into another table.13:57
    2. Advanced Applications on Triggers - Part OneExplains uses of Triggers, including checking data before an operation and stopping it on error, with a salary update example and an inventory example that increases and decreases stock.26:07
    3. Advanced Applications on Triggers - Part TwoApplies Triggers to a banking system by creating customer and transaction tables and a trigger that raises the balance on deposit and lowers it on withdrawal, then repeats the idea for a warehouse.12:29

    Instructor

    Eng. Azab Mohamed Azab

    Eng. Azab Mohamed Azab

    Lecturer at the Arab Academy for Science, Technology and Maritime Transport, and the Egyptian Ministry of Interior, specializing in technology and information.
    68.8K views28 courses

    Related courses