mysql5.7 编程_MySQL 5.7 create VIEW or FUNCTION or PROCEDURE

1.视图

a.

b.

2.存储过程

a.

b.

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;

/* Determine start and end time periods */

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 a temporary storage area for

Customer IDs.

*/

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

/*

Find all customers meeting the

monthly purchase requirements

*/

INSERT INTO tmpCustomer (customer_id)

SELECT p.customer_id

FROM payment AS p

WHERE DATE(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;

/* Populate OUT parameter with count of found customers */

SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;

/*

Output ALL customer information of matching rewardees.

Customize output as needed.

*/

SELECT c.*

FROM tmpCustomer AS t

INNER JOIN customer AS c ON t.customer_id = c.customer_id;

/* Clean up */

DROP TABLE tmpCustomer;

END

3.函数

a.

film.rental_duration,((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees

FROM rental,film

WHERE film.film_id = inventory.film_id

AND inventory.inventory_id = rental.inventory_id

AND rental.rental_date <= p_effective_date

AND rental.customer_id = p_customer_id;

SELECT IFNULL(SUM(payment.amount),0) INTO v_payments

FROM payment

WHERE payment.payment_date <= p_effective_date

AND payment.customer_id = p_customer_id;

RETURN v_rentfees + v_overfees - v_payments;

END

b.

0 THEN

RETURN FALSE;

ELSE

RETURN TRUE;

END IF;

END

以上所述是小编给大家介绍的MySQL 5.7 create VIEW or FUNCTION or PROCEDURE，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对编程之家网站的支持！