我是想实现插入一条查询,想先判断输入数据的关键字是否在数据库中有重复,先selet查询一下。
DROP FUNCTION IF EXISTS `proc_operator_insert`;CREATE DEFINER = `root`@`%` FUNCTION `proc_operator_insert`(
`s_code` char(10),
`s_name` char(10),
`s_pwd` char(40),
`s_company` char(40),
`s_email` char(20),
`i_phone` int(11),
`i_role` tinyint(1),
`s_is_login` char(1),
`s_is_email` char(1),
`s_is_fetion` char(1),
`i_last_time` int(10))
RETURNS tinyint(1)
BEGIN
IF
SELECT
operator.code
FROM
operator
WHERE
operator.code = `s_code`;INSERT INTO operator (
code,
name,
pwd,
company,
email,
phone,
role,
is_login,
is_email,
is_fetion,
last_time)
VALUES(
`s_code`,`s_name`,`s_pwd`,`s_company`,`s_email`,`i_phone`,`i_role`,`s_is_login`,`s_is_email`,`s_is_fetion`,`i_last_time`);
RETURN 1;
END;谢谢告诉我应该怎么改?不知道IF怎么写
DROP FUNCTION IF EXISTS `proc_operator_insert`;CREATE DEFINER = `root`@`%` FUNCTION `proc_operator_insert`(
`s_code` char(10),
`s_name` char(10),
`s_pwd` char(40),
`s_company` char(40),
`s_email` char(20),
`i_phone` int(11),
`i_role` tinyint(1),
`s_is_login` char(1),
`s_is_email` char(1),
`s_is_fetion` char(1),
`i_last_time` int(10))
RETURNS tinyint(1)
BEGIN
IF
SELECT
operator.code
FROM
operator
WHERE
operator.code = `s_code`;INSERT INTO operator (
code,
name,
pwd,
company,
email,
phone,
role,
is_login,
is_email,
is_fetion,
last_time)
VALUES(
`s_code`,`s_name`,`s_pwd`,`s_company`,`s_email`,`i_phone`,`i_role`,`s_is_login`,`s_is_email`,`s_is_fetion`,`i_last_time`);
RETURN 1;
END;谢谢告诉我应该怎么改?不知道IF怎么写
可是我在存储过程里写:
RETURN 0;
RETURN 1;
结果只会返回第一行的RETURN 0;为什么?哪里错了?
`s_code` char(10),
`s_name` char(10),
`s_pwd` char(40),
`s_company` char(40),
`s_email` char(20),
`i_phone` int(11),
`i_role` tinyint(1),
`s_is_login` char(1),
`s_is_email` char(1),
`s_is_fetion` char(1),
`i_last_time` int(10))
RETURNS tinyint(1)
BEGIN declare bExist as int; -- define the var.
select count(*) into bExist FROM operator WHERE operator.code = `s_code`; -- check the record exist?IF bExist=0 thenINSERT INTO operator (
code,
name,
pwd,
company,
email,
phone,
role,
is_login,
is_email,
is_fetion,
last_time)
VALUES(
`s_code`,`s_name`,`s_pwd`,`s_company`,`s_email`,`i_phone`,`i_role`,`s_is_login`,`s_is_email`,`s_is_fetion`,`i_last_time`);
end ifRETURN 1;
END;
`s_code` char(10),
`s_name` char(10),
`s_pwd` char(40),
`s_company` char(40),
`s_email` char(20),
`i_phone` int(11),
`i_role` tinyint(1),
`s_is_login` char(1),
`s_is_email` char(1),
`s_is_fetion` char(1),
`i_last_time` int(10))
RETURNS tinyint(1)
BEGIN declare bExist as int; -- define the var.
select count(*) into bExist FROM operator WHERE operator.code = `s_code`; -- check the record exist?IF bExist=0 thenINSERT INTO operator (
code,
name,
pwd,
company,
email,
phone,
role,
is_login,
is_email,
is_fetion,
last_time)
VALUES(
`s_code`,`s_name`,`s_pwd`,`s_company`,`s_email`,`i_phone`,`i_role`,`s_is_login`,`s_is_email`,`s_is_fetion`,`i_last_time`);
RETURN 1;
else
RETURN 0;
end if
END;
end if后面加上;
就可以了
可是个NND怎么不能用注释?我用--和/* */都不行,报错
还有个问题:
怎么可以提前退出存储过程,
比如:
if 1=0 then
退出
else
继续
end是不是RETURN 这个语句执行后就会自动退出
Query OK, 0 rows affected (0.02 sec)mysql> insert into tb_primary values('a','test1');
Query OK, 1 row affected (0.01 sec)mysql> insert ignore tb_primary values('a','test_bbb');
Query OK, 0 rows affected (0.01 sec)mysql> select * from tb_primary;
+------+-------+
| name | memo |
+------+-------+
| a | test1 |
+------+-------+
1 row in set (0.01 sec)mysql> insert ignore tb_primary values('b','test2');
Query OK, 1 row affected (0.00 sec)mysql> select * from tb_primary;
+------+-------+
| name | memo |
+------+-------+
| a | test1 |
| b | test2 |
+------+-------+
2 rows in set (0.00 sec)mysql> insert ignore tb_primary values('b','test2');
Query OK, 0 rows affected (0.00 sec)mysql> select * from tb_primary;
+------+-------+
| name | memo |
+------+-------+
| a | test1 |
| b | test2 |
+------+-------+
2 rows in set (0.00 sec)mysql>