赞
踩
使用存储过程时,需要转换结束符,这样执行不会报错,如下
mysql>DELIMITER &&
然后在写下面语句
DROP PROCEDURE IF EXISTS p_test1 &&
CREATE procedure p_test1(in parm1 int,out count_sum int)
BEGIN
select count(*) into count_sum from user where id> parm1;
END &&
执行完过程后,如果想要恢复原来分号结尾的执行方式,就再调用一次,如下:
mysql> DELIMITER ;
这样话调用存储存储过程就可以使用分号结尾执行了。
call p_test1(100,@test_s);
如果还是上面&&结尾的话,就还需要转换。
drop procedure if exists sp4 &&
create procedure sp4(inout p4 int)
begin
if p4 = 4 then
set @pg = 400;
else
set @pg = 500;
end if;
select @pg;
end&&
set @pp=4&&
call sp4(@pp)&&
------------------------------------------------------------------------------------------------
下面我换了一种结尾符来标明结束
DELIMITER $$
DROP PROCEDURE IF EXISTS test_sp1 $$
CREATE PROCEDURE test_sp1 (out error int)
BEGIN
DECLARE
t_error INTEGER DEFAULT 0 ; DECLARE
CONTINUE HANDLER FOR SQLEXCEPTION
SET t_error = 1 ; START TRANSACTION ;
insert into person(name,sex) values('0',10);
insert into person(name,sex) values('0',1111);
IF t_error = 1 THEN
set error=t_error;
ROLLBACK ;
ELSE
set error=0;
COMMIT ;
END
IF ;
END $$
DELIMITER ;
insert into person(name,sex) values('0',0);
call test_sp1(@error);
select @error;
------------------------------------------------------------------------------------------------
DELIMITER $$
DROP FUNCTION IF EXISTS `wesource`.`getdate`$$
CREATE FUNCTION `wesource`.`getdate`(id_1 int) RETURNS varchar(255)
BEGIN
DECLARE x VARCHAR(255) DEFAULT '';
DECLARE gdate datetime DEFAULT NULL;
select ctime into gdate from messages where id= id_1;
SET x= date_format(gdate,'%Y年%m月%d日%h时%i分%s秒');
RETURN x;
END $$
DELIMITER ;
select getdate(1);
------------------------------------------------------------------------------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS test_sp1 $$
CREATE PROCEDURE `test_sp1`(in sexVla int,out error int)
BEGIN
DECLARE
t_error INTEGER DEFAULT 0 ; DECLARE
CONTINUE HANDLER FOR SQLEXCEPTION
SET t_error = 1 ; START TRANSACTION ;
insert into person(name,sex) values('0',sexVla);
insert into person(name,sex) values('0',sexVla+1);
IF t_error = 1 THEN
set error=t_error;
ROLLBACK ;
ELSE
set error=0;
COMMIT ;
END
IF ;
END $$
DELIMITER
call test_sp1(20,@p);
select @p;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。