MySQL 5.7 Create VIEW or FUNCTION or PROCEDURE

1. View A.    CREATE algorithm = UNDEFINED definer = ' root ' @ ' localhost ' SQL SECURITY invokerview ' Sakila '. ' Actor_info ' as SELECT ' a '. ' actor_id ' as ' actor_id ', ' a '. ' First_Name ' as ' first_name ', ' a '. ' Last_Name ' as ' Last_nam                             E ', Group_concat (DISTINCT CONCAT (' C '. ' Name ', ': ', (SELECT                                    Group_concat (' F '. ' title ' ORDER by ' f '. ' title ' ASC                            SEPARATOR ', ') from (' Sakila '. ' film ' F ' Join ' Sakila '. ' Film_category ' FC ' on (' f '. ' film_id ' = ' fc '. ' film_id ')) join ' Sakila '. ' Film_actor ' FA ' on (' f '. ' film_id ' = ' fa '. ' film_id '))) WHERE (' FC '. ' CA tegory_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 ' on (' a '. ' actor_id ' = ' FA '. ' actor_id '))) left JOIN ' Sakila '. ' Film_category ' FC ' on (' FA '. ' film_id ' = ' fc '. ' film_id '))] left JOI N ' Sakila '. ' Category ' ' C ' on (' 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 definerview ' Sakila '. ' Staff                _list ' as SELECT ' s '. ' 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 ' as ' city ', ' Sakila '. ' Country '. ' Country ' as ' country ', ' s '. ' Store _id ' as ' SID ' from ((' Sakila ', ' staff ' ' s ' JOIN ' Sakila ', ' address ' a ' on (' s '. ' address_id ' = ' a '. ' Address_id '))) join ' Sakila '. ' City ' on (' a '. ' city_id ' = ' sakila '. ' "City '. ' city_id ') ') join ' Sakila '. ' Country ' on (' Sakila '. ' City '. ' country_id ' = ' sakila '. ' Country '. ' country_id '))) 2. Stored procedure 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 databegin 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; Endb.create definer= ' root ' @ ' localhost ' PROCEDURE ' rewards_report ' (in Min_monthly_purchases TINYINT UNSIGNED, in MI n_dollar_amount_purchased DECIMAL (10,2) UNSIGNED, out count_rewardees INT) READS SQL DATA COMMENT ' provides a CU    Stomizable report on the 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 is > $        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 (year (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); /* Find All Customers meeting the monthly purchase requirements */INSERT into Tmpcustomer (Customer_i    d) 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 have SUM (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 into count_rewardees;        /* Output All customer information of matching rewardees.    Customize output as needed.    */SELECT c.* from Tmpcustomer as T INNER JOIN Customer as C on t.customer_id = c.customer_id; /* Clean up */DROP TABLE Tmpcustomer; END3. 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 deterministicbegin #OK, WE need to CALCULATE the current BALANCE GIVEN a customer_id and a DATE #THAT WE want the BALANCE to being effective for. The BALANCE is: # 1) Rental FEES-PREVIOUS Rentals # 2) One DOLLAR for every day the PREVIOUS RENTA LS is overdue # 3) IF A FILM was more THAN rental_duration * 2 Overdue, CHARGE the Replacement_cost # 4) S Ubtract All PaymenTS made before the DATE SPECIFIED DECLARE v_rentfees DECIMAL (5,2);      #FEES PAID to RENT the VIDEOS initially DECLARE V_overfees 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_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_DA YS (rental.return_date)-to_days (rental.rental_date))-film.rental_duration), 0)), 0) into the v_overfees from rental, Inven Tory, 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_cu  stomer_id; RETURN v_rentfees + v_overfees-v_payments; Endb.create definer= ' root ' @ ' localhost ' FUNCTION ' inventory_in_stock ' (p_inventory_id INT) RETURNS tinyint (1) READS SQL D    Atabegin DECLARE v_rentals INT;    DECLARE V_out INT; #AN Item is In-stock IF there be either NO ROWS in the rental TABLE #FOR the item OR all ROWS has return_date Populat    ED 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

　　

MySQL 5.7 Create VIEW or FUNCTION or PROCEDURE