Posts
What is Cursor?
A cursor is a temporary work area,it will create in memory when a SQL statement is executed. A cursor holds set of records accessed based on a select statement. This temporary work area is used to store the data retrieved from the database, and manipulate/process this data row by row. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.Why we need to avoid the cursor?
Mostly we will use cursor inside Stored Procedures, we have seen how to optimize the stored procedure,Cursors are one of the most costliest operations in sql,it's better to avoid the Cursors. We can avoid the Cursors in following ways...Select, Loop, Process at out side of Data Base-
Looping and processing are properly handled by programing languages(.Net/Java/PHP..etc) than Data Base. Select the requested data from Data Base and store into collection. Loop thorough the collection and process it.We can use inline functions to avoid the cursor-
We can use functions in select statement, Inside the function we can process the row based on the input and return the result to select statement.To Better understand just look at the following example...
Here we are going to find rank of customer based on the total sales of the customer.the functions as follows...
CREATE DEFINER=`root`@`%` FUNCTION `getCustomerRank`(customerID VARCHAR(32)) RETURNS VARCHAR(10)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE Rank VARCHAR(10);
DECLARE totalSales INT(11);
SELECT SUM(sale) INTO totalSales FROM tbl_saLes WHERE customer_id=customerID;
IF(totalSales < 1000 ) THEN
SELECT 'Sliver' INTO Rank ;
ELSEIF(totalSales < 5000 ) THEN
SELECT 'Gold' INTO Rank ;
ELSEIF(totalSales < 10000 ) THEN
SELECT 'Diamond' INTO Rank ;
END IF;
RETURN Rank;
END$$
DELIMITER ;
Now lets see how to use the function inside the select statement.
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE Rank VARCHAR(10);
DECLARE totalSales INT(11);
SELECT SUM(sale) INTO totalSales FROM tbl_saLes WHERE customer_id=customerID;
IF(totalSales < 1000 ) THEN
SELECT 'Sliver' INTO Rank ;
ELSEIF(totalSales < 5000 ) THEN
SELECT 'Gold' INTO Rank ;
ELSEIF(totalSales < 10000 ) THEN
SELECT 'Diamond' INTO Rank ;
END IF;
RETURN Rank;
END$$
DELIMITER ;
SELECT c.`customer_id`,c.`name`,getCustomerRank(c.customer_id) AS Rank FROM tbl_customer c;
We can use the case statement to avoid the cursor-
Based on the requirement we can avoid the Cursors some times,for example the same above example we can achieve through the case statement.
SELECT s.customer_id,c.name,SUM(s.`sale`) AS 'Total Sales',
CASE WHEN SUM(s.sale) < 1000 THEN 'Sliver'
WHEN SUM(s.sale) < 5000 THEN 'Gold'
WHEN SUM(s.sale) < 10000 THEN 'Diomand'
END AS Rank
FROM tbl_sales s JOIN tbl_customer c ON s.customer_id=c.customer_id
GROUP BY s.customer_id;
Happy Coding!
`CASE WHEN SUM(s.sale) < 1000 THEN 'Sliver'
WHEN SUM(s.sale) < 5000 THEN 'Gold'
WHEN SUM(s.sale) < 10000 THEN 'Diomand'
END AS Rank
FROM tbl_sales s JOIN tbl_customer c ON s.customer_id=c.customer_id
GROUP BY s.customer_id;
2 comments:
These are the tips I needed!
The second example doesn't use a cursor to begin with, so how is this a tip?
Respects for your's Questions & Opinions