Loading...

SQL/PSM Advanced

Learn concepts of Procedural SQL (SQL/PSM or SQL/Persistent Stored Modules) beyond basic SQL queries

55 Participants 30 Minutes Beginner

In this lab, you will delve into the advanced concepts of procedural SQL programming for MariaDB. Procedural SQL empowers you to write stored procedures, functions, triggers, and use cursors, enabling you to perform complex database operations efficiently. This knowledge is valuable for building robust and performant database-driven applications.

 

Prerequisites:

- Solid understanding of SQL and database concepts.

- Familiarity with basic procedural SQL programming.

- You can start by covering the “SQL-PSM: Basics”.

 

 

Procedures

Procedures are named sets of SQL statements that can be executed as a single unit.

They are useful for encapsulating logic that performs specific tasks, such as data manipulation, calculations, or reporting.

Procedures can accept parameters, and they can return result sets or output parameters.

Examples:

  • A procedure for calculating the total price of a shopping cart and applying discounts.

  • A procedure for generating monthly reports with financial data.

 

Functions

Functions in MariaDB are similar to procedures, but they return a single value.

They are often used for calculations or data transformations that result in a single output.

Functions can be called within SQL statements and used in queries.

Examples:

  • A function for calculating the average order value.

  • A function for formatting dates in a specific way.

 

Difference between Procedures and Functions

The primary difference between procedures and functions is that functions return values, while procedures do not.

Procedures are used for tasks that don't require returning a value, like data manipulation or printing reports.

Functions are used when you need to calculate or transform data and return a result to the caller.

Examples:

  • Use a procedure to update customer records.

  • Use a function to calculate the total order value.

 

Triggers

Triggers are special types of stored procedures that are automatically invoked in response to specific events or actions in the database.

They are used for enforcing constraints, auditing changes, or automating specific tasks.

Triggers can be set to run before or after an event.

Examples:

  • A trigger to audit changes to sensitive data.

  • A trigger to automatically update a timestamp when a row is modified.

 

Cursors

Cursors in MariaDB provide a way to retrieve and manipulate rows from the result set of a query.

They are often used in stored procedures and functions for more complex data processing.

Cursors offer control over row retrieval, allowing for row-by-row processing.

Examples:

  • Using a cursor to iterate through a result set and perform calculations on each row.

  • Employing a cursor to update records in a specific way based on certain criteria.

 

Summary

In this advanced procedural SQL lab, you will explore key concepts, including stored procedures, functions, triggers, and cursors. This knowledge will equip you with the tools to create powerful, efficient, and maintainable database-driven applications. You can then perform complex database operations, handle data transformations, and automate tasks within MariaDB, enhancing the capabilities of your database system.

Understanding these advanced concepts in procedural SQL is crucial for developing sophisticated applications that rely on MariaDB's data manipulation and management capabilities.

 

SQL/PSM Advanced

Support

Have a doubt? Got stuck somewhere?

 https://t.me/+uMUZaLqsvNE2OWZl

 support@btechbasics.in

Related Labs

course

Advanced Queries and Joins

Database

  • 30 m
  • Advance
  • 298
Learn advanced SQL queries and joins with MariaDB server and client
course

NoSQL Basics

Database

  • 30 m
  • Beginner
  • 140
Learn NoSQL basic concepts, commands and queries with MongoDB server and client
course

Mongo Express Basics

Database

  • 30 m
  • Beginner
  • 275
Learn to use Mongo Express web-based admin interface to working with MongoDB database
course

PHPMyAdmin Basics

Database

  • 30 m
  • Beginner
  • 159
Learn how PHPMyAdmin makes it easy to manage and work with SQL databases