mysql 获取 uuid MySQL 获取存储过程ddl

mysql存储过程及函数

• 基本语法
• IF语句
• while循环
• repeat循环
• loop循环
• 游标cursor
• 存储函数
• 触发器

基本语法

-- 创建存储过程
delimiter $
CREATE PROCEDURE pro_test01()
BEGIN
	SELECT 'Hello Mysql';
END $
delimiter ;

-- 调用存储过程
CALL pro_test01();

-- 查看存储过程
-- 查询某数据库中的所有的存储过程
select name from mysql.proc where db='数据库名';
-- 查询存储过程的状态信息
show procedure status;
-- 查询某个存储过程的定义
show create procedure 数据库名.存储过程名 \G;

delimiter
该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了,mysql是否可以执行了。
默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

IF语句

DROP PROCEDURE IF EXISTS pro_test1;
CREATE PROCEDURE pro_test1(IN height INT)
BEGIN
	DECLARE description VARCHAR(50);
	IF height >= 180 THEN
		SET description = '身材高挑';
	ELSEIF height >= 170 THEN
		SET description = '标准身材';
	ELSE
		SET description = '一般身材';
	END IF;

	SELECT description;
END

CALL pro_test1(180);

while循环

WHILE condition DO
	statements
END WHILE;

DROP PROCEDURE pro_calculate_input;
-- 从0加到n的值 -- 累加和
CREATE PROCEDURE pro_calculate_input(IN n INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	DECLARE start_num INT DEFAULT 0;
	
	WHILE start_num <= n DO
		SET total = total + start_num;
		SET start_num = start_num + 1;
	END WHILE;
	
	SELECT total;
END 

CALL pro_calculate_input(2);

repeat循环

REPEAT
	statements
	UNTIL continue_condition
END REPEAT;

DROP PROCEDURE pro_calculate_input;
CREATE PROCEDURE pro_calculate_input(IN n INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	
	REPEAT
		SET total = total + n;
		SET n = n - 1;
		UNTIL n = 0
	END REPEAT;
	
	SELECT total;
END 

CALL pro_calculate_input(2);

loop循环

[custom_label:] LOOP
	statemens
	-- 通常使用 leave 语句退出循环
END LOOP [custom_label]

DROP PROCEDURE pro_calculate_input;
CREATE PROCEDURE pro_calculate_input(IN n INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	
	calc:LOOP
		SET total = total + n;
		SET n = n - 1;
		
		IF n <= 0 THEN
			leave calc;
		END IF;
	END LOOP calc;
	
	SELECT total;
END 

CALL pro_calculate_input(2);

游标cursor

-- 声明游标
DECLARE cursor_name CURSOR FOR select_statement;
-- 打开游标
OPEN cursor_name;
-- 遍历游标
FETCH cursor_name INTO var_name1 [, var_name2];
-- 关闭游标
CLOSE cursor_name;

DROP PROCEDURE IF EXISTS pro_cursor_print;
CREATE PROCEDURE pro_cursor_print()
BEGIN
	DECLARE v_id INT(11);
	DECLARE v_username VARCHAR(10);
	DECLARE v_money DECIMAL(10,2);
	DECLARE has_data_flag INT DEFAULT 1;
	
	-- 声明游标
	DECLARE account_result cursor FOR SELECT id,username,money FROM account;
	DECLARE EXIT handler FOR NOT FOUND SET has_data_flag = 0;
	-- 打开游标
	OPEN account_result;
	-- 遍历游标
	REPEAT
		FETCH account_result INTO v_id,v_username,v_money;
		SELECT CONCAT('v_id=',v_id,',  v_username=',v_username,',  v_money',v_money);
		-- 退出循环
		UNTIL has_data_flag = 0
	END REPEAT;
	-- 关闭游标
	CLOSE account_result;
END

CALL pro_cursor_print();

存储函数

DROP TABLE IF EXISTS `payment`;
CREATE TABLE `payment`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `serial` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- 语法
CREATE FUNCTION function_name([param type ... ]) 
RETURNS type 
BEGIN
	...
END;

定义一个存储函数, 查询表中总记录数 ;

-- 例子
drop function if exists count_record;
create function count_record()
returns int
begin
  declare v_rslt int ;
  select count(*) into v_rslt from payment;
  return v_rslt;
end

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。
行级触发器
语句级触发器

开启创建trigger权限

-- 使用root用户登录执行如下操作
set global log_bin_trust_function_creators=1
show variables like 'log_bin_trust_function_creators'

触发器类型	NEW 和 OLD的使用
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

create trigger trigger_name 
before/after insert/update/delete
on tbl_name 
[ for each row ]  -- 行级触发器
begin
	trigger_stmt ;
end;

创建logs表

create table payment_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作表的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

drop trigger if exists payment_logs_insert_trigger;

create trigger emp_logs_insert_trigger
after insert 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));	
end $

DROP TABLE IF EXISTS `michael_student`;
CREATE TABLE `michael_student`  (
  `id` bigint NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sync_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


DROP TRIGGER IF EXISTS `michael_student_trigger`;
delimiter ;;
CREATE TRIGGER `michael_student_trigger` AFTER DELETE ON `michael_student` FOR EACH ROW begin
insert into nqi_wfw.michael_student(id,name,sync_time)
values(old.id,old.name,old.sync_time);
end
;;
delimiter ;

---

---

系统变量：全局变量、会话变量
状态变量：

show global variables \G;

show global variables like ‘%version%’ \G;

show global variables where variable_name like ‘character%’;

show global variables where variable_name like ‘%log%’ and value=‘off’;

show session variables \G;
show variables;

SELECT @@global.wait_timeout;
SELECT @@session.wait_timeout;
SELECT @@global.pid_file;
SELECT @@session.warning_count;

在写存储过程时，如果需要调用系统变量的值，可以通过如下方法调用。
@@GLOBAL.var_name
@@SESSION.var_name
@@var_name 表示优先从会话级别获取变量值。

查看状态变量。
show status;

设置系统变量的语法：
SET GLOBAL var_name = value;
SET @@GLOBAL.var_name = value;

SET SESSION var_name = value;
SET @@SESSION.var_name = value;

不指定级别默认设置会话级别的变量。
SET var_name = value;

状态变量对于用户来说是只读的。

---