Set-based programming asks for the rows to evaluate and lets the relational database management system choose how to perform the work. Rather than spelling out an action for one row, then repeating it for the next, a query describes the result needed for a whole set. This is the natural style for SQL queries and is usually shorter than procedural code. section 22.4.1
A cursor takes the opposite approach: it gives the database detailed, row-by-row instructions through operations such as opening a cursor, fetching values into variables, calculating values, inserting a row, and looping again. That control can be necessary when each value must be supplied individually to another stored procedure, but it is rare. Procedural cursor code can be long, consume more server resources, cause blocking, and perform more slowly than a set-based alternative. section 22.4.1 section 22.6
When a cursor appears, first test whether its per-row work can become operations on groups and joins:
- Identify the input rows and the output rows instead of starting from the loop steps. section 23.2.2
- Calculate repeated values for all relevant rows at once, often with aggregates such as COUNT or SUM and GROUP BY. section 23.2.2 section 23.2.3
- Join those calculated result sets by their shared key, such as TitleID, then insert or select the finished rows. section 23.2.3
- Review existing cursors as opportunities to reduce complexity, improve stored-procedure performance, and reduce server resource use. section 22.4.2
Annual royalty payments without a cursor
Suppose each author must receive a share of a title's annual royalty. A row-by-row procedure fetches each TitleID and AuthorID, separately looks up the royalty rate, counts authors, totals sales for the year, calculates a payment, and conditionally inserts it. The set-based version instead derives annual sales per title with SUM, derives each title's royalty and author count with COUNT and GROUP BY, joins both results to titleauthor, and uses one INSERT ... SELECT to populate authorpayment. The resulting procedure needs no cursor or per-row variables, while keeping the same payment calculation. section 23.2.2 section 23.2.3
Set-based does not mean that every request must avoid cursors. If a task can pass only one CustomerID at a time to a stored procedure, the work cannot be completed as a set-based request. In ordinary lookup, reporting, and update-style tasks, however, subqueries, joins, temporary tables, and grouped calculations provide choices beyond a cursor. section 22.6
DELIMITER //
CREATE PROCEDURE InsertAnnualPayment(
IN _PaymentYear int
)
BEGIN
INSERT authorpayment (
AuthorID,
TitleID,
PaymentYear,
PaymentAmount
)
/* Calculate the total royalty per author */
SELECT
ta.AuthorID,
ta.TitleID,
_PaymentYear,
CONVERT((
(sales.TotalSales * (royalty.Royalty/100))/royalty.AuthorCount),
decimal(7,2)) AS RoyaltyPerAuthor
FROM titleauthor ta
INNER JOIN (
/* Determine annual sales by title */
SELECT
oi.TitleID,
SUM(oi.Quantity * oi.ItemPrice) AS TotalSales
FROM orderheader oh
INNER JOIN orderitem oi
ON oh.OrderID = oi.OrderID
WHERE oh.OrderDate >= MAKEDATE(@Year, 1)
AND oh.OrderDate < DATE_ADD(MAKEDATE(@Year, 1), INTERVAL 1 YEAR)
GROUP BY
oi.TitleID
) sales
ON ta.TitleID = sales.TitleID
INNER JOIN (
/* Determine the royalty and count of authors */
SELECT
t.TitleID,
t.Royalty,
COUNT(ta2.AuthorID) AS AuthorCount
FROM title t
INNER JOIN titleauthor ta2
ON t.TitleID = ta2.TitleID
GROUP BY
t.TitleID,
t.Royalty
) royalty
ON ta.TitleID = royalty.TitleID;
END //
DELIMITER ;
Inspect the single INSERT ... SELECT procedure to see grouped sales and royalty data joined to author rows before payments are inserted. section 23.2.3
