1 / 35

Chapter 7 – Part 3

Chapter 7 – Part 3. Stored Procedure, Function and Trigger. Objectives. Database Programming Stored Procedure Function Trigger. 1. Database Programming. 1.1 Variables 1.2 Control-of-Flow Tools. 1.1 Variables. Declare a variable: DECLARE  @limit money

noleta
Download Presentation

Chapter 7 – Part 3

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. Chapter 7 – Part 3 Stored Procedure, Function and Trigger

  2. Objectives • Database Programming • Stored Procedure • Function • Trigger

  3. 1. Database Programming 1.1 Variables 1.2 Control-of-Flow Tools

  4. 1.1 Variables • Declare a variable: DECLARE @limit money DECLARE @min_rangeint, @hi_rangeint • Assign a value into a variable: SET @min_range = 0, @hi_range = 100 SET @limit = $10 • Assign a value into a variable in SQL statement: SELECT @price = price FROM titles WHERE  title_id = 'PC2091'

  5. 1.2 Control-of-Flow Tools 1.2.1 BEGIN…END 1.2.2 IF…ELSE 1.2.3 CASE … WHEN 1.2.4 RETURN [n] 1.2.5 WHILE 1.2.6 PRINT

  6. 1.2.1 BEGIN…END • Defines a statement block • Other Programming Languages: • C#, Java, C: { … } • Pascal, Delphi: BEGIN … END

  7. 1.2.2 IF…ELSE • Defines conditional and, optionally, alternate execution when a condition is false IF Boolean_expression T-SQL_statement | block_of_statements [ELSE  T-SQL_statement | block_of_statements ] Example: IF (SELECT ytd_sales FROM titles WHERE title_id = 'PC1035') > 5000 PRINT 'Year-to-date sales are  greater than $5,000 for PC1035.‘

  8. 1.2.3 CASE … WHEN CASE input_expression WHEN  when_expression  THEN result_expression [WHEN when_expression  THEN result_expression…n] [ELSE else_result_expression ] END Example: SELECT CASE payterms WHEN 'Net 30' THEN 'Payable 30 days  after invoice' WHEN 'Net 60' THEN 'Payable 60 days after invoice'   WHEN 'On invoice' THEN 'Payable upon  receipt of invoice'      ELSE 'None'     END as Payment_Terms FROM sales ORDER BY payterms Other Programming Language C#, Java: Switch … Case ; VB: Select … Case

  9. 1.2.4 RETURN [n] • Exits unconditionally of Trigger, Procedure or Function and return a value (if any).

  10. 1.2.5 WHILE • Repeats a statement (or block) while a specific condition is true WHILE Boolean_expression SQL_statement | block_of_statements [BREAK] SQL_statement | block_of_statements [CONTINUE] Example: WHILE (SELECT AVG(royalty) FROM roysched) < 25 BEGIN UPDATE roysched SET royalty = royalty * 1.05 IF (SELECT MAX(royalty)FROM roysched) > 27 BREAK ELSE CONTINUE END SELECT MAX(royalty) AS "MAX royalty"  FROM roysched

  11. 1.2.6 PRINT • Display message in SQL Query Analyze (Console) PRINT string • Other Programming Languages: • Java: System.out.print • C#, VB.NET: Console.WriteLine

  12. 2. Stored Procedure 2.1 What Is a Stored Procedure? 2.2 Stored Procedure vs. SQL Statement 2.3 Create, update and delete a procedure

  13. 2.1 What Is a Stored Procedure? • A stored procedure is a collection of T-SQL statements that SQL Server compiles into a single execution plan. • Procedure is stored in cache area of memory when the stored procedure is first executed so that it can be used repeatedly. SQL Server does not have to recompile it every time the stored procedure is run. • It can accept input parameters, return output values as parameters, or return success or failure status messages.

  14. 2.2 Stored Procedure vs. SQL Statement SQL Statement Stored Procedure • Creating • Check syntax • Compile • First Time • - Check syntax • - Compile • Execute • Return data • First Time • Execute • Return data • Second Time • - Check syntax • - Compile • Execute • Return data • Second Time • Execute • Return data

  15. 2.3 Create, update and delete a procedure 2.3.1 Create a Procedure 2.3.2 Update a Procedure 2.3.3 Delete a Procedure

  16. 2.3.1 Create a Procedure 2.3.1.1 Syntax 2.3.1.2 Example 1 (Without parameters) 2.3.1.3 Example 2 (With parameters) 2.3.1.4 Example 3 (Using RETURN)

  17. 2.3.1.1 Syntax CREATE PROC[EDURE] procedure_name [ @parameter_name data_type] [= default] OUTPUT][,...,n] AS  T-SQL_statement(s)

  18. 2.3.1.2 Example 1 (Without parameters) CREATE PROC Departments_Members AS SELECT Dep_Name, COUNT(Emp_ID) NumberOfMember FROM Departments D, Employees E WHERE D.Dep_ID = E.Dep_ID GROUP BY Dep_Name Run Procedure Execute Departments_Members

  19. 2.3.1.3 Example 2 (With parameters) CREATE PROC Department_Members @DeptName varchar(50) AS SELECT Dep_Name, COUNT(Emp_ID) NumberOfMember FROM Departments D, Employees E WHERE D.Dep_ID = E.Dep_ID and Dep_Name = @DeptName GROUP BY Dep_Name Run Procedure Execute Department_Members ‘Accounting’

  20. 2.3.1.4 Example 3 (Using RETURN ) CREATE PROC GROUPLEADER_MEMBERS @Emp_Code varchar(10) = null AS IF @Emp_Code is null BEGIN PRINT 'Please enter Employee Code!' RETURN END SELECT * FROM Employees WHERE EMP_EMP_ID = (SELECT EMP_ID FROM Employees WHERE Emp_Code = @Emp_Code) ORDER BY Emp_Name

  21. 2.3.2 Update a Procedure ALTER PROC[EDURE] procedure_name [ @parameter_name data_type]  [= default] [OUTPUT] [,...,n] AS  t-sql_statement(s)

  22. 2.3.3 Delete a Procedure • DROP PROCEDURE  procedure_name

  23. 3. Function 3.1 What is a Function? 3.2 Scalar functions Example 3.3 Inline Table-valued Functions Example 3.4 Multi-statement Table-Valued Functions Example

  24. 3.1 What is a Function? • See “What Is a Stored Procedure?” on slide 13 • SQL Server supports three types of user-defined functions: • Scalar functions • Inline table-valued functions • Multi-statement table-valued functions

  25. 3.2 Scalar functions Example CREATE FUNCTION Revenue_Day (@Date datetime) Returns money AS BEGIN DECLARE @total money SELECT @total = sum(sali_Quantity * sali_price) FROM Sales_Orders s, Sales_Orders_Items si WHERE s.sal_number = si.sal_number and year(sal_date) = year(@Date) and month(sal_date) = month(@Date) and day(sal_date)= day(@Date) RETURN @total END Use: select dbo.Revenue_In_Day(GETDATE())

  26. 3.3 Inline Table-valued Functions Example CREATE FUNCTION AveragePricebyItems (@price money = 0.0) RETURNS table AS RETURN ( SELECT Ite_Description, Ite_Price FROM Items WHERE Ite_Price > @price) Use: select * from AveragePricebyItems (15.00)

  27. 3.4 Multi-statement Table-Valued Functions Example CREATE FUNCTION AveragePricebyItems2 (@price money = 0.0) RETURNS @table table (Description varchar(50) null, Price money null) AS begin insert @table SELECT Ite_Description, Ite_Price FROM Items WHERE Ite_Price > @price return end Use: select * from AveragePricebyItems2 (15.00)

  28. 4. Trigger 4.1 What is a Trigger? 4.2 Creating Syntax 4.3 Disable/Enable syntax 4.4 Deleted and Inserted tables 4.5 Example 4.6 Other Functions

  29. 4.1 What is a Trigger? • A trigger is a special type of stored procedure that is executed automatically as part of a data modification. • A trigger is created on a table and associated with one or more actions linked with a data modification (INSERT, UPDATE, or DELETE). • When one of the actions for which the trigger is defined occurs, the trigger fires automatically • Following are some examples of trigger uses: • Maintenance of duplicate and derived data • Complex column constraints • Cascading referential integrity • Complex defaults • Inter-database referential integrity

  30. 4.2 Creating Syntax CREATE TRIGGER trigger_name ON <tablename> <{FOR | AFTER}> {[DELETE] [,] [INSERT] [,] [UPDATE]} AS SQL_Statement [...n]

  31. 4.3 Disable/Enable syntax • Disable syntax Disable trigger <trigger_name> on <table_name> • Enable syntax Enable trigger <trigger_name> on <table_name>

  32. 4.4 Deleted and Inserted tables • When you create a trigger, you have access to two temporary tables (the deleted and inserted tables). They are referred to as tables, but they are different from true database tables. They are stored in memory—not on disk. • When the insert, update or delete statement is executed. All data will be copied into these tables with the same structure. • The values in the inserted and deleted tables are accessible only within the trigger. Once the trigger is completed, these tables are no longer accessible. Insert Update Delete new old old new Inserted Table Deleted Table

  33. 4.5 Example CREATE TRIGGER Print_Update ON Bicycle_Inventory FOR UPDATE AS PRINT "The Bicycle_Inventory table was updated"

  34. 4.6 Other Functions • View contents of trigger sp_helptext <trigger name> • View number of triggers of a table sp_helptrigger <table name>

  35. ?

More Related