laika222
12/7/2016 - 10:39 PM
Functions
FUNCTIONS
-- EXAMPLE OF FUNCTION THAT TAKES A DATE ENTERED BY A USER AND ADDS ONE YEAR TO THAT DATE
DELIMITER $
CREATE FUNCTION add_one_year(p_base_date DATE)
RETURNS DATE
READS SQL DATA
BEGIN
RETURN (SELECT DATE_ADD(p_base_date, INTERVAL 1 YEAR));
END $
DELIMITER ;
-- HOW TO INVOKE THIS FUNCTION
SELECT add_one_year('2017-07-30');
-- HOW TO DROP THE FUNCTION
DROP FUNCTION add_one_year;
-- HOW TO SHOW FUNCTIONS THAT EXIST
SHOW FUNCTION STATUS;
-- HOW TO SHOW CRITERIA THAT CREATED FUNCTION
SHOW CREATE FUNCTION add_one_year;
-- EXAMPLE OF HOW TO CREATE average_commission, WHICH HAS 2 ARGUMENTS p_no_sales AND p_no_salespersons, WHICH RETURNS A DECIMAL (19,2), WHICH SELECTS THE AVG() FROM THE PRICE COLUMN OF products1 AND MULTIPLIES IT BY THE NUMBER ENTERED IN p_no_sales, AND THEN DIVIDES THAT BY THE NUMBER ENTERED IN p_no_salespersons).
DELIMITER $
CREATE FUNCTION average_commission(p_no_sales INT, p_no_salespersons INT)
RETURNS DECIMAL (19,2)
READS SQL DATA
BEGIN
RETURN (SELECT ((SELECT AVG(Price) from products1) * p_no_sales) / p_no_salespersons);
END $
-- EXAMPLE OF HOW TO INVOKE THE FUNCTION average_commission PLUGGING IN A NUMBER 17 FOR p_no_sales AND THE NUMBER 4 for p_no_salespersons.
SELECT average_commission(17,4);
-- VERY SIMPLE FUNCTION EXAMPLE, WHICH ADDS 7 TO WHATEVER NUMBER IS INPUT FOR THE ARGUMENT
DELIMITER $
CREATE FUNCTION add_seven(p_enter_number_here INT)
RETURNS INT
READS SQL DATA
BEGIN
RETURN 7 + (SELECT p_enter_number_here);
END $
DELIMITER ;
/****************************************
FUNCTIONS
****************************************/
/*
A FUNCTION accepts parameters, performs an action, and then returns a value(s). There are built-in functions, and you have the ability to create your own functions that return a value or a table. A function can return a results set in a similiar fashion to a stored procedure, but a function can be referenced in a FROM clause, whereas a procedure cannot.
Types of built-in functions:
SCALAR - operates on a single row, returns a single value
AGGREGATE FUNCTIONS - takes one or more input values, then returns a single value summarizing those values
LOGICAL FUNCTIONS - scalar functions that compare multiple values to determine a single output
WINDOW FUNCTIONS - operates on a window (set) of rows
You can also create your own SCALAR and TABLE VALUED FUNCTIONS, which often are given a prefix of 'udf' for User Defined Function. See the TABLE VALUED FUNCTION example below for the syntax for creating a User Defined Function.
TABLE VALUED FUNCTIONS - a function that returns a virtual table. Can be thought of as a View (a saved query) that accepts parameters.
*/
/****************************************
EXAMPLES OF BUILT-IN FUNCTIONS
****************************************/
-- SCALAR FUNCTIONS - operates on a single row, returns a single value. Can be used like an expression in queries. If you apply a scalar function to a set of rows, it will act on each row individually, therefore it's still one in and one out. May be deterministic or non-deterministic (deterministic always returns the same value, but non-deterministic do not always return the same results every time they are called, even with the same set of input values. Example - if you compare a function with today's date against a due date, the same date going in won't always have the same value coming out, depending on what today's date is).
-- Scalar example #1, YEAR.
SELECT YEAR(SellStartDate)
FROM products1;
-- Scalar example #2, DATEDIFF, GETDATE, DATENAME.
SELECT DATEDIFF(yy,SellStartDate, GETDATE()) AS YearsSold, DATENAME(mm,SellStartDate), ProductID
FROM products1
ORDER BY ProductID;
-- AGGREGATE FUNCTIONS - takes one or more input values (set of data), then returns a single value summarizing those values. Aggregate functions ignore NULL values.
-- Aggregate example #1, COUNT. COUNT(*) by itself will return a count of all rows in a table.
SELECT COUNT(*) AS Products, COUNT(DISTINCT ProductCategoryID) AS Categories0
FROM products1
WHERE Products LIKE '%Bikes';
-- LOGICAL FUNCTIONS - scalar functions that compare multiple values to determine a single output.
-- Logical example #1, ISNUMERIC, used to determine if a string value is number. It returns a 1 if this is True (it is numeric) and 0 if False (it's not numeric).
SELECT Name, Size as NumericSize
FROM products1
WHERE ISNUMERIC(Size) = 1;
-- Logical example #2, IIF, which checks something, and then returns either one value or another based on the result.
SELECT productid, listprice, IIF(listprice > 50, 'high', 'low') AS PricePoint
FROM products1;
-- Logical example #3, CHOOSE, which turns a code into a string value. In example below, 1 will return as 'Bikes', 2 will return as 'Components', 3 will return as 'Clothing', etc. (the string values are derived from the ordinal position you place the values in).)
SELECT ProductName, Color, Size
CHOOSE(ProductCategoryID, 'Bikes', 'Components', 'Clothing') AS Category
FROM products1;
-- WINDOW FUNCTIONS - operates on a window (set) of rows. Includes ranking, offest, aggregate, and distribution functions. The OVER clause defines a window or user-specified set of rows within a query result set, which is used before the window function then computes a value for each row in the window.
-- Window function example #1, RANK, which looks at values and gives them a numeric ranking based on the value.
SELECT TOP 3 ProductID, Name, ListPrice
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM products1
ORDER BY RankByPrice;
-- Window function example #2, ROW_NUMBER counts the rows, and RANK ranks along with a PARTION (the ranking restarts each time a new SalesPersonID comes up in the result set: TITION (allows you to reset the numbering each time a new partition comes up.
SELECT SalesPersonID, TotalDue,
ROW_NUMBER() OVER(ORDER BY SalesPersonID, TotalDue) AS 'Row Count',
RANK() OVER(PARTITION BY SalesPersonID ORDER BY SalesPersonID, TotalDue) AS 'Rank by SalesPerson'
FROM Sales.SalesOrderHeader
ORDER BY SalesPersonID;
/* Excerpt from results. Note how ROW_NUMBER is not partitioned, so it counts the rows throughout entire result set. RANK is partioned by SalesPersonID, so it restarts the ranking when a new SalesPersonID comes up:
SalesPersonID TotalDue Row Count Rank by SalesPerson
...
274 95426.0186 46 46
274 97248.3525 47 47
274 126852.1615 48 48
275 6.3484 49 1
275 12.0152 50 2
275 18.3792 51 3
278 22.3061 52 1
278 25.3868 53 2
278 27.4401 54 3
278 27.4401 54 4
...
*/
/****************************************
CREATING A USER DEFINED FUNCTION
****************************************/
/* SCALAR USER DEFINED FUNCTIONS - a function that returns a single value. Note that a scalar valued
function uses BEGIN and END, the RETURN keyword is needed (apparently a SELECT cannot be used to
return a value through a function, RETURN is needed), and that the SELECT syntax is simply SELECT
followed by the function name (SELECT udf_functionname(2)), rather than the syntax used when SELECTing
a table valued function, which is more like SELECTing from a table (SELECT * FROM udf_functionname(2)).
Also note that you are apparently required to include schema prefix (in this case 'dbo.') when calling
a scalar function.
*/
CREATE FUNCTION udf_prod_num_orders(@productID INT)
RETURNS INT
AS
BEGIN
RETURN
(
SELECT COUNT(ProductID)
FROM orders1
WHERE ProductID = @productID
)
END;
SELECT dbo.udf_prod_num_orders(1);
/*TABLE VALUED USER DEFINED FUNCTIONS - a function that returns a virtual table. Can be thought of as
a View (named, saved query) that accepts parameters. Note that an inline table valued function
(this kind) does not use BEGIN and END (though a multi-line table valued function apparently does),
the RETURN keyword is required to pass the value out of the function, and that the SELECT syntax is
not like that with calling a scalar function (SELECT udf_functionname(2)), but rather is like SELECTing
from a table (SELECT * FROM udf_functionname(2)).
*/
CREATE FUNCTION udf_prod_customer_date_table_valued(@productID INT)
RETURNS TABLE
AS
RETURN
(
SELECT CustomerID, OrderDate
FROM orders1
WHERE ProductID = @productID
);
SELECT * FROM udf_prod_customer_date_table_valued(1);
/****************************************
FUNCTION DETERMINISM
****************************************/
/*
Function determinism is a characteristic that indicates whether the function is guaranteed to return the
same result give the same set of input values (including an empty set) in different invocations. If the
function provides such a guarantee, it is said to be deterministic; otherwise, it is nondeterministic.
There are three main categories of function determinism:
1. Functions that are always deterministic
2. Functions that are deterministic when invoked in a certain way
3. Functions that are always nondeterministic
Always deterministic functions include all string functions, COALESCE, ISNULL, ABS, SQRT and others. They'll
always give the same result given the same inputs.
Other functions are deterministic depending on how they're used. CAST and CONVERT are not deterministic
when converting from a character string to a date or time type or the other way because the
interpretation of the value might be dependent on the login's language. RAND is also not always deterministic.
When it is used with a seed it is deterministic, always giving the same result. When not used with a
seed, it will give you a pseudo random value and is therefore nondeterministic.
Always nondeterministic functions include SYSDATETIME and NEWID.
*/
/****************************************
VIEWING A FUNCTION DEFINITION AFTER IT HAS BEEN CREATED
****************************************/
/* Viewing the definition can be done by finding the function in Object Explorer, right-clicking and selecting
Modify or Script Function As. It can also be done using SQL:
*/
-- Get the function name, definition, and relevant properties
SELECT sm.object_id,
OBJECT_NAME(sm.object_id) AS object_name,
o.type,
o.type_desc,
sm.definition,
sm.uses_ansi_nulls,
sm.uses_quoted_identifier,
sm.is_schema_bound,
sm.execute_as_principal_id
-- using the two system tables sys.sql_modules and sys.objects
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
-- from the function 'dbo.ufnGetProductDealerPrice'
WHERE sm.object_id = OBJECT_ID('dbo.myfunctionnotsecret')
ORDER BY o.type;
GO
/****************************************
FUNCTION DEFINITION ENCRYPTION
****************************************/
/*
Using the WITH ENCRYPTION keyword when creating the function will hide the function definition from all future users
who try to view it. This can be useful if for example you are creating a simple text encryption function to jumble the
letters of a sensitive column. If an attacker were to access your database, she could find the encryption function
and use the definition to decrypt the data. However, if you hide the function definition from being viewed, then it
becomes more difficult to figure out what actions were taken via the encryption process, and therefore it becomes more
difficul to decrypt the sensitive data.
*/
CREATE FUNCTION myfunctionnotsecret(@enterNumber INT)
RETURNS INT
WITH ENCRYPTION -- will encrypt the definition from future view
AS
BEGIN
declare @finalNumber INT
SELECT @finalNumber = @enterNumber * 2
RETURN @finalNumber
END
GO
/* The function will still work, but if you use WITH ENCRYPTION when defining the function, if you right-click and Modify/Script
in Object Explorer, you will get an error saying the text is encrypted. If you use the SQL script method, it'll show up with a
NULL value in the definition column.
*/