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