-- 循环累加
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
标签:return,list,介绍,语法,varchar2,split,end,loop
From: https://www.cnblogs.com/xiaotao-tpc/p/grammar-introduction-z27prbg.html