PLSQL - INTRODUCTION

PL/SQL, or Procedural Language/Structured Query Language, is Oracle Corporation's procedural extension for SQL (Structured Query Language). It combines the ease of SQL with the procedural functionality of a programming language to create a powerful and flexible programming environment for Oracle Database.

Architecture of the PL/SQL :
PLSQL Architecture consists of Three components
They are :
  • PL/SQL block
  • PL/SQL Engine
  • Database Server
The PL/SQL compilation and run-time system is an engine. It compiles and executes PL/SQL blocks and subprograms. The engine can be installed in an Oracle server. An application development tool such as Oracle Forms.

plsql-intro

PL/SQL Block :
A block groups related declarations and statements. We can place declarations close to where they are used, such as inside a large subprogram.
There are two type of blocks
  • Anonymous block
  • Named block (or) Subprogram. (eg-procedure, function)
The PLSQL block has three parts
  • Declarative part
  • Executable part
  • exception handling part
Here are some key aspects of PL/SQL :
Procedural Language :
  • PL/SQL is a procedural language, meaning it allows you to write code that specifies the steps to be taken to accomplish a task.
  • It supports constructs like loops, conditionals, and exception handling, making it suitable for implementing complex business logic and data processing tasks.

    • Integration with SQL :
  • PL/SQL is tightly integrated with SQL, allowing you to embed SQL statements within your PL/SQL code seamlessly.
  • You can use SQL commands to manipulate data in the database, and PL/SQL to control the flow of execution and perform additional processing.

    • Blocks and Structure :
  • PL/SQL code is organized into blocks. A block consists of three parts: declaration, executable, and exception handling.
    • The declaration section is where you declare variables, constants, and cursors. The executable section contains the actual code to perform tasks, and the exception handling section deals with error conditions.
    Variables and Data Types :
  • PL/SQL supports various data types, including scalar types (such as VARCHAR2, NUMBER), composite types (such as records and tables), and reference types.
  • You can declare variables, constants, and cursors to store and manipulate data within PL/SQL blocks.

    • Cursors :
  • Cursors are used to process individual rows returned by a query. PL/SQL allows both implicit and explicit cursors.
  • Implicit cursors are created by SQL statements inside PL/SQL blocks, whereas explicit cursors are defined explicitly by the programmer.

    • Exception Handling :
  • PL/SQL provides a robust exception handling mechanism to manage errors and exceptions that may occur during the execution of code.
  • You can define exception handlers to gracefully handle errors and take appropriate actions.

    • Stored Procedures and Functions :
  • PL/SQL enables the creation of stored procedures and functions, which are named blocks of code that can be called with specific parameters.
  • Stored procedures are used for performing specific tasks, while functions return a value.

    • Triggers :
  • PL/SQL can be used to define triggers, which are special types of stored procedures that are automatically executed in response to events such as insertions, updates, or deletions in a table.


    • (PL/SQL - Declaring PLSQL variables)