How to delete all MySQL tables beginning with a certain prefix?

There's no single statement to do that.

The simplest approach is to generate a set of statements, and execute them individually.

A simple query can generate the statements for you:

 SELECT CONCAT('DROP TABLE `',t.table_schema,'`.`',t.table_name,'`;') AS stmt
   FROM information_schema.tables t
  WHERE t.table_schema = 'mydatabase'
    AND t.table_name LIKE 'aggregate\_temp%' ESCAPE '\\'
  ORDER BY t.table_name

That just returns a rowset, but the rows conveniently contain the exact SQL statements you need to execute. (Note that information_schema is a builtin database that contains metadata. You'd just need to replace mydatabase with the name of the database you want to drop tables from.

Save the resultset from this query as a plain text file, remove any heading line, and voila, you've got a script you can execute in your SQL client.

There's no need for an elaborate stored procedure.

A little googling found this:

SELECT 'DROP TABLE "' + TABLE_NAME + '"' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'prefix%'

This should generate a script.

Source: Drop all tables whose names begin with a certain string

From memory you have to use prepared statements, for example: plenty of samples on stack exchange

I would recommend this example:

SQL: deleting tables with prefix

The SQL from above, this one includes the specific databasename - it builds it for you

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
AS statement FROM information_schema.tables 
WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%';

Here is a different way to do it:

MySQL bulk drop table where table like?