DATABASE LECTURE FRI MAR 20th
Database Function Creation and Management
Terminology
The term "store" distinguishes from traditional table storage; functions are stored in a database without attachment to a specific table.
Workbench provides a Graphical User Interface (GUI) to facilitate understanding and functionality.
Function Definition
A function in SQL is created to perform a specific task and return a value.
Important components of a function are the header and the body.
Function Header
Create Function Statement:
Syntax begins with a statement that indicates the creation of a function.
For example:
CREATE FUNCTION function_name (parameters) RETURNS data_type
Parameters:
Parameters must be defined within parentheses.
Parameters are listed as:
parameter_name data_type.E.g., for Java, the parameter definition may look different since Java specifies data types first (e.g.
int a).Reminder: Each parameter should have a name and data type specified.
Return Type:
Functions must specify a return type using the keyword
RETURNS.The value returned should match the specified data type.
Function Body
The body of the function contains the SQL statements that define the function's behavior.
Use the
BEGIN ... ENDblock to encapsulate the function's operations.Return Statement:
A return statement is mandatory; it specifies the value returned by the function.
This value can be a local variable, specific value, or the result of an expression.
The return value must be consistent with the declared return type.
Example of Function Creation
Example function may be defined as follows:
CREATE FUNCTION function_name (parameter1 data_type, parameter2 data_type) RETURNS data_type
BEGIN
-- function statements
RETURN value;
END;
Creating Function with Session Variable
Session Variables:
Referenced directly without declaration.
Their value can be modified (e.g., incrementing a session variable).
Syntax and Delimiters
Delimiters:
Important in defining function bodies; allows separating multiple statements.
Example: Set a delimiter with
DELIMITER $$to treat commands until a specified termination.Resume with the standard delimiter after defining the function.
Function Invocation
Functions are invoked within queries, mimicking system functions (e.g. counting rows).
Must include parentheses even if no parameters are passed when calling.
Example usage in query:
SELECT function_name();
Best Practices:
Use local variables instead of session variables for better safety and architecture.
Store any output from function calls properly, especially in iterate rows of data.
Managing Functions
Show Function Code:
Use
SHOW CREATE FUNCTION function_nameto display the function's definition including all code details.
List Functions:
To list all user-defined functions, employ the
SHOW FUNCTION STATUScommand.
Basic System Functions
Numeric Functions
CEIL():
Returns the smallest integer that is greater than or equal to the provided number.
FLOOR():
Returns the largest integer that is less than or equal to the provided number.
ROUND():
Accepts one or two parameters; second parameter specifies decimal places.
Example:
ROUND(value, decimal_places).
TRUNCATE():
Shortens a number to a specified number of decimal places.
Random Number Generation
RAND():
Returns a random floating-point number between 0 (inclusive) and 1 (exclusive).
For integer range, adjust to shifted values and utilize the
FLOOR()function to get desired results.
String Functions
LEFT():
Extracts a substring from the left side of a string based on specified length.
RIGHT():
Extracts a substring from the right side of the string.
SUBSTRING():
Extracts characters from a string, requires starting point and length.
Date and Time Functions
CURRENTDATE and CURRENTTIME:
Return the current date and time.
WEEKDAY():
Returns numerical representation of the day of the week ranging from 0 (Monday) to 6 (Sunday).
Conditionals in SQL Functions
IF() Function:
Utilized for conditional evaluations to return one value if the condition is true and another if false.
Syntax:
IF(condition, true_value, false_value).
IFNULL() Function:
Fetches value presenting default fallback when a value is NULL, thus preserving math calculations.
Conclusion
Remember to always check for the function's data type matching and handle session variables with care, prioritizing local variables.