自己以前写的,也不是很规范!上代码DELIMITER $$USE `iccsdb`$$DROP PROCEDURE IF EXISTS `allocCustomerlog`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `allocCustomerlog`() BEGIN DECLARE v_id INT; DECLARE v_operateid VARCHAR(12); DECLARE v_bankid VARCHAR(9); /* 取变量赋值 */ DECLARE v_randID VARCHAR(12); DECLARE v_operatetime VARCHAR(50); DECLARE Done INT DEFAULT 0; DECLARE v_count INT DEFAULT 0; DECLARE rs CURSOR FOR SELECT seqnum,operateid,bankid,operatetime FROM customerlogtb WHERE bankid!=LEFT(operateid,9); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; OPEN rs; DROP TABLE IF EXISTS operatorTemp; CREATE TABLE `operatorTemp`( `SeqTime` DATETIME DEFAULT NULL, `OperatorID` VARCHAR(16) DEFAULT NULL ); FETCH NEXT FROM rs INTO v_id,v_operateId,v_bankid,v_operatetime; REPEAT IF NOT Done THEN INSERT INTO operatorTemp SELECT seqtime,operatorid FROM idcardinfotbtmp WHERE v_bankid=LEFT(operatorid,9) AND RIGHT(operatorid,3)<100 AND DATE(seqtime)=DATE(v_operatetime) ; UPDATE customerlogtb SET operateid=(SELECT OperatorID INTO v_randID FROM operatorTemp ORDER BY RAND() LIMIT 1) WHERE customerlogtb.seqnum=v_id; UPDATE idcardinfotb SET operatorid=v_randID WHERE bankid=v_bankid AND operatorid=v_operateid; DELETE FROM operatorTemp; SET v_count=v_count+1; SELECT v_count FROM DUAL; END IF; FETCH NEXT FROM rs INTO v_id,v_operateId,v_bankid,v_operatetime; UNTIL Done END REPEAT; CLOSE rs; DROP TABLE operatorTemp; END$$DELIMITER ;楼主是想问自己的出错在哪里?
楼主可以写一个简单的select 的 语句就可以实现简单的存储过程create procedure test() begin select * from test where test.id=1; end这是简单的无参的存储过程。 call test();测试一下
DELIMITER $$ create procedure test() begin select * from test where test.id=1; end$$ DELIMITER ;
BEGIN
DECLARE v_id INT;
DECLARE v_operateid VARCHAR(12);
DECLARE v_bankid VARCHAR(9);
/* 取变量赋值 */
DECLARE v_randID VARCHAR(12);
DECLARE v_operatetime VARCHAR(50);
DECLARE Done INT DEFAULT 0;
DECLARE v_count INT DEFAULT 0;
DECLARE rs CURSOR FOR SELECT seqnum,operateid,bankid,operatetime FROM customerlogtb WHERE bankid!=LEFT(operateid,9);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
OPEN rs;
DROP TABLE IF EXISTS operatorTemp;
CREATE TABLE `operatorTemp`(
`SeqTime` DATETIME DEFAULT NULL,
`OperatorID` VARCHAR(16) DEFAULT NULL
);
FETCH NEXT FROM rs INTO v_id,v_operateId,v_bankid,v_operatetime;
REPEAT
IF NOT Done THEN
INSERT INTO operatorTemp SELECT seqtime,operatorid FROM idcardinfotbtmp WHERE v_bankid=LEFT(operatorid,9) AND RIGHT(operatorid,3)<100 AND DATE(seqtime)=DATE(v_operatetime) ;
UPDATE customerlogtb SET operateid=(SELECT OperatorID INTO v_randID FROM operatorTemp ORDER BY RAND() LIMIT 1) WHERE customerlogtb.seqnum=v_id;
UPDATE idcardinfotb SET operatorid=v_randID WHERE bankid=v_bankid AND operatorid=v_operateid;
DELETE FROM operatorTemp;
SET v_count=v_count+1;
SELECT v_count FROM DUAL;
END IF;
FETCH NEXT FROM rs INTO v_id,v_operateId,v_bankid,v_operatetime;
UNTIL Done END REPEAT;
CLOSE rs;
DROP TABLE operatorTemp;
END$$DELIMITER ;楼主是想问自己的出错在哪里?
begin
select * from test where test.id=1;
end这是简单的无参的存储过程。
call test();测试一下
create procedure test()
begin
select * from test where test.id=1;
end$$
DELIMITER ;
少了 delimiter
少了 分号建议先看一下MYSQL官方免费手册中的教程和例子。毕竟和SQL SERVER语法上不同。
按照这么个格式来!
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `iccsdb`.`test`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN END$$DELIMITER ;在公司测试的时候也是这种错误,不知道为什么。
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `iccsdb`.`test`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN END$$DELIMITER ;
九本mysql经典教材的下载地址,有一本专讲存储过程的
http://download.csdn.net/detail/kofandlizi/6475545