17,377
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE FUNCTION FFCS_SMS_CHECK_FUC(v_stype IN VARCHAR,v_phone IN VARCHAR,v_ip IN VARCHAR)
RETURN VARCHAR IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_times INTEGER;
v_minute INTEGER;
v_i INTEGER; --查到的次数
v_str VARCHAR(64);
v_sid NUMBER;
v_result VARCHAR(64);
v_state VARCHAR(2);
BEGIN
v_times := 8; --8次
v_minute := 5; --5分钟
select SEQ_SMS_CHECK.nextval into v_sid FROM DUAL; --ID
SELECT (SELECT COUNT(*) FROM ffcs_sms_check a
WHERE a.phone = v_phone AND a.ip = v_ip AND a.stype = v_stype
AND a.sendtime > SYSDATE -(v_minute/1440)) INTO v_i FROM dual;
v_str := '您在'||v_minute||'分钟内发送了'||v_i||'次短信验证码';
IF(v_i>=v_times) THEN
BEGIN
v_result := '[失败]:'||v_str;
v_state := '1';
INSERT INTO ffcs_sms_check(sid,stype,phone,sendtime,state,ip,remark)
VALUES(v_sid,v_stype,v_phone,Sysdate,v_state,v_ip,v_result);
END;
ELSE
BEGIN
v_result := '[成功]:'||v_str;
v_state := '0';
INSERT INTO ffcs_sms_check(sid,stype,phone,sendtime,state,ip,remark)
VALUES(v_sid,v_stype,v_phone,Sysdate,v_state,v_ip,v_result);
END;
END IF;
RETURN v_result;
END FFCS_SMS_CHECK_FUC;
CREATE OR REPLACE FUNCTION FFCS_SMS_CHECK_FUC(v_stype IN VARCHAR,v_phone IN VARCHAR,v_ip IN VARCHAR)
RETURN VARCHAR IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_times INTEGER;
v_minute INTEGER;
v_i INTEGER; --查到的次数
v_str VARCHAR(64);
v_sid NUMBER;
v_result VARCHAR(64);
v_state VARCHAR(2);
BEGIN
v_times := 8; --8次
v_minute := 5; --5分钟
select SEQ_SMS_CHECK.nextval into v_sid FROM DUAL; --ID
SELECT (SELECT COUNT(*) FROM ffcs_sms_check a
WHERE a.phone = v_phone AND a.ip = v_ip AND a.stype = v_stype
AND a.sendtime > SYSDATE -(v_minute/1440)) INTO v_i FROM dual;
v_str := '您在'||v_minute||'分钟内发送了'||v_i||'次短信验证码';
IF(v_i>=v_times) THEN
BEGIN
v_result := '[失败]:'||v_str;
v_state := '1';
INSERT INTO ffcs_sms_check(sid,stype,phone,sendtime,state,ip,remark)
VALUES(v_sid,v_stype,v_phone,Sysdate,v_state,v_ip,v_result);
commit;
END;
ELSE
BEGIN
v_result := '[成功]:'||v_str;
v_state := '0';
INSERT INTO ffcs_sms_check(sid,stype,phone,sendtime,state,ip,remark)
VALUES(v_sid,v_stype,v_phone,Sysdate,v_state,v_ip,v_result);
commit;
END;
END IF;
RETURN v_result;
END FFCS_SMS_CHECK_FUC;
SQL> select FFCS_SMS_CHECK_FUC('1','2','3') from dual;
FFCS_SMS_CHECK_FUC('1','2','3'
--------------------------------------------------------------------------------
[成功]:您在5分钟内发送了0次短信验证码
SQL>