By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table lager(stueckkap int, lnr int)
insert into lager values (1, 10)
1 rows affected
insert into lager values (2, 9)
1 rows affected
create table lagerbuchung(lnr int, stueck int)
insert into lagerbuchung values (10, -5)
1 rows affected
insert into lagerbuchung values (10, 100)
1 rows affected
create type test_type is object (lagernummer2 varchar2(500), summe2 varchar2(500));
create type test_tab is table of test_type;
create or replace function test_cursor (lagernummer in int)
return test_tab
is
v_test_tab test_tab := test_tab();
n integer := 0;
summe number := 0;
kapazitätsgränze number;
begin
SELECT l.stueckkap
INTO kapazitätsgränze
FROM lager l
WHERE lagernummer = l.lnr;
FOR v_rec IN (SELECT *
FROM lagerbuchung l2
WHERE l2.lnr = lagernummer)
LOOP
summe := summe + v_rec.stueck;
IF (summe > kapazitätsgränze) then
v_test_tab.extend;
n := n + 1;
v_test_tab(n) := test_type('Überfüllter Lagerstand' || ' ' || lagernummer, summe);
else
v_test_tab.extend;
n := n + 1;
v_test_tab(n) := test_type('Negativer Lagerstand' || ' ' || lagernummer, '<html><font size="5" color="red">' || summe);
end if;
end loop;
return v_test_tab;
end;
/
select * from table(test_cursor(10));
| LAGERNUMMER2 | SUMME2 |
|---|---|
| Negativer Lagerstand 10 | <html><font size="5" color="red">-5 |
| Überfüllter Lagerstand 10 | 95 |