Identify Different Types of PL/SQL Variables

Variables can be used to store the results of a query for later processing or to calculate values to insert into Oracle database tables.PL/SQL variables can be used anywhere in an expression, either in SQL or in PL/SQL statements. A variable must be declared before referencing it in other statements, including other declarative statements. The following series of images discuss more about PL/SQL variables.

A PL/SQL variable can have

1. any SQL data type (such as CHAR, DATE, or NUMBER) or
2. a PL/SQL- only data type (such as BOOLEAN or PLS_INTEGER).

One has a PL/SQL-only data type; the others have SQL data types.
PL/SQL Variable Declarations

SQL> DECLARE
2	part_number NUMBER(6);		-- SQL data type
3	part_name VARCHAR2(20);		-- SQL data type
4	in_stock BOOLEAN;		-- PL/SQL-only data type
5	part_price NUMBER(6,2);		-- SQL data type
6	part_description VARCHAR2(50);	-- SQL data type
7	BEGIN
8	NULL;
9	END;
10 /
PL/SQL procedure successfully completed.
SQL>

PL/SQL, or Procedural Language/Structured Query Language, is Oracle's proprietary procedural extension to the widely used SQL language. Developed by Oracle Corporation for use with its relational database management systems (RDBMS), PL/SQL allows developers to create powerful, robust, and efficient server-side applications. This article provides an overview of PL/SQL, including its key features, benefits, and typical use cases.

1. Key Features of PL/SQL: PL/SQL combines the data manipulation capabilities of SQL with the procedural constructs of traditional programming languages, offering a range of features that enhance application development, including:
   
   • Block structure: PL/SQL code is organized into units called blocks, which contain declarations, executable statements, and exception handlers.
   • Variables and constants: PL/SQL supports the declaration of variables and constants, allowing developers to store and manipulate data within program logic.
   • Control structures: PL/SQL provides conditional and iterative control structures, such as IF-THEN-ELSE, LOOP, and CASE, enabling complex decision-making and repetitive processing.
   • Error handling: Exception handling mechanisms in PL/SQL allow developers to gracefully handle runtime errors and ensure the stability and reliability of applications.
   • Procedures and functions: PL/SQL supports the creation of modular, reusable procedures and functions, encapsulating program logic for improved maintainability and code reuse.
   • Triggers: PL/SQL enables the creation of triggers, which are special types of stored procedures that automatically execute in response to specific events in the database, such as data modifications or schema changes.
   • Cursors: Cursors in PL/SQL facilitate the retrieval and manipulation of multiple rows of data from the database, supporting both implicit and explicit cursor management.
2. Benefits of PL/SQL: PL/SQL offers several advantages for developers and organizations, including:
   
   • Enhanced performance: PL/SQL enables the execution of multiple SQL statements within a single block, reducing network traffic and improving overall performance.
   • Scalability: PL/SQL's support for modular programming and reusable code components promotes scalability and ease of maintenance in large-scale applications.
   • Security: PL/SQL's support for server-side programming minimizes the exposure of application logic to end-users, enhancing security and reducing the risk of unauthorized access.
   • Portability: PL/SQL code is portable across different Oracle RDBMS versions and platforms, allowing for seamless migration and compatibility.
3. Typical Use Cases for PL/SQL: PL/SQL is used in a variety of applications and scenarios, including:
   
   • Data validation: PL/SQL can be used to implement complex business rules and data validation logic, ensuring the consistency and integrity of data stored in the database.
   • Data transformation: PL/SQL facilitates the transformation and manipulation of data, enabling developers to perform calculations, aggregations, and other data processing tasks.
   • Workflow automation: PL/SQL can be employed to automate business processes and workflows, streamlining operations and improving efficiency.
   • Event-driven processing: PL/SQL triggers enable event-driven processing, allowing developers to automatically perform actions in response to changes in the database.

PL/SQL is a powerful and versatile procedural language designed for use with Oracle's RDBMS. Its rich feature set, including block structure, control structures, error handling, and support for modular programming, enables developers to create robust and efficient server-side applications. The benefits of PL/SQL, such as enhanced performance, scalability, security, and portability, make it an essential tool for organizations leveraging Oracle's database technologies.

The standard Procedural Language (PL) for Structured Query Language (SQL, pronounced sequel) is referred to as PL/SQL. PL/SQL is an imperative 3GL that was designed specifically for the seamless processing of SQL commands. It provides specific syntax for this purpose and supports exactly the same datatypes as SQL. Server-side PL/SQL is stored and compiled in Oracle Database and runs within the Oracle executable. It automatically inherits the robustness, security, and portability of Oracle Database.
What is PL/SQL? It is the procedural (and sometimes object-oriented) programming extension to SQL, provided by Oracle, exclusively for Oracle. If you are familiar with another programming language called Ada, you will find striking similarities in PL/SQL. The reason they are so similar is that PL/SQL grew from Ada, borrowing many of its concepts from it. The PL in PL/SQL stands for procedural language. PL/SQL is a proprietary language not available outside the Oracle Database. It is a third-generation language (3GL) that provides programming constructs similar to other 3GL languages, including variable declarations, loops, error handling. Historically, PL/SQL was procedural only. PL/SQL can now be considered part of the object-oriented category of languages. Should we change the name to PL/OO/SQL?
Answer: No.
As you have seen, variables provide you with a medium for manipulating data.

Your program stores values in variables and constants. As the program executes, the values of variables can change, but the values of constants cannot. You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package.
Declarations:

1. allocate storage space for a value,
2. specify its datatype, and
3. name the storage location so that you can reference it.

Some examples follow:

DECLARE
birthday DATE;
emp_count SMALLINT := 0;

The first declaration names a variable of type DATE. The second declaration names a variable of type SMALLINT and uses the assignment operator to assign an initial value of zero to the variable. The next examples show that the expression following the assignment operator can be arbitrarily complex and can refer to previously initialized variables:

DECLARE
pi REAL := 3.14159;
radius REAL := 1;
area REAL := pi * radius**2;

By default, variables are initialized to NULL, so it is redundant to include ":= NULL" in a variable declaration.
In the next lesson, the different variable datatypes will be discussed.