1 / 19

Comprehensive Guide to Stored Procedures and User Defined Functions in Database Management

This guide delves into the benefits, syntax, and best practices of stored procedures, user-defined functions, transactions, and their importance in database management. Learn about the advantages, security, and efficiency gained through these techniques.

markl
Télécharger la présentation

Comprehensive Guide to Stored Procedures and User Defined Functions in Database Management

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. Stored Procedures & User Defined Functions MIS 424 Professor Sandvig

  2. Today • Stored Procedures • Store SQL statements on DB • User Defined Functions (UDF) • Database • Transactions • Rollback

  3. Stored Procedures • So far: • SQL or LINQ queries in .NET code • Pass to database • Alternative: • Stored procedures • Store SQL query on database • Call by name, pass parameters

  4. Stored Procedures • Benefits: • Reusability • Call same SP from many projects • Hide complexity • Complex queries hidden behind simple interface • Division of duties • Programmers vs. database admin

  5. Stored Procedures • Benefits: • Loose coupling • Create clear separation between code & data • Reduces dependencies • Changes in data source do not affect code • N-Tier Programming • Diagram (Source: Microsoft)

  6. Benefits of Stored Procedures • Security • Set permissions on procedures • Users see only data accessible via procedure • Limit access to tables

  7. Benefits of Stored Procedures • Efficiency • SQL compiled • Execution plan saved • Some controversy • Execute complex procedures • Triggers • Multiple SQL statements • Programming languages • Transact-SQL • .NET languages

  8. Disadvantages • More code • Dependencies difficult to manage • Not fully supported by all databases • MySQL • Recently added • Limited • Microsoft Access • Store basic SQL statements

  9. Creating • Visual Studio

  10. Creating

  11. Syntax & Example Example: • Using stored procedure • See handout

  12. Views & Functions • Views • “Virtual” tables • No data, definition only • View defined by sql statement • May include data from several tables • Similar to stored procedure except no parameters • Benefits: • Hide table details • Set permissions

  13. User Defined Functions • Functions (user defined functions) • Retrieve only • May use parameters • May call from SPs, other functions, SQL commands • Benefits • Reusability • Hide complexity

  14. User Defined Functions • Creating in Visual Studio

  15. User Defined Functions • Syntax Create FUNCTION dbo.GetProductCategories ( @ProductID int ) RETURNS TABLE AS RETURN Select c.CatLabel FROM tblCategories c, tblProductCategories p where c.CategoryID = p.CategoryID AND p.ProductID = @ProductID sql = "select * from dbo.GetProductCategories(16) order by CatLabel";

  16. Transactions • Many transactions have dependency • Bank transfer • Remove $$ from one account • Add to another • On-line purchase • Charge credit card • Ship item • Item out-of-stock?

  17. Transactions If one task fails: • Prior transactions are rolled back • Supported by most commercial databases • Not mySQL • Database keeps a log of transaction • Easy to use • Example: Datebase Transactions

  18. Summary • Advanced Data Techniques • Stored Procedures • Parameters • Transactions • Goal: • Modularity • Reusability • Robust

More Related