第四章 存储过程和存储函数，触发器和视图

零、存储过程和存储函数的区别

• 存储过程是一组操作的过程的集合，可以很复杂，可以有参数或者没有参数，可以零个或多个返回值。
• 函数针对某个特定功能进行编程，针对性较强。有且只有一个返回值。参数也是可以有，可以没有。

一、存储过程

存储过程的好处：把一堆常用的Sql语句或者业务逻辑封装起来，预编译保存在数据库中，当需要的时候直接调用，省去了编译的过程。

存储过程的基本语法

0.变量定义

(A)参数有三种模式:

• IN：默认。 由外部将参数传入过程。
• OUT: 可以由存储过程将值传出。
• INOUT: 可以传入也可以传出。

举例：
#分支:
delimiter //
		create procedure score_level(score int)
		begin
		   -- 声明变量
		  declare v_level varchar(20);
		  if score >= 90 then
			  set v_level = 'A';
			elseif score >=80 then
	      set v_level = 'B';
		  elseif score >= 70 then
	      set v_level = 'C';
		  else
	      set v_level = 'D';
		  end if;	
	    select v_level; 	
		end;
	  //

#循环:
     While 条件 do … end while;
     Loop…….end loop;   (leave)
     Repeat … end repeat;  (until)
   #while
		delimiter //
		create procedure calc1()
	  begin
		  declare i int;
			declare sum int;
			set i = 1;
			set sum = 0;
			while i<=100 do
			  set sum = sum + i;
			  set i = i+1;
			end while;
			select sum;
		end;
		//
		call calc1();
		
		
		#loop
		delimiter //
		create procedure calc2()
	  begin
		  declare i int;
			declare sum int;
			set i = 1;
			set sum = 0;
			lip:loop
			  set sum = sum + i;
			  set i = i+1;
				if i>100 then
				  leave lip;
				end if;
			end loop;
			select sum;
		end;
		//
		call calc2();
		
		#repeat
		delimiter //
		create procedure calc3()
	  begin
		  declare i int;
			declare sum int;
			set i = 1;
			set sum = 0;
			repeat
			  set sum = sum + i;
			  set i = i+1;
				until i>100
			end repeat;
			select sum;
		end;
		//
		call calc3();

1.定义

一、无参过程定义
delimiter //
CREATE PROCEDURE showTime()
BEGIN   
select now ();
END //
delimiter ; 

二、有参过程定义
delimiter //
CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED,OUT num SMALLINT UNSIGHED )
BEGIN   
delete from son where id = uid;
select row_count () int num;
END //
delimiter ; 

(注意这里delimiter //是改变结束符号为//的意思。)

2.调用存储过程

CALL sp_name ([ proc_parameter [,proc_parameter ...]])
CALL sp_name
无参时可以省略括号

3.删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

二、存储函数（自定义函数udf）

• 自定义函数是对Mysql功能的一个扩展。
• 注意下面存储函数的过程控制语法存储过程中也可以用

（一）存储函数的基本语法

0.基本语法

DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED) 
RETURNS SMALLINT
BEGIN
DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;
SET  a = x, b = y;
RETURN a+b;
END//

1.声明变量用Declare关键字；
2.变量赋值用Set 变量 = 值的形式。也可以用Sql语句赋值
如：
...在某个UDF中...
DECLARE x int;
SELECT COUNT(id) FROM tdb_name INTO x;
RETURN x;
END//
3.用户自定义语法（可以理解为全局变量）
SET @param_name = value
如：
SET @allParam = 100;
SELECT @allParam;

1.定义

创建UDF:

　　CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])
　　RETURNS {STRING|INTEGER|REAL}
　　runtime_body

简单来说就是:

　　CREATE FUNCTION 函数名称(参数列表)
　　RETURNS 返回值类型
　　函数体
　　函数体都以begin开头，end结尾，全部函数包括返回值也写在begin和end之间。
　　

2.删除

删除UDF:
　　DROP FUNCTION function_name

3.调用

SELECT function_name(parameter_value,...)
注意他是select开头的，区别于存储过程，存储过程是call开头的。

（二）过程控制

• if语句，case语句，loop语句，leave语句，iterate语句，repeat语句，while语句。
• 每个流程可能包含一个单独语句，或者是使用begin…end构造符合语句，构造可以嵌套

1.if…then…end if语句,需要以endif结束。

IF age>20 THEN SET @count1=@count1+1;  
ELSEIF age=20 THEN SET @count2=@count2+1;  
ELSE SET @count3=@count3+1;  
END IF; 

另外，mysql自带有一个if()函数，区别于此。

2.case…when…then…, end case语句

CASE age 
WHEN 20 THEN SET @count1=@count1+1; 
ELSE SET @count2=@count2+1; 
END CASE ; 

3.loop…leave loop语句

add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
END LOOP add_num ; 

4.iterate语句：跳出本次循环，直接进入下次循环，只能用在loop,repeat,while语句中。

add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
ELSE IF MOD(@count,3)=0 THEN 
ITERATE add_num; 
SELECT * FROM employee ; 
END LOOP add_num ; 

5.repeat…until语句

REPEAT 
SET @count=@count+1; 
UNTIL @count=100 
END REPEAT ; 

6.while…do语句

WHILE @count<100 DO 
SET @count=@count+1; 
END WHILE ; 

三、触发器 create trigger trig_name

• 触发器不能手动调用也不能传递参数。由事件触发。Mysql里增删改会触发。
• Mysql只支持行级触发器，Oracle支持表级触发器。

#语法格式：
CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

例子

delimiter //
		create trigger tri_stu
		after delete
		on stu for each row
		begin
		  insert into student(sname,cid) values(old.sname,old.cid);
		end;
		//
触发器影响性能。

四、视图和索引

（一）视图 create view view_name

1.视图定义：是实体表的一个虚拟映射，在数据库中没有物理结构存在。

2.视图

• 1.视图数据来自于基表，
• 2.视图可以使用增删改查的功能，视图增删改功能会修改基表数据。
• 所以视图创建是为了查找，不是为了增删改。

3.基本语法

create view view_emp
		as
		select * from emp where deptno = 10 with check option;
		
select * from view_emp; 
update view_emp set deptno = 20;

drop view view_emp;

4.视图特点

• 1.简化开发。存储之前查找出来的集合。
• 2.安全性。只映射出一部分内容公开给别人看。
• 3.定制化数据。拆分或组合字段信息。

（二）索引 create index index_name on table(id)

索引是为提高查询效率设计的数据结构。本质需要文件存储，需要维护。

#创建索引
create index index_sid on stu(sid);
#销毁索引
drop index index_sid on stu;

1.索引的添加规则

• 1.数据量较大
• 2.添加索引在高奇数时
• 3.不能添加过多索引（需要维护）
• 4.默认是B树索引

2.不适合添加

• 增删改过多的操作。增删改操作不能超过5%。