创建函数

MySQL 创建函数

MySQL

BEGIN
declare current INTEGER;
set current = (select t.seqno from t_par_sys_seq t where t.seqname = seqname);
update t_par_sys_seq t set t.seqno = t.seqno + 1 where t.seqname = seqname;
set current = current + 1;
return current;
END

Oracle

create or replace function nextseqno(myseqname in varchar2) return number is
current number;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
select t.seqno into current from t_par_sys_seq t where t.seqname = myseqname;
update t_par_sys_seq t set t.seqno = t.seqno + 1 where t.seqname = myseqname;
current := current + 1;
commit;
return current;
end;

警告：myseqname 千万不能使用表字段名

create or replace function function_t_param_macposinfo(unitidvar in VARCHAR2, posbiztypevar in VARCHAR2) return t_param_macposinfo_table is
  t_param_macposinfo_result t_param_macposinfo_table :=  t_param_macposinfo_table();
  i NUMBER := 0;--定义变量的方式有点特别，不需要declare关键字
  j NUMBER := 0;
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  SELECT t.posseq INTO j FROM t_param_macposinfo t where t.unitid = unitidvar AND t.posbiztype = posbiztypevar;
  SELECT t_param_macposinfo_object(posid, macid, unitid, netid, postmkstatus, postmk, checkvalue, printertype, printername, cardreadertype, cardreaderdllname, cardreadporttype, lastcheckstatus, lastchecktime, lastcheckuserid, lastcheckbatch, posseq, printcountperbiz, passwdkeypadcom, mackey, pinkey, pinkey_checkvalue, posbiztype, posuse, status) BULK COLLECT into t_param_macposinfo_result from t_param_macposinfo t where t.unitid = unitidvar AND t.posbiztype = posbiztypevar;
  IF j + 1 >= 999999 THEN
    i := 0;
  ELSE
   i := j + 1;
  END IF;
  UPDATE t_param_macposinfo t SET t.posseq = i WHERE t.unitid = unitidvar AND t.posbiztype = posbiztypevar;
  COMMIT;
  RETURN t_param_macposinfo_result;
end function_t_param_macposinfo;

--执行
select * from table(function_t_param_macposinfo('898210058120063','00'));