sakila.mwb

这是MySQL官方测试用的数据库

    
    IF min_monthly_purchases = 0 THEN
        SELECT 'Minimum monthly purchases parameter must be > 0';
        LEAVE proc;
    END IF;
    IF min_dollar_amount_purchased = 0.00 THEN
        SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
        LEAVE proc;
    END IF;

    
    SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
    SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
    SET last_month_end = LAST_DAY(last_month_start);

    
    CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);

    
    INSERT INTO tmpCustomer (customer_id)
    SELECT p.customer_id 
    FROM payment AS p
    WHERE p.payment_date BETWEEN last_month_start AND last_month_end
    GROUP BY customer_id
    HAVING SUM(p.amount) > min_dollar_amount_purchased
    AND COUNT(customer_id) > min_monthly_purchases;

    
    SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;

    
    SELECT c.* 
    FROM tmpCustomer AS t   
    INNER JOIN customer AS c ON t.customer_id = c.customer_id;

    
    DROP TABLE tmpCustomer;
END</value>
              <value type="string" key="routineType">PROCEDURE</value>
              <value type="string" key="security">DEFINER</value>
            </value>
            <value type="dict" struct-name="db.mysql.Routine">
              <value type="string" key="_id">{AE483912-B4FC-42CF-A4C3-45F5503A3998}</value>
              <value type="string" key="comment"></value>
              <value type="string" key="name">get_customer_balance</value>
              <value type="string" key="oldName">get_customer_balance</value>
              <value type="string" key="owner" option="ref">{0D24FE2A-C389-4E95-BB0F-257921515C3F}</value>
              <value type="list" content-type="dict" content-struct-name="db.mysql.RoutineParam" key="params">
                <value type="dict" struct-name="db.mysql.RoutineParam">
                  <value type="string" key="_id">{10B62F59-DF84-41F5-B101-361994C498AF}</value>
                  <value type="string" key="datatype">INT</value>
                  <value type="string" key="name">p_customer_id</value>
                  <value type="string" key="owner" option="ref">{AE483912-B4FC-42CF-A4C3-45F5503A3998}</value>
                  <value type="string" key="paramType">IN</value>
                </value>
                <value type="dict" struct-name="db.mysql.RoutineParam">
                  <value type="string" key="_id">{57007B2C-CBD4-4486-938D-EC83933C2FF9}</value>
                  <value type="string" key="datatype">DATETIME</value>
                  <value type="string" key="name">p_effective_date</value>
                  <value type="string" key="owner" option="ref">{AE483912-B4FC-42CF-A4C3-45F5503A3998}</value>
                  <value type="string" key="paramType">IN</value>
                </value>
              </value>
              <value type="string" key="returnDatatype">decimal(5,2)</value>
              <value type="string" key="routineCode">CREATE FUNCTION `get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS decimal(5,2)
    DETERMINISTIC
BEGIN&#13;
&#13;
       
       
       
       
       
       
&#13;
  DECLARE v_rentfees DECIMAL(5,2); 
  DECLARE v_overfees INTEGER;      
  DECLARE v_payments DECIMAL(5,2); 
&#13;
  SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees&#13;
    FROM film, inventory, rental&#13;
    WHERE film.film_id = inventory.film_id&#13;
      AND inventory.inventory_id = rental.inventory_id&#13;
      AND rental.rental_date &lt;= p_effective_date&#13;
      AND rental.customer_id = p_customer_id;&#13;
&#13;
  SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) &gt; film.rental_duration,&#13;
        ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees&#13;
    FROM rental, inventory, film&#13;
    WHERE film.film_id = inventory.film_id&#13;
      AND inventory.inventory_id = rental.inventory_id&#13;
      AND rental.rental_date &lt;= p_effective_date&#13;
      AND rental.customer_id = p_customer_id;&#13;
&#13;
&#13;
  SELECT IFNULL(SUM(payment.amount),0) INTO v_payments&#13;
    FROM payment&#13;
&#13;
    WHERE payment.payment_date &lt;= p_effective_date&#13;
    AND payment.customer_id = p_customer_id;&#13;
&#13;
  RETURN v_rentfees + v_overfees - v_payments;&#13;
END</value>
              <value type="string" key="routineType">FUNCTION</value>
              <value type="string" key="security">DEFINER</value>
            </value>
            <value type="dict" struct-name="db.mysql.Routine">
              <value type="string" key="_id">{3C905D33-0C1A-40B9-8BEC-EA4105405D2A}</value>
              <value type="string" key="comment"></value>
              <value type="string" key="name">inventory_held_by_customer</value>
              <value type="string" key="oldName">inventory_held_by_customer</value>
              <value type="string" key="owner" option="ref">{0D24FE2A-C389-4E95-BB0F-257921515C3F}</value>
              <value type="list" content-type="dict" content-struct-name="db.mysql.RoutineParam" key="params">
                <value type="dict" struct-name="db.mysql.RoutineParam">
                  <value type="string" key="_id">{5714E7D3-E93D-4482-83B1-4AC00F9AD927}</value>
                  <value type="string" key="datatype">INT</value>
                  <value type="string" key="name">p_inventory_id</value>
                  <value type="string" key="owner" option="ref">{3C905D33-0C1A-40B9-8BEC-EA4105405D2A}</value>
                  <value type="string" key="paramType">IN</value>
                </value>
              </value>
              <value type="string" key="returnDatatype">int(11)</value>
              <value type="string" key="routineCode">CREATE FUNCTION `inventory_held_by_customer`(p_inventory_id INT) RETURNS int(11)
BEGIN&#13;
  DECLARE v_customer_id INT;&#13;
  DECLARE CONTINUE HANDLER FOR 1329 RETURN NULL;&#13;
&#13;
  SELECT customer_id INTO v_customer_id&#13;
  FROM rental&#13;
  WHERE return_date IS NULL;&#13;
&#13;
  RETURN v_customer_id;&#13;
END</value>
              <value type="string" key="routineType">FUNCTION</value>
              <value type="string" key="security">DEFINER</value>
            </value>
            <value type="dict" struct-name="db.mysql.Routine">
              <value type="string" key="_id">{EBD980E7-D97C-43C4-9EB4-41C8CAF95316}</value>
              <value type="string" key="comment"></value>
              <value type="string" key="name">inventory_in_stock</value>
              <value type="string" key="oldName">inventory_in_stock</value>
              <value type="string" key="owner" option="ref">{0D24FE2A-C389-4E95-BB0F-257921515C3F}</value>
              <value type="list" content-type="dict" content-struct-name="db.mysql.RoutineParam" key="params">
                <value type="dict" struct-name="db.mysql.RoutineParam">
                  <value type="string" key="_id">{A38D0C4C-C7AF-4A32-B42F-3163A95E69EF}</value>
                  <value type="string" key="datatype">INT</value>
                  <value type="string" key="name">p_inventory_id</value>
                  <value type="string" key="owner" option="ref">{EBD980E7-D97C-43C4-9EB4-41C8CAF95316}</value>
                  <value type="string" key="paramType">IN</value>
                </value>
              </value>
              <value type="string" key="returnDatatype">tinyint(1)</value>
              <value type="string" key="routineCode">CREATE FUNCTION `inventory_in_stock`(p_inventory_id INT) RETURNS tinyint(1)
BEGIN&#13;
    DECLARE v_rentals INT;&#13;
    DECLARE v_out     INT;&#13;
&#13;
    
    
&#13;
    SELECT COUNT(*) INTO v_rentals&#13;
    FROM rental&#13;
    WHERE inventory_id = p_inventory_id;&#13;
&#13;
    IF v_rentals = 0 THEN&#13;
      RETURN TRUE;&#13;
    END IF;&#13;
&#13;
    SELECT COUNT(rental_id) INTO v_out&#13;
    FROM inventory LEFT JOIN rental USING(inventory_id)&#13;
    WHERE inventory.inventory_id = p_inventory_id&#13;
    AND rental.return_date IS NULL;&#13;
&#13;
    IF v_out &gt; 0 THEN&#13;
      RETURN FALSE;&#13;
    ELSE&#13;
      RETURN TRUE;&#13;
    END IF;&#13;
END</value>
              <value type="string" key="routineType">FUNCTION</value>
              <value type="string" key="security">DEFINER</value>
            </value>
          </value>
          <value type="list" content-type="dict" content-struct-name="db.mysql.Sequence" key="sequences"/>
          <value type="string" key="sql"></value>
          <value type="list" content-type="dict" content-struct-name="db.mysql.StructuredDatatype" key="structuredTypes"/>
          <value type="list" content-type="dict" content-struct-name="db.mysql.Synonym" key="synonyms"/>
          <value type="list" content-type="dict" content-struct-name="db.mysql.Table" key="tables">
            <value type="dict" struct-name="db.mysql.Table">
              <value type="string" key="_id">{D618AACE-8414-4FDB-92A6-EB49D25267F4}</value>
              <value type="list" content-type="dict" content-struct-name="db.mysql.Column" key="columns">
                <value type="dict" struct-name="db.mysql.Column">
                  <value type="string" key="_id">{545E727B-A55B-4E9B-B6F4-FAFFC8C0E4A7}</value>
                  <value type="int" key="autoIncrement">1</value>
                  <value type="string" key="comment"></value>
                  <value type="string" key="datatypeName">SMALLINT</value>
                  <value type="int" key="defaultValueIsNull">1</value>
                  <value type="int" key="isNullable">0</value>
                  <value type="string" key="name">actor_id</value>
                  <value type="string" key="oldName">actor_id</value>