MySQL 5.7 Create VIEW or FUNCTION or Procedure_mssql

1. View

A.

CREATE algorithm = UNDEFINED definer = ' root ' @ ' localhost ' SQL security invoker VIEW ' Sakila ' ' actor_info ' as SELECT ' a '. ' actor_id ' as ' actor_id ', ' a '. ' First_Name ' as ' first_name ', ' a '. ' Last_Name ' as ' last_name ', Group_concat (DISTINCT CONCAT ( ' C '. ' Name ', ': ', (SELECT group_concat (' F '. ' title ' ORDER by ' F '. ' title ' ASC SEPARATOR ', ') from (' Sakila '. ' film ' F ' JOI  N ' Sakila ' film_category ' FC ' On ((' F '. ' film_id ' = ' fc '. ' film_id ')) JOIN ' Sakila '. ' film_actor ' ' fa ' (' f '. ' film_id ') = ' fa '. ' film_id ')) WHERE ((' FC '. ' category_id ' = ' C '. ' category_id ') and (' FA '. ' actor_id ' = ' a '. ' actor_id '))) Order BY ' C '. ' Name ' ASC SEPARATOR '; ' As ' Film_info ' from ((' Sakila '. ' Actor ' "a ' left JOIN ' Sakila '. ' film_actor ' ' fa ' (' a '. ' actor_id ' = ' fa '. ' actor_id ') ) left join ' Sakila '. ' Film_category ', ' FC ' on ((' FA '. ' film_id ' = ' fc '. ' film_id ')) left join ' Sakila '. ' Category ' ' C ' ( ' FC '. ' category_id ' = ' C '. ' category_id ')) GROUP by ' a '. ' actor_id ', ' a '. ' First_Name ', ' a '. ' Last_Name '

B.

CREATE
algorithm = UNDEFINED
definer = ' root ' @ ' localhost '
SQL security definer
VIEW ' Sakila '. ' Staff_ List ' as
SELECT
'. ' staff_id ' as ' id ',
CONCAT (' s '. ' first_name ',
_utf8 ',
' s '. ' last_name ') as ' Name ', '
a '. ' Address ' as ' address ',
' a '. ' Postal_Code ' as ' zip code ',
' a '. ' Phone ' as ' phone ',
' Sakila '. ' City '-' city ' as ' city ',
' Sakila '. ' Country '. ' Country ' as ' country ',
' s '. ' store_id ' as ' SID '
From
((' Sakila '. ' Staff ') ' JOIN ' Sakila ', ' address ' '
a ' ((' s '. ' address_id ' = ' a '. ' address_id ')
) Join ' Sakila ' (' a ' city_id ' = ' sakila '. ' City '. ' city_id '))
joins ' Sakila '. ' Country ' on (' Sakila '. ') City '. ' country_id ' = ' sakila '. ' Country '. ' country_id '))

2. Stored Procedures

A.

CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' film_in_stock ' (in p_film_id int, in p_store_id int, out p_film_count int) 
   reads SQL DATA
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

B.

CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' rewards_report ' (in Min_monthly_purchases TINYINT UNSIGNED, in Min_dollar_  amount_purchased DECIMAL (10,2) UNSIGNED, out count_rewardees INT) reads SQL DATA COMMENT ' provides a customizable
On best customers ' Proc:begin DECLARE Last_month_start DATE;
DECLARE Last_month_end DATE; 
/* Some Sanity checks ... */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;
/* Determine start and end time periods */SET Last_month_start = Date_sub (Current_date (), INTERVAL 1 month);
SET Last_month_start = str_to_date (CONCAT (Last_month_start), '-', Month (last_month_start), ' -01 '), '%y-%m-%d ');
SET last_month_end = Last_day (Last_month_start);
/* Create a temporary storage area for Customer IDs. * * CREATE temporary TABLE tmpcustomer (customer_id smalLINT UNSIGNED not NULL PRIMARY KEY); * Customers meeting the monthly purchase requirements * * * 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 has S
UM (P.amount) > Min_dollar_amount_purchased and COUNT (customer_id) > min_monthly_purchases;
/* Populate out parameter with Count of found customers/SELECT count (*) from Tmpcustomer to Count_rewardees;
/* Output All customer information of matching rewardees.
Customize output as needed.
* * SELECT c.* from Tmpcustomer as T INNER JOIN the customer as C on t.customer_id = c.customer_id;
/* Clean up */DROP TABLE Tmpcustomer; End

3. function

A.

CREATE definer= ' root ' @ ' localhost ' FUNCTION ' get_customer_balance ' (p_customer_id INT, P_effective_date DATETIME) RETURNS Decimal (5,2) reads SQL DATA deterministic BEGIN #OK, WE NEED to CALCULATE the current BALANCE GIVEN A customer_id and A DATE #THAT WE WANT The BALANCE to is effective for.  The BALANCE is: # 1 rental FEES to all PREVIOUS Rentals # 2) One DOLLAR to EVERY day the PREVIOUS rentals ARE Overdue #  3) IF A FILM is more THAN rental_duration * 2 Overdue, CHARGE the Replacement_cost # 4) Subtract all PAYMENTS MADE before The DATE SPECIFIED DECLARE v_rentfees DECIMAL (5,2); #FEES PAID to RENT the videos initially DECLARE INTEGER; #LATE FEES for PRIOR rentals DECLARE v_payments DECIMAL (5,2);  #SUM of PAYMENTS MADE previously 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_effecti Ve_date and rental.customer_id = P_customer_id; SELECT ifnull (SUM (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) to 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 P
ayment.customer_id = p_customer_id;
return v_rentfees + v_overfees-v_payments; End

B.

CREATE definer= ' root ' @ ' localhost ' FUNCTION ' inventory_in_stock ' (p_inventory_id INT) RETURNS tinyint (1)
reads SQL DATA
BEGIN
DECLARE v_rentals INT;
DECLARE v_out INT;
#AN ITEM is in-stock IF THERE ARE either NO rows in the rental TABLE
#FOR the item OR all rows HAVE return_date popula TED
SELECT COUNT (*) into the 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

The above is a small set to introduce the MySQL 5.7 create VIEW or FUNCTION or PROCEDURE, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!