Fundamentals of PL/SQL

PL/SQL (Procedural Language/SQL) is a powerful language that extends the capabilities of SQL by adding procedural programming constructs. It allows you to write complex logic, control flow statements, and modular code within a database environment.

PL/SQL Data Types

PL/SQL supports a variety of data types to store different kinds of data. Some of the most common data types include:

• Number: Used to store numeric values.

  • NUMBER(precision, scale): Specifies precision and scale.

• VARCHAR2: Used to store variable-length character strings.

  • VARCHAR2(size): Specifies the maximum length of the string.

• CHAR: Used to store fixed-length character strings.

  • CHAR(size): Specifies the fixed length of the string.

• DATE: Used to store date and time values.

• BOOLEAN: Used to store Boolean values (TRUE or FALSE).

• CLOB: Used to store large character objects.

• BLOB: Used to store large binary objects.

Variables and Constants

• Variables:

  • Declared using the DECLARE keyword.

  • Can be assigned values using the := operator.

  • Can be used to store temporary values during program execution.

• Constants:

  • Declared using the CONSTANT keyword.

  • Assigned a value at the time of declaration.

  • Cannot be modified after declaration.

Example:

DECLARE
  v_first_name VARCHAR2(50) := 'John';
  v_last_name  VARCHAR2(50) := 'Doe';
  v_salary     NUMBER(10, 2) := 50000;
  
BEGIN
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_first_name || ' ' || v_last_name);
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

Scope and Visibility of Variables

The scope of a variable determines where it can be accessed within a PL/SQL block. PL/SQL has three levels of scope:

1. Local Scope: Variables declared within a block are local to that block.

2. Package Scope: Variables declared within a package specification are accessible to all procedures and functions within that package.

3. Global Scope: Variables declared in the DECLARE section of an anonymous block are global to that block.

Key Points:

• PL/SQL is a powerful language for database programming.

• Understanding data types, variables, and constants is crucial for writing effective PL/SQL code.

• The scope and visibility of variables determine where they can be accessed within a PL/SQL program.

• By mastering these fundamental concepts, you can create complex and efficient database applications.