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 step_details( record_id INT, step_id INT, material_type VARCHAR2(50) );
INSERT INTO step_details SELECT 1,1,'in' FROM dual UNION ALL SELECT 2,1,'in' FROM dual UNION ALL SELECT 3,1,'out' FROM dual UNION ALL SELECT 4,2,'in' FROM dual UNION ALL SELECT 5,2,'out' FROM dual UNION ALL SELECT 6,2,'out' FROM dual;
6 rows affected
CREATE TABLE actions_details( record_id INT, action_id INT, material_type VARCHAR2(50) );
INSERT INTO actions_details SELECT 1,11,'in' FROM dual UNION ALL SELECT 2,11,'out' FROM dual UNION ALL SELECT 3,12,'in' FROM dual UNION ALL SELECT 4,12,'out' FROM dual;
4 rows affected
CREATE OR REPLACE FUNCTION get_input_count(p_parent_id INT, p_from VARCHAR2) RETURN INT IS l_count INT; crs SYS_REFCURSOR; v_sql VARCHAR2(32767); v_from VARCHAR2(32); v_col VARCHAR2(99); BEGIN SELECT DECODE(p_from,'A','actions_details','S','step_details'), DECODE(p_from,'A','action_id','S','step_id') INTO v_from, v_col FROM dual; v_sql := 'SELECT COUNT(*) FROM '||v_from||' WHERE material_type = ''in'' AND '||v_col||' = :prt_id'; OPEN crs FOR v_sql USING p_parent_id; LOOP FETCH crs INTO l_count; EXIT WHEN crs%NOTFOUND; END LOOP; CLOSE crs; RETURN l_count; END; /
DECLARE v_count INT; BEGIN v_count := get_input_count(1,'S'); DBMS_OUTPUT.PUT_LINE( v_count ); END; /
1 rows affected dbms_output: 2