MySQL Articles - Page 371 of 439

MySQL Stored functions can reference tables but they cannot make use of statements that return a result set. Hence we can say that there is no SELECT query that returns result set. But we can have SELECT INTO to get rid of that. For example, we are creating a function ‘Avg_marks’ that uses the dynamic data from table named ‘Student_marks’, having following records, to calculate the average of marks.mysql> Select * from Student_marks; +-------+------+---------+---------+---------+ | Name  | Math | English | Science | History | +-------+------+---------+---------+---------+ | Raman |   95 |      89 |      85 | ... Read More

With the help of SHOW CREATE FUNCTION statement, we can see the source code of a stored function. To make it understand we are using the stored function named Hello() in the query as follows −mysql> SHOW CREATE FUNCTION Hello\G *************************** 1. row ***************************            Function: Hello            sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `Hello`(S Varchar(20)) RETURNS varchar(20) CHARSET latin1 DETERMINISTIC RETURN CONCAT('Hello, ', S, '!') character_set_client: cp850 collation_connection: cp850_general_ci   Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)Read More

We can mysql.proc to see the list, along with complete information, of stored functions in a particular MySQL database by the following query −mysql> Select * from mysql.proc where db = 'query' AND type = 'FUNCTION' \G *************************** 1. row ***************************                   db: query                 name: factorial                 type: FUNCTION        specific_name: factorial             language: SQL      sql_data_access: CONTAINS_SQL     is_deterministic: YES        security_type: DEFINER     ... Read More

We can see the list, along with other information, of stored functions in a particular MySQL database by the following query −mysql> SHOW FUNCTION STATUS WHERE db = 'query'\G *************************** 1. row ***************************                   Db: query                 Name: factorial                 Type: FUNCTION              Definer: root@localhost             Modified: 2021-11-16 14:04:48              Created: 2021-11-16 14:04:48        Security_type: DEFINER           ... Read More

As we know that with the help of QUOTE() function we can put the values of a column in single quotes. By using QUOTE() function with UPDATE clause we can update the table having quoted values. We need to give column name as the parameter of QUOTE() function. Following example will update the table ‘examination_btech’ after putting the values of column ‘Course’ in single quotes. Example mysql> UPDATE examination_btech SET Course = QUOTE(Course); Query OK, 10 rows affected (0.05 sec) mysql> Select * from examination_btech; +--------+----------+----------+ | RollNo | Name | Course ... Read More

We can update MySQL table after padding a string with the values of a column by using LPAD() or RPAD() function along with UPDATE clause. Following the example from ‘examination_btech’ table will make it clearer −ExampleSuppose if we want to append the values, in last, of column course with the string ‘(CSE)’ and want to update the table too then it can be done with the help of the following query −mysql> Update examination_btech set course = RPAD(Course, 11, '(CSE)'); Query OK, 10 rows affected (0.16 sec) mysql> Select * from examination_btech; +-----------+----------+-------------+ | RollNo    | Name   ... Read More

Just like LENGTH() function, MySQL BIT_LENGTH() function is not a multi-byte safe function. As we know that the difference of the result between multi-byte safe functions, like CHAR_LENGTH() or CHARACTER_LENGTH(), and BIT_LENGTH() function especially relevant for Unicode, in which most of the characters are encoded in two bytes or relevant for UTF-8 where the number of bytes varies. It is demonstrated in the example below −Examplemysql> Select BIT_LENGTH('tutorialspoint'); +------------------------------+ | BIT_LENGTH('tutorialspoint') | +------------------------------+ | 112                          | +------------------------------+ 1 row in set (0.00 sec)The above result set shows that ... Read More

We can see only the list of stored functions in a particular MySQL database by the following query −mysql> SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'query' AND ROUTINE_TYPE = 'FUNCTION'// +--------------+--------------------+ | ROUTINE_TYPE | ROUTINE_NAME       | +--------------+--------------------+ | FUNCTION     | factorial          | | FUNCTION     | Hello              | +--------------+--------------------+ 2 rows in set (0.07 sec)

If we want to use prepared statements in a stored procedure then it must be written inside the BEGIN and END block. To understand it, we are creating an example with the help of which we can get all the records from a table by passing the name of the table as a parameter of the stored procedure.Examplemysql> DELIMITER // mysql> Create procedure tbl_detail(tab_name Varchar(40))     -> BEGIN     -> SET @A:= CONCAT('Select * from', ' ', tab_name);     -> Prepare stmt FROM @A;     -> EXECUTE stmt;     -> END // Query OK, 0 ... Read More

The most significant difference between procedures and functions is that they are invoked differently and for different purposes. Other than that following are the differences between procedure and functions −A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records.On the other hand, a function is invoked within an expression and returns a single value directly to the caller to be used in the expression. That is, a function is used in expressions the same way as a constant, a built-in function, or ... Read More