1.存储过程
-
创建存储过程:
create procedure 名称(参数1,参数2...) begin ......... end
-
删除存储过程:
drop procedure 存储过程;
-
执行存储过程:
cell 存储过程(参数1,参数2...);
-
设置变量:declare username varchar(32) default ‘张三’;
-
给变量赋值(set): set username = ‘李四’;
-
给变量赋值(into):
create procedure sp_username() begin declare user_name varchar(32) default '张三' ; select username into user_name from t_user where id = 1; select user_name; end
-
入参出参:
语法:in | out | inout param_name type- in:
create procedure sp_param01(in age int) begin set @user_age = age; end
- out:
create procedure sp_param02(in name varchar(64),out age int(11)) begin select a.age into age from tuser a where a.name = name; -- 如果出入参数和字段名一样,表必须起别名 end set @age = '1'; call sp_param02('王五',@age); select @age;
- inout:
create procedure sp_test03(inout name varchar(32)) begin select concat(a.name,',hello') into name from tuser a where a.name = name; end set @name = '张三'; call sp_test03(@name); select @name;
- in:
-
判断:
-
if:
语法:IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF
例:
create procedure sp_test04() begin set @num = 3; if @num = 1 then set @num = 'a'; elseif @num = 2 then set @num = 'b'; else set @num = 'c'; end if; select @num; end call sp_test04();
-
case:
语法:-- 语法一(类比java的switch): CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE -- 语法二: CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE
例:
create procedure sp_test06() begin set @num = 2; case when @num = 1 then set @num = 'a'; when @num = 2 then set @num = 'b'; else set @num = 'c'; end case; select @num; end call sp_test06();
-
-
循环:
- loop:
语法:
例:-- 语法 [begin_label:] LOOP statement_list END LOOP [end_label]
CREATE PROCEDURE sp_test07 () BEGIN DECLARE mIndex INT DEFAULT 1; zhangsan:LOOP SELECT mIndex; IF mIndex > 7 THEN LEAVE zhangsan; else set mIndex = mIndex + 1; END IF; END LOOP zhangsan; END
- repeat:
语法:
例:-- 语法 [begin_label:] REPEAT statement_list UNTIL search_condition -- 直到…为止,才退出循环 END REPEAT [end_label]
create procedure sp_test08() BEGIN declare mIndex int default 1; zhangsan:repeat select mIndex; set mIndex = mIndex + 1; until mIndex>5 end repeat zhangsan; end
- while:
语法:
例:-- 语法 [begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
create procedure sp_test09() BEGIN declare mIndex int default 1; while mIndex<5 do select mIndex; set mIndex = mIndex+1; end while; end
- loop:
-
leave:流程退出,类似Java的break;
-
iterate:结束当前循环,执行下次循环,类似Java的continue;
2.游标
用游标得到某一个结果集,逐行处理数据。类似jdbc的ResultSet。
-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name
例:
-- 需求:按照部门名称查询员工,通过select查看员工的编号、姓名、薪资。(注意,此处仅仅演示游标用法)
delimiter $$
create procedure sp_create_table02(in dept_name varchar(32))
begin
declare e_no int;
declare e_name varchar(32);
declare e_sal decimal(7,2);
declare lp_flag boolean default true;
declare emp_cursor cursor for
select e.empno,e.ename,e.sal
from emp e,dept d
where e.deptno = d.deptno and d.dname = dept_name;
-- handler 句柄
declare continue handler for NOT FOUND set lp_flag = false;
open emp_cursor;
emp_loop:loop
fetch emp_cursor into e_no,e_name,e_sal;
if lp_flag then
select e_no,e_name,e_sal;
else
leave emp_loop;
end if;
end loop emp_loop;
set @end_falg = 'exit_flag';
close emp_cursor;
end$$
call sp_create_table02('RESEARCH');
注:在语法中,变量声明、游标声明、handler声明是必须按照先后顺序书写的,否则创建存储过程出错。
3.视图
视图是一个虚拟表,视图的数据变化会影响到基表,基表的数据变化也会影响到视图[insert update delete ]
-
作用:
- 封装复杂sql语句,提高复用性
- 逻辑放在数据库上面,更新不需要发布程序,面对频繁的需求变更更灵活
-
基本语法:
create view 视图名 as select 字段名 from 表名;
注:mysql的视图中不允许有from后面的子查询,oracle可以
4.触发器
触发器是一种特殊类型的存储过程,它不同于存储过程,主要是通过事件触发而被执行的,即不是主动调用而执行的;而存储过程则需要主动调用其名字执行。
触发器发生错误时,前面用户已经执行成功的操作会被撤销,类似事务的回滚
-
基本语法:
create trigger 触发器名字 触发时间 触发事件 on 表 for each row begin -- 触发器内容主体,每行用分号结尾 end
-
触发时间
当 SQL 指令发生时,会令行中数据发生变化,而每张表中对应的行有两种状态: -
before:表中数据发生改变前的状态
-
after:表中数据发生改变后的状态
注:如果 before 触发器失败或者语句本身失败,将不执行 after 触发器(如果有的话)
-
触发事件
触发器是针对数据发送改变才会被触发,对应的操作只有 -
INSERT
-
DELETE
-
UPDATE
注意事项:
- 在 MySQL 5 中,触发器名必须在每个表中唯一,但不是在每个数据库中唯一,即同一数据库中的两个表可能具有相同名字的触发器
- 每个表的每个事件每次只允许一个触发器,因此,每个表最多支持 6 个触发器,before/after insert、before/after delete、before/after update
- MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作,否则会报错
-
查看触发器
show triggers;
-
删除触发器
drop trigger 触发器名字