Packages

 

Architecture of PL/SQL

PL/SQL's architecture involves around the three main components working together:

• Code Block: A PL/SQL source program is essentially a collection of code blocks, each grouping related variable declarations and executable statements.
• PL/SQL Engine: The PL/SQL engine compiles submitted code blocks and interact with database server.
• Database Server: This is where the database objects (tables, views, etc.) are stored. The PL/SQL engine accesses to the database server and forwards SQL statements embedded in the submitted code blocks.

Architecture of PL/SQL

PL/SQL's architecture involves around the three main components working together:

• Code Block: A PL/SQL source program is essentially a collection of code blocks, each grouping related variable declarations and executable statements.
• PL/SQL Engine: The PL/SQL engine compiles submitted code blocks and interact with database server.
• Database Server: This is where the database objects (tables, views, etc.) are stored. The PL/SQL engine accesses to the database server and forwards SQL statements embedded in the submitted code blocks.

PL/SQL Code Block 

The PL/SQL code block is the basic units that makes up a PL/SQL source program. As shown in the figure on the right, a PL/SQL block is composed with three major sections: a declaration section, an executable section, and an exception-handling section. Each statement in any section must end with a semicolon (;), and each PL/SQL block must conclude with the keyword END followed by a semicolon and a slash (/).

PL/SQL code blocks can be either anonymous or named. Anonymous blocks are those that are not stored as procedures, functions, or packages in the database. These are typically used for testing and debugging purposes. On the other hand, named blocks are those with given callable names, allowing them to be reused to perform specific tasks. Commonly, what database programming refers to is creating named blocks using the PL/SQL programming language.

Declaration Section

Beginning with the special keyword DECLARE, the declaration section declares variables and/or constants that will be used in the subsequent executable section. Variables are a specific type of identifier used to store data temporarily in a computer memory during code execution. They act as named containers capable of holding assigned values in a callable status. Similar to variables, constants also hold values for the corresponding executable section. However, unlike variables, constants are fixed during execution and cannot be modified once declared. If there is no variable or constant to declare, the declaration section can be omitted. 

All PL/SQL identifiers, including variables, constants, and named blocks, must adhere naming rules, which are technical restrictions imposed by Oracle. Violating any of these rules will result in a compilation error:

• Length: Must be less than 31 characters.
• Start Character: Must begin with a letter (A-Z, a-z)^[2].
• Subsequent Characters: Can include letters, numbers, underscores (_), and dollar signs ($).
• Reserved Words: Cannot use any reserved words that are already defined by Oracle. 

In addition to naming rules, you should also consider naming conventions, which are widely accepted practices to enhance code readability and maintainability. While not enforced by PL/SQL syntax itself, following these conventions is highly recommended.

• Meaningful Names: Choose names that clearly reflect the purpose of the object.
• Lowercase: It is generally recommended to use lowercase for an object name.
• Prefixes and Suffixes: Use prefixes and suffixes sparingly and consistently.
• Variables: v_variable_name, e.g., v_current_inventory
• Constants: c_constant_name, e.g., c_sales_tax_rate
• Bind Variable: b_bine_name, e.g., b_customer_id
• Cursors: cur_cursor_name, e.g., cur_custoemrs
• Exception: e_exception_name, e.g., e_invalid_unit_price
• Procedures: p_procedure_name, e.g., p_updat_order_status
• Functions: f_function_name, e.g., f_calc_shipment_duration
• File Handlers: fh_file_handler_name e.g., fh_store_logo_img

In the declaration section, following a valid name for a variable or a constant, you should declare of which data type your it will contain. There are four main groups of native data types in PL/SQL:

• Scalar: A scalar type has ho internal components. It holds a single value, such as a number or character string.
• Composite: A composite type has internal components that can be manipulated individually, such as the elements of an array.
• Reference: A reference type holds values, called pointers, that designate other program items.
• Large Objects (LOBs): A LOB type holds values called lob locators, that specify the location of large objects, such as text blocks or graphic images, that are stored separately from other database data. 

Executable Section

The executable section is where the actual business logic is implemented. This section begins with the keyword BEGIN. For example:

SET SERVEROUTPUT ON[3]

DECLARE
    v_language VARCHAR2(15) := 'PL/SQL';
BEGIN
    dbms_output.put_line('Hello, ' || v_language || '!');
END;
/

In the declaration section, a scalar variable named v_language is declared with a data type of VARCHAR2(15). Next, the executable section processes the declared variable according to the given instructions: concatenating it with some additional strings and passing the result into the dbms_output.put_line^[4] for printing.

The executable section may include some instructions based on variables and/or constants declared in the declaration section, as well as literals, operators, and functions. PL/SQL supports additional operators tailored for procedural programming tasks, in addition to all standard SQL operators for data manipulation. Here is the list of PL/SQL operators: