CC SQL 23-working with stored procedure

Card Set Information

Author:
dau108
ID:
146420
Filename:
CC SQL 23-working with stored procedure
Updated:
2012-04-08 17:00:10
Tags:
CC SQL 23 working stored procedure
Folders:

Description:
CC SQL 23-working with stored procedure
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user dau108 on FreezingBlue Flashcards. What would you like to do?


  1. using stored procedures
    • Input

    • CALL productpricing(@pricelow,
    • @pricehigh,
    • @priceaverage);

    • Analysis

    Here a stored procedure named productpricing is executed; it calculates and returns the lowest, highest, and average product prices.

    Stored procedures might or might not display results, as you will see shortly.
  2. creating stored procedures
    The default MySQL statement delimiter is ; (as you have seen in all of the MySQL statement used thus far). However, the mysql command-line utility also uses ; as a delimiter. If the command-line utility were to interpret the ; characters inside of the stored procedure itself, those would not end up becoming part of the stored procedure, and that would make the SQL in the stored procedure syntactically invalid.The solution is to temporarily change the command-line utility delimiter, as seen here:

    DELIMITER //

    • CREATE PROCEDURE productpricing()
    • BEGIN
    • SELECT Avg(prod_price) AS priceaverage
    • FROM products;
    • END //

    DELIMITER ;

    CALL productpricing();

    • • Output
    • | priceaverage |
    • +--------------+
    • | 16.133571 |
    • +--------------+
  3. dropping stored procedures
    After they are created, stored procedures remain on the server, ready for use, until dropped. The drop command (similar to the statement seen Chapter 21, "Creating and Manipulating Tables") removes the stored procedure from the server.

    To remove the stored procedure we just created, use the following statement:

    • Input

    DROP PROCEDURE productpricing;

    This removes the just-created stored procedure. Notice that the trailing () is not used; here just the stored procedure name is specified.
  4. working with parameters
    productpricing is a really simple stored procedureit simply displays the results of aSELECT statement. Typically stored procedures do not display results; rather, they return them into variables that you specify.

    • New Term
    • Variable A named location in memory, used for temporary storage of data.Here is an updated version of productpricing
    • • Input

    • CREATE PROCEDURE productpricing(
    • OUT pl DECIMAL(8,2),
    • OUT ph DECIMAL(8,2),
    • OUT pa DECIMAL(8,2)
    • )
    • BEGIN
    • SELECT Min(prod_price)
    • INTO pl
    • FROM products;
    • SELECT Max(prod_price)
    • INTO ph
    • FROM products;
    • SELECT Avg(prod_price)
    • INTO pa
    • FROM products;
    • END;

    • Analysis

    This stored procedure accepts three parameters: pl to store the lowest product price, ph to store the highest product price, and pa to store the average product price (and thus the variable names). Each parameter must have its type specified; here a decimal value is used. The keyword OUT is used to specify that this parameter is used to send a value out of the stored procedure (back to the caller). MySQL supports parameters of types IN (those passed to stored procedures), OUT (those passed from stored procedures, as we've used here), and INOUT (those used to pass parameters to and from stored procedures). The stored procedure code itself is enclosed within BEGIN and END statements as seen before, and a series of SELECTstatements are performed to retrieve the values that are then saved into the appropriate variables (by specifying the INTO keyword).

    • To call this updated stored procedure, three variable names must be specified, as seen here:
    • • Input

    • CALL productpricing(@pricelow,
    • @pricehigh,
    • @priceaverage);

    • Analysis

    As the stored procedure expects three parameters, exactly three parameters must be passed, no more and no less. Therefore, three parameters are passed to this CALL statement. These are the names of the three variables that the stored procedure will store the results in.

    Note: Variable Names

    All MySQL variable names must begin with @.


    • To display the retrieved average product price you could do the following:
    • • Input

    SELECT @priceaverage;

    • Output

    • | @priceaverage |
    • +---------------+
    • | 16.133571428 |
    • +---------------+


    Here is another example, this time using both IN and OUT parameters. ordertotalaccepts an order number and returns the total for that order:

    • Input

    • CREATE PROCEDURE ordertotal(
    • IN onumber INT,
    • OUT ototal DECIMAL(8,2)
    • )
    • BEGIN
    • SELECT Sum(item_price*quantity)
    • FROM orderitems
    • WHERE order_num = onumber
    • INTO ototal;
    • END;

    • Analysis

    onumber is defined as IN because the order number is passed in to the stored procedure. ototal is defined as OUT because the total is to be returned from the stored procedure. The SELECT statement used both of these parameters, the WHEREclause uses onumber to select the right rows, and INTO uses ototal to store the calculated total.

    To invoke this new stored procedure you can use the following:

    • Input

    CALL ordertotal(20005, @total);

    • AnalysisTwo parameters must be passed to ordertotal; the first is the order number and the second is the name of the variable that will contain the calculated total.To display the total you can then do the following:

    • Input

    SELECT @total;

    • • Output+--------+
    • | @total |
    • +--------+
    • | 149.87 |
    • +--------+
  5. building intelligent stored procedures
    All of the stored procedures used thus far have basically encapsulated simple MySQLSELECT statements. And while they are all valid examples of stored procedures, they really don't do anything more than what you could do with those statements directly (if anything, they just make things a little more complex). The real power of stored procedures is realized when business rules and intelligent processing are included within them.

    Consider this scenario. You need to obtain order totals as before, but also need to add sales tax to the total, but only for some customers (perhaps the ones in your own state). Now you need to do several things:

    • 1. Obtain the total (as before).
    • 2. Conditionally add tax to the total.
    • 3. Return the total (with or without tax).That's a perfect job for a stored procedure:

    • Input--

    • Name: ordertotal
    • -- Parameters: onumber = order number
    • -- taxable = 0 if not taxable, 1 if taxable
    • -- ototal = order total variable

    • CREATE PROCEDURE ordertotal(
    • IN onumber INT,
    • IN taxable BOOLEAN,
    • OUT ototal DECIMAL(8,2)
    • ) COMMENT 'Obtain order total, optionally adding tax'
    • BEGIN

    • -- Declare variable for total
    • DECLARE total DECIMAL(8,2);
    • -- Declare tax percentage
    • DECLARE taxrate INT DEFAULT 6;

    • -- Get the order total
    • SELECT Sum(item_price*quantity)
    • FROM orderitems
    • WHERE order_num = onumber
    • INTO total;

    • -- Is this taxable?
    • IF taxable THEN
    • -- Yes, so add taxrate to the total
    • SELECT total+(total/100*taxrate) INTO total;
    • END IF;

    • -- And finally, save to out variable
    • SELECT total INTO ototal;

    END;
  6. 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.

    Note

    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';

What would you like to do?

Home > Flashcards > Print Preview