C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML
MySQL FunctionsCreating a functionIn MySQL, Function can also be created. A function always returns a value using the return statement. The function can be used in SQL queries. SyntaxCREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ] RETURNS return_datatype BEGIN Declaration_section Executable_section END; Parameter:Function_name: name of the function Parameter: number of parameter. It can be one or more than one. return_datatype: return value datatype of the function declaration_section: all variables are declared. executable_section: code for the function is written here. Example 1Step 1: Create database and table. Database: employee Table 1 : designation Table 2 : staff Step 2: Create a function Function query: DELIMITER $$ CREATE FUNCTION get_designation_name(d_id INT) RETURNS VARCHAR( 20 ) BEGIN DECLARE de_name VARCHAR( 20 ) DEFAULT ""; SELECT name INTO de_name FROM designation WHERE id = d_id; RETURN de_name; END $$ Step 3: Execute the function Query : SELECT id, get_designation1(`d_id`) as DESIGNATION, name FROM 'staff' Drop a functionIn MySQL Function can also be dropped. When A function id dropped, it is removed from the database. Syntax:Drop function [ IF EXISTS ] function_name; Parameterfunction_name: name of the function to be dropped. Example 1:drop function get_designation_name;
Next TopicMySQL ROLLUP
|