494KyZD2

1DELIMITER //
2DROP PROCEDURE IF EXISTS P_TOTAL_PRICES//
3
4CREATE PROCEDURE P_TOTAL_PRICES (OUT n_total_price DOUBLE)
5BEGIN
6	DECLARE cursor_PRICE DOUBLE;
7    
8	DECLARE done INT DEFAULT FALSE;
9    
10	DECLARE cursor_i CURSOR FOR
11		SELECT PRICE FROM product;
12
13	DECLARE CONTINUE HANDLER
14		FOR NOT FOUND
15        SET done = TRUE;
16    
17	SET n_total_price = 0.00;
18    
19    OPEN cursor_i;
20    
21	total_loop: LOOP
22        FETCH cursor_i INTO cursor_PRICE;
23        
24		IF done
25			THEN LEAVE total_Loop;
26		END IF;
27        
28		SET n_total_price = n_total_price + cursor_PRICE;
29        
30    END LOOP total_loop;
31    
32    CLOSE cursor_i;
33END//
34
35DELIMITER ;
36
37/*
38Stored Procedures 2
39*/
40DELIMITER //
41DROP PROCEDURE IF EXISTS P_DYN_TABLE//
42
43CREATE PROCEDURE P_DYN_TABLE (
44	IN n_source_table VARCHAR (128),
45    IN n_table_name VARCHAR (128)
46)
47BEGIN
48	DECLARE l_create_statement VARCHAR (1000);
49	DECLARE l_insert_statement VARCHAR (1000);
50    
51	SET @l_create_statement = CONCAT(
52		'CREATE TABLE IF NOT EXISTS ', n_table_name, ' LIKE ', n_source_table
53	);
54    SET @l_insert_statement = CONCAT(
55        'INSERT INTO ', n_table_name, ' SELECT * FROM ', n_source_table
56    );
57    
58    PREPARE create_statement FROM @l_create_statement;
59    EXECUTE create_statement;
60    
61    PREPARE insert_statement FROM @l_insert_statement;
62    EXECUTE insert_statement;
63END//
64
65DELIMITER ;