What is PL/SQL?⌗

1. PL/SQL stands for Procedural Language Extension to SQL
2. It is a Extension of SQL developed by Oracle.
3. It adds SQL capabilities of procedural programming like variables, loops and conditional statements in SQL.

PL/SQL Architecture⌗

Physical Architecture⌗

• PL/SQL engine processes the entire PL/SQL block of code.
• It Separate SQL statements and Procedural statements.

Simple Architecture Flow⌗

PL/SQL Logical Architecture⌗

• Corporates with SQL Engine.
• Enables Subprograms -> Allows re-usability of code/script like variables and functions.
• Dynamic Queries-> We can modify or create new Queries.
• Case Insensitivity
• Optimizer-> It optimizes our code for better performance.
• Enables Object-Oriented Programming
• Web Development #Extras Context Switching: When you write SQL Query in PL/SQL the PL/SQL send the query to SQL Engine and the result will be returned this operation is called Context Switching.

---

Pluggable Database⌗

What is PDB?⌗

Pluggable Database (PDB) is a portable collection of schemas, tables, and objects that can be plugged into a Container Database (CDB) in Oracle.

Why PDB?⌗

• Better resource management
• Easier database consolidation
• Faster provisioning and cloning

Structure⌗

1. A Container Database (CDB) holds multiple Pluggable Databases (PDBs).
2. A PDB is independent and can be moved or cloned between CDBs.
3. Each PDB has its own:
   • Data files
   • Schemas
   • Tables
   • Users
4. A single CDB can manage multiple PDBs, improving scalability and consolidation.

---

What are Blocks?⌗

Block is a logical unit of code that groups related declarations, statements.

Structure⌗

1. DECLARE Block.

Here we declare Variables, const, exceptions.

2. EXECUTION BLOCK.

Contains Executable Statements like SQL, procedural statements. Starts with BEGIN

3. EXCEPTION HANDLING BLOCK.

Starts with EXCEPTION Handles exceptions and errors during execution.

Syntax of PL/SQL Block⌗

DECLARE 
   -- Declaration statements (optional)
BEGIN 
   -- Executable statements (mandatory)
EXCEPTION 
   -- Exception handling statements (optional)
END;

Types of Blocks⌗

1. Anonymous Block
2. Procedures
3. Functions

Coding in PL/SQL⌗

How to Print Text as Output in PL/SQL⌗

SET SERVEROUTPUT ON;
begin
dbms_output.put_line('Hello World');
end;

Nested Blocks⌗

*We can add begin end inside begin end block that way we can do nested blocks.

SET SERVEROUTPUT ON;
begin
dbms_output.put_line('Hello World');
    BEGIN
        dbms_output.put_line('Its Me Rawat');    
    END;
end;

Variables⌗

`Same Use Cases as in normal Programming language.'

PL/SQL Variable Types⌗

1. SCALAR Holds single value of same type.
2. REFERENCE Pointers.
3. LARGE OBJECTS Holds pointers of large data.
4. COMPOSITE Hold more then one data type.

SCALAR DATA TYPES⌗

1. CHAR()
2. VARCHAR()
3. NUMBER[,]
4. BINARY_INTEGER = PLSINTEGER
5. BINARY_FLOAT
6. BINARY_DOUBLE
7. BOOLEAN
8. DATE
9. TIMESTAMP
10. TIMESTAMP(P) WITH TIME ZONE
11. TIMESTAMP(P) WITH LOCAL TIME ZONE
12. INTERVAL(P) YEAR TO MONTH
13. INTERVAL(P) DAY TO SECOND(P)

Variable Naming Conventions⌗

• Must Start with letter.
• Can contain some special character.(like _ ,#,$)
• Can be maximum 30 char
• can not have oracle reserved words.

Operations in PL-SQL⌗

Setting default variables⌗

SET SERVEROUTPUT ON;
DECLARE
    V_TEXT STRING(50) NOT NULL DEFAULT 'ITS_RAWAT';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_TEXT);
END;

Assigning variables⌗

SET SERVEROUTPUT ON;
DECLARE
    V_TEXT STRING(50) NOT NULL DEFAULT 'ITS_RAWAT';
BEGIN
    V_TEXT:= 'PL/SQL';
    DBMS_OUTPUT.PUT_LINE(V_TEXT);
END;

Concatenation of Strings⌗

SET SERVEROUTPUT ON;
DECLARE
    V_TEXT STRING(50) NOT NULL DEFAULT 'ITS_RAWAT';
BEGIN
    V_TEXT:= 'PL/SQL' || ' IS DIFFERENT';
    DBMS_OUTPUT.PUT_LINE(V_TEXT || ' BUT MANAGEABLE');
END;
    

OR

SET SERVEROUTPUT ON;
DECLARE
    V_TEXT STRING(50) NOT NULL := 'WELCOME';
BEGIN
    DBMS_OUTPUT.PUT_LINE(V_TEXT || ' ITS_RAWAT');
END;
    

---