Mysql-存储过程

kdyonly

发布于 2024-03-12 13:33:51

1060

发布于 2024-03-12 13:33:51

文章被收录于专栏：个人编程笔记

最近干的工作就是将SqlServer数据库替换成Mysql数据库，目的是为了后续的软件的国产化，这其中就涉及了大量的脚本需要修改成支持Mysql数据库的脚本，改动比较大就是存储过程的脚本，二者的语法有些区别。

对mysql的语法不是很熟，故做此记录，后续可能会有参考，就可以快速找到了，记录一个实际的例子，中间涉及一些常用的操作，比如：创建临时表，游标的使用，执行sql语句等，当然，写出来的脚本可能不是很完美，但是可以运行，可能会有抄写错误，仅供参考。

begin
declare equipId int;
declare tableName varchar(100);--表名
declare columnName varchar(100);--字段名
declare columnCHName varchar(100);--字段中文名
declare dataTypeName varchar(100);--数据类
declare dataTypeCHName varchar(100);
declare dataLength int;
declare fKeyColumnName varchar(100):
declare fKeyTableName varchar(100);
declare fKeyDisplayName varchar(100);
declare propId int;是否显示
declare isShow int;--是否显示
declare colLevel int;
declare currentDb VARCHAR(100);
declare strSql TEXT;
declare done INT DEFAULT O:
declare tmpPrTableCursor CURSOR FOR SELECT * from TmpPrTable;
declare CONTINUE HANDLERFOR NOT FOUND SET done = 1:


CREATE TEMPORARY TABLE IF NOTEXISTS TmpPropTable(
    Prop Id int,
    Prop_Name varchar(100),
    Prop_CHName varchar(100)
    Prop_Value varchar(200),
    DataType_CHName varchar(100)
    DataType_Name varchar(100),
    DATA_LENGTH int,
    PropType varchar(100),
    Table_Name varchar(100),
    TEQUIP_ID int,
    DEV_ID int,
    FKEY_COLUMN_NAME varchar(100),
    FKEY_TABLE_NAME varchar(100),
    FKEY_DISPLAY_NAME varchar(100),
    IS_SHOW int
);

CREATE TEMPORARY TABLE IF NOT EXISTS TmpPropValueTable(
    PrOpValue VARCHAR(50)
);

CREATE TEMPORARY TABLE IF NOT EXISTS TmpPrTable(
    TABLE_NAME varchar(100),
    REC_ID int,
    COLUMN_NAME varchar(100),
    COLUMN_CHNAME varchar(100),
    DATATYPE_CHNAME varchar(100),
    DATATYPE_NAME varchar(100),
    DATA_LENGTH int,
    FKEY_COLUMN_NAME varchar(100),
    FKEY_TABLE_NAME varchar(100),
    FKEY_DISPLAY_NAME varchar(100),
    COLUMN LEVEL INT
);

set currentDb=DATABASE():
select TABLE_NAME INTO tableName from TEQUIP GROUP aS a LEFT JOIN TEQUIP BENCHMARK as b on a.bunchGuid = b.Guid where a.GUID=tequipguid;
select REC_ID INTo equipId from TEQUIP_GROUP where GUID = tequipguid;
set strSql = CONCAT("insert into TmpPropvaluerable(Propvalue) select GulD from ",tableName," where DEETE_FLAG=0 AND GUID = '", devguid, "'");
-- select strSql;
set @strSql=strSql;
PREPARE stmt FROM @strSql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt:
-- select * from TmpPropValueTable;

delete from TmpPrTable;
set strSql="此处省略sql语句";
set @strSql=strSql;
PREPARE stmt FROM @strSql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt:

-- 清空数据
DELETE FROM TmpPropTable;
open tmpPrTableCursor;
read_loop: LO0P
fetch tmpPrTableCursor into tableName,propId,columnName,columnCHName,dataTypeCHName,dataTypeName,dataLength,fKeyColumnName,fKeyTableName,fKeyDisplayName,colLevel;
IF done=1 THEN
    LEAVE read_loop;
END IF;
DELETE FROM TmpPropvalueTable;
IF LENGTH(fKeyTableName)>0 THEN
    -- 如果不是存储过程则执行
    IF EXISTS (SELECT 1 FROM information_schema.TABLES WHERE TABLE_NAME = fKeyTableName) THEN
        set strSql="此处省略sql语句";
    END IF;
    IF LENGTH(strSql)>0 THEN
        set @strSql=strSql;
        PREPARE stmt FROM strsql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF:
    -- ……
END LOOP:
CLOsE tmpPrTableCursor;
-- ……
select * form TmpPropTable;
end

图片预览