oracle 存储过程

1、使用循环

create or replace type msg_array as table of number;

create or replace procedure modifyage(m_array in msg_array)
as

begin

for i in 1..m_array.count loop

update users set age=age+1 where id=m_array(i);

end loop;

commit;

exception

when others then

rollback;

end modifyage;

2、定义数组，并在java代码中调用

https://www.cnblogs.com/iyoume2008/p/6139925.html

3、oracle 存储过程当中嵌套函数定义

https://blog.csdn.net/weihongrao/article/details/10509699

---------------------------------------------------------------------------

create or replace procedure checks(firstNum in number)
is
secondNum number:=3;
str_arr varchar2(32676):='3,1,2';
function checkScore return number is
secondNum number:=-100;
begin
if checks.firstNum<=50
then
return secondNum;
else
return checks.secondNum;
end if;
end;

function returnString return varchar2 is
str_result varchar2(32676):='select * from dual';
begin
for i in 1..secondNum loop
if(REGEXP_SUBSTR(str_arr,'[^,]+',1,i)=1) then
str_result :=str_result || ' union all select 1 from dual';
elsif (REGEXP_SUBSTR(str_arr,'[^,]+',1,i)=2) then
str_result :=str_result || ' union all select 2 from dual';
elsif (REGEXP_SUBSTR(str_arr,'[^,]+',1,i)=3) then
str_result :=str_result || ' union all select 3 from dual';
end if;
end loop;
return str_result;
end;

procedure test is
begin
--dbms_output.put_line('this is defined in declare');
for i in 1..secondNum loop
dbms_output.put_line('this is defined in declare');
end loop;
end test;
begin
--dbms_output.put_line(checkScore);
--test;
dbms_output.put_line(returnString);
end checks;