mysql数据库note6:PL/SQL（存储函数，触发器，物化视图，数据库优化）-CSDN博客

本文链接：https://blog.csdn.net/qq_38971534/article/details/107539347
SQL/PLSQL
/* 
 * PL/SQL - 过程化SQL语言（Procedural Language/SQL）
		对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，
			所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，
			通过逻辑判断、循环等操作实现复杂的功能或者计算
	
		1: 程序端(实现相关逻辑处理) ->  多次数据库ARUD操作
		2: PL/SQL -> 数据库端(一个结构中 - 自身逻辑处理) 
	
  	函数	function	用于完成一个特定的计算，具有返回值和参数
		存储过程	procedure	完成某项完整的业务处理，没有返回值，但可通过传出参数将多个值传个调用环境
		触发器	trigger	相当于一个事件的监听器，当数据库发生特定的事件后，触发器被触发，完成响应处理
			编译 - 保存在服务器端 -> 需要时调用
 */

/*
 * 函数
		 1: 预定义函数
		 2: 自定义函数	
				create function 函数名([参数, ...]) returns type 
				begin 
					...
					return value;
				end;	
 */
select upper('John');
select md5(MD5('123456'));

-- 查看所有人工资 
	select id, name, salary + ifnull(bonus, 0) +  ifnull(mobileBonus, 0) +  ifnull(hotbonus, 0) +  ifnull(allbonus, 0) +  ifnull(oilbonus, 0) as salary
		from employee;

	-- 函数创建报错 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法
	set global log_bin_trust_function_creators=TRUE;

	-- 定义工资求和的函数  - 依据员工编号查询员工所有工资和
		drop function allSalary;
		create function allSalary(v_id int) returns int 
			begin 
				declare v_salary int; 			-- default 0;				-- 定义变量  default 0;  - 缺省值
				-- 变量赋值 set allSum = 10000;
				-- select .. into 变量   ->  seleclt查询的结果必须是一条
				select salary + ifnull(bonus, 0) +  ifnull(mobileBonus, 0) +  
							 ifnull(hotbonus, 0) +  ifnull(allbonus, 0) +  ifnull(oilbonus, 0) into v_salary 
							from employee where id = v_id;
				return v_salary;
			end;

	select allSalary(2) ;

	-- 函数 - 计算员工工资列
		create function salary(salary int, bonus int, mobileBonus int, 
												   hotbonus int, allbonus int, oilbonus int) returns int 
			begin
				return salary + ifnull(bonus, 0) +  ifnull(mobileBonus, 0) +  
							 ifnull(hotbonus, 0) +  ifnull(allbonus, 0) +  ifnull(oilbonus, 0);
			end;

		select id, name, salary(salary, bonus, mobileBonus, hotbonus, allbonus, oilbonus) as salary 
			from employee;
		
-- 银行转账(扣、加) 
	drop function transferFun;
	
	-- 定义变量 - 不要与列名一致
	create function transferFun(myCode varchar(20), a_money double, tagetCode varchar(20)) returns int 
		begin 
			declare v_flag int default 0;    					 -- 转账成功
			declare v_money int default 0;						 -- 余额
			declare v_targetId int default 0;
			-- 判断余额是否足够转账
			select money into v_money from account where id = myCode;
			if v_money > a_money then 								 -- 可以转账
					update account set money = money - a_money where id = mycode;			-- 付款账号扣款
					select id into v_targetId from account where id = tagetCode;
					if v_targetId = 0 then 				-- 转账账号不存在
						 set v_flag = 2;
					else 
						 update account set money = money + a_money where id = tagetCode;
					end if;	
			else
				set v_flag = 1;														-- 余额不足
			end if;
			-- 判断对方账号是否存在
			return v_flag;
		end;

		start transaction;
			select transfer(2, 20000, 3);			-- 0-转账成功  1-余额不足  2-转账账号错误
		commit;
		rollback;

-- --------------------------------------------------------------------------------------------------
	/* 
   * 存储过程    in-传入数据(默认类型), out-传出数据(数据处理 -> 返回给调用者)， inout
	 *    create procedure pname([参数,..]) 
	 *      begin 
	 *         ...
	 *			end;
   */
	-- 银行转账(扣、加) 
  drop PROCEDURE transferPro;
	create procedure transferPro(myCode int, tagetCode int,
															 a_money double, out v_flag int)
		begin 
			declare v_money int default 0;						 -- 余额变量
			declare v_targetId int default 0;					 -- 缺省的不存在的账号
			select money into v_money from account where id = myCode;		-- 判断余额是否足够转账
			if v_money > a_money then 								 -- 可以转账
					select id into v_targetId from account where id = tagetCode;		-- 判断转账账号不存在
					if v_targetId = 0 then 									
						 set v_flag = 2;											-- 转账账号不存在
					else 
						 -- start transaction;
							 update account set money = money - a_money where id = mycode;			-- 付款账号扣款
							 update account set money = money + a_money where id = tagetCode;	  -- 加款
						 -- commit;
						 set v_flag = 3;											-- 转账成功
					end if;	
			else
				set v_flag = 1;														-- 余额不足
			end if;
		end;

	-- 调用存储过程		call proName(参数, ..., @v_flag)   -> 定义变量接受out的数据
	start transaction;
		call transferPro(3, 20000, 2, @flag);	
		select @flag;
	commit;
	rollback;

-- 商品分页查询(count、pages, 数据)  -> 某种类中的商品分页数据
	create procedure itemPages(v_sid int, v_pageIndex int, v_pageNum int, out v_count long, out v_pages int) 				-- mysql数据 - 游标 - 不需要out
		begin 
			select count(*) into v_count from item where sid = v_sid;
			select ceil(v_count / v_pageNum) into v_pages;
			set v_pageIndex = (v_pageIndex - 1) * v_pageNum;
			select * from item where sid = v_sid order by id desc limit v_pageIndex, v_pageNum;
		end;
	
	call itemPages(4, 1, 20, @count, @pages);
	select @count;
	select @pages;

-- 所有员工薪资调整(不同结构调整)  其他+500  3000以上 + 800  	5000以上 + 1000 	8000以下 + 1200  10000以上 + 1500
	-- 查询的数据有多条 -> 依据每条数据 -> 不同处理 -> 游标(光标)cursor
		-- open游标
		-- loop fetch获取一行数据
		-- close游标
	
	  drop procedure addAll;
		create procedure addAll() 
			begin
				-- 定义变量 接收fetch每行数据(变量数量与查询数量一致)
				declare v_id int;
				declare v_name varchar(255);
				declare v_salary int;
				declare v_addMoney int;

				declare done int default false;		-- 游标之前定义boolean变量游标处理 - 循环 
				declare allEmp cursor for select id, name, salary from employee;		-- 声明游标
				declare continue handler for not found set done = true;		-- 游标执行时 -> 游标无下一行

				open allEmp;				-- 打开游标
					 fetch allEmp into v_id, v_name, v_salary;				-- 获取下一行
					 while not done do 					-- 循环处理游标
							-- select v_id, v_name, v_salary;
							if v_salary > 10000 then 
								set v_addMoney = 1500;
							elseif v_salary > 8000 then 
								set v_addMoney = 1200;
							elseif v_salary > 5000 then 
								set v_addMoney = 1000;
							elseif v_salary > 3000 then 
								set v_addMoney = 800;
							else 
								set v_addMoney = 500;
							end if;
							update employee set salary = salary + v_addMoney where id = v_id;

							fetch allEmp into v_id, v_name, v_salary;			-- 继续获取下一行
					 end while;
				close allEmp;				-- 关闭游标
			end;

		call addAll();

-- --------------------------------------------------------------------------------------------------
	-- 触发器	trigger	相当于一个事件的监听器，当数据库发生特定的事件后，触发器被触发，完成响应处理
		/*  
			CREATE TRIGGER trigger_name 
				trigger_time 				-  BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发
				trigger_event				-  INSERT、UPDATE、DELETE
				ON tbl_name 
			  FOR EACH ROW 
				trigger_stmt				-  new.列名   old.列名
     */
	create trigger upadteAccount 
		before update on account 
		for each row 
		begin
			if old.id <= 5 then
				insert into accountBak(id, money, adate, aid, updateDate) 
					values(old.id, old.money, old.adate, old.aid, now());
			end if;
		end;

	create trigger deleteAccount 
		before delete on account 
		for each row 
		begin
			insert into accountBak(id, money, adate, aid, updateDate) 
				values(old.id, old.money, old.adate, old.aid, now());
		end;

	start transaction;
		update account set money = money + 800000000 where id = 3;

		delete from account where id = 3;
	commit;
	rollback;


-- mySQL -> 物化视图( - 自定义 表 - 自己维护数据)
	delete from item where id > 20;

	select i.id, i.name, i.salePrice, s.`name` as smallKindName
		from item i, smallKind s 
		where i.sid = s.sid;

	insert into itemView (select i.id, i.name, i.salePrice, s.`name` as smallKindName
													from item i, smallKind s 
													where i.sid = s.sid);
	
	select * from itemView;

	create trigger itemTrigger 
		after insert on item 
		for each row 
		begin 
			-- 先删除原有所有数据
			delete from itemView;
			-- 再维护数据
			insert into itemView (select i.id, i.name, i.salePrice, s.`name` as smallKindName
													from item i, smallKind s 
													where i.sid = s.sid);
		end;

  insert -- commit;

/* 
 * 数据库优化
		 1: 子查询 
		 2: index索引
		 3: 视图view
		 4: 物化视图
		 5: 存储过程、函数
		 6: 数据库连接池
 */