零、存储过程和存储函数的区别
- 存储过程是一组操作的过程的集合,可以很复杂,可以有参数或者没有参数,可以零个或多个返回值。
- 函数针对某个特定功能进行编程,针对性较强。有且只有一个返回值。参数也是可以有,可以没有。
一、存储过程
存储过程的好处:把一堆常用的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%。