This self-paced SQL training course is perfect for advancing your current SQL skills. While Oracle uses a standard SQL like other major databases, PL/SQL is specific to Oracle. PL/SQL is a procedural language with variables, conditions, loops, subprograms, stored procedures, and functions. By course completion, you will understand and know how to use PL/SQL's main features, including declaring, naming, and assigning variables.

1. PL/SQL Basics

2. What is PL/SQL?

3. Blocks

4. Outputting Information

5. Variables and Constants

6. Constants

7. Data Types

8. Naming Variables and Other Elements

9. Embedding SQL in PL/SQL

10. SELECT...INTO and RETURNING...INTO

11. PL/SQL Features

12. Subprograms

13. Introduction to Subprograms

14. Procedures

15. Variable Declarations

16. Dropping a Procedure

17. Parameters

18. Parameters with Default Values

19. Parameter Modes

20. IN Mode

21. OUT Mode

22. IN OUT Mode

23. Named Notation

24. Using SQL in a Subprogram

25. %TYPE

26. Functions

27. Using PL/SQL Functions in SQL Queries

28. Conditional Processing

29. Conditions and Booleans

30. IF-ELSIF-ELSE Conditions

31. ELSIF

32. BOOLEAN Values and Standard SQL

33. The CASE Statement

34. CASE Expressions

35. Exceptions

36. Introduction to Exceptions

37. Predefined Exceptions

38. The EXCEPTION Part of the Block

39. User-defined Exceptions

40. User-defined Exceptions in Subprograms

41. Re-raising Exceptions

42. Naming Unnamed Predefined Exceptions

43. WHILE Loops

44. When to Use Exceptions

45. Cursors

46. Implicit Cursors

47. Explicit Cursors

48. %ROWTYPE

49. Explicit Cursor Use Case

50. Cursor FOR LOOP

51. Cursor Parameters

52. Packages

53. Package Basics

54. The Package Specification

55. The Package Body

56. Building an Employee Package

57. Overloading Subprograms

58. Auditing

59. Validation Procedures

60. Package Cursors

61. Benefits of Packages

62. Triggers

63. What are triggers?

64. Trigger Parts

65. Validation Triggers

66. The WHEN Clause

67. Audit Triggers

68. Statement-level Triggers

69. Compound Triggers

70. Trigger Warning

Prerequisites:

Prior to enrolling in this course, we recommend having an intermediate understanding of SQL or taking the Oracle SQL Training course.

Requirements:

Hardware Requirements:

• This course must be taken on a PC. Macs are not compatible.

Software Requirements:

• PC: Windows 10 or later.
• Oracle Database Express Edition (XE) 12c or 18c - Free edition of Oracle RDBMS.
• Oracle SQL Developer - The client interface for connecting to Oracle (software not included).
• Browser: The latest version of Google Chrome or Mozilla Firefox are preferred. Microsoft Edge and Safari are also compatible.
• Adobe Acrobat Reader.

Other:

• Software must be installed and fully operational before the course begins.

Instructional Material Requirements:

The instructional materials required for this course are included in enrollment and will be available online.