1 reply to this topic

[the_code_charmer]

Good day to all,

I have no idea how to use views and blocks nor how to implement them in my database. Can someone explain to me how and when i should use them in the plainest English possible. (Pictures and pretty stuff is also welcome to help me understand.(joke)) I have attached the mysql code in a text file; and the names and fields of the tables are in spanish but the words are pretty close to english but i can answer question about any of the fields or tables
thank you

Attached Files

•  restaurante_cc..txt   10.25K   40 downloads

[the_code_charmer]

I guess that i should have mentioned that this is supposed to be a database that backs a restaurant POS system.
so here is my attempt at making cursors useful in my data base what it does is take the values from "totals" from the table "cuentas"(bills) and creates another table called "ventas_hoy" (sales_today) and lists each sale with the number of the bill "idcuenta" (idbill) any and all feedback is welcome. i just tried to run it in workbench and it gives me the error code :ERROR 1324: Undefined CURSOR: idcuenta

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ventas_esta_noche`()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;
   DECLARE t DECIMAL(10,0);

   -- Declare the cursor
   DECLARE totales CURSOR
   FOR
   SELECT idcuenta FROM cuenta;
   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

   -- Create a table to store the results
   CREATE TABLE IF NOT EXISTS ventas_hoy
      (idcuenta INT, total DECIMAL(10,0));

   -- Open the cursor
   OPEN totales;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH idcuenta INTO o;

      -- Get the total for this bill
      CALL ventas_hoy(o, 1, t);

      -- Insert order and total into ventas_hoy
      INSERT INTO ventas_hoy(idcuenta, total)
      VALUES(o, t);

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE totales;

END$$