MySQL(2)

1 存储过程

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；
不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，
一般存储过程适用于个别对性能要求较高的业务。其它的必要性不是很大。

优点：
存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等

缺点：
存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。

1.1 创建和调用

存储过程就是具有名字的一段代码，用来完成一个特定的功能。
创建的存储过程保存在数据库的数据字典中。

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

声明语句结束符，可以自定义：
DELIMITER $$ 或 DELIMITER //
声明存储过程：
CREATE PROCEDURE demo_in_parameter(IN p_in int)
存储过程开始和结束符号：
BEGIN .... END
变量赋值：
SET @p_in=1
变量定义：
DECLARE l_int int unsigned default 4000000;
创建mysql存储过程、存储函数：
create procedure 存储过程名(参数)
创建过程体：
create function 存储函数名(参数)
调用存储过程：
call 存储过程名(参数)
把查询的数据赋值给变量：
select into 变量 from 表名;

存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等
过程体格式：以begin开始，以end结束(可嵌套)

为语句贴标签：

label1: BEGIN
　　label2: BEGIN
　　　　label3: BEGIN
　　　　　　statements; 
　　　　END label3 ;
　　END label2;
END label1

好处：
1、增强代码的可读性
2、在某些语句(例如:leave和iterate语句)，需要用到标签

1.2 存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型IN,OUT,INOUT形式如：
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量），尽量少用。

例如：in p_in int （类型在变量后面）

// 创建
create procedure test1()
	begin
		select * from department;
		select * from personnel;
	end;
// 调用
call test1();

存储过程的修改：ALTER PROCEDURE
存储过程的删除： DROP PROCEDURE

1.3 存储过程的控制语句

if 条件 then – else 条件 then
if-then elseif-then (注意elseif的写法)

while 条件 do
    --循环体
endwhile

case

2 触发器

2.1 概念

触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。

触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。

触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。

表 名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

触发器能实现如下功能：
功能：
1、 允许/限制对表的修改
2、 自动生成派生列，比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑
new : 代表当前数据 insert new update new old
old : 之前的数据 delete old

create TRIGGER 触发器名		// 创建触发器
BEFORE 触发事件 on 触发表	// 触发器的触发条件
		[for each row		-- 每一条数据操作]
begin 
		// 操作
end;

//删除  分类  (班级)  但是分类有商品   可以删除  但是把商品的分类改为默认类型  
//该学生放入  0  中
//0  分类是不能被删除的  
CREATE  TRIGGER   deleteDepart105
		BEFORE  DELETE /*[ delete,update,insert ]*/  ON department
		FOR EACH ROW  
	BEGIN
//if deleting  then
//else  if inserting  then
		IF  old.depNo=105 THEN
			signal  SQLSTATE   '45000'  SET     message_text='该数据不能被删除';
		END IF;
END;

//日志表
CREATE  TABLE  log_department (
	id INT PRIMARY  KEY  AUTO_INCREMENT,
	daoName VARCHAR(32) NOT NULL,
	dataDepNo INT NOT NULL,
	daoTime TIMESTAMP
);				

//日志记录  日志表  department  
//做什么操作(三个)    那条数数据       删除时间
CREATE  TRIGGER   deleteDepartLog
		AFTER  DELETE /*[ delete,update,insert ]*/  ON department
		FOR  EACH  ROW  
	BEGIN
		INSERT log_department(daoName,daoTime,dataDepNo) VALUES
		('删除',NOW(),old.depNo);
END;
CREATE  TRIGGER   insertDepartlog
		AFTER  INSERT /*[ delete,update,insert ]*/  ON department
		FOR  EACH  ROW  
	BEGIN
		INSERT log_department(daoName,daoTime,dataDepNo) VALUES
		('新增',NOW(),new.depNo);
END;
CREATE TRIGGER updateDepartlog
		AFTER UPDATE /*[ delete,update,insert ]*/  ON department
		FOR  EACH  ROW  
	BEGIN
		INSERT log_department(daoName,daoTime,dataDepNo) VALUES ('修改',NOW(),old.depNo);
END;

UPDATE  department SET DepDescription='修改了数据'  WHERE  depNO = 105

DELETE  FROM department  WHERE  depNo=101;