1. 函数

1. 函数的定义

delimiter 自定义符号 $　-- 如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略
-- 用于命令行定义函数时,不设置会把函数中语句中的 分号识别 为 结束
-- $ 表示把原来的结束符 替换为 $
create function 函数名(形参 参数类型) returns 返回值类型（宽度）
 	begin
 		declare 变量名 类型(宽度);
 		set 变量 = ....;
 		return 变量;
 	end

命令行使用delimiter

2. 函数的创建与使用

CREATE DEFINER=`root`@`localhost` FUNCTION `fm`(`mydate` datetime) RETURNS varchar(255) CHARSET utf8 -- 创建函数,指定函数名，参数列表，返回值类型
BEGIN
	DECLARE x VARCHAR(255);-- 定义变量
	SET x = DATE_FORMAT(`mydate`,"%Y%m%d %h%i%s");-- 函数过程
	RETURN x;-- 返回值
END

调用自定义函数

2. 存储过程

1. 创建存储过程

-- 存储过程的参数
-- IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
-- OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
-- INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

create procedure 过程名([in|out|inout] 参数名 类型,......)
	begin 
		declare .....;
		
		-- 过程...
		
	end;

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_item`(IN stu_id int,IN goodss_id int,IN quantity int,OUT totalPrice float,INOUT remark varchar(255))
BEGIN
	-- Routine body goes here...
	declare goodPrice float; -- 商品单价
	declare goodName varchar(255); -- 商品名
	declare goodQuantity int; -- 商品库存数量
	declare stuName varchar(10); -- 用户名字
	
	select price,`name`,num from goodss where id = goodss_id into goodPrice,goodName,goodQuantity;
	-- 用into 给局部变量赋值
	select `name` from stu where id = stu_id into stuName;
	-- 设置局部变量的值
	set totalPrice = goodPrice * quantity;
	set remark = '买了';
	update goodss set num = num - quantity where id = goodss_id;
	insert into item (stu_id,goodss_id,much) values (stu_id,goodss_id,quantity);
	
END;

2. 调用存储过程

set @stu_id=1; -- 哪个用户购买
set @goodss_id=1; -- 购买的商品id
set @quantity = 2; -- 购买的数量
set @totalPrice = 0; -- 总金额
set @remark = ' '; -- 文档备注
-- out和inout参数传值时，声明的参数都要设置值。
-- 否则要报错，但是执行时out参数并没有接收值，而inout参数接收了值。
-- 如果时使用jdbc调用jdbc调用时out参数不用设置值，
-- 而inout参数可以设置值，也可以不设置值(得到反回值为null)。

call pro_item(@stu_id,@goodss_id,@quantity,@totalPrice,@remark);
-- call pro_item 传递参数
select @stu_id,@goodss_id,@quantity,@totalPrice,@remark;

<调用存储过程

<item表中的记录

3. Java调用存储过程

//存储过程测试类
public class ProcedureTest {
    public static void main(String[] args) {
        try{
            Connection connection = JDBCUtils.getConnection();
            //调用存储过程
            CallableStatement callableStatement = connection.prepareCall("{call pro_item(?,?,?,?,?)}");
            //设置输入参数的值
            //用户ID
            callableStatement.setInt(1,10);
            //商品ID
            callableStatement.setInt(2,1);
            //购买数量
            callableStatement.setInt(3,2);
            //注册输出参数，总金额
            callableStatement.registerOutParameter(4,java.sql.Types.FLOAT);
            //输出参数，商品备注
            callableStatement.registerOutParameter(5, java.sql.Types.VARCHAR);
            //执行一次调用
            callableStatement.execute();

            //获得输出参数的值
            float totalPrice = callableStatement.getFloat(4);
            System.out.println(totalPrice);//12000.0
            String string = callableStatement.getString(5);
            System.out.println(string);//remark:买了
            connection.close();

        } catch (SQLException e){
            e.printStackTrace();
        }
    }
}

4. 存储过程好处与弊端

  1. 用了存储过程 ....

    • 换数据库： 由于语法，每个数据库语法都不同，需要重写编写存储过程

  2. 不用存储过程

    • 一系列sql语句执行都写在java代码中
    • 换语言：......

3. 触发器

1. 创建触发器

-- trigger_time 启动的时刻 after before
-- trigger_event 启动的前提时间  insert,update,delete
-- table_name 表铭
-- trigger_statement 触发器的具体语句
create trigger 触发器名 trigger_time trigger_event ON table_name FOR EACH ROW
trigger_statement


-- 在插入新行时，如果要用触发器来改变它的值再进行插入的话，则需要用 BEFORE 而不是 AFTER ，并使用 关键字 new 因为它才是代表将要插入的这个新行。
-- 输入宽和高自动计算面积,在插入时
set new.area = new.width * new.height;
  • MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：
  1. 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
  2. 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
  3. 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；