Here a cut down example (not very realistic, but easy to follow):
create table mytab (myval number);
create or replace procedure tuwas (
varA in number,
varB in number,
varC OUT number)
is
begin
varC := varA;
insert into mytab values (varA);
if varB = 0 then
commit;
else
rollback;
end if;
end;
/
The Idea behind the code is, to get varA and varB as input parameters, do some calculations (missing here), insert the result into a table and return the calculated value for next steps.
There might be reasons to rollback the DML within the procedure, which is not seen as an error.
And here the result of the procedure:
set serverout on
declare
vA number;
vB number;
vC number;
begin
vA := 1;
vB := 0; -- commit;
tuwas(vA, vB, vC);
dbms_output.put_line('vC: ' ||vC);
vA := 2;
vB := 1; -- rollback;
tuwas(vA, vB, vC);
dbms_output.put_line('vC: ' ||vC);
END;
/
vC: 1
vC: 2
select * from mytab;
MYVAL
----------
1
What's dangerous here? Even with
the return value
vA := 2;
vB := 1;
is given. But t's not visible in the table, as the procedure did a rollback.
vC: 2
I learned from this example never to populate return-values until you are sure it's really stored (if this is a requirement, of course).
Keine Kommentare:
Kommentar veröffentlichen