先贴出两个不同数据库的prc进行直观对比.
此为oracle存储过程
create or replace procedure PROC_AAA_VOICECODE_LIMIT(
appid in varchar2, -- 商户id
callee in varchar2, -- callee被叫
ret out integer -- 0 允许接受验证码， 1 不允许
)
as
defaultbegintime varchar2(20) := null;
defaultendtime varchar2(20) := null;
defaultnumbers integer := 0;
callnumbers integer := 0;
begintime varchar2(20) := null;
endtime varchar2(20) := null;
sysdatetime varchar2(20) := null;
sysdatetimeforday varchar2(20) := null;
tmpnumbers integer := 0;
trimsysdate varchar2(20) := null;
begin
ret := 0;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') into sysdatetime from dual;
--sysdatetime := '2016/05/11 03:33:33'; --test data
select to_char(sysdate,'yyyy/mm/dd') into sysdatetimeforday from dual;
trimsysdate := substr(sysdatetime,1,16);
-- 凌晨限制
-- 查看是否有默认凌晨限制
begin
select um_callnumbers ,to_char(um_ambegintime,' hh24:mi:ss'), to_char(um_amendtime,' hh24:mi:ss') into defaultnumbers,defaultbegintime, defaultendtime from um_item_limit where um_appid = '9527DEFAULT' and um_type = 2;
exception when no_data_found then
defaultnumbers := null;
defaultbegintime := null;
defaultendtime := null;
end;
-- 限制信息不全， 直接清空
if defaultnumbers is null or defaultbegintime is null or defaultendtime is null then
defaultnumbers := null;
defaultbegintime := null;
defaultendtime := null;
end if;
-- 查看商户是否有配置凌晨限制
begin
select um_callnumbers,to_char(um_ambegintime,' hh24:mi:ss'), to_char(um_amendtime,' hh24:mi:ss') into callnumbers,begintime, endtime from um_item_limit where um_appid = appid and um_type =2;
exception when no_data_found then
callnumbers := null;
begintime := null;
endtime := null;
end;
-- 如果凌晨限制不全，使用默认的
if callnumbers is null or begintime is null or endtime is null then
callnumbers := defaultnumbers;
begintime := defaultbegintime;
endtime := defaultendtime;
end if;
--有完整的凌晨限制配置信息
if callnumbers is not null and begintime is not null and endtime is not null then
--查看当前时间是否在限制范围之内
--if it does
if to_date(sysdatetime, 'yyyy/mm/dd hh24:mi:ss') > to_date(sysdatetimeforday || begintime ,'yyyy/mm/dd hh24:mi:ss') and to_date(sysdatetime, 'yyyy/mm/dd hh24:mi:ss') < to_date(sysdatetimeforday || endtime ,'yyyy/mm/dd hh24:mi:ss') then
--如果限制为0 直接退出 表示限制 不能外呼
if callnumbers = 0 then
ret := 1;
return ;
end if;
--查看已经拨打的次数
begin
select count(*) into tmpnumbers from center_mes_manage where cc_caller = callee and cc_creatlogtime > to_date(sysdatetimeforday || begintime ,'yyyy/mm/dd hh24:mi:ss') and cc_creatlogtime < to_date(sysdatetimeforday || endtime ,'yyyy/mm/dd hh24:mi:ss')
and cc_appid = appid;
exception when no_data_found then
tmpnumbers := 0;
end;
if tmpnumbers >= callnumbers then
ret :=1;
return ;
end if;
end if;
end if;
------------------end for morining limit----
------------------begin for limit in min-----
-- 查看默认的分钟限制信息，如果默认配置为0。不能表示全部禁止拨打
begin
select um_callnumbers into defaultnumbers from um_item_limit where um_appid = '9527DEFAULT' and um_type = 1;
exception when no_data_found then
defaultnumbers := null;
end;
if defaultnumbers = 0 then
defaultnumbers := null;
end if;
--查看商户的分钟限制拨打的限制，如果为0 表示禁止拨打。
begin
select um_callnumbers into callnumbers from um_item_limit where um_appid = appid and um_type = 1;
exception when no_data_found then
callnumbers := null;
end;
if callnumbers = 0 then -- 0 限制拨打
ret := 1;
return ;
end if;
if callnumbers is null then
callnumbers := defaultnumbers;
end if;
if callnumbers is not null then
begin
select count(*) into tmpnumbers from center_mes_manage where cc_caller = callee
and cc_creatlogtime > to_date(trimsysdate || ':00','yyyy/mm/dd hh24:mi:ss')
and cc_creatlogtime < to_date(trimsysdate || ':59','yyyy/mm/dd hh24:mi:ss')
and cc_appid = appid;
exception when no_data_found then
tmpnumbers := 0;
end;
if callnumbers <= tmpnumbers then
ret := 1;
return;
end if;
end if;
--------------end for limit min--------
--呼叫次数限制功能 1. 优先看商户有没有单独配限制，有则使用之
--2. 如没有，再使用公共的限制值，如公共参数未配置，则不限制
begin
select um_callnumbers into defaultnumbers from um_item_limit where um_appid = '9527DEFAULT' and um_type = 0;
exception when no_data_found then
defaultnumbers := null;
end;
if defaultnumbers = 0 then
defaultnumbers := null;
end if;
begin
select um_callnumbers into callnumbers from um_item_limit where um_appid = appid and um_type = 0;
exception when no_data_found then
callnumbers := null;
end;
if callnumbers = 0 then -- 0 限制拨打
ret := 1;
return ;
end if;
if callnumbers is null then
callnumbers := defaultnumbers;
end if;
if callnumbers is not null then
begin
select count(*) into tmpnumbers from center_mes_manage where cc_caller = callee and cc_creatlogtime > to_date( sysdatetimeforday || ' 00:00:00','yyyy/mm/dd hh24:mi:ss') and cc_appid = appid;
exception when no_data_found then
tmpnumbers := 0;
end;
if callnumbers <= tmpnumbers then
ret := 1;
return ;
end if;
end if;
--end for call numbers
end PROC_AAA_VOICECODE_LIMIT;
/此为mysql存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS PROC_AAA_VOICECODE_LIMIT$$
CREATE PROCEDURE PROC_AAA_VOICECODE_LIMIT(
IN appid VARCHAR(255), -- 商户id
IN callee VARCHAR(255), -- callee被叫
OUT ret INTEGER -- 0 允许接受验证码， 1 不允许
)
aa:BEGIN
DECLARE defaultbegintime VARCHAR(20) DEFAULT NULL;
DECLARE defaultendtime VARCHAR(20) DEFAULT NULL;
DECLARE defaultbeginday VARCHAR(20) DEFAULT NULL; -- moren 默认开始日期
DECLARE defaultendday VARCHAR(20) DEFAULT NULL; -- 默认的结束日期
DECLARE defaultnumbers INTEGER DEFAULT 0;
DECLARE callnumbers INTEGER DEFAULT 0;
DECLARE begintime VARCHAR(20) DEFAULT NULL;
DECLARE endtime VARCHAR(20) DEFAULT NULL;
DECLARE beginday VARCHAR(20) DEFAULT NULL; -- 开始日期
DECLARE endday VARCHAR(20) DEFAULT NULL; -- 结束日期
DECLARE sysdatetime VARCHAR(20) DEFAULT NULL;
DECLARE sysdatetimeforday VARCHAR(20) DEFAULT NULL;
DECLARE sysdatetimeforyesterday VARCHAR(20) DEFAULT NULL;
DECLARE sysdatetimeforbeginday VARCHAR(20) DEFAULT NULL;
DECLARE tmpnumbers INTEGER DEFAULT 0;
DECLARE trimsysdate VARCHAR(20) DEFAULT NULL;
SET ret = 0;
SET sysdatetime = NOW();
-- sysdatetime := '2016/05/11 03:33:33'; -- test data
SET sysdatetimeforday = CURDATE();
SET sysdatetimeforyesterday = DATE_SUB(CURDATE(),INTERVAL 1 DAY); -- 获得昨天的日期
SET trimsysdate = SUBSTR(sysdatetime,1,16);
-- 凌晨限制
-- 查看是否有默认凌晨限制
BEGIN
SELECT um_callnumbers ,DATE_FORMAT(um_ambegintime,'%T'), DATE_FORMAT(um_amendtime,'%T'),DATE_FORMAT(um_ambegintime,'%Y-%m-%d'),DATE_FORMAT(um_amendtime,'%Y-%m-%d') INTO defaultnumbers,defaultbegintime, defaultendtime,defaultbeginday,defaultendday FROM UM_ITEM_LIMIT WHERE um_appid = '9527DEFAULT' AND um_type = 2;
IF FOUND_ROWS()=0 THEN
SET defaultnumbers = NULL;
SET defaultbegintime = NULL;
SET defaultendtime = NULL;
SET defaultbeginday = NULL;
SET defaultendday = NULL;
END IF;
END;
-- 限制信息不全， 直接清空
IF defaultnumbers IS NULL OR defaultnumbers = '' OR defaultbegintime IS NULL OR defaultbegintime = '' OR defaultendtime IS NULL OR defaultendtime = ''THEN
SET defaultnumbers = NULL;
SET defaultbegintime = NULL;
SET defaultendtime = NULL;
SET defaultbeginday = NULL;
SET defaultendday = NULL;
END IF;
-- 查看商户是否有配置凌晨限制
BEGIN
SELECT um_callnumbers,DATE_FORMAT(um_ambegintime,'%T'), DATE_FORMAT(um_amendtime,'%T'),DATE_FORMAT(um_ambegintime,'%Y-%m-%d'),DATE_FORMAT(um_amendtime,'%Y-%m-%d') INTO callnumbers,begintime, endtime,beginday,endday FROM UM_ITEM_LIMIT WHERE um_appid = appid AND um_type =2;
IF FOUND_ROWS()=0 THEN
SET callnumbers = NULL;
SET begintime = NULL;
SET endtime = NULL;
SET beginday = NULL;
SET endday = NULL;
END IF;
END;
-- 如果凌晨限制不全，使用默认的
IF callnumbers IS NULL OR callnumbers = '' OR begintime IS NULL OR begintime = '' OR endtime IS NULL OR endtime = ''OR beginday IS NULL OR beginday = '' OR endday IS NULL OR endday = ''THEN
SET callnumbers = defaultnumbers;
SET begintime = defaultbegintime;
SET endtime = defaultendtime;
SET beginday = defaultbeginday;
SET endday = defaultendday;
END IF;
-- 有完整的凌晨限制配置信息
IF (callnumbers IS NOT NULL OR callnumbers <> '') AND (begintime IS NOT NULL OR begintime <> '') AND (endtime IS NOT NULL OR endtime <> '') THEN
-- 查看当前时间是否在限制范围之内
-- if it does
IF beginday <> endday THEN
SET sysdatetimeforbeginday = sysdatetimeforyesterday;
ELSE
SET sysdatetimeforbeginday = sysdatetimeforday;
END IF;
IF STR_TO_DATE(sysdatetime,'%Y-%m-%d %T') > STR_TO_DATE(CONCAT(sysdatetimeforbeginday,begintime),'%Y-%m-%d %T') AND STR_TO_DATE(sysdatetime,'%Y-%m-%d %T') < STR_TO_DATE(CONCAT(sysdatetimeforday,endtime),'%Y-%m-%d %T') THEN
-- 如果限制为0 直接退出 表示限制 不能外呼
IF callnumbers = 0 THEN
SET ret = 1;
LEAVE aa; -- return
END IF;
-- 查看已经拨打的次数
BEGIN
SELECT COUNT(*) INTO tmpnumbers FROM CENTER_MES_MANAGE WHERE cc_caller = callee AND cc_creatlogtime > STR_TO_DATE(CONCAT(sysdatetimeforbeginday,begintime),'%Y-%m-%d %T') AND cc_creatlogtime < STR_TO_DATE(CONCAT(sysdatetimeforday,endtime),'%Y-%m-%d %T')
AND cc_appid = appid;
IF FOUND_ROWS()=0 THEN
SET tmpnumbers = 0;
END IF;
END;
IF tmpnumbers >= callnumbers THEN
SET ret =1;
LEAVE aa; -- return
END IF;
END IF;
END IF;
-- ----------------end for morining limit----
-- ----------------begin for limit in min-----
-- 查看默认的分钟限制信息，如果默认配置为0。不能表示全部禁止拨打
BEGIN
SELECT um_callnumbers INTO defaultnumbers FROM UM_ITEM_LIMIT WHERE um_appid = '9527DEFAULT' AND um_type = 1;
IF FOUND_ROWS()=0 THEN
SET defaultnumbers = NULL;
END IF;
END;
IF defaultnumbers = 0 THEN
SET defaultnumbers = NULL;
END IF;
-- 查看商户的分钟限制拨打的限制，如果为0 表示禁止拨打。
BEGIN
SELECT um_callnumbers INTO callnumbers FROM UM_ITEM_LIMIT WHERE um_appid = appid AND um_type = 1;
IF FOUND_ROWS()=0 THEN
SET callnumbers = NULL;
END IF;
END;
IF callnumbers = 0 THEN -- 0 限制拨打
SET ret = 1;
LEAVE aa; -- return
END IF;
IF callnumbers IS NULL OR callnumbers = '' THEN
SET callnumbers = defaultnumbers;
END IF;
IF callnumbers IS NOT NULL OR callnumbers<> '' THEN
BEGIN
SELECT COUNT(*) INTO tmpnumbers FROM CENTER_MES_MANAGE WHERE cc_caller = callee
AND cc_creatlogtime > STR_TO_DATE(CONCAT(trimsysdate,':00'),'%Y-%m-%d %T')
AND cc_creatlogtime < STR_TO_DATE(CONCAT(trimsysdate,':59'),'%Y-%m-%d %T')
AND cc_appid = appid;
IF FOUND_ROWS()=0 THEN
SET tmpnumbers = 0;
END IF;
END;
IF callnumbers <= tmpnumbers THEN
SET ret = 1;
LEAVE aa; -- return
END IF;
END IF;
-- ------------end for limit min--------
-- 呼叫次数限制功能 1. 优先看商户有没有单独配限制，有则使用之
-- 2. 如没有，再使用公共的限制值，如公共参数未配置，则不限制
BEGIN
SELECT um_callnumbers INTO defaultnumbers FROM UM_ITEM_LIMIT WHERE um_appid = '9527DEFAULT' AND um_type = 0;
IF FOUND_ROWS()=0 THEN
SET defaultnumbers = NULL;
END IF;
END;
IF defaultnumbers = 0 THEN
SET defaultnumbers = NULL;
END IF;
BEGIN
SELECT um_callnumbers INTO callnumbers FROM UM_ITEM_LIMIT WHERE um_appid = appid AND um_type = 0;
IF FOUND_ROWS()=0 THEN
SET callnumbers = NULL;
END IF;
END;
IF callnumbers = 0 THEN -- 0 限制拨打
SET ret = 1;
LEAVE aa; -- return
END IF;
IF callnumbers IS NULL OR callnumbers = '' THEN
SET callnumbers = defaultnumbers;
END IF;
IF callnumbers IS NOT NULL OR callnumbers <> '' THEN
BEGIN
SELECT COUNT(*) INTO tmpnumbers FROM CENTER_MES_MANAGE WHERE cc_caller = callee AND cc_creatlogtime > STR_TO_DATE(CONCAT(sysdatetimeforday,'00:00:00'),'%Y-%m-%d %T') AND cc_appid = appid;
IF FOUND_ROWS()=0 THEN
SET tmpnumbers = 0;
END IF;
END;
IF callnumbers <= tmpnumbers THEN
SET ret = 1;
LEAVE aa;
END IF;
END IF;
-- end for call numbers
END;
$$
执行的内容是一致的只是在不同的数据库
| Oracle | Mysql |
定义存储过程 | create or replace procedure | CREATE PROCEDURE |
传入传出参数 | appid in varchar2(关键字在名称和类型中间) | IN appid VARCHAR(255)(关键字在名称之前) |
参数定义 | defaultbegintime varchar2(20) := null; | DECLARE defaultbegintime VARCHAR(20) DEFAULT NULL; |
输出 | return | LEAVE |
还有什么特点就自己看吧.我也就是简单总结一下.重点在上面的对比
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
