MySQL 视图 存储过程

博文目录

1. 视图的创建及使用
2. 存储过程的创建及使用
3. Java调用存储过程

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询，而自身不包含任何数据。

使用视图的好处

• 重用SQL语句
• 简化复杂的SQL操作
• 使用表的组成部分而不是整个表
• 保护数据。可以给用户赋予表的某个部分的访问权限，而不是整个表的访问权限

使用视图的规则

• 与表一样，视图必须唯一命名
• 在一个数据库中，可以创建的视图数目没有限制
• 视图可以嵌套，即利用从其他视图中查询出来的数据构建新的视图
• Order by可以用在视图中，但是如果从该视图检索数据的select中也包含order by，那么该视图中的order by将会被覆盖
• 视图不能索引，也不能有关联的触发器或默认值
• 视图和表可以一起使用，例如编写一条连接表和视图的查询

创建视图

create view v_customers 
as
select customers.cust_name,customers.cust_id,count(orders.order_num) from customers 
left outer join orders 
on
customers.cust_id=orders.cust_id 
group by customers.cust_id;

使用视图

select * from v_customers;

删除视图

select * from v_customers;

 
视图的更新

对视图进行insert update delete将影响其基表，因为视图不包含任何数据
不是所有视图都可以更新：

• 含有分组（group by和having）
• 联接查询
• 子查询
• 聚合函数
• DISTINCT

不是迫不得已，不要对视图进行更新操作，因为效率低。视图主要用于查询数据。

存储过程

存储过程是一组为了完成某个特定功能而编写的并运行在数据库端的SQL程序集。

存储过程的优点

• 通过把处理封装在简单易用的单元中，简化复杂的操作
• 提高性能。使用存储过程比使用单独的SQL语句要快
• 安全，调用者只需要调用指定的存储过程即可，而不用关心存储过程的内容

存储过程的缺点

• 编写复杂
• 如果没有响应的权限，你将无法编写存储过程

创建存储过程

delimiter //
create procedure productavg() 
begin 
select avg(prod_price) as avgprice from products;
end // 
delimiter ;

 

调用存储过程

call productavg();

 

带返回值的存储过程

delimiter //
create procedure productinfo(
out pl decimal(8,2),
out ph decimal(8,2),
out pa decimal(8,2)
)
begin
select min(prod_price) into pl from products;
select max(prod_price) into ph from products;
select avg(prod_price) into pa from products;
end //
delimiter ;

 

调用带返回值的存储过程

call productinfo(@pricelow,@pricehigh,@priceavg);
select @pricelow,@pricehigh,@priceavg;

 

带传入参数的存储过程

delimiter //
create procedure ordertotal(
in ordernum int,
out total decimal(8,2)
) 
begin 
select sum(item_price*quantity) info total from orderitems where order_num=ordernum;
end //
delimiter ;

 

调用带传入参数的存储过程

call ordertotal(20005,@total);
select @total;

 

有选择流程的存储过程

delimiter //
create procedure ordertotaltax(
in ordernum int,
in tax boolean,
out total decimal(8,2)
)
begin 
declare ordertotal decimal(8,2);
declare taxvalue float default 0.6;
select sum(item_price*quantity) into ordertotal from orderitems where order_num=ordernum;
if tax then 
select ordertotal+(ordertotal*taxvalue) into ordertotal;
end if;
select ordertotal into total;
end //
delimiter ;

 

使用有选择流程的存储过程

call ordertotaltax(20005,1,@total);
select @total;

call ordertotaltax(20005,0,@total);
select @total;

 

IF语句的存储过程

delimiter //
create procedure iftest(out var int) 
begin 
declare id int default 14;
if id='12' then
  set var=0;
elseif id<12 then 
  set var=1;
else 
  set var=2;
end if;
end //
delimiter ;

 

WHILE语句的存储过程

delimiter //
create procedure whiletest() 
begin 
declare num int;
declare total int default 0;
set num=0;

while num<10 do 
  set total=total+num;
  set num=num+1;
end while;

select total;
end //
delimiter ;

 

删除存储过程

drop procedure productavg;

 

Java调用存储过程

DELIMITER //
CREATE PROCEDURE insert_test(IN uname VARCHAR(50),IN uaddress VARCHAR(50))
BEGIN
  INSERT INTO t_test(username,address) VALUES(uname,uaddress);
END//
DELIMITER ;

 

Class.forName("com.mysql.jdbc.Driver");
   
Connection conn = DriverManager.getConnection("jdbc:mysql:///proc_db","root","root");
   
CallableStatement cs = conn.prepareCall("{CALL insert_test(?,?)}");
   
cs.setString(1, "hunthon");
cs.setString(2, "hennan");
   
int rows = cs.executeUpdate();
   
System.out.println(rows);
   
cs.close();
conn.close();

 

DELIMITER //
CREATE PROCEDURE find_test() 
BEGIN
  SELECT id,username,address FROM t_test;
END//
DELIMITER ;

 

Class.forName("com.mysql.jdbc.Driver");
   
Connection conn = DriverManager.getConnection("jdbc:mysql:///proc_db","root","root");
   
CallableStatement cs = conn.prepareCall("{CALL find_test()}");
   
ResultSet rs = cs.executeQuery();
   
while(rs.next()) {
  String name = rs.getString("username");
  String address = rs.getString("address");
  System.out.println("name:" + name + "\taddress:" + address);
}
   
rs.close();
cs.close();
conn.close();

 

DELIMITER //
CREATE PROCEDURE insert_getId(OUT id INT,IN uname VARCHAR(50),IN uaddress VARCHAR(50))
BEGIN
  INSERT INTO t_test(username,address) VALUES(uname,uaddress);
  SELECT LAST_INSERT_ID() INTO id;
  SELECT id;
END//
DELIMITER ; 

 

Class.forName("com.mysql.jdbc.Driver");
    
Connection conn = DriverManager.getConnection("jdbc:mysql:///proc_db","root","root");
    
CallableStatement cs = conn.prepareCall("{CALL insert_getId(?,?,?)}");
    
cs.registerOutParameter(1, Types.INTEGER);
cs.setString(2, "fankai");
cs.setString(3, "hennan");
    
cs.executeUpdate();
    
int id = cs.getInt(1);
    
System.out.println("刚刚插入的Id：" + id);
    
cs.close();
conn.close();