我写了个存储过程,作用是将数据插入到a表中,请看源代码:DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
-- 创建b表
CREATE TABLE b
(b1 INT NOT NULL,
b2 BIGINT(20),
b3 VARCHAR(50),
CONSTRAINT PRIMARY KEY (b1)
);
-- 插入b表数据
INSERT INTO b VALUES (1001,201303,'b1');
INSERT INTO b VALUES (1002,201403,'b2');
INSERT INTO b VALUES (1003,201503,'b3');
INSERT INTO b VALUES (1004,201603,'b3');
INSERT INTO b VALUES (1005,201703,'b3');
INSERT INTO b VALUES (1006,201803,'b3');
-- 创建a表
CREATE TABLE a
(a1 INT NOT NULL,
a2 VARCHAR(50),
a3 BIGINT(20),
b1 INT,
CONSTRAINT PRIMARY KEY(a1),
CONSTRAINT fk_b1 FOREIGN KEY (b1) REFERENCES b (b1)
);
-- 插入a表数据
INSERT INTO a VALUES (1,'SB01',20130301,1001);
INSERT INTO a VALUES (2,'SB02',20140302,1002);
INSERT INTO a VALUES (3,'SB03',20150303,1003);
-- 创建存储过程
CREATE PROCEDURE spInsa
(v_a1 INT,
v_a2 VARCHAR(50),
v_b1 INT
)
BEGIN
DECLARE v_b2 BIGINT(20);
DECLARE v_a3 BIGINT(20);
SELECT b2 INTO v_b2 FROM b WHERE b1=v_b1;
SET v_a3=CONCAT(v_b2,(v_a2+0));-- a3的数据是b2的数据和a2数据中数字部分联合组成的
INSERT INTO a VALUES
(v_a1,
v_a2,
v_a3,
v_b1
);
END;然后调用这个存储过程时报错:CALL spInsa(4,'SB04',1004);[Err] 1292 - Truncated incorrect DOUBLE value: 'SB04'不知道错在哪里,请高手们帮忙看看。
DROP TABLE IF EXISTS b;
-- 创建b表
CREATE TABLE b
(b1 INT NOT NULL,
b2 BIGINT(20),
b3 VARCHAR(50),
CONSTRAINT PRIMARY KEY (b1)
);
-- 插入b表数据
INSERT INTO b VALUES (1001,201303,'b1');
INSERT INTO b VALUES (1002,201403,'b2');
INSERT INTO b VALUES (1003,201503,'b3');
INSERT INTO b VALUES (1004,201603,'b3');
INSERT INTO b VALUES (1005,201703,'b3');
INSERT INTO b VALUES (1006,201803,'b3');
-- 创建a表
CREATE TABLE a
(a1 INT NOT NULL,
a2 VARCHAR(50),
a3 BIGINT(20),
b1 INT,
CONSTRAINT PRIMARY KEY(a1),
CONSTRAINT fk_b1 FOREIGN KEY (b1) REFERENCES b (b1)
);
-- 插入a表数据
INSERT INTO a VALUES (1,'SB01',20130301,1001);
INSERT INTO a VALUES (2,'SB02',20140302,1002);
INSERT INTO a VALUES (3,'SB03',20150303,1003);
-- 创建存储过程
CREATE PROCEDURE spInsa
(v_a1 INT,
v_a2 VARCHAR(50),
v_b1 INT
)
BEGIN
DECLARE v_b2 BIGINT(20);
DECLARE v_a3 BIGINT(20);
SELECT b2 INTO v_b2 FROM b WHERE b1=v_b1;
SET v_a3=CONCAT(v_b2,(v_a2+0));-- a3的数据是b2的数据和a2数据中数字部分联合组成的
INSERT INTO a VALUES
(v_a1,
v_a2,
v_a3,
v_b1
);
END;然后调用这个存储过程时报错:CALL spInsa(4,'SB04',1004);[Err] 1292 - Truncated incorrect DOUBLE value: 'SB04'不知道错在哪里,请高手们帮忙看看。
解决方案 »
- vc6.0和mysql数据库连接读取数据乱码的问题。。
- 对于这个要求,我有以下几个方案,大家来看看或者出主意。回者给分~!
- mysql like关键字问题
- Mysql数据库,用关键字做了列名,查询的时候如何解决?
- [MySql5.0] 建立外键问题
- 用mysql创建完表后,如何设置主键
- mysql + odbc + vc + IsEOF的问题
- 问个MYSQL的左连接,实现论坛的最新回帖功能的,在线裸等,即给分
- 急,在线等!windows2000Sp4和mysql4.0.18
- myodbc插入较长string总是失败,如何解决?
- 关于数据库插入的问题 求助~~
- mysql坑爹了,把我一个大活人折腾成白痴了。。。
[Err] 1630 - FUNCTION test.RIGHT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
-> begin
-> declare v_b2 bigint(20);
-> declare v_a3 bigint(20);
-> select b2 into v_b2 from b where b1=v_b1;
-> set v_a3 = concat(v_b2,substring(v_a2,3,2));
-> insert into a values (v_a1,v_a2,v_a3,v_b1);
-> end //
Query OK, 0 rows affected (0.00 sec)mysql> call spInsa(4,'SB04',1004)//
Query OK, 1 row affected (0.03 sec)mysql> select * from a where a1 =4 //
+----+------+----------+------+
| a1 | a2 | a3 | b1 |
+----+------+----------+------+
| 4 | SB04 | 20160304 | 1004 |
+----+------+----------+------+
1 row in set (0.00 sec)