mysql 5.7创建function_MySQL 5.7 create VIEW or FUNCTION or PROCEDURE

雨点打透心脏的1/2处 2023-01-12 04:22 116阅读 0赞

1.视图

CREATE

ALGORITHM = UNDEFINED

DEFINER = \`root\`@\`localhost\`

SQL SECURITY INVOKER

VIEW \`sakila\`.\`actor\_info\` AS

SELECT

\`a\`.\`actor\_id\` AS \`actor\_id\`,

\`a\`.\`first\_name\` AS \`first\_name\`,

\`a\`.\`last\_name\` AS \`last\_name\`,

GROUP\_CONCAT(DISTINCT CONCAT(\`c\`.\`name\`,

': ',

(SELECT

GROUP\_CONCAT(\`f\`.\`title\`

ORDER BY \`f\`.\`title\` ASC

SEPARATOR ', ')

FROM

((\`sakila\`.\`film\` \`f\`

JOIN \`sakila\`.\`film\_category\` \`fc\` ON ((\`f\`.\`film\_id\` = \`fc\`.\`film\_id\`)))

JOIN \`sakila\`.\`film\_actor\` \`fa\` ON ((\`f\`.\`film\_id\` = \`fa\`.\`film\_id\`)))

WHERE

((\`fc\`.\`category\_id\` = \`c\`.\`category\_id\`)

AND (\`fa\`.\`actor\_id\` = \`a\`.\`actor\_id\`))))

ORDER BY \`c\`.\`name\` ASC

SEPARATOR '; ') AS \`film\_info\`

FROM

(((\`sakila\`.\`actor\` \`a\`

LEFT JOIN \`sakila\`.\`film\_actor\` \`fa\` ON ((\`a\`.\`actor\_id\` = \`fa\`.\`actor\_id\`)))

LEFT JOIN \`sakila\`.\`film\_category\` \`fc\` ON ((\`fa\`.\`film\_id\` = \`fc\`.\`film\_id\`)))

LEFT JOIN \`sakila\`.\`category\` \`c\` ON ((\`fc\`.\`category\_id\` = \`c\`.\`category\_id\`)))

GROUP BY \`a\`.\`actor\_id\` , \`a\`.\`first\_name\` , \`a\`.\`last\_name\`

CREATE

ALGORITHM = UNDEFINED

DEFINER = \`root\`@\`localhost\`

SQL SECURITY DEFINER

VIEW \`sakila\`.\`staff\_list\` AS

SELECT

\`s\`.\`staff\_id\` AS \`ID\`,

CONCAT(\`s\`.\`first\_name\`,

\_UTF8' ',

\`s\`.\`last\_name\`) AS \`name\`,

\`a\`.\`address\` AS \`address\`,

\`a\`.\`postal\_code\` AS \`zip code\`,

\`a\`.\`phone\` AS \`phone\`,

\`sakila\`.\`city\`.\`city\` AS \`city\`,

\`sakila\`.\`country\`.\`country\` AS \`country\`,

\`s\`.\`store\_id\` AS \`SID\`

FROM

(((\`sakila\`.\`staff\` \`s\`

JOIN \`sakila\`.\`address\` \`a\` ON ((\`s\`.\`address\_id\` = \`a\`.\`address\_id\`)))

JOIN \`sakila\`.\`city\` ON ((\`a\`.\`city\_id\` = \`sakila\`.\`city\`.\`city\_id\`)))

JOIN \`sakila\`.\`country\` ON ((\`sakila\`.\`city\`.\`country\_id\` = \`sakila\`.\`country\`.\`country\_id\`)))

2.存储过程

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`film\_in\_stock\`(IN p\_film\_id INT, IN p\_store\_id INT, OUT p\_film\_count INT)

READS SQL DATA

BEGIN

SELECT inventory\_id

FROM inventory

WHERE film\_id = p\_film\_id

AND store\_id = p\_store\_id

AND inventory\_in\_stock(inventory\_id);

SELECT FOUND\_ROWS() INTO p\_film\_count;

END

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`rewards\_report\`(

IN min\_monthly\_purchases TINYINT UNSIGNED

, IN min\_dollar\_amount\_purchased DECIMAL(10,2) UNSIGNED

, OUT count\_rewardees INT

)

READS SQL DATA

COMMENT 'Provides a customizable report on best customers'

proc: BEGIN

DECLARE last\_month\_start DATE;

DECLARE last\_month\_end DATE;

/\* Some sanity checks... \*/

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;

/\* 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.函数

CREATE DEFINER=\`root\`@\`localhost\` FUNCTION \`get\_customer\_balance\`(p\_customer\_id INT, p\_effective\_date DATETIME) RETURNS decimal(5,2)

READS SQL DATA

DETERMINISTIC

BEGIN

\#OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER\_ID AND A DATE

\#THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:

\# 1) RENTAL FEES FOR ALL PREVIOUS RENTALS

\# 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE

\# 3) IF A FILM IS MORE THAN RENTAL\_DURATION \* 2 OVERDUE, CHARGE THE REPLACEMENT\_COST

\# 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED

DECLARE v\_rentfees DECIMAL(5,2); \#FEES PAID TO RENT THE VIDEOS INITIALLY

DECLARE v\_overfees INTEGER; \#LATE FEES FOR PRIOR RENTALS

DECLARE v\_payments DECIMAL(5,2); \#SUM OF PAYMENTS MADE PREVIOUSLY

SELECT IFNULL(SUM(film.rental\_rate),0) INTO v\_rentfees

FROM film, inventory, rental

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(IF((TO\_DAYS(rental.return\_date) - TO\_DAYS(rental.rental\_date)) > film.rental\_duration,

((TO\_DAYS(rental.return\_date) - TO\_DAYS(rental.rental\_date)) - film.rental\_duration),0)),0) INTO v\_overfees

FROM rental, inventory, 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

CREATE DEFINER=\`root\`@\`localhost\` FUNCTION \`inventory\_in\_stock\`(p\_inventory\_id INT) RETURNS tinyint(1)

READS SQL DATA

BEGIN

DECLARE v\_rentals INT;

DECLARE v\_out INT;

\#AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE

\#FOR THE ITEM OR ALL ROWS HAVE return\_date POPULATED

SELECT COUNT(\*) INTO v\_rentals

FROM rental

WHERE inventory\_id = p\_inventory\_id;

IF v\_rentals = 0 THEN

RETURN TRUE;

END IF;

SELECT COUNT(rental\_id) INTO v\_out

FROM inventory LEFT JOIN rental USING(inventory\_id)

WHERE inventory.inventory\_id = p\_inventory\_id

AND rental.return\_date IS NULL;

IF v\_out > 0 THEN

RETURN FALSE;

ELSE

RETURN TRUE;

END IF;

END

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

mysql 5.7创建function_MySQL 5.7 create VIEW or FUNCTION or PROCEDURE

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL创建存储过程（CREATE PROCEDURE）

相关 MySQL创建视图（CREATE VIEW）

相关 MySQL创建视图（CREATE VIEW）

相关 mysql57修改密码

相关 mysql57审计功能开启

相关 mysql57配置教程

相关 mysql57修改用户密码

相关 mysql57中文手册

相关 mysql 5.7创建function_MySQL 5.7 create VIEW or FUNCTION or PROCEDURE

相关 CentOS7 配置MySQL57