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.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
run
markdown
donate
feedback
about
By using db<>fiddle, you agree to license everything you submit by
Creative Commons CC0
.
create table products( prod_id int, prod_name text); insert into products values (1,'tv'), (2,'radio'), (3,'typewriter');
create table shipments( prod_id int, ship_id int, ship_date date); insert into shipments values (1,22,'2022-12-12'), (1,33,'2021-12-12'), (1,52,'2021-01-10'), (2,31,'2021-04-10'), (2,36,'2021-09-10');
/* DELIMITER $$ */ CREATE PROCEDURE check_shipments() BEGIN DECLARE no_more_products int DEFAULT 0; DECLARE p_id int DEFAULT 0; DECLARE ship_times int DEFAULT 0; DECLARE cur_shipments CURSOR FOR SELECT prod_id FROM shipments; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1; CREATE table IF NOT EXISTS products_Info( prod_id int primary key, prod_shipments int); OPEN cur_shipments; FETCH cur_shipments INTO p_id; REPEAT SELECT count(prod_id) INTO ship_times FROM shipments where prod_id = p_id; IF ship_times > 0 THEN INSERT INTO products_Info VALUES(p_id, 1) ON DUPLICATE KEY UPDATE prod_shipments = prod_shipments + 1; END IF; FETCH cur_shipments INTO p_id; UNTIL no_more_products = 1 END REPEAT; CLOSE cur_shipments; SELECT * FROM products_Info; drop table products_Info; END ;/*$$ DELIMITER ;*/
call check_shipments
prod_id
prod_shipments
1
3
2
2