A database cursor solves the problem of impedance mismatch. It acts as a filter between the result of a SQL query and the statements that process this result.

Cursors in SQL

A Cursor is a temporary memory that is allocated by the database server at the time of performing the Data Manipulation Language operations on a table, such as INSERT, UPDATE and DELETE etc. It is used to retrieve and manipulate data stored in the SQL table.

In MySQL, you cannot declare a cursor directly outside of a stored procedure or function. Cursors are generally declared within stored procedures, functions, or blocks of SQL code in MySQL database.

Using cursors, we can perform multiple operations on each row of a result set, with or without returning the original data.

Properties of Cursors

Following are the properties of MySQL Cursors −

• READ ONLY − We cannot update or modify any records in the table using the MySQL cursors. We can just fetch and process data from a table.

• Non-Scrollable − We can retrieve records from a table in a single direction, i.e. from the first record or the last. We cannot move backward or jump to a specific position within the result set.

• Asensitive Cursor − An asensitive cursor operates directly on the actual data in the database, it does not create a copy of the data. If any change is made to the data by other connections, it can affect the data that the cursor is working with.

In addition to the Asensitive cursor there is another type known as Insensitive Cursor. An insensitive cursor uses a temporary copy of the data. Therefore, these cursors are insensitive (not affected) to the changes that are made in the table.

Life Cycle of the Cursor

There are four steps to manage these cursors. Following diagram illustrates the lifecycle of an SQL cursor −

Now, let us discuss the phases of life cycle of the cursor one-by-one.

Declare Cursor Statement

In MySQL we can declare a cursor using the DECLARE statement and associate it with a SELECT statement to retrieve records from a database table.

However, this SELECT statement associated with a cursor does not use the INTO clause, as it”s purpose is to fetch and process rows rather than assigning values to variables.

Syntax

Following is the syntax to declare a cursor in MySQL database −

DECLARE cursor_name CURSOR FOR select_statement;

Open Cursor Statement

After declaring a cursor in MySQL, the next step is to open the cursor using the OPEN statement. It initializes the result-set, allowing us to fetch and process rows from the associated SELECT statement in the cursor.

Syntax

Following is the syntax to open a cursor in MySQL database −

OPEN cursor_name;

Fetch Cursor Statement

Then, we can use the FETCH statement to retrieve the current row pointed by the cursor, and with each FETCH, the cursor moves to the next row in the result set. This allows us to process each row one by one.

Syntax

Following is the syntax to fetch a cursor in MySQL database −

FETCH cursor_name INTO variable_list;

Close Cursor Statement

Once all the rows are fetched, we must close the cursor to release the memory associated with it. We can do this using the CLOSE statement.

Syntax

Following is the syntax to close a cursor in MySQL database −

CLOSE cursor_name;

Example

In this example, let us see how to manage a cursor in a stored procedure.

Assume we have created a table with the name CUSTOMERS using the CREATE TABLE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert some records into the CUSTOMERS table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 );

Now, we will create a backup table named ”CUSTOMERS_BACKUP” to store customer data −

CREATE TABLE CUSTOMERS_BACKUP (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   PRIMARY KEY (ID)
);

Here, we are creating a stored procedure named FetchCustomers to fetch customer names from the CUSTOMERS table and inserting them one by one into the BACKUP table. We are using a cursor to iterate through the rows and a handler to detect the end of the result-set, ensuring all names are processed −

DELIMITER //
CREATE PROCEDURE FetchCustomers()
BEGIN
   DECLARE done INT DEFAULT FALSE;
   DECLARE customer_id INT;
   DECLARE customer_name VARCHAR(255);
   DECLARE auto_id INT;

   -- Declare cursor
   DECLARE MY_CURSOR CURSOR FOR
   SELECT id, name FROM CUSTOMERS;

   -- Declare exit handler
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   -- Open cursor
   OPEN MY_CURSOR;

   -- Fetch and insert rows
   read_loop: LOOP
      FETCH MY_CURSOR INTO customer_id, customer_name;
      IF done = 1 THEN
         LEAVE read_loop;
      END IF;

      -- Insert the fetched data into the backup table
      INSERT INTO customers_backup VALUES (customer_id, customer_name);

      -- Get the last auto-generated ID used in the insertion
      SET auto_id = LAST_INSERT_ID();

   END LOOP;

    -- Close cursor
    CLOSE MY_CURSOR;
END //
DELIMITER ;

Once we create the procedure successfully, we can execute it using the CALL statement as shown below −

CALL FetchCustomers();

Verification

You can verify the contents of the CUSTOMERS_BACKUP table using the SELECT statement as shown below −

SELECT * FROM CUSTOMERS_BACKUP;

The contents of the table would be −

A Common Table Expression (CTE) can make it easier to manage and write complex queries by making them more readable and simple, like database views and derived tables. We can reuse or rewrite the query by breaking down the complex queries into simple blocks.

The SQL Common Table Expression

The WITH clause in MySQL is used to specify a Common Table Expression.

A Common Table Expression (CTE) in SQL is a one-time result set, i.e. it is a temporary table that exists only during the execution of a single query. It allows us to work with data specifically within that query, such as using it in SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, OR MERGE statements.

CTE is temporary because it cannot be stored anywhere for later use; once the query is executed, it is lost.

The MySQL WITH Clause

To specify common table expressions, we use WITH clause that consists of one or more comma-separated subclauses. Within each subclause, we can present a subquery that produces a result set and assigns a name to this subquery.

You cannot use the WITH clause in MySQL versions before 8.0.

Syntax

Following is the syntax to create a CTE using WITH clause −

WITH CTE_NAME (column_name) AS (query)
SELECT * FROM CTE_NAME;

Where,

• CTE_NAME − It is the name assigned to the CTE.
• column_name − It is the column names for the CTE, which can be useful for improving query readability.
• query − It defines the CTE and it can be any valid SQL query.
• After defining the CTE, you can reference it in subsequent queries within the same session.

Example

Assume we have created a table named CUSTOMERS in MySQL database using CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, we are inserting some records into the above created table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The table created is as shown below −

Here, we are creating a Common Table Expression (CTE) named CUSTOMER_AGE that selects all customers with an age of 23. We are then retrieving the ID, NAME, and AGE of these customers from the CTE.

WITH CUSTOMER_AGE AS (
SELECT * FROM customers WHERE AGE = 23)
SELECT ID, NAME, AGE FROM CUSTOMER_AGE;

Output

Following is the output of the above query −

CTE from Multiple Tables

We can also create a Common Table Expression (CTE) that combines data from multiple tables by using JOIN operations within the CTE”s subquery. To do this, we need to use the comma operator to separate each CTE definition, effectively merging them into a single statement.

Syntax

Following is the basic syntax for multiple Common Table Expression (CTE) −

WITH
   CTE_NAME1 (column_name) AS (query),
   CTE_NAME2 (column_name) AS (query)
SELECT * FROM CTE_NAME1
UNION ALL
SELECT * FROM CTE_NAME2;

We can use multiple Common Table Expressions (CTEs) with various SQL operations, such as UNION, UNION ALL, JOIN, INTERSECT, or EXCEPT.

Example

In here, we are defining two CTEs namely ”CUSTOMERS_IN_DELHI” and ”CUSTOMERS_IN_MUMBAI” to segregate customers based on their addresses in Delhi and Mumbai. Then, we are using the UNION ALL operator to combine the results from both CTEs into a single result set, retrieving customer information from both cities.

WITH
CUSTOMERS_IN_DELHI AS (
   SELECT * FROM CUSTOMERS WHERE ADDRESS = ''Delhi''),
CUSTOMERS_IN_MUMBAI AS (
   SELECT * FROM CUSTOMERS WHERE ADDRESS = ''Mumbai'')
SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_DELHI
UNION ALL
SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_MUMBAI;

Output

Output of the above query is as shown below −

Recursive CTE

A common table expression is a query that keeps referring back to its own result in a loop repeatedly until it returns an empty result.

A recursive query continually iterates across a subset of the data during its execution, and defines itself in a self-referencing manner. This self-referencing mechanism allows it to repeatedly process and expand its results until a stopping condition is met.

To make a CTE recursive, it must include a UNION ALL statement and provide a second definition of the query that utilizes the CTE itself. This allows the CTE to repeatedly reference to its own results, creating a recursive behaviour in the query.

Example

Now, we are using a recursive CTE named recursive_cust to retrieve data from the ”CUSTOMERS” table created above. Initially, we are selecting customers with salaries above 3000 and then recursively appending customers older than 25 to the result set using the UNION ALL operator −

WITH recursive_cust (ID, NAME, ADDRESS, AGE) AS (
   SELECT ID, NAME, ADDRESS, AGE
   FROM CUSTOMERS
   WHERE SALARY > 3000
   UNION ALL
   SELECT ID, NAME, ADDRESS, AGE
   FROM CUSTOMERS
   WHERE AGE > 25
)
SELECT * FROM recursive_cust;

Output

When the above query is executed, all data from the customers table whose age is greater than 25 or salary is greater than 3000 will be displayed recursively as shown below −

Example

In the following query, we are using a recursive CTE named Numbers to generate and display numbers from 1 to 5. The recursive part continually adds 1 to the previous value until it reaches 5, creating a sequence −

WITH RECURSIVE Numbers AS (
  SELECT 1 AS N
  UNION ALL
  SELECT N + 1 FROM Numbers WHERE N < 5
)
SELECT n FROM Numbers;

Output

After executing the above query, we get the following output −

Advantages of CTE

Following are the advantages of the CTE −

• CTE makes the code maintenance easier.

• It increases the readability of the code.

• It increases the performance of the query.

• CTE allows for the simple implementation of recursive queries.

Disadvantages of CTE

Following are the disadvantages of the CTE −

• CTE can only be referenced once by the recursive member.

• We cannot use the table variables and CTEs as parameters in a stored procedure.

• A CTE can be used in place of a view, but a CTE cannot be nested while views can.

SQL Database Tuning

Database Tuning in SQL is a set of activities performed to optimize a database and prevents it from becoming a bottleneck.

There are various techniques with which you can configure the optimal performance of a particular database. Database tuning overlaps with query tuning; so, good indexing and avoiding improper queries help in increasing the database efficiency. In addition, increasing storage, updating to latest database versions and investing in a more powerful CPU (if needed) are also some of the general techniques.

Database Tuning Techniques

We can implement the following techniques to optimize the performance of a database −

Database Normalization

Normalization is the process of removing of duplicate data from a database. We can normalize a database by breaking down larger tables into smaller related tables. This increases the performance of database as it requires less time to retrieve data from small tables instead of one large table.

Proper Indexes

In SQL, indexes are the pointers (memory address) to the location of specific data in database. We use indexes in our database to reduce query time, as the database engine can jump to the location of a specific record using its index instead of scanning the entire database.

Avoid Improper Queries

Choosing the correct query to retrieve data efficiently also improves the performance of a database. For example, choosing to retrieve an entire table when we only need the data in a single column will unnecessarily increase query time. So, query the database wisely.

Let us discuss some of the common improper queries made and how to rectify them to optimize the database performance.

1. Use SELECT fields instead of SELECT (*)

In large databases, we should always retrieve only the required columns from the database instead of retrieving all the columns, even when they are not needed. We can easily do this by specifying the column names in the SELECT statement instead of using the SELECT (*) statement.

Example

Assume we have created a table with name CUSTOMERS in MySQL database using CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query inserts values into this table using the INSERT statement −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Let us say we only want the data in ID, NAME and SALARY columns of the CUSTOMERS table. So, we should only specify those three columns in our SELECT statement as shown below −

SELECT ID, NAME, SALARY FROM CUSTOMERS;

Output

The output obtained is as shown below −

2. Use Wildcards

Wildcards (%) are characters that we use to search for data based on patterns. These wildcards paired with indexes only improves performance because the database can quickly find the data that matches the pattern.

Example

If we want to retrieve the names of all the customers starting with K from the CUSTOMERS table, then, the following query will provide the quickest result −

SELECT ID, NAME FROM CUSTOMERS WHERE NAME LIKE ''K%

Output

Following is the output of the above query −

3. Use Explicit Join

SQL JOINs are used to combine two tables based on a common column. There are two ways of creating a JOIN implicit join and explicit join. Explicit Join notation use the JOIN keyword with the ON clause to join two tables while the implicit join notation does not use the JOIN keyword and works with the WHERE clause.

Performance wise, they are both on the same level. However, in more complicated cases, the implicit join notation might produce completely different results than intended. Therefore, Explicit Joining is preferred.

4. Avoid using SELECT DISTINCT

The DISTINCT operator in SQL is used to retrieve unique records from the database. And on a properly designed database table with unique indexes, we rarely use it.

But, if we still have to use it on a table, using the GROUP BY clause instead of the DISTINCT keyword shows a better query performance (at least in some databases).

5. Avoid using Multiple OR

The OR operator is used to combine multiple conditions when filtering a database. Whenever we use OR in a filter condition each statement is processed separately. This degrades database performance as the entire table must be scanned multiple times to retrieve the data that matches the filter condition.

Instead, we can use a more optimized solution; by breaking the different OR conditions into separate queries, which can be processed parallelly by the database. Then, the results from these queries can be combined using UNION.

Example

For example, let us say we have a requirement of getting the details of all the customers whose age is greater than 25 or whose salary is greater than 2,000. The optimized query would be as show below −

SELECT ID, NAME FROM CUSTOMERS WHERE AGE > 25
UNION
SELECT ID, NAME FROM CUSTOMERS WHERE SALARY > 2000;

Output

After executing the above code, we get the following output −

6. Use WHERE instead of HAVING

The WHERE and HAVING clause are both used to filter data in SQL. However, WHERE clause is more efficient than HAVING. With WHERE clause, only the records that match the condition are retrieved. But with HAVING clause, it first retrieves all the records and then filters them based on a condition. Therefore, the WHERE clause is preferable.

Database Defragmentation

When the data is stored in a database, they are placed in contiguous physical locations. In this case, both logical locations and physical locations are in the same order.

However, when the database tables are altered by deleting or updating the records, indexes are also altered to suit the changes made. This will cause the indexes to be scattered across the storage. Physical locations also lose the contiguous allocation. Thus, reducing the database performance.

Defragmentation is the solution this problem. It will reorganize/rebuild the logical ordering of the indexes to match the physical ordering. But, this process first analyses the indexes and chooses whether they only need to be reorganized or rebuilt completely.

Built-In Tuning Tools

Some databases provide built-in tuning tools to monitor the database performance. For instance, the Oracle database provides the following tuning tools −

• EXPLAIN − In SQL, the EXPLAIN command give us the order in which a query is executed along with the estimated cost of each step. We can use this to find the query the least cost to optimize the database.

• tkprof − tkprof is a command that gives us various statistics, such as CPU and I/O usage of a query. By using these statistics, we can tune our queries to reduce CPU and I/O utilization to increase the efficiency of our database.

In SQL, we have two commonly used clauses that help us to manipulate data; Group By clause and Order By clause.

A Group By clause is used to arrange the identical data/records into groups and the Order By clause is used to sort the data in ascending or descending order.

The SQL Group By Clause

Using the GROUP BY clause we can organize the data in a table into groups (based on a column) and perform required calculations on them.

This clause is often used with the aggregate functions such as MIN(), MAX(), SUM(), AVG(), and COUNT() etc.

It is often used with the SELECT statement, and it is placed after the WHERE clause or before the HAVING clause. If we use the Order By clause, the Group By clause should precede the Order By clause.

Syntax

Following is the syntax of the SQL Group By clause −

SELECT column_name, aggregate_function() FROM table_name
WHERE condition GROUP BY column_name;

The aggregate_function() and the WHERE clause are optional in the above syntax.

Example

Assume we have created a table named CUSTOMERS that contains records of customers such as NAME, AGE, ADDRESS, and SALARY etc.., using the following CREATE statement −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, we are inserting values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The CUSTOMERS table will be created as −

In the SQL query below, we are using the Group by clause to group the rows based on their salaries from the CUSTOMERS table and counting the number of records in each group −

SELECT SALARY, COUNT(SALARY) from CUSTOMERS GROUP BY SALARY;

Output

When we execute the above query, the following result will be displayed −

The SQL Order By Clause

The ORDER BY clause is used to sort the query results. This clause is used at the end of a SELECT statement, following the WHERE, HAVING and GROUP BY clauses. We can sort the table column in ascending or descending order with the by specifying the sort order as ASC and DESC respectively. If we do not specify any order, it defaults to ascending order.

Syntax

Following is the syntax to sort the column value in ascending/descending order using the SQL ORDER BY clause −

SELECT column_name FROM table_name ORDER BY ASC/DSC;

Example

In the following query, we are retrieving the ID and NAME from the CUSTOMERS table and using the ORDER BY clause, we are sorting the names in ascending order.

SELECT ID, NAME FROM CUSTOMERS ORDER BY NAME;

Output

When we run the above query, we can see that the resultant table is sorted by name in ascending order.

Example

In the following example, we are retrieving the NAME, calculating the AVG SALARY, and using the GROUP BY clause to group the table by NAME.

SELECT NAME, AVG(SALARY) FROM CUSTOMERS GROUP BY NAME;

Output

When we run the above query, we get the name and average salary. The average salary is the same as the actual salary because there are no two or more than two records with the same name. As a result, the average salary is the same as the actual salary, and the table is grouped by name. as shown in the table below.

Example

In the following example, we are retrieving, NAME, AGE, and SALARY and using the ORDER BY clause to sort the AGE in the ascending order.

SELECT NAME, AGE, SALARY FROM customers ORDER BY AGE;

Output

The table generated by the above query is as shown below −

Group by vs Order by

Following table summarizes the differences between the Group By clause and Order by clause −

In SQL, we use the IN operator to simplify queries and reduce the need for multiple OR conditions. It allows us to match a value against a list of values. On the other hand, the EXISTS operator checks whether one or more rows exist in a subquery and returns either true or false based on this condition. If the subquery finds at least one row, the EXISTS operator returns true; otherwise, it returns false.

The SQL IN Operator

The IN operator in SQL is used to check if a particular value matches any within a given set. This set of values can be specified individually or obtained from a subquery. We can use the IN operator with the WHERE clause to simplify queries and reduce the use of multiple OR conditions.

Suppose we have a table named CUSTOMERS and we want to retrieve customer details based on their IDs. In this scenario, we can use the IN operator with the WHERE clause to fetch the details of these specific IDs.

Syntax

Following is the syntax of the SQL IN operator −

SELECT column_name
FROM table_name
WHERE column_name
IN (value1, value2, valueN);

In the above syntax, the column_name matches every value (value1, value2, … valueN). If the matches occur, The IN operators returns true; otherwise, false.

Example

First of all, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, add records into the above created table using the INSERT INTO statement as shown below −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table will be created as follows −

The following query retrieves the NAME and SALARY columns from the CUSTOMERS table for rows where the ID is 1, 2, or 3.

SELECT NAME, SALARY FROM CUSTOMERS WHERE ID IN(1, 2, 3);

Output

On executing the query above, the output is displayed as −

The SQL EXISTS Operator

The EXISTS operator is used to look for the existence of a row in a given table that satisfies a set of criteria. It is a Boolean operator that compares the result of the subquery to an existing record and returns true or false.

The returned value is true, if the subquery fetches single or multiple records; and false, if no record is matched. EXISTS operator follows the querys efficiency features, i.e. when the first true event is detected, it will automatically stop processing further.

We can use the EXISTS operator with the SELECT, UPDATE, INSERT and DELETE queries.

Syntax

Following is the basic syntax of SQL EXISTS operator −

SELECT column_name FROM table_name
WHERE EXISTS (
   SELECT column_name FROM table_name
   WHERE condition
);

Example

First of all, consider the CUSTOMERS table, and create another table named EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES (
   EID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   CITY CHAR (25),
   CONTACT INT,
   PRIMARY KEY (EID)
);

Now, let us insert some records into the EMPLOYEES table using the INSERT INTO statement as shown below −

INSERT INTO EMPLOYEES VALUES
(1, ''Varun'', 32, ''Ahmedabad'', 12345),
(2, ''Mahesh'', 22, ''Kashmir'', 34235 ),
(3, ''Suresh'', 43, ''Kerala'', 12355 );

The table will be created as follows −

In the following query, we are using the EXISTS operator to fetch the names and ages of CUSTOMERS whose AGE is same as the AGE in the EMPLOYEES table.

SELECT NAME, AGE
FROM CUSTOMERS
WHERE EXISTS(
   SELECT * FROM EMPLOYEES
   WHERE CUSTOMERS.AGE = EMPLOYEES.AGE
);

Output

Following is the output of the above query −

IN vs EXISTS

Following table summarizes all the differences between IN and EXISTS −

SQL NULL functions are used to perform operations on NULL values that are stored in the database tables.

A NULL value serves as a placeholder in the database when data is absent or the required information is unavailable. It is a flexible value not associated to any specific data type and can be used in columns of various data types, including string, int, varchar, and more.

Following are the various features of a NULL value −

• The NULL value is different from a zero value or a field containing a space. A record with a NULL value is one that has been left empty or unspecified during record creation.

• The NULL value assists us in removing ambiguity from data. Thus, maintaining the uniform datatype across the column.

SQL NULL Functions

To handle these NULL values in a database table, SQL provides various NULL functions. They are listed as follows −

• ISNULL()
• COALESCE()
• NULLIF()
• IFNULL()

The ISNULL() Function

The SQL ISNULL() function returns 0 and 1 depending on whether the expression is null or not. If the expression is null, then this function returns 1; otherwise, it returns 0.

Syntax

Following is the syntax for the ISNULL() function −

ISNULL(column_name)

Example

First of all let us create a table named CUSTOMERS, containing the personal details of customers including their name, age, address and salary etc., using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert records into this table using the INSERT INTO statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', NULL ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', NULL ),
(7, ''Indore'', 24, ''Indore'', 10000.00 );

The table will be created as −

Following is the query to check whether SALARY is NULL or not −

SELECT SALARY, ISNULL(SALARY) AS Null_value FROM CUSTOMERS;

Output

On execution of the above query, we get the column “SALARY” and Null_value. If the SALARY is NULL, then their null value is 1; otherwise, it is 0. −

The COALESCE() Function

The SQL COALESCE() function returns the first occurred NON-NULL expression among its arguments. If all the expressions are NULL, then the COALESCE() function will return NULL.

An integer is evaluated first in the COALESCE() function, and an integer followed by a character expression always produces an integer as the output.

Syntax

Following is the syntax for the COALESCE() function −

COALESCE(expression_1, expression_2, expression_n);

Example

In the following query, we are returning the first occurred NON-NULL value −

SELECT COALESCE (NULL, ''welcome'', ''tutorialspoint'') AS Result;

Output

On executing the above query, we get “welcome” as a result, because it is the first NON-NULL value −

Example

In the following query, we are using the COALESCE() function on the SALARY and AGE columns of CUSTOMERS table. The first NON-NULL values evaluated from these two columns are displayed in another column named “Result”.

SELECT NAME, SALARY, AGE, COALESCE(SALARY, AGE) AS Result FROM CUSTOMERS;

Output

When you execute the above query, we get the following table as a result −

The NULLIF() Function

The SQL NULLIF() function compares two expressions. If both expressions are the same, it returns NULL. Otherwise, it returns the first expression. This function can be used directly with clauses like SELECT, WHERE, and GROUP BY.

Syntax

Following is the syntax of NULLIF() function −

NULLIF(expression_1, expression_2);

Example

The following SQL query uses NULLIF() function to compare values in NAME and ADDRESS columns of the CUSTOMERS table. If the NAME value matches the ADDRESS value, the result is NULL; otherwise, it returns the NAME value. The result values are stored in another column called “Result”.

SELECT NAME, ADDRESS, NULLIF(NAME, ADDRESS) AS Result FROM CUSTOMERS;

Output

When you execute the above query, we get the following table as a result −

The IFNULL() Function

The IFNULL() function replaces the NULL values in a database table with a specific value. This function accepts two arguments. If the first argument is a NULL value, it is replaced with the second argument. Otherwise, the first argument is returned as it is.

This function does not work in the SQL Server database.

If both the arguments are NULL, the result of this function is also NULL.

Syntax

Following is the syntax for IFNULL() function −

IFNULL(column_name, value_to_replace);

Example

The following query evaluates the values in SALARY column of the CUSTOMERS table. Using the IFNULL() function, we are replacing the NULL values in this column (if any) with the value 5500 −

SELECT NAME, SALARY, IFNULL(SALARY, 5500) AS Result FROM CUSTOMERS;

Output

Following is the output of the above query −

The SQL CHECK Constraint

The SQL CHECK constraint is used to add conditions on a column of a table.

Once you add the check constraint on a column, it ensures that the data entered into the column meets the specified conditions. If a particular record does not meet the conditions, the database will prevent you from inserting or updating that record.

Suppose we have a table CUSTOMERS having a column AGE. We can add a CHECK constraint on this column to ensure that the age entered is always a positive number and not greater than 50 years. If someone tries to input a negative age or an age over 50, the database will reject it, ensuring that your data remains accurate and valid.

Check Constraint on Single Column

To add a check constraint on a column level, we have to specify the check constraint just after the column name during table creation.

Syntax

Following is the syntax to specify the check constraint on a single column −

CREATE TABLE table_name (
   column_name data_type CHECK (condition)
);

Example

In the following query, we are creating a table named CUSTOMERS. Here, we are specifying a column-level check constraint on the AGE column, that allows only those records to be inserted where the age value of the customer is greater than “20” −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL CHECK(AGE>=20),
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Verification

To verify whether the check constraint is added to the AGE column, we can use the following query in the MySQL database −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name=''CUSTOMERS

Output

The above query will show all the details of the CUSTOMERS table, including how many columns have check constraints and what constraints we have specified in the table as shown below −

Now, to verify if the CHECK constraint is working properly, let us insert a record into CUSTOMERS where AGE contains a value less than 20 (does not satisfy the given condition) −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 15, ''Ahmedabad'', 2000.00 );

The output of the above query is as shown below −

ERROR 3819 (HY000): Check constraint ''customers_chk_1'' is violated.

Check Constraint on Multiple Columns

We can also add check constraint on multiple columns of a table by specifying the conditions that must be met for the combination of values in those columns.

Suppose we have a table containing the details of products, including their start and end dates. We can add a CHECK constraint that ensures the end date is always greater than or equal to the start date. In this case, the constraint is checking the values in two columns (start date and end date) within the same row to make sure they follow a specific relationship.

Example

In the following example, we are specifying a column-level check constraint on multiple columns (AGE and SALARY) of the CUSTOMERS table. Here, the AGE column will allow only those records where the AGE is greater than or equal to 20, and the SALARY column will allow only those records where the SALARY is greater than 20000 −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL CHECK(AGE >= 20),
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2) CHECK(SALARY >= 20000),
   PRIMARY KEY (ID)
);

Verification

To verify whether the check constraint is applied on both the columns, we can use the following query in the MySQL database −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name=''CUSTOMERS

Output

It will show all the details of the created table, including how many columns have check constraints and what constraints we have specified in the table −

Now, we are inserting values into the CUSTOMERS table where the age is less than 20 and the salary is less than 20000.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, ''Ramesh'', 15, ''Ahmedabad'', 2000.00 );

The above query throws an error because the values passed in the AGE and the SALARY columns are not satisfying the CHECK constraints −

ERROR 3819 (HY000): Check constraint ''customers_chk_1'' is violated.

Check Constraint at the Table Level

We must use the check constraint before completing the table creation in order to ensure the check constraint at the table level.

Syntax

Following is the syntax to specify the check constraint on the table level −

CREATE TABLE table_name (
   column1 data_type,
   column2 data_type,...,
   CONSTRAINT constraint_name CHECK(column_name condition_value)
);

Example

In the following SQL query, we are creating a table PRODUCTS. In here, we are specifying a table level check constraint on the DATE_OF_ORDER column, that allows only those records to be inserted where the DATE_OF_ORDER is less than (before) “2023-02-09” −

CREATE TABLE PRODUCTS(
   PID INT NOT NULL,
   PNAME VARCHAR(30),
   DELIVERY_CITY VARCHAR(20),
   DATE_OF_ORDER Date NOT NULL,
   PRICE INT,
   PRIMARY KEY(PID),
   CONSTRAINT Constraint_DOO CHECK(DATE_OF_ORDER <= ''2023-02-09'')
);

Verification

We can verify the CHECK constraint on the created table using the following SQL query −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name=''PRODUCTS

Output

It will show all the details of the created table, including how many columns have check constraints on the table level as shown below −

In here, we are inserting values in the PRODUCTS which have the constraint less than “2023-02-09” on the column DATE_OF_ORDER −

INSERT INTO PRODUCTS VALUES
(001, ''Nike Shoe'', ''Ranchi'', ''2023-01-11'', 2000);

Following is the output of the above query −

Query OK, 1 row affected (0.01 sec)

Check Constraint on an Existing Column

We can use the ALTER TABLE statement to add the check constraint on an existing column of the table.

Syntax

Following is the Syntax to add a check-constraint on an existing table −

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK(ColumnName condition_value);

Example

In the following query, we are creating a table named CUSTOMERS −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

To add a check constraint on the AGE column, we are using the following query −

ALTER TABLE CUSTOMERS
ADD CONSTRAINT Constraint_Age CHECK (AGE >= 21);

Verification

To verify whether the check constraint is applied after the table creation, use the following SQL query −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name=''CUSTOMERS

Output

It will display all of the table”s information, including the constraint we added to the age column −

Removing a Check Constraint

If there is a way to add a constraint on a column, then you must also be able to remove the constraint from that column. To do that, you can use the ALTER DROP statement.

Syntax

Following is the syntax to remove a check constraint from the table −

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

Following example shows how to drop the check constraint from the CUSTOMERS table created above −

ALTER TABLE CUSTOMERS
DROP CONSTRAINT Constraint_Age;

Verification

Using the following SQL query, we are verifying whether the constraint is removed −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name=''CUSTOMERS

Output

We can see that the check constraint added on the age column is removed −

The SQL DEFAULT Constraint

The SQL DEFAULT Constraint is used to specify the default value for a column of a table. We usually set default value while creating the table.

The default values are treated as the column values if no values are provided while inserting the data, ensuring that the column will always have a value. We can specify default values for multiple columns in an SQL table.

Syntax

Following is the syntax of the SQL DEFAULT Constraint −

CREATE TABLE table_name (
   column1 datatype DEFAULT default_value,
   column2 datatype DEFAULT default_value,
   column3 datatype,
   .....
   columnN datatype
);

Example

In the following query we are creating the CUSTOMERS table using the CREATE TABLE statement. Here, we are adding a default constraint to the columns NAME, AGE, ADDRESS, and SALARY −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL DEFAULT ''Ramesh'',
   AGE INT NOT NULL DEFAULT ''32'',
   ADDRESS CHAR (25) DEFAULT ''Ahmedabad'',
   SALARY DECIMAL (18, 2) DEFAULT ''2000'',
   PRIMARY KEY (ID)
);

Following query inserts values into this table using the INSERT statement −

INSERT INTO CUSTOMERS (ID) VALUES (1);
INSERT INTO CUSTOMERS VALUES (2, ''Khilan'', 25, ''Delhi'', 1500.00 );

The table is created with default values in the NAME, AGE, ADDRESS, and SALARY columns for the first row as shown below −

Passing “DEFAULT” as Value

While inserting data into a table, if the column names are not included in the INSERT query, to insert the default value into the record we need to pass “DEFAULT” as a value, as shown below −

INSERT INTO CUSTOMERS VALUES
(3, ''Kaushik'', DEFAULT, DEFAULT, 2000.00),
(4, ''Chaitali'', DEFAULT, DEFAULT, DEFAULT);

The table obtained is as shown below −

Adding Default Constraint to an Existing Column

We can also add default constraints to an existing column of a table using the ALTER TABLE statement. This allows us to modify the structure of existing table by specifying default values, ensuring data consistency in the database.

Syntax

Following is the syntax for adding a default constraint to a column in an existing table −

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT ''default_value

Example

Assume we have created another table named BUYERS using the CREATE TABLE statement as shown below −

CREATE TABLE BUYERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query, we adds a default constraint to the ADDRESS column of the BUYERS table.

ALTER TABLE BUYERS ALTER ADDRESS SET DEFAULT ''Delhi

Following INSERT statement inserts a record into the BUYERS table by providing values to all the columns except ADDRESS −

INSERT INTO BUYERS (ID, NAME, AGE, SALARY) VALUES
(01, ''Rahul'', 27, 50000);

Verification

After inserting the record if you retrieve it back, you can observe the default value (“Delhi”) in the address column −

SELECT * FROM BUYERS WHERE ID = 01;

Output

The table obtained is as follows −

Dropping Default Constraint

We can delete the default constraint from a table using the ALTER TABLE… DROP statement.

Syntax

Following is the syntax to delete the default constraint from a table −

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

Example

In here, we are removing the default constraint from the ADDRESS column of the CUSTOMERS table −

ALTER TABLE CUSTOMERS ALTER ADDRESS DROP DEFAULT;

Verification

We can verify the table details (structure) and check whether there is a default constraint or not using the following query −

DESC CUSTOMERS;

The table obtained is as shown below −

SQL uses the term NULL to represent a non-existent data value in the database. These values are not the same as an empty string or a zero. They don”t hold any space in the database and are used to signify the absence of a value or the unknown value in a data field.

Some common reasons why a value may be NULL −

• The value may not be provided during the data entry.

• The value is not yet known.

Since the NULL values are basically non-existent, you cannot use comparison operators such as = , <, or > with them. However, you can check if a value is NULL using the IS NULL, “NOT NULL” or IS NOT NULL operators.

Creating a Table without NULL Values

NULL values can be inserted in any column of a table as they are not associated with any specific data type. However, when a column is defined with the “NOT NULL” keyword, an error is raised whenever you try to insert NULL values into that specific column.

Syntax

The basic syntax of NOT NULL while creating a table is as follows −

CREATE TABLE table-name (
   column1 datatype NOT NULL,
   column2 datatype NOT NULL,
   ...
   columnN datatype
);

Here, NOT NULL signifies that column should always accept an explicit value of the given data type. You can insert NULL values into the columns where we did not use NOT NULL.

Example

Let us create a table with the name CUSTOMERS in the SQL database using the CREATE statement as shown in the query below −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Let us insert some values into the above created table using the following query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', NULL),
(7, ''Muffy'', 24, ''Indore'', NULL);

The table is successfully created in the database.

Now, let us retrieve the records present in the table that are not null using the IS NOT NULL operator −

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;

The above query would produce the following result −

You can also retrieve the NULL records present in the table using IS NULL operator in the SELECT query as shown below −

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;

The above query would produce the following result −

Updating NULL Values in a Table

You can update the NULL values present in a table using the UPDATE statement in SQL. To do so, you can use the IS NULL operator in your WHERE clause to filter the rows containing NULL values and then set the new value using the SET keyword.

Example

Consider the previously created table and update the NULL value(s) present in the table using the UPDATE statement as shown below −

UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL;

Output

When you execute the above query, the output is obtained as follows −

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

Let us verify whether the specified record(s) in the table is updated or not using the following query −

SELECT * FROM CUSTOMERS;

On executing the above query, the output is displayed as follows −

Deleting Records with NULL Values

You can delete records containing NULL values from a table using the DELETE FROM statement. You first check whether the table consists of NULL values using the IS NULL operator in WHERE clause and delete the records that are filtered.

Example

Consider the previously created CUSTOMERS table and delete the NULL value(s) present in the table using the DELETE statement as shown below −

DELETE FROM CUSTOMERS WHERE SALARY IS NULL;

Output

When you execute the above query, the output is obtained as follows −

Query OK, 2 rows affected (0.01 sec)

Verification

Let us verify whether the filtered record(s) in the table is deleted or not, by displaying the table using a SELECT statement.

SELECT * FROM CUSTOMERS;

The table will be displayed as −

SQL Stored Procedures

An SQL stored procedure is a group of pre-compiled SQL statements (prepared SQL code) that can be reused by simply calling it whenever needed.

It can be used to perform a wide range of database operations such as inserting, updating, or deleting data, generating reports, and performing complex calculations. Stored procedures are very useful because they allow you to encapsulate (bundle) a set of SQL statements as a single unit and execute them repeatedly with different parameters, making it easy to manage and reuse the code.

Procedures have similar structure as functions: they accept parameters and perform operations when we call them. But, the difference between them is that SQL stored procedures are simpler to write or create, whereas functions have a more rigid structure and support fewer clauses.

Syntax

The basic syntax to create an SQL stored procedure is as follows −

DELIMITER //
CREATE PROCEDURE procedure_name(parameter1 datatype, parameter2 datatype, ...)
BEGIN
   -- SQL statements to be executed
END
DELIMITER ;

• The CREATE PROCEDURE statement is used to create the procedure. We can define any number of input parameters as per the requirement.

• The SQL statements that make up the procedure are placed between the BEGIN and END keywords.

Creating a Procedure

We can create a stored procedure using the CREATE PROCEDURE statement in SQL. Following are the simple steps for creating a stored procedure −

• Choose a name for the procedure.

• Write the SQL code for the procedure.

• We can then test the stored procedure by executing it with different input parameters.

Example

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table will be created as −

Now, let us look at a simple example of creating a stored procedure that takes an input parameter and returns a result set.

In the following query, we are creating the stored procedure with the name GetCustomerInfo. then we provide it with a single input parameter called @CutomerAge. The stored procedure then selects all records from the CUSTOMERS table where the value of the CutomerAge matches the input parameter.

DELIMITER //
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
   BEGIN
      SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
   END //
DELIMITER ;

Output

This would produce the following result −

Query OK, 0 rows affected (0.01 sec)

Verification

We can test the stored procedure by executing it using the CALL statement as shown below −

CALL GetCustomerInfo(25);

This will return all columns from the CUSTOMERS table where the customers age is 25.

Stored Procedure Parameter Types

Stored procedures in a database system can have different types of parameters, which are placeholders for values that will be passed to the stored procedure when it is executed. Following are the different types of stored procedure parameters in SQL −

Procedure with IN parameter

IN is the default parameter of the procedure that will receive input values. We can pass the values as arguments when the stored procedure is being called.

These values are read-only, so they cannot be modified by the stored procedure.

Example

In the following query, we are creating a stored procedure that takes a customer ID as an input parameter and returns the corresponding customer salary.

The procedure body simply performs a SELECT statement to retrieve the “Salary” column from the “CUSTOMERS” table, where the “CustomerID” matches the input parameter.

DELIMITER //
CREATE PROCEDURE GetCustomerSalary(IN CustomerID Int)
   BEGIN
      SELECT SALARY FROM CUSTOMERS WHERE ID = CustomerID;
   END //
DELIMITER ;

Output

This would produce the following result −

Query OK, 0 rows affected (0.01 sec)

Verification

We can test it by executing it with different ID as an input parameter as shown in the query below −

CALL GetCustomerSalary(6);

This will return the salary for the customer with an ID of 6, assuming there is a corresponding row in the CUSTOMERS table −

Procedure with OUT parameter

The OUT parameter is used to return the output value from the procedure.

Note that when using an OUT parameter, we must specify the keyword OUT before the parameter name when passing it to the stored procedure. This tells the SQL database that the parameter is an output parameter and should be assigned with a value in the stored procedure.

Example

In the following query we are creating a stored procedure that used to count the number of records of customer having same age and assign this count to the ”total” variable which holds the number of records.

The procedure body performs a SELECT statement to get the count of records having same age from the “CUSTOMERS” table

DELIMITER //
CREATE PROCEDURE GetDetail(OUT total INT)
   BEGIN
      SELECT COUNT(AGE) INTO total FROM CUSTOMERS
      WHERE AGE = 25;
   END //
DELIMITER ;

Calling the created procedure and passing the ”total” parameter

CALL GetDetail(@total);

Here, we are using the SELECT statement and getting the count −

SELECT @total;

Output

This would produce the following result −

Verification

To verify weather the procedure is created, we can use the following query −

SHOW CREATE PROCEDURE GetDetails;

Procedure with INOUT parameter

The INOUT parameter is a combination of an IN parameter and an OUT parameter. You can pass data into the stored procedure and receive data from the stored procedure using the same parameter.

To declare an INOUT parameter in a stored procedure, we need to specify the INOUT keyword before the parameter name.

Example

In the following query, we provide two INOUT parameters to the stored procedure: cust_id and curr_Salary. These two are used as both an input and output parameters.

The stored procedure first retrieves the current salary of the customer from the database using the cust_id parameter. It then increases the salary by 10% and updates the customers salary in the database using the same parameter.

DELIMITER //
CREATE PROCEDURE increaseSalary(INOUT Cust_Id Int,  INOUT curr_Salary Int)
   BEGIN
      SELECT SALARY INTO curr_Salary From CUSTOMERS Where ID = Cust_Id;
      SET curr_Salary = curr_Salary * 1.1;
      Update CUSTOMERS SET SALARY = curr_Salary Where ID = Cust_Id;
   END //
DELIMITER ;

Output

This would produce the following result −

Query OK, 0 rows affected (0.01 sec)

Verification

We can test it by executing it with different ID or input parameters as shown in the query below −

SET @customerID = 1;
SET @salary = 0.0;
CALL increaseSalary(@customerID, @salary);

Following is Query to select the updated salary from the stored procedure

SELECT @salary AS updated_salary;

The result-set is obtained as −

Advantages of Stored Procedures

Following are the advantages of stored procedures −

• Improved Performance: Stored procedures are pre-compiled and stored on the server, so they can be executed more quickly than SQL statements that are sent from client applications.

• Code Reuse: Stored procedures can be called from different client applications, which means that the same code can be reused across different applications. This reduces development time and maintenance costs.

• Reduced Network Traffic: Because stored procedures are executed on the server, only the results are returned to the client, which reduces network traffic and improves application performance.

• Better Security: Stored procedures can be used to enforce security rules and prevent unauthorized access to sensitive data. They can also limit the actions that can be performed by users, making it easier to maintain data integrity and consistency.

• Simplified Maintenance: By storing SQL code in a single location, it becomes easier to maintain and update the code. This makes it easier to fix bugs, add new functionality, and optimize performance.

Drawbacks of Stored Procedures

Following are the disadvantages of stored procedures −

• Increased Overhead: Stored procedures can consume more server resources than simple SQL statements, particularly when they are used frequently or for complex operations.

• Limited Portability: Stored procedures are often specific to a particular database management system (DBMS), which means they may not be easily portable to other DBMSs.

• Debugging Challenges: Debugging stored procedures can be more challenging than debugging simple SQL statements, particularly when there are multiple layers of code involved.

• Security Risks: If stored procedures are not written correctly, they can pose a security risk, particularly if they are used to access sensitive data or to perform actions that could compromise the integrity of the database.