The MYSQL stored procedure

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

  1. Use stored procedures:

  2. The stored procedure:
  3.     Set for the future is to use and preservation of one or more of the MYSQL statement.

  4.     3 advantages: simple stored procedure, safety, high performance.

  5.     The stored procedure defect:
  6.     @Writing stored procedures than basic complex SQL statements, writing stored procedures require higher skills, higher experience.
  7.     @Security access to create a stored procedure. Create access administrator has restricted the stored procedure allows the user to use the stored procedure, the single did not allow them to create a stored procedure.


  8. Execute a stored procedure:
  9.     MYSQL said the execution of the stored procedure call. CALL
  10.     
  11.     >CALL productpricing(@pricelow, @pricehigh,@priceaverage);
  12.     Analysis: the implementation of a productpricing stored procedure, calculation of minimum, maximum, average value.



  13. Create a stored procedure:
  14.     A simple example:
  15.     CREATE PROCEDURE productpricing()
  16.     BEGIN
  17.         SELECT Avg(prod_price) AS priceaverage
  18.         FROM products;
  19.     END;

  20.     Analysis: this stored procedure called productpricing, defined by CREATE PROCEDURE productpricing. Storage Chen accept parameters, he will be in () are listed in the. The BEGIN and END statements used to define a procedure body.

  21.     Note: the MYSQL command line client segmentation.:
  22.     If the command line using the application program to explain the stored procedure itself; character, they eventually will not be stored procedure component, this will make the stored procedure SQL syntax error.
  23.     The solution is to temporarily change the command line utility statement separator, as follows:
  24.     
  25.     DELIMITER //
  26.     
  27.     CREATE PROCEDURE productpricing()
  28.     BEGIN
  29.         SELECT Avg(prod_price) AS priceaverage
  30.         FROM products;
  31.     END //
  32.     
  33.     DELIMITER ;

  34.     Among them, DELIMITER // tell command utility use // as the new sentence ending delimiter, can see the sign END defined stored procedure ends instead of END for END//;. In this way, the stored procedure in vivo; remained motionless, and the right to transfer to the database engine. Finally, to restore the original statement separator.

  35.     You can use the DELIMITER , .
  36.     In addition, symbols, any character can be used as the statement separator.


  37. Delete a stored procedure:

  38.     >DROP PROCEDURE productpricing;
  39.     Note: do not use parenthesis();

  40. Use the parameters:
  41.     Look at an example:
  42.     CREATE PROCEDURE productpricing(
  43.      OUT pl DECIMAL(8,2),
  44.      OUT ph DECIMAL(8,2),
  45.      OUT pa DECIMAL(8,2)
  46.      )
  47.     BEGIN
  48.         SELECT Min(prod_price) INTO pl FROM products;
  49.         SELECT Max(prod_price) INTO ph FROM products;
  50.         SELECT Avg(prod_price) INTO pa FROM products;
  51.     END;
  52.     
  53.     Analysis: this stored procedure accepts 3 parameters: pl, ph, pa.
  54.     MYSQL supports IN (passed to the stored procedure), OUT (outgoing from a stored procedure), INOUT (storage process of incoming and outgoing) type parameters. INTO (preserved in the corresponding variable).


  55.     The calling:
  56.     CALL productpricing(@pricelow,
  57.                 @pricehigh,
  58.                 @priceaverage);

  59.     Note: all MYSQL variables must take the @ start.

  60.     If you want to retrieve the average:
  61.     SELECT @priceaverage;

  62.     
  63.     Using the IN and OUT parameters of the example:
  64.     CREATE PROCEDURE ordertotal(
  65.      IN onumber INT,
  66.      OUT ototal DECIMAL(8,2)
  67.      )
  68.     BEGIN
  69.         SELECT Sum(item_price*quantity) FROM orderitems
  70.         WHERE order_num = onumber
  71.         INTO ototal;
  72.     END;
  73.     Analysis: the definition of onumber IN, because the order was introduced to the stored procedure. Ototal is defined as OUT, because the stored procedure to return together. The WHERE clause using the onumber suspended the correct line, together with the INTO using the ototal storage calculated.
  74.     This stored procedure call:
  75.     CALL ordertotal(20005,@total);
  76.     SELECT @Total - shows total.


  77. The establishment of intelligent storage process:

  78.     Contains the business rules and intelligent processing in the stored procedure, in order to play its power.

  79.     Look at an example:
  80.     --Name: ordertotal
  81.     --Parameters:onumber = order number
  82.     --        taxable = 0 if not taxable, 1 if taxable
  83.     --         ototal = order total variable

  84.     CREATE PROCEDURE ordertotal(
  85.      IN onumber INT,
  86.      IN taxable BOOLEAN,
  87.      OUT ototal DECIMAL(8,2)
  88.      )COMMENT'obtain order total,optionally adding tax'
  89.      BEGIN

  90.      -- Declare variable for total
  91.      DECLARE total DECIMAL(8,2);
  92.      --Declare tax percentage
  93.      DECLARE taxrate INT DEFAULT 6;

  94.      --Get the order total
  95.      SELECT Sum(item_price*quantity) FROM orderitems
  96.      WHERE order_num = onumber
  97.      INTO total;

  98.      -- Is this taxable?

  99.      IF taxable THEN
  100.      --Yes,so add taxable to the to total
  101.      SELECT total+(total/100*taxble) INTO total;
  102.      END IF;
  103.     
  104.      -- And finally,save to out variable
  105.      SELECT total INTO ototal;

  106.      END;

  107.      Analysis: @-- notes,
  108.     @DECLARE requirements defined variable names and data types, this example defines two local variables.
  109.     @DEFAULT sets the default value of 6%.
  110.     @The COMMENT keyword, it is generally not required, but if given, will be displayed in the SHOW PROCEDURE STATUS results.
  111.     Calling a stored procedure:
  112.     >CALL ordertotal(20005, 0, @total),
  113.     >SELECT @total;


  114. Check the stored procedure:
  115.     
  116.     In order to display the CREATE statement is used to create a stored procedure, using the SHOW CREATE PROCEDURE statement.
  117.     
  118.     >SHOW CREATE PROCEDURE ordertotal;
  119.     
  120.     In order to obtain list stored procedure contains when, who create and other detailed information, use SHOW PROCEDURE STATUS.
  121.     The limit state:
  122.     You can use LIKE to specify a filter mode, for example:
  123.     >SHOW PROCEDURE STATUS LIKE 'ordertotal'.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Natalie at December 09, 2013 - 7:33 AM