一个mysql存储记录

页面导航:首页 > 数据库 > Mysql > 一个mysql存储记录

一个mysql存储记录

来源: 作者: 时间:2016-02-05 10:56 【

一个mysql存储记录01BEGIN02#-------------------------tbsysinfo03prepare tempSql from ALTER TABLE `tbsysinfo` ADD COLUMN `szClass` varchar(2) NULL DEFAULT #39;00#39; AFTER `nT...
一个mysql存储记录
 
01
BEGIN
02
#-------------------------tbsysinfo
03
prepare tempSql from "ALTER TABLE `tbsysinfo` ADD COLUMN `szClass`  varchar(2) NULL  DEFAULT '00'  AFTER `nType`";
04
execute tempSql; 
05

06
#-------------------------netCard
07
prepare tempSql from "CREATE TABLE `tbNetCfg` ( `nID` INT(10) NULL AUTO_INCREMENT,  `szCapture` VARCHAR(100) NOT NULL DEFAULT '0',  `szServiceName` VARCHAR(100) NOT NULL DEFAULT '0',  `szServiceIP` VARCHAR(100) NOT NULL DEFAULT '0',    PRIMARY KEY (`nID`))COLLATE='utf8_general_ci' ENGINE=InnoDB;";
08
execute tempSql;
09
SELECT szAdminIP into @szAdminIp FROM `tbsyscfg`;
10
Select szName into @card1 from  tbnetcards where nType = 1 limit 1;
11
Select szName into @card2 from  tbnetcards where nType = 2 limit 1;
12
if @card2 is NULL THEN
13
    set @card2 = @card1;
14
end if;
15
insert into tbNetCfg values(NULL,@card1,@card2,@szAdminIp);
16

17
#-------------------------limitSpeed
18
prepare tmpSqlToRun from "ALTER TABLE `tblimitspeed` ADD COLUMN `nWeekDay`  smallint(1) NOT NULL DEFAULT 1 AFTER `nTimeZDef`,ADD COLUMN `group`  smallint(1) NULL DEFAULT 1 AFTER `nWeekDay`,ADD PRIMARY KEY (`nWeekDay`)";
19
execute tmpSqlToRun;
20
select nWeekDay,`group`,nBandWidth,nTimeZDef,nTime0,nTime1,nTime2,nTime3,nTime4,nTime5,nTime6,nTime7,nTime8,nTime9,nTime10,nTime11,nTime12,nTime13,nTime14,nTime15,nTime16,nTime17,nTime18,nTime19,nTime20,nTime21,nTime22,nTime23
21
into @nWeekDay,@groupp,@BandWidth,@nTimeZDef,@nTime0,@nTime1,@nTime2,@nTime3,@nTime4,@nTime5,@nTime6,@nTime7,@nTime8,@nTime9,@nTime10,@nTime11,@nTime12,@nTime13,@nTime14,@nTime15,@nTime16,@nTime17,@nTime18,@nTime19,@nTime20,@nTime21,@nTime22,@nTime23
22
from tblimitspeed;
23
insert into tblimitspeed values(@nTime0,@nTime1,@nTime2,@nTime3,@nTime4,@nTime5,@nTime6,@nTime7,@nTime8,@nTime9,@nTime10,@nTime11,@nTime12,@nTime13,@nTime14,@nTime15,@nTime16,@nTime17,@nTime18,@nTime19,@nTime20,@nTime21,@nTime22,@nTime23,@BandWidth,@nTimeZDef,2,@groupp);  
24
END

 

说明
升级写的一个存储过程,记录一下,以前每次写存储过程都需要全网找资料,很烦。
 
执行修改表结构语句:
 
prepare tempSql from "ALTER TABLE tbsysinfo ADD COLUMN szClass varchar(2) NULL DEFAULT '00' AFTER nType"; 
execute tempSql;

 

 
结果赋值:
 
Select szName into @card1 from tbnetcards where nType = 1 limit 1; 
set @card2 = @card1; 
未使用的一种 
DECLARE cnt INT DEFAULT 0; 
select count(*) into cnt from test_tbl; 
select cnt;

 

 
IF语法:
 
if @card2 is NULL THEN 
set @card2 = @card1; 
end if;

 

 
最后limitSpeed 部分,20多个字段,没找到简单的多列赋值的方法,蠢办法一个个赋值,真是--!!
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<