MySQL 5.7 create VIEW or FUNCTION or PROCEDURE

发布时间：2020-12-12 09:14:41 所属栏目：MsSql教程来源：网络整理

导读：1.视图 a. b. 2.存储过程 a. b. 0';LEAVE proc;END IF;IF min_dollar_amount_purchased = 0.00 THENSELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';LEAVE proc;END IF;/* Determine start and end time periods */SET last_mo

1.视图

a.

2.存储过程

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.函数

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

0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!