MariaDB Crash Course - Ben Forta [73]
* * *
Tip: The COMMENT Keyword
The stored procedure for this example included a COMMENT value in the CREATE PROCEDURE statement. This is not required, but if specified, is displayed in SHOW PROCEDURE STATUS results.
* * *
This is obviously a more sophisticated and powerful stored procedure. To try it out, use the following two statements:
Input
CALL ordertotal(20005, 0, @total);
SELECT @total;
Output
+--------+
| @total |
+--------+
| 149.87 |
+--------+
Input
CALL ordertotal(20005, 1, @total);
SELECT @total;
Output
+---------------+
| @total |
+---------------+
| 158.862200000 |
+---------------+
Analysis
BOOLEAN values may be specified as 1 for true and 0 for false (actually, any nonzero value is considered true and only 0 is considered false). By specifying 0 or 1 in the middle parameter you can conditionally add tax to the order total.
* * *
Note: The IF Statement
This example showed the basic use of the MariaDB IF statement. IF also supports ELSEIF and ELSE clauses (the former also uses a THEN clause; the latter does not). We see additional uses of IF (as well as other flow control statements) in future chapters.
* * *
Inspecting Stored Procedures
To display the CREATE statement used to create a stored procedure, use the SHOW CREATE PROCEDURE statement:
Input
SHOW CREATE PROCEDURE ordertotal;
To obtain a list of stored procedures including details on when and who created them, use SHOW PROCEDURE STATUS.
* * *
Tip: Limiting Procedure Status Results
SHOW PROCEDURE STATUS lists all stored procedures. To restrict the output you can use LIKE to specify a filter pattern, for example: SHOW PROCEDURE STATUS LIKE 'ordertotal';
* * *
Summary
In this chapter, you learned what stored procedures are and why they are used. You also learned the basics of stored procedure execution and creation syntax, and you saw some of the ways these can be used. We continue this subject in the next chapter.
24. Using Cursors
In this chapter, you learn what cursors are and how to use them.
Understanding Cursors
As you have seen in previous chapters, MariaDB retrieval operations work with sets of rows known as result sets. The rows returned are all the rows that match a SQL statement—zero or more of them. Using simple SELECT statements, there is no way to get the first row, the next row, or the previous ten rows, for example. Nor is there an easy way to process all rows, one at a time (as opposed to all of them in a batch).
Sometimes there is a need to step through rows forward or backward and one or more at a time. This is what cursors are used for. A cursor is a database query stored on the MariaDB server—not a SELECT statement, but the result set retrieved by that statement. Once the cursor is stored, applications can scroll or browse up and down through the data as needed.
Cursors are used primarily by interactive applications in which users need to scroll up and down through screens of data, browsing or making changes.
* * *
Note: Only in Stored Procedures
Unlike most DBMSs, MariaDB cursors (like those in MySQL) may only be used within stored procedures (and functions).
* * *
Working with Cursors
Using cursors involves several distinct steps:
1. Before a cursor can be used it must be declared (defined). This process does not actually retrieve any data; it merely defines the SELECT statement to be used.
2. After it is declared, the cursor must be opened for use. This process actually retrieves the data using the previously defined SELECT statement.
3. With the cursor populated with data, individual rows can be fetched (retrieved) as needed.
4. When it is done, the cursor must be closed.
After a cursor is declared, it may be opened and closed as often