Oracle PL/SQL Study Notes

Overview of Oracle PL/SQL

  • Oracle PL/SQL is a programming language developed in the mid 1990s.

  • Its creation was driven by the need for a native fourth generation programming language to work efficiently with the Oracle SQL engine.

  • PL/SQL can be likened to languages such as C or Java.

Historical Context and Influences

  • The rooted influence of PL/SQL can be traced back to the Ada programming language.

  • Ada was developed in the 1980s for the U.S. Department of Defense (DOD) to unify numerous programming languages.

  • Advantages of basing PL/SQL on Ada include its strong type system and structure.

Characteristics of PLSQL

  • PL/SQL is described as:

    • Procedural programming language.

    • Feature-rich, comparable to modern programming languages.

    • A language that supports both procedural constructs and embedded SQL.

Distinction Between SQL and PL/SQL

  • SQL (Structured Query Language) is characterized as a declarative language:

    • Users specify what they want to achieve without detailing how to accomplish it.

  • PL/SQL is characterized as procedural:

    • Users detail the step-by-step process to achieve tasks.

    • It allows embedding SQL statements for enhanced functionality.

Core Functionalities of PL/SQL

Programming Constructs

Control Constructs

  • PL/SQL supports essential programming elements such as:

    • Looping: Allows iterations over blocks of code, enabling actions like stepping through ranges.

    • Example: A PLSQL block iterates through numbers 1 to 3 and processes output.

    • Conditional Logic: Executes different program sections based on evaluated conditions.

    • Example: A VARCHAR2 variable 'day' compares to weekday strings; if it matches, outputs "another workday", otherwise it's labeled as "the weekend".

Stored Procedures and Functions

  • Definition: A stored procedure or function encapsulates reusable code sections.

  • Functions are stored in the data dictionary:

    • They can be called by any user with appropriate permissions.

    • Example: A stored function calculates the square of two inputs and returns their sum using a SELECT statement.

Exception Handling

  • Exception blocks in PL/SQL allow for error management:

    • Functionality to address errors without halting the entire procedure.

    • Options include fixing the error or ignoring it to continue with other operations.

Practical Applications of PL/SQL

  • PL/SQL is deemed superior for complex business logic.

  • Ideal for running SQL-inclusive code blocks repeatedly.

  • PL/SQL is the native choice for managing intricate tasks within Oracle databases, showcasing efficiency and, ultimately, effectiveness.