小弟对Mysql存储过程语法不太熟悉,以下存储过程在调整过后执行仍不成功,求各位大侠指点指点。。
create procedure prc_AddChannel
(
id int,
iname varchar(50),
isdisplay int,
descriptions varchar(100),
demo varchar(100)
)
begin
declare myRight int;
declare pro int;
SELECT Right_Value into myRight FROM CHANNEL WHERE CHANNEL_ID=id;
SELECT count(*) into pro FROM CHANNEL WHERE PARENT_ID=id;
UPdate CHANNEL SET HAS_CHILD = 1 WHERE CHANNEL_ID=id;
UPdate CHANNEL SET RIGHT_VALUE = RIGHT_VALUE+2 WHERE RIGHT_VALUE>=myRight;
UPdate CHANNEL SET LEFT_VALUE = LEFT_VALUE+2 WHERE LEFT_VALUE>myRight;
INSERT INTO CHANNEL(PARENT_ID,TITLE,CREATE_TIME,Left_Value,Right_Value,HAS_CHILD,PRORITY,Is_Display,Description,Demo) VALUES (id,iname,now(),myRight,myRight + 1,0,pro+1,isdisplay,descriptions,demo);
end; create procedure prc_DelChannel(id int)
begin
declare myRight int ;
declare myLeft int ;
declare myWidth int ;
SELECT Right_Value into myRight FROM CHANNEL WHERE CHANNEL_ID = id;
SELECT LEFT_VALUE into myLeft FROM CHANNEL WHERE CHANNEL_ID = id;
SELECT Right_Value - LEFT_VALUE + 1 into myWidth FROM CHANNEL WHERE CHANNEL_ID = id;
DELETE FROM CHANNEL WHERE LEFT_VALUE BETWEEN myLeft AND myRight;
UPdate CHANNEL SET Right_Value = Right_Value - myWidth WHERE Right_Value > myRight;
UPdate CHANNEL SET LEFT_VALUE = LEFT_VALUE - myWidth WHERE LEFT_VALUE > myRight;
end;
create procedure prc_AddChannel
(
id int,
iname varchar(50),
isdisplay int,
descriptions varchar(100),
demo varchar(100)
)
begin
declare myRight int;
declare pro int;
SELECT Right_Value into myRight FROM CHANNEL WHERE CHANNEL_ID=id;
SELECT count(*) into pro FROM CHANNEL WHERE PARENT_ID=id;
UPdate CHANNEL SET HAS_CHILD = 1 WHERE CHANNEL_ID=id;
UPdate CHANNEL SET RIGHT_VALUE = RIGHT_VALUE+2 WHERE RIGHT_VALUE>=myRight;
UPdate CHANNEL SET LEFT_VALUE = LEFT_VALUE+2 WHERE LEFT_VALUE>myRight;
INSERT INTO CHANNEL(PARENT_ID,TITLE,CREATE_TIME,Left_Value,Right_Value,HAS_CHILD,PRORITY,Is_Display,Description,Demo) VALUES (id,iname,now(),myRight,myRight + 1,0,pro+1,isdisplay,descriptions,demo);
end; create procedure prc_DelChannel(id int)
begin
declare myRight int ;
declare myLeft int ;
declare myWidth int ;
SELECT Right_Value into myRight FROM CHANNEL WHERE CHANNEL_ID = id;
SELECT LEFT_VALUE into myLeft FROM CHANNEL WHERE CHANNEL_ID = id;
SELECT Right_Value - LEFT_VALUE + 1 into myWidth FROM CHANNEL WHERE CHANNEL_ID = id;
DELETE FROM CHANNEL WHERE LEFT_VALUE BETWEEN myLeft AND myRight;
UPdate CHANNEL SET Right_Value = Right_Value - myWidth WHERE Right_Value > myRight;
UPdate CHANNEL SET LEFT_VALUE = LEFT_VALUE - myWidth WHERE LEFT_VALUE > myRight;
end;
你可以参考一下手册中的语法。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
错误码: 1327
Undeclared variable: myRight
错误码: 1327
Undeclared variable: pro
错误码: 1054
Unknown column 'id' in 'where clause'
错误码: 1054
Unknown column 'myRight' in 'where clause'
错误码: 1054
Unknown column 'id' in 'field list'
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end' at line 1
我看蒙了。。不知道怎么解决哇
--创建栏目表
create table CHANNEL
(
CHANNEL_ID int AUTO_INCREMENT primary key not null,
PARENT_ID int references CHANNEL(CHANNEL_ID),
TITLE varchar(100) not null,
CREATE_TIME datetime,
LEFT_VALUE int,
RIGHT_VALUE int,
HAS_CHILD int,
PRORITY int,
IS_DISPLAY int ,
DESCRIPTION varchar(100),
DEMO varchar(100)
)
这个是栏目表结构,麻烦大侠们帮忙测试下能否执行成功
insert into CHANNEL(PARENT_ID,TITLE,CREATE_TIME,LEFT_VALUE,RIGHT_VALUE,HAS_CHILD,IS_DISPLAY) values(null,'资讯中心',now(),1,2,1,0)
-> (
-> CHANNEL_ID int AUTO_INCREMENT primary key not null,
-> PARENT_ID int references CHANNEL(CHANNEL_ID),
-> TITLE varchar(100) not null,
-> CREATE_TIME datetime,
-> LEFT_VALUE int,
-> RIGHT_VALUE int,
-> HAS_CHILD int,
-> PRORITY int,
-> IS_DISPLAY int ,
-> DESCRIPTION varchar(100),
-> DEMO varchar(100)
-> );
Query OK, 0 rows affected (0.16 sec)mysql>
CALL prc_DelChannel(5);
没有问题
delimtier //
create procedure ..
being
...
end
//
delimiter ;