Cursor dienen im Zusammenhang mit relationalen Datenbanken dazu, um das Impedance Mismatch auszugleichen. Das Impedance Mismatch entsteht durch den Unterschied von Programmiersprache und Datenbanksprache. Programmiersprachen arbeiten mit einzelnen Tupeln und relationale Datenbanken mit Mengen von Tupeln. Der Cursor ermöglicht es nun, die Ergebnismenge einer Datenbankabfrage in einzelnen Tupeln zu verarbeiten. Durch das Schlüsselwort FETCH sorgt dafür, dass das nächste Tupel aus der abgefragten Menge herangezogen wird. Um das zu veranschaulichen, habe ich das Beispiel aus dem [post id=2616]MySQL-Trigger Beitrag[/post] um eine Tabelle mit Produkten (products) und eine Tabelle mit Bestellungen (orders) erweitert.
Aus Zeitgründen kann ich dieses Beispiel im Moment nicht erklären. Ich werde dazu aber später ein Video machen und hoffe, dass bis dahin der Code zur Verständigung ausreicht.
Cursor in Kurzform
1 2 3 4 5 6 7 8 9 10 11 12 13 | ... BEGIN DECLARE my_var INT(10); DECLARE my_cursor CURSOR FOR -- Insert a SQL statement here OPEN my_cursor; -- Start a condition here FETCH my_cursor INTO my_var; -- Finish a condition here CLOSE my_cursor; END$ ... |
SQL-Skript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | -- Settings DELIMITER $ -- Create database DROP DATABASE IF EXISTS testdb_trigger$ CREATE DATABASE testdb_trigger DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci$ USE testdb_trigger$ -- Create tables CREATE TABLE IF NOT EXISTS users ( user_id INT(10) NOT NULL AUTO_INCREMENT ,surname VARCHAR(255) NOT NULL ,prename VARCHAR(255) NOT NULL ,PRIMARY KEY(user_id) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci$ CREATE TABLE IF NOT EXISTS users_a_m ( id INT(10) NOT NULL AUTO_INCREMENT ,user_id INT(10) NOT NULL ,surname VARCHAR(255) NOT NULL ,prename VARCHAR(255) NOT NULL ,PRIMARY KEY(id) ,FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci$ CREATE TABLE IF NOT EXISTS users_n_z ( id INT(10) NOT NULL AUTO_INCREMENT ,user_id INT(10) NOT NULL ,surname VARCHAR(255) NOT NULL ,prename VARCHAR(255) NOT NULL ,PRIMARY KEY(id) ,FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci$ CREATE TABLE IF NOT EXISTS products ( prod_id INT(10) NOT NULL AUTO_INCREMENT ,name VARCHAR(255) NOT NULL ,store VARCHAR(255) NOT NULL ,stock INT(5) NOT NULL ,PRIMARY KEY(prod_id) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci$ CREATE TABLE IF NOT EXISTS orders ( id INT(10) NOT NULL AUTO_INCREMENT ,user_id INT(10) NOT NULL ,product VARCHAR(255) NOT NULL ,amount INT(5) NOT NULL ,PRIMARY KEY(id) ,FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci$ -- Create Triggers CREATE TRIGGER split_users AFTER INSERT ON users FOR EACH ROW BEGIN IF(NEW.surname BETWEEN 'A' AND 'N') THEN INSERT INTO users_a_m VALUES (NULL, NEW.user_id, NEW.surname, NEW.prename); ELSE INSERT INTO users_n_z VALUES (NULL, NEW.user_id, NEW.surname, NEW.prename); END IF; END$ -- Trigger with Cursor CREATE TRIGGER check_amount BEFORE INSERT ON orders FOR EACH ROW BEGIN DECLARE product_id INT(10); DECLARE order_quantity INT(5); DECLARE available_quantity INT(5); -- Cursor DECLARE amount_cursor CURSOR FOR SELECT prod_id, stock FROM products WHERE name=NEW.product ORDER BY stock DESC; OPEN amount_cursor; SET order_quantity = NEW.amount; WHILE (order_quantity > 0) DO FETCH amount_cursor INTO product_id, available_quantity; IF(order_quantity > available_quantity) THEN UPDATE products SET stock = 0 WHERE prod_id = product_id; ELSE UPDATE products SET stock = stock - order_quantity WHERE prod_id = product_id; END IF; SET order_quantity = order_quantity - available_quantity; END WHILE; CLOSE amount_cursor; END$ -- Insert 10 people INSERT INTO users(user_id,surname,prename) VALUES (NULL,'Quander','Nadja')$ INSERT INTO users(user_id,surname,prename) VALUES (NULL,'Labuhn','Milan')$ INSERT INTO users(user_id,surname,prename) VALUES (NULL,'Ebisch','Alena')$ INSERT INTO users(user_id,surname,prename) VALUES (NULL,'Paffenholz','Ellen')$ INSERT INTO users(user_id,surname,prename) VALUES (NULL,'Haas','Vin')$ INSERT INTO users(user_id,surname,prename) VALUES (NULL,'Jaber','Chris')$ INSERT INTO users(user_id,surname,prename) VALUES (NULL,'Radicke','Yannik')$ INSERT INTO users(user_id,surname,prename) VALUES (NULL,'Abadre','Sean')$ INSERT INTO users(user_id,surname,prename) VALUES (NULL,'Nähr','Friedrich')$ INSERT INTO users(user_id,surname,prename) VALUES (NULL,'Waagner','Lilian')$ -- Insert some products INSERT INTO products(prod_id,name,store,stock) VALUES (NULL,'Fernseher','Berlin',250)$ INSERT INTO products(prod_id,name,store,stock) VALUES (NULL,'Fernseher','Potsdam',200)$ INSERT INTO products(prod_id,name,store,stock) VALUES (NULL,'Fernseher','Bremen',100)$ INSERT INTO products(prod_id,name,store,stock) VALUES (NULL,'Radio','Berlin',100)$ -- Insert some orders INSERT INTO orders(user_id,product,amount) VALUES (1,'Fernseher',300)$ |