1 / 42

COP 2700 – Data Structures (SQL)

COP 2700 – Data Structures (SQL). Lecture 9 – June 17. Announcements. Assignment 4 is due Saturday Night, June 20 by 11:59 PM. Next Monday Meta-Database Accessing Databases Outside of Management Studio Review? Wednesday Review? Final Exam Select Statements Update Statements

lesterk
Download Presentation

COP 2700 – Data Structures (SQL)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. COP 2700 – Data Structures (SQL) Lecture 9 – June 17

  2. Announcements • Assignment 4 is due Saturday Night, June 20 by 11:59 PM. • Next Monday • Meta-Database • Accessing Databases Outside of Management Studio • Review? • Wednesday • Review? • Final Exam • Select Statements • Update Statements • Procedures, Functions and Triggers • You will not have to actually write any BUT the test will include scenarios that require you to think through how your would solve the problem and with what tools. • Meta-Data • I have a lecture ready on the differences between SQLServer, Oracle and MySQL. Trying to decide if I am going to give it.

  3. Tonight • Functions, Procedures and Triggers • Assignment 5 • Lab Time

  4. What is missing from SQL to make it a true Programming Language? • Variables • Conditional Statements • If, Else, Switch/Case • Iterative Statements • For, While • Traverse a “file” (which in this case is a Query Result Set) • Communicate with Outside World

  5. Variables • Variable Names in SQLServer Script language must begin with the “@” sign. • To create a variable, one uses the Declare statement along with a data type: • DECLARE @AvgCost Numeric (18,4); • A variable can be initialized in the declare • DECLARE @AvgCost Numeric (18,4) = 0; • Multiple variables can be created using one Declare statement: • DECLARE @AvgCost Numeric(18,4) = 0, @TotalCost Numeric(18,4) = 0;

  6. Assigning Variables a Value • The SET command is used to assign a value to a variable • SET @AvgCost = 12.4; • SET @AvgCost = @TotalCost / @ItemCount; • The left of the = is the variable name, and the right is a valid expression that results in the same variable type • @SET @AVGCost = ‘Too Much’; Won’t work since ‘Too Much’ is a string, and @AVGCost is a Number

  7. Communicating with Outside World • The Print Command can output a string to the Message Area. • It can also force a message to be displayed in the event of an error. • Print {String Expression}

  8. Let’s Put What We Have Together into a Mini-Program DECLARE @AvgCost Numeric (18,4) = 0, @TotalCost Numeric (18,4) = 25; DECLARE @TotalItems Numeric(18,4) = 6; SET @AvgCost = @TotalCost / @TotalItems; Print @AvgCost;

  9. Logic (IF/ELSE) • Logic is added to SQLServer Script language with the IF and ELSE statements. IF (Conditional Expression) Begin • Do some stuff End ELSE Begin • Do some other stuff End

  10. Let’s add that to our Mini Program DECLARE @AvgCost Numeric (18,4) = 0, @TotalCost Numeric (18,4) = 25; DECLARE @TotalItems Numeric(18,4) = 6; IF @TotalItems > 0 BEGIN SET @AvgCost = @TotalCost / @TotalItems; Print 'The average cost is: ' + cast(@AvgCost as varchar(10)); END; ELSE BEGIN Print 'Total Items = 0'; END;

  11. Selects in Procedures • Scalar Selects • Returns One Row and can then directly have the values of the columns assigned to variables • Cursors • Returns a result set that one can then transverse through • Direct Select Statements • Like Regular Old Selects. Write the output out to the console or to a grid. • Let’s add a select to our previous script.

  12. Let’s do a Simple Scalar Select DECLARE @OnHand Numeric(18,0), @Price Numeric (18,2); SELECT @OnHand = On_Hand, @Price = Price FROM PART Where PART_NUM = 'AT94'; PRINT 'Total On-Hand Value for AT94 is:' + Cast(@OnHand * @Price as Varchar(10)); If we did not specify the Part_Num, then the assignments of @OnHand and @Price would be to whatever is the first record returned. As mentioned Monday, I think SQLServer used the @ so there would be no issues between variable and column names

  13. Let’s Move On With a Cursor Select • Define the Select Clause for the Cursor • Run the Cursor • Fetch the Records in the Cursor using a WHILE loop • @@FETCH_STATUS is an internal variable that is set by the Fetch command. If it is zero, the Fetch was good. If it was -1, we are at end of file. • Close the Cursor • Deallocate the Cursor

  14. Let’s Move On With a Cursor Select • Let’s say we want to list who has a particular part on order in a simple report. • We need to write a Select that pulls the Customer Number and Number on Order for that part • We need to loop through the rows returned and print out the information

  15. Let’s Move On With a Cursor Select DECLARE @Customer char(30); DECLARE @OnOrder Numeric(18,0); BEGIN DECLARE PartOrder CURSOR FOR SELECT NUM_ORDERED, CUSTOMER_NAME FROM ORDER_LINE join ORDERS on ORDER_LINE.ORDER_NUM = orders.ORDER_NUM JOIN CUSTOMER on orders.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM WHERE ORDER_LINE.PART_NUM = 'DR93'; OPEN PartOrder; FETCH PartOrder INTO @OnOrder, @Customer; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @customer + cast(@OnOrder as Varchar(10)); FETCH PartOrder INTO @OnOrder, @Customer; END; CLOSE PartOrder; DEALLOCATE PartOrder; END;

  16. Let’s put the two together • Let’s change the report so that it includes the part description at the top: DECLARE @PartDescription Varchar(15); SELECT @PartDescription = Description FROM PART WHERE PART_NUM = 'DR93'; PRINT ' ORDERS FOR PART DR93: ' + @PartDescription;

  17. Wonderful, but we are locked into one part. • Let’s change it so that we can enter the part by turning this little program into a Stored Procedure: • Replace the Part Number with a Variable • Create the Stored Procedure • Let’s call it PartOrderDetail

  18. CREATE PROCEDURE PartOrderDetail (@PartNum Varchar(4)) AS BEGIN DECLARE @PartDescription Varchar(15); SELECT @PartDescription = Description FROM PART WHERE PART_NUM = @PartNum; IF @PartDescription is NULL BEGIN PRINT 'Part Number Not Found'; RETURN; END; PRINT ' ORDERS FOR PART ' + @PartNum + ': ' + @PartDescription; DECLARE @Customer char(30); DECLARE @OnOrder Numeric(18,0); DECLARE PartOrder cursor for SELECT NUM_ORDERED, CUSTOMER_NAME FROM ORDER_LINE join ORDERS on ORDER_LINE.ORDER_NUM = orders.ORDER_NUM JOIN CUSTOMER on orders.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM WHERE ORDER_LINE.PART_NUM = @PartNum; OPEN PartOrder; FETCH PartOrder INTO @OnOrder, @Customer; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @customer + cast(@OnOrder as Varchar(10)); FETCH PartOrder INTO @OnOrder, @Customer END; CLOSE PartOrder; DEALLOCATE PartOrder; END; The Complete Procedure(With some basic error checking)

  19. Let’s take what we learned and make a Function. • We want to pass in a Part Number and receive back the quantity of that part on order. • Yeah, we could do it with a Scalar, but let’s try it with a function to see if we like it!!

  20. Parts On Order CREATE FUNCTION PartsOnOrder (@PartNum Varchar(4)) Returns Numeric(18,0) AS BEGIN DECLARE @OnOrder Numeric(18,0) = 0; SELECT @OnOrder = SUM(NUM_ORDERED) FROM ORDER_LINE WHERE PART_NUM = @PartNum; RETURN @OnOrder; END; • SELECT *, dbo. PartsOnOrder (Part_Num) FROMPart

  21. Triggers • Small Transact SQL programs that are “Triggered” when the RDMS processes a change to a table • Used to maintain constraints, database consistency • Used to automatically create log type entries into other tables for security.

  22. SQLServer Basic Trigger Syntax CREATE [ALTER] TRIGGER Trigger_Name ON Table_Name {AFTER | INSTEAD OF} { DELETE, INSERT, UPDATE} AS Transact-SQL Procedure

  23. After/Instead Of • After Triggers are processed after the update to the table is complete. You cannot make changes to the table on which the trigger fired. • Instead Of Triggers are run instead of the actual operation. So, instead of actually deleting a record, the Instead of trigger is fired and it can then determine if the record is to be truly deleted or not.

  24. Update, Delete or Insert • Allows the trigger to determine when it is to be executed • Each update type can have its own trigger, or a trigger can be shared • Trigger firing can be dependant on changes to specific columns, instead of an entire database record (but we’ll save that for another class)

  25. How to know what changed • When a Trigger is fired, two “dummy” tables are created called inserted and deleted • inserted contains the current values of the rows if the update or insert is completed. It will return null values on a delete. • deleted contains the previous values of the row before the update. It will return null values on an insert. • These two fake tables can be treated just like regular tables. If multiple rows are changed, then each row changed will be included in the inserted/deleted tables.

  26. Let’s Do A Real Simple Trigger CREATE TRIGGER trgWhatsUp ON PART AFTER UPDATE AS BEGIN Print 'What''s Up Dog?'; END;

  27. Now Let’s Do Something Real • We want to keep track of all changes to the Order_Line Table so that we know of all Updates, Deletes and Inserts to that table. • To track these changes, we first need to create a table (that we will name Audit) to hold the changes.

  28. Here is our new Audit Table CREATE TABLE AUDIT (AUDIT_ID Numeric(18,0) NOT NULL IDENTITY PRIMARY KEY, ORDER_NUM VarChar(5) NOT NULL FOREIGN KEY REFERENCES ORDERS (ORDER_NUM), PART_NUM Varchar(4) NOT NULL FOREIGN KEY REFERENCES PART (Part_Num), OLD_NUM_ORDERED Numeric(3,0), OLD_QUOTED_PRICE Numeric (6,2), NEW_NUM_ORDERED Numeric (3,0), NEW_QUOTED_PRICE Numeric (6,2), DATE_TIME_CHANGED DateTime)

  29. Next, Let’s walk through how this is going to work. • If we insert a new record into Order_Line, then the deleted fake table will be empty, but the inserted fake table will have the record that was inserted. • If we delete a record from the Order_Line table, then the deleted fake table will have what was deleted, but the inserted fake table will be empty. • If we update a record in the Order_Line table, then the delete table will contain the rows(s) before the change and the inserted will contain the row(s) after the change.

  30. How can we handle this • We could write separate triggers for Insert, Delete and Update. Then we know what to expect in the delete and inserted tables. • Let’s be adventurous, and say we only want to write one trigger for update, delete and insert. • How can we pull together the inserted and deleted tables based on the keys in Order_Line so that all conditions are handled?

  31. Full Outer Join • If we use a Full Outer Join on our inserted and deleted tables we would • Get all deleted records with the inserted columns null • Get all inserted records with the deleted columns null • Get all updated records with all columns with values. SELECT ISNULL(I.ORDER_NUM,D.ORDER_NUM) as ORDER_Num, ISNULL(I.PART_NUM,D.PART_NUM) as Part_Num, D.NUM_ORDERED as OldOnOrder, I.NUM_ORDERED as NewOnOrder, D.QUOTED_PRICE as OldPrice, I.QUOTED_PRICE as NewPrice FROM inserted I FULL OUTER JOIN deleted D ON I.order_num = d.order_num and I.part_num = d.part_num;

  32. Now we can pull together our trigger CREATE TRIGGER trgLogOrderChanges ON Order_Line AFTER INSERT, UPDATE, DELETE AS BEGIN – Define our variables declare @OrderNum varchar(5), @PartNum varchar(4); declare @OldNumOrdered decimal(3), @OldQuotedPrice decimal(6,2); declare @NewNumOrdered decimal(3), @NewQuotedPrice decimal(6,2); -- Declare our cursor that pulls together inserted and deleted DECLARE TableChanges CURSOR FOR SELECT ISNULL(I.ORDER_NUM,D.ORDER_NUM) as ORDER_Num, ISNULL(I.PART_NUM,D.PART_NUM) as Part_Num, D.NUM_ORDERED as OldOnOrder, I.NUM_ORDERED as NewOnOrder, D.QUOTED_PRICE as OldPrice, I.QUOTED_PRICE as NewPrice FROM inserted I FULL OUTER JOIN deleted D ON I.order_num = d.order_num and I.part_num = d.part_num; -- Run through the records and log them to the Audit table OPEN TableChanges; FETCH TableChanges INTO @Ordernum, @PartNum, @OldNumOrdered, @NewNumOrdered, @OldQuotedPrice, @NewQuotedPrice; WHILE @@FETCH_STATUS = 0 BEGIN INSERT into Audit (Order_Num, Part_Num, Old_Num_Ordered, Old_Quoted_Price, New_Num_Ordered, New_Quoted_Price, Date_Time_Changed) VALUES(@OrderNum,@PartNum,@OldNumOrdered,@OldQuotedPrice,@NewNumOrdered,@NewQuotedPrice,getdate()); FETCH TableChanges INTO @Ordernum, @PartNum, @OldNumOrdered, @NewNumOrdered, @OldQuotedPrice, @NewQuotedPrice; END; CLOSE TableChanges; DEALLOCATE TableChanges; END; GO

  33. Instead Of Trigger • The Instead Of Trigger takes the place of whatever was happening to the table. • So, if it was a Delete, unless you redo the delete in the Trigger, it will not happen. • Let’s do a quick one that will not allow more than one record at a time to be deleted from the Order_Line table:

  34. Instead of Trigger CREATE TRIGGER trgOnlyOneDelete ON Order_Line INSTEAD OF DELETE AS BEGIN declare @OrderNum varchar(5); declare @PartNum varchar(4); Declare @Count Numeric(18,0); SELECT @Count=COUNT(*) FROM deleted; IF @Count > 1 BEGIN Print 'Cannot Delete More Than One Record At A Time!!' Return; END; ELSE BEGIN Select @OrderNum = ORDER_NUM, @PartNum = PART_NUM FROM deleted; Delete From ORDER_LINE where ORDER_NUM = @OrderNum AND PART_NUM = @PartNum; Print 'Deleted One Record'; END; END;

  35. Constraints using a Trigger • Let’s Assume we want to add the ability to check for a pre-requisite when a student tries to register for a class. • That is, before a student can register for one class, he must have already taken and passed another class • We could do this in our code, but then everywhere that we may do this type of operation, we would have to duplicate the code • One solution would be to write a trigger that would stop an insert if the student has not already taken the class

  36. What do we need to do to be able to implement this? • A Prerequiste table needs to be set up that contains pairs of course_ids, the first the course that the student wants to take, and the second a pre-requisite course_id. Both with foreign keys back to Course • What is the primary key to this new table. • Write the DDL commands needed to create this table. • Add a couple of pre-requisite records using basic INSERT commands.

  37. Now for the trigger • This trigger will only be on Inserts and we will assume that the program is doing separate insert statements for each course/student. • Why would this matter? • To which table should this trigger be applied? • What kind of trigger do we need to use? • AFTER or INSTEAD OF

  38. Let’s Design the Processing • To validate a pre-requisite , we need • The Course_ID • The Student_ID • How do we get the Course_ID when the Insert is to the Transcript Table? • Once we have the information, what processing do we need to do? • Query the Prerequisite table to see what courses must be passed (let’s say with A B or C) before allowing someone to Insert the new course.

  39. A multitude of ways • Join Pre-Requisite, Schedule and Transcript for the Course and Student, then fetch through the records. • Count the number of pre-requisites then count the distinct number of courses that the student took in the list and see if they match. • Start with the pre-requisites and remove all that the student has taken, any left means he hasn’t fulfilled all pre-requisites.

  40. Then, once we know, we can complete the trigger • If the student does not meet the pre-requisites • send out an error message • otherwise • re-do the insert

  41. Could we do this all with one statement using the inserted table? • Yep – Let’s give it a try.

  42. Assignment 5 • Granny is being a real …..

More Related