mysql存储过程语句(六)
---------
04
DROP FUNCTION IF EXISTS `GetListNums`;
05
DELIMITER ;; www.2cto.com
06
CREATE DEFINER=`root`@`%` FUNCTION `GetListNums`(CurLogID bigint) RETURNS bigint(20)
07
begin
08
RETURN (Select Count(*) as ListenNums from QueryRecord, UserLog Where UserLog.LogID=CurLogID and QueryRecord.UserID = UserLog.UserID
09
and QueryRecord.QueryTime >= UserLog.LogINTime and QueryRecord.QueryTime <= UserLog.LogOUTTime);
10
end
11
;;
12
DELIMITER ;
01
-- ----------------------------
02
-- Function structure for `GetParDepIDByLevel`
03
-- ----------------------------
04
DROP FUNCTION IF EXISTS `GetParDepIDByLevel`;
05
DELIMITER ;;
06
CREATE DEFINER=`root`@`%` FUNCTION `GetParDepIDByLevel`(depID bigint,
07
depLevel int) RETURNS bigint(20)
08
COMMENT '根据部门编号和所要查找的级别找到该级别相对应的部门编号\r\n如 32010101,1 --->32000000\r\n'
09
begin
10
declare parDepID bigint default depID;
11
declare levelPar bigint default 0;
12
declare i int default 0;
13
14
if GetDepLevelByID(depID)<>depLevel then
15
WHILE parDepID>power(100,depLevel) DO
16
set levelPar=power(100,i);
17
set parDepID=depID div levelPar;
18
set i=i+1;
19
END WHILE;
20
set levelPar=power(100,i-1);
21
set parDepID=parDepID*levelPar;
22 www.2cto.com
return parDepID;
23
24
else
25
return depID;
26
end if;
27
end
28
;;
29
DELIMITER ;
作者 bug哥