PL/SQL in Oracle
The proprietary procedural language extension to SQL developed by Oracle Corporation is called PL/SQL, or Procedural Language extensions to the Structured Query Language. In order to overcome the shortcomings of SQL, including its lack of array handling, procedural control, and iterative (looping) capabilities, it was introduced. Building mission-critical applications against the Oracle database is made easier using PL/SQL, which combines the data manipulation capabilities of SQL with the strong characteristics of a procedural language.
Through the construction of stored procedures and packages, this language enables developers to add complex programming logic to the execution of SQL queries, trigger database events, and codify business rules. In addition to populating databases, PL/SQL allows you to read and edit data, execute intricate logical operations, construct a variety of stored objects, transfer data between databases, and even create and display web pages. Iterative processing (using LOOP, FOR loop, and WHILE loop constructions), conditional logic (such IF and CASE expressions), variables, and strong exception handling to control failures are some of its primary procedural characteristics.
Additionally, PL/SQL provides a wide variety of datatypes, including sophisticated data structures like collections (Oracle’s equivalent of arrays) and records (like relational table rows). One of PL/SQL’s main advantages is its close compatibility with Oracle’s SQL language, which enables SQL statements to be run straight from PL/SQL programs without the use of intermediary application programming interfaces (APIs) like ODBC or JDBC. On the other hand, it is also possible to execute user-defined PL/SQL functions straight from SQL statements.
PL/SQL Architecture
The Oracle database itself is intimately incorporated with the programming environment known as PL/SQL. The Oracle server comes with the PL/SQL engine, which is an essential part of it. Data management and procedural logic can interact with great efficiency and coherence thanks to this architecture.
All database calls are coordinated by the Oracle server when a PL/SQL program is running. The PL/SQL engine then assumes control of the program’s memory structures and logical execution flow after the compiled PL/SQL program has been loaded into memory. The SQL engine is also in charge of sending data requests to the database at the same time. A “closed system” with this division of labour guarantees extremely effective programming.
Context switching is an essential component of this integration. When control is passed from the PL/SQL engine (for procedural statements) to the SQL engine (for SQL statements), this is the overhead that results. Although SQL and PL/SQL have a close syntactic relationship, these alterations have an underlying performance impact. Oracle introduced bulk processing statements like BULK COLLECT and FORALL to counteract this.
By compressing many context switches into a single interaction with the SQL engine, these statements enable the PL/SQL engine to greatly enhance the performance of applications that use PL/SQL to perform repetitive SQL operations. A FORALL statement, for example, can minimise context shifts by producing a series of SQL statements that are then sent to the SQL engine in a single roundtrip.
This design is also used in PL/SQL memory management. Each connection to the database has its own Program Global Area (PGA), where program data stated within a PL/SQL block uses RAM for dedicated server connections. Since information may frequently be retrieved from the PGA more quickly than from the System Global Area (SGA), PGA-based caching can provide performance gains.
Basic PL/SQL Block Structure
The block is the essential component of all PL/SQL programming. The scope for declaring variables and managing exceptions, as well as the execution flow, are both specified by a PL/SQL block. Both named (saved procedures, functions, packages, or triggers, which are stored in the database and can be called repeatedly) and anonymous (unnamed, usually executed once) blocks are available. Anonymous procedures are similar to anonymous blocks.
Up to four separate pieces can make up a typical PL/SQL block, however only one is required:
- DECLARE Section (Optional): In this section, all variables, constants, cursors, and nested subblocks that will be used in the program are defined and initialised, beginning with the
DECLAREkeyword. - BEGIN Section (Mandatory): The block’s executable portion is enclosed between the
BEGINandENDkeywords. It includes the actual PL/SQL statements that carry out the logic of the program, including loops and flow-control directives likeIFstatements. Even aNULLstatement stating that nothing should be executed is sufficient to constitute at least one executable line of code in this section. - EXCEPTION Section (Optional): This section offers customisable handling for error conditions that may arise during the
BEGINsection’s execution and is started by theEXCEPTIONkeyword. It enables the program to handle unforeseen circumstances with grace and avoids abrupt termination. - END Keyword: A semicolon (
;) and theENDkeyword mark the end of each PL/SQL block. A forward slash (/) on a new line following theEND;statement is also necessary in many client environments, including SQL*Plus, in order to execute the block.
Developers may write extremely well-organised, clear, and maintainable code for a variety of application requirements because to the versatility of this block structure.
Declaring Variables
The DECLARE part of a block is where variables and constants are declared and optionally initialised in PL/SQL programs. Variables are used to store values that may change while the program is running, giving intermediate results or database-retrieved data dynamic storage. Constants, on the other hand, are given a value when they are declared and stay that value during the course of the program.
The basic syntax for declaring a variable or constant is: name datatype [NOT NULL] [:= | DEFAULT default_assignment];.
Values are assigned to variables and constants using the := (set equal to) operator. If a variable is declared with the NOT NULL constraint, it is mandatory to assign an initial value to it during its declaration. For example: l_counter NUMBER := 0; l_today DATE := SYSDATE;
Standard SQL types like NUMBER, CHAR, VARCHAR2, DATE, TIMESTAMP, CLOB, and BLOB are among the many datatypes that PL/SQL supports. Other types that are unique to PL/SQL include BOOLEAN, PLS_INTEGER, and user-defined kinds. For example, in PL/SQL, the variable-length alphanumeric VARCHAR2 datatype can hold up to 32,767 bytes.
A particularly valuable feature for variable declaration is the %TYPE attribute. This attribute allows you to define a variable’s datatype directly from an existing database table column (table_name.column_name%TYPE). This means that if the underlying database column’s definition changes, the PL/SQL variable’s definition automatically adapts, thus enhancing code maintainability and robustness. For example, v_product_id products.prod_id%TYPE links the v_product_id variable’s type to the prod_id column in the products table.
DBMS_OUTPUT
PL/SQL programs may show information thanks to the built-in Oracle DBMS_OUTPUT package, which is mostly used for debugging and giving the user instant feedback. Sending text to a buffer, which the client application usually reads and displays, is how it operates.
The command SET SERVEROUTPUT ON must be run in your client environment (such as SQL*Plus or SQL Developer) in order to allow server output before you may read the output produced by DBMS_OUTPUT. No information will be shown and any calls to DBMS_OUTPUT procedures will run silently without this command.
DBMS_OUTPUT.PUT_LINE, the package’s most frequently used procedure, adds a newline character and writes a line of text to the buffer. DBMS_OUTPUT.NEW_LINE must manually flush the buffer if you want to do so since the PUT method inserts content into the buffer without a newline.
Not all PL/SQL types, including BOOLEAN values, are automatically supported by DBMS_OUTPUT, even though it may implicitly convert several datatypes (such as NUMBER and DATE) to VARCHAR2 for display. In these situations, you would need to convert the Boolean value to a displayable string using an IF statement or write a bespoke wrapper method before using PUT_LINE. In recent Oracle versions, the DBMS_OUTPUT buffer, which is maintained by each user session, is usually set to UNLIMITED, whereas prior versions had a limit of one million bytes. When the outermost PL/SQL block has finished running, the buffered output is typically shown.
Practical Demonstration
Let’s put these concepts into practice. We will create a simple PRODUCTS table, insert some values, and then run a PL/SQL anonymous block to process and display product information using variables, conditional logic, loops, and DBMS_OUTPUT.
Create Table and Insert Values
First, ensure SERVEROUTPUT is enabled to see the script’s output in SQL*Plus or SQL Developer:
SET SERVEROUTPUT ON;
-- Drop table if it already exists (for clean execution)
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE PRODUCTS';
EXCEPTION
WHEN OTHERS THEN
NULL; -- Ignore error if table does not exist
END;
/
-- Create the PRODUCTS table
CREATE TABLE PRODUCTS (
PRODUCT_ID NUMBER(6) PRIMARY KEY,
PRODUCT_NAME VARCHAR2(100) NOT NULL,
LIST_PRICE NUMBER(10, 2) NOT NULL,
STOCK_QUANTITY NUMBER(5) DEFAULT 0
);
/
-- Insert some sample values into the PRODUCTS table
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, LIST_PRICE, STOCK_QUANTITY) VALUES (101, 'Laptop Pro', 1200.00, 50);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, LIST_PRICE, STOCK_QUANTITY) VALUES (102, 'Wireless Mouse', 25.50, 200);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, LIST_PRICE, STOCK_QUANTITY) VALUES (103, 'Mechanical Keyboard', 80.00, 10);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, LIST_QUANTITY) VALUES (104, 'Webcam HD', 75.00); -- Default stock_quantity will be 0
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, LIST_PRICE, STOCK_QUANTITY) VALUES (105, 'External SSD 1TB', 150.00, 120);
COMMIT;
/
-- Verify the inserted data
SELECT * FROM PRODUCTS ORDER BY PRODUCT_ID;Output of Table Creation and Initial Data:
Table dropped.
PL/SQL procedure successfully completed.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.
PRODUCT_ID PRODUCT_NAME LIST_PRICE STOCK_QUANTITY
---------- -------------------------------- ---------- --------------
101 Laptop Pro 1200.00 50
102 Wireless Mouse 25.50 200
103 Mechanical Keyboard 80.00 10
104 Webcam HD 75.00 0
105 External SSD 1TB 150.00 120PL/SQL Anonymous Block Code
This PL/SQL block will:
- Declare variables to hold product details.
- Fetch product details using
SELECT INTO. - Apply a discount based on
STOCK_QUANTITYusingIF-ELSIF-ELSE. - Simulate a simple loop and display its iteration.
- Update the
LIST_PRICEin the table. - Use
DBMS_OUTPUT.PUT_LINEfor all display messages. - Include a basic exception handler.
SET SERVEROUTPUT ON;
DECLARE
-- Declare variables based on table column definitions using %TYPE
v_product_id PRODUCTS.PRODUCT_ID%TYPE := 103; -- We'll process product 103
v_product_name PRODUCTS.PRODUCT_NAME%TYPE;
v_list_price PRODUCTS.LIST_PRICE%TYPE;
v_stock_quantity PRODUCTS.STOCK_QUANTITY%TYPE;
-- Declare a constant for the discount threshold
c_high_stock_discount CONSTANT NUMBER(4,2) := 0.10; -- 10% discount
c_medium_stock_discount CONSTANT NUMBER(4,2) := 0.05; -- 5% discount
c_low_stock_threshold CONSTANT NUMBER(5) := 20;
-- Declare a variable for the calculated new price
v_new_price NUMBER(10,2);
-- Declare a loop counter
i_loop_counter PLS_INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Starting PL/SQL Product Processing ---');
-- Retrieve product details for the specified product ID
SELECT PRODUCT_NAME, LIST_PRICE, STOCK_QUANTITY
INTO v_product_name, v_list_price, v_stock_quantity
FROM PRODUCTS
WHERE PRODUCT_ID = v_product_id;
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Processing Product ID: ' || v_product_id);
DBMS_OUTPUT.PUT_LINE('Product Name: ' || v_product_name);
DBMS_OUTPUT.PUT_LINE('Original List Price: $' || TO_CHAR(v_list_price, '99999.99'));
DBMS_OUTPUT.PUT_LINE('Stock Quantity: ' || v_stock_quantity);
-- Apply conditional logic based on stock quantity
IF v_stock_quantity > 100 THEN
v_new_price := v_list_price * (1 - c_high_stock_discount);
DBMS_OUTPUT.PUT_LINE('Applying ' || TO_CHAR(c_high_stock_discount * 100) || '% discount for high stock.');
ELSIF v_stock_quantity > c_low_stock_threshold THEN
v_new_price := v_list_price * (1 - c_medium_stock_discount);
DBMS_OUTPUT.PUT_LINE('Applying ' || TO_CHAR(c_medium_stock_discount * 100) || '% discount for medium stock.');
ELSE
v_new_price := v_list_price; -- No discount or potential price increase logic could go here
DBMS_OUTPUT.PUT_LINE('No discount applied, stock is low (' || v_stock_quantity || ' units).');
END IF;
DBMS_OUTPUT.PUT_LINE('New Calculated Price: $' || TO_CHAR(v_new_price, '99999.99'));
-- Simulate a simple loop for demonstration
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('--- Demonstrating a simple loop (3 iterations) ---');
FOR i_loop_counter IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE('Loop Iteration: ' || i_loop_counter);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Loop complete.');
-- Update the product's list price in the database
UPDATE PRODUCTS
SET LIST_PRICE = v_new_price
WHERE PRODUCT_ID = v_product_id;
COMMIT;
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Product ID ' || v_product_id || ' updated successfully with new price.');
DBMS_OUTPUT.PUT_LINE('--- PL/SQL Product Processing Completed ---');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: Product ID ' || v_product_id || ' not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLCODE || ' - ' || SQLERRM);
ROLLBACK; -- Rollback any changes on error
END;
/
-- Verify the updated data in the PRODUCTS table
SELECT * FROM PRODUCTS WHERE PRODUCT_ID = 103;
Output of PL/SQL Block:
If v_product_id is set to 103 (Mechanical Keyboard, Stock: 10):
--- Starting PL/SQL Product Processing ---
Processing Product ID: 103
Product Name: Mechanical Keyboard
Original List Price: $80.00
Stock Quantity: 10
No discount applied, stock is low (10 units).
New Calculated Price: $80.00
--- Demonstrating a simple loop (3 iterations) ---
Loop Iteration: 1
Loop Iteration: 2
Loop Iteration: 3
Loop complete.
Product ID 103 updated successfully with new price.
--- PL/SQL Product Processing Completed ---
PL/SQL procedure successfully completed.
PRODUCT_ID PRODUCT_NAME LIST_PRICE STOCK_QUANTITY
---------- -------------------------------- ---------- --------------
103 Mechanical Keyboard 80.00 10
If v_product_id is set to 101 (Laptop Pro, Stock: 50):
-- Change v_product_id to 101 in the DECLARE section and re-run
-- DECLARE
-- v_product_id PRODUCTS.PRODUCT_ID%TYPE := 101;
--- Starting PL/SQL Product Processing ---
Processing Product ID: 101
Product Name: Laptop Pro
Original List Price: $1200.00
Stock Quantity: 50
Applying 5% discount for medium stock.
New Calculated Price: $1140.00
--- Demonstrating a simple loop (3 iterations) ---
Loop Iteration: 1
Loop Iteration: 2
Loop Iteration: 3
Loop complete.
Product ID 101 updated successfully with new price.
--- PL/SQL Product Processing Completed ---
PL/SQL procedure successfully completed.
PRODUCT_ID PRODUCT_NAME LIST_PRICE STOCK_QUANTITY
---------- -------------------------------- ---------- --------------
101 Laptop Pro 1140.00 50