mysql 4.26作业（视图、存储过程、函数）

1.在carsales数据库中（下同）创建视图（aboveAvgProducts），该视图包含价格高于所有产品的平均价格的产品，按价格从高到底拍寻，列出字段：产品编号（productCode）、产品名称（productName）和价格（buyPrice）。

CREATE VIEW aboveAvgProducts as 
SELECT productCode,productName,buyPrice
FROM products
WHERE buyPrice>(SELECT avg(buyPrice) FROM products)
ORDER BY buyPrice DESC

2.创建视图（officesOfChina），该视图列出在办公地点在中国的办公室信息，包含字段办公室编号（officeCode）、城市（city）、电话（phone）、地址1（addressLine1）、国际（country）、邮编（postalCode）和管区（territory），视图带有检查选项。

CREATE VIEW officesOfChina as 
SELECT officeCode,city,phone,addressLine1,country,postalCode,territory
FROM offices
WHERE country='China'
WITH CHECK OPTION;

 

3.创建存储过程（getProductsByLen），该存储过程接收一个输入整型参数len，在products表中查找出产品名称（productName）的长度等于传入参数len值的产品信息，返回结果集包含字段产品代码（productCode）和产品名称（productName）

delimiter $
CREATE PROCEDURE getProductsByLen(in len int)
begin
SELECT productCode,productName
FROM products
WHERE LENGTH(productName)=len;
end $

 

4.创建存储过程（getOrderStatusByNo），该存储过程接收客户编号作为输入参数，返回四个值分别为该客户发货（shipped），取消（canceled），解决（resolved）和争议（disputed）的订单数。

delimiter $
CREATE PROCEDURE getOrderStatusByNo(in customer_no int,out shipped int,out canceled int,out resolved int,out disputed int)
begin
#shipped
SELECT COUNT(*)into shipped
FROM orders
WHERE customerNumber=customer_no and `status`='shipped';
#canceled
SELECT COUNT(*)into canceled
FROM orders
WHERE customerNumber=customer_no and `status`='canceled';
#resolved
SELECT COUNT(*)into resolved
FROM orders
WHERE customerNumber=customer_no and `status`='resolved';
#disputed
SELECT COUNT(*)into disputed
FROM orders
WHERE customerNumber=customer_no and `status`='disputed';
end $

 

5.创建存储过程（getShippingDuration），该存储过程接收客户编号作为输入参数，并根据客户所在国家返回这个订单的运送天数：如果客户位于美国（USA），则运送期为2天； 如果客户在加拿大（Canada），运送期为3天；如果客户其他国家的客户则需要5天的运输时间。请使用CASE语句。

delimiter $
CREATE PROCEDURE getShippingDuration(in customer_no int,out days int)
begin

DECLARE cust_country VARCHAR(50);
SELECT country INTO cust_country from customers WHERE customerNumber=customer_no;

case cust_country 
when 'USA' THEN SET days=2;
when 'Canada' THEN SET days=3;
else SET days=5;
END CASE;

end $

 

 

6.创建函数（func_rand_str），该函数接收输入整型参数n，返回随机生成长度为n（n<=255）的字符串，字符串中符号可包括a-z、A-Z、0-9。

CREATE FUNCTION func_rand_str(n int) RETURNS VARCHAR(255)
begin
DECLARE chars VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE chars_return VARCHAR(255) DEFAULT '';
DECLARE i int DEFAULT 0;
WHILE i<n DO
 SET chars_return=CONCAT(chars_return,SUBSTRING(chars,FLOOR(1+RAND()*62),1));
 SET i=i+1;
END WHILE;
RETURN chars_return;
end;

 

 

注：其中，floor是向下取整，rand()取的是0~1之间的小数.

 

 

 

 

7.创建函数（func_rand_telnum），该函数返回随机生成的长度为11位的手机号，开头为130 131 132 133 134 135 136 137 138 139 186 187 189 151 157。

CREATE FUNCTION func_rand_telnum() RETURNS VARCHAR(11)
begin
DECLARE head VARCHAR(100) DEFAULT '000130131132133134135136137138139186187189151157';
DECLARE tail VARCHAR(10) DEFAULT '0123456789';
DECLARE str_return VARCHAR(11) DEFAULT SUBSTRING(head,1+(floor(1+(rand()*15))*3),3);
DECLARE i int DEFAULT 0;
WHILE i<8 DO
	SET str_return=CONCAT(str_return,SUBSTRING(tail,FLOOR(1+RAND()*10),1));
	SET i=i+1;
END WHILE;
RETURN str_return;
end;

运行：SELECT func_rand_telnum();

 

8.创建存储过程（pr_inset_testCases），该存储过程往一张测试数据表test_user（自行建立）中插入100条测试数据，test_user有四个字段uid,username,telephone,birthday，其中uid为自动递增的整型数，username是由函数func_rand_str生成的长度为8的字符串，telephone由函数func_rand_telnum生成，birthday为当前插入日期随机减去20-40年的日期。

drop PROCEDURE if EXISTS pr_inset_testCases;
delimiter $$
CREATE PROCEDURE pr_inset_testCases()
BEGIN 
DECLARE i int DEFAULT 0;
DECLARE year_change int DEFAULT 0;
WHILE i<100 DO
	SET year_change=FLOOR(20+RAND()*21);
	INSERT INTO test_user VALUES(null,SUBSTRING(func_rand_str(8),1,8),func_rand_telnum(),DATE_SUB(CURDATE(),INTERVAL year_change year));
	set i=i+1;
END WHILE;
END;
$$

运行：call pr_inset_testCases();