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 ... END block 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_name to display the function's definition including all code details.

  • List Functions:

    • To list all user-defined functions, employ the SHOW FUNCTION STATUS command.

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.