【数据库系统 -5】数据库编程

上节快速学习了一下SQL语句，但这些SQL语句大多都是零散的语句。如何将这些SQL语句组织起来，形成一个完整的SQL程序，是本节要解决的问题。存放在MySQL服务器端，供重复使用的对象叫做存储程序，存储程序可分为存储过程，存储函数，触发器和事件。使用存储过程可以带来哪些优点呢？一般说来，存储过程可以重复使用，运行效率高，降低客户机和服务器之间的通信量，可以提高数据库安全性。
upd：2021/1/17更新：翻了一下阿里的Java开发手册，上面写着禁用存储过程，原因各位百度知乎搜狗。

存储过程 | 函数

存储过程：不直接返回一个计算结果，但可以用来完成一般的运算或是生成一个结果集并传递回客户端。
存储函数：返回一个计算结果，该结果可以用在表达式里。

定义一个存储过程，惯用代码示例如下：

delimiter //
create PROCEDURE <存储过程名> (in <传入参数1><传入参数1的类型>, in <传入参数2><传入参数2的类型>, out <返回值1><返回值1类型>)
	BEGIN
		declare <内部变量名><变量类型>
		... (此处执行操作)
	END //
delimiter ;

定义一个存储函数，惯用代码模板为：

delimiter //
create FUNCTION <存储函数名> (in <传入参数1><传入参数1的类型>, in <传入参数2><传入参数2的类型>) returns <返回类型>
	BEGIN
		declare <内部变量名><变量类型>
		... (此处执行操作)
		...(此处为return的值)
	END //
delimiter ;

在外部调用存储过程或函数，只需要使用call <存储函数 | 过程名>即可。

在存储过程或存储函数里面定义的变量，一般生命周期就是本过程或本函数。有一个除外，就是以@开头的变量，我们将其称为会话变量，会话变量在存储函数或存储过程之外还可以被引用到。会话变量的生命周期是整个会话（Session）。

这里还要提一下，对于存储过程而言，传入参数的类型可以指定为IN，OUT，INOUT，具体什么意思相信从字面意义上就可以理解了；对于存储函数而言，传入参数仅限定为IN。对于存储过程而言，可以有返回值，也可以没有返回值；但对于存储函数而言，则必须指明其返回的类型。

所有的存储程序都可以使用流程控制和循环控制，基本格式如下：

IF if_expression THEN commands
	[ELSEIF elseif_expression THEN commands]
	[ELSE commands]
END IF;

CASE  case_expression
 	WHEN when_expression_1 THEN commands
  	WHEN when_expression_2 THEN commands
   	...
	ELSE commands
END CASE;

WHILE expression DO
	Statements
END WHILE;

REPEAT
     Statements;
UNTIL expression
END REPEAT;
注：REPEAT循环类似于do-while循环

当想提前结束循环时，我们可以使用LEAVE或者ITERATE，LEAVE相当于beak，ITERATE相当于continue。

触发器

触发器是用户定义在数据表上的一类由事件驱动的特殊过程，当数据表被INSERT、DELETE或UPDATE语句修改时，触发器将自动执行。

触发器的定义模板为：

create trigger <触发器名>
	 { before | after } 
	 { insert | update | delete } ON <表名>
	 for each row 
	 <执行主体代码>

触发器一个比较由意思的就是after和befor，因为能够引起触发器工作的事件，都是和更改数据表有关的事件，因此，这里面还可以使用NEW和OLD关键字援引更新前后的数据值。

• INSERT事件：NEW可以被使用，指向新插入的row
• DELETE事件：OLD可以被使用，指向刚刚删掉的row
• UPDATE事件：NEW和OLD可以同时被使用

OLD引用的值，是只读的，不能被修改（修改也没有意义）。

使用触发器的一些例子：
【例1】限定新插入数据的percent的范围位[5, 80]，sale_date为当前日期。

mysql> create trigger before_insert_sale
	 		before insert on Sale for each row
    		begin  
	            set new.sale_date = curdate();
		     	if new.percent < 5 then
		         	set new.percent = 5;  
	            elseif new.percent > 80 then  
	                set new.percent = 80;  
	            end if;  
    		end // 

【例2】当更新数据时，如果更新前后的price不一样，则插入新记录。

mysql> create trigger update_product_price
		   after update on products
		   for each row
		   begin
    	       if old.price <> new.price then  
       	       insert into product_log values (old.pid, user(), current_timestamp, old.price, new.price);
		  end //

【例3】当插入新记录时，更新另外一表的记录。

mysql> create trigger prod_qoh_on_order
		after insert on orders for each row
		begin
   			update products set quantity = quantity – new.qty where pid = new.pid;
		 end //

触发器的使用有一定的限制：

• 对数据表中数据的修改，都应该通过NEW，OLD的引用来修改
• 触发器不能有SELECT语句，但可以有SELECT INTO语句
• 触发器不能有return语句

事件

根据时间表在预定时刻自动执行.

事件可以理解为是一个定时器，定时器和触发器还是不同的。事件的定义模板如下：

create event <事件名>
           on schedule <schedule_spec> 
           do <执行主体操作>; 
其中，schedule_specde的一般格式是：
at timestamp [+ interval interval_spec] | every interval [starts timestamp [+ interval interval_spec] ] [ends timestamp [+ interval interval_spec] ]

示例如下：

mysql> create event my_schedule 
      		on schedule at current_timestamp + interval 3 hours 
      		do insert into MySchedule values (‘faculty meeting’, ‘2013-11-14 16:30:00’, ‘G11’); 

mysql> create event my_schedule 
      		on schedule every 1 day do 
      		delete from MySchedule where event_time < current_timestamp;

游标

若SQL查询语句是多条记录，我们要如何存储这些结果呢？我们可以将一条条记录存储起来，用游标（Cursor）一行一行进行遍历，游标是系统为用户开设的一个数据缓冲区。注意MySQL的游标是只读的，这意味着我们没办法通过游标进行数据的增删改；MySQL中的游标也是无法滚动的，这就是说我们只能按找结果集的顺序，一行一行地遍历，不可以往回走，也不可以跳着走。

如何定义一个游标？可以通过语句：declare c cursor for select * from ...，注意游标定义的时候，还没有执行SQL语句，真正执行SQL语句是在游标的OPEN阶段：

在MySQL中使用游标的时候，我们也需要定义一个NOT FOUND Handler来处理游标指到最后的情况，定义格式如下：declare continue handler for not found set <标志位>
使用游标的一个完整示例程序如下：

mysql> delimiter //
mysql> create procedure build_email_list (inout email_list varchar(4000))
	begin
	    declare v_finished int default 0;
	    declare v_email varchar(100) default '';
	    declare email_cursor cursor for select email from employees;  
	    declare continue handler  for not found set v_finished = 1;
	    open email_cursor;
	    get_email: loop
	        fetch email_cursor into v_email;
	        if v_finished = 1 then 
	            leave get_email;  -- exit the loop
	        end if;
	        set email_list = concat(v_email, ';', email_list);
	    end loop get_email;
 	    close email_cursor;
	end //
delimiter ;

写在最后

现在的数据库管理系统一般都提供了UI界面，通过UI界面，我们只需要专注于代码的逻辑，其他杂七杂八的诸如delimiter //，BEGIN等，都无需在我们的考虑范围内，比如phpmyadmin，定义一个事件，我们只需要专注事件的指令逻辑，而时间等的设定都可以通过可视化界面设置。