|
Editar
|
Copiar
|
Borrar
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'rewards_report' AND `proc`.`type` = 'PROCEDURE'
|
sakila |
rewards_report |
PROCEDURE |
rewards_report |
SQL |
READS_SQL_DATA |
NO |
DEFINER |
IN min_monthly_purchases TINYINT UNSIGNED
, IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
, OUT count_rewardees INT
|
|
proc: BEGIN
DECLARE last_month_start DATE;
DECLARE last_month_end DATE;
IF min_monthly_purchases = 0 THEN
SELECT 'Minimum monthly purchases parameter must be > 0';
LEAVE proc;
END IF;
IF min_dollar_amount_purchased = 0.00 THEN
SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
LEAVE proc;
END IF;
SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
SET last_month_end = LAST_DAY(last_month_start);
CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO tmpCustomer (customer_id)
SELECT p.customer_id
FROM payment AS p
WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
GROUP BY customer_id
HAVING SUM(p.amount) > min_dollar_amount_purchased
AND COUNT(customer_id) > min_monthly_purchases;
SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
SELECT c.*
FROM tmpCustomer AS t
INNER JOIN customer AS c ON t.customer_id = c.customer_id;
DROP TABLE tmpCustomer;
END
|
root@localhost |
2013-12-18 15:04:09 |
2013-12-18 15:04:09 |
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... |
Provides a customizable report on best customers |
utf8 |
utf8_general_ci |
latin1_swedish_ci |
proc: BEGIN
DECLARE last_month_start DATE;
DECLARE last_month_end DATE;
IF min_monthly_purchases = 0 THEN
SELECT 'Minimum monthly purchases parameter must be > 0';
LEAVE proc;
END IF;
IF min_dollar_amount_purchased = 0.00 THEN
SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
LEAVE proc;
END IF;
SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
SET last_month_end = LAST_DAY(last_month_start);
CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO tmpCustomer (customer_id)
SELECT p.customer_id
FROM payment AS p
WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
GROUP BY customer_id
HAVING SUM(p.amount) > min_dollar_amount_purchased
AND COUNT(customer_id) > min_monthly_purchases;
SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
SELECT c.*
FROM tmpCustomer AS t
INNER JOIN customer AS c ON t.customer_id = c.customer_id;
DROP TABLE tmpCustomer;
END
|
|
Editar
|
Copiar
|
Borrar
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'get_customer_balance' AND `proc`.`type` = 'FUNCTION'
|
sakila |
get_customer_balance |
FUNCTION |
get_customer_balance |
SQL |
READS_SQL_DATA |
YES |
DEFINER |
p_customer_id INT, p_effective_date DATETIME
|
decimal(5,2)
|
BEGIN
DECLARE v_rentfees DECIMAL(5,2);
DECLARE v_overfees INTEGER;
DECLARE v_payments DECIMAL(5,2);
SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
FROM film, inventory, rental
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
FROM rental, inventory, film
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
FROM payment
WHERE payment.payment_date <= p_effective_date
AND payment.customer_id = p_customer_id;
RETURN v_rentfees + v_overfees - v_payments;
END
|
root@localhost |
2013-12-18 15:04:09 |
2013-12-18 15:04:09 |
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... |
|
utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DECLARE v_rentfees DECIMAL(5,2);
DECLARE v_overfees INTEGER;
DECLARE v_payments DECIMAL(5,2);
SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
FROM film, inventory, rental
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
FROM rental, inventory, film
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
FROM payment
WHERE payment.payment_date <= p_effective_date
AND payment.customer_id = p_customer_id;
RETURN v_rentfees + v_overfees - v_payments;
END
|
|
Editar
|
Copiar
|
Borrar
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'film_in_stock' AND `proc`.`type` = 'PROCEDURE'
|
sakila |
film_in_stock |
PROCEDURE |
film_in_stock |
SQL |
READS_SQL_DATA |
NO |
DEFINER |
IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT
|
|
BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND inventory_in_stock(inventory_id);
SELECT FOUND_ROWS() INTO p_film_count;
END
|
root@localhost |
2013-12-18 15:04:09 |
2013-12-18 15:04:09 |
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... |
|
utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND inventory_in_stock(inventory_id);
SELECT FOUND_ROWS() INTO p_film_count;
END
|
|
Editar
|
Copiar
|
Borrar
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'film_not_in_stock' AND `proc`.`type` = 'PROCEDURE'
|
sakila |
film_not_in_stock |
PROCEDURE |
film_not_in_stock |
SQL |
READS_SQL_DATA |
NO |
DEFINER |
IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT
|
|
BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND NOT inventory_in_stock(inventory_id);
SELECT FOUND_ROWS() INTO p_film_count;
END
|
root@localhost |
2013-12-18 15:04:09 |
2013-12-18 15:04:09 |
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... |
|
utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND NOT inventory_in_stock(inventory_id);
SELECT FOUND_ROWS() INTO p_film_count;
END
|
|
Editar
|
Copiar
|
Borrar
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'inventory_held_by_customer' AND `proc`.`type` = 'FUNCTION'
|
sakila |
inventory_held_by_customer |
FUNCTION |
inventory_held_by_customer |
SQL |
READS_SQL_DATA |
NO |
DEFINER |
p_inventory_id INT
|
int(11)
|
BEGIN
DECLARE v_customer_id INT;
DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
SELECT customer_id INTO v_customer_id
FROM rental
WHERE return_date IS NULL
AND inventory_id = p_inventory_id;
RETURN v_customer_id;
END
|
root@localhost |
2013-12-18 15:04:09 |
2013-12-18 15:04:09 |
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... |
|
utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DECLARE v_customer_id INT;
DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
SELECT customer_id INTO v_customer_id
FROM rental
WHERE return_date IS NULL
AND inventory_id = p_inventory_id;
RETURN v_customer_id;
END
|
|
Editar
|
Copiar
|
Borrar
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'inventory_in_stock' AND `proc`.`type` = 'FUNCTION'
|
sakila |
inventory_in_stock |
FUNCTION |
inventory_in_stock |
SQL |
READS_SQL_DATA |
NO |
DEFINER |
p_inventory_id INT
|
tinyint(1)
|
BEGIN
DECLARE v_rentals INT;
DECLARE v_out INT;
SELECT COUNT(*) INTO v_rentals
FROM rental
WHERE inventory_id = p_inventory_id;
IF v_rentals = 0 THEN
RETURN TRUE;
END IF;
SELECT COUNT(rental_id) INTO v_out
FROM inventory LEFT JOIN rental USING(inventory_id)
WHERE inventory.inventory_id = p_inventory_id
AND rental.return_date IS NULL;
IF v_out > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END
|
root@localhost |
2013-12-18 15:04:09 |
2013-12-18 15:04:09 |
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... |
|
utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DECLARE v_rentals INT;
DECLARE v_out INT;
SELECT COUNT(*) INTO v_rentals
FROM rental
WHERE inventory_id = p_inventory_id;
IF v_rentals = 0 THEN
RETURN TRUE;
END IF;
SELECT COUNT(rental_id) INTO v_out
FROM inventory LEFT JOIN rental USING(inventory_id)
WHERE inventory.inventory_id = p_inventory_id
AND rental.return_date IS NULL;
IF v_out > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END
|
|
Editar
|
Copiar
|
Borrar
DELETE FROM proc WHERE `proc`.`db` = 'mysql' AND `proc`.`name` = 'DropGeometryColumn' AND `proc`.`type` = 'PROCEDURE'
|
mysql |
DropGeometryColumn |
PROCEDURE |
DropGeometryColumn |
SQL |
CONTAINS_SQL |
NO |
INVOKER |
catalog varchar(64), t_schema varchar(64),
t_name varchar(64), geometry_column varchar(64)
|
|
begin
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
|
root@localhost |
2019-10-30 15:45:43 |
2019-10-30 15:45:43 |
|
|
latin1 |
latin1_swedish_ci |
latin1_swedish_ci |
begin
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
|
|
Editar
|
Copiar
|
Borrar
DELETE FROM proc WHERE `proc`.`db` = 'mysql' AND `proc`.`name` = 'AddGeometryColumn' AND `proc`.`type` = 'PROCEDURE'
|
mysql |
AddGeometryColumn |
PROCEDURE |
AddGeometryColumn |
SQL |
CONTAINS_SQL |
NO |
INVOKER |
catalog varchar(64), t_schema varchar(64),
t_name varchar(64), geometry_column varchar(64), t_srid int
|
|
begin
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
|
root@localhost |
2019-10-30 15:45:43 |
2019-10-30 15:45:43 |
|
|
latin1 |
latin1_swedish_ci |
latin1_swedish_ci |
begin
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
|