add batch
remove batch
split batch
comment selection
show hidden batches
hide batch
highlight batch
db<>fiddle
Db2
Firebird
MariaDB
MySQL
Node.js
Oracle
Postgres
SQLite
SQL Server
TimescaleDB
YugabyteDB
Developer-C 11.1
3.0
4.0
10.2
10.3
10.4
10.5
10.6
10.7
10.8
10.9
5.5
5.6
5.7
8.0
18
11g Release 2
18c
21c
23c
8.4
9.3
9.4
9.5
9.6
10
11
12
13
14
15
16
3.8
3.16
3.27
3.39
2012
2014
2016
2017
2017 (Linux)
2019
2019 (Linux)
2022
2.11
2.14
2.6
2.8
2.18
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
Sakila
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
run
markdown
donate
feedback
about
By using db<>fiddle, you agree to license everything you submit by
Creative Commons CC0
.
create table sbceybudget_financial_year( fin_yr int, SCHOOL_YEAR_WEEKS int )
create or replace package pkg_spFIN as type spFIN_RowType is record( FIN_YR sbceybudget_financial_year.FIN_YR%type, SCHOOL_YEAR_WEEKS sbceybudget_financial_year.SCHOOL_YEAR_WEEKS%type ); type spFIN_CurType IS REF CURSOR RETURN spFIN_RowType; type spFIN_tab is table of spFIN_RowType; procedure spFINTEST (s1 OUT SYS_REFCURSOR); procedure spFinTestInsert; end pkg_spFIN; /
create or replace package body pkg_spFIN as function get_cursor(n int) return spFIN_CurType is c spFIN_CurType; begin open c for SELECT t.FIN_YR + 1 AS FIN_YR, t.SCHOOL_YEAR_WEEKS FROM sbceybudget_financial_year t WHERE t.fin_yr = n; return c; end; procedure spFINTEST (s1 OUT SYS_REFCURSOR) is begin s1:=get_cursor(2021); end spFINTEST; procedure spFinTestInsert is cur spFIN_CurType; tab spFIN_tab; begin pkg_spFIN.spFINTEST(cur); loop fetch cur bulk collect into tab limit 100; exit when tab.count()=0; for i in 1..tab.count loop dbms_output.put_line(tab(i).FIN_YR); dbms_output.put_line(tab(i).SCHOOL_YEAR_WEEKS); -- or insert: -- insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS) -- values(tab(i).FIN_YR, SCHOOL_YEAR_WEEKS) -- you can change it to FORALL insert end loop; end loop; end spFinTestInsert; end pkg_spFIN; /
begin insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS) values(2019,19); insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS) values(2020,20); insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS) values(2021,21); commit; end; /
1 rows affected
begin pkg_spFIN.spFinTestInsert(); end; /
1 rows affected