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,‘[^,]+‘,i)=1) then
str_result :=str_result || ‘ union all select 1 from dual‘;
elsif (REGEXP_SUBSTR(str_arr,i)=2) then
str_result :=str_result || ‘ union all select 2 from dual‘;
elsif (REGEXP_SUBSTR(str_arr,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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!