1 / 29

Introduction to T-SQL

Introduction to T-SQL. Using SQL. Interactively… SQL*Plus (Oracle) QBE (Access) SSMS (SQL Server) In application programs/environments. Accessing Databases from Application Programs. Options include: Script Files  already covered APIs  last course topic

dena
Télécharger la présentation

Introduction to T-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. Introduction to T-SQL G. Green

  2. Using SQL... • Interactively… • SQL*Plus (Oracle) • QBE (Access) • SSMS (SQL Server) • In application programs/environments...

  3. Accessing Databases from Application Programs • Options include: • Script Files  already covered • APIs  last course topic • Application-embedded DBMS Commands  will not cover • Database-Stored Code  our focus

  4. Internet resources • T-SQL Basics http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p2.aspx • Cursors http://www.sqlteam.com/article/cursors-an-overview • Parameters http://www.mssqltips.com/sqlservertutorial/162/how-to-create-a-sql-server-stored-procedure-with-parameters/

  5. Database-Stored Code • Program code stored in database • T-SQL • CLR Languages (C#, VB.net, …) • Good for: • sharing code • performance • Enforcing security, business rules, audits Database Application Program ------------------- execute A ------------------- execute B Procedure A ------------------- SQL… SQL...... ------------------- end A

  6. Database-stored code, con’t... • Stored Procedure • T-SQL block • Procedural programming features • stored in database • * invoked by name • * accepts arguments • Database Trigger • T-SQL block • Procedural programming features • stored in database • * invoked by DML (Insert, Update, or Delete) • * accepts no arguments

  7. Create stored procedure: template CREATE PROCEDURE <procedure_name> [parameter_information] AS DECLARE /* program variables defined here */ BEGIN /* program logic goes here */ END

  8. Example 1: Simple proc /* retrieves and displays all customer records */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'show_all_customers' AND TYPE = 'P') DROP PROCEDURE show_all_customers; GO CREATE PROCEDURE show_all_customers AS BEGIN SELECT customer_id, customer_name, postal_code FROM customer_t; END; GO

  9. Invoke Example 1 Procedure executeshow_all_customers; -- OR Exec show_all_customers;

  10. Example 2: Add Parameters, variables, logic • /* retrieves a specific customer record based on customer ID */ • /* if the customer record has a postal code, code adds 1 to the current postal code. */ • IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'add_one_to_zips' AND TYPE = 'P') • DROP PROCEDURE add_one_to_zips; • GO • CREATE PROCEDURE add_one_to_zips • @desired_customer numeric(5) = null • AS • DECLARE • @ID numeric(4), • @zip numeric(5); • BEGIN • SELECT @ID = customer_id, @zip = postal_code • FROM customer_t • WHERE customer_id = @desired_customer; • IF @zip is not null • BEGIN • SET @zip = @zip + 1 • UPDATE customer_t SET postal_code = @zip WHERE customer_id = @desired_customer; • END; • END; • GO

  11. Invoke and verify example 2 Procedure /* statement below included ONLY so I can undo changes after demo. You DO NOT need to do this for project */ Set implicit_transactions on GO /* show customer 2's record before calling procedure */ select * from customer_t where customer_id = 2; exec add_one_to_zips @desired_customer = 2; -- OR -- exec add_one_to_zips2; GO /* check to see if procedure updated customer 2's record */ Select * from customer_t where customer_id = 2; GO /* statements below included ONLY so I can undo changes after demo. You DO NOT need to do this for project */ Rollback; Set implicit_transactionsoff

  12. Example 3: Printing from stored procedure /* retrieves a specific customer record based on customer ID */ /* if the customer record has a postal code, it will change the postal code to all 9’s & print a message to user */ /* CONVERTs needed since we're concatenating numeric data to text data */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'change_zip_to_nines_and_print' AND TYPE = 'P') DROP PROCEDURE change_zip_to_nines_and_print; GO CREATE PROCEDURE change_zip_to_nines_and_print @desired_customer numeric(5) = null AS DECLARE @ID numeric(4), @zip numeric(5); BEGIN SELECT @ID = customer_id, @zip = postal_code FROM customer_t WHERE customer_id = @desired_customer; IF @zip IS NOT NULL BEGIN SET @zip = 99999 UPDATE customer_t SET postal_code = @zip WHERE customer_id = @desired_customer; print 'cust#: ' + convert(varchar, @desired_customer)+' zipcode changed to: ' + convert(varchar,@zip); END; END; GO

  13. Invoke and verify example 3 Procedure /* statement below included ONLY so I can undo changes after demo. You DO NOT need to do this for project */ Set implicit_transactions on GO Select * from customer_t where customer_id = 2; exec change_zip_to_nines_and_print @desired_customer = 2; GO Select * from customer_t where customer_id = 2; GO /* statement below included ONLY so I can undo changes after demo. You DO NOT need to do this for project */ Rollback; Set implicit_transactionsoff

  14. MULTIPLE-RECORD RETRIEVALS • Require cursors • Declare CURSOR • OPEN cursor • FETCH data from cursor • CLOSE & DEALLOCATE cursor • Use control structures to process each record • WHILE <expression> … [BREAK can be used to force exit from WHILE loop] END; • IF <expression> <statement> ELSE <statement> • IF <expression> BEGIN <statements> END; ELSE BEGIN <statements> END;

  15. Example 4: CURSOR /* retrieve customer records based on NAME; if customer(s) have a zip code, zip is decreased by 1 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'getcust' AND TYPE = 'P') DROP PROCEDURE getcust; GO CREATE PROCEDURE getcust @name varchar(25) AS DECLARE custcursorCURSOR FOR SELECT * from customer_t WHERE customer_name like '%' + @name + '%'; DECLARE @cid numeric (11,0),@cnamevarchar(25), @caddrvarchar(30), @ccityvarchar(20), @cstatevarchar(2), @czip numeric(5), @cowner numeric(11), @corders numeric(4); BEGIN -- logic OPENcustcursor; --find customers meeting criteria IF @@CURSOR_ROWS = 0 BEGIN RAISERROR ('no customer found', 10,1); RETURN; END; FETCHFROMcustcursorINTO @cid,@cname,@caddr,@ccity,@cstate,@czip, @cowner,@corders; /* code continued here… */ WHILE @@FETCH_STATUS = 0 BEGIN -- loop thru each customer record found IF @czipIS NOT NULL BEGIN -- process and display old/new zip PRINT 'id: ' + convert(varchar, @cid) + ' , name: ' + @cname; PRINT 'old zip: ' + convert(varchar, @czip); SET @czip = @czip - 1 UPDATE customer_t SET postal_code = @czip WHERE customer_id = @cid; PRINT 'new zip: '+convert(varchar,@czip); PRINT ' ======================= '; END; -- processing and displaying of zip FETCHNEXT FROMcustcursorINTO @cid,@cname,@caddr,@ccity,@cstate,@czip, @cowner,@corders; END; -- looping thru records CLOSE custcursor; DEALLOCATEcustcursor; END; -- logic GO

  16. Invoke and verify example 4 Procedure Set implicit_transactions on GO Select * from customer_t where customer_name like '%furn%'; Exec getcust'furniture' GO Select * from customer_t where customer_name like '%furn%'; GO Rollback; Set implicit_transactions off

  17. Example 5: another cursor /* for a given order, show the total number of products on the order, and the description of each product on the order */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'showordereditems' AND TYPE = 'P') DROP PROCEDURE showordereditems; GO CREATE PROCEDURE showordereditems @orderid numeric(11) AS DECLARE ordercursor CURSOR FOR select ol.order_id, ol.product_id, p.product_description from order_line_tol, product_t p where ol.order_id = @orderid and ol.product_id = p.product_id; DECLARE @oloid numeric(11), -- storing order # @olpid numeric(11), -- storing product # @pdescvarchar(50), -- storing product description @mycount numeric(4); -- to store the number of products on order BEGIN -- logic SET @mycount=0; -- initialize the counter OPEN ordercursor; -- find the products associated with the given order IF @@CURSOR_ROWS=0 BEGIN RAISERROR ('order not found',10,1) RETURN; END; SELECT @mycount = count(*) FROM order_line_tol WHERE ol.order_id = @orderid; -- calculate total # of products on given order PRINT '** order# ' + convert(varchar,@orderid) + ' has ' + convert(varchar,@mycount) + ' products on it **'; -- print the total # of products on the order PRINT '** the products are listed below **'; FETCH FROM ordercursor INTO @oloid, @olpid, @pdesc; WHILE @@FETCH_STATUS = 0 -- loop thru each product on the order BEGIN PRINT 'product#: ' + convert (varchar, @olpid ) + ' product desc: ' + @pdesc; -- print each product on the order FETCH NEXT FROM ordercursor INTO @oloid, @olpid, @pdesc; END; CLOSE ordercursor; DEALLOCATE ordercursor; END; -- logic

  18. Invoke example 5 Procedure Exec showordereditems1002 GO

  19. Example 6: nested loops OPEN ccursor; -- retrieve all customers FETCH FROM ccursor INTO @ccid, @cname; -- work with one customer IF @@CURSOR_ROWS=0 BEGIN RAISERROR ('customer not found',10,1); RETURN; END; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @mycount = count(*) FROM order_t o WHERE o.customer_id = @ccid; PRINT '**********************************'; PRINT 'cust# '+convert(varchar,@ccid) +' has '+convert(varchar,@mycount) + ' orders'; PRINT 'the products are listed below: '; OPEN ocursor; -- retrieve all ordered products FETCH FROM ocursor INTO @ocid, @oloid, @olpid, @pdesc; WHILE @@FETCH_STATUS = 0 BEGIN IF @ocid = @ccid -- if the ordered product is for the current customer BEGIN -- then print the product info PRINT ' prod#' + convert(varchar,@olpid)+' '+@pdesc; END FETCH NEXT FROM ocursor INTO @ocid, @oloid, @olpid, @pdesc; END; CLOSE ocursor; FETCH NEXT FROM ccursor INTO @ccid, @cname; END; CLOSE ccursor; DEALLOCATE ccursor; DEALLOCATE ocursor; END; -- logic GO /* FOR EACH CUSTOMER who has placed an order */ /* list their total number of orders */ /* AND */ /* list each product description requested on their order(s) */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'customerorders' AND TYPE = 'P') DROP PROCEDURE customerorders; GO CREATE PROCEDURE customerorders AS /* cursor that finds all customers who have placed orders */ DECLARE ccursor CURSOR FOR SELECT customer_id, customer_name FROM customer_t WHERE customer_id IN (SELECT customer_id FROM order_t); /* cursor finds ordered products and the ordered product info */ DECLARE ocursor CURSOR FOR SELECT o.customer_id, ol.order_id, ol.product_id, p.product_description FROM order_line_tol, product_t p, order_t o WHERE ol.product_id = p.product_id AND o.order_id = ol.order_id; DECLARE @ocidvarchar(11), @oloid numeric(11), @olpid numeric(11), @pdescvarchar(50), @ccidvarchar(11), @cnamevarchar(25), @mycount numeric(4); --stores how many ords BEGIN; -- logic SET @mycount = 0; --initialize counter

  20. Invoke example 6 Procedure Exec customerorders GO

  21. Summary: Steps to Create / Invoke Procedures • Store code that creates procedure in a script • Run and debug script in SSMS • Creates & stores procedure in database • Invoke procedure • Issue "execute" command • Include parameter values where required

  22. triggers • Common Uses: • Implementing RI • Complex defaults • Interrelation constraints/updates • Updating views • Auditing business activities • 3 Kinds: • Before  Not supported in SQL Server • After • Instead of • A trigger can execute: • Once for each command • Once for each row impacted by a command  Not supported in SQL Server

  23. Create trigger: from scratch CREATE TRIGGER <trigger_name> ON <table_name> <AFTER | INSTEAD OF> <INSERT [,] UPDATE [,] DELETE> AS DECLARE /* program variables defined here */ BEGIN /* program logic goes here */ END

  24. Example 1: Maintain Counter /* maintain the orders placed counter in the customer table */ /* add 1 to the counter each time a customer places a new order */ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'add_order_placed' AND type = 'TR') DROP TRIGGER add_order_placed; GO CREATE TRIGGER add_order_placed ON order_t AFTER INSERT AS DECLARE @customer AS numeric(11,0) BEGIN SELECT @customer = customer_ID FROM INSERTED; UPDATE customer_t SET orders_placed = orders_placed+1 WHERE customer_t.customer_ID = @customer; END; GO

  25. Example 1: maintain counter, cont… /* Maintain the orders placed counter in the customer table */ /* Every time the customer# on an order is changed, the orders_placed column in the customer table is updated */ /* to reflect the # of orders placed by both the original and new customer */ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'change_order_placed' AND type = 'TR') DROP TRIGGER change_order_placed; GO CREATE TRIGGER change_order_placed ONorder_tAFTER UPDATE AS DECLARE @newcustomer AS numeric(11,0), @oldcustomer AS numeric(11,0) BEGIN SELECT @newcustomer = customer_ID FROMINSERTED; SELECT @oldcustomer = customer_ID FROM DELETED; UPDATE customer_t SET orders_placed = orders_placed+1 WHERE customer_t.customer_ID = @newcustomer; UPDATE customer_t SET orders_placed = orders_placed-1 WHERE customer_t.customer_ID = @oldcustomer; END; GO

  26. FirE/Verify Triggers Set implicit_transactions on GO /* check status of customer’s orders_placed counter before placing new orders */ select customer_id, orders_placed from customer_t where customer_id=10; /* fire insert trigger by placing orders for customer #10 */ insert into order_t values (9900, '10-OCT-01', 10); insert into order_t values (9901, '10-OCT-01', 10); insert into order_t values (9902, '10-OCT-01', 10); /* issue select statement to verify that trigger updated orders_placed counter */ select customer_id, orders_placed from customer_t where customer_id=10; /* select status of customer’s orders_placed counters before placing new orders */ select customer_id, orders_placed from customer_t where customer_id=10; select customer_id, orders_placed from customer_t where customer_id=1; /* fire update trigger by changing the customer# of one of the orders for customer #10 */ update order_t set customer_id = 1 where order_id = 9901; /* issue select statement to verify that the orders_placed counter is correct */ select customer_id, orders_placed from customer_t where customer_id=10; select customer_id, orders_placed from customer_t where customer_id=1; GO Rollback; Set implicit_transactions off

  27. Example 2: setting default value /* when a new product is added, if the standard price is NOT given, then set the product’s standard price to either: */ /* the highest standard price of a product in the same product line if one exists, OR to $300 */ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'set_standard_price' AND type = 'TR') DROP TRIGGER set_standard_price; GO CREATE TRIGGER set_standard_price ON product_t AFTER INSERTAS DECLARE @newproductlineid as numeric(11,0), @newstandardprice as numeric(6,2), @higheststandardprice as numeric(6,2), @productID as numeric (11,0) BEGIN SELECT @productID=product_ID, @newproductlineid= product_line_id , @newstandardprice = standard_price FROM INSERTED; IF @newstandardprice IS NULL BEGIN SELECT @higheststandardprice = MAX(standard_price) FROM product_t WHERE product_line_id = @newproductlineid; IF @higheststandardprice IS NOT NULL SET @newstandardprice = @higheststandardprice ELSE SET @newstandardprice = 300; UPDATE product_t SET standard_price = @newstandardprice WHERE product_id= @productID; END --end IF END; -- end program GO

  28. FirE/Verify Trigger Set implicit_transactions on GO /* issue select statement to view product table before adding new products */ select * from product_t; /* fire insert trigger adding new products to the product_t table*/ insert into product_t values (999,null,null,null,null,20001); insert into product_t values (998, null, null, 123, 3, 33333); Insert into product_t values (997,null,null,null,null,44444); /* issue select statement to verify that trigger updated standard prices for products */ select product_id, product_line_id, standard_price from product_t where product_id>900; GO Rollback; Set implicit_transactions off

  29. Next Time… • 4/17 Internet Database Environment • Chapter 8, pages 337-360 • 4/24 Team Assignment Part 2 DUE

More Related