The MySQL view stored procedure

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The MySQL view stored procedure

Blog directory

  1. Creating and using views
  2. Create and use a stored procedure
  3. Java stored procedure call

The view is a virtual table, and the table that contains the data is not the same, view contains only when using dynamic retrieval of data query, and does not contain any data.


The benefits of using the view


Using the view of rules


Create view

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;



Using the view

select * from v_customers;



Deleting a view

select * from v_customers;

 
The view update

On the view of insert update delete will affect the base table, because the view contains no data
Not all views can be updated:


Not have no alternative against one's will, don't update operation on the view, because the low efficiency. The view is mainly used to query data.

The stored procedure

Stored procedures are a group in order to perform a specific function and write and run in the database SQL assemblies.

The advantages of the stored procedures


The stored procedure defect


Create a stored procedure

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

 

Calling a stored procedure

call productavg();

 

The stored procedure with the return value.

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 ;

 

Returns the value of a call with a stored procedure

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

 

With the stored procedure of the incoming parameters

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 stored procedure with the incoming parameters

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

 

A selection process of the stored procedure

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 ;

 

Use a selection process of the stored procedure

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

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

 

The stored procedure IF statement

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 ;

 

The stored procedure WHILE statement

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 ;

 

Delete a stored procedure

drop procedure productavg;

 

Java stored procedure call

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("Just insert the Id: " + id);
    
cs.close();
conn.close();

 

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Regan at December 17, 2013 - 2:39 AM