How to Use Stored Procedure in SQLite?

SQLite is a popular, lightweight, self-contained, serverless, and open-source relational database management system (RDBMS) that is widely used in various applications. SQLite does not directly support stored procedures like other database management systems (DBMS) such as MySQL or PostgreSQL. To achieve the functionality of stored procedures in SQLite there are different approaches. In this article, we will see all approaches, their syntax, and examples.

How to Use Stored Procedure in SQLite

In SQLite, stored procedures can be implemented using User-defined Functions (UDFs) or Triggers. UDFs allow defining custom functions, while Triggers execute actions in response to database events, both enabling stored procedure functionality.

Using User-defined Functions (UDFs)
Using Triggers.

1. User-defined Functions (UDFs)

User-defined functions (UDFs) a functions in SQLite that extend the functionality of the database by creating custom functions by user. With the help of UDFs users can create functions with their logic and condition according to its requirements.

Syntax:

To create stored procedures in SQLite we have to define user-defined functions using the ‘CREATE FUNCTION‘ statement.

CREATE FUNCTION function_name(param1, param2, ...) RETURNS return_type AS 
BEGIN
        -- SQL statement 
END ;

Here,

function_name: It is the name of a user-defined function.
param1,param2, etc,: These are input parameters.
return_type: it is the data type that the function returns.
BEGIN and END: These are delimited in the body of the function.

Example: Finding the Total Price of the Product

In this example, we want to retrieve the total price of products in specific category.

First of all, we create a product table.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT,
    price REAL,
    category_id INTEGER
);

Now we write UDFs code for this product table to find total price of the product.

CREATE FUNCTION total_price(category_id INTEGER) RETURNS REAL AS 
BEGIN
       DECLARE total REAL;
       SELECT SUM(price) INTO total FROM products WHERE category_id = category_id;
       RETURN total;
END;

Now we can call this stored procedure to get the total price:

SELECT total_price(1);

Here 1 is an argument that passed to a total_price UDF.

Output:

Function "total_price" created successfully.

Explanation: This output indicates that the UDF was successfully created without any errors. If there were any issues during creation, the compiler would provide error messages detailing the problem.

2. Using Triggers

Triggers in SQLite are special type of stored program that are automatically executed when any specific event occurs such as inserting, deleting, or updating data in a table. Triggers are not designed for implementing the stored procedures, they can be used for encapsulating and executing the complex logic in response to manipulating the operation.

Syntax:

To create the trigger in SQLite we use the CREATE TRIGGER statement

CREATE TRIGGER [IF NOT EXISTS] trigger_name
    [BEFORE|AFTER] [DELETE|INSERT|UPDATE] [OF column1, column2, ...]
    ON table_name
    [FOR EACH ROW]
    [WHEN condition]
BEGIN
    -- Trigger logic here
END;

Here,

IF NOT EXISTS: Optional clause to avoid an error if the trigger already exists.
trigger_name: The name of the trigger.
BEFORE|AFTER: Specifies whether the trigger should be executed before or after the triggering event.
DELETE|INSERT|UPDATE: The type of data manipulation operation that will trigger the execution.
OF column1, column2, …: Optional list of columns to which the trigger should respond (for UPDATE triggers).
table_name: The name of the table on which the trigger will operate.
FOR EACH ROW: Optional clause to specify that the trigger should be executed for each row affected by the triggering event.
WHEN condition: Optional condition that must be satisfied for the trigger to execute.

Example: Finding the Total Price of a Product

First of all, we create a category total table that stores the total price of products according to the category.

CREATE TABLE category_totals (
    category_id INTEGER PRIMARY KEY,
    total_price REAL,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

Now, we create the trigger for update and insert operation

-- Trigger to update category totals when a product is inserted or updated

CREATE TRIGGER update_category_total
AFTER INSERT OR UPDATE ON products
BEGIN
    UPDATE category_totals
    SET total_price = (
        SELECT SUM(price)
        FROM products
        WHERE category_id = NEW.category_id
    )
    WHERE category_id = NEW.category_id;
END;

-- Trigger to handle category total when a product is deleted

CREATE TRIGGER delete_category_total
AFTER DELETE ON products
BEGIN
    UPDATE category_totals
    SET total_price = (
        SELECT SUM(price)
        FROM products
        WHERE category_id = OLD.category_id
    )
    WHERE category_id = OLD.category_id;
END;

Output:

Trigger "update_category_total" created successfully.
Trigger "delete_category_total" created successfully.

This trigger is triggered when an update or insert operation can be done on the product table.

Explanation: This output indicates that both triggers were successfully created without any errors. If there were any issues during creation, the compiler would provide error messages detailing the problem.

Conclusion

SQLite is a light weight and very popular database management system (DBMS). SQLite does not support stored procedures but we can achieve similar functionality using User-defined,functions and Triggers. It also provides very important features like encapsulation of SQL code which increase the reusability, maintainability, and readability of SQL code.

Article Tags :

Databases

SQLite