240 likes | 340 Vues
Task oriented processing. And Queries. How do I want my information?. A customer should not be required to remember An order number A product code An account code except for security purposes. Where is my…?. A customer of Northwind wants to know Where is the order I placed last week?
E N D
Task oriented processing And Queries
How do I want my information? • A customer should not be required to remember • An order number • A product code • An account code • except for security purposes
Where is my…? • A customer of Northwind wants to know • Where is the order I placed last week? • How much do I pay for Chai? • How much more does Chai cost than Chang? • What is the least expensive drink? • What is the fanciest (most expensive) cheese?
How do I manage …? • A line manager wants to know • Are there any products that are out of stock? • Are there any customers on the record that have never placed an order? • Are there any employees who have never made a sale?
How do I save …? • A managing director wants to know • What is our best selling product? • What is our highest earning product? • Who is our highest earning employee? • Who is the employee who sells the most units?
Formulating queries • Ask the question the user would ask • Avoid code-dependent queries – go for name-based or point and pick based queries. • Once the query has been stated • Consult the ERD • Check the enquiry access path • Try to fill the query using joins rather than separate queries.
Stored Procedures Saving your queries in your database
Simple Stored Procedures • Create the query and check it in the Query Analyser. • Using the Enterprise Manager, Add a new stored procedure to your database. • Name your Stored Procedure appropriately. • Paste in your query, check syntax and click OK. • Run your stored procedure from the query analyser using ‘EXEC <SP_NAME>.
Using variables • Variables can be used in SQL Server using either the Query Analyser or Stored Procedures. • All variables must begin with the symbol @ • Variables must be given a data type: • e.g. Declare @cost numeric(6,2); Declare @name varchar(20);
Using Variables • Variables can be assigned values using Set: Set @cost = 0; Set @cost = @cost * 1.1; • Or using Select: Declare @givenId numeric(7,0); Set @givenId = 1234567 Select @name = suppliername from Supplier where SupplierId = @givenId;
Parameters in Stored Procedures • Parameters can be passed to stored procedures by reference. • If the stored procedure is expected to return a significant value, then it should be explicitly marked as an output parameter. • To use parameters: • Define the formal parameters in the Stored Procedure. • Define the actual parameters and give them values in the Query Analyser.
Exercise • Write a stored procedure that will allow Joe to add an orderline to his database, decrementing the stock quantity in the stock table and returning a cost for that orderline. If there is not enough stock in the yard, a message should be displayed and the new orderline should be rejected.
Procedure specification • Input: OrderNo, StockCode, QuantityRequired • Output: The linecost • To reject the request if the stock is not in the yard: • Read the stock row • Check the quantity • If enough, • Decrement stock • Calculate line cost • Update record • Write new orderline • Else • Print error message
CREATE procedure sp_SELLSTOCK @DCKT numeric (5,0), @STKCD varchar(5), @QTYREQ numeric(4,0), @linecost numeric (7,2) OUTPUT as Begin Declare @QTY numeric(4,0) Set @linecost = 0 Select @qty = stock_level from stock where stock_code = @stkcd If @qty > @qtyreq Begin Set @qty = @qty - @qtyreq print 'Quantity required = ' select @qtyreq Set @linecost = (select unit_price from stock where stock_code = @stkcd) print 'cost = ' select @linecost set @linecost = @linecost* @qtyreq print 'line cost = ' select @linecost Update stock set stock_level = @qty where stock_code = @stkcd select @qty = quantity from order_line where stock_code = @stkcd and order_id =@dckt If @qty is null Insert into order_line values (@qtyreq, @stkcd, @dckt) else Update order_line set quantity = quantity + @qtyreq where stock_code = @stkcd and order_id = @dckt End else Begin print 'There are not enough items in stock'; end End GO
To invoke sell stock and show results declare @cost numeric (7,2) set @cost = 0 exec sp_sellstock 5, e101, 2, @cost output; print 'cost =' select @cost; select * from jcustomer; select * from customerorder; select * from order_line; select * from stock; select * from Supplier;
Uses of stored Procedures • This particular procedure can be used when Orders are being added. • It only adds the orderline. All of the other information must be added outside this. • Remember, most databases are accessed from an outside source such as JSP, ASP or Visual Basic.
Recording Joe’s Docket Populating multiple tables
How do you get the information in? • Normally, this is done using a GUI, which populates a number of variables. • We will cheat, by giving the variables values. • Assume the stock being requested is: • 10 of 3321 held as @QTY1, @STK1 • 14 of 5555 held as @QTY2, @STK2 • 2 of 1212 held as @QTY3, @STK3
We need to • Generate a new docket number. • Repeatedly • ENTER the stock code and quantity being requested. • Check to see if that stock is available. • If so, decrement it from the stock and allocate it to the orderline. • Write a new orderline. • Put the system date and time onto the docket • Write the docket.
Approach. • 1. Have a rolling currentdocket, set on application launch. • 2. Declare a function SellStock (DocketNo, StockCode, Quantity) • This will take in the DocketNo, stock code and quantity and • Check the stock table to see if the code exists and if there is enough stock. • Write a new stock line. • Return a cost value for the stock.
Generate a new docket number. • This will be numerically one after the last docket. • In the morning, when the system starts up, the last docket number used can be selected from the table: • Declare @currentdocket int, docketdate datetime • Select @currentdocket = top docketno from docket • Before every new docket: • Set @currentdocket = @currentdocket + 1
Repeatedly • Write the docket. • Select @Docketdate GETDATE() • Insert into docket values docketno,,,docketdate • Repeatedly • Execute the SELLSTOCK procedure, varying the actual parameter values. • Accumulate the linecosts from each sale
Not using interface – use variables • variable_name := value • SET @variable_name = value • SELECT expression[, ...n] INTO variable_name[, ...n] FROM... • SELECT {@variable_name = expression}[, ...n] FROM... • FETCH cursor_name INTO variable_name[, ...n] • FETCH [fetch option] FROM cursor_name INTO variable_name[, ...n]