Mysql与Oracle存储过程语法对比

先贴出两个不同数据库的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;

$$

执行的内容是一致的只是在不同的数据库

 OracleMysql
定义存储过程create or replace procedureCREATE PROCEDURE
传入传出参数appid in varchar2(关键字在名称和类型中间)IN appid VARCHAR(255)(关键字在名称之前)
参数定义defaultbegintime varchar2(20)  := null;DECLARE defaultbegintime VARCHAR(20)  DEFAULT NULL;
输出returnLEAVE

还有什么特点就自己看吧.我也就是简单总结一下.重点在上面的对比

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值