MySQL 5.7 create VIEW or FUNCTION or PROCEDURE
1.视图
a.
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQLSECURITYINVOKER 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(DISTINCTCONCAT(`c`.`name`, ':', (SELECT GROUP_CONCAT(`f`.`title` ORDERBY`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`.`category_id`=`c`.`category_id`) AND(`fa`.`actor_id`=`a`.`actor_id`)))) ORDERBY`c`.`name`ASC SEPARATOR';')AS`film_info` FROM (((`sakila`.`actor``a` LEFTJOIN`sakila`.`film_actor``fa`ON((`a`.`actor_id`=`fa`.`actor_id`))) LEFTJOIN`sakila`.`film_category``fc`ON((`fa`.`film_id`=`fc`.`film_id`))) LEFTJOIN`sakila`.`category``c`ON((`fc`.`category_id`=`c`.`category_id`))) GROUPBY`a`.`actor_id`,`a`.`first_name`,`a`.`last_name`
b.
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQLSECURITYDEFINER VIEW`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`zipcode`, `a`.`phone`AS`phone`, `sakila`.`city`.`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.存储过程
a.
CREATEDEFINER=`root`@`localhost`PROCEDURE`film_in_stock`(INp_film_idINT,INp_store_idINT,OUTp_film_countINT) READSSQLDATA BEGIN SELECTinventory_id FROMinventory WHEREfilm_id=p_film_id ANDstore_id=p_store_id ANDinventory_in_stock(inventory_id); SELECTFOUND_ROWS()INTOp_film_count; END
b.
CREATEDEFINER=`root`@`localhost`PROCEDURE`rewards_report`( INmin_monthly_purchasesTINYINTUNSIGNED ,INmin_dollar_amount_purchasedDECIMAL(10,2)UNSIGNED ,OUTcount_rewardeesINT ) READSSQLDATA COMMENT'Providesacustomizablereportonbestcustomers' proc:BEGIN DECLARElast_month_startDATE; DECLARElast_month_endDATE; /*Somesanitychecks...*/ IFmin_monthly_purchases=0THEN SELECT'Minimummonthlypurchasesparametermustbe>0'; LEAVEproc; ENDIF; IFmin_dollar_amount_purchased=0.00THEN SELECT'Minimummonthlydollaramountpurchasedparametermustbe>$0.00'; LEAVEproc; ENDIF; /*Determinestartandendtimeperiods*/ SETlast_month_start=DATE_SUB(CURRENT_DATE(),INTERVAL1MONTH); SETlast_month_start=STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d'); SETlast_month_end=LAST_DAY(last_month_start); /* Createatemporarystorageareafor CustomerIDs. */ CREATETEMPORARYTABLEtmpCustomer(customer_idSMALLINTUNSIGNEDNOTNULLPRIMARYKEY); /* Findallcustomersmeetingthe monthlypurchaserequirements */ INSERTINTOtmpCustomer(customer_id) SELECTp.customer_id FROMpaymentASp WHEREDATE(p.payment_date)BETWEENlast_month_startANDlast_month_end GROUPBYcustomer_id HAVINGSUM(p.amount)>min_dollar_amount_purchased ANDCOUNT(customer_id)>min_monthly_purchases; /*PopulateOUTparameterwithcountoffoundcustomers*/ SELECTCOUNT(*)FROMtmpCustomerINTOcount_rewardees; /* OutputALLcustomerinformationofmatchingrewardees. Customizeoutputasneeded. */ SELECTc.* FROMtmpCustomerASt INNERJOINcustomerAScONt.customer_id=c.customer_id; /*Cleanup*/ DROPTABLEtmpCustomer; END
3.函数
a.
CREATEDEFINER=`root`@`localhost`FUNCTION`get_customer_balance`(p_customer_idINT,p_effective_dateDATETIME)RETURNSdecimal(5,2) READSSQLDATA DETERMINISTIC BEGIN #OK,WENEEDTOCALCULATETHECURRENTBALANCEGIVENACUSTOMER_IDANDADATE #THATWEWANTTHEBALANCETOBEEFFECTIVEFOR.THEBALANCEIS: #1)RENTALFEESFORALLPREVIOUSRENTALS #2)ONEDOLLARFOREVERYDAYTHEPREVIOUSRENTALSAREOVERDUE #3)IFAFILMISMORETHANRENTAL_DURATION*2OVERDUE,CHARGETHEREPLACEMENT_COST #4)SUBTRACTALLPAYMENTSMADEBEFORETHEDATESPECIFIED DECLAREv_rentfeesDECIMAL(5,2);#FEESPAIDTORENTTHEVIDEOSINITIALLY DECLAREv_overfeesINTEGER;#LATEFEESFORPRIORRENTALS DECLAREv_paymentsDECIMAL(5,2);#SUMOFPAYMENTSMADEPREVIOUSLY SELECTIFNULL(SUM(film.rental_rate),0)INTOv_rentfees FROMfilm,inventory,rental WHEREfilm.film_id=inventory.film_id ANDinventory.inventory_id=rental.inventory_id ANDrental.rental_date<=p_effective_date ANDrental.customer_id=p_customer_id; SELECTIFNULL(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)INTOv_overfees FROMrental,inventory,film WHEREfilm.film_id=inventory.film_id ANDinventory.inventory_id=rental.inventory_id ANDrental.rental_date<=p_effective_date ANDrental.customer_id=p_customer_id; SELECTIFNULL(SUM(payment.amount),0)INTOv_payments FROMpayment WHEREpayment.payment_date<=p_effective_date ANDpayment.customer_id=p_customer_id; RETURNv_rentfees+v_overfees-v_payments; END
b.
CREATEDEFINER=`root`@`localhost`FUNCTION`inventory_in_stock`(p_inventory_idINT)RETURNStinyint(1) READSSQLDATA BEGIN DECLAREv_rentalsINT; DECLAREv_outINT; #ANITEMISIN-STOCKIFTHEREAREEITHERNOROWSINTHErentalTABLE #FORTHEITEMORALLROWSHAVEreturn_datePOPULATED SELECTCOUNT(*)INTOv_rentals FROMrental WHEREinventory_id=p_inventory_id; IFv_rentals=0THEN RETURNTRUE; ENDIF; SELECTCOUNT(rental_id)INTOv_out FROMinventoryLEFTJOINrentalUSING(inventory_id) WHEREinventory.inventory_id=p_inventory_id ANDrental.return_dateISNULL; IFv_out>0THEN RETURNFALSE; ELSE RETURNTRUE; ENDIF; END
以上所述是小编给大家介绍的MySQL5.7createVIEWorFUNCTIONorPROCEDURE，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！