MySQL【MySQL】【demo】procedure
【demo1】判断表索引是否存在
--建表
create table t_student(
id int auto_increment primary key,
serialno varchar(20) not null,
name varchar(20) not null,
age int not null,
);
--查看表状态
describe t_student;
--添加表索引
alter table t_student
add index idx_t_student_serialno(serialno);
describe t_student; - -MUL表示索引
--判断表索引是否存在
select * from information_schema.statistics
where table_schema='testdb'
and table_name=upper('T_STUDENT')
and index_name=upper('IDX_T_STUDENT_SERIALNO');
--删除表索引
alter table t_student
drop index idx_t_student_serialno;
--创建 存储过程
drop produce if exists prc_add_tab_index;
delimiter // #修改MySQL语句结束标志为// (默认为 ;)
create produce prc_add_tab_index(in i_tablename varchar(64),in i_index_name varchar(64),in i_index_col varchar(64))
begin
declare currentdatabase varchar(100); #每个变量的定义都需要 使用 declare
declare log_msg varchar(100) default 'Start to execute produce PRC_ADD_TAB_INDEX';
select concat('[INFO]:',log_msg); #输出日志
select database() into currentdatabase;
if not exists (select * from information_schema.statistics where table_schema=currentdatabase and table_name=upper(i_tablename) and index_name=upper(i_index_name) ) then
set @sqlstr=concat('alter table ',i_tablename,' add index ',i_index_name,'(',i_index_col,')');
prepare stmt from @sqlstr;
execute stmt;
end if;
end//
delimiter ;
call prc_add_tab_index('T_STUDENT','IDX_T_STUDENT_SERIALNO','SERIALNO');
【demo2】判断表字段是否存在
--建表
create table t_student(
id int auto_increment primary key,
serialno varchar(20) not null,
name varchar(20) not null,
age int not null,
);
--查看表状态
describe t_student;
--添加表字段
alter table t_student
add (class_no varchar(1) binary);
alter table t_student
add column class_no varchar(1) binary;
alter table t_student
add (class_no1 varchar(1) binary,class_no2 varchar(1) binary);
--删除表字段
alter table t_student
drop column class_no;
--判断表字段是否存在
select * from information_schema.columns
where table_schema='testdb'
and table_name=upper('t_student')
and column_name=upper('class_no');
--创建 存储过程
drop produce if exists prc_add_tab_col;
delimiter //
create produce prc_add_tab_col(in i_tablename varchar(64),in i_col_name varchar(64),in i_col_type varchar(64))
begin
declare currentdatabase varchar(100); #每个变量的定义都需要 使用 declare
declare log_msg varchar(100) default 'Start to execute produce PRC_ADD_TAB_COL';
select concat('[INFO]:',log_msg); #输出日志
select database() into currentdatabase;
if not exists (select * from information_schema.columns where table_schema=currentdatabase and table_name=upper(i_tablename) and column_name=upper(i_col_name) ) then
set @sqlstr=concat('alter table ',i_tablename,' add column ',i_col_name,' 'i_col_type);
prepare stmt from @sqlstr;
execute stmt;
end if;
end//
delimiter ;
call prc_add_tab_col('T_STUDENT','CLASS_NO','varchar(1) binary');
【demo3】MySQL produce的in参数 与 out参数
#连接MySQL
mysql -help
方式1.
mysql -u root -p
输入密码
show databases;
use <database_name>
方式2.
mysql -uroot -p<password>
show databases;
use <database_name>
方式3.
mysql -uroot -p<password> -d<database_name>
#创建database
show databases;
create database <database_name>;
use <database_name>;
show tables;
select upper('Abc');
select lower('Abc');
--建表
create table t_student(
id int auto_increment primary key,
name varchar(20) not null,
age int not null,
)ENGIN=InnoDB DEFAULT CHARSET=utf8;
insert into t_student values('1001','Lucy',22);
insert into t_student values('1002','Scott',25);
insert into t_student values('1003','Shirly',20);
commit;
--查看表状态
describe t_student;
--创建 存储过程
drop produce if exists prc_get_cnt;
delimiter //
create produce prc_get_cnt(in ii_age int,out oi_cnt int)
begin
select count(*) into oi_cnt from t_student where age >= ii_age;
end//
#调用 存储过程 写法1
delimiter //
set @vv_age = 25//
set @vv_cnt = 0 //
call prc_get_cnt(@vv_age,@vv_cnt)//
select @vv_cnt//
#调用 存储过程 写法2
delimiter ;
set @vv_age = 25;
set @vv_cnt = 0 ;
call prc_get_cnt(@vv_age,@vv_cnt);
select @vv_cnt;
delimiter ;
drop produce prc_get_cnt;
【demo4】MySQL的声明语句&赋值语句
写法1.以 @ 开头的变量,可以直接用于sql语句(用户变量),也可以用于子程序begin...end块中(局部变量)
delimiter ;
set @vv_cnt = 0 ;
select @vv_cnt;
写法2.以 declare定义的变量,只能用于存储过程中,也成为 存储过程变量(局部变量)
drop produce if exists prc_get_cnt;
delimiter //
create produce prc_test()
begin
declare vv_name,vv_address varchar(64);
declare vv_age int default 20; #声明时指定默认值
set vv_name = 'Hellen'; #使用 set 赋值
select 'Nanjing' into vv_address; #使用 select into 赋值
-- select vv_name,vv_age,vv_address; #注释的写法1:--空格xxx
#select vv_name,vv_age,vv_address; #注释的写法2:#xxx
/*select vv_name,vv_age,vv_address;*/ #注释的写法1:/*xxx*/
end//
参考:
https://www.cnblogs.com/EasonJim/p/7966918.html
【demo4】MySQL 打印日志
方式1:直接用select语句打印
select 'this is a comment';
方式2:使用用户变量和concat拼接字符串
set @err_msg = 'this is an error comment';
select concat('[EORROR]',@err_msg);
方式3:日志插入到表中
create table t_err_log(err_info varchar(100)); #创建日志表
drop procedure if exsits prc_log;
delimiter //
create produce prc_log(in err_msg varchar(100))
begin
declare err_level varchar(10) default '[ERROR]';
set err_level= '[Fatal ERROR]';
insert into t_err_loger(err_info) values(concat(err_level,err_msg));
set @sqlstr='commit';
prepare stmt from @sqlstr;
execute stmt;
end//
delimiter ;
call prc_log('this is an error comment');
select err_info from t_err_log;