What are the different types of PL/SQL blocks, and how do they differ?

PL/SQL (Programming Language/Structured Query Language) is a powerful and efficient programming language that can be used to interact with Oracle databases. PL/SQL blocks are sections of PL/SQL code that can be executed together, and different types of PL/SQL blocks serve different purposes.

The different types of PL/SQL blocks are:

1. Anonymous PL/SQL blocks - These are standalone PL/SQL blocks that can be executed once. They do not have a name and cannot be called from other programs. Anonymous blocks can be useful for testing or for performing simple operations.

Example:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;

2. Stored procedures - Stored procedures are named PL/SQL blocks that can be called from other programs or scripts. They are stored in the database and can be reused as many times as needed. Stored procedures can accept input parameters and can also return values.

Example:

CREATE OR REPLACE PROCEDURE get_employee_details (
  p_employee_id  IN  NUMBER,
  p_name         OUT VARCHAR2,
  p_salary       OUT NUMBER
)
AS
BEGIN
  SELECT first_name || ' ' || last_name, salary
  INTO   p_name, p_salary
  FROM   employees
  WHERE  employee_id = p_employee_id;
END;

3. Stored functions - Stored functions are similar to stored procedures, but they always return a value. Stored functions can be called from other programs or scripts and can be used in SQL statements.

Example:

CREATE OR REPLACE FUNCTION calculate_bonus (p_salary IN NUMBER)
RETURN NUMBER
AS
  bonus NUMBER;
BEGIN
  IF p_salary > 10000 THEN
    bonus := p_salary * 0.1;
  ELSE
    bonus := p_salary * 0.05;
  END IF;
  RETURN bonus;
END;

4. Packages - Packages are collections of related functions, procedures, and other constructs that can be stored in the database and reused by other programs or scripts. Packages provide a higher level of encapsulation and organization than standalone functions or procedures.

Example:

CREATE OR REPLACE PACKAGE employee_mgmt AS
  FUNCTION get_employee_name (p_employee_id IN NUMBER) RETURN VARCHAR2;
  PROCEDURE update_employee_salary (p_employee_id IN NUMBER, p_new_salary IN NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY employee_mgmt AS
  FUNCTION get_employee_name (p_employee_id IN NUMBER) RETURN VARCHAR2
  AS
    name VARCHAR2(100);
  BEGIN
    SELECT first_name || ' ' || last_name
    INTO   name
    FROM   employees
    WHERE  employee_id = p_employee_id;
    RETURN name;
  END;
  
  PROCEDURE update_employee_salary (p_employee_id IN NUMBER, p_new_salary IN NUMBER)
  AS
  BEGIN
    UPDATE employees
    SET    salary = p_new_salary
    WHERE  employee_id = p_employee_id;
  END;
END;

In summary, PL/SQL blocks come in several different types, each with its own set of specific features and intended use. Understanding the different PL/SQL block types is crucial for designing efficient and effective database programs that can quickly and accurately retrieve, manipulate, and store data.