mysql存储过程和存储函数练习
mysql数据库练习4-存储过程和存储函数
存储过程和存储函数语法
创建存储过程
create procedure p1(存储名)（in suppiler_num varchar(10)）
begin
declare results int default0; #声明变量并初始化
c语言定义二维数组省略下标select count(*) into results #赋值
from factory.suppiler
where factory.suppiler.suppiler_num=suppiler_num;
select if(results>0,'成功','失败'); #使⽤
end
（参数列表：模式参数名参数类型）
模式in(默认)、out、 inout：in模式；out 模式；inout 模式
删除存储过程
drop procedure p1 （并且只能⼀次删除⼀个存储过程）
更改存储过程
不能直接修改存储过程，只能先删除该过程，在重新创建该过程
调⽤存储过程
call p1('s1')
注意：因为是in模式所有可以传⼊常量，如果是inout模式
不可以传⼊常量，⽽必须是变量。
查看存储过程
show create procedure p1
存储函数
如果是第⼀次在mysql上创建存储函数，可能会发⽣SQL错误[1418][HY000]:
This function has none of DETERMINISTIC ,NO SQL,or READS SQL DATA in its declaration and binary logging is
enabled(you “might” want to use the less safe_log_bin_trust_function_creators variable)。此时的mysql 服务器开启了⼆进制⽇志选项，这种模式是默认禁⽌创建存储函数的。
使⽤下列命令选择开启就可以了。
创建存储函数
create function函数名(参数列表) returns 返回类型
begin
函数体
end
1、参数列表（参数名参数类型）
2、函数体肯定有return语句
调⽤存储函数
select 函数名（参数列表）
案例演⽰
有⼀简易电⼦商务⽹站，其平台数据库中部分表及其结构如下：
（1）⽤户表：tUser（⽤户账号varchar(10)，⽤户名称varchar(50)，登陆密码varchar(20)，varchar(20)，邮件地址varchar(100)，已购商品总额numeric(10,2)，送货地址varchar(100)）
mysql查看所有存储过程（2）⽤户登录历史记录表：tUserHisRec（登录序号int identity(1,1)，⽤户账号varchar(10)，登录时间datetime）
（3）商品列表：productsList（商品编号varchar(20)，商品名称varchar(100)，商品类别varchar(100)，商品价格numeric(10,2)，出⼚⽇期datetiem，⽣产商varchar(200)）
（4）⽤户购买商品记录：tUserOrder（登陆序号int identity(1,1)，⽤户账号varchar(10)，商品编号varchar(20)，购买时间datetime，送货地址varchar(100)）
--⽤户表：
Create table tUser
(/*⽤户账号*/ tuser_num varchar(10),
/* ⽤户名称 */tuser_name varchar(50),
/*登陆密码 */ tuser_password varchar(20),
/* */ tuser_tel varchar(20),
/
* 邮箱地址 */tuser_maill varchar(100),
/*已购商品总额 */ tuser_sum_expence numeric(10,2),
/*送货地址 */ tuser_addr varchar(100)
)
--⽤户登录历史记录表：
Create table tUserHisRec(
/*登录序号*/ tuserhisrec_row int auto_increment,
/*⽤户账号*/tuserhisrec_num varchar(10),
/*登录时间*/tuserhisrec_time datetime,
primary key(tuserhisrec_row) #⾃增必须要设键
)
-
-商品列表：
sql图书馆管理系统>html编程器>c++二进制转换十进制Create table productsList(
/*商品编号*/ productsList_num varchar(20),
/*商品名称*/ productsList_name varchar(100),
/*商品类别 */productsList_kind varchar(100),
/*商品价格 */productsList_price numeric(10,2),
/*出⼚⽇期 */productsList_date datetime,
/*⽣产商 */productsList_suppiler varchar(200)
)
--⽤户购买商品记录：
Create table tUserOrder(
/
*登陆序号*/ tuserorder_row int auto_increment,
/*⽤户账号 */tuser_num varchar(10),
/*商品编号 */productsList_num varchar(20),
/*购买时间 */tuserorder_time datetime,
/*送货地址 */tuser_addr varchar(100),
primary key(tuserorder_row)
)
1. 创建商品检索存储过程procBrowProduct (关联⼦查询)
要求：输⼊商品名称或商品类别（要求模糊查询），输出商品编号，商品名称，商品价格，出⼚⽇期，⽣产商，已购⽤户数量，最近⼀次购买⽤户姓名，最近⼀次购买时间
create procedure t2(in products_name varchar(100),in products_kind varchar(100))
begin
select *,(select count(distinct tuser_num)from tUserOrder
where `productsList_num`=a.productsList_num)as'已购⽤户数量',
(select tuser_num from factory.tuserorder
where tuserorder_time=(select max(tuserorder_time)
from factory.tuserorder
where `productsList_num`=a.productsList_num
group by a.productsList_num))as'最近⼀次购买的⽤户',
(select max(tuserorder_time)from factory.tuserorder
where `productsList_num`=a.productsList_num
group by a.productsList_num)as'最近⼀次购买的时间'
from factory.productslist a where `productsList_num`in
(select productsList_num from factory.productslist
where `productsList_name` like concat('%',products_name,'%')
and `productsList_kind` like concat('%',products_kind,'%'));
end
分析：
输⼊的商品名称和商品类别是为了到对应商品编号，有了商品编号就可以输出⼀系列信息，对应已购⽤户数量和最近⼀次购买⽤户姓名，和最近⼀次购买时间，分别使⽤三个⼦查询，⽽⼦查询与外层查询联系就是a.productsListnum作为限制条件，及关联⼦查询
关联⼦查询
给出⼀个简单例⼦引出关联⼦查询：查询低于相同职位平均⼯资的员⼯信息
table staff(staff_num,staff_depart,staff_salary)
select staff_num from staff a where staff_salary<(
select avg(staff_salary from staff
where staff_depart=a.staff_deapart)
查询⾸先会从最外层select * from staff
在将每⼀⾏结果传递给⼦查询，传⼊第⼀⾏结果就是select staff_num from staff where staff_salary<(select avg(staff_salary from staff where staff_depart=‘经理’))，然后⼦查询的结果⼜返回给外层查询
select staff_num from staff where staff_salary<9666。逐⾏逐⾏，直到结束。
关联⼦查询，外层与内层查询是信息是双向传递的。
2. 创建商品检索存储过程t1
要求：输⼊商品名称或商品类别（要求模糊查询），输出商品编号，商品名称，商品价格，出⼚⽇期，⽣产商，已购⽤户数量，最近⼀次购买⽤户姓名，最近⼀次购买时间
create procedure t1(in use_num varcharacter(10),in product_num varcharacter(20))
begin
declare tuser_addr1 varchar(100);
declare productsList_price1 numeric(10,2);
select tuser_addr into tuser_addr1
from tUser
where tUser.tuser_num=use_num;
select productsList_price into productsList_price1
from factory.productsList
where productsList_num=product_num;
update tUser
set tuser_sum_expence=tuser_sum_expence+productsList_price1
where tuser_num=use_num;
insert into factory.tuserOrde(tuser_num,productsList_num,tuserorder_time,tuser_addr)
values(use_num,product_num,now(),tuser_addr1);
end
这个⽐较简单。
3 创建⽤户⾃定义函数：varchar fGetUserMaxProduct(⽤户账号)（在查询结果⼦表中在进⾏查询）要求：输⼊⽤户账号，返回该⽤户购买最多的商品编号。
create function t3(user_num varchar(10)) returns varchar(20)
begin
declare products_num varchar(20);
select `productsList_num` into products_num from
(select count(`productsList_num`)as c1,`productsList_num`
from factory.tuserorder where tuser_num=user_num
group by `productsList_num`)as t1 having max(c1);
return products_num;
end
分析：
将查询结果设成别名为t1 的表，再重t1的表中，在继续查询。
查询结果⼦表中，再查询
例如：
数据分析师考什么证select a from (select a,b,c from table1 where…)as t1
从t1结果表中查询a
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论