语法介绍

-- 循环累加
declare
  var_sum number := 0;
begin
  for i in 35001 .. 39999 loop
    var_sum := var_sum + i;
    update NewGoods set GoodsRef=CONVERT(GoodsRef, 'UTF8','ZHS16GBK')
    where billno = i and GoodsRef is not null;
    commit;
  end loop;
  sys.dbms_output.put_line(var_sum); -- 5050
end;

declare
   errorCode number; --异常编码  
   errorMsg varchar2(32767); --异常信息 
   out_return varchar2(32767); 
   flag varchar2(10); 
begin
    for i in 31001 .. 32000 loop
    begin
    update NewGoods set GoodsRef=CONVERT(GoodsRef, 'UTF8','ZHS16GBK') where billno = i;
    commit;
     EXCEPTION
     when others then
         errorCode := SQLCODE;  
          errorMsg := SUBSTR(SQLERRM, 1, 200);   
              flag := 'false';  
        out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg||i;
        dbms_output.put_line(out_return); 
      null;
    END;
   END LOOP;
end;

----创建虚拟表
create or replace type result_split_list as table of varchar2(100);


create or replace function split_strs(strs varchar2, type_split varchar2)
  return result_split_list
  pipelined is
  index_num  pls_integer;
  str_list varchar2(100) := strs;
begin
  loop
    index_num := instr(str_list, type_split);
    if index_num > 0 then
      pipe row(substr(str_list, 1, index_num - 1));
      str_list := substr(str_list, index_num + length(type_split));
    else
      pipe row(str_list);
      exit;
    end if;
  end loop;
  return;
end split_strs;

SELECT * FROM TABLE(SPLIT('2001|1|2000|3000', '|'));


create or replace noneditionable function GETVALUE(params varchar2, ro number)
  return varchar2
as
  tmpStr varchar2(2000);
begin
  
  select to_char(a.column_value) into tmpStr
  from (
  SELECT *  FROM TABLE(split_strs(params, '|')) where rownum = ro) a;

  return tmpStr;
exception
  when others then
    return '0';
end;

select getvalue('2021|2|3|4|5',1) from dual


begin
  for rowInfo in (select rownum,a.column_value from ( SELECT *  FROM TABLE(split_strs('2023,2024',','))) a) loop
    select getValue(rowInfo.Column_Value,1) from dual
  end loop;
end;

----oracle 定时任务
TRUNC(sysdate,'mi') + 1/ (24*60)【间隔一分钟执行】
TRUNC(sysdate+ 1)  +1/ (24) 【每天凌晨一点执行】

----创建序列
CREATE SEQUENCE SHOPPING_ID INCREMENT BY 1 START WITH 1 NOMAXvalue NOCYCLE NOCACHE;
SELECT SHOPPING_ID.NEXTVAL FROM DUAL