u "drugom" jeziku je bilo da pozoves tvoj udf koji ce onda trigerovati neki tvoj jezik koji ce odraditi sta treba, drugi nacin je da imas tvoj app koji zoves iz krona svakih sat vremena ili jednom dnevno ili ... i koji odradi sta treba ..
... no to je sve moguce direkt iz stored procedure / trigera .. ne znam gde si zaglavio, evo ti primer kako to radi
Code:
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.10 sec)
mysql> drop table if exists t2;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t3;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t4;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t5;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE t1 (a varchar(500) );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES
-> ("CREATE TABLE t2 (x int, y int) engine=innodb"),
-> ("CREATE TABLE t3 (x int, y int) engine=myisam"),
-> ("CREATE TABLE t4 (x int, y int) engine=myisam"),
-> ("CREATE TABLE t5 (x int, y int) engine=innodb");
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> DROP PROCEDURE IF EXISTS simpleproc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE simpleproc ()
-> BEGIN
-> DECLARE var1 VARCHAR(500);
-> DECLARE gotovo BOOLEAN;
-> DECLARE cur1 CURSOR FOR SELECT a FROM t1;
->
-> DECLARE CONTINUE HANDLER FOR NOT FOUND
-> SET gotovo = TRUE;
->
-> SET gotovo = FALSE;
-> OPEN cur1;
->
-> glavna_petlja: LOOP
->
-> FETCH cur1 INTO var1;
->
-> IF gotovo THEN
-> CLOSE cur1;
-> LEAVE glavna_petlja;
-> END IF;
->
-> SET @temp = var1;
-> PREPARE stmt1 FROM @temp;
-> EXECUTE stmt1;
-> DEALLOCATE PREPARE stmt1;
->
-> END LOOP glavna_petlja;
->
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> call simpleproc();
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
| t3 |
| t4 |
| t5 |
+----------------+
5 rows in set (0.00 sec)
mysql>
Sve sto ti ovde treba da uradis je da, posto nemas CREATE ovo ono u tabeli sa podacima nego negi XML, napravis svoju funkciju koja ce od XML-a napraviti CREATE i u ovoj skripti samo zamenis
SET @temp = var1;
sa
SET @temp = tvojudf(var1);
tvom udf-u se prosledi taj xml string i on vrati nazad create table ovo ono ...
MySQL i dinamicko kreiranje tabela.