1 / 15

Create Stored Procedures and Functions

Create Stored Procedures and Functions. LESSON 2.4. 98-364 Database Management Fundamentals. Lesson Overview In this lesson, you will learn about: Functions Aggregate Functions Scalar Functions User Defined Functions Stored Procedures Benefits of Stored Procedures. What is a function?

adonia
Download Presentation

Create Stored Procedures and Functions

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. Create Stored Procedures and Functions LESSON 2.4 98-364 Database Management Fundamentals

  2. Lesson Overview • In this lesson, you will learn about: • Functions • Aggregate Functions • Scalar Functions • User Defined Functions • Stored Procedures • Benefits of Stored Procedures

  3. What is a function? The purpose of, or the action carried out by a program or routine A function is a routine that returns a value routine - Any section of code that can be invoked (executed) within a program. A routine usually has a name (identifier) associated with it and is executed by referencing that name. Related terms include function, procedure, and subroutine Functions and routines can be synonymous and they are commands that return a value

  4. Aggregate Functions Return a single value, calculated from values in a column Examples: • AVG() - Returns the average value SELECT AVG(column_name) FROM table_name • COUNT() - Returns the number of rows SELECT COUNT(column_name) FROM table_name • FIRST() - Returns the first value SELECT FIRST(column_name) FROM table_name

  5. Aggregate Functions (Continued) • LAST() - Returns the last value SELECT LAST(column_name) FROM table_name • MAX() - Returns the largest value SELECT MAX(column_name) FROM table_name • MIN() - Returns the smallest value SELECT MIN(column_name) FROM table_name • SUM() - Returns the sum SELECT SUM(column_name) FROM table_name

  6. Scalar Functions Scalar Functions return a single value, based on the input value. Examples: • UCASE() - Converts text in a field to upper case SELECT UCASE(column_name) FROM table_name • LCASE() - Converts a field to lower case SELECT LCASE(column_name) FROM table_name • MID() - Selects characters from a text field SELECT MID(column_name,start[,length]) FROM table_name

  7. Scalar Functions (Continued) • LEN() - Returns the length of a text field SELECT LEN(column_name) FROM table_name • ROUND() - Rounds a numeric field to the number of decimals specified SELECT ROUND(column_name,decimals) FROM table_name

  8. User Defined Functions • Written incompact SQL code which can accept parameters and returns either a value or a table • Advantage - can be used in Select, Where, or Case statements. They also can be used to create joins • Cannot be used to modify base table information 

  9. User Defined Function (Continued) To create Function CREATE FUNCTION [Function name] ([input variables]) RETURNS varchar(10) AS BEGIN DECLARE [output variable] varchar(10) RETURN [output variable] END

  10. User Defined Function (Continued) • To call a function: SELECT [Function name] ([input variables]) • To remove a function: DROP FUNCTION [Function name]

  11. Stored Procedures • Precompiled group of SQL statements written by users and saved to the database • A SQL statement(s) generated by the user that is saved in the database Create a stored procedure CREATE PROCEDURE [sp_Procedure Name] AS SELECT * FROM [Table] sp = stored procedure (This is a standard naming convention)

  12. Stored Procedures (Continued) Create a stored procedure for multi-site campus CREATE PROCEDURE sp_getclass @campuslocation varchar(30) AS SELECT ClassName, ClassSection FROM Current_term WHERE campus = @campuslocation

  13. Stored Procedures (Continued) Call a stored procedure EXEC [procedure name] ([input variables]) EXECUTE sp_getclass ‘Twin Cities‘ This stored procedure will return a two item list or view (ClassName,ClassSection) of all classes on the “Twin Cities” campus

  14. Benefits of Stored Procedures Speed - Stored procedures are pre-compiled Code reuse  - Stored procedures often involve complex code which only has to written once Security - Permissions can be granted for stored procedures while being restricted for the underlying tables Reduced traffic between client and server - The query is stored on the server and only the procedure call gets sent, so traffic to the server is decreased

  15. Lesson Review Quiz for Discussion • What is the basic difference between a function and a stored procedure? • What would be the Stored Procedure call for a list of classes on the Duluth campus? (slide 13) • What can we add to our sp_getclass stored procedure to make it more useful? (slide 13)

More Related