#

/**  
	@author Professor Tom DeDonno

	function getImageURL( fid smallint)
	procedure orderInvoiceVerySimple( orderID ) no PLSQL block example
	procedure orderInvoice( orderID ) full featured orderInvoice
	note fruitShared.orderInvoiceSimple( orderID ) list orderID,customerID and line items
	with submit we cannot list startDate, or shipDate set when file is run

	@date 8/2013, modified 8/2015

   Essay 6-8 are in 
   
   fruitTrigger.sql.html

   Essay 9 what is the advantage and disadvantage of using BLOB
   instead of the method implemented in function getImageURL
   make sure you include the basic functionality of getImageURL in your answer
   
   */

#when using drop, any function that reads using select can cause out of sync error,

drop function if exists `getImageURL`;
drop procedure if exists `orderInvoice`;
drop procedure if exists `orderInvoiceVerySimple`;

delimiter $$
# 

for getImageURL you need to use HTML view source

# <a> anchor and <img> tags are being rendered create function getImageURL( fID smallint unsigned ) RETURNS TEXT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER BEGIN declare str TEXT; declare n varchar( 24 ); set n = (select name from fruit where fruit.fruitID = fID); set str = concat( '', '' ); return str; END; $$ delimiter ; delimiter $$ CREATE PROCEDURE `orderInvoiceVerySimple`( IN `activeOrderID` INT ) COMMENT 'Very Simple Query, pass procedure orderID, kicks out order info ' DETERMINISTIC READS SQL DATA # for submit we cannot have a shipDate BEGIN SET FOREIGN_KEY_CHECKS = ON; SELECT orderID, customerID, startDate FROM `orders` WHERE `orders`.orderID = activeOrderID; END; $$ delimiter ; /** Essay 10 how does Extended Price column get created? Answers to essay 10 to 15 are at essay10To15.html Essay 11-12 are in fruitTransactionCreate.sql.html */ delimiter EOT CREATE PROCEDURE `orderInvoice`( IN `activeOrderID` INT ) COMMENT 'Prints out a formal invoice' DETERMINISTIC READS SQL DATA BEGIN declare sDate timestamp; declare cID mediumint; declare cName VARCHAR( 32 ); declare header TEXT; SET FOREIGN_KEY_CHECKS = ON; IF ( activeOrderID not in (select orderID from orders) ) THEN set header = concat( "orderID: ", activeOrderID, " is not an order in the system"); SIGNAL SQLSTATE '90001' set message_text = header; END IF; set sDate = (select shipDate from `orders` where `orders`.orderID = activeOrderID); set cID = (select customerID from `orders` where `orders`.orderID = activeOrderID); set cName = (select name from customer where customer.customerID = cID ); select concat( "Name: ", cName, " Creation Date: ", (select startDate from `orders` where `orders`.orderID = activeOrderID), " Ship Date: ", if(sDate is null," Not Sent Yet", "Sent Already" ) ) as ""; # note JOIN is inner join same as orderLine, fruitView with where SELECT o.fruitID as "fruitID", name, concat( "$", price ) as "Unit Price" , o.quantity as "Quantity", concat( "$", format( ifnull( o.quantity, 0 )*price, 2 ) ) as "Extended Price" FROM ( SELECT * FROM orderLine WHERE orderID = activeOrderID ) AS o JOIN fruitView ON fruitView.fruitID = o.fruitID; #note we don't need format each time price is a decimal(5,2)a Select concat( "$", sum( price*o.quantity )) as "Total Price" FROM ( SELECT * FROM orderLine WHERE orderID = activeOrderID ) AS o JOIN fruitView ON fruitView.fruitID = o.fruitID; END; EOT delimiter ; #