领导让我整理一份数据库表的结构与相关数据,顺便将所有的数据表加上索引。
一开始我用很笨的方法,一个一个表加,例:
ALTER TABLE asset_assetmanagecontract ADD INDEX idx_orgcode_assetmanagecontract(orgcode);
ALTER TABLE asset_assetmanagecontract ADD INDEX idx_baseid_assetmanagecontract(baseid);
ALTER TABLE asset_assetsummary ADD INDEX idx_orgcode_assetsummary(orgcode);
ALTER TABLE asset_assetsummary ADD INDEX idx_baseid_assetsummary(baseid);
在使用过程中发现,若原表已存在索引,再次添加时会报错,并且不会继续向下执行。解决这个bug我想通过判断索引是否存在,若已存在,则不添加,反之添加。于是,我找到了之前没用过的存储过程
DROP PROCEDURE IF EXISTS add_index; --删除原来的索引
DELIMITER $ -- 分隔符号 默认为“;”,常用$$
-- 存储过程方法中都是以";"结尾,若不重新设置分隔符,则只会执行最后一个";"
CREATE PROCEDURE add_index(in tab_name VARCHAR(100)) -- tab_name 为传入的表名
BEGIN
DECLARE target_database VARCHAR(100); -- 声明变量
DECLARE target_table_name VARCHAR(100);
DECLARE target_index_name VARCHAR(100);
DECLARE target_index_name2 VARCHAR(100);
set target_table_name = tab_name; -- 给变量赋值
set target_index_name = 'idx_orgcode';
set target_index_name2 = 'idx_baseid';
SELECT DATABASE() INTO target_database; -- 查询当前数据库并放入target_database中
IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema = target_database AND table_name = target_table_name AND index_name = target_index_name) THEN
-- set @statement = "alter table asset_debt add index idx_orgcode (orgcode)";
-- 一开始写死的表格,后来通过拼接传参将其写活
set @statement = concat("alter table ",tab_name," add index idx_orgcode (orgcode)");
-- 为orgcode字段添加名为idx_orgcode的索引
PREPARE STMT FROM @statement;
EXECUTE STMT;
END IF;
IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema = target_database AND table_name = target_table_name AND index_name = target_index_name2) THEN
-- set @statement = "alter table asset_debt add index idx_baseid (baseid)";
set @statement = concat("alter table ",tab_name," add index idx_baseid (baseid)");
-- 为baseid字段添加名为idx_baseid的索引
PREPARE STMT FROM @statement;
EXECUTE STMT;
END IF;
END;
$
DELIMITER ;
SELECT DATABASE() INTO target_database;
这段代码中的 into target_database不知道具体作用,但经过测试,同时执行多条调用存储过程的语句,只有第一条能添加进去索引。
去掉他的结果
不去掉的结果:
if 语句
if 逻辑表达式 then
内容
Elsif 逻辑表达式 then
内容
Else
内容
End if;
游标与循环(暂没用到,先写下来)
CREATE OR REPLACE PROCEDURE yzy_test()
is
type myCur is ref cursor;
cur myCur;
returnValue VARCHAR2(3000);
SelectSQL VARCHAR2(3000);
begin
SelectSQL:= 'select test from yzy_test';
open cur for SelectSQL;
loop
exit when cur%notfound ; --当游标属于notfound,直接弹出
fetch cur into returnValue;
end loop;
close cur;
EXCEPTION WHEN OTHERS THEN
--存储过程出错走这里
end yzy_test;