DELIMITER $$
create function f_pid(@Dept_id varchar(3))
returns @t_level int
as
begin
create TEMPORARY table mytest(id varchar(3))
insert into mytest select @Dept_id;
select @Dept_id := DEPT_PARENT from department where DEPT_ID = @Dept_id and DEPT_PARENT >0;
insert into mytest select @Dept_id ;
select @Dept_id := DEPT_PARENT from department where DEPT_ID = @Dept_id and DEPT_PARENT >0;
insert into mytest select @Dept_id ;
select DISTINCT @t_level:=DEPT_ID from mytest a left join department b on b.DEPT_ID = a.id where b.DEPT_PARENT = 0
end;
DELIMITER;
create function f_pid(@Dept_id varchar(3))
returns @t_level int
as
begin
create TEMPORARY table mytest(id varchar(3))
insert into mytest select @Dept_id;
select @Dept_id := DEPT_PARENT from department where DEPT_ID = @Dept_id and DEPT_PARENT >0;
insert into mytest select @Dept_id ;
select @Dept_id := DEPT_PARENT from department where DEPT_ID = @Dept_id and DEPT_PARENT >0;
insert into mytest select @Dept_id ;
select DISTINCT @t_level:=DEPT_ID from mytest a left join department b on b.DEPT_ID = a.id where b.DEPT_PARENT = 0
end;
DELIMITER;
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
create function f_pid(v_Dept_id varchar(3))
returns int
begin
create TEMPORARY table mytest(id varchar(3)); insert into mytest select v_Dept_id;
select v_Dept_id := DEPT_PARENT from department where DEPT_ID = v_Dept_id and DEPT_PARENT >0;
insert into mytest select v_Dept_id ;
select v_Dept_id := DEPT_PARENT from department where DEPT_ID = v_Dept_id and DEPT_PARENT >0;
insert into mytest select v_Dept_id ;
select DISTINCT @t_level:=DEPT_ID from mytest a left join department b on b.DEPT_ID = a.id where b.DEPT_PARENT = 0
return t_level;
end;
$$ -- 缺少 $$
DELIMITER ; -- ; 前缺少空格
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 'DELIMITER $$
create function f_pid(v_Dept_id varchar(3))
returns int begi' at line 1
(0 ms taken)Error Code : 1054
Unknown column 'v_Dept_id' in 'field list'
(0 ms taken)Error Code : 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 ':= DEPT_PARENT from department where DEPT_ID = v_Dept_id and DEPT_PARENT >0' at line 1
(0 ms taken)Error Code : 1054
Unknown column 'v_Dept_id' in 'field list'
(10 ms taken)Error Code : 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 ':= DEPT_PARENT from department where DEPT_ID = v_Dept_id and DEPT_PARENT >0' at line 1
(0 ms taken)Error Code : 1054
Unknown column 'v_Dept_id' in 'field list'
(0 ms taken)Error Code : 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 'return t_level' at line 2
(0 ms taken)Error Code : 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
(0 ms taken)Error Code : 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 '$$ -- 缺少 $$
DELIMITER' at line 1
(0 ms taken)(0 row(s) affected)
(0 ms taken)
DELIMITER $$
create function f_pid(i_Dept_id varchar(3))
returns int
begin
create TEMPORARY table mytest(id varchar(3));insert into mytest select i_Dept_id;
select DEPT_PARENT from department where DEPT_ID = i_Dept_id and DEPT_PARENT >0 into i_Dept_id;
insert into mytest select i_Dept_id ;
select DEPT_PARENT from department where DEPT_ID = i_Dept_id and DEPT_PARENT >0 into i_Dept_id;
insert into mytest select Dept_id ;
select DISTINCT DEPT_ID from mytest a left join department b on b.DEPT_ID = a.id where b.DEPT_PARENT = 0 into @t_level;
return @t_level;
end$$
DELIMITER ;