我在Oracle中下了一个function,操作时先按一个条件检查,然后根据不同的检查结果新增进库里一条数据,函数如下: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,re)
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,re)
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;结果我执行的时候老是给我回滚了,我看网上说查询中不能有DML语句,按网上的加了语句PRAGMA AUTONOMOUS_TRANSACTION;
但是还是不能执行SELECT FFCS_SMS_CHECK_FUC('登录','18911112222','192.168.1.1') FROM dual;
我的是哪里出错了?请大侠指点!不胜感激!
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,re)
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,re)
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;结果我执行的时候老是给我回滚了,我看网上说查询中不能有DML语句,按网上的加了语句PRAGMA AUTONOMOUS_TRANSACTION;
但是还是不能执行SELECT FFCS_SMS_CHECK_FUC('登录','18911112222','192.168.1.1') FROM dual;
我的是哪里出错了?请大侠指点!不胜感激!
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,re)
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,re)
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> --------增加了两处COMMIT;