-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variableCREATE PROCEDURE ordertotal(IN onumber INT,IN taxable BOOLEAN,OUT ototal DECIMAL(8,2)
) COMMENT ‘Obtain order total, optionally adding tax’
BEGIN-- Declare variable for totalDECLARE total DECIMAL(8,2);-- Declare tax percentageDECLARE taxrate INT DEFAULT 6;-- Get the order totalSELECT Sum(item_price*quantity)FROM orderitemsWHERE order_num = onumberINTO total;-- Is this taxable?IF taxable THEN-- Yes, so add taxrate to the totalSELECT total+(total/100*taxrate) INTO total;END IF;-- And finally, save to out variableSELECT total INTO ototal;
END;CALL ordertotal(20005, 0, @total);
SELECT @total;CALL ordertotal(20005, 1, @total);
SELECT @total;
（求是否含稅的給定訂單總金額）
6）Inspecting Stored Procedures（檢查存儲(chǔ)過程） 1、
SHOW CREATE PROCEDURE ordertotal;
（功能：顯示用于創(chuàng)建一個(gè)存儲(chǔ)過程ordertotal的CREATE語句，即定義語句的本身）
2）
SHOW PROCEDURE STATUS LIKE 'ordertotal' ;
（功能：獲得當(dāng)前數(shù)據(jù)庫中包括何時(shí)、何人創(chuàng)建等詳細(xì)信息的存儲(chǔ)過程列表，可以限制或指定一個(gè)過濾模式，如LIKE關(guān)鍵字的使用。）
-- Name: ordertotal
-- Parameters: onumber = length
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variableCREATE PROCEDURE ordertotal(IN onumber INT,IN taxable BOOLEAN,OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN-- Declare variable for totalDECLARE total DECIMAL(8,2);-- Declare tax percentageDECLARE taxrate INT DEFAULT 6;-- Get the order totalSELECT Sum(producerC)FROM moviesWHERE length = onumberINTO total;-- Is this taxable?IF taxable THEN-- Yes, so add taxrate to the totalSELECT total+(total/100*taxrate) INTO total;END IF;-- And finally, save to out variableSELECT total INTO ototal;
END;CALL ordertotal(116, 0, @total);
SELECT @total;
6）Inspecting Stored Procedures（檢查存儲(chǔ)過程） 1、 SHOW CREATE PROCEDURE ordertotal; （功能：顯示用于創(chuàng)建一個(gè)存儲(chǔ)過程ordertotal的CREATE語句，即定義語句的本身）
2）
SHOW PROCEDURE STATUS LIKE 'ordertotal' ;
（功能：獲得當(dāng)前數(shù)據(jù)庫中包括何時(shí)、何人創(chuàng)建等詳細(xì)信息的存儲(chǔ)過程列表，可以限制或指定一個(gè)過濾模式，如LIKE關(guān)鍵字的使用。）
創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)
